【一】【SQL】表的增删查改（部分）

最新推荐文章于 2024-06-13 21:12:59 发布
最新推荐文章于 2024-06-13 21:12:59 发布
阅读量1.4k 收藏 25
点赞数 34
分类专栏： 数据库SQL 文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_74163972/article/details/136310628
版权

表之“增”操作

建表的操作

 

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> 

 

结尾
 

最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
 

 

同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
 

 

谢谢您的支持，期待与您在下一篇文章中再次相遇！
 


                

        

        

    




    

      

        

            

              
                
                  妖精七七_
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    34
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    25
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
                
  • 
                  
                    打赏
                    
                  
                  
    打赏
    
                
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                  1
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
关于SQL的增删查改

				
			

			

				

					yue_tuo的博客
				

				

					06-08
					
					539
					
				

			

		

		

			
				
开发工具与关键技术：SQL SQL增删查改
作者：李冰
撰写时间：2021年6月8日

1、	SELECT查询
SELECT +（你要查询的信息，可分为全部查询和部分查询）+ FROM +（你要查询的哪张表，可意为表名）
部分查询：（选择你想要查询的内容）
SELECT column_name,column_name
FROM table_name
全部查询：（使用星号（）来代替其他字段，SELECT语句会返回表的所有字段数据）
SELECT （）
FROM table_name
以下是 SQL SELEC

			
		

	



                

              
                



	

		

			

				
					
SQL语句增删查改

				
			

			

				

					qq_51955445的博客
				

				

					05-19
					
					101
					
				

			

		

		

			
				
/数据库的删除/
/DROP DATABASE test;/
/数据库的创建/
CREATE DATABASE IF NOT EXISTS test;
/展示所有数据库/
SHOW DATABASES;
/指定数据库/
USE test;
/创建表/
CREATE TABLE IF NOT EXISTS customer(
pk_id BIGINT PRIMARY KEY,/主键约束/
idx_name VARCHAR(10) UNIQUE,/唯一性约束/
idx_gender VARCHAR(10) NO

			
		

	



                


  
              

                


	

		

			

				
					
mysql表的增删查改

				
			

			

				

					m0_51641706的博客
				

				

					04-13
					
					1145
					
				

			

		

		

			
				
文章目录create（插入数据）单行数据全列插入多行数据全列插入指定列插入插入数据，如果是唯一性的数据且表中有重复数据，更新它replaceretrieveselectselect *select 指定列查询表达式select distinctselect + where英语不及格的同学及英语成绩 ( < 60 )语文成绩在 [80, 90] 分的同学及语文成绩数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩姓孙的同学名字两个字且姓孙的人语文成绩好于英语成绩的同学总分在 200

			
		

	





	

		

			

				
					
SQL之增删查改操作

				
			

			

				

					weixin_53344209的博客
				

				

					10-26
					
					1881
					
				

			

		

		

			
				
INSERT还可以用来将SELECT语句的结果插入表中，比如我们想将table2的数据插入到table1中，前提是这两个表的列结构要相同。NULL是默认的，如果要把一个列当主键，那么必须指定这列NOT NULL，主键不允许有空值。如果想要删除所有信息，用truncate table 表名更快，因为它不扫描表，不记录数据变动。表的更新和UPDATE更新不同，ALTER更新的是表的结构，比如增加删除列，增加删除约束，键等。使用ALTER应小心，表的修改是不能撤销的，比如数据丢失等情况会发生。

			
		

	



		

			
		



	

		

			

				
					
表的增删查改

				
			

			

				

					weixin_58867976的博客
				

				

					11-22
					
					809
					
				

			

		

		

			
				
表的增删查改详细解读

			
		

	





	

		

			

				
					
SQL数据库/表的增删查改

				
			

			

				

					weixin_46599926的博客
				

				

					02-02
					
					868
					
				

			

		

		

			
				
1. 数据库的增删查

基本语法

-- 创建数据库
CREATE DATABASE <数据库名称>;
-- 删除数据库
DROP DATABASE <数据库名称>;
-- 查询数据库
SHOW DATABASES;
-- 查询某个数据库的编码
SHOW CREATE DATABASE <数据库名称>;


例子

CREATE DATABASE datawhale;

2. 表的增删查改
2.1 创建表

基本语法

CREATE TABLE < 表名 >


			
		

	





	

		

			

				
					
SQL第一章、增删查改

				
			

			

				

					weixin_51782835的博客
				

				

					12-04
					
					921
					
				

			

		

		

			
				
执行不含有 WHERE 子句的 UPDATE 语句后，表 中所有老师的email均更新为“linghu.chong@lintcode.com”了，因此，执行没有 WHERE 子句的。如果我们只想知道有哪些不同的值，即希望查询的值都是唯一不重复的，我们该怎么办呢？3）数据查询语言（Data Query Language，DQL）：用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。为对应插入数据表中的值，每个值的属性需要与对应表中的列名属性相匹配，而且需要把插入的信息填写完整，否则会报错。

			
		

	





	

		

			

				
					
MySQL中表的增删查改操作(CRUD)

				
			

			

				

					trong_ 的烂笔头
				

				

					11-07
					
					3504
					
				

			

		

		

			
				
介绍MySQL中的对表的增,删,改,查操作(CRUD).

			
		

	





	

		

			

				
					
MySQL表的增删查改

				
			

			

				

					m0_61567378的博客
				

				

					10-09
					
					1899
					
				

			

		

		

			
				
LIKE:模糊匹配，%表示任意多个自读，_表示任意一个字符,后面只能跟一个字符。多个条件排序 ，优先按照前面的排序，相同按后面的排序，再相同再按后面的排序。把所有人的id和英语成绩查出来，把英语成绩+10分 ,没有影响原始数据。2 row affected :表中有冲突数据，不求额数据被更新。也可以包含一个不存在的字段，只能是一个可被计算的表达式。order by 是最后执行的，筛选数据是最先执行的。查询姓s的同学或者姓c的同学，结果按照数学成绩排序。先执行from emp，后按部门分组，再进行计算。

			
		

	





	

		

			

				
					
[SQL]增删查改语法概览

				
			

			

				

					Gremmie的博客
				

				

					02-27
					
					2295
					
				

			

		

		

			
				
本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引SQL的数据定义功能：定义数据库、基本表、视图和索引语法:CREATE DATABASE [IF NOT EXISTS] 定义数据库实际上定义了一个命名空间.在这个空间中可以定义各种数据库对象,例如基本表,视图,索引等如果没有IF NOT EXISTS选项,在创建一个已经存在的数据库时会出现报错语法:CREATE TABLE 

			
		

	





	

		

			

				
					
详解Java的MyBatis框架中SQL语句映射部分的编写

				
			

			

				

					09-02
				

			

		

		

			
				
主要介绍了Java的MyBatis框架中SQL语句映射部分的编写,文中分为resultMap和增删查改实现两个部分来讲解,需要的朋友可以参考下

			
		

	





	

		

			

				
					
python+django+sql学生信息管理后台开发

				
			

			

				

					12-25
				

			

		

		

			
				
实现对学生对个人信息的增删查改 实现后台对所有学生信息的操作  二、平台 windows+pycharm（python开发工具）  三、逻辑框图 四、代码展示 此后台采用的是MVC的风格模式进行编码 1.VIEWS部分  def hello(request):#...

			
		

	





	

		

			

				
					
基于SQL Server课程设计——人事管理系统

				
			

			

				

					01-11
				

			

		

		

			
				
该资源是基于SQL Server设计的企业管理上的一款人事管理系统，该系统包括五个部分：需求分析、概念结构和逻辑结构设计、数据库的设计、触发器设计、总结反思等，通过需求分析，实体...触发器从而实现对数据的增删查改。

			
		

	





	

		

			

				
					
MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解

				
			

			

				

					01-19
				

			

		

		

			
				
相关增删查改操作的性能？亦或是其他考虑？该理论到底有没有道理或者可行性，本文就个人的理解，做一个粗浅的分析。 1，基于存储的考虑 这里对存储的分析要清楚MySQL数据行的存储格式，这里直接从这篇文章白嫖一部分...

			
		

	





	

		

			

				
					
基于Python Tkinter的学生管理系统源码(怎删改查、点名等)+sql数据库+项目说明.zip

				
			

			

				

					09-01
				

			

		

		

			
				
根据市场上所具有的学生管理系统得，目前市面上的学生管理系统大多是具有增删查改等几部分内容，所面向的群体是中高校的教师群体，最主要的功能是教师方便管理学生的信息。而本设计是面向小学低年级学生的管理系统，...

			
		

	





	

		

			

				
					
Spring Security实现用户认证四：使用JWT与Redis实现无状态认证

				
			

			

				

					不做菜虚困的博客
				

				

					06-12
					
					667
					
				

			

		

		

			
				
在基本的通信流程中，我们一般采用Session去存储用户的认证状态。在Spring Security实现用户认证三中讲过，在拿到前端传输过来的用户名和密码之后，会有专门的过滤器处理这部分的需求，并且对认证成功的用户生成Token且存储在Session中。在下次发起请求时，直接从Session中取出同用户名的token进行密码哈希的比较要认证用户。对于无状态认证，则我们的认证不依赖与服务器端存储的Session的状态。所以无状态认证需要我们每次从前端传输一个包含完整认证信息的Token到服务器端进行自定义的认

			
		

	





	

		

			

				
					
【Qt实现录频】

					
最新发布

				
			

			

				

					m0_45463480的博客
				

				

					06-13
					
					53
					
				

			

		

		

			
				
请注意，这只是一个简单的示例代码，实际应用中你可能需要处理更多的细节，如捕获不同视频格式、处理录制过程中的错误等。希望这个示例对你有所帮助，另外你也可以在Qt官方文档中找到更多关于Qt Multimedia模块的详细信息。在Qt中实现录制视频可以通过使用Qt Multimedia模块来实现。在上面的示例中，VideoRecorder类提供了一个简单的界面，其中包括一个用于录制视频的按钮。当按钮被点击时，toggleRecord()槽函数将会启动或停止录制。

			
		

	





	

		

			

				
					
jmeter性能优化之mysql监控sql慢查询语句分析

				
			

			

				

					回家吃月饼的学习交流地
				

				

					06-09
					
					837
					
				

			

		

		

			
				
jmeter性能之sql慢查询语句分析

			
		

	





	

		

			

				
					
canal应用

				
			

			

				

					qq_33816292的博客
				

				

					06-12
					
					99
					
				

			

		

		

			
				
canal分享模块链接。

			
		

	





	

		

			

				
					
sql sever增删查改

				
			

			

				

					05-25
				

			

		

		

			
				
以下是SQL Server 增删查改的简单介绍：  1.增加（INSERT）：在SQL Server中，插入新记录可以使用INSERT INTO语句。例如，要向名为"employee"的表中插入新员工记录，可以使用以下语句：  ``` INSERT INTO employee ...

			
		

	



              



  
“相关推荐”对你有帮助么？

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		
评论 1

	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
妖精七七_

			
你的鼓励将是我创作的最大动力

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值