正文
数据库基础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个步骤。具体操作如下：
[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 ~]#
第一步我们需要停止MySQL服务（[root@node101 ~]# /etc/init.d/mysql.server stop）
[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 ~]#
第二步就是将需要转移的磁盘挂在到当前的操作系统并将mysql 的数据目前迁移过去（[root@node101 ~]# mv /usr/local/mysql-8.0.13-linux-glibc2.12-x86_64 /yinzhengjie/data/mysql#停止服务后，我们将MySQL的数据目录移动到我们想要修改的指定位置）
[root@node101 ~]#
[root@node101 ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/data/mysql/#指定MySQL的安装目录
datadir=/yinzhengjie/data/mysql/data#指定MySQL存放数据的目录
[root@node101 ~]#
第三步我们只需要在mysql的启动文件中指定当前的mysql数据目录路径即可（[root@node101 ~]# cat /etc/my.cnf）
[root@node101 ~]# /etc/init.d/mysql.server start
Starting MySQL. SUCCESS!
[root@node101 ~]#
第四步启动MySQL服务([root@node101 ~]# /etc/init.d/mysql.server start)
以上四个步骤可参考：https://www.cnblogs.com/yinzhengjie/p/10201038.html。
八.来自企业内网 172.30.1.x 网段的应用程序django希望能赋予MySQL数据库实例里的A1数据库的增删改查权限，A2数据库里B2表的查询权限，A3数据库B3表上ID字段的查询权限，这个数据库用户的创建语句是什么？ 在授权语句志气啊嘛，我们先把题意的测试数据建立出来，我自己定义一些数据，如下所示：
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>
定义测试数据，戳我查看测试数据的详细内容～
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数据库的增删改查权限
为django@172.30.1.% 用户分配A1数据库的增删改查权限
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>
为django@172.30.1.% 用户A2数据库里B2表的查询权限
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>
为django@172.30.1.% 用户分配A3数据库B3表上ID字段的查询权限
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>
在执行上述授权操作后，我们需要验证一下是否生效，具体验证过程如下所示：
[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 - GPLCopyright (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: 0mysql>
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 -ADatabase 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 -ADatabase 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>
想要查看验证的详细内容，请使劲儿的戳我～
我们继续以该题为例，请说明这个用户那些系统权限表中会有数据。
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>
首先‘django’@'172.30.1.%' 用户在mysql.user表中的增删改查权限，基本上都是“N”（SELECT * FROM mysql.user WHERE user='django' AND host='172.30.1.%'\G ）
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>
在mysql.db表中拥有增删改查以及授权操作，其他的基本上都是“N”（mysql> SELECT * FROM mysql.db WHERE user='django' AND host='172.30.1.%'\G ）
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>
在mysql.tables_priv表中可以看到对A2对B2表拥有Select和Grant权限，对A3数据库对v_B3拥有Select权限（mysql> SELECT * FROM mysql.tables_priv WHERE user='django' AND host='172.30.1.%'\G ）
mysql> SELECT * FROM mysql.columns_priv WHERE user='django' AND host='172.30.1.%'\G
Empty set (0.00 sec)mysql>
在mysql.columns表中是无权限的（mysql> SELECT * FROM mysql.columns_priv WHERE user='django' AND host='172.30.1.%'\G ）
mysql> SELECT * FROM mysql.procs_priv WHERE user='django' AND host='172.30.1.%'\G
Empty set (0.00 sec)mysql>
在mysql.procs_priv表中是无权限的（mysql> SELECT * FROM mysql.procs_priv WHERE user='django' AND host='172.30.1.%'\G ）
如果要回收该用户在A2数据库里B2表的查询权限，SQL语句应该怎么写？
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>
mysql> REVOKE SELECT ON A2.B2 FROM `django`@`172.30.1.%` ;
九.外键的作用是什么？请举例说明。
外键用于与另一张表的关联。是能确定另一张表记录的字段，保持数据的一致性、完整性。详情请参考：https://www.cnblogs.com/yinzhengjie/p/10285553.html。
十.Insert on duplicate key update语句的作用是什么？
当INSERT语句中使用ON DUPLICATE KEY UPDATE子句时，如果碰到当前插入的数据违反主键或者唯一性约束，则INSERT会转变成UPDATE语句修改对应依旧存在表中的这条数据。
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: 0mysql>
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>
mysql> INSERT INTO student_primary(stu_id,stu_name,gender) VALUES(1,'尹正杰',18) ON DUPLICATE KEY UPDATE gender=gender+2;
十一.主键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语句有哪几种？有什么区别？
drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。drop语句删除表结构及所有数据，并将表所占用的空间全部释放。drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。
DROP表后在使用CREATE将该表创建出来
truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。truncate table不能用于参与了索引视图的表。
TRUNCATE可以快速清空一张表的数据
delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。
使用DELETE语句逐条删除表中的数据
十四.Union 和union all的区别是什么？
UNION用来将多个SELECT语句的执行结果合并成一个结果。
1>.第一个SELECT语句的COLUMN_NAME会被当做最后查询结果的列名，接下来的每一个SELECT语句所一一对应的列应该和第一个语句的列类型最好保持一致。
2>.默认情况下UNION语句会把最终结果中的重复行去掉，这和增加DISTINCT这个关键词的作用一样，如果使用UNION ALL则代表最终结果中的重复行保留。
3>.如果相对UNION语句的最后结果做排序或者LIMIT限制，则需要将每个SELECT语句用括号括起来，把ORDER BY或LIMIT语句放在最后。
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>
mysql> SELECT * FROM student UNION SELECT * FROM teacher;
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>
mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher;
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>
mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher ORDER BY id LIMIT 10;
十五.举例常用的聚合函数
聚合函数通常用在存在GROUP BY子句的语句中。
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>
创建测试数据（mysql> CREATE TABLE score_graph( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), subject VARCHAR(30), score INT(3) );）
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>
AVG([DISTINCT] expr) 计算expr的平均值，distinct关键词表示是否排除重复值
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>
COUNT(expr) 计算expr中的个数，如果没有匹配则返回0，注意NULL的区别
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;
COUNT(DISTINCT expr,[expr...]) 计算有多少个不重复的expr值，注意是计算非NULL的个数
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>
MAX([DISTINCT] expr),MIN([DISTINCT] expr) 返回expr中最大或者最小的值
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>
SUM([DISTINCT] expr) 返回expr的求和值
十六.设计一个学生选课数据库系统
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>.将上述的数据库与表创建出来并往表中插入相关测试数据
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>
创建数据库与对应的表结构
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: 0mysql> 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>
往dept表中插入测试数据
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: 0mysql>
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>
往teacher表中插入数据
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: 0mysql>
往student表中插入数据
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: 0mysql> 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>
往course表中插入数据
2>.在第1步时我们已经创建的students, dept, teacher ,course四个表及测试数据，现需要查看每个系里面所有的老师name和对应的学生的sname，结果按照dept_id升序排序，请写出SQL。
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>
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;#升序
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>
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;#降序
3>.用两个SQL分别求出每个系的学生个数和每个系里的老师个数？如果只用一个SQL语句实现怎么写？
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>
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 ;
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>
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 ;
mysql> SELECT dept_id,COUNT(*) FROM students GROUP BY dept_id;
+---------+----------+
| dept_id | COUNT(*) |
+---------+----------+
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
+---------+----------+
rows in set (0.00 sec)mysql>
mysql>
mysql> SELECT dept_id,COUNT(*) FROM students GROUP BY dept_id;
mysql> SELECT dept_id,COUNT(*) FROM teacher GROUP BY dept_id;
+---------+----------+
| dept_id | COUNT(*) |
+---------+----------+
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
+---------+----------+
rows in set (0.00 sec)mysql>
mysql>
mysql> SELECT dept_id,COUNT(*) FROM teacher GROUP BY dept_id;
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) |
+---------+-----------------------+----------------------+
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
+---------+-----------------------+----------------------+
rows in set (0.00 sec)mysql>
mysql>
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;
4>.通过老师表和课程表求出每个老师的课程个数，并且需要保证如果老师没有课程的时候则要显示课程为0。
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) |
+---------------------------------------------+----------+-------------+
| 尤塞恩·博尔特 | | |
| 米芾 | | |
| 顾颉刚 | | |
| 林肯 | | |
| 德米特里·伊万诺维奇·门捷列夫 | | |
| 马云 | | |
| 王羲之 | | |
| 高斯 | | |
| 史蒂芬·威廉·霍金 | | |
| 达尔文 | | |
| 司马光 | | |
| 贝多芬 | | |
| 查尔斯·巴贝奇 | | |
+---------------------------------------------+----------+-------------+
rows in set (0.00 sec)mysql>
mysql>
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;
5>.查看学生信息表里按照sid升序排序后的第7到第15行的学生数据
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>
mysql> SELECT * FROM students ORDER BY sid ASC LIMIT 6,9;
6>.使用select into outfile将teacher表里的数据导出，注意字段之间用;号隔开，字符串用”号隔开
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 ~]#
mysql> SELECT id,name,dept_id INTO OUTFILE '/yinzhengjie/backup/teacher.bak' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM teacher;
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(*)>;
Query OK, rows affected (0.01 sec)mysql>
