MySQL 数据库表操作
第1章 表的介绍
MySQL中的表就和我们excel表格中的sheet一样，一个excel里面可以有多个sheet。在MySQL数据库里面，一个库也可以有多个表，每个表中记录了无数条的数据。这些关系型的数据显示为列和行（类似excel表格）。我们队MySQL的表操作就是对一个sheet操作。
第2章 表的操作
2.1查看表
表是存在于数据库中，所以我们看的表应该是在某个库的下面例如：database.sheet表示数据库database下面的sheet表。下面我们来学习SQL命令来查看表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
|
test
|
| test2 |
+------------------+
3 rows
in
set
(0.01 sec)
mysql> show create tabletest\G
*************************** 1. row***************************
Table:
test
Create Table: CREATE TABLE `
test
` (
`
id
` int(4)NOT NULL AUTO_INCREMENT,
`name`char(20) NOT NULL,
PRIMARY KEY(`
id
`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULTCHARSET=utf8
1 row
in
set
(0.00 sec)
mysql> desc
test
;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
|
id
|int(4) | NO | PRI | NULL | auto_increment |
| name |char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows
in
set
(0.01 sec)
小结：
1、show tables;
#显示库里面包含的所有表
2、desc
test
;
#显示表的结构
3、show create table
test
;
#显示创建表的SQL语句
|
2.2表的创建
我们可以通过help create or help contents来查看帮助
|
1
2
|
mysql> create table student_test(
id
int(4) notnull, name char(20) not null, age tinyint(2) NOT NULL default
'0'
, dept varchar(16) default NULL );
#
创建一个表为student_test，有4个字段
|
可以通过desc来查看表的结构
|
1
2
3
4
5
6
7
8
9
10
|
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| name |char(20) | NO | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| dept |varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows
in
set
(0.00 sec)
|
2.3表的删除
方法一：drop命令
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> drop table student_test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
|
test
|
| test2 |
+------------------+
3 rows
in
set
(0.00 sec)
|
方法二：delete命令
我们用上面表创建的命令重新创建表：student_test
|
1
|
create table student_test(
id
int(4) not null, namechar(20) not null, age tinyint(2) NOTNULL default
'0'
, dept varchar(16) default NULL );
|
查看表
|
1
2
3
4
5
6
7
8
9
10
11
|
show tables
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
| student_test |
|
test
|
| test2 |
+------------------+
4 rows
in
set
(0.00 sec)
|
|
1
2
3
|
删除表
delete
from
表名：
删除所有记录，表结构还在，写日志，可以恢复的，速度慢
|
方法三：truncate
|
1
2
3
4
5
6
7
8
9
|
truncate talbe 表名
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
小结：
delete from 表名：
删除所有记录，表结构还在，写日志，可以恢复的，速度慢
drop talbe 表名：
删除表的结构和数据
truncate talbe 表名
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
|
使用场合：
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a whereclause), 用 delete.
注意：
对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够激活触发器
2.4表的修改
2.4.1添加字段
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> alter table student_test add sex char(2)after age;
#这里使用了参数after，表示在什么之后插入，如果在第一行
QueryOK, 0 rows affected (0.02 sec) firs参数。
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| name |char(20) | NO | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| sex | char(2) | YES | | NULL | |
| dept |varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows
in
set
(0.00 sec)
|
2.4.2修改type类型
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| name |char(20) | NO | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| sex | char(2) | YES | | NULL | |
| dept |varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows
in
set
(0.00 sec)
mysql> alter tablestudent_test modify sex varchar(4);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| name |char(20) | NO | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| sex | varchar(4) | YES | | NULL | |
| dept |varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows
in
set
(0.00 sec)
|
2.4.3删除部分字段
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter tablestudent_test drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| name |char(20) | NO | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| dept |varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows
in
set
(0.00 sec)
|
2.4.4改变表的字符集
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> show create table student_test\G
*************************** 1. row***************************
Table:student_test
Create Table: CREATE TABLE `student_test` (
`
id
` int(4)NOT NULL,
`name`char(20) NOT NULL,
`age`tinyint(2) NOT NULL DEFAULT
'0'
,
`dept`varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row
in
set
(0.00 sec)
mysql> alter tablestudent_test CONVERT TO CHARACTER SET latin1;
mysql> show create table student_test\G
*************************** 1. row***************************
Table:student_test
Create Table: CREATE TABLE `student_test` (
`
id
` int(4)NOT NULL,
`name`char(20) NOT NULL,
`age`tinyint(2) NOT NULL DEFAULT
'0'
,
`dept`varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row
in
set
(0.00 sec)
|
2.4.5修改字段名字已经type类型
把name字段改成xingming，类型从char改成varchar
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
mysql> desc student_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| dept |varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows
in
set
(0.01 sec)
mysql> alter table student_test change columnname xingming varchar(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_test;
+----------+-------------+------+-----+---------+-------+
| Field |Type | Null | Key | Default |Extra |
+----------+-------------+------+-----+---------+-------+
|
id
|int(4) | NO | | NULL | |
| xingming | varchar(40) | YES | | NULL | |
| age |tinyint(2) | NO | | 0 | |
| dept |varchar(16) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows
in
set
(0.00 sec)
|
2.4.6表的重命名
把student_test表重命名为stu_test
m
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
ysql> rename table student_test to stu_test;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| stu_test |
| student |
|
test
|
| test2 |
+------------------+
4 rows
in
set
(0.00 sec)
|
第3章 表数据的操作
3.1insert命令插入数据
数据的表的插入使用命令insert，语法insert into 表名
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into stu_testvalues(1,
'kirk'
,28,
'male'
);
Query OK, 1 row affected (0.00 sec)
mysql>
select
* from stu_test;
+----+----------+-----+------+
|
id
| xingming | age | dept |
+----+----------+-----+------+
| 1 |kirk | 28 | male |
+----+----------+-----+------+
1 row
in
set
(0.00 sec)
|
3.1.1插入数据遇到的问题
ERROR 1300 (HY000): Invalid utf8 character string: '\xA1\xAFaaa\xA1\xAF'
解决办法：引号问题’ ’这种是GB2312 , ' '这种是utf-8
ERROR 1366 (HY000): Incorrect stringvalue: '\xC4\xD0' for column 'sex' at row 1
解决办法：show variables like 'chara%';
setcharacter_set_client=gb2312;
该处查看会显示乱码
解决方法：set character_set_results=gb2312;
3.2修改表数据update
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> update stu_test setxingming=
'laowang'
where
id
=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
select
* from stu_test;
+----+----------+-----+------+
|
id
| xingming | age | dept |
+----+----------+-----+------+
| 1 |laowang | 28 | male |
| 2 |huasheng | 23 | male |
| 3 |dadou | 20 | nv |
+----+----------+-----+------+
3 rows
in
set
(0.00 sec)
|
第4章 表的查询
4.1条件查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
mysql>
select
* from stu_test where xingming=
'dadou'
;
+----+----------+-----+------+
|
id
| xingming | age | dept |
+----+----------+-----+------+
| 3 |dadou | 20 | nv |
+----+----------+-----+------+
1 row
in
set
(0.00 sec)
mysql>
select
xingming,age from stu_test;
+----------+-----+
| xingming | age |
+----------+-----+
| laowang | 28 |
| huasheng | 23 |
| dadou | 20 |
+----------+-----+
3 rows
in
set
(0.00 sec)
+----+----------+-----+------+
|
id
| xingming | age | dept |
+----+----------+-----+------+
| 1 |laowang | 28 | male |
| 2 |huasheng | 23 | male |
| 3 |dadou | 20 | nv |
| 4 |dadou | 20 | male |
+----+----------+-----+------+
4 rows
in
set
(0.00 sec)
mysql>
select
20 from stu_test;
+----+
| 20 |
+----+
| 20 |
| 20 |
| 20 |
| 20 |
+----+
4 rows
in
set
(0.00 sec)
mysql>
select
distinct20 from stu_test;
+----+
| 20 |
+----+
| 20 |
+----+
1 row
in
set
(0.00 sec)
mysql>
select
xingming,(age+dept) from stu_test;
+----------+------------+
| xingming | (age+dept) |
+----------+------------+
| laowang | 28 |
| huasheng | 23 |
| dadou | 20 |
| dadou | 20 |
+----------+------------+
4 rows
in
set
, 3 warnings (0.01 sec)
mysql> selectxingming,(age+dept)+10 from stu_test;
#在上面的基础上加10
+----------+---------------+
| xingming | (age+dept)+10 |
+----------+---------------+
| laowang | 38 |
| huasheng | 33 |
| dadou | 30 |
| dadou | 30 |
+----------+---------------+
4 rows
in
set
, 3 warnings (0.00 sec)
小结：
select
* from student where name=
'王五'
;
select
name,english from student;
select
distinct english from student;
select
name,(chinese+english+math) fromstudent;
select
name,(chinese+english+math)+10 fromstudent;
select
name as 姓名,(chinese+english+math)+10 as 总分 from student;
//
别名
select
name 姓名,(chinese+english+math)+10 总分 from student;
select
* from student whereenglish>
'90'
;
select
name from student where(chinese+english+math)>200;
select
name from student where(chinese+english+math)>
'200'
//
这里用单引号也是可以的
select
name from student whereenglish>80 and english<90;
select
name from student where Englishbetween 80 and 90;
//
这句跟上面那句是一样的
select
* from student where mathin(89,90,91);
|
4.2模糊查询
使用like 加上百分号来匹配
|
1
2
|
select
* from student where name like
'李_'
; （2个字符）
select
* from student where name like
'李%'
; (2个字符以上)
|
4.3排序查询
order by 排序查询
|
1
2
3
4
|
select
name,math from student order by math;
#默认是升序排序
select
name 姓名,(chinese+english+math) 总分 from student order by (chinese+english+math) desc;
#desc参数是降序
select
name 姓名,(chinese+english+math) 总分 from student order by 总分 desc;
select
* from student where name like
'李%'
order by (chinese+english+math) desc;
|
4.4统计数据
我们有时候要看一个表里面有多少条数据，需要统计
|
1
2
3
|
select
count(name) from student;
##查看name字段有多少条记录
select
count(*) from student;
select
count(*) from student where(chinese+english+math)>250;
|
4.5数据求和
求和
|
1
2
3
|
select
sum
(chinese),
sum
(english),
sum
(math) fromstudent;
select
sum
(chinese+english+math) from student;
|
4.6求平均
|
1
2
|
select
avg(chinese) from student;
select
avg(chinese+math+english) from student;
|
本文转自 kesungang 51CTO博客，原文链接：http://blog.51cto.com/sgk2011/1844109，如需转载请自行联系原作者
