mysql基础作业

作业

1. 搭建mysql服务
2. 创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age）
3. 查看下该新建的表有无内容（用select语句）
4. 往新建的student表中插入数据（用insert语句），结果应如下所示：略
5. 修改lisi的年龄为50
6. 以age字段降序排序
7. 查询student表中年龄最小的3位同学跳过前2位
8. 查询student表中年龄最大的4位同学
9. 查询student表中名字叫zhangshan的记录
10. 查询student表中名字叫zhangshan且年龄大于20岁的记录
11. 查询student表中年龄在23到30之间的记录
12. 修改wangwu的年龄为100
13. 删除student中名字叫zhangshan且年龄小于等于20的记录

[root@localhost ~]# mysqldump -uroot -pre123456! --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

//1.创建fxx数据库
mysql> create database fxx;
Query OK, 1 row affected (0.00 sec)
//启用fxx数据库
mysql> use fxx;
Database changed

//2.创建学生表
mysql> create table student(id int(11) not null primary key auto__increment,name varchar(100) not null ,age tinyint(4) );
Query OK, 0 rows affected (0.03 sec)

//3.查看学生表
mysql> desc stduent;
ERROR 1146 (42S02): Table 'fxx.stduent' doesn't exist
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)

//4.添加记录
mysql> insert student(name,age) values('tom',20),('jerry',23),('wangqing',25),('zhangshan',26),('zhangshan',20),('lisi',null),('cchenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
//查看表
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | zhangshan   |   26 |
|  5 | zhangshan   |   20 |
|  6 | lisi        | NULL |
|  7 | chenshuo    |   10 |
|  8 | wangwu      |    3 |
|  9 | qiuyi       |   15 |
| 10 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.01 sec)

//5
mysql> select * from student where name='lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  6 | lisi |   50 |
+----+------+------+
1 row in set (0.00 sec)

//6
mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  6 | lisi        |   50 |
|  4 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  5 | zhangshan   |   20 |
| 10 | qiuxiaotian |   20 |
|  9 | qiuyi       |   15 |
|  7 | chenshuo    |   10 |
|  8 | wangwu      |    3 |
+----+-------------+------+
10 rows in set (0.00 sec)

//7
mysql> select * from student order by age LIMIT 2,1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  9 | qiuyi |   15 |
+----+-------+------+
1 row in set (0.00 sec)

//8
mysql> select * from student order by age desc LIMIT 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  6 | lisi      |   50 |
|  4 | zhangshan |   26 |
|  3 | wangqing  |   25 |
|  2 | jerry     |   23 |
+----+-----------+------+
4 rows in set (0.00 sec)

//9
mysql> select * from student where name='zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | zhangshan |   26 |
|  5 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.00 sec)

//10
mysql> select * from student where name='zhangshan' and age>2
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.00 sec)

//11
mysql> select * from student where age between 23 and 30 ;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | zhangshan |   26 |
+----+-----------+------+
3 rows in set (0.00 sec)

//12
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  |
+----+--------+------+
|  8 | wangwu |  100 |
+----+--------+------+
1 row in set (0.00 sec)

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

mysql> select * from student where name='zhangshan' and age<=20;
Empty set (0.00 sec)