数据库基础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 - 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>

想要查看验证的详细内容，请使劲儿的戳我～

　　我们继续以该题为例，请说明这个用户那些系统权限表中会有数据。

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: 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>

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: 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>

往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: 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>

往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: 0

mysql>

往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: 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>

往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(*) |
+---------+----------+
|       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>

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(*) |
+---------+----------+
|       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>

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) |
+---------+-----------------------+----------------------+
|       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>

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) |
+---------------------------------------------+----------+-------------+
| 尤塞恩·博尔特                               |        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>

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(*)>1;
Query OK, 0 rows affected (0.01 sec)

mysql>

posted @ 2019-01-04 23:33 尹正杰阅读(2014) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

尹正杰

数据库基础SQL知识面试题一

公告