表之“增”操作
建表的操作
mysql> create table students(
-> id int unsigned primary key auto_increment,
-> sn int unsigned unique key,
-> name varchar(20) not null,
-> qq varchar(32) unique key
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sn | int(10) unsigned | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| qq | varchar(32) | YES | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)指定列插入
mysql> insert into students (sn,name,qq) values (123,'张飞','4567890');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+------+--------+---------+
| id | sn | name | qq |
+----+------+--------+---------+
| 1 | 123 | 张飞 | 4567890 |
+----+------+--------+---------+
1 row in set (0.00 sec)
全列插入
mysql> insert into students values (10,124,'关羽','123456');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+------+--------+---------+
| id | sn | name | qq |
+----+------+--------+---------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
+----+------+--------+---------+
2 rows in set (0.00 sec)
主键约束展示
mysql> insert into students values (10,124,'关羽','123456');
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> insert into students values (11,125,'刘备','123459990');
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+----+------+--------+-----------+
| id | sn | name | qq |
+----+------+--------+-----------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
+----+------+--------+-----------+
3 rows in set (0.00 sec)
mysql>
多列插入---全列
mysql> insert into students values (13,127,'许攸','1234545656'),(14,128,'许褚','1123334455'),(15,129,'诸葛亮','32111234343');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 127 | 许攸 | 1234545656 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
+----+------+-----------+-------------+
6 rows in set (0.00 sec)
mysql>
多列插入---指定列
mysql> insert into students (sn,name,qq) values (130,'孙权','64533764'),(131,'吕布','4232455');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 127 | 许攸 | 1234545656 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
+----+------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
表之“改”操作
键冲突时修改数据（冲突键不变）
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 127 | 许攸 | 1234545656 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
+----+------+-----------+-------------+
8 rows in set (0.00 sec)
mysql> insert into students values (13,128,'xuyou','11111111');
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> insert into students values (13,132,'xuyou','11111111') on duplicate key update sn=132,name='xuyou',qq='11111111';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
+----+------+-----------+-------------+
8 rows in set (0.00 sec)
mysql> insert into students values (18,134,'貂蝉','111223234') on duplicate key update sn=134,name='貂蝉',qq='111223234';
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values (18,134,'貂蝉','111223234') on duplicate key update sn=134,name='貂蝉',qq='111223234';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 111223234 |
+----+------+-----------+-------------+
9 rows in set (0.00 sec)
mysql> insert into students values (18,134,'貂蝉','111223234') on duplicate key update sn=134,name='貂蝉',qq='1112235554';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
+----+------+-----------+-------------+
9 rows in set (0.00 sec)
mysql>
-- 0 row affected :表中有冲突数据，但冲突数据的值和update的值相等
-- 1 row affected:表中没有冲突数据，数据被插入
-- 2 row affected:表中有冲突数据，并且数据已经被更新
键冲突时修改数据（冲突键会变）
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
+----+------+-----------+-------------+
9 rows in set (0.00 sec)
mysql> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sn | int(10) unsigned | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| qq | varchar(32) | YES | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> replace into students (sn,name,qq)values(140,'许攸','31213554');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
| 19 | 140 | 许攸 | 31213554 |
+----+------+-----------+-------------+
10 rows in set (0.00 sec)
mysql> replace into students (sn,name,qq)values(140,'许攸','31213554');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
| 20 | 140 | 许攸 | 31213554 |
+----+------+-----------+-------------+
10 rows in set (0.00 sec)
mysql> replace into students (sn,name,qq)values(140,'许攸','31213554');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
| 21 | 140 | 许攸 | 31213554 |
+----+------+-----------+-------------+
10 rows in set (0.00 sec)
mysql>
主键或唯一键没有冲突，则直接插入。
主键或唯一键有冲突，则删除后插入。
1 row affected：表中没有冲突数据，数据被插入。
2 row affectede：表中有冲突数据，删除后重新插入。
update
将孙悟空同学的数学成绩变更为80分
mysql> select name ,math from exam_result ;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
mysql> update exam_result set math=80 where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name ,math from exam_result ;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 80 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
mysql>
将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分
mysql> select * from exam_result ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.01 sec)
mysql> update exam_result set math=60 ,chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result ;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 60 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql>
将总成绩倒数前三的3位同学的数学成绩加上30分
mysql> select name , math+english+chinese total from exam_result ;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 244 |
| 猪悟能 | 276 |
| 曹孟德 | 197 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql> select name , math+english+chinese total from exam_result order by total ;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 曹孟德 | 197 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 孙悟空 | 244 |
| 猪悟能 | 276 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql> select name , math+english+chinese total from exam_result order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 244 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 197 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql> select name , math+english+chinese total from exam_result order by total desc limit 0,3;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 244 |
| 唐三藏 | 221 |
+-----------+-------+
3 rows in set (0.00 sec)
mysql> select name , math+english+chinese total from exam_result order by total limit 3;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 曹孟德 | 197 |
+-----------+-------+
3 rows in set (0.00 sec)
mysql> update exam_result set math=math+30 order by chinese+english+math asc limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select name , math+english+chinese total from exam_result order by total limit 3;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 200 |
| 刘玄德 | 215 |
| 唐三藏 | 221 |
+-----------+-------+
3 rows in set (0.00 sec)
mysql> select name ,math+chinese+english total from exam_result order by total;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 200 |
| 刘玄德 | 215 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 227 |
| 孙悟空 | 244 |
| 猪悟能 | 276 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql>
将所有同学的语文成绩更新为原来的2倍
mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 刘玄德 | 55 | 115 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 95 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> update exam_result set chinese=chinese*2 ;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孙悟空 | 174 | 80 | 77 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> 表之“查”操作（部分）
全列查询
mysql> select * from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
| 21 | 140 | 许攸 | 31213554 |
+----+------+-----------+-------------+
10 rows in set (0.00 sec)
指定列查询
mysql> select id from students;
+----+
| id |
+----+
| 1 |
| 10 |
| 11 |
| 14 |
| 15 |
| 16 |
| 17 |
| 13 |
| 18 |
| 21 |
+----+
10 rows in set (0.00 sec)
mysql> select id,sn from students;
+----+------+
| id | sn |
+----+------+
| 1 | 123 |
| 10 | 124 |
| 11 | 125 |
| 14 | 128 |
| 15 | 129 |
| 16 | 130 |
| 17 | 131 |
| 13 | 132 |
| 18 | 134 |
| 21 | 140 |
+----+------+
10 rows in set (0.00 sec)
mysql> select id,sn,name from students;
+----+------+-----------+
| id | sn | name |
+----+------+-----------+
| 1 | 123 | 张飞 |
| 10 | 124 | 关羽 |
| 11 | 125 | 刘备 |
| 13 | 132 | xuyou |
| 14 | 128 | 许褚 |
| 15 | 129 | 诸葛亮 |
| 16 | 130 | 孙权 |
| 17 | 131 | 吕布 |
| 18 | 134 | 貂蝉 |
| 21 | 140 | 许攸 |
+----+------+-----------+
10 rows in set (0.00 sec)
mysql> select id,sn,name,qq from students;
+----+------+-----------+-------------+
| id | sn | name | qq |
+----+------+-----------+-------------+
| 1 | 123 | 张飞 | 4567890 |
| 10 | 124 | 关羽 | 123456 |
| 11 | 125 | 刘备 | 123459990 |
| 13 | 132 | xuyou | 11111111 |
| 14 | 128 | 许褚 | 1123334455 |
| 15 | 129 | 诸葛亮 | 32111234343 |
| 16 | 130 | 孙权 | 64533764 |
| 17 | 131 | 吕布 | 4232455 |
| 18 | 134 | 貂蝉 | 1112235554 |
| 21 | 140 | 许攸 | 31213554 |
+----+------+-----------+-------------+
10 rows in set (0.00 sec)
mysql>
表达式查询
示例展示
mysql> create table exam_result(
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null comment '同学姓名',
-> chinese float default 0.0 comment '语文成绩',
-> math float default 0.0 comment '数学成绩',
-> english float default 0.0 comment '英语乘积'
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc exam_result;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| chinese | float | YES | | 0 | |
| math | float | YES | | 0 | |
| english | float | YES | | 0 | |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into exam_result (name ,chinese,math,english)values
-> ('唐三藏',67,98,56),
-> ('孙悟空',87,78,77),
-> ('猪悟能',88,98,90),
-> ('曹孟德',82,84,67),
-> ('刘玄德',55,85,45),
-> ('孙权',70,73,78),
-> ('宋公明',75,65,30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> 表达式查询
mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
mysql> select 7+8;
+-----+
| 7+8 |
+-----+
| 15 |
+-----+
1 row in set (0.00 sec)
mysql> select name,math,10 from exam_result;
+-----------+------+----+
| name | math | 10 |
+-----------+------+----+
| 唐三藏 | 98 | 10 |
| 孙悟空 | 78 | 10 |
| 猪悟能 | 98 | 10 |
| 曹孟德 | 84 | 10 |
| 刘玄德 | 85 | 10 |
| 孙权 | 73 | 10 |
| 宋公明 | 65 | 10 |
+-----------+------+----+
7 rows in set (0.00 sec)
mysql> select name,math,1+1 from exam_result;
+-----------+------+-----+
| name | math | 1+1 |
+-----------+------+-----+
| 唐三藏 | 98 | 2 |
| 孙悟空 | 78 | 2 |
| 猪悟能 | 98 | 2 |
| 曹孟德 | 84 | 2 |
| 刘玄德 | 85 | 2 |
| 孙权 | 73 | 2 |
| 宋公明 | 65 | 2 |
+-----------+------+-----+
7 rows in set (0.00 sec)
mysql> select name,math,math+chinese+english from exam_result;
+-----------+------+----------------------+
| name | math | math+chinese+english |
+-----------+------+----------------------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+----------------------+
7 rows in set (0.00 sec)
mysql> select name,math,math+chinese+english as total from exam_result;
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+-------+
7 rows in set (0.00 sec)
mysql> select name,math,math+chinese+english total from exam_result;
+-----------+------+-------+
| name | math | total |
+-----------+------+-------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+------+-------+
7 rows in set (0.00 sec)
mysql> select name 姓名 ,math 数学,math+chinese+english 总分 from exam_result;
+-----------+--------+--------+
| 姓名 | 数学 | 总分 |
+-----------+--------+--------+
| 唐三藏 | 98 | 221 |
| 孙悟空 | 78 | 242 |
| 猪悟能 | 98 | 276 |
| 曹孟德 | 84 | 233 |
| 刘玄德 | 85 | 185 |
| 孙权 | 73 | 221 |
| 宋公明 | 65 | 170 |
+-----------+--------+--------+
7 rows in set (0.00 sec)
mysql>
去重查询（distinct）
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
7 rows in set (0.00 sec)
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
6 rows in set (0.00 sec)
mysql>
WHERE条件
| 运算符 | 说明 |
| >,>=,<,<= | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL不安全，例如NULL=NULL的结果是NULL |
| <=> | 等于，NULL安全，例如NULL<=>NULL的结果是TRUE（1） |
| !=,<> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配，[a0,a1],如果a0<=value<=a1,返回TRUE（1） |
| IN（option,...） | 如果是option中的任意一个，返回TRUE（1） |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| LIKE | 模糊匹配。%表示匹配多个（包括0个）任意字符；_表示任意一个字符 |
| AND | 多个条件必须为TRUE（1），结果为TRUE（1） |
| OR | 任意一个条件为TRUE（1），结果为TRUE（1） |
| NOT | 条件为TRUE（1），结果为FALSE(0) |
mysql> select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> select 0;
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql> select NULL=NULL;
+-----------+
| NULL=NULL |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> select null<=>null
-> ;
+-------------+
| null<=>null |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select 1=1
-> ;
+-----+
| 1=1 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> select 2=2;
+-----+
| 2=2 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> select 2=1;
+-----+
| 2=1 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> select 2=null;
+--------+
| 2=null |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> select 1<=>1;
+-------+
| 1<=>1 |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select 1<=>0;
+-------+
| 1<=>0 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql> select 1!=1;
+------+
| 1!=1 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select 1!=2;
+------+
| 1!=2 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql>
条件查询
mysql> select name , english from exam_result ;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 孙悟空 | 77 |
| 猪悟能 | 90 |
| 曹孟德 | 67 |
| 刘玄德 | 45 |
| 孙权 | 78 |
| 宋公明 | 30 |
+-----------+---------+
7 rows in set (0.00 sec)
mysql> select name , english from exam_result where english<60;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
+-----------+---------+
3 rows in set (0.00 sec)
mysql>
where条件查询案例
语文成绩在[80,90]分的同学及语文成绩
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select name,chinese from exam_result;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
| 刘玄德 | 55 |
| 孙权 | 70 |
| 宋公明 | 75 |
+-----------+---------+
7 rows in set (0.00 sec)
mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
mysql> select name,chinese from exam_result where chinese between 80 and 90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
mysql>
数学成绩是58或59或98或99的同学及数学成绩
mysql> select name ,math from exam_result;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
mysql> select name ,math from exam_result where math=58 or math =59 or math =98 or math =99;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
2 rows in set (0.00 sec)
mysql> select name ,math from exam_result where math in(58,59,98,99);
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
2 rows in set (0.00 sec)
mysql>
姓孙的同学及孙某同学
mysql> select name from exam_result where name like '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
2 rows in set (0.00 sec)
mysql> select name from exam_result where name like '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+
1 row in set (0.00 sec)
mysql>
语文成绩好于英语成绩的同学
mysql> select name ,chinese,english from exam_result where chinese>english;
+-----------+---------+---------+
| name | chinese | english |
+-----------+---------+---------+
| 唐三藏 | 67 | 56 |
| 孙悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 刘玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)
mysql>
总分在200分以下的同学
mysql> select name ,chinese+math+english from exam_result;
+-----------+----------------------+
| name | chinese+math+english |
+-----------+----------------------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+----------------------+
7 rows in set (0.00 sec)
mysql> select name ,chinese+math+english from exam_result where chinese+math+english<200;
+-----------+----------------------+
| name | chinese+math+english |
+-----------+----------------------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+----------------------+
2 rows in set (0.00 sec)
mysql> select name ,chinese+math+english total from exam_result ;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql>
mysql语句执行顺序
mysql> select name ,chinese+math+english total from exam_result where total<200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
mysql> select name ,chinese+math+english total from exam_result where chinese+math+english<200;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> select name ,chinese+math+english as total from exam_result where chinese+math+english<200;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> 
首先要知道在哪一个表中做操作，第二步是条件判断，前两步得出的是符合条件的表，第三步在得到的符合条件的表中选择列数据。在执行第二步的时候，并不知道total是什么。
语文成绩>80并且不姓孙的同学
mysql> select name ,chinese from exam_result where chinese>80;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
mysql> select name ,chinese from exam_result where name like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 孙权 | 70 |
+-----------+---------+
2 rows in set (0.00 sec)
mysql> select name ,chinese from exam_result where name not like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
| 刘玄德 | 55 |
| 宋公明 | 75 |
+-----------+---------+
5 rows in set (0.00 sec)
mysql> select name ,chinese from exam_result where chinese>80 and name not like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
2 rows in set (0.00 sec)
mysql>
孙某同学，否则要求总成绩>200并且语文成绩<数学成绩并且英语成绩>80
mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 唐三藏 | 67 | 98 | 56 | 221 |
| 孙悟空 | 87 | 78 | 77 | 242 |
| 猪悟能 | 88 | 98 | 90 | 276 |
| 曹孟德 | 82 | 84 | 67 | 233 |
| 刘玄德 | 55 | 85 | 45 | 185 |
| 孙权 | 70 | 73 | 78 | 221 |
| 宋公明 | 75 | 65 | 30 | 170 |
+-----------+---------+------+---------+--------+
7 rows in set (0.00 sec)
mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where chinese +math+english >200;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 唐三藏 | 67 | 98 | 56 | 221 |
| 孙悟空 | 87 | 78 | 77 | 242 |
| 猪悟能 | 88 | 98 | 90 | 276 |
| 曹孟德 | 82 | 84 | 67 | 233 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
5 rows in set (0.00 sec)
mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where chinese +math+english >200 and chinese <math;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 唐三藏 | 67 | 98 | 56 | 221 |
| 猪悟能 | 88 | 98 | 90 | 276 |
| 曹孟德 | 82 | 84 | 67 | 233 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
4 rows in set (0.00 sec)
mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where chinese +math+english >200 and chinese <math and english>80;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
+-----------+---------+------+---------+--------+
1 row in set (0.00 sec)
mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where name like '孙_';
+--------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+--------+---------+------+---------+--------+
| 孙权 | 70 | 73 | 78 | 221 |
+--------+---------+------+---------+--------+
1 row in set (0.00 sec)
mysql> select name ,chinese ,math,english ,chinese+math+english 总分 from exam_result where name like '孙_' or chinese +math+english >200 and chinese <math and english>80
0;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)
mysql>
NULL和''的区别
mysql> create table test_null(
-> id int,
-> name varchar (20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into test_null (id,name) values (1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_null (id,name) values (null,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_null (id,name) values (1,null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_null (id,name) values (null,null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_null (id,name) values (1,'');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_null;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | NULL |
| NULL | NULL |
| 1 | |
+------+--------+
5 rows in set (0.00 sec)
mysql> select * from test_null where name is null;
+------+------+
| id | name |
+------+------+
| 1 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test_null where name = '';
+------+------+
| id | name |
+------+------+
| 1 | |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test_null where name is not null;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | |
+------+--------+
3 rows in set (0.00 sec)
mysql>
Order by
asc升序
desc降序
默认升序
同学及数学成绩，按照数学成绩升序显示
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select name ,math from exam_result order by math asc;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
mysql>
NULL的排序
mysql> select * from test_null;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | NULL |
| NULL | NULL |
| 1 | |
+------+--------+
5 rows in set (0.00 sec)
mysql> select * from test_null order by name asc;
+------+--------+
| id | name |
+------+--------+
| 1 | NULL |
| NULL | NULL |
| 1 | |
| 1 | 张三 |
| NULL | 张三 |
+------+--------+
5 rows in set (0.00 sec)
mysql> select * from test_null order by name desc;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | |
| 1 | NULL |
| NULL | NULL |
+------+--------+
5 rows in set (0.00 sec)
mysql>
NULL在排序中视作为比任何值都要小。
查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示
mysql> select name ,math,english,chinese from exam_result;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 孙悟空 | 78 | 77 | 87 |
| 猪悟能 | 98 | 90 | 88 |
| 曹孟德 | 84 | 67 | 82 |
| 刘玄德 | 85 | 45 | 55 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name ,math,english,chinese from exam_result order by math;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 宋公明 | 65 | 30 | 75 |
| 孙权 | 73 | 78 | 70 |
| 孙悟空 | 78 | 77 | 87 |
| 曹孟德 | 84 | 67 | 82 |
| 刘玄德 | 85 | 45 | 55 |
| 唐三藏 | 98 | 56 | 67 |
| 猪悟能 | 98 | 90 | 88 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name ,math,english,chinese from exam_result order by math desc;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 猪悟能 | 98 | 90 | 88 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name ,math,english,chinese from exam_result order by math desc,english desc;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name ,math,english,chinese from exam_result order by math desc,english desc,chinese;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name ,math,english,chinese from exam_result order by math desc,english desc,chinese asc;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql>
默认的排序
默认升序
mysql> select name,math from exam_result;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
mysql> select name,math from exam_result order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
| 刘玄德 | 85 |
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
mysql> select name,math from exam_result order by math asc;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
mysql> select name,math from exam_result order by math ;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
mysql>
查询同学及总分，由高到低（mysql语句执行顺序）
mysql> select name ,math+english+chinese from exam_result;
+-----------+----------------------+
| name | math+english+chinese |
+-----------+----------------------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+----------------------+
7 rows in set (0.00 sec)
mysql> select name ,math+english+chinese as total from exam_result;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.01 sec)
mysql> select name ,math+english+chinese as total from exam_result order by total;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql> select name ,math+english+chinese as total from exam_result order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql>
查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
mysql> select name ,math from exam_result where name like '孙%' or name like '曹%';
+-----------+------+
| name | math |
+-----------+------+
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 孙权 | 73 |
+-----------+------+
3 rows in set (0.00 sec)
mysql> select name ,math from exam_result where name like '孙%' or name like '曹%' order by math;
+-----------+------+
| name | math |
+-----------+------+
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
+-----------+------+
3 rows in set (0.00 sec)
mysql> select name ,math from exam_result where name like '孙%' or name like '曹%' order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 1;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 5 offset 0;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql>
筛选分页
limit的使用
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select * from exam_result limit 5;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql> select * from exam_result limit 1;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select * from exam_result limit 2;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from exam_result limit 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 1,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 2,4;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
mysql> select * from exam_result limit 2,1;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select * from exam_result limit 2,2;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from exam_result limit 2,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 2,4;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
mysql> select * from exam_result limit 0,4;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
mysql>分页的具体操作
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select * from exam_result limit 0,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 6,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 0;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql>
同学名字及总分，总分大于200分，总分降序排序，分页操作
mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+-------+
5 rows in set (0.00 sec)
mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
+-----------+-------+
5 rows in set (0.01 sec)
mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 2 offset 0;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 2 offset 2;
+-----------+-------+
| name | total |
+-----------+-------+
| 曹孟德 | 233 |
| 唐三藏 | 221 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> select name ,math+english+chinese total from exam_result where english+math+chinese >200 order by total desc limit 2 offset 4;
+--------+-------+
| name | total |
+--------+-------+
| 孙权 | 221 |
+--------+-------+
1 row in set (0.00 sec)
mysql>
表之“删”操作
删表
mysql> create table t1( id int, name varchar(20) );
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| exam_result |
| students |
| t1 |
+--------------+
3 rows in set (0.00 sec)
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| exam_result |
| students |
+--------------+
2 rows in set (0.00 sec)
mysql>
Delete
删除孙悟空同学的考试成绩
mysql> select *from exam_result where name='孙悟空';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 174 | 80 | 77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.00 sec)
mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)
mysql> select name ,chinese +math+english total from exam_result ;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 288 |
| 猪悟能 | 364 |
| 曹孟德 | 297 |
| 刘玄德 | 270 |
| 孙权 | 291 |
| 宋公明 | 275 |
+-----------+-------+
6 rows in set (0.00 sec)
mysql> select name ,chinese +math+english total from exam_result order by total;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 270 |
| 宋公明 | 275 |
| 唐三藏 | 288 |
| 孙权 | 291 |
| 曹孟德 | 297 |
| 猪悟能 | 364 |
+-----------+-------+
6 rows in set (0.00 sec)
mysql> select name ,chinese +math+english total from exam_result order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| 猪悟能 | 364 |
| 曹孟德 | 297 |
| 孙权 | 291 |
| 唐三藏 | 288 |
| 宋公明 | 275 |
| 刘玄德 | 270 |
+-----------+-------+
6 rows in set (0.01 sec)
mysql> select name ,chinese +math+english total from exam_result order by total asc;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 270 |
| 宋公明 | 275 |
| 唐三藏 | 288 |
| 孙权 | 291 |
| 曹孟德 | 297 |
| 猪悟能 | 364 |
+-----------+-------+
6 rows in set (0.00 sec)
mysql> select name ,chinese +math+english total from exam_result order by total asc limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 270 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> delete from exam_result order by english +math+chinese asc limit 1;
Query OK, 1 row affected (0.00 sec)
mysql> select name ,chinese+math+english total from exam_result order by total asc limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 275 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql>
删除整个表
mysql> create table for_delete(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into for_delete (name) value ('A'),('B'),('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show CREATE table for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)
mysql> select *from for_delete;
Empty set (0.00 sec)
mysql> show create table for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into for_delete (name) value ('E');
Query OK, 1 row affected (0.00 sec)
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 4 | E |
+----+------+
1 row in set (0.00 sec)
mysql>
截断表
无法回滚，会重置auto_increment项。
mysql> create table for_truncate(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc for_truncate;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into for_truncate (name) value('A'),('B'),('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
mysql> show create table for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> truncate for_truncate;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| exam_result |
| for_delete |
| for_truncate |
| students |
| test_null |
+--------------+
5 rows in set (0.00 sec)
mysql> select * from for_truncate;
Empty set (0.00 sec)
mysql> show create table for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into for_truncate (name) values ('E');
Query OK, 1 row affected (0.01 sec)
mysql> select *from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | E |
+----+------+
1 row in set (0.01 sec)
mysql> show create table for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
结尾
最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
谢谢您的支持，期待与您在下一篇文章中再次相遇！
![[NOIP2011 普及组] 数字反转](https://img-blog.csdnimg.cn/direct/29abc83666914b5f8579b24fc746fbc9.png)
