PartTwo——MySQL基础

SQL语句语法

一、SQL常识

1.什么是SQL？
	Structured Query Language：结构化查询语言
	其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
	
2. SQL分类
	1) DDL(Data Definition Language)数据定义语言
		用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
	2) DML(Data Manipulation Language)数据操作语言
		用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
	3) DQL(Data Query Language)数据查询语言
		用来查询数据库中表的记录(数据)。关键字：select, where 等
	4) DCL(Data Control Language)数据控制语言(了解)
		用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

二、DDL

1.数据类型与转换

1）与java对应表

类型名称	显示长度	数据库类型	JAVA类型	JDBC类型索引(int)
VARCHAR	L+N	VARCHAR	java.lang.String	12
CHAR	N	CHAR	java.lang.String	1
TEXT	65535	VARCHAR	java.lang.String	-1
INTEGER	4	INTEGER [UNSIGNED]	java.lang.Long	4
TINYINT	3	TINYINT [UNSIGNED]	java.lang.Integer	-6
SMALLINT	5	SMALLINT [UNSIGNED]	java.lang.Integer	5
MEDIUMINT	8	MEDIUMINT [UNSIGNED]	java.lang.Integer	4
BIGINT	20	BIGINT [UNSIGNED]	java.math.BigInteger	-5
FLOAT	4+8	FLOAT	java.lang.Float	7
DOUBLE	22	DOUBLE	java.lang.Double	8
DECIMAL	11	DECIMAL	java.math.BigDecimal	3
DATE	10	DATE	java.sql.Date	91
YEAR	4	YEAR	java.sql.Date	91
TIME	8	TIME	java.sql.Time	92
DATETIME	19	DATETIME	java.sql.Timestamp	93
TIMESTAMP	19	TIMESTAMP	java.sql.Timestamp	93

2）数据超出范围和溢出处理

当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：
如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。
如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告

在我们的配置文件中可以看到SQL模式的配置，关于SQL模式详情请看SQL模式官方文档

3）基本数据类型

MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等
数据类型描述使用以下约定：

• M表示整数类型的最大显示宽度。对于浮点和定点类型， M是可以存储的总位数（精度）。对于字符串类型， M是最大长度。允许的最大值M取决于数据类型。

• D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。

• **[ ]**表示类型定义的可选部分。

char和varchar：char的M限定字符长度不超出范围则默认是设定的长度；varchar不超出范围根据实际的占用来计算。

enum和set：两个都是设置一套value数组。(jdbc没法用，弱化)

enum是只能选其中一个值，可以用下标代替：

INSERT enum_db() VALUES(2);等同于"女"

set可以选多个值：

INSERT set_db() VALUES('1')
INSERT set_db() VALUES('1,2,3')

time：TIME类型不仅可以用于表示一天中的时间，还可以用于表示两个事件之间的经过时间或时间间隔。

TIME的完整的显示为 D HH:MM:SS
D：表示天数，当指定该值时，存储时小时会先乘以该值
HH：表示小时
MM：表示分钟
SS:表示秒

--   表示某一天的对应时间
INSERT time_db() VALUES('22:14:16');
--   -2表示间隔了2两天
INSERT time_db() VALUES('-2 22:14:16');
-- 有冒号从小时开始
INSERT time_db() VALUES('14:16');
-- 没有冒号且没有天数则数据从秒开始
INSERT time_db() VALUES('30');
-- 有天数也从小时开始
INSERT time_db() VALUES('3 10');
-- 直接使用数字代替也可以
INSERT time_db() VALUES(253621);

datetime日期/timestamp时间戳：两种类型相似范围大小的区别而已，都支持多种形式的输入：

INSERT timestamp_db() VALUES(20020121);
INSERT timestamp_db() VALUES(20020121142554);
INSERT timestamp_db() VALUES("2015-12-16 21:14:15");
INSERT timestamp_db() VALUES("2015-12-17");
INSERT timestamp_db() VALUES(NULL);
INSERT timestamp_db() VALUES(CURRENT_TIMESTAMP);
INSERT timestamp_db() VALUES();

4）数据之间的转换

字符串与数字：
1.数字转字符串
select cast(number as char) as number from test;  
-- 或者convert（）方法。因为转换的时候mysql不支持转换成varchar所有要转成char.
2.字符串转数字
+0法：select * from orders order by (mark+0)  desc
函数法：浮点数 : DECIMAL 
　　　　整数 : SIGNED
　　　　无符号整数 : UNSIGNED 
select * from test order by convert(mark,SIGNED)；
select * from orders order by  cast(mark as SIGNED)；
日期、字符串、时间戳
1.日期转字符串
date_format(date,format) 
-- date：日期（时间字段）、format：日期格式
select date_format(now(), '%Y-%m-%d %H:%m:%s');  
2.日期转时间戳
select unix_timestamp(now()); 
3.字符串转日期：
select str_to_date('2016-01-02', '%Y-%m-%d %H'); 
4.字符串转时间戳：
select unix_timestamp('2016-01-02'); 
#结果：1451664000  
5.时间戳转日期：
select from_unixtime(1451997924); 
#结果：2016-01-05 20:45:24  
6.时间戳转字符串：
select from_unixtime(1451997924,'%Y-%d');  
#结果：2016-01-05 20:45:24  

2.基本操作

一. 操作数据库：CRUD
	1. C(Create):创建
		* create database if not exists db 
		defult character set utf-8;
	2. R(Retrieve)：查询
		* 查询所有数据库的名称:
			* show databases;
		* 查询某个数据库的创建语句
			* show create database 数据库名称;
	3. U(Update):修改数据库的字符集
		* alter database db character set gbk;
	4. D(Delete):删除
		* 删除数据库
		* drop database if exists db;
	5. 使用数据库
		* 查询当前正在使用的数据库名称 select database();
		* 使用数据库 use 数据库名称;

二. 操作表
	1. C(Create):创建与复制
		create table 表名(
			....
			列名n 数据类型n
		);*注意：最后一列，不需要加逗号（,）
		
		*全面复制表：
			CREATE TABLE 新表 SELECT * FROM 旧表；
		*复制表结构：
			create table 表名 like 被复制的表名;
	2. R(Retrieve)：查询
		* 查询某个数据库中所有的表名称
			* show tables;
		* 查询表结构
			* desc 表名;
	3. U(Update):修改
		1. 修改表名
			alter table 表名 rename to 新的表名;
		2. 修改表的字符集
			alter table 表名 character set 字符集名称;
		3. 添加一列
			alter table 表名 add 列名 数据类型;
		4. 修改列名称 类型
		alter table 表名 change 列名 新列名 新数据类型;
		ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型  COMMENT 'xxx';
		5. 删除列
			alter table 表名 drop 列名;
	4. D(Delete):删除
		* drop table  if exists 表名 ;
级联操作：在外键约束时顺便添加
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
ON UPDATE CASCADE 
ON DELETE CASCADE
*不一定要是主键才可以做外键约束，但必须是唯一列才行，即（unique/primary key）

3.约束

约束添加时间：创建表和修改表时

1. NOT NULL ：非空

2. DEFAULT：默认约束

3. 索引约束：

   KEY:普通索引在mysql中与INDEX同意

   PRIMARY KEY：主键索引，非空mysql中包含unique，一般添加识别列，auto_increment

   UNIQUE：唯一索引，可空

   CHECK：检查约束（MySql不支持）

   FOREIGN KEY：外键

4. 建议约束规则（mysql为例）：

   列级约束：主键+auto_increment、非空、默认、唯一

   表级约束：外键、普通索引

   constraint xxx foreign key(majorId) references major(id)
   KEY idx_actid_name(act_id,act_name) USING BTREE 
   --可以单例也可组合，索引名字只要一个
   

4.标识列

又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 
);
TRUNCATE TABLE tab_identity;

#### 2）索引
[建表后创建管理索引](https://www.cnblogs.com/yeyublog/p/5898588.html)
索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；

[添加索引规范](https://blog.csdn.net/WuLex/article/details/69540136?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.baidujs&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.baidujs)

**MYISAM和InnoDB**

MYISAM和InnoDB存储引擎只支持BTREE索引；MEMORY和HEAP存储引擎可以支持HASH和BTREE索引

B-tree索引是数据库中存取和查找文件(称为记录或键值)的一种方法.B-tree算法减少定位记录时所经历的中间过程,从而加快存取速度.

**InnoDB**
优点：支持事务和外键，数据完整性机制比较完备；可以用SHOW TABLE STATUS查得某个库或表的磁盘占用
缺点：速度超慢，磁盘空间占用多；所有库都存于一个（通常情况）或数个文件中，无法通过操作系统了解某个库或表的占用空间。

**MyISAM**
优点：速度快，磁盘空间占用少；某个库或表的磁盘占用情况既可以通过操作系统查相应的文件（夹）的大小得知，也可以通过SQL语句SHOW TABLE STATUS查得
缺点：没有数据完整性机制，即不支持事务和外键

**修改引擎：**

ALTER TABLE tablename ENGINE = MyISAM ；

4.一个建表标准

DROP TABLE IF EXISTS `tb_category`;
CREATE TABLE `tb_category` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '类目id',
 `parent_id` bigint(20) NOT NULL COMMENT '父类目id,顶级类目填0',
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `key_parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1424（自增起始） DEFAULT CHARSET=utf8 COMMENT='商品类目表，类目和商品(spu)是一对多关系，类目与品牌是多对多关系';

三、常见函数

函数的使用位置在语句中是相对自由的

数据类型转换函数

1、CAST()
CAST (<expression> AS <data_ type>[ length ])

2、CONVERT()
CONVERT (<data_ type>[ length ]， <expression> [style])

cast函数应用详解

日期函数

1、day(date_expression) 
返回date_expression中的日期值

2、month(date_expression)
返回date_expression中的月份值

3、year(date_expression)
返回date_expression中的年份值

4、DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：
MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR...

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders

5、timediff、timestampdiff、datediff
5.1、timestampdiff
SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); # 104
5.2、datediff
SELECT DATEDIFF('2013-01-13','2012-10-01'); # 104
5.3、timediff
SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');# 49:57:00

6、DATEPART()
DATEPART (<datepart>， <date>)
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART (dd， date) 等同于DAY (date)
DATEPART (mm， date) 等同于MONTH (date)
DATEPART (yy， date) 等同于YEAR (date)

7、其他
SELECT NOW(); # 2018-05-21 14:41:00
SELECT CURDATE(); # 2018-05-21
SELECT CURTIME(); # 14:41:38

流程控制函数

1.if函数： if else 的效果
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注 FROM employees;
2.case函数的使用：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

统计与数学函数

AVG （ ） -返回的平均价值
count（ ） -返回的行数
first（ ） -返回第一个值
last（ ） -返回最后一个值
max（ ） -返回的最大价值
min（ ） -返回最小的价值
total（ ） -返回的总和
=========================
abs(numeric_expr)                   求绝对值
ceiling(numeric_expr)                取大于等于指定值的最小整数
exp(float_expr)                      取指数
floor(numeric_expr)                   小于等于指定值得最大整数
pi()                                  3.1415926.........
power(numeric_expr,power)            返回power次方      
rand([int_expr])                     随机数产生器
round(numeric_expr,int_expr)         安int_expr规定的精度四舍五入
sign(int_expr)                      根据正数,0,负数,,返回+1,0,-1
sqrt(float_expr)                     平方根

四、DML

insert into 表名(列名1,...列名n) values(值1,...值n);
delete from 表名 [where 条件]
TRUNCATE TABLE 表名;(删除表内所有数据)
update 表名 set 列名1 = 值1, ... [where 条件];

五、DQL

语法：
	select
		字段列表
	from
		表名列表
	where
		条件列表
	group by X,Y
		分组字段：常规用法是配合聚合函数；GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。分组之后的数据其他字段要唯一！
	having
		分组之后的条件
	order by A ASC,BDESC
		排序:多条件满足第一个条件才判断第二条件。
	limit 5(前五条数据)/3,5(第三条后的五条记录)
		分页限定：mysql的方言
基础查询:SELECT 后跟基础查询
	1. 去除重复：
		* distinct
		select distinct t1.id from t1
	2. 起别名：
		* as：as也可以省略

条件查询
	1. where子句后跟条件
	2. 运算符
		* > 、< 、<= 、>= 、= 、<>
		* BETWEEN...AND  
		* IN( 集合) 
		* LIKE：模糊查询
			* 占位符：
				* _:单个任意字符
				* %：多个任意字符
		* IS NULL  
		* and  或 &&
		* or  或 || 
		* not  或 !

SELECT( SELECT d.dept_name FROM departments d WHERE de.dept_no = d.dept_no ) AS 部门,
count( de.emp_no ) AS 人数 
FROM
	dept_emp de 
WHERE
	de.to_date = '9999-01-01' 
GROUP BY
	de.dept_no 
HAVING
	count( de.emp_no ) > 30000

六、函数与存储过程

存储过程

DELIMITER $
DROP PROCEDURE test_randstr_insert$

CREATE PROCEDURE test_randstr_insert (IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1 ;
DECLARE str VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxyz' ;
DECLARE startIndex INT ; #代表初始索引
DECLARE len INT ; #代表截取的字符长度
WHILE i <= insertcount DO
SET startIndex = FLOOR(RAND() * 26 + 1) ; #代表初始索引，随机范围1-26
SET len = FLOOR(RAND() *(26 - startIndex + 1) + 1) ; #代表截取长度，随机范围1-（26-startIndex+1）
IF len < 3 THEN
SET len = len + 3 ;
END IF;
IF startIndex > 23 THEN
SET startIndex = FLOOR(RAND() * 23 + 1) ;
END IF; 
INSERT INTO `student` (	`s_id`,	`m_id`,	`s_name`,	`birthtime`,	`grnder`)
VALUES	(FLOOR(RAND() * 10000000),FLOOR(RAND() * 10) + 1,SUBSTR(str, startIndex, len),
         date(from_unixtime(unix_timestamp('1998-01-01') + floor(
             rand() * (unix_timestamp('2003-12-31') - unix_timestamp('1998-01-01') + 1)))),FLOOR(RAND() + 0.5));
SET i = i + 1 ;
END WHILE; 
END$

CALL test_randstr_insert (100000) ; 

函数

-- WHERE循环中的continue：ITERATE a(循环名);
-- WHERE循环中的break：LEAVE a(循环名);
DELIMITER $
DROP PROCEDURE clear_student$

CREATE FUNCTION clear_student (maxLen INT) RETURNS INT
BEGIN
DECLARE i INT DEFAULT 0 ;
DECLARE sName VARCHAR (26) DEFAULT '' ; 
a : WHILE i < (SELECT max(id) FROM student) DO
SET sName = (SELECT	s_name	FROM student WHERE		id = i) ;
IF LENGTH(sName) > maxLen THEN
	UPDATE student SET s_name = SUBSTR(s_name, 1, 3) WHERE	id = i ;
ELSEIF LENGTH(sName) < 3 THEN
	LEAVE a ;
END IF;
SET i = i + 1 ;
END WHILE a ; 
RETURN i ; 
END$

SELECT	clear_student (5)$

七、事务

事务：是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）；

事务的四大特性：
1 、原子性 (atomicity）:强调事务的不可分割.
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性 （consistency）:事务的执行的前后数据的完整性保持一致.
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

3 、隔离性 （isolation）:一个事务执行的过程中,不应该受到其他事务的干扰
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性 （durability） :事务一旦结束,数据就持久到数据库
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

事物危害

串行化：所有事物依次执行。

隔离级别：

1. read uncommitted：读未提交

    		* 产生的问题：脏读、不可重复读、幻读
   

2. read committed：读已提交 （Oracle）

    		* 产生的问题：不可重复读、幻读
   

3. repeatable read：可重复读 （MySQL默认）

 ​		* 产生的问题：幻读

不可重复毒侧重于两次的数据值不一样（update），幻读侧重是全表操作后两次的数据量不一样呀（insert/delete）。

4. serializable：串行化，所有事物依次执行。
   * 可以解决所有的问题

   • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
   • 数据库查询隔离级别：
     • select @@tx_isolation;
   • 数据库设置隔离级别：
     • set global transaction isolation level 级别字符串;

用userId做的分库分表，现在需要用电话号码查询怎么办？

和回表逻辑一样，单独建一个电话号码索引表，存放电话号码和userId，查询时先根据电话号码查询userId，然后再根据userId查询数据。