数据库基础SQL知识面试题一
数据库基础SQL知识面试题一
作者:尹正杰
版权声明:原创作品,谢绝转载!否则将追究法律责任。
如标题所示,本篇博客主要介绍基础知识的面试题。大家可以用来测试面试者的技术水平,由于个人水平所限,难免会出现错误,欢迎广大读者和DBA专家留言指正。
一.常见的关系型数据库有那些?常见的非关系型数据库有那些?
答:常见的关系型数据库:
1>.MySQL;
2>.SQL Server;
3>.Oracle;
4>.Sybase;
5>.DB2等;
常见的非关系型数据库:
1>.Redis;
2>.Cassandra;
3>.MongoDB;
4>.HBase;
5>.KiokuDB;
6>.Scalaris等;
二.字段,数据库服务器,数据库实例,表,数据库之间是什么逻辑关系?
答:简而言之,它们符合下面的一种对应关系:
1>.一个数据库服务其可以有多个数据库实例;
2>.一个数据库实例可以有多个数据库;
3>.一个数据库可以有多张表;
4>.一张表中可以有多个字段;
三.视图view是否负责存储数据?
答:并不负责存储数据,他只是提前定义好了SQL语句。
四.MySQL从哪个版本开始使用了InnoDB作为默认存储引擎?
答:MySQL5.5版本,默认存储引擎更改为InnoDB,提高性能和可扩展性, 增加半同步复制。
五.MySQL安装之后的root临时密码怎么修改?
答:如果是二进制方式登录后执行“alter user user() identified by 'yinzhengjie';”即可,如果是yum方式安装需要设置一个复杂度相对较高的密码,详情请参考:使用yum源的方式单机部署MySQL8.0.13。
六.MySQL默认的监听端口是多少?如何修改?
答:MySQL默认监听的端口是3306,我们可以通过修改Mysql的默认配置文件(my.cnf)修改里面的port参数。如果想要修改其他的常用参数,可参考我的笔记:MySQL8常见客户端和启动相关参数。
七.如果企业由于扩容问题需要将数据文件夹data转移到另外的磁盘,应该怎么做?
答:大致分为4个步骤。具体操作如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node101 ~]# /etc/init.d/mysql.server status SUCCESS! MySQL running (3058) [root@node101 ~]# [root@node101 ~]# [root@node101 ~]# /etc/init.d/mysql.server stop Shutting down MySQL... SUCCESS! [root@node101 ~]# [root@node101 ~]# [root@node101 ~]# /etc/init.d/mysql.server status ERROR! MySQL is not running [root@node101 ~]#
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node101 ~]# mkdir -pv /yinzhengjie/data [root@node101 ~]# [root@node101 ~]# ll /yinzhengjie/data/ total 0 [root@node101 ~]# [root@node101 ~]# [root@node101 ~]# ll /usr/local/ total 384624 drwxr-xr-x. 2 root root 6 Aug 12 2015 bin drwxr-xr-x. 2 root root 6 Aug 12 2015 etc drwxr-xr-x. 2 root root 6 Aug 12 2015 games drwxr-xr-x. 2 root root 6 Aug 12 2015 include drwxr-xr-x. 2 root root 6 Aug 12 2015 lib drwxr-xr-x. 2 root root 6 Aug 12 2015 lib64 drwxr-xr-x. 2 root root 6 Aug 12 2015 libexec -rw-r--r--. 1 root root 393852364 Jan 15 05:10 mysql-8.0.13-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 Aug 12 2015 sbin drwxr-xr-x. 5 root root 46 Jan 15 05:59 share drwxr-xr-x. 2 root root 6 Aug 12 2015 src [root@node101 ~]# [root@node101 ~]# mv /usr/local/mysql-8.0.13-linux-glibc2.12-x86_64 /yinzhengjie/data/mysql #停止服务后,我们将MySQL的数据目录移动到我们想要修改的指定位置 [root@node101 ~]# [root@node101 ~]# ll /yinzhengjie/data/ total 4 drwxr-xr-x. 10 mysql mysql 4096 Jan 15 16:04 mysql [root@node101 ~]#
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node101 ~]# [root@node101 ~]# cat /etc/my.cnf [mysqld] basedir=/yinzhengjie/data/mysql/ #指定MySQL的安装目录 datadir=/yinzhengjie/data/mysql/data #指定MySQL存放数据的目录 [root@node101 ~]#
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node101 ~]# /etc/init.d/mysql.server start Starting MySQL. SUCCESS! [root@node101 ~]#
以上四个步骤可参考:https://www.cnblogs.com/yinzhengjie/p/10201038.html。
八.来自企业内网 172.30.1.x 网段的应用程序django希望能赋予MySQL数据库实例里的A1数据库的增删改查权限,A2数据库里B2表的查询权限,A3数据库B3表上ID字段的查询权限,这个数据库用户的创建语句是什么? 在授权语句志气啊嘛,我们先把题意的测试数据建立出来,我自己定义一些数据,如下所示:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> CREATE DATABASE A1 CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.01 sec) mysql> mysql> CREATE DATABASE A2 CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> CREATE DATABASE A3 CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.01 sec) mysql> mysql> CREATE TABLE A2.B2(id INT NOT NULL,name VARCHAR(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE A3.B3(id INT NOT NULL,name VARCHAR(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO A3.B3 values(1,'jason'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO A3.B3 values(2,'yinzhengjie'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM A3.B3; +----+-------------+ | id | name | +----+-------------+ | 1 | jason | | 2 | yinzhengjie | +----+-------------+ rows in set (0.00 sec) mysql> mysql> CREATE USER 'django'@'172.30.1.%' IDENTIFIED BY 'yinzhengjie'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +---------------------------------------------+ | Grants for django@172.30.1.% | +---------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | +---------------------------------------------+ 1 row in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +---------------------------------------------+ | Grants for django@172.30.1.% | +---------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | +---------------------------------------------+ row in set (0.00 sec) mysql> mysql> GRANT CREATE,INSERT,DELETE,UPDATE,SELECT ON A1.* TO `django`@`172.30.1.%` WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +-------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | +-------------------------------------------------------------------------------------------+ rows in set (0.00 sec) mysql> 为django@172.30.1.% 用户分配A1数据库的增删改查权限
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +-------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | +-------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> mysql> mysql> GRANT SELECT ON A2.B2 TO `django`@`172.30.1.%` WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +-------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A2`.`B2` TO `django`@`172.30.1.%` WITH GRANT OPTION | +-------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +-------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A2`.`B2` TO `django`@`172.30.1.%` WITH GRANT OPTION | +-------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> mysql> CREATE VIEW A3.v_B3 AS SELECT id FROM A3.B3; Query OK, 0 rows affected (0.00 sec) mysql> mysql> GRANT SELECT ON A3.v_B3 TO `django`@`172.30.1.%`; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +-------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A2`.`B2` TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A3`.`v_B3` TO `django`@`172.30.1.%` | +-------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql>
mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +---------------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +---------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A2`.`B2` TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A3`.`v_B3` TO `django`@`172.30.1.%` | +---------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> mysql>
在执行上述授权操作后,我们需要验证一下是否生效,具体验证过程如下所示:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node105 ~]# mysql -h node110.yinzhengjie.org.cn -udjango -pyinzhengjie mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 8.0.14 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | A1 | | A2 | | A3 | | information_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> USE A1 Database changed mysql> mysql> SHOW TABLES; Empty set (0.00 sec) mysql> mysql> CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(50)); Query OK, 0 rows affected (0.02 sec) mysql> mysql> UPDATE student SET sname = 'yinzhengjie' WHERE sid = 1 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> SELECT * FROM student; +-----+-------------+ | sid | sname | +-----+-------------+ | 1 | yinzhengjie | +-----+-------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> USE A2 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SHOW TABLES; +--------------+ | Tables_in_A2 | +--------------+ | B2 | +--------------+ 1 row in set (0.01 sec) mysql> CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(50)); ERROR 1142 (42000): CREATE command denied to user 'django'@'node105.yinzhengjie.org.cn' for table 'student' mysql> mysql> DESC B2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql> SELECT * FROM B2; Empty set (0.00 sec) mysql> mysql> INSERT INTO B2 VALUES(1,'jason'); ERROR 1142 (42000): INSERT command denied to user 'django'@'node105.yinzhengjie.org.cn' for table 'B2' mysql> mysql> mysql> USE A3; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SHOW TABLES; +--------------+ | Tables_in_A3 | +--------------+ | v_B3 | +--------------+ 1 row in set (0.00 sec) mysql> mysql> CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(50)); ERROR 1142 (42000): CREATE command denied to user 'django'@'node105.yinzhengjie.org.cn' for table 'student' mysql> mysql> SELECT * FROM v_B3; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec) mysql> mysql> INSERT INTO v_B3 VALUES(100); ERROR 1142 (42000): INSERT command denied to user 'django'@'node105.yinzhengjie.org.cn' for table 'v_B3' mysql> mysql>
我们继续以该题为例,请说明这个用户那些系统权限表中会有数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM mysql.user WHERE user='django' AND host='172.30.1.%'\G *************************** 1. row *************************** Host: 172.30.1.% User: django Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED password_expired: N password_last_changed: 2019-01-22 05:41:42 password_lifetime: NULL account_locked: Y Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL 1 row in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM mysql.db WHERE user='django' AND host='172.30.1.%'\G *************************** 1. row *************************** Host: 172.30.1.% Db: A1 User: django Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: N Grant_priv: Y References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N 1 row in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM mysql.tables_priv WHERE user='django' AND host='172.30.1.%'\G *************************** 1. row *************************** Host: 172.30.1.% Db: A2 User: django Table_name: B2 Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Grant Column_priv: *************************** 2. row *************************** Host: 172.30.1.% Db: A3 User: django Table_name: v_B3 Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select Column_priv: 2 rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM mysql.columns_priv WHERE user='django' AND host='172.30.1.%'\G Empty set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM mysql.procs_priv WHERE user='django' AND host='172.30.1.%'\G Empty set (0.00 sec) mysql>
如果要回收该用户在A2数据库里B2表的查询权限,SQL语句应该怎么写?
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +---------------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +---------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A2`.`B2` TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A3`.`v_B3` TO `django`@`172.30.1.%` | +---------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> mysql> REVOKE SELECT ON A2.B2 FROM `django`@`172.30.1.%` ; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SHOW GRANTS FOR 'django'@'172.30.1.%'; +---------------------------------------------------------------------------------------------------+ | Grants for django@172.30.1.% | +---------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `django`@`172.30.1.%` | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `A1`.* TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT USAGE ON `A2`.`B2` TO `django`@`172.30.1.%` WITH GRANT OPTION | | GRANT SELECT ON `A3`.`v_B3` TO `django`@`172.30.1.%` | +---------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql>
九.外键的作用是什么?请举例说明。
外键用于与另一张表的关联。是能确定另一张表记录的字段,保持数据的一致性、完整性。详情请参考:https://www.cnblogs.com/yinzhengjie/p/10285553.html。
十.Insert on duplicate key update语句的作用是什么?
当INSERT语句中使用ON DUPLICATE KEY UPDATE子句时,如果碰到当前插入的数据违反主键或者唯一性约束,则INSERT会转变成UPDATE语句修改对应依旧存在表中的这条数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM student_primary WHERE stu_id < 10; +--------+----------+--------+ | stu_id | stu_name | gender | +--------+----------+--------+ | 1 | json | 10 | | 2 | Danny | 20 | | 3 | 胡歌 | 30 | +--------+----------+--------+ rows in set (0.00 sec) mysql> mysql> INSERT INTO student_primary(stu_id,stu_name,gender) VALUES(1,'尹正杰',18) ON DUPLICATE KEY UPDATE gender=gender+2; #注意,我们修改stu_id这个字段为1的这行数据已经存在,因此它只修改已经存在的字段对应的值! Query OK, 2 rows affected (0.00 sec) mysql> mysql> SELECT * FROM student_primary WHERE stu_id < 10; +--------+----------+--------+ | stu_id | stu_name | gender | +--------+----------+--------+ | 1 | json | 12 | | 2 | Danny | 20 | | 3 | 胡歌 | 30 | +--------+----------+--------+ rows in set (0.00 sec) mysql> mysql> UPDATE student_primary SET gender=gender+2 WHERE stu_id = 1; #上面的那条语句很明显只是修改已经存在表中的那条数据,它和咱们这条执行语句结果相同! Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> SELECT * FROM student_primary WHERE stu_id < 10; +--------+----------+--------+ | stu_id | stu_name | gender | +--------+----------+--------+ | 1 | json | 14 | | 2 | Danny | 20 | | 3 | 胡歌 | 30 | +--------+----------+--------+ rows in set (0.00 sec) mysql>
十一.主键primary key和唯一键unique key的区别是什么?
PRIMARY KEY:表示该字段为主键,主键字段必须唯一,必须非空,一个表中只能有一个主键,主键可以包含一个或者多个字段。
Unique KEY表示该字段为唯一属性字段,且允许包含多个NULL值。
十二.请解释create table … as select语句和create table … like语句的区别
CREATE TABLE ... AS SELECT 语句:表示创建表的同时将SELECT的查询结果数据插入到表中,但索引和主外键信息都不会同步过来
CREATE TABLE ... LIKE 语句:表示基于另外一个表的定义复制一个新的空表,空表时尚的字段属性和索引都和原表相同。
十三.清空表数据的SQL语句有哪几种?有什么区别?
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
drop是DDL,会隐式提交,所以,不能回滚,不会触发触发器。 drop语句删除表结构及所有数据,并将表所占用的空间全部释放。 drop语句将删除表的结构所依赖的约束,触发器,索引,依赖于该表的存储过程/函数将保留,但是变为invalid状态。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
truncate是DDL,会隐式提交,所以,不能回滚,不会触发触发器。 truncate会删除表中所有记录,并且将重新设置高水线和所有的索引,缺省情况下将空间释放到minextents个extent,除非使用reuse storage,。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作(如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复)。 对于外键(foreignkey )约束引用的表,不能使用 truncate table,而应使用不带 where 子句的 delete 语句。 truncate table不能用于参与了索引视图的表。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delete是DML,执行delete操作时,每次从表中删除一行,并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,但要注意表空间要足够大,需要手动提交(commit)操作才能生效,可以通过rollback撤消操作。 delete可根据条件删除表中满足条件的数据,如果不指定where子句,那么删除表中所有记录。 delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不变。
十四.Union 和union all的区别是什么?
UNION用来将多个SELECT语句的执行结果合并成一个结果。
1>.第一个SELECT语句的COLUMN_NAME会被当做最后查询结果的列名,接下来的每一个SELECT语句所一一对应的列应该和第一个语句的列类型最好保持一致。
2>.默认情况下UNION语句会把最终结果中的重复行去掉,这和增加DISTINCT这个关键词的作用一样,如果使用UNION ALL则代表最终结果中的重复行保留。
3>.如果相对UNION语句的最后结果做排序或者LIMIT限制,则需要将每个SELECT语句用括号括起来,把ORDER BY或LIMIT语句放在最后。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ rows in set (0.00 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ rows in set (0.00 sec) mysql> mysql> SELECT * FROM student UNION SELECT * FROM teacher; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+------------+ rows in set (0.01 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+------------+ rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+------------+ rows in set (0.00 sec) mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher ORDER BY id LIMIT 10; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 谢霆锋 | 11 | | 1 | 尹正杰 | 1 | | 2 | 周杰伦 | 1 | | 2 | 耿宇星 | 2 | | 3 | 蔡依林 | 13 | | 3 | 陈飞 | 3 | | 4 | 杨幂 | 2 | | 4 | 彭兴旭 | 4 | | 5 | 胡歌 | 12 | | 5 | 李慧鹏 | 5 | +----+-----------+------------+ rows in set (0.00 sec) mysql>
十五.举例常用的聚合函数
聚合函数通常用在存在GROUP BY子句的语句中。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> USE yinzhengjie; Database changed mysql> mysql> CREATE TABLE score_graph( -> id INT(11) PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(30), -> subject VARCHAR(30), -> score INT(3) -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','英语',120); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','语文',118); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','数学',120); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','物理',110); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','化学',112); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','生物',108); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','语文',120); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','英语',110); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','数学',115); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','物理',105); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','化学',102); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','生物',98); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','语文',116); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','数学',112); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','英语',101); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','物理',103); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','化学',112); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','生物',78); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ rows in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ rows in set (0.01 sec) mysql> mysql> SELECT name,AVG(score) FROM score_graph GROUP BY name; +-----------+------------+ | name | AVG(score) | +-----------+------------+ | 尹正杰 | 114.6667 | | jason | 108.3333 | | 耿宇星 | 103.6667 | +-----------+------------+ rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ rows in set (0.00 sec) mysql> mysql> SELECT name,COUNT(*) FROM score_graph GROUP BY name; +-----------+----------+ | name | COUNT(*) | +-----------+----------+ | 尹正杰 | 6 | | jason | 6 | | 耿宇星 | 6 | +-----------+----------+ rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ rows in set (0.00 sec) mysql> mysql> SELECT COUNT(DISTINCT id) FROM score_graph; +--------------------+ | COUNT(DISTINCT id) | +--------------------+ | 18 | +--------------------+ row in set (0.00 sec) mysql> mysql> SELECT COUNT(DISTINCT name) FROM score_graph; +----------------------+ | COUNT(DISTINCT name) | +----------------------+ | 3 | +----------------------+ row in set (0.00 sec) mysql> mysql> SELECT COUNT(DISTINCT subject) FROM score_graph; +-------------------------+ | COUNT(DISTINCT subject) | +-------------------------+ | 6 | +-------------------------+ row in set (0.01 sec) mysql> mysql> SELECT COUNT(DISTINCT score) FROM score_graph;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ rows in set (0.00 sec) mysql> mysql> SELECT name,MIN(score),MAX(score) FROM score_graph GROUP BY name; +-----------+------------+------------+ | name | MIN(score) | MAX(score) | +-----------+------------+------------+ | 尹正杰 | 108 | 120 | | jason | 98 | 120 | | 耿宇星 | 78 | 116 | +-----------+------------+------------+ rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ rows in set (0.00 sec) mysql> mysql> SELECT name,SUM(score) FROM score_graph GROUP BY name; +-----------+------------+ | name | SUM(score) | +-----------+------------+ | 尹正杰 | 688 | | jason | 650 | | 耿宇星 | 622 | +-----------+------------+ rows in set (0.00 sec) mysql>
十六.设计一个学生选课数据库系统
course数据库中有以下四张表:
•students表(学生表):
sid整型自增主键,sname字符串64位,gender字符串12位,dept_id整型并外键到dept表的id字段
•dept表(系表):
id整型自增主键,dept_name字符串64位
•course表(课程表):
id整型自增字段主键,course_name字符串64位,teacher_id整型外键到teacher表的id字段
•teacher表(老师表):
id整型自增字段主键,name字符串64位,dept_id整型外键到dept表的id字段
1>.将上述的数据库与表创建出来并往表中插入相关测试数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> CREATE DATABASE course CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> USE course; Database changed mysql> mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | course | +------------+ 1 row in set (0.00 sec) mysql> mysql> CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR(64)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE students( -> sid INT PRIMARY KEY AUTO_INCREMENT, -> sname VARCHAR(64), -> gender VARCHAR(12), -> dept_id INT NOT NULL, -> CONSTRAINT student_dept FOREIGN KEY(dept_id) REFERENCES dept(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> CREATE TABLE teacher( -> id INT PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(64), -> dept_id INT NOT NULL, -> CONSTRAINT teacher_dept FOREIGN KEY(dept_id) REFERENCES dept(id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE course( -> id INT PRIMARY KEY AUTO_INCREMENT, -> course_name VARCHAR(64), -> teacher_id INT, -> CONSTRAINT course_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW TABLES; +------------------+ | Tables_in_course | +------------------+ | course | | dept | | students | | teacher | +------------------+ 4 rows in set (0.00 sec) mysql> mysql> 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.00 sec) mysql> mysql> mysql> mysql> DESC dept; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | demt_name | varchar(64) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> mysql> DESC students; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | sid | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(64) | YES | | NULL | | | gender | varchar(12) | YES | | NULL | | | dept_id | int(11) | NO | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> mysql> DESC teacher; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | dept_id | int(11) | NO | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> DESC dept; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | demt_name | varchar(64) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> mysql> mysql> INSERT INTO dept(demt_name) VALUES('语文'),('数学'),('英语'),('物理'),('化学'),('生物'),('政治'),('历史'),('地理'),('音乐'),('美术'),('计算机'),('体育'); Query OK, 13 rows affected (0.00 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM dept; +----+-----------+ | id | demt_name | +----+-----------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | | 4 | 物理 | | 5 | 化学 | | 6 | 生物 | | 7 | 政治 | | 8 | 历史 | | 9 | 地理 | | 10 | 音乐 | | 11 | 美术 | | 12 | 计算机 | | 13 | 体育 | +----+-----------+ 13 rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> DESC teacher; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | dept_id | int(11) | NO | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> mysql> INSERT INTO teacher(name,dept_id) VALUES('尤塞恩·博尔特',13),('米芾',11),('顾颉刚',9),('林肯',7),('德米特里·伊万诺维奇·门捷列夫',5),('马云',3),('王羲之',1),('高斯',2),('史蒂芬·威廉·霍金',4),('达尔文',6),('司马光',8),('贝多芬',10),('查尔斯·巴贝奇',12); Query OK, 13 rows affected (0.00 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM teacher; +----+---------------------------------------------+---------+ | id | name | dept_id | +----+---------------------------------------------+---------+ | 1 | 尤塞恩·博尔特 | 13 | | 2 | 米芾 | 11 | | 3 | 顾颉刚 | 9 | | 4 | 林肯 | 7 | | 5 | 德米特里·伊万诺维奇·门捷列夫 | 5 | | 6 | 马云 | 3 | | 7 | 王羲之 | 1 | | 8 | 高斯 | 2 | | 9 | 史蒂芬·威廉·霍金 | 4 | | 10 | 达尔文 | 6 | | 11 | 司马光 | 8 | | 12 | 贝多芬 | 10 | | 13 | 查尔斯·巴贝奇 | 12 | +----+---------------------------------------------+---------+ 13 rows in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> DESC students; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | sid | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(64) | YES | | NULL | | | gender | varchar(12) | YES | | NULL | | | dept_id | int(11) | NO | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> mysql> INSERT INTO students(sname,gender,dept_id) VALUES('蔡依林','girl',10),('甄子丹','boy',13),('周杰伦','boy',10),('林心如','girl',4),('杨幂','girl',2),('周润发','boy',5),('王宝强','boy',9),('杨紫','girl',11),('尹正杰','boy',8),('尹正杰','boy',11),('成龙','boy',13),('李连杰','boy',13),('邓紫棋','girl',10),('周星驰','boy',4),('尹正杰','boy',12),('刘亦菲','girl',7); Query OK, 16 rows affected (0.01 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.00 sec) mysql> mysql> INSERT INTO course(course_name,teacher_id) VALUES('PE_lesson',1),('Art_lesson',2),('Geograghy_lesson',3),('Political_lesson',4),('Chemistry_lesson',5),('English_lesson',6),('Chinese_lesson',7),('Computer_lesson',13),('Music_lesson',12),('History_lesson',11),('Biology_lesson',10),('Physics',9),('Mathematics_lesson',8); Query OK, 13 rows affected (0.01 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM course; +----+--------------------+------------+ | id | course_name | teacher_id | +----+--------------------+------------+ | 1 | PE_lesson | 1 | | 2 | Art_lesson | 2 | | 3 | Geograghy_lesson | 3 | | 4 | Political_lesson | 4 | | 5 | Chemistry_lesson | 5 | | 6 | English_lesson | 6 | | 7 | Chinese_lesson | 7 | | 8 | Computer_lesson | 13 | | 9 | Music_lesson | 12 | | 10 | History_lesson | 11 | | 11 | Biology_lesson | 10 | | 12 | Physics | 9 | | 13 | Mathematics_lesson | 8 | +----+--------------------+------------+ 13 rows in set (0.00 sec) mysql> mysql>
2>.在第1步时我们已经创建的students, dept, teacher ,course四个表及测试数据,现需要查看每个系里面所有的老师name和对应的学生的sname,结果按照dept_id升序排序,请写出SQL。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT sname,name,t.dept_id FROM students AS s INNER JOIN teacher AS t ON s.dept_id = t.dept_id ORDER BY s.dept_id ASC; +-----------+---------------------------------------------+---------+ | sname | name | dept_id | +-----------+---------------------------------------------+---------+ | 杨幂 | 高斯 | 2 | | 林心如 | 史蒂芬·威廉·霍金 | 4 | | 周星驰 | 史蒂芬·威廉·霍金 | 4 | | 周润发 | 德米特里·伊万诺维奇·门捷列夫 | 5 | | 刘亦菲 | 林肯 | 7 | | 尹正杰 | 司马光 | 8 | | 王宝强 | 顾颉刚 | 9 | | 周杰伦 | 贝多芬 | 10 | | 邓紫棋 | 贝多芬 | 10 | | 蔡依林 | 贝多芬 | 10 | | 杨紫 | 米芾 | 11 | | 尹正杰 | 米芾 | 11 | | 尹正杰 | 查尔斯·巴贝奇 | 12 | | 甄子丹 | 尤塞恩·博尔特 | 13 | | 成龙 | 尤塞恩·博尔特 | 13 | | 李连杰 | 尤塞恩·博尔特 | 13 | +-----------+---------------------------------------------+---------+ 16 rows in set (0.00 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT sname,name,t.dept_id FROM students AS s INNER JOIN teacher AS t ON s.dept_id = t.dept_id ORDER BY s.dept_id DESC; +-----------+---------------------------------------------+---------+ | sname | name | dept_id | +-----------+---------------------------------------------+---------+ | 甄子丹 | 尤塞恩·博尔特 | 13 | | 成龙 | 尤塞恩·博尔特 | 13 | | 李连杰 | 尤塞恩·博尔特 | 13 | | 尹正杰 | 查尔斯·巴贝奇 | 12 | | 杨紫 | 米芾 | 11 | | 尹正杰 | 米芾 | 11 | | 周杰伦 | 贝多芬 | 10 | | 邓紫棋 | 贝多芬 | 10 | | 蔡依林 | 贝多芬 | 10 | | 王宝强 | 顾颉刚 | 9 | | 尹正杰 | 司马光 | 8 | | 刘亦菲 | 林肯 | 7 | | 周润发 | 德米特里·伊万诺维奇·门捷列夫 | 5 | | 林心如 | 史蒂芬·威廉·霍金 | 4 | | 周星驰 | 史蒂芬·威廉·霍金 | 4 | | 杨幂 | 高斯 | 2 | +-----------+---------------------------------------------+---------+ 16 rows in set (0.00 sec) mysql>
3>.用两个SQL分别求出每个系的学生个数和每个系里的老师个数?如果只用一个SQL语句实现怎么写?
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT d.demt_name, COUNT(*) AS 人数 FROM students AS s INNER JOIN dept AS d WHERE s.dept_id = d.id GROUP BY dept_id ; +-----------+--------+ | demt_name | 人数 | +-----------+--------+ | 数学 | 1 | | 物理 | 2 | | 化学 | 1 | | 政治 | 1 | | 历史 | 1 | | 地理 | 1 | | 音乐 | 3 | | 美术 | 2 | | 计算机 | 1 | | 体育 | 3 | +-----------+--------+ 10 rows in set (0.01 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT d.demt_name, COUNT(*) AS 人数 FROM teacher AS t INNER JOIN dept AS d WHERE t.dept_id = d.id GROUP BY dept_id ; +-----------+--------+ | demt_name | 人数 | +-----------+--------+ | 语文 | 1 | | 数学 | 1 | | 英语 | 1 | | 物理 | 1 | | 化学 | 1 | | 生物 | 1 | | 政治 | 1 | | 历史 | 1 | | 地理 | 1 | | 音乐 | 1 | | 美术 | 1 | | 计算机 | 1 | | 体育 | 1 | +-----------+--------+ 13 rows in set (0.01 sec) mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT dept_id,COUNT(*) FROM students GROUP BY dept_id; +---------+----------+ | dept_id | COUNT(*) | +---------+----------+ | 2 | 1 | | 4 | 2 | | 5 | 1 | | 7 | 1 | | 8 | 1 | | 9 | 1 | | 10 | 3 | | 11 | 2 | | 12 | 1 | | 13 | 3 | +---------+----------+ 10 rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT dept_id,COUNT(*) FROM teacher GROUP BY dept_id; +---------+----------+ | dept_id | COUNT(*) | +---------+----------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 5 | 1 | | 6 | 1 | | 7 | 1 | | 8 | 1 | | 9 | 1 | | 10 | 1 | | 11 | 1 | | 12 | 1 | | 13 | 1 | +---------+----------+ 13 rows in set (0.00 sec) mysql> mysql>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT a.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; +---------+-----------------------+----------------------+ | dept_id | COUNT(distinct b.sid) | COUNT(distinct a.id) | +---------+-----------------------+----------------------+ | 2 | 1 | 1 | | 4 | 2 | 1 | | 5 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | | 9 | 1 | 1 | | 10 | 3 | 1 | | 11 | 2 | 1 | | 12 | 1 | 1 | | 13 | 3 | 1 | +---------+-----------------------+----------------------+ 10 rows in set (0.00 sec) mysql> mysql>
4>.通过老师表和课程表求出每个老师的课程个数,并且需要保证如果老师没有课程的时候则要显示课程为0。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT a.name,COUNT(*),COUNT(b.id) FROM teacher a LEFT JOIN course b ON a.id=b.teacher_id GROUP BY a.name; +---------------------------------------------+----------+-------------+ | name | COUNT(*) | COUNT(b.id) | +---------------------------------------------+----------+-------------+ | 尤塞恩·博尔特 | 1 | 1 | | 米芾 | 1 | 1 | | 顾颉刚 | 1 | 1 | | 林肯 | 1 | 1 | | 德米特里·伊万诺维奇·门捷列夫 | 1 | 1 | | 马云 | 1 | 1 | | 王羲之 | 1 | 1 | | 高斯 | 1 | 1 | | 史蒂芬·威廉·霍金 | 1 | 1 | | 达尔文 | 1 | 1 | | 司马光 | 1 | 1 | | 贝多芬 | 1 | 1 | | 查尔斯·巴贝奇 | 1 | 1 | +---------------------------------------------+----------+-------------+ 13 rows in set (0.00 sec) mysql> mysql>
5>.查看学生信息表里按照sid升序排序后的第7到第15行的学生数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM students ORDER BY sid ASC LIMIT 6,9; +-----+-----------+--------+---------+ | sid | sname | gender | dept_id | +-----+-----------+--------+---------+ | 7 | 王宝强 | boy | 9 | | 8 | 杨紫 | girl | 11 | | 9 | 尹正杰 | boy | 8 | | 10 | 尹正杰 | boy | 11 | | 11 | 成龙 | boy | 13 | | 12 | 李连杰 | boy | 13 | | 13 | 邓紫棋 | girl | 10 | | 14 | 周星驰 | boy | 4 | | 15 | 尹正杰 | boy | 12 | +-----+-----------+--------+---------+ 9 rows in set (0.00 sec) mysql> mysql>
6>.使用select into outfile将teacher表里的数据导出,注意字段之间用;号隔开,字符串用”号隔开
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT * FROM teacher; +----+---------------------------------------------+---------+ | id | name | dept_id | +----+---------------------------------------------+---------+ | 1 | 尤塞恩·博尔特 | 13 | | 2 | 米芾 | 11 | | 3 | 顾颉刚 | 9 | | 4 | 林肯 | 7 | | 5 | 德米特里·伊万诺维奇·门捷列夫 | 5 | | 6 | 马云 | 3 | | 7 | 王羲之 | 1 | | 8 | 高斯 | 2 | | 9 | 史蒂芬·威廉·霍金 | 4 | | 10 | 达尔文 | 6 | | 11 | 司马光 | 8 | | 12 | 贝多芬 | 10 | | 13 | 查尔斯·巴贝奇 | 12 | | 14 | 尹正杰 | 12 | +----+---------------------------------------------+---------+ 14 rows in set (0.00 sec) mysql> mysql> mysql> SELECT id,name,dept_id INTO OUTFILE '/yinzhengjie/backup/teacher.bak' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM teacher; Query OK, 14 rows affected (0.00 sec) mysql> quit Bye [root@node110 ~]# [root@node110 ~]# cat /yinzhengjie/backup/teacher.bak 1;"尤塞恩·博尔特";13 2;"米芾";11 3;"顾颉刚";9 4;"林肯";7 5;"德米特里·伊万诺维奇·门捷列夫";5 6;"马云";3 7;"王羲之";1 8;"高斯";2 9;"史蒂芬·威廉·霍金";4 10;"达尔文";6 11;"司马光";8 12;"贝多芬";10 13;"查尔斯·巴贝奇";12 14;"尹正杰";12 [root@node110 ~]#
7>.请创建一个包含每个老师姓名和对应的课程个数,且只显示所教课程个数在2个以上的视图view
mysql> CREATE VIEW view_course AS SELECT a.name,COUNT(*) count1 FROM teacher a INNER JOIN course b ON a.id=b.teacher_id GROUP BY a.name HAVING COUNT(*)>1; Query OK, 0 rows affected (0.01 sec) mysql>