2021-07-02

MySQ练习题

练习1

Manager(管理员表)：
mid 编号 int （主键）
mname 名字 varchar(20)
age 年龄 int
sex 性别 char(2)
password 密码 varchar(20)
address 地址 varchar(20)
phone 电话 varchar(20)
数据：
1 王子 18 男 123 北京 110
2 公主 20 女 456 上海 220
3 太子 23 男 789 南京 330
需求：
(1)创建表

mysql> create table Manager(mid int,mname varchar(20),age int,sex char(2),password varchar(20),address varchar(20),phone varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> desc Manager;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| mid      | int         | YES  |     | NULL    |       |
| mname    | varchar(20) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| sex      | char(2)     | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
| address  | varchar(20) | YES  |     | NULL    |       |
| phone    | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

(2)将数据插入到表中

mysql> insert into Manager values(1,'王子',18,'男','123','北京','110'),
    -> (2,'公主',20,'女','456','上海','220'),
    -> (3,'太子',23,'男','789','南京','330');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+-------+
| mid  | mname | age  | sex  | password | address | phone |
+------+-------+------+------+----------+---------+-------+
|    1 | 王子  |   18 | 男   | 123      | 北京    | 110   |
|    2 | 公主  |   20 | 女   | 456      | 上海    | 220   |
|    3 | 太子  |   23 | 男   | 789      | 南京    | 330   |
+------+-------+------+------+----------+---------+-------+
3 rows in set (0.00 sec)

(3)将王子的年龄修改为24

mysql> update Manager set age=24 where mname='王子';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+-------+
| mid  | mname | age  | sex  | password | address | phone |
+------+-------+------+------+----------+---------+-------+
|    1 | 王子  |   24 | 男   | 123      | 北京    | 110   |
|    2 | 公主  |   20 | 女   | 456      | 上海    | 220   |
|    3 | 太子  |   23 | 男   | 789      | 南京    | 330   |
+------+-------+------+------+----------+---------+-------+
3 rows in set (0.00 sec)

(4)将地址是南京的管理员改为天津

mysql> update Manager set address='天津' where address='南京';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+-------+
| mid  | mname | age  | sex  | password | address | phone |
+------+-------+------+------+----------+---------+-------+
|    1 | 王子  |   24 | 男   | 123      | 北京    | 110   |
|    2 | 公主  |   20 | 女   | 456      | 上海    | 220   |
|    3 | 太子  |   23 | 男   | 789      | 天津    | 330   |
+------+-------+------+------+----------+---------+-------+
3 rows in set (0.01 sec)

(5)将性别是女,并且年龄大于30的用户密码改为888888

mysql> update Manager set password='888888' where sex='女'and age>30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+-------+
| mid  | mname | age  | sex  | password | address | phone |
+------+-------+------+------+----------+---------+-------+
|    1 | 王子  |   24 | 男   | 123      | 北京    | 110   |
|    2 | 公主  |   20 | 女   | 456      | 上海    | 220   |
|    3 | 太子  |   23 | 男   | 789      | 天津    | 330   |
+------+-------+------+------+----------+---------+-------+
3 rows in set (0.00 sec)

##表中没有满足条件的内容所以修改后表没有变化

(6)将所有用户的密码恢复最初设置111111

mysql> update Manager set password='111111';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+-------+
| mid  | mname | age  | sex  | password | address | phone |
+------+-------+------+------+----------+---------+-------+
|    1 | 王子  |   24 | 男   | 111111   | 北京    | 110   |
|    2 | 公主  |   20 | 女   | 111111   | 上海    | 220   |
|    3 | 太子  |   23 | 男   | 111111   | 天津    | 330   |
+------+-------+------+------+----------+---------+-------+
3 rows in set (0.00 sec)

(7)将员工的电话中不是110的电话号码改为7654321

mysql> update Manager set phone='7654321' where phone<>'110';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+---------+
| mid  | mname | age  | sex  | password | address | phone   |
+------+-------+------+------+----------+---------+---------+
|    1 | 王子  |   24 | 男   | 111111   | 北京    | 110     |
|    2 | 公主  |   20 | 女   | 111111   | 上海    | 7654321 |
|    3 | 太子  |   23 | 男   | 111111   | 天津    | 7654321 |
+------+-------+------+------+----------+---------+---------+
3 rows in set (0.00 sec)

(8)将王子的年龄改为18，地址改为承德，性别改为女

mysql> update Manager set age=18,address='承德',sex='女' where mname='王子';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from Manager;
+------+-------+------+------+----------+---------+---------+
| mid  | mname | age  | sex  | password | address | phone   |
+------+-------+------+------+----------+---------+---------+
|    1 | 王子  |   18 | 女   | 111111   | 承德    | 110     |
|    2 | 公主  |   20 | 女   | 111111   | 上海    | 7654321 |
|    3 | 太子  |   23 | 男   | 111111   | 天津    | 7654321 |
+------+-------+------+------+----------+---------+---------+
3 rows in set (0.01 sec)

(9) 删除王子的信息

mysql> delete from Manager where mname='王子';
Query OK, 1 row affected (0.01 sec)

mysql> select *from Manager;
+------+-------+------+------+----------+---------+---------+
| mid  | mname | age  | sex  | password | address | phone   |
+------+-------+------+------+----------+---------+---------+
|    2 | 公主  |   20 | 女   | 111111   | 上海    | 7654321 |
|    3 | 太子  |   23 | 男   | 111111   | 天津    | 7654321 |
+------+-------+------+------+----------+---------+---------+
2 rows in set (0.01 sec)

(10)删除地址在南京并且年龄大于60的员工信息

mysql> delete from Manager where address='南京'and age>60;
Query OK, 0 rows affected (0.00 sec)

(11)删除不在北京的员工信息

mysql> delete from Manager where address<>'北京';
Query OK, 2 rows affected (0.01 sec)

mysql> select *from Manager;
Empty set (0.01 sec)

(12)删除地址在北京或上海的员工信息

 delete from Manager where address='北京'or address='上海';
Query OK, 0 rows affected (0.00 sec)

(13)删除电话号码是空的员工信息

 delete from Manager where phone=''or phone is null;
Query OK, 0 rows affected (0.00 sec)

练习2：

scores
stuid int 学生id
java int java成绩
mysql int mysql成绩
stuname varchar(20) 学生姓名
数据：
limit (pageindex-1)*pagesize,pagesize;
1 67 78 张三
2 87 55 李四
3 66 90 王五
4 98 78 赵六
5 80 88 田七
需求：
(1)对java成绩进行降序排序

mysql> select *from score order by java desc;
+-------+------+-------+---------+
| stuid | java | mysql | stuname |
+-------+------+-------+---------+
|     4 |   98 |    78 | 赵六    |
|     2 |   87 |    55 | 李四    |
|     5 |   80 |    88 | 田七    |
|     1 |   67 |    78 | 张三    |
|     3 |   66 |    90 | 王五    |
+-------+------+-------+---------+
5 rows in set (0.00 sec)

(2)得到mysql成绩前三名

对MySQL降序取前3列

mysql> select *from score order by mysql desc limit 0,3;
+-------+------+-------+---------+
| stuid | java | mysql | stuname |
+-------+------+-------+---------+
|     3 |   66 |    90 | 王五    |
|     5 |   80 |    88 | 田七    |
|     1 |   67 |    78 | 张三    |
+-------+------+-------+---------+
3 rows in set (0.00 sec)

(3)得到java学生中最后一名的学生信息

对Java升序 取第1列

mysql> select *from score order by java asc limit 0,1;
+-------+------+-------+---------+
| stuid | java | mysql | stuname |
+-------+------+-------+---------+
|     3 |   66 |    90 | 王五    |
+-------+------+-------+---------+
1 row in set (0.00 sec)

(4)查询出两门成绩都优秀(>=80)的学生姓名

 select *from score where java>=80 and mysql >=80;
+-------+------+-------+---------+
| stuid | java | mysql | stuname |
+-------+------+-------+---------+
|     5 |   80 |    88 | 田七    |
+-------+------+-------+---------+
1 row in set (0.00 sec)

(5)查询出成绩在90分以上(>=90)的学生信息

mysql> select *from score where java>=90 or mysql>=90;
+-------+------+-------+---------+
| stuid | java | mysql | stuname |
+-------+------+-------+---------+
|     3 |   66 |    90 | 王五    |
|     4 |   98 |    78 | 赵六    |
+-------+------+-------+---------+
2 rows in set (0.00 sec)

(6)查询出每名学员的java,mysql,总成绩
mysql> select stuname,java,mysql,java+mysql from score;
±--------±-----±------±-----------+
| stuname | java | mysql | java+mysql |
±--------±-----±------±-----------+
| 张三 | 67 | 78 | 145 |
| 李四 | 87 | 55 | 142 |
| 王五 | 66 | 90 | 156 |
| 赵六 | 98 | 78 | 176 |
| 田七 | 80 | 88 | 168 |
±--------±-----±------±-----------+
5 rows in set (0.00 sec)

(7)显示出每名学生的总分以及姓名

mysql> select stuname,java+mysql total from score;
+---------+-------+
| stuname | total |
+---------+-------+
| 张三    |   145 |
| 李四    |   142 |
| 王五    |   156 |
| 赵六    |   176 |
| 田七    |   168 |
+---------+-------+
5 rows in set (0.00 sec)

练习3

测试数据：
郭敬明 1371234567 北京 java S1101 89 1979-04-05
张三丰 1372839201 上海 数据库 S1102 67 1967-09-07
赵敏 1387839201 山东 mysql S1103 99 1987-09-07
Student2
stuname 姓名 varchar(20)
telphone 电话 varchar(20)
address 住址 varchar(20)
subject 科目 varchar(20)
stuNo 学号 varchar(20)
score 成绩 int
birthday 出生日期 date

mysql> create table student2(stuname varchar(20),telephone varchar(20),
    -> address varchar(20),subject varchar(20),stuNo varchar(20),score int);
Query OK, 0 rows affected (0.07 sec)

mysql> desc student2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| stuname   | varchar(20) | YES  |     | NULL    |       |
| telephone | varchar(20) | YES  |     | NULL    |       |
| address   | varchar(20) | YES  |     | NULL    |       |
| subject   | varchar(20) | YES  |     | NULL    |       |
| stuNo     | varchar(20) | YES  |     | NULL    |       |
| score     | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table student2 add birthday varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| stuname   | varchar(20) | YES  |     | NULL    |       |
| telephone | varchar(20) | YES  |     | NULL    |       |
| address   | varchar(20) | YES  |     | NULL    |       |
| subject   | varchar(20) | YES  |     | NULL    |       |
| stuNo     | varchar(20) | YES  |     | NULL    |       |
| score     | int         | YES  |     | NULL    |       |
| birthday  | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> insert into student2 values('郭敬明','1371234567','北京','java','S1101',89,'1979-04-05'),
    -> ('张三丰','1372839201','上海','数据库','S1102',67,'1967-09-07'),
    -> ('赵敏','1387839201','山东','mysql','S1103',99,'1987-09-07');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from student2;
+---------+------------+---------+---------+-------+-------+------------+
| stuname | telephone  | address | subject | stuNo | score | birthday   |
+---------+------------+---------+---------+-------+-------+------------+
| 郭敬明  | 1371234567 | 北京    | java    | S1101 |    89 | 1979-04-05 |
| 张三丰  | 1372839201 | 上海    | 数据库  | S1102 |    67 | 1967-09-07 |
| 赵敏  | 1387839201| 山东   |mysql  | S1103 |    99 | 1987-09-07 |
+---------+------------+---------+---------+-------+-------+------------+
3 rows in set (0.01 sec)

//1.要查询列 2.条件
a.查询住址为“山东”的学生姓名、电话、住址

mysql> select stuname,telephone,address from student2 where address='山东';
+---------+------------+---------+
| stuname | telephone  | address |
+---------+------------+---------+
| 赵敏    | 1387839201 | 山东    |
+---------+------------+---------+
1 row in set (0.00 sec)

b.查询名称中含有“数据库”字样科目信息

mysql> select subject from student2 where subject like '%数据库%';
+---------+
| subject |
+---------+
| 数据库  |
+---------+
1 row in set (0.00 sec)

c.查询电话中以“1387”开头的学生信息

mysql> select *from student2 where telephone like '1387%';
+---------+------------+---------+---------+-------+-------+------------+
| stuname | telephone  | address | subject | stuNo | score | birthday   |
+---------+------------+---------+---------+-------+-------+------------+
| 赵敏    | 1387839201 | 山东    | mysql   | S1103 |    99 | 1987-09-07 |
+---------+------------+---------+---------+-------+-------+------------+
1 row in set (0.00 sec)

d.查询姓姜的，三个字的学生信息

mysql> select *from student2 where stuname like '姜__';
Empty set (0.00 sec)

e.查询学号为S1101的指定java,mysql科目考试成绩

mysql> select score from student2 where stuNo='S1101'and (subject = 'java'or subject='mysql');
+-------+
| score |
+-------+
|    89 |
+-------+```

 f.查询出80后学员信息
 mysql> select *from student2 where birthday between '1980-01-01'and '1989-12-31';
+---------+------------+---------+---------+-------+-------+------------+
| stuname | telephone  | address | subject | stuNo | score | birthday   |
+---------+------------+---------+---------+-------+-------+------------+
| 赵敏    | 1387839201 | 山东    | mysql   | S1103 |    99 | 1987-09-07 |
+---------+------------+---------+---------+-------+-------+------------+
1 row in set (0.00 sec)

g.查询出家庭住址在北上广的学生名字

mysql> select stuname from student2 where address='北京' or address='上海' or address='广州';
+---------+
| stuname |
+---------+
| 郭敬明  |
| 张三丰  |
+---------+
2 rows in set (0.00 sec)

mysql> select stuname from student2 where address in ('北京','上海','广州');
+---------+
| stuname |
+---------+
| 郭敬明  |
| 张三丰  |
+---------+
2 rows in set (0.00 sec)

h.显示成绩在第5-10名的学生名字和电话

mysql> select stuname,telephone from student2 order by score desc limit 4,6;
Empty set (0.01 sec)

i.查询分数在80-90之间并且在北京的学生

mysql> select *from student2 where score between 80 and 90 and address='北京';
+---------+------------+---------+---------+-------+-------+------------+
| stuname | telephone  | address | subject | stuNo | score | birthday   |
+---------+------------+---------+---------+-------+-------+------------+
| 郭敬明  | 1371234567 | 北京    | java    | S1101 |    89 | 1979-04-05 |
+---------+------------+---------+---------+-------+-------+------------+
1 row in set (0.00 sec)

练习4.聚合函数练习

表：scores2
年级 grade varchar(10)
学号 stuno varchar(20)
考试时间 examDate date
科目 subject varchar(20)
成绩 score int
学期 xueqi int
数据:
S1 S1101 2015-02-03 C 89 1
S2 S1103 2015-03-03 JAVA 90 2
S3 S1102 2015-07-03 C 100 1
1.查询学生总人数

mysql> select count(*) from scores2;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

2.学号为S1101的学生第一学期考试总成绩,平均分

mysql> select sum(score),avg(score) from scores2 where stuno='S1101'and xueqi=1;
+------------+------------+
| sum(score) | avg(score) |
+------------+------------+
|         89 |    89.0000 |
+------------+------------+
1 row in set (0.01 sec)

3.查询2013年3月22日科目“C”的最高分、最低分、平均分

mysql> select max(score),min(score),avg(score) from scores2 where examDate='2013-03-22'and subject='c';
+------------+------------+------------+
| max(score) | min(score) | avg(score) |
+------------+------------+------------+
|       NULL |       NULL |       NULL |
+------------+------------+------------+
1 row in set (0.01 sec)

4.查询2013年3月22日科目“C”及格学生的平均分

 select avg(score) from scores2 where score>=60 and(examDate='2013-03-22'and subject='c');
+------------+
| avg(score) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

5.查询所有参加“C”科目考试的平均分

mysql> select avg(score) from scores2 where subject='c';
+------------+
| avg(score) |
+------------+
|    94.5000 |
+------------+
1 row in set (0.00 sec)

6.查看考java的人数

mysql> select count(*) from scores2 where subject='java';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

练习5.分组练习

表名：student
年级(grade) varchar(10)
学生姓名(name) varchar(10)
学时(xueshi) int --每人单个学时
参加考试(isexam) char(1) 是/否、
课程(subject) varchar(10)
分数(score) int
数据：
1 张三 10 是 java 99
1 李四 10 否 java 0
2 王五 20 是 mysql 88
2 赵六 20 是 mysql 77
2 王五 20 是 java 99
2 赵六 20 否 java 0
1 张三 10 是 mysql 88

练习：
a:查询每个年级的总学时数，并按照升序排列

 select grade,sum(xueshi) total from student group by grade order by total asc;
+-------+-------+
| grade | total |
+-------+-------+
| 1     |    30 |
| 2     |    80 |
+-------+-------+
2 rows in set (0.00 sec)

b:查询每个参加考试的学员的平均分

mysql> select name,avg(score) from student where isexam='是' group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    93.5000 |
| 王五 |    93.5000 |
| 赵六 |    77.0000 |
+------+------------+
3 rows in set (0.00 sec)

c:查询每门课程的平均分，并按照降序排列

mysql> select subject,avg(score) from student group by subject order by score desc;
+---------+------------+
| subject | avg(score) |
+---------+------------+
| java    |    49.5000 |
| mysql   |    84.3333 |
+---------+------------+
2 rows in set (0.00 sec)

练习6.综合练习

Student3
科目名称 subjectName varchar(20)
学生姓名 stuname varchar(20)
学生地址 address varchar(20)
学生性别 sex char(2)
电子邮件 email varchar(30)
年级 grade varchar(10)
出生日期 birthday date
考试日期 examDate date
成绩 scores int

数据：
JAVA 张三 北京 男 123@qq.com S1 1990-03-04 2013-5-6 89
html 李四 上海 男 S2 1993-08-04 2014-5-6 87
html 王五 北京 男 123@qq.com S2 1990-03-04 2015-4-6 90

1.查询S2的科目名称

mysql> select subjectName from student3 where grade='S2';
+-------------+
| subjectName |
+-------------+
| html        |
| html        |
+-------------+
2 rows in set (0.00 sec)

2.查询S2男同学的姓名和住址

mysql> select stuname,address from student3 where grade='S2' and sex='男';
+---------+---------+
| stuname | address |
+---------+---------+
| 李四    | 上海    |
| 王五    | 北京    |
+---------+---------+
2 rows in set (0.00 sec)

3.查询无电子邮件的学生姓名和年级信息

mysql> select stuname,grade from student3 where email='';
+---------+-------+
| stuname | grade |
+---------+-------+
| 李四    | S2    |
+---------+-------+
1 row in set (0.00 sec)

4.查询出生日期在1993年之后的S2的学生姓名和年级信息

mysql> select stuname,grade from student3 where grade='S2' and birthday>'1993-12-31';
Empty set (0.00 sec)

5.查询参加了日期为2013年2月15日的“HTML” 科目考试的成绩信息

mysql> select scores from student3 where examDate='2013-02-15' and subjectName='html';
Empty set (0.00 sec)