作业

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

MariaDB [meijianbiao]> DESC   student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.001 sec)


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

MariaDB [meijianbiao]> SELECT * FROM student ;
Empty set (0.000 sec)

4.往新建的student表中插入数据（用insert语句）
MariaDB [meijianbiao]> INSERT INTO student (id,name,age) VALUES (1,'tom',20),(2,'jerry',23),(3,'wangqing',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);
Query OK, 11 rows affected (0.001 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [meijianbiao]> SELECT * FROM student ;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   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 |
+----+-------------+------+
11 rows in set (0.000 sec)


5.修改lisi的年龄为50
MariaDB [meijianbiao]>  update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [meijianbiao]> SELECT * FROM student ;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

6.以age字段降序排序
MariaDB [meijianbiao]> SELECT age FROM student ORDER BY age DESC;
+------+
| age  |
+------+
|   50 |
|   28 |
|   26 |
|   25 |
|   23 |
|   20 |
|   20 |
|   20 |
|   15 |
|   10 |
|    3 |
+------+
11 rows in set (0.000 sec)

7.查询student表中年龄最小的3位同学跳过前2位
MariaDB [meijianbiao]> SELECT age FROM student ORDER BY age  limit 2 , 3; 
+------+
| age  |
+------+
|   15 |
|   20 |
|   20 |
+------+
3 rows in set (0.000 sec)

8.查询student表中年龄最大的4位同学
MariaDB [meijianbiao]> SELECT age FROM student ORDER BY age DESC limit  4 ; 
+------+
| age  |
+------+
|   50 |
|   28 |
|   26 |
|   25 |
+------+
4 rows in set (0.000 sec)



9.查询student表中名字叫zhangshan的记录
MariaDB [meijianbiao]> SELECT * FROM student WHERE name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.000 sec)


10.查询student表中名字叫zhangshan且年龄大于20岁的记录
MariaDB [meijianbiao]> SELECT * FROM student WHERE  age > 20 and  name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.000 sec)



11.查询student表中年龄在23到30之间的记录
MariaDB [meijianbiao]> SELECT * FROM student WHERE  age >=23 and age<=30 ;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.000 sec)



12.修改wangwu的年龄为100
MariaDB [meijianbiao]>  update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [meijianbiao]> SELECT * FROM student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)




13.删除student中名字叫zhangshan且年龄小于等于20的记录
MariaDB [meijianbiao]> delete from student where name = 'zhangshan' and age <= 20; 
Query OK, 1 row affected (0.001 sec)

MariaDB [meijianbiao]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.000 sec)