[Study]MySQL

一、MySQL 概述

1. MySQL 数据库表中一行数据称为一条记录，一列数据称为一个字段。对应到 Java 程序中，一行记录往往使用一个对象表示

2. SQL语句分类：

   • DDL（数据定义语句）[DEFINE]
   • DML（数据操作语句）[INSERT、UPDATE、DELETE]
   • DQL（数据查询语句）(SELECT)
   • DCL（数据控制语句）[GRANT REVOKE]

3. NoSQL 的发展历史：

   • 1970：NoSQL = We have no SQL
   • 1980：NoSQL = Know SQL
   • 2000：NoSQL = No SQL！
   • 2005：NoSQL = Not only SQL
   • 2013：NoSQL = No，SQL！

4. 数据库的三种模型：

   • 层次模型

     

   • 网状模型

     

   • 关系模型：

5. MySQL 的安装与配置：

   • MySQL Client 的可执行程序是 mysql，MySQL Server 的可执行程序是 mysqld（3306）

   • my.ini 配置文件：

     [mysqld]
     port=3306
     max_connections=200
     max_connect_errors=10
     character-set-server=utf8
     basedir=D:\Software\MySQL\mysql-8.0.26
     datadir=D:\Software\MySQL\mysql-8.0.26\data
     default_authentication_plugin=mysql_native_password
     [mysql]
     default-character-set=utf8
     [client]
     port=3306
     default-character-set=utf8
     

   • 常见安装配置命令：

     命令	功能
     mysqld --initialize --console	初始化 MySQL
     mysqld --install mysql	安装 MySQL 服务
     net start mysql	启动服务
     net stop mysql	停止服务
     mysql -h 主机名 -P 端口 -u 用户名 -p 密码	登录 MySQL
     sc delete mysql	删除 MySQL 服务

二、数据库与表

2.1 数据库管理

1. 创建数据库：

   -- 语法格式
   CREATE DATABASE [IF NOT EXISTS] db_name [create_specification]···
   -- 指定数据库采用的字符集，默认为 utf8
   [DEFAULT] CHARACTER SET char_set_name
   -- 指定数据库字符集的校对规则，默认为 utf8_general_ci(不区分大小写)，常用的还有 utf8_bin(区分大小写)
   [DEFAULT] COLLATE collation_name
   

2. 管理数据库：

   -- 显示所有数据库
   SHOW DATABASES;
   -- 显示数据库的创建语句
   SHOW CREATE DATABASE db_name
   -- 删除数据库
   DROP DATABASE [IF EXISTS] db_name
   

3. 数据库的备份与恢复：

   -- 备份数据库：在 DOS 命令行执行（文件名前可带路径）
   mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
   -- 备份表：在 DOS 命令行执行（文件名前可带路径）
   mysqldump -u 用户名 -p 数据库 表1 表2 表n > 文件名.sql
   -- 恢复数据库：登录 MySQL 后在 MySQL 命令行执行（文件名前可带路径）
   Source 文件名.sql
   

2.2 数据类型

1. MySQL 字符集编码存储 VARCHAR 的区别：
   • Unicode 编码中一个字符占 3 个字节，所以 VARCHAR(size) 字符串 UTF8 编码时最大存放 21844 个字符，需要用 1~3 个字节用于记录字段大小即（65535 字节 - 3 字节）/ 3 字节每字符 )
   • GBK 编码中一个字符占 2 个字节，所以 VARCHAR(size) 字符串 GBK 编码时最大存放 32766 个字符，需要用 1~3 个字节记录字段大小即（65535 字节 - 3 字节）/ 2 字节每字符 )
2. CHAR 与 VARCHAR 的对比：
   • 含义不同：CHAR(4) 数字 4 表示 字符数，不管是中文还是英文都存放 4 个；VARCHAR(4) 数字 4 表示 字节数，不管是字符还是中文都以定义好的表的编码来存放数据
   • 空间占用不同：CHAR(4) 所谓定长指的是分配的空间是固定的，仅仅插入了 “aa” 两个字符但占用的空间也是 4 个字符的空间；VARCHAR(4) 所谓变长指的是占用的空间是动态大小，即根据实际占用的空间大小进行分配。VARCHAR 本身还需要占用 1~3 个字节来存放内容长度信息
3. 文本存储：在存放文本时，也可以使用 TEXT 数据类型，TEXT 不能有默认值，大小范围为 [0,2¹⁶-1] 字节。如果希望存放更多字符，可以使用 MEDIUMTEXT[0,2²⁴-1] 或 LONGTEXT [0,2³²-1]

2.3 表管理

1. 创建表：

   • 为了规避关键字，可以使用反引号 `` 解决
   • 通常情况下，字段应避免允许为 NULL。不允许为 NULL 可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为 NULL

   CREATE TABLE table_name (
    `field1` datatype NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `field2` datatype,
    `fieldn` datatype
   ) CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 引擎； 
   
   -- 复制表结构
   CREATE TABLE `table_name` LIKE `table`
   

2. 管理表：

   -- 修改表的字符集
   ALTER TABLE `table_name` CHARSET character_set_name
   -- 查看表结构
   DESC `table_name`
   -- 重命名表名
   RENAME TABLE old_table_name TO new_table_name
   

3. 管理列：

   -- 添加列
   ALTER TABLE `table_name` ADD column1 datatype,columnn datatype 
   -- 添加列到指定列之后
   ALTER TABLE `table_name` ADD column1 datatype AFTER column_name
   -- 删除列
   ALTER TABLE `table_name` DROP column1,columnn
   -- 修改列属性
   ALTER TABLE `table_name` MODIFY column1 datatype,columnn datatype
   -- 修改列名
   ALTER TABLE `table_name` CHANGE old_column_name new_column_name datatype
   

三、CRUD

3.1 INSERT、UPDATE、DELETE

-- 如果是给表中所有字段添加数据，则可以省略字段名称（当不给某个字段添加值时，如果有默认值则自动填充默认值，否则报错）
INSERT INTO t_01 VALUES (val1,val2,val3)
-- 可以使用下列形式添加多条记录
INSERT INTO t01 VALUES (),(),()
-- UPDATE 语法格式
UPDATE `table_name` SET col_name = exp1,col_name = exp2 WHERE ···
-- DELETE 语法格式（使用 DELETE 语句不能删除某一列的值）
DELETE FROM `table_name` WHERE ···

-- 在插入数据或更新数据时自动更新时间戳
······ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- 蠕虫复制：表的自我复制
INSERT INTO `table_name` (field1,field2···) SELECT field1,field2 FROM `table_name`

3.2 SELECT

1. SELECT 语法格式：

   SELECT {* | <字段列表>}
          [
              [FROM <表1>,<表2>...]
              [WHERE <表达式>]
              [GROUP BY <group by definition>]
              [HAVING <expression> [{<operator> <expreesion>}...]]
              [ORDER BY <order by definition>]
              [LIMIT [<offset>, ] <row count>] -- offset begin with 0
          ]
   

2. SELECT 语句常用过滤表达式：

   表达式	作用
   BETWEEN···AND···	显示在某一区间的值，闭区间
   IN(…)	显示在 IN 列表中的值
   LIKE	模糊查询（% 表示 0 到多个字符；_ 表示单个任意字符）
   NOT LIKE	模糊查询
   IS NULL	是否为空，判断是否为空不使用 = NULL，使用 IS NULL
   AND	与
   OR	或
   NOT	非
   ORDER BY	对指定的列进行排序
   COUNT(*)	返回满足条件的的记录的行数；COUNT(列) 统计满足条件的某列有多少个，会排除 NULL

3. 连接查询对多个表进行 JOIN 运算，简单地说就是先确定一个主表作为结果集，然后把其他表的行有选择性地 “连接” 在主表结果集上

• 多表查询的条件不能少于 表的个数 - 1，否则会出现笛卡尔集
• INNER JOIN 是选出两张表都存在的记录
• LEFT OUTER JOIN 是选出左表存在的记录
• RIGHT OUTER JOIN 是选出右表存在的记录
• FULL OUTER JOIN 则是选出左右表的所有记录

-- 左外连接
SELECT ··· FROM table1 LEFT JOIN table2 ON condition1
-- 右外连接
SELECT ··· FROM table1 RIGHT JOIN table2 ON condition1 

-- 自连接查询，给当前表指定两个别名
SELECT * FROM `table_name` alias1, `table_name` alias2

4. 连接查询（UNION）：为了合并多个 SELECT 语句的结果，可以使用集合操作符号 UNION 或 UNION ALL，UNION ALL 操作符用于取得两个结果集的并集且不会去除重复行，UNION 会自动去重

5. 子查询：也称嵌套查询。指嵌入在其它 SQL 语句中的 SELECT 语句

   • 单行子查询：只返回一行数据的子查询语句

   • 多行子查询：返回多行数据的子查询，使用关键字 IN 进行连接

   • 多列子查询：返回多列数据的子查询

     SELECT ··· WHERE (字段1，字段2···) = (SELECT 字段1，字段2 FROM ···)
     

6. 特殊查询：

   -- 查看当前用户
   SELECT USER() FROM DUAL
   -- 查询当前使用的数据库
   SELECT DATABASE() FROM DUAL
   

四、系统函数

4.1 字符串函数

4.2 数学函数

4.3 日期函数

-- 日期格式化：2021-11-06 21:39:34（DUAL 表是系统的亚元表，在无表可用的情况下可以用 DUAL 来进行测试）
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s') FROM DUAL

4.4 加密函数

-- 为字符串创建一个 MD5 算法加密的密码，32 位字符
SELECT MD5(str) FROM DUAL
-- 为字符串创建一个 MySQL 加密算法创建的密码
SELECT PASSWORD(str) FROM DUAL

4.5 流程控制函数

-- 如果 exp1 为 true，则返回 exp2，否则返回 exp3
IF(exp1, exp2, exp3)
-- 如果 exp1 为空，则返回 exp2，否则返回 exp1
IFNULL(exp1, exp2)
-- 如果 exp1 为 true，则返回 exp2；如果 exp3 为 true，则返回 exp4；否则返回 exp5
SELECT 
	CASE 
        WHEN exp1 THEN exp2 
        WHEN exp3 THEN exp4 
        ELSE exp5 
	END;

五、高级特性

5.1 键约束

1. 主键（Primary Key）：主键不能重复且不能为空，一张表中最多有一个主键，可以是复合主键（多个字段构成主键）

2. 外键（Foreign Key）：用于定义主表和从表之间的关系

   • 只有表的引擎为 INNODB 的表才支持外键。外键约束需要定义在从表上，主表必须具有主键约束或是 UNIQUE 约束
   • 当定义了外键约束后，要求外键列数据必须在主表的主键列存在或是为 NULL（由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性），数据的删除原则应遵循先删外键所在从表的记录，才能删除主表中对应的记录

   -- 外键约束放在建表语句最后
   FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或UNIQUE字段名)
   

3. 唯一键（Unique）：唯一约束如果没有指定该字段不允许为空，则 UNIQUE 字段可以有多个 NULL

4. 检查（Check）：MySQL5.7 不支持 Check，只做语法校验但不生效。实现 check 数据校验功能一般是在应用程序中进行控制或是通过触发器完成

   -- sex 只能是男、女中的一个
   sex ENUM('男','女') NOT NULL
   

5.2 自增长

自增长一般来说是和主键配合使用的（一般情况只用于整数），单独使用时需要配合一个 UNIQUE 唯一键约束。只有在查询有索引的字段时查询速度才有明显提高

• 开启事务操作，哪怕事务回滚，自增 ID 值也会被占用
• 如果添加数据过程中自行指定了自增长值，则下一条记录从指定值之后开始自增

CREATE TABLE t_user (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32)
);

-- 自增字段复制
INSERT INTO `table_name` (字段1，字段2，···) VALUES (NULL,值2，···)
INSERT INTO `table_name` (字段2,字段3，···) VALUES (值2，值3，···)
INSERT INTO `table_name` VALUES (NULL,值2,值3,···)

-- 修改自增长的默认值
ALTER TABLE `table_name` AUTO_INCREMENT = digit

5.3 索引

1. 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高

   • 优先：索引的优点是提高了查询效率
   • 缺点：缺点是在插入、更新和删除记录时需要同时修改索引，因此索引越多，插入、更新和删除记录的速度就越慢

2. 索引的分类：

   • 主键索引：主键自动为主索引
   • 唯一索引：UNIQUE
   • 普通索引：INDEX
   • 全文索引：FULLTEXT，适用于 MyISAM（在开发中一般不使用 MySQL 自带的全文索引，考虑使用全文搜索框架 Solar 和 ElasticSearch）

3. 索引的创建与修改：

   -- 查询表中所有索引
   SHOW INDEXES FROM `table_name`
   -- 添加索引
   CREATE [UNIQUE] INDEX index_name ON `table_name` (col_name)
   ALTER TABLE `table_name` ADD INDEX [index_name] (index_col_name)
   -- 添加主键索引
   ALTER TABLE `table_name` PRIMARY KEY (col_name)
   -- 删除索引
   DROP INDEX index_name ON `table_name`
   -- 删除主键索引
   ALTER TABLE `table_name` DROP PRIMARY KEY
   

4. 索引使用与否：

   • 较频繁的作为查询添加的字段应该创建索引
   • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
   • 更新非常频繁的字段不适合创建索引

5.4 事务

1. 事务的概念：用于保证数据的一致性，它由一组相关的 DML 语句组成，该组的 DML 语句要么全部成功，要么全部失败。MySQL 的事务机制需要 INNODB 的存储引擎，MyISAM 不可用

2. 锁：当执行事务操作时（ DML 语句），MySQL 会在表上加锁（表级锁），防止其它用户修改表数据

3. 事务操作只能提交或回滚，不能回滚后再前进。如果不主动开启事务，默认情况下，DML 操作是自动提交的，不能回滚

4. 事务的基本操作：

   -- 1. 开始一个事务
   start transaction
   -- 2. 设置保存点
   savepoint point_name
   -- 3. 回退事务到保存点
   rollback to point_name
   -- 4. 回退全部事务
   rollback
   -- 5. 提交事务，一经提交，立即生效，不能回退
   commit
   

5.5 隔离级别

1. 事务隔离级别：多个数据库连接各自开启事务操作数据库中的数据时，DBMS 要负责隔离操作，以保证各个连接在获取数据时的准确性（查询到的数据是登录时刻的数据才算准确），如果不考虑隔离性可能会引发 脏读、不可重复读、幻读 等问题，隔离级别定义了事务与事务之间的隔离程度

   问题	描述
   脏读(dirty read)	一个事务读取到另一个事务尚未提交的操作
   幻读(phantom read)	一个事务读取到另一个事务已提交的插入操作
   不可重复读(nonrepeatable read)	一个事务读取到另一个事务已提交的修改或删除操作

2. MySQL 的四种隔离级别：默认情况为 可重复读

   • 加锁读：有且仅有一个事务正在处理数据
   • 可串行化：一张表正在被一个事务操作但尚未提交时，当前事务会停下等待，直到另一个事务提交时当前事务才可继续操作

   隔离级别	脏读	不可重复读	幻读	加锁读
   读未提交(read uncommintted)	Y	Y	Y	N
   读已提交(read committed)	N	Y	Y	N
   可重复读(repeatable read)	N	N	Y	N
   可串行化(serializable)	N	N	N	Y

3. 事务的四大特性（ACID）：

   名称	描述
   原子性(atomicity)	事务是一个不可分割的工作单位，要么都发生，要么都失败
   一致性(consistency)	事务保证数据库从一个一致性状态转换到另一个一致性状态
   隔离性(isolation)	多个并发事务之间根据隔离级别不同相互隔离
   持久性(durability)	事务一旦提交，对数据的改变就是永久性的

4. 隔离级别的查看与设置：

   -- 查看当前会话隔离级别
   SELECT @@tx_isolation
   -- 查看系统当前隔离级别（所有用户）
   SELECT @@global.tx_isolation
   -- 设置当前会话隔离级别
   SET SESSION TRANSACTION ISOLATION LEVEL isolation_name
   -- 设置系统当前会话隔离级别（所有用户）
   SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL isolation_name
   

5.6 存储引擎

1. MySQL 存储引擎：MySQL 数据表主要支持六种存储引擎，大体上分为事务安全型 (transaction-safe) 和非事务安全型 (non-transaction-safe)

   • 事务安全型：InnoDB
   • 非事务安全型：MyISAM、Memory、CSV、Archive、MRG_MyISAM

   特点	InnoDB	MyISAM	Memory	Archive
   批量插入速度	低	高	高	非常高
   事务安全	支持
   全文索引		支持
   锁机制	行锁	表锁	表锁	行锁
   存储限制	64TB	无限制	有限制	无限制
   B树索引	支持	支持	支持
   哈希索引	支持		支持
   集群索引	支持
   数据缓存	支持		支持
   索引缓存	支持	支持	支持
   数据可压缩		支持		支持
   空间使用	高	低		非常低
   内存使用	高	低	中等	低
   支持外键	支持

2. 常用存储引擎的选择：

   • InnoDB：具有提交、回滚和崩溃恢复能力的事务安全处理能力。但比起 MyISAM 存储引擎，InnoDB 写入数据的处理效率相对较低并且会占用更多的磁盘空间以保留数据和索引
   • MyISAM：不支持事务和外键，但其访问速度快，对事务完整性没有要求
   • Memory：使用内存来创建表，表访问速度非常快，默认使用 Hash 索引。当 MySQL 服务关闭时，Memory 表中所有数据都会丢失，但表的结构仍然存在

3. 存储引擎的查看与修改：

   -- 查看所有的存储引擎
   SHOW ENGINES;
   -- 修改表的存储引擎
   ALTER TABLE `table_name` ENGINE = engine_name
   

5.7 视图

1. 视图定义：视图是一张虚拟表，其内容由查询语句定义。同真实的表一样，视图包含列和值，其数据映射自对应的真实表（基表）。由视图的定义可知可以在视图上再定义视图

2. 基表与视图的修改会相互影响，创建视图后磁盘中只存在一个视图结构文件，不存在数据存储文件，因为视图仅仅映射基表中的数据

3. 关系数据库的数据常常会分表存储，使用外键维护这些表之间的关系。查询时难免用到连接（JOIN）。这样做不仅麻烦，效率也比较低。如果建立视图，将相关的表和字段组合在视图中，就可以避免使用 JOIN 查询数据

4. 视图的生成与管理：

   -- 创建视图
   CREATE VIEW view_name AS SELECT field1··· FROM `table_name`
   -- 修改视图
   ALTER VIEW view_name AS SELECT field1··· FROM `table_name`
   -- 查看视图生成语句
   SHOW CREATE VIEW view_name
   -- 删除视图
   DROP VIEW view_name
   

六、系统管理

6.1 用户管理

-- mysql8 修改 root 密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
-- 创建用户，如果不指定 Host，则为 %，% 表示所有 IP 都有连接权限
CREATE USER user_name@ip_addr IDENTIFIED BY pwd
-- 删除用户
DROP user_name@ip_addr
-- 修改自己的密码
SET PASSWORD = PASSWORD(pwd)
-- 修改别人的密码（需要权限）
SET PASSWORD FOR user_name @ip_addr = PASSWORD(new_pwd)

6.2 权限管理

-- 给用户授权，权限列表，多个权限用逗号分隔
GRANT 权限列表 ON database.object TO username@ip [IDENTIFIED BY pwd]
-- 回收权限
REVOKE 权限列表 ON database.object FROM username@ip
-- 权限生效指令
FLUSH PRIVILEGES

-- 设置用户所有 IP 可登录
update mysql.user set host = '%' where user = 'root';
flush privileges;
grant all on *.* to 'root'@'%' with grant option;

七、练习

7.1 练习 1

-- 1.1. 建立数据库
CREATE DATABASE test;
USE test;

-- 1.2. 建立 emp 表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1.3. 建立 salgrade 表
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `losal` decimal(17,2) NOT NULL,
  `hisal` decimal(17,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1.4. 建立 dept 表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(20) NOT NULL DEFAULT '',
  `loc` varchar(13) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1.5. 插入数据到 emp 表
INSERT INTO `emp` VALUES (7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1991-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1991-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1991-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1991-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1991-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1991-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1997-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1991-09-08',1500.00,NULL,30),(7900,'JAMES','CLERK',7698,'1991-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1991-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1992-01-23',1300.00,NULL,10);

-- 1.6. 插入数据到 salgrade 表
INSERT INTO `salgrade` VALUES (1,700.00,1200.00),(2,1201.00,1400.00),(3,1401.00,2000.00),(4,2001.00,3000.00),(5,3001.00,9999.00);

-- 1.7. 插入数据到 dept 表
INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

7.2 练习 2

-- 2.1 查看dept表和emp表的结构
DESC emp；
DESC dept；
-- 2.2 显示所有部门名称
SELECT dname FROM dept；
-- 2.3 显示所有雇员名及其全年收入13月（工资+补助），并指定列别名“年收入”
SELECT ename,(sal+IF(comm IS NULL,0.0,comm))*13 AS '年收入' FROM emp；
-- 2.4 显示工资超过2850的雇员姓名和工资
SELECT ename,sal FROM emp WHERE sal > 2850；
-- 2.5 显示工资不在1500~2850之间的所有雇员名及工资
SELECT ename,sal FROM emp WHERE sal NOT BETWEEN 1500 AND 2850；
-- 2.6 显示编号为7566的雇员姓名及所在部门编号
SELECT ename,dname FROM emp,dept WHERE empno = 7566 AND emp.deptno = dept.deptno ；
-- 2.7 显示部门为10和30中员工工资超过1500的雇员名及工资
SELECT ename,sal FROM emp WHERE sal > 1500 AND deptno IN(10,30)；
-- 2.8 显示无管理者的雇员名及工资
SELECT ename,job FROM emp WHERE mgr IS NULL；
-- 2.9 显示在1991-2-1到1991-5-1之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序
SELECT ename,job,hiredate FROM emp WHERE hiredate >= '1991-2-1' AND hiredate <= '1991-5-1' ORDER BY hiredate；
-- 2.10 显示获得补助的所有雇员名、工资及补助，并以工资降序排序
SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC；

7.3 练习 3

-- 3.1 选择部门30中的所有员工
SELECT ename FROM emp WHERE deptno = 30；
-- 3.2 列出所有办事员(CLERK)的姓名、编号及部门编号
SELECT ename,empno,deptno FROM emp WHERE job='CLERK'；
-- 3.3 找出雇佣金高于薪金的员工
SELECT * FROM emp WHERE IFNULL(comm,0.0)>sal；
-- 3.4 找出佣金高于薪金60%的员工
SELECT * FROM emp WHERE IFNULL(comm,0.0) > sal*0.6；
-- 3.5 找出部门10中所有的经理（MANAGER）和部门20中所有的办事员（CLERK）的详细资料
SELECT * FROM emp WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK')；
-- 3.6 找出部门10中所有经理，部门20中所有办事员，还有既不是经理也不是办事员但其佣金大于或等于2000的所有员工的详细资料
SELECT * FROM emp WHERE (deptno=30 AND job='MANAGER') OR (deptno=20 AND job='CLERK') OR (job NOT IN('MANAGER','CLERK') AND sal >= 2000)；
-- 3.7 找出有奖金的员工的工作类型
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL；
-- 3.8 找出没有奖金或奖金金额小于100的员工的雇员名
SELECT ename FROM emp WHERE comm IS NULL OR (comm IS NOT NULL AND comm < 100)；
-- 3.9 找出各月倒数第3天受雇的员工
SELECT * FROM emp WHERE DATE_SUB(LAST_DAY(hiredate),INTERVAL 2 DAY)=hiredate；
-- 3.10 找出早于12年前受雇的员工
SELECT * FROM emp WHERE hiredate < DATE_SUB(NOW(),INTERVAL 12 YEAR)；
-- 3.11 以首字母小写的方式显示所有员工的姓名
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) FROM emp；
-- 3.12 显示正好为5个字符的员工的姓名
SELECT ename FROM emp WHERE LENGTH(ename)=5；

7.4 练习 4

-- 4.1 显示不带'R'字母的员工的姓名
SELECT ename FROM emp WHERE INSTR(ename,'R')=0;
-- 4.2 显示所有员工姓名的前三个字符
SELECT SUBSTRING(ename,1,3) FROM emp;
-- 4.3 显示所有员工的姓名，用a替换所有的A
SELECT REPLACE(ename,'A','a') FROM emp;
-- 4.4 显示满10年服务年限的员工的姓名和受雇日期
SELECT ename,hiredate FROM emp WHERE DATE_SUB(NOW(),INTERVAL 10 YEAR)>hiredate;
-- 4.5 显示员工的详细资料，按姓名排序
SELECT * FROM emp ORDER BY ename;
-- 4.6 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
SELECT ename,hiredate FROM emp ORDER BY hiredate;
-- 4.7 显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序
SELECT ename,job,sal FROM emp ORDER BY job,sal;
-- 4.8 显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将按年份升序排序
SELECT ename,YEAR(hiredate) AS `year`,MONTH(hiredate) AS `month` FROM emp ORDER BY `month`,`year`;
-- 4.9 显示一个月为30天的员工的日薪金，单位为元
SELECT ename,FLOOR(sal/30) AS day_pay FROM emp WHERE DAY(LAST_DAY(hiredate))=30;
-- 4.10 找出所有年份中2月受聘的员工
SELECT ename FROM emp WHERE MONTH(hiredate)=2;
-- 4.11 显示每个员工加入公司的天数，按天数降序排列
SELECT ename,DATEDIFF(NOW(),hiredate) AS days FROM emp ORDER BY days DESC;
-- 4.12 显示员工姓名中含有A的所有员工的姓名
SELECT ename FROM emp WHERE ename LIKE '%A%';
-- 4.13 以年月日的方式显示所有员工的服务年限
SELECT ename, FROM_DAYS(DATEDIFF(NOW(),hiredate)) AS service_time FROM emp;

7.5 练习 5

-- 5.1 列出至少有一个员工的部门
SELECT deptno,dname FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp);
-- 5.2 列出薪金比 "SMITH" 高的员工
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='SMITH');
-- 5.3 列出受雇日期晚于其直接上级的所有员工
SELECT emp1.ename AS 'employee',emp2.ename AS 'leader' FROM emp emp1,emp emp2 WHERE emp1.mgr=emp2.empno AND emp1.hiredate > emp2.hiredate;
-- 5.4 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT emp.*,dname FROM emp RIGHT JOIN dept ON dept.deptno=emp.deptno ORDER BY dname ;
-- 5.5 列出所有办事员（CLERK）的姓名及其部门名称
SELECT ename,dname FROM emp,dept WHERE emp.job='CLERK' AND dept.deptno=emp.deptno;
-- 5.6 列出最低薪金大于1500的各种工作
SELECT DISTINCT job FROM emp WHERE emp.sal > 1500;
-- 5.7 列出在销售部门（SALES）工作的员工的姓名
SELECT ename FROM emp,dept WHERE emp.deptno=dept.deptno AND dname='SALES';
-- 5.8 列出薪金高于公司平均薪金的员工姓名
SELECT ename FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

7.6 练习 6

-- 6.1 列出与 “SCOTT” 工作相同的所有员工
SELECT ename FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT';
-- 6.2 列出薪金高于30号部门最高工资的其它部门的员工姓名和薪金
SELECT ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30) AND deptno != 30;
-- 6.3 列出每个部门的员工数量，平均工资和员工平均服务天数
SELECT COUNT(empno) AS numbers_of_dept, AVG(sal) AS avg_sal ,AVG(DATEDIFF(NOW(),hiredate)) AS avg_days FROM emp GROUP BY deptno;
-- 6.4 列出所有员工的姓名，部门名称及工资
SELECT ename,dname,sal FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 6.5 列出所有部门的详细信息和部门人数
SELECT dept.*,temp.cnt_emp FROM dept,(SELECT COUNT(empno) AS cnt_emp,deptno FROM emp GROUP BY deptno) AS temp WHERE dept.deptno = temp.deptno ；
-- 6.6 列出各种工作的最低工资
SELECT MIN(sal) AS min_sal, job FROM emp GROUP BY job;
-- 6.7 列出经理（MANAGER）的最低薪金
SELECT MIN(sal) AS min_sal_manager FROM emp WHERE job = 'MANAGER';
-- 6.8 列出所有员工的年薪，按年薪多少升序排列
SELECT ename, (sal*12 + IFNULL(comm,0)) AS year_sal FROM emp ORDER BY year_sal;

7.7 练习 7

设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。现要建立关于系、班级、学生的数据库，数据库名为stu_db。关系模式如下：

dept(系)：deptid(系号)，deptname(系名)
class(班)：classid(班号)，subject(专业),deptname(所在系)，enrolltime(入学年份)，num(人数)
student(学生)：sudid(学号)，stuname(姓名)，stuage(年龄)，classid(班级)

-- 建立 stu_db 数据库
CREATE DATABASE `stu_db`;

USE `stu_db`;

-- 建立 class 表
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `classid` int(11) NOT NULL,
  `subject` varchar(32) DEFAULT NULL,
  `deptname` varchar(32) DEFAULT NULL,
  `enrolltime` year(4) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`classid`),
  KEY `deptname` (`deptname`),
  CONSTRAINT `class_ibfk_1` FOREIGN KEY (`deptname`) REFERENCES `dept` (`deptname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 建立 dept 表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptid` int(11) NOT NULL,
  `deptname` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`deptid`),
  UNIQUE KEY `deptname` (`deptname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 建立 student 表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuid` int(11) NOT NULL,
  `stuname` varchar(32) NOT NULL,
  `stuage` smallint(6) DEFAULT NULL,
  `classid` int(11) DEFAULT NULL,
  PRIMARY KEY (`stuid`),
  KEY `classid` (`classid`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据到 class 表
INSERT INTO `class` VALUES (101,'软件','计算机',1995,20),(102,'微电子','计算机',1996,30),(111,'无机化学','化学',1995,29),(112,'高分子化学','化学',1996,25),(121,'统计数学','数学',1995,20),(131,'现代语言','中文',1996,20),(141,'国际贸易','经济',1997,30),(142,'国际金融','经济',1996,14);

-- 插入数据到 dept 表
INSERT INTO `dept` VALUES (4,'中文'),(3,'化学'),(1,'数学'),(5,'经济'),(2,'计算机');

-- 插入数据到 student 表
INSERT INTO `student` VALUES (8101,'张三',18,101),(8102,'钱四',16,121),(8103,'王玲',17,131),(8105,'李飞',19,102),(8109,'赵四',18,141),(8110,'李可',20,142),(8201,'张飞',18,111),(8203,'王亮',17,111),(8302,'周瑜',16,112),(8305,'董庆',19,102),(8409,'赵龙',18,101),(8510,'李丽',20,142);

-- 7.1. 找出所有姓李的学生
SELECT * FROM student WHERE stuname LIKE '李%';
-- 7.2. 列出专业数量大于1的系的名字
SELECT COUNT(deptname) AS sub_nums FROM class GROUP BY deptname HAVING sub_nums>1;
-- 7.3. 列出人数大于等于30的系的编号和名称
SELECT dept.* FROM dept,class WHERE class.num >= 30 AND dept.deptname = class.deptname;
-- 7.4. 学校又增加了一个物理系，编号为006
INSERT INTO dept VALUES (6,'物理系');
-- 7.5. 学生张三退学，请更新相关的表
START TRANSACTION;
UPDATE class SET num = num-1 WHERE classid = (SELECT classid FROM student WHERE stuname = '张三');
DELETE FROM student WHERE stuname = '张三';
COMMIT;

7.8 实用 SQL

-- 1. 插入或替换
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
-- 2. 插入或更新
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
-- 3. 插入或忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
-- 4. 强制指定索引
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;