mysql作业_MySQL 作业三

1.清空表数据的SQL语句有哪几种？有什么区别？

delete fromtable_name; ##删除速度慢，可以回滚

truncate table table_name; ##删除速度快，不能回滚

2.在课堂练习创建的course数据库里，已知我们已经创建的students, dept, teacher ,course四个表，需要查看每个系里面所有的老师name和对应的学生的sname，结果按照dept_id升序排序，请写出SQL

selecta.name,b.snamefrom teacher a inner join students b on a.dept_id=b.dept_id

order by a.dept_id;

3.用两个SQL分别求出每个系的学生个数和每个系里的老师个数？如果只用一个SQL语句实现怎么写？

select dept_id,count(*) fromstudents group by dept_id;select dept_id,count(*) fromteacher group by dept_id;selecta.dept_id,count(distinct b.sid),count(distinct a.id)from teacher a inner join students b on a.dept_id=b.dept_id

group by a.dept_id;

mysql>use course;

Reading table informationforcompletion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> select * fromteacher;+-------+-----------+---------+

| id | name | dept_id |

+-------+-----------+---------+

| 10002 | zhang san | 1 |

| 10003 | li si | 4 |

| 10004 | wang wu | 4 |

+-------+-----------+---------+

3 rows in set (0.02sec)

mysql> select * fromstudents;+-----+-------+--------+---------+------------+

| sid | sname | gender | dept_id | tstamp |

+-----+-------+--------+---------+------------+

| 1 | aa | 3 | 1 | 1587125614 |

| 4 | cc | 3 | 1 | 1587125614 |

| 5 | dd | 1 | 2 | 1587125614 |

| 6 | aac | 1 | 1 | 1587125614 |

| 10 | a | 1 | 1 | 1587125614 |

| 20 | bac | 1 | 1 | 1587125614 |

+-----+-------+--------+---------+------------+

6 rows in set (0.00sec)

mysql> select * fromteacher a inner join students b;+-------+-----------+---------+-----+-------+--------+---------+------------+

| id | name | dept_id | sid | sname | gender | dept_id | tstamp |

+-------+-----------+---------+-----+-------+--------+---------+------------+

| 10002 | zhang san | 1 | 1 | aa | 3 | 1 | 1587125614 |

| 10003 | li si | 4 | 1 | aa | 3 | 1 | 1587125614 |

| 10004 | wang wu | 4 | 1 | aa | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 4 | cc | 3 | 1 | 1587125614 |

| 10003 | li si | 4 | 4 | cc | 3 | 1 | 1587125614 |

| 10004 | wang wu | 4 | 4 | cc | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 5 | dd | 1 | 2 | 1587125614 |

| 10003 | li si | 4 | 5 | dd | 1 | 2 | 1587125614 |

| 10004 | wang wu | 4 | 5 | dd | 1 | 2 | 1587125614 |

| 10002 | zhang san | 1 | 6 | aac | 1 | 1 | 1587125614 |

| 10003 | li si | 4 | 6 | aac | 1 | 1 | 1587125614 |

| 10004 | wang wu | 4 | 6 | aac | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 10 | a | 1 | 1 | 1587125614 |

| 10003 | li si | 4 | 10 | a | 1 | 1 | 1587125614 |

| 10004 | wang wu | 4 | 10 | a | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 20 | bac | 1 | 1 | 1587125614 |

| 10003 | li si | 4 | 20 | bac | 1 | 1 | 1587125614 |

| 10004 | wang wu | 4 | 20 | bac | 1 | 1 | 1587125614 |

+-------+-----------+---------+-----+-------+--------+---------+------------+

18 rows in set (0.00sec)

mysql> select * from teacher a inner join students b on a.dept_id=b.dept_id;+-------+-----------+---------+-----+-------+--------+---------+------------+

| id | name | dept_id | sid | sname | gender | dept_id | tstamp |

+-------+-----------+---------+-----+-------+--------+---------+------------+

| 10002 | zhang san | 1 | 1 | aa | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 4 | cc | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 6 | aac | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 10 | a | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 20 | bac | 1 | 1 | 1587125614 |

+-------+-----------+---------+-----+-------+--------+---------+------------+

5 rows in set (0.00sec)

mysql> select a.name,b.sname from teacher a inner join students b on a.dept_id=b.dept_id;+-----------+-------+

| name | sname |

+-----------+-------+

| zhang san | aa |

| zhang san | cc |

| zhang san | aac |

| zhang san | a |

| zhang san | bac |

+-----------+-------+

5 rows in set (0.00sec)

mysql> select a.name,b.sname from teacher a inner join students b on a.dept_id=b.dept_id order by dept_id;

ERROR1052 (23000): Column 'dept_id' in order clause isambiguous

mysql> select a.name,b.sname from teacher a inner join students b on a.dept_id=b.dept_id order by a.dept_idd;+-----------+-------+

| name | sname |

+-----------+-------+

| zhang san | aa |

| zhang san | cc |

| zhang san | aac |

| zhang san | a |

| zhang san | bac |

+-----------+-------+

5 rows in set (0.00sec)

mysql> select a.name,b.sname from teacher a inner join students b on a.dept_id=b.dept_id order by b.dept_idd;+-----------+-------+

| name | sname |

+-----------+-------+

| zhang san | aa |

| zhang san | cc |

| zhang san | aac |

| zhang san | a |

| zhang san | bac |

+-----------+-------+

5 rows in set (0.01sec)

mysql> select name,sname from teacher a inner join students b on a.dept_id=b.dept_id order by b.dept_id;+-----------+-------+

| name | sname |

+-----------+-------+

| zhang san | aa |

| zhang san | cc |

| zhang san | aac |

| zhang san | a |

| zhang san | bac |

+-----------+-------+

5 rows in set (0.00 sec)

mysql> select * from teacher a inner join students b on a.dept_id=b.dept_id;+-------+-----------+---------+-----+-------+--------+---------+------------+

| id | name | dept_id | sid | sname | gender | dept_id | tstamp |

+-------+-----------+---------+-----+-------+--------+---------+------------+

| 10002 | zhang san | 1 | 1 | aa | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 4 | cc | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 6 | aac | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 10 | a | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 20 | bac | 1 | 1 | 1587125614 |

+-------+-----------+---------+-----+-------+--------+---------+------------+

5 rows in set (0.00sec)

mysql> select a.dept_id,count(*) from teacher a inner join students b on a.dept_id=b.dept_id group by a.dept_id;pt_id;+---------+----------+

| dept_id | count(*) |

+---------+----------+

| 1 | 5 |

+---------+----------+

1 row in set (0.00sec)

mysql> select a.dept_id,count(*),count(*) from teacher a inner join students b on a.dept_id=b.dept_id group p by a.dept_id;+---------+----------+----------+

| dept_id | count(*) | count(*) |

+---------+----------+----------+

| 1 | 5 | 5 |

+---------+----------+----------+

1 row in set (0.00sec)

mysql> select a.dept_id,count(a.id),count(b.sid) from teacher a inner join students b on a.dept_id=b.dept_id id group by a.dept_id;+---------+-------------+--------------+

| dept_id | count(a.id) | count(b.sid) |

+---------+-------------+--------------+

| 1 | 5 | 5 |

+---------+-------------+--------------+

1 row in set (0.00sec)

mysql> select a.dept_id,count(distinct a.id),count(b.sid) from teacher a inner join students b on a.dept_id=b.dept_d=b.dept_id group by a.dept_id;+---------+----------------------+--------------+

| dept_id | count(distinct a.id) | count(b.sid) |

+---------+----------------------+--------------+

| 1 | 1 | 5 |

+---------+----------------------+--------------+

1 row in set (0.01sec)

mysql> select * from teacher a inner join students b on a.dept_id=b.dept_id;+-------+-----------+---------+-----+-------+--------+---------+------------+

| id | name | dept_id | sid | sname | gender | dept_id | tstamp |

+-------+-----------+---------+-----+-------+--------+---------+------------+

| 10002 | zhang san | 1 | 1 | aa | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 4 | cc | 3 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 6 | aac | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 10 | a | 1 | 1 | 1587125614 |

| 10002 | zhang san | 1 | 20 | bac | 1 | 1 | 1587125614 |

+-------+-----------+---------+-----+-------+--------+---------+------------+

5 rows in set (0.00sec)

mysql> select a.* from teacher a inner join students b on a.dept_id=b.dept_id;+-------+-----------+---------+

| id | name | dept_id |

+-------+-----------+---------+

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

+-------+-----------+---------+

5 rows in set (0.00sec)mysql> select aa.dept_id,count(*) from (select a.* from teacher a inner join students b on a.dept_id=b.dept_id) aa group by aa.dept_id;+---------+----------+

| dept_id | count(*) |

+---------+----------+

| 1 | 5 |

+---------+----------+

1 row in set (0.00 sec)

mysql> create table aa as select a.* from teacher a inner join students b on a.dept_id=b.dept_id;

Query OK,5 rows affected (0.10sec)

Records:5 Duplicates: 0 Warnings: 0mysql> select a.* from teacher a inner join students b on a.dept_id=b.dept_id;+-------+-----------+---------+

| id | name | dept_id |

+-------+-----------+---------+

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

+-------+-----------+---------+

5 rows in set (0.00sec)

mysql> select * fromaa;+-------+-----------+---------+

| id | name | dept_id |

+-------+-----------+---------+

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

| 10002 | zhang san | 1 |

+-------+-----------+---------+

5 rows in set (0.00sec)mysql> select dept_id,count(*) fromaa group by dept_id;+---------+----------+

| dept_id | count(*) |

+---------+----------+

| 1 | 5 |

+---------+----------+

1 row in set (0.01sec)

mysql> select dept_id,count(distinct id) fromaa group by dept_id;+---------+--------------------+

| dept_id | count(distinct id) |

+---------+--------------------+

| 1 | 1 |

+---------+--------------------+

1 row in set (0.00sec)

mysql> select dept_id,count(distinct id) from teacher a inner join students b on a.dept_id=b.dept_id groupmysql> select dept_id,count(*) fromaa group by dept_id;+---------+----------+

| dept_id | count(*) |

+---------+----------+

| 1 | 5 |

+---------+----------+

1 row in set (0.00sec)

mysql> select a.dept_id,count(distinct a.id) from teacher a inner join students b on a.dept_id=b.dept_id grouproup by a.dept_id;+---------+----------------------+

| dept_id | count(distinct a.id) |

+---------+----------------------+

| 1 | 1 |

+---------+----------------------+

1 row in set (0.00sec)

mysql> select a.dept_id,count(distinct a.id),count(distinct b.sid) from teacher a inner join students b on a.dept_id=b.dept_id group by a.dept_id;+---------+----------------------+-----------------------+

| dept_id | count(distinct a.id) | count(distinct b.sid) |

+---------+----------------------+-----------------------+

| 1 | 1 | 5 |

+---------+----------------------+-----------------------+

1 row in set (0.01 sec)

4.通过老师表和课程表求出每个老师的课程个数，并且需要保证如果老师没有课程的时候则要显示课程为0

select a.name,count(*),count(b.id)from teacher a left join course b on a.id=b.teacher_id

group by a.name;

mysql> select * fromteacher;+-------+-----------+---------+

| id | name | dept_id |

+-------+-----------+---------+

| 10002 | zhang san | 1 |

| 10003 | li si | 4 |

| 10004 | wang wu | 4 |

+-------+-----------+---------+

3 rows in set (0.01sec)

mysql>desc course;+-------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| course_name | varchar(64) | YES | | NULL | |

| teacher_id | int(11) | YES | MUL | NULL | |

+-------------+-------------+------+-----+---------+----------------+

3 rows in set (0.03sec)

mysql> select * fromcourse;

Emptyset (0.03sec)

mysql> insert into course(course_name,teacher_id) values('A',10002),('B',10002),('C',10003);

Query OK,3 rows affected (0.05sec)

Records:3 Duplicates: 0 Warnings: 0mysql> select * fromcourse;+----+-------------+------------+

| id | course_name | teacher_id |

+----+-------------+------------+

| 1 | A | 10002 |

| 2 | B | 10002 |

| 3 | C | 10003 |

+----+-------------+------------+

3 rows in set (0.00sec)

mysql> select * from teacher a inner join course b on a.id=b.teacher_id;+-------+-----------+---------+----+-------------+------------+

| id | name | dept_id | id | course_name | teacher_id |

+-------+-----------+---------+----+-------------+------------+

| 10002 | zhang san | 1 | 1 | A | 10002 |

| 10002 | zhang san | 1 | 2 | B | 10002 |

| 10003 | li si | 4 | 3 | C | 10003 |

+-------+-----------+---------+----+-------------+------------+

3 rows in set (0.00sec)

mysql> select a.id,count(*) from teacher a inner join course b on a.id=b.teacher_id group by a.id;+-------+----------+

| id | count(*) |

+-------+----------+

| 10002 | 2 |

| 10003 | 1 |

+-------+----------+

2 rows in set (0.01sec)

mysql> select a.id,count(*) from teacher a left join course b on a.id=b.teacher_id group by a.id;+-------+----------+

| id | count(*) |

+-------+----------+

| 10002 | 2 |

| 10003 | 1 |

| 10004 | 1 |

+-------+----------+

3 rows in set (0.01sec)

mysql> select * from teacher a left join course b on a.id=b.teacher_id;+-------+-----------+---------+------+-------------+------------+

| id | name | dept_id | id | course_name | teacher_id |

+-------+-----------+---------+------+-------------+------------+

| 10002 | zhang san | 1 | 1 | A | 10002 |

| 10002 | zhang san | 1 | 2 | B | 10002 |

| 10003 | li si | 4 | 3 | C | 10003 |

| 10004 | wang wu | 4 | NULL | NULL | NULL |

+-------+-----------+---------+------+-------------+------------+

4 rows in set (0.00sec)

mysql> select a.id,count(b.id) from teacher a left join course b on a.id=b.teacher_id group by a.id;+-------+-------------+

| id | count(b.id) |

+-------+-------------+

| 10002 | 2 |

| 10003 | 1 |

| 10004 | 0 |

+-------+-------------+

3 rows in set (0.00sec)mysql> select count(1),count('a');+----------+------------+

| count(1) | count('a') |

+----------+------------+

| 1 | 1 |

+----------+------------+

1 row in set (0.00sec)

mysql> select count(1),count('a'),count(null);+----------+------------+-------------+

| count(1) | count('a') | count(null) |

+----------+------------+-------------+

| 1 | 1 | 0 |

+----------+------------+-------------+

1 row in set (0.00sec)

mysql> select a.id,count(b.course_name) from teacher a left join course b on a.id=b.teacher_id group by a.iid;+-------+----------------------+

| id | count(b.course_name) |

+-------+----------------------+

| 10002 | 2 |

| 10003 | 1 |

| 10004 | 0 |

+-------+----------------------+

3 rows in set (0.00sec)mysql> select a.id,count(a.name) from teacher a left join course b on a.id=b.teacher_id group by a.id;+-------+---------------+

| id | count(a.name) |

+-------+---------------+

| 10002 | 2 |

| 10003 | 1 |

| 10004 | 1 |

+-------+---------------+

3 rows in set (0.01sec)

mysql> select count(0);+----------+

| count(0) |

+----------+

| 1 |

+----------+

1 row in set (0.01sec)mysql> select sum(0);+--------+

| sum(0) |

+--------+

| 0 |

+--------+

1 row in set (0.00sec)

mysql> select count('');+-----------+

| count('') |

+-----------+

| 1 |

+-----------+

1 row in set (0.00 sec)

5.查看学生信息表里按照sid升序排序后的第7到第15行的学生数据

select * from students order by sid limit 6,9;

mysql> select * fromstudents order by sid;+-----+-------+--------+---------+------------+

| sid | sname | gender | dept_id | tstamp |

+-----+-------+--------+---------+------------+

| 1 | aa | 3 | 1 | 1587125614 |

| 4 | cc | 3 | 1 | 1587125614 |

| 5 | dd | 1 | 2 | 1587125614 |

| 6 | aac | 1 | 1 | 1587125614 |

| 10 | a | 1 | 1 | 1587125614 |

| 20 | bac | 1 | 1 | 1587125614 |

+-----+-------+--------+---------+------------+

6 rows in set (0.00sec)

mysql> select * from students order by sid limit 2;+-----+-------+--------+---------+------------+

| sid | sname | gender | dept_id | tstamp |

+-----+-------+--------+---------+------------+

| 1 | aa | 3 | 1 | 1587125614 |

| 4 | cc | 3 | 1 | 1587125614 |

+-----+-------+--------+---------+------------+

2 rows in set (0.00sec)

mysql> select * from students order by sid limit 2,2;+-----+-------+--------+---------+------------+

| sid | sname | gender | dept_id | tstamp |

+-----+-------+--------+---------+------------+

| 5 | dd | 1 | 2 | 1587125614 |

| 6 | aac | 1 | 1 | 1587125614 |

+-----+-------+--------+---------+------------+

2 rows in set (0.00sec)

mysql> select * from students order by sid limit 0,2;+-----+-------+--------+---------+------------+

| sid | sname | gender | dept_id | tstamp |

+-----+-------+--------+---------+------------+

| 1 | aa | 3 | 1 | 1587125614 |

| 4 | cc | 3 | 1 | 1587125614 |

+-----+-------+--------+---------+------------+

2 rows in set (0.00 sec)

6.Union 和union all的区别是什么？

Union 是最后的合并结果删除重复行

union all 是显示所有的结果

mysql> select sid,sname fromstudents;+-----+-------+

| sid | sname |

+-----+-------+

| 10 | a |

| 1 | aa |

| 6 | aac |

| 20 | bac |

| 4 | cc |

| 5 | dd |

+-----+-------+

6 rows in set (0.00sec)

mysql> select id,name fromteacher;+-------+-----------+

| id | name |

+-------+-----------+

| 10002 | zhang san |

| 10003 | li si |

| 10004 | wang wu |

+-------+-----------+

3 rows in set (0.00sec)

mysql> select id,name from teacher union select sid,sname fromstudents;+-------+-----------+

| id | name |

+-------+-----------+

| 10002 | zhang san |

| 10003 | li si |

| 10004 | wang wu |

| 10 | a |

| 1 | aa |

| 6 | aac |

| 20 | bac |

| 4 | cc |

| 5 | dd |

+-------+-----------+

9 rows in set (0.00 sec)

mysql>show tables;+------------------+

| Tables_in_course |

+------------------+

| aa |

| course |

| dept |

| dept_bak |

| scores |

| students |

| students3 |

| students_bak |

| students_new |

| students_tmp |

| teacher |

| teacher_backup |

| v_1 |

+------------------+

13 rows in set (0.01sec)

mysql> select * fromstudents3;

Emptyset (0.03sec)

mysql>desc students3;+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| sid | int(11) | NO | PRI | NULL | |

| sname | varchar(64) | YES | | NULL | |

| gender | varchar(12) | NO | PRI | NULL | |

| dept_id | int(11) | NO | | NULL | |

| sname2 | varchar(10) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

5 rows in set (0.00sec)

mysql> insert into students3 values(1,'aa',1,1,'a');

Query OK,1 row affected (0.03sec)

mysql> select * fromstudents3;+-----+-------+--------+---------+--------+

| sid | sname | gender | dept_id | sname2 |

+-----+-------+--------+---------+--------+

| 1 | aa | 1 | 1 | a |

+-----+-------+--------+---------+--------+

1 row in set (0.00sec)

mysql> select * fromstudents;+-----+-------+--------+---------+------------+

| sid | sname | gender | dept_id | tstamp |

+-----+-------+--------+---------+------------+

| 1 | aa | 3 | 1 | 1587125614 |

| 4 | cc | 3 | 1 | 1587125614 |

| 5 | dd | 1 | 2 | 1587125614 |

| 6 | aac | 1 | 1 | 1587125614 |

| 10 | a | 1 | 1 | 1587125614 |

| 20 | bac | 1 | 1 | 1587125614 |

+-----+-------+--------+---------+------------+

6 rows in set (0.00sec)

mysql> select sid,sname from students union select sid,sname fromstudents3;+-----+-------+

| sid | sname |

+-----+-------+

| 10 | a |

| 1 | aa |

| 6 | aac |

| 20 | bac |

| 4 | cc |

| 5 | dd |

+-----+-------+

6 rows in set (0.00sec)

mysql> select sid,sname from students union all select sid,sname fromstudents3;+-----+-------+

| sid | sname |

+-----+-------+

| 10 | a |

| 1 | aa |

| 6 | aac |

| 20 | bac |

| 4 | cc |

| 5 | dd |

| 1 | aa |

+-----+-------+

7 rows in set (0.00 sec)

7.举例常用的聚合函数

max(), min(), count(), sum(), avg()

8.使用select into outfile将teacher表里的数据导出，注意字段之间用;号隔开，字符串用”号隔开

SELECT * INTO OUTFILE '/tmp/students.txt'FIELDS TERMINATED BY';' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY'\n'FROM teacher;

9.请创建一个包含每个老师姓名和对应的课程个数，且只显示所教课程个数在2个以上的视图view

create view v_course as select a.name,count(*) count1 fromteacher a inner join

course b on a.id=b.teacher_id

group by a.name having count(*)>1

mysql> select * from teacher a inner join course b on a.id=b.teacher_id;+-------+-----------+---------+----+-------------+------------+

| id | name | dept_id | id | course_name | teacher_id |

+-------+-----------+---------+----+-------------+------------+

| 10002 | zhang san | 1 | 1 | A | 10002 |

| 10002 | zhang san | 1 | 2 | B | 10002 |

| 10003 | li si | 4 | 3 | C | 10003 |

+-------+-----------+---------+----+-------------+------------+

3 rows in set (0.00sec)

mysql> select a.id,count(*) from teacher a inner join course b on a.id=b.teacher_id group by a.id having count(*)>2;

Emptyset (0.00sec)

mysql> select a.id,count(*) from teacher a inner join course b on a.id=b.teacher_id group by a.id having count(*)>=2;+-------+----------+

| id | count(*) |

+-------+----------+

| 10002 | 2 |

+-------+----------+

1 row in set (0.00sec)

mysql> select a.id,count(*) from teacher a left join course b on a.id=b.teacher_id group by a.id having couunt(*)>=2;+-------+----------+

| id | count(*) |

+-------+----------+

| 10002 | 2 |

+-------+----------+

1 row in set (0.01sec)

mysql> create view v_temp as select a.id,count(*) from teacher a inner join course b on a.id=b.teacher_id group by a.id having count(*)>=2;

Query OK,0 rows affected (0.05sec)

mysql> select * fromv_temp;+-------+----------+

| id | count(*) |

+-------+----------+

| 10002 | 2 |

+-------+----------+

1 row in set (0.00 sec)