实战案例（MDL语句）

实战案例

1.搭建mysql服务
mysql基础（安装mysql）

2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9

命令如下：
mysql> create database ftx;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ftx                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use ftx
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(100) not null,age tinyint); 
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

3.查看下该新建的表有无内容（用select语句）

命令如下：
mysql> select * from student;
Empty set (0.00 sec)               //表中还没有任何数据
1
2
3

4.往新建的student表中插入数据（用insert语句），结果应如下所示：

+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | student     |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

命令如下：
mysql> insert student(name,age) values('tom',20),('jerry',23),('student',25),('sean',28),('zhangsan',26),('zhangsan',20),('lisi',null),('chenshuo',10),('wangwu',3),('qiuyi',10),('qiuxiaotian',20);    //因为我们在创建表的时候设置了id自动继承（auto_increment），所以此处可以不要手动指定id
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | student     |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   10 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

5.修改lisi的年龄为50

命令如下：
mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi |   50 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

6.以age字段降序排序

命令如下：
mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  3 | student     |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangsan    |   20 |
| 11 | qiuxiaotian |   20 |
|  8 | chenshuo    |   10 |
| 10 | qiuyi       |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

7.查询student表中年龄最小的3位同学跳过前2位

命令如下：
mysql> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  8 | chenshuo    |   10 |
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

8.查询student表中年龄最大的4位同学

命令如下：
mysql> select * from student order by age desc limit 4;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | lisi     |   50 |
|  4 | sean     |   28 |
|  5 | zhangsan |   26 |
|  3 | student  |   25 |
+----+----------+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

9.查询student表中名字叫zhangshan的记录

命令如下：
mysql> select * from student where name = 'zhangsan';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
|  6 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

10.查询student表中名字叫zhangshan且年龄大于20岁的记录

命令如下：
mysql> select * from student where name = 'zhangsan' and age > 20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
+----+----------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

11.查询student表中年龄在23到30之间的记录

命令如下：
mysql> select * from student where age between 23 and 30;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | student  |   25 |
|  4 | sean     |   28 |
|  5 | zhangsan |   26 |
+----+----------+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

12.修改wangwu的年龄为100

命令如下：
mysql> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

13.删除student中名字叫zhangshan且年龄小于等于20的记录

命令如下：
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | student     |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   10 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> delete from student where name = 'zhangsan' and age <= 20;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | student     |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   10 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38