MySQL数据库——表的CRUD

CRUD：Create（创建）、Retrieve（查询）、Update（更新）、Delete（删除）

一、Create

表的创建语法在前面的博客中已经讲过，这里我们直接创建一张学生表

mysql> create table student(
    -> id int unsigned primary key auto_increment,
    -> name varchar(32) not null,
    -> sex varchar(2) default '男'
    -> );
Query OK, 0 rows affected (0.35 sec)

接下来我们为表中插入数据：

1.1 单行插入

例如：插入1：id 为 100，name 为 赵一，男      

                  2：id 为 101，name 为 钱二，女

mysql> insert into student  values (100,'赵一','男');
Query OK, 1 row affected (0.04 sec)

mysql> insert into student (id,name,sex) values (101,'钱二','女');
Query OK, 1 row affected (0.03 sec)

mysql> select * from student;
+-----+------+------+
| id  | name | sex  |
+-----+------+------+
| 100 | 赵一 | 男   |
| 101 | 钱二 | 女   |
+-----+------+------+
2 rows in set (0.00 sec)

1.2 多行插入

例如：插入3：id 为 102，name 为 孙三，男

                  4：id 为 103，name 为 李四，女

mysql> insert into student (id,name,sex) values (102,'孙三','男'),(103,'李四','女');
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-----+------+------+
| id  | name | sex  |
+-----+------+------+
| 100 | 赵一 | 男   |
| 101 | 钱二 | 女   |
| 102 | 孙三 | 男   |
| 103 | 李四 | 女   |
+-----+------+------+
4 rows in set (0.00 sec)

1.3 插入新字段

​alter table 表名 add 字段名 字段类型 约束 ...;​

这里我们插入一组新的字段（sno）在 id 之后，并用唯一键约束

mysql> alter table student add sno int unsigned unique after id;
Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-----+------+------+------+
| id  | sno  | name | sex  |
+-----+------+------+------+
| 100 | NULL | 赵一 | 男   |
| 101 | NULL | 钱二 | 女   |
| 102 | NULL | 孙三 | 男   |
| 103 | NULL | 李四 | 女   |
+-----+------+------+------+
4 rows in set (0.00 sec)

二、Retrieve

为了查询方便，先建一个数据表

mysql> select * from grade;
+-----+------+---------+------+---------+
| id  | name | chinese | math | english |
+-----+------+---------+------+---------+
| 101 | 张三 |      78 |   88 |      90 |
| 102 | 李四 |      79 |   82 |      90 |
| 103 | 王五 |      88 |   76 |     100 |
| 104 | 王六 |      77 |   88 |      99 |
| 105 | 周七 |      30 |   67 |      90 |
| 106 | 李八 |      40 |   88 |      70 |
+-----+------+---------+------+---------+
6 rows in set (0.00 sec)

2.1 全列查询

select * from grade;

2.2 指定列查询

2.2.1 指定已有列查询

select id,name,math from grade;

mysql> select id,name,math from grade;
+-----+------+------+
| id  | name | math |
+-----+------+------+
| 101 | 张三 |   88 |
| 102 | 李四 |   82 |
| 103 | 王五 |   76 |
| 104 | 王六 |   88 |
| 105 | 周七 |   67 |
| 106 | 李八 |   88 |
+-----+------+------+
6 rows in set (0.00 sec)

2.2.2 查询字段为表达式

  表达式不包含字段：

mysql> select id,name,10 from grade;
+-----+------+----+
| id  | name | 10 |
+-----+------+----+
| 101 | 张三 | 10 |
| 102 | 李四 | 10 |
| 103 | 王五 | 10 |
| 104 | 王六 | 10 |
| 105 | 周七 | 10 |
| 106 | 李八 | 10 |
+-----+------+----+
6 rows in set (0.00 sec)

  表达式包含一个字段：

mysql> select id,name,10+english from grade;
+-----+------+------------+
| id  | name | 10+english |
+-----+------+------------+
| 101 | 张三 |        100 |
| 102 | 李四 |        100 |
| 103 | 王五 |        110 |
| 104 | 王六 |        109 |
| 105 | 周七 |        100 |
| 106 | 李八 |         80 |
+-----+------+------------+
6 rows in set (0.10 sec)

   表达式包含多个字段：

mysql> select id,name,chinese+math+english from grade;
+-----+------+----------------------+
| id  | name | chinese+math+english |
+-----+------+----------------------+
| 101 | 张三 |                  256 |
| 102 | 李四 |                  251 |
| 103 | 王五 |                  264 |
| 104 | 王六 |                  264 |
| 105 | 周七 |                  187 |
| 106 | 李八 |                  198 |
+-----+------+----------------------+
6 rows in set (0.00 sec)

2.2.3 为查询结果指定别名

select column [as] alias_name [...] from table_name;

mysql> select id,name,chinese+math+english  总分 from grade;
+-----+------+------+
| id  | name | 总分 |
+-----+------+------+
| 101 | 张三 |  256 |
| 102 | 李四 |  251 |
| 103 | 王五 |  264 |
| 104 | 王六 |  264 |
| 105 | 周七 |  187 |
| 106 | 李八 |  198 |
+-----+------+------+
6 rows in set (0.00 sec)

2.2.4 结果去重

select distinct 字段名 from 表名;

mysql> select math from grade;
+------+
| math |
+------+
|   88 |
|   82 |
|   76 |
|   88 |
|   67 |
|   88 |
+------+
6 rows in set (0.00 sec)

mysql> select distinct math from grade;
+------+
| math |
+------+
|   88 |
|   82 |
|   76 |
|   67 |
+------+
4 rows in set (0.00 sec)

2.3 where 条件 

  2.3.1 查找 chinese 不及格的同学name 及成绩

mysql> select name, chinese from grade where chinese < 60;
+------+---------+
| name | chinese |
+------+---------+
| 周七 |      30 |
| 李八 |      40 |
+------+---------+
2 rows in set (0.00 sec)

2.3.2  查找 math 在70 到 80 之间的同学及成绩[70,80]

----使用 and 连接两个条件----

mysql> select name, math from grade where (math >70 and math <80);
+------+------+
| name | math |
+------+------+
| 王五 |   76 |
+------+------+
1 row in set (0.00 sec)

----使用 between...and 连接----

mysql> select name, math from grade where (math between 70 and 80);
+------+------+
| name | math |
+------+------+
| 王五 |   76 |
+------+------+
1 row in set (0.00 sec)

2.3.3  查找 english 是89 或者 90 或者 99 或者 100 分的同学及成绩

----使用 or 进行条件连接----

mysql> select name,english from grade where (english = 89 or
    -> english = 90 or english = 99 or english = 100);
+------+---------+
| name | english |
+------+---------+
| 张三 |      90 |
| 李四 |      90 |
| 王五 |     100 |
| 王六 |      99 |
| 周七 |      90 |
+------+---------+
5 rows in set (0.00 sec)

----使用 in 条件----

mysql> select name,english from grade where english in(89,90,99,100);
+------+---------+
| name | english |
+------+---------+
| 张三 |      90 |
| 李四 |      90 |
| 王五 |     100 |
| 王六 |      99 |
| 周七 |      90 |
+------+---------+
5 rows in set (0.00 sec)

2.3.4 姓王的同学 及 王某 同学

mysql> select name from grade where name like '王%';
+------+
| name |
+------+
| 王五 |
| 王六 |
+------+
2 rows in set (0.00 sec)

2.3.5 chinese成绩 大于80 并且 不姓 李 的同学

mysql> select name,chinese from grade where (chinese > 80 and name not like '李%');
+------+---------+
| name | chinese |
+------+---------+
| 王五 |      88 |
+------+---------+
1 row in set (0.00 sec)

2.3.6 查询总分大于200，english 成绩大于math 并且 chinese 大于 80的 同学

mysql> select name,chinese,math,english,chinese+math+english 总分 from grade where(
    -> chinese+math+english > 200 and english > math and chinese > 80);
+------+---------+------+---------+------+
| name | chinese | math | english | 总分 |
+------+---------+------+---------+------+
| 王五 |      88 |   76 |     100 |  264 |
+------+---------+------+---------+------+
1 row in set (0.00 sec)

三、Update

语法：

update table_name set column = expr [, column = expr ...] [where ...] [order by ...] [limit ...]

例如：

mysql> select name,chinese from grade where name = '张三';
+------+---------+
| name | chinese |
+------+---------+
| 张三 |      78 |
+------+---------+
1 row in set (0.00 sec)

将张三的 chinese 成绩改为 100分

mysql> update grade set chinese = 100 where name = '张三';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name,chinese from grade where name = '张三';
+------+---------+
| name | chinese |
+------+---------+
| 张三 |     100 |
+------+---------+
1 row in set (0.00 sec)

将 李四 的 chinese 改为 66，math 改为 77 ，english 改为 88

----原数据----

mysql> select name,chinese,math,english from grade where name = '李四';
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 李四 |      79 |   82 |      90 |
+------+---------+------+---------+
1 row in set (0.00 sec)

----更新数据----

mysql> update grade set chinese = 66,math = 77,english = 88 where name = '李四';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

----更新后的数据----

mysql> select name,chinese,math,english from grade where name = '李四';
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 李四 |      66 |   77 |      88 |
+------+---------+------+---------+
1 row in set (0.00 sec)

将总成绩倒数前三的同学的 chinese 成绩加上 30  

----先查询总分倒数前三----

mysql> select name,chinese,chinese+math+english 总分 from grade order by 总分 limit 3;
+------+---------+------+
| name | chinese | 总分 |
+------+---------+------+
| 周七 |      30 |  187 |
| 李八 |      40 |  198 |
| 李四 |      66 |  231 |
+------+---------+------+
3 rows in set (0.00 sec)

----更新数据，chinese+30----

mysql> update grade set chinese = chinese+30 order by chinese+math+english limit 3;
Query OK, 3 rows affected (0.15 sec)
Rows matched: 3  Changed: 3  Warnings: 0

----查看更新后的数据----

mysql> select name,chinese,chinese+math+english 总分 from grade where name in ('周七','李八','李四');
+------+---------+------+
| name | chinese | 总分 |
+------+---------+------+
| 李四 |      96 |  261 |
| 周七 |      60 |  217 |
| 李八 |      70 |  228 |
+------+---------+------+
3 rows in set (0.00 sec)

将所有同学的所有成绩都更新为原来的二倍

----更新前的数据----

mysql> select * from grade;
+-----+------+---------+------+---------+
| id  | name | chinese | math | english |
+-----+------+---------+------+---------+
| 101 | 张三 |     100 |   88 |      90 |
| 102 | 李四 |      96 |   77 |      88 |
| 103 | 王五 |      88 |   76 |     100 |
| 104 | 王六 |      77 |   88 |      99 |
| 105 | 周七 |      60 |   67 |      90 |
| 106 | 李八 |      70 |   88 |      70 |
+-----+------+---------+------+---------+
6 rows in set (0.00 sec)

----所有成绩*2----

mysql> update grade set chinese = chinese*2,math = math*2,english = english*2;
Query OK, 6 rows affected (0.18 sec)
Rows matched: 6  Changed: 6  Warnings: 0

----更新后的数据----

mysql> select * from grade;
+-----+------+---------+------+---------+
| id  | name | chinese | math | english |
+-----+------+---------+------+---------+
| 101 | 张三 |     200 |  176 |     180 |
| 102 | 李四 |     192 |  154 |     176 |
| 103 | 王五 |     176 |  152 |     200 |
| 104 | 王六 |     154 |  176 |     198 |
| 105 | 周七 |     120 |  134 |     180 |
| 106 | 李八 |     140 |  176 |     140 |
+-----+------+---------+------+---------+
6 rows in set (0.00 sec)

四、Delete

4.1 删除 王六 的所有成绩信息

----查看 王六 的成绩信息----

mysql> select * from grade where name = '王六';
+-----+------+---------+------+---------+
| id  | name | chinese | math | english |
+-----+------+---------+------+---------+
| 104 | 王六 |     154 |  176 |     198 |
+-----+------+---------+------+---------+
1 row in set (0.00 sec)

----删除 王六 的成绩----

mysql> delete from grade where name = '王六';
Query OK, 1 row affected (0.07 sec)

----删除后，查看结果----
mysql> select * from grade where name = '王六';
Empty set (0.00 sec)

4.2 删除整张表数据（谨慎操作）

----先建一个test 表用于删除测试 ----

mysql> create table test_delete(
    -> id int,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> insert into test_delete (id,name) values (1,'小白'),(2,'小红'),(3,'小黑');
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_delete;
+------+------+
| id   | name |
+------+------+
|    1 | 小白 |
|    2 | 小红 |
|    3 | 小黑 |
+------+------+
3 rows in set (0.00 sec)

----删除表数据----

mysql> delete from test_delete;
Query OK, 3 rows affected (0.15 sec)

mysql> select * from test_delete;
Empty set (0.00 sec)