韩顺平Java | C24 MySQL数据库(下)

※多表查询

笛卡尔集：查询两个表，默认无条件情况下，取出第一张表中的每一条记录和第二张表的每一条记录进行组合，返回row1*row2条记录数，包含两张表的所有列

内连接

# 写出正确的过滤条件：多表查询条件不能少于表数-1，否则会出现笛卡尔集
SELECT column1, table1_column2 FROM table1, table2 WHERE table1.column2 = table2.column2;
SELECT emp, sal, grade FROM emp, salgrade WHERE sal BETWEEN losal AND hisal;

###自连接
在同一张表的连接查询（将同一张表看作两张表）；需要给表取别名 表名 表别名；列名不明确，可以指定列别名 列名 AS 列别名

# 显示公司员工名和对应的上级名
SELECT worker.ename AS '员工名', boss.ename AS '上级名' FROM emp worker, emp boss WHERE worfer.mgr = boss.empno;

子查询

子查询/嵌套查询：嵌入在其他sql语句中的select语句

单行子查询

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

# 查询和SMITH相同部门的员工信息
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');

多行子查询

返回多行数据的子查询
使用关键字IN

# 查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但不包含10自己的
SELECT ename, job, sal, deptno 
	FROM emp 
	WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10)
		AND deptno != 10;

在多行子查询中使用操作符ALL ANY

#如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30) 
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30) 
#如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30) 
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30) 

子查询临时表

# 查询ecshop各个类别中价格最高的商品
SELECT goods_id, temp.cat_id, goods_name, shop_price
	FROM (
		SELECT cat_id, MAX(shop_price) AS max_price 
		FROM ecs_goods 
		GROUP BY cat_id # 先查询各类别中的最高价格，把子查询当作临时表
		) temp, ecs_goods # 再联合商品表查询各类中最高商品的详细信息
	WHERE temp.cat_id = ecs_goods.cat_id 
	AND temp.max_price = ecs_goods.shop_price

多列子查询

查询返回多个列数据的子查询语句

(column1, column2) = SELECT column1, column2 FROM ...
# 查询工作和部门于ALLEN相同的其他同事
SELECT * FROM emp 
	WHERE (job, deptno) = (
		SELECT job, deptno FROM emp WHERE ename='ALLEN'
		) AND ename != 'ALLEN'

表复制

自我赋值数据/蠕虫复制

有时为了对某个sql语句进行效率测试，可以使用此方法为表创建海量数据

# 先从其他表复制数据
INSERT INTO my_tab01 (id, 'name', sal, job, deptno) 
	SELECT empno, ename, sal, job, deptno FROM emp
# 自我复制-多次执行
INSERT INTO my_tab01
	SELECT * FROM my_tab01

去重

如何删除一张表的重复记录

CREATE TABLE my_tab02 LIKE emp; -- 复制emp的表结构
INSERT INTO my_tab02 SELECT * FROM emp;
#考虑去重
CREATE TABLE my_tmp like my_tab02; --1 先创建一张结构一样的临时表
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02; --2 把原表的记录通过DISTINCT关键字处理后复制到临时表

DELETE FROM my_tab02; --3 清除原表数据
INSERT INTO my_tab02 SELECT * FROM my_tmp; --4 将临时表中数据复制到原表
DROP my_mp; --5 删除临时表
-- 或
DROP FROM my_tab02; --3 删除原表
RENAME my_tmp TO my_tab02; --4 将临时表的表名修改为原表的

合并查询

为了合并多个select语句，可以使用集合操作符号UNION UNION ALL

select1 UNIQUE ALL select2; --查询结果合并，不去重
select1 UNION select2; --自动去重

※外连接

需求：内连接只能显示满足条件的记录，对于不满足条件的记录不能显示
左外连接：左侧的表完全显示
右外连接：右侧的表完全显示

SELECT column1, column2 FROM table1 LEFT | RIGHT JOIN table2 ON on_defination;

约束

用于确保数据库的数据满足特定的商业规则

primary key

用于唯一标识表行的数据，定义主键约束后，该列不能重复，且不能为null
一张表最多只能有一个主键，但是可以有复合主键

CREATE TABLE tablename (id INT PRIMARY KEY, column2 VARCHAR(32)); --字段名后定义主键
CREATE TABLE tablename (id INT, 'name' VARCHAR(32), email VARCHAR(32), PRIMARY KEY (id, 'name')); --表后定义主键、复合主键
DESC tablename; --查看表结构，显示约束情况

not null

非空，记录中该列值必有数据

CREATE tablename (id INT NOT NULL);

unique

唯一约束，该列值不能重复
字段未指定NOT NULL，列值可以有多个NULL
UNIQUE NOT NULL等价于PRIMARY KEY

CREATE tablename (id INT UNIQUE);

foreign key

外键约束：要求某个列值须在另一表中的某列值中存在，如果要删除另一表中对应记录，需先删除本表相应数据
外键约束定义在从表上，主表必须具有主键约束或是UNIQUE约束。定义外键约束后，要求外键列数据必须在主表的主键列存在或是null
表的类型须是innodb，才支持外键
外键字段类型要和主键字段类型一致，长度可不同

CREATE TABLE tablename (
	column1 INT,
	column2 VARCHAR(32),
	FOREIGN KEY (本/从表字段名) REFERENCES 主表名(主键名或unique字段名));

check

强制行数据必须满足条件。oracle和sql sercer均支持check，但mysql5.7之作语法校验，不生效。

CREATE TABLE tablename(
	gender VARCHAR(6) CHECK (gender IN (male, female)));

自增长

存在某一列，在添加记录时，默认从1开始自动增长（可以修改）
· 一般来所自增长是和primary key配合使用的
· 自增长也可以单独使用，但需要配合一个unique
· 自增长修饰的字段为整型（小数可以使用，但非常少）
· 如果添加数据时，给自增长字段指定值，下次插入数据再依次增长，一般按自增长规则来添加数据

CREATE TABLE tablename(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(32));
INSERT INTO tablename(id, column...) VALUES (null, 'value'...);
INSERT INTO tablename(column2...) VALUES(value2);
INSERT INTO tablename VALUES(null, 'value'...);
ALTER TABLE tablename AUTO_INCREMENT = 100;

※索引

提高数据库性能。无需加内存、改程序，调sql，可对该字段查询千百倍提高查询速度
原理：查找会遍历整张表；建立索引，如会创建二叉树（小左大右），若比较30次，可覆盖2^30个数据。
代价：索引占用磁盘；对dml(update delete insert)效率有一定影响，重新维护索引

CREATE INDEX column1_index ON tablename (column1);

主键索引(primary key)： 主键自动为主索引
唯一索引(UNIQUE)：unique自动为索引
普通索引(INDEX)：允许列值索引
全文索引(FULLTEXT)：适用于MylSAM，开发中考虑使用全文搜索框架Solr和ElasticSearch(ES)

# 索引使用
CREATE TABLE tablename (id INT, `name` VARCHAR(32));
SHOW INDEXES FROM tablename; --查看表是否有索引

# 添加索引
--创建唯一索引
CREATE [unique] INDEX index_name ON tbl_name (col_name[(length)] [ASC | DESC], ...); --添加普通[唯一]索引，根据索引列是否会重复，优先考虑唯一索引
--添加普通索引方式2
ALTER TABLE tablename ADD INDEX [index_name] (index_col_name, ...);
--添加主键索引
CREATE TABLE tablename(id INT PRIMARY KEY, `name` VARCHAR(32));
ALTER TABLE tablename ADD PRIMARY KEY (column1);

# 删除索引
DROP INDEX iddex_col_name ON tablename;
ALTER TABLE tablename DROP PPRIMARY_KEY; --删除主键索引
# 修改索引：先删除，再添加

# 查询索引
SHOW INDEX FROM tablename
SHOW INDEXES FROM tablename
SHOW KEYS FROM tablename
DESC tablename

适合创建索引列：
较频繁的作为查询条件的字段适合
唯一性太差的不适合，及时频繁使用
更新频繁的不适合创建，会频繁维护二叉树
不会出现在where子句中的字段

事物

事物：用于保证数据的一致性，由一组相关的dml(除select语句，select对数据无影响)语句组成，该组要么全部成功要么全部失败，如转账。
事物和锁：当执行事物操作时(dml语句)，mysql会在表上加锁，防止其他用户改表的数据。

事物管理

mysql数据库控制台事物的几个重要操作：

 --开始一个事物
start transaction --开启事物方式1
set autocommit=off --开启事物方式2
savepoint point_name-- 保存点名，设置保存点
rollback to point_name--保存点名，回退事物
rollback --回退全部事物
commit --提交事物，所有操作生效，不能回退。事物结束，自动删除该事物定义的所有回退点、释放锁，数据生效（其他会话/连接可以查看事物变化后的新数据）

注意：mysql的事物机制需要innodb的存储引擎才可以使用，myism不行

隔离级别

多个连接开启各自事物操作数据库中数据时，数据库系统负责隔离操作，以保证各连接获取数据的准确性。（不同隔离级别看到的数据不同）
不考虑隔离存在问题：

• 脏读(dirty read)：一个事物读取另一个事物尚未提交commit的dml；
• 不可重复读(nonrepeatable read)：同一个查询在同一事物中多次进行，由于其他提交commit事物所作的修改或删除，每次返回不同的结果；如10点操作盘点订单数据，但获取了10点后的数据。
• 幻读(phantom read)：同一查询在同一事物中多次进行，由于其他提交commit事物所做的插入操作，每次返回不同的结果。

事物隔离级别：定义了事物与事物之间的隔离程度。
可以通过多个cmd客户端模拟数据库多连接的情况。
加锁： 由其他事物为提交时，当前事物操作会卡住，待其他事物commit后执行。
mysql默认事物隔离级别是repeatable read，可满足大部分项目需求，一般不作修改。如需全局修改，修改程序系统文件my.ini，添加配置项 transaction-isolation = READ-UNCOMMITED | READ-COMMITED | REPEATABLE-READ | SERIALIZABLE，mysql服务重启net stop | start mysql后查询已改变。

USE database_name; --选择数据库
SELECT @@tx_isolation; --查看当前mysql会话隔离级别
SELECT @@global.tx_isolation; --查看系统当前隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;--设置当前会话隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;--设置当前系统隔离级别

ACID

• 原子性(Atomicity)：事物是一个不可分割的工作单位，事物中的操作要么都发生，要么都不发生；
• 一致性(Consistency)：事物必须使数据库从一个一致性状态变换到另一个一致性状态；（提交后整体变化）
• 隔离性(Isolation)：多个用户并发访问数据库时，数据库为每一个用户开启的事物，不能被其他事物的操作数据所干扰，多个并发事物之间要相互隔离；
• 持久性(Durability)：事物一旦提交，对数据库中数据的改变就是永久性的。

表类型&存储引擎

• MySQL的表类型由存储引擎(Storage Engines) 决定，主要包括MyISAM\innodb\memory等
• MySQL数据表主要支持六种类型，分为(非non-)事物安全型(transaction-safe)两类：csv、memory、archive、mrg_myisam、myisam、innobdb(事物安全型，支持事物机制)；

SHOW ENGINES; --查看所有的存储引擎

MEMORY存储引擎没有IO读写

CREATE TABLE tablename (id INT, `name` VARCHAR(32)) ENGINE MYISAM;
ALTER TABLE tablename ENGINE = INNODB; --修改表的存储引擎

用户在线状态频繁变化

视图view

视图&基表：视图是一个虚拟表，其内容由查询定义，视图也有列，数据来源于对应的真实表(基表，可以是多个基表)，通过视图可以修改基表的数据，基表的变化也会影响到视图的数据
· 创建试图后，查看数据库，对应视图只有一个视图结构文件(viewname.frm)
· 视图中可以再创建视图

CREATE VIEW viewname AS SELECT...; --创建
ALTER  VIEW viewname AS SELECT...; --修改
SHOW CREATE VIEW viewname; --显示创建视图的指令
DROP VIEW viewname, viewname2; --删除

应用：安全；性能-避免JOIN分表查询，提高性能；灵活-如旧表由于涉及问题即将被废弃，但很多数据基于该表不易修改，可以建立视图映射该表，基于视图使用，减少改动。

# 使用多张基表创建视图
# 使用三张表显示雇员编号，雇员名，雇员部门名称和薪水级别
CREATE VIEW my_view 
	AS 
	SELECT empno, ename, dname，grade
		FROM emp, dept, salgrade
		WHERE emp.deptno = dept.deptno AND
		(SAL BETWEEN losal AND hisal);

Mysql管理

MySQL用户

需求：项目开发时，mysql管理员(root)根据不同的开发人员赋给他相应的mysql操作权限。不同的数据库用户登录到DBMS后，根据权限，可以操作的数据库和数据对象（表/视图/触发器）不一样。
mysql用户都存储在系统数据库mysql中user表中，重要字段说明：
host：允许登录的“位置”，表示该用户只允许本机登录，也可以指定ip地址
user：用户名
suthentication_string：密码，是通过mysql的password()函数加密之后的密码

CREATE USER `username`@`host_address` IDENTIFIED BY `pwd`;--创建用户，同时指定密码(自动加密后存入数据库)
SELECT PASSWORD('pwd');--查询加密后的密码数据
DROP USER `username`@`host_address`; --删除用户
--客户端/命令行登录数据库：mysql -h 主机名 -P端口 -u用户名 -p密码
SET PASSWORD = PASSWORD(`pwd`);--修改自己的密码
SET PASSWORD FOR `orther_name`@`other_address` = PASSWORD(`pwd`);--修改别人的密码需要权限

CREATE USER `username`;--创建用户时若不指定Host，则为%，表示所有IP均有连接权限；
CREATE USER `username`%`192.168.1.%`;--代表该用户在192.168.1.*的ip段内均可以登录mysql
DROP USER `username`;--删除用户时，若host不是&，需要明确指定`user`@`host_add`

用户权限

库.对象名替换
*.*：代表本系统中所有数据库的所有对象(表，视图，存储过程)
库.*：表示某个数据库中所有数据对象(表，视图，存储过程)

# 赋予该用户在该对象上的权限
GRANT 权限1[, 权限2...] ON 库.对象名 TO `用户名`@`登录位置` [IDENTIFIED BY `pwd`]; --密码可选，用户存在代表修改密码，用户不存在代表创建用户

REVOKE 权限列表 ON 库.对象名 FROM `用户名`@`登录位置`;--回收用户权限
FLUSH PRIVILEGES; --刷新生效权限指令（MYSQL5.7赋权限后一般立即生效，低版本如5.5可能不能立即生效）