本节记录表内容的增删改查
查看表内容
SELECT ... FROM table
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(root@localhost) [book]> select * from student; 查看全部内容
+--------+----------+-----------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+-----------+---------+
| 1001 | 梨花 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
+--------+----------+-----------+---------+
2 rows in set (0.00 sec)
(root@localhost) [book]> select stu_id,stu_name from student; 查看指定内容
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1001 | 梨花 |
| 1002 | 百里 |
+--------+----------+
2 rows in set (0.00 sec)
(root@localhost) [book]> select * from student where stu_id=1001; 查看指定内容
+--------+----------+-----------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+-----------+---------+
| 1001 | 梨花 | 计算机 | M |
+--------+----------+-----------+---------+
1 row in set (0.00 sec)
(root@localhost) [book]> select * from student limit 2 offset 1; 限制行数,加偏移量
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 汉语 | F |
+--------+----------+--------------+---------+
2 rows in set (0.00 sec)SELECT column AS name FROM table 列名起别名
1
2
3
4
5
6
7
8
9(root@localhost) [book]> select stu_name as '姓名', major as '专业' from student;
+--------+--------------+
| 姓名 | 专业 |
+--------+--------------+
| 百里 | 土木 |
| 王虎 | 环境工程 |
| 柳江 | 汉语 |
+--------+--------------+
3 rows in set (0.00 sec)SELECT column CASE WHEN column='content' THEN name END 内容起别名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16(root@localhost) [book]> select stu_name as '姓名',
-> case
-> when major='土木' then 'TM'
-> when major='汉语' then 'HY'
-> when major='环境工程' then 'HJ'
-> end as '专业'
-> from student;
+-----------+--------+
| 姓名 | 专业 |
+-----------+--------+
| 百里 | TM |
| 王虎 | HJ |
| 柳江 | HY |
| 何凯文 | TM |
+-----------+--------+
4 rows in set (0.00 sec)SELECT DISTINCT column FROM table 不显示重复记录
1
2
3
4
5
6
7
8
9(root@localhost) [book]> select distinct major from student;
+--------------+
| major |
+--------------+
| 土木 |
| 环境工程 |
| 汉语 |
+--------------+
3 rows in set (0.01 sec)SELECT SUM(column) FROM table 求和
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16(root@localhost) [book]> select * from book;
+------+-----------+-----------+-------+
| b_id | b_name | b_author | price |
+------+-----------+-----------+-------+
| 1001 | 小时代 | 郭敬明 | 25.32 |
| 1002 | 活着 | 余华 | 25.72 |
+------+-----------+-----------+-------+
2 rows in set (0.00 sec)
(root@localhost) [book]> select sum(price) from book;
+------------+
| sum(price) |
+------------+
| 51.04 |
+------------+
1 row in set (0.02 sec)SELECT COUNT(column) FROM table 数量
1
2
3
4
5
6
7(root@localhost) [book]> select count(*) as '图书个数' from book;
+--------------+
| 图书个数 |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)SELECT MAX(column)|MIN(column) FROM table 求最大和最小值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16(root@localhost) [book]> select max(price) from book;
+------------+
| max(price) |
+------------+
| 25.72 |
+------------+
1 row in set (0.00 sec)
(root@localhost) [book]> select min(price) from book;
+------------+
| min(price) |
+------------+
| 25.32 |
+------------+
1 row in set (0.00 sec)SELECT ... FROM table WHERE column=(SELECT ... FROM table) 嵌套查询
1
2
3
4
5
6
7
8(root@localhost) [book]> select * from book
-> where price=(select max(price) from book);
+------+--------+----------+-------+
| b_id | b_name | b_author | price |
+------+--------+----------+-------+
| 1002 | 活着 | 余华 | 25.72 |
+------+--------+----------+-------+
1 row in set (0.01 sec)SELECT ... FROM table WHERE column LIKE ... 模糊查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23(root@localhost) [book]> select * from student where major like '%木%';
+--------+-----------+--------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+-----------+--------+---------+
| 1007 | 百里 | 土木 | F |
| 1013 | 何凯文 | 土木 | M |
+--------+-----------+--------+---------+
2 rows in set (0.01 sec)
(root@localhost) [book]> select * from book
-> where b_name like '%@_%'escape'@'; 声明转义符号
+------+---------+----------+--------+
| b_id | b_name | b_author | price |
+------+---------+----------+--------+
| 1004 | 活_着 | 余华 | 123.34 |
+------+---------+----------+--------+
1 row in set (0.01 sec)
% : 任意字符
_ : 一个字符
escape : 声明转义符号SELECT ... FROM table WHERE column REGEXP ... 正则表达式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16(root@localhost) [book]> select * from student where major regexp'管理|木|语';
+--------+-----------+--------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+-----------+--------+---------+
| 1007 | 百里 | 土木 | F |
| 1009 | 柳江 | 汉语 | F |
| 1013 | 何凯文 | 土木 | M |
+--------+-----------+--------+---------+
3 rows in set (0.00 sec)
| : 选择匹配
[] : 范围匹配
{} : 个数
^ : 开头
$ : 结尾SELECT ... FROM table WHERE column IN(...) 选择匹配
1
2
3
4
5
6
7
8
9
10(root@localhost) [book]> select * from student
-> where major in('土木', '汉语');
+--------+-----------+--------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+-----------+--------+---------+
| 1007 | 百里 | 土木 | F |
| 1009 | 柳江 | 汉语 | F |
| 1013 | 何凯文 | 土木 | M |
+--------+-----------+--------+---------+
3 rows in set (0.00 sec)SELECT ... FROM table WHERE column BETWEEN ... AND ... 范围匹配
1
2
3
4
5
6
7
8
9(root@localhost) [book]> select * from book
-> where price between 10 and 50;
+------+-----------+-----------+-------+
| b_id | b_name | b_author | price |
+------+-----------+-----------+-------+
| 1001 | 小时代 | 郭敬明 | 25.32 |
| 1002 | 活着 | 余华 | 25.72 |
+------+-----------+-----------+-------+
2 rows in set (0.01 sec)SELECT ... FROM table ORDER BY column
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20(root@localhost) [book]> select * from book order by price; 升序排列
+------+-----------+-----------+--------+
| b_id | b_name | b_author | price |
+------+-----------+-----------+--------+
| 1001 | 小时代 | 郭敬明 | 25.32 |
| 1002 | 活着 | 余华 | 25.72 |
| 1004 | 活_着 | 余华 | 123.34 |
+------+-----------+-----------+--------+
3 rows in set (0.00 sec)
(root@localhost) [book]> select * from book order by price desc; 降序排列
+------+-----------+-----------+--------+
| b_id | b_name | b_author | price |
+------+-----------+-----------+--------+
| 1004 | 活_着 | 余华 | 123.34 |
| 1002 | 活着 | 余华 | 25.72 |
| 1001 | 小时代 | 郭敬明 | 25.32 |
+------+-----------+-----------+--------+
3 rows in set (0.00 sec)SELECT ... FROM table GROUP BY column 分类汇总
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(root@localhost) [book]> select count(stu_id), major from student group by major;
+---------------+--------------+
| count(stu_id) | major |
+---------------+--------------+
| 2 | 土木 |
| 1 | 环境工程 |
| 1 | 汉语 |
+---------------+--------------+
3 rows in set (0.02 sec)
(root@localhost) [book]> select count(stu_id), major from student
-> group by major with rollup; with子句,总汇总
+---------------+--------------+
| count(stu_id) | major |
+---------------+--------------+
| 1 | 汉语 |
| 1 | 环境工程 |
| 2 | 土木 |
| 4 | NULL |
+---------------+--------------+
4 rows in set (0.00 sec)
(root@localhost) [book]> select count(stu_id), major from student
-> group by major having count(stu_id)=2; having子句,条件
+---------------+--------+
| count(stu_id) | major |
+---------------+--------+
| 2 | 土木 |
+---------------+--------+
1 row in set (0.01 sec)
添加表内容
INSERT INTO table VALUES 插入多行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21(root@localhost) [book]> insert into student 推荐使用
-> (stu_id, stu_name, major, stu_sex) values
-> (1001,'梨花','计算机',default),
-> (null,'百里','土木','F');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
(root@localhost) [book]> insert into student values 不安全
-> (null, '王虎', '环境工程', default);
Query OK, 1 row affected (0.01 sec)
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 梨花 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
+--------+----------+--------------+---------+
3 rows in set (0.00 sec)INSERT INTO table SET 插入一行
1
2
3
4
5
6
7
8
9
10
11
12
13
14(root@localhost) [book]> insert into student
-> set stu_name='柳江', major='日语', stu_sex='F';
Query OK, 1 row affected (0.01 sec)
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 梨花 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1005 | 柳江 | 日语 | F |
+--------+----------+--------------+---------+
4 rows in set (0.01 sec)CREATE TABLE table AS SELECT ... FROM table
1
2
3
4
5
6
7
8
9
10
11
12
13
14(root@localhost) [book]> create table student_copy as select * from student;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
(root@localhost) [book]> select * from student_copy;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 梨花 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1005 | 柳江 | 日语 | F |
+--------+----------+--------------+---------+
4 rows in set (0.00 sec)INSERT INTO table SELECT ... FROM table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19(root@localhost) [book]> insert into student(stu_name, major, stu_sex)
-> select stu_name, major, stu_sex from student_copy;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 梨花 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1005 | 柳江 | 日语 | F |
| 1006 | 梨花 | 计算机 | M |
| 1007 | 百里 | 土木 | F |
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 日语 | F |
+--------+----------+--------------+---------+
8 rows in set (0.00 sec)
修改表内容
REPLACE INTO table VALUES 根据主键替换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19(root@localhost) [book]> replace into student
-> (stu_id, stu_name, major, stu_sex) values
-> (1001, '李华', '计算机', default);
Query OK, 2 rows affected (0.01 sec)
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 李华 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1005 | 柳江 | 日语 | F |
| 1006 | 梨花 | 计算机 | M |
| 1007 | 百里 | 土木 | F |
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 日语 | F |
+--------+----------+--------------+---------+
8 rows in set (0.00 sec)UPDATE table SET ... WHERE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17(root@localhost) [book]> update student set major='汉语' where stu_id=1009;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 李华 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1005 | 柳江 | 日语 | F |
| 1007 | 百里 | 土木 | F |
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 汉语 | F |
+--------+----------+--------------+---------+
7 rows in set (0.00 sec)
删除表内容
DELETE FROM table WHERE
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(root@localhost) [book]> delete from student where stu_id=1006;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 李华 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1005 | 柳江 | 日语 | F |
| 1007 | 百里 | 土木 | F |
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 日语 | F |
+--------+----------+--------------+---------+
7 rows in set (0.00 sec)
(root@localhost) [book]> delete from student 使用AND关键字构成多条件
-> where stu_name='柳江' and major='日语';
Query OK, 1 row affected (0.00 sec)
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1001 | 李华 | 计算机 | M |
| 1002 | 百里 | 土木 | F |
| 1004 | 王虎 | 环境工程 | M |
| 1007 | 百里 | 土木 | F |
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 汉语 | F |
+--------+----------+--------------+---------+
6 rows in set (0.00 sec)DELETE FROM table,table USING table,table WHERE 关联表互相删除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22(root@localhost) [book]> delete from student,student_copy
-> using student,student_copy
-> where student.stu_id=student_copy.stu_id;
Query OK, 6 rows affected (0.01 sec)
(root@localhost) [book]> select * from student;
+--------+----------+--------------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------------+---------+
| 1007 | 百里 | 土木 | F |
| 1008 | 王虎 | 环境工程 | M |
| 1009 | 柳江 | 汉语 | F |
+--------+----------+--------------+---------+
3 rows in set (0.00 sec)
(root@localhost) [book]> select * from student_copy;
+--------+----------+--------+---------+
| stu_id | stu_name | major | stu_sex |
+--------+----------+--------+---------+
| 1005 | 柳江 | 日语 | F |
+--------+----------+--------+---------+
1 row in set (0.00 sec)DELETE FROM table 删除所有表内容,表还在
1
2
3
4
5(root@localhost) [book]> delete from student_copy;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [book]> select * from student_copy;
Empty set (0.00 sec)TRUNCATE table 删除所哟表内容,表还在
1
2
3
4
5(root@localhost) [book]> truncate student_copy;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [book]> select * from student_copy;
Empty set (0.00 sec)