MySQL笔记

MySQL笔记

一、SQL分类介绍

SQL：Structured Query Language(结构化查询语言)

1、DDL

数据定义语言(Data Definition Language)

用于管理数据库对象，比如创建数据库、表、索引、视图等等；

(1)CREATE:创建数据库对象

(2)ALTER:修改数据库对象

(3)DROP:删除数据库对象

2、DML

数据操作语言(Data Manipulation Language)

用于管理数据库中表的数据，比如增删改操作

关键词：INSERT、UPDATE、DELETE

3、DQL

数据查询语言(Data Query Language)

用于从表中检索数据，关键词是select from where

4、DCL

数据控制语言(Data Control Language)

实现权限管理，控制用户可以访问的数据库对象，关键词Grant和Revoke

二、DDL系列操作

1、创建数据库时，默认会使用UTF8MB4编码，后面带有mb4，表示可以保存特殊字符表情符号

2、操作数据库的常用语句

/*显示当前数据库*/
SHOW DATABASES;
/*创建数据库*/
CREATE DATABASE JAVA001;
CREATE DATABASE IF NOT EXISTS JAVA001;
/*删除数据库*/
DROP DATABASE JAVA001;
DROP DATABASE IF EXISTS JAVA001;
/*使用或切换数据库*/
USE JAVA001;
/*查看当前数据库*/
SELECT DATABASE();

3、操作数据表的常用语句

/*显示当前数据库所有表*/
SHOW TABLES;
/*查看表结构*/
DESC T_STUDENT;
/*创建表*/
CREATE TABLE 表名(
	字段名1	数据类型1,
  	字段名2	数据类型2,
  	...
  	字段名n	数据类型n
);
/*删除表*/
DROP TABLE IF EXISTS 表名;
/*修改表*/
ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE 表名 ADD COLUMN 字段名 数据类型;
ALTER TABLE 表名 DROP COLUMN 字段名;

三、MySQL数据类型

1、整数

定义时在括号中写数字不能定义实际占用的存储长度

数据类型	长度（一个字节8位）
tinyint	1个字节，无符号（unsigned）0-255(28-1)，有符号-128(27)~127(2^7-1)
smallint	2个字节，无符号0-65535(216-1)，有符号-32768(215)~32767(2^15-1)
mediumit	3个字节
int	4个字节
bigint	8个字节

括号的n表示显示长度，并不影响实际占用的存储长度。

2、浮点数

数据类型	长度
float(总长度，小数点后保留的位数)	4个字节
double(总长度，小数点后保留的位数)	8个字节

计算时不精确，如果保存金额小数点，最好采用decimal类型。

3、字符类型

数据类型	长度
char(n)	固定长度，n取值在1-255之间，总长度为n
varchar(n)	可变长度，n取值在1-65535之间，总长度为n+1
text	可变长度，最大长度为2^16-1(65535)
mediumtext	可变长度，最大长度为2^24-1
longtext	可变长度，最大长度为2^32-1

4、日期类型

数据类型	含义
date	日期，只存储年月日
time	时间，只存储时分秒
datetime	日期时间，存储年月日时分秒（8个字节，从1001年到9999年）
timestamp	日期时间，存储时间戳（4个字节，从1970年1月1日以来的毫秒数，只能表示到2038年）

四、约束

为什么要有约束？

为了对表中的数据做限制性的要求，从而保证数据的正确性。

1、非空约束(NOT NULL)

CREATE TABLE T_STUDENT(
	ID INT,
	NAME VARCHAR(12) NOT NULL,
  	PASSWORD CHAR(32) NOT NULL,
  	AGE TINYINT,
  	CARD_NO CHAR(18) NOT NULL
);

2、唯一约束(UNIQUE)

CREATE TABLE T_STUDENT(
	ID INT,
	NAME VARCHAR(12) NOT NULL,
  	PASSWORD CHAR(32) NOT NULL,
  	AGE TINYINT,
  	CARD_NO CHAR(18) NOT NULL UNIQUE
);

3、默认约束(默认值DEFAULT)

CREATE TABLE T_STUDENT(
	ID INT,
	NAME VARCHAR(12) NOT NULL,
  	PASSWORD CHAR(32) NOT NULL,
  	AGE TINYINT DEFAULT 18,
  	CARD_NO CHAR(18) NOT NULL UNIQUE
);

4、主键约束(PRIMARY KEY)

唯一约束+非空约束

CREATE TABLE T_STUDENT(
	ID INT PRIMARY KEY,
	NAME VARCHAR(12) NOT NULL,
  	PASSWORD CHAR(32) NOT NULL,
  	AGE TINYINT DEFAULT 18,
  	CARD_NO CHAR(18) NOT NULL UNIQUE
);

5、自增(AUTO_INCREMENT)

CREATE TABLE T_STUDENT(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(12) NOT NULL,
  	PASSWORD CHAR(32) NOT NULL,
  	AGE TINYINT DEFAULT 18,
  	CARD_NO CHAR(18) NOT NULL UNIQUE
);

五、操作数据库表数据-DML

1、插入

INSERT INTO 表名 values(值1,值2,值n);
INSERT INTO 表名(字段1,字段2,字段3) VALUES(值1,值2,值3);

2、更新

UPDATE 表名 SET 列名1=值,列名2=值 WHERE 条件;

3、删除

/*删除前一定要先做备份*/
DELETE FROM 表名 WHERE 条件;
/*不会记录日志，效率更高，但不安全，尽量不要使用*/
TRUNCATE TABLE 表名;

六、DQL查询数据

1、 选择列

2、 选择行

关键词LIKE

/*模糊查询*/
/*以888结尾*/
LIKE '%888';
/*以55开头*/
LIKE '55%';
/*存在asd*/
LIKE '%asd%';
/*第二位是a*/
LIKE '_a%';

3、排序

ORDER BY
默认是升序ASC，降序是DESC

4、分页

关键字LIMIT

/*每页展示2条记录，显示第一页*/
/*LIMIT 开始位置(从0开始),查询的数量*/
SELECT * FROM T_STUDENT LIMIT 0,2;
/*每页展示2条记录，显示第二页*/
SELECT * FROM T_STUDENT LIMIT 2,2;
/*每页展示2条记录，显示第三页*/
SELECT * FROM T_STUDENT LIMIT 4,2;
/*总结规律：起始位置=(当前页码-1)*每页展示数量*/

七、聚合函数

用于统计数据

/*获取总记录数COUNT()*/
SELECT COUNT(1) FROM T_STUDENT;
/*如果count中的列值为null则不参与计算*/
SELECT COUNT(AGE) FROM T_STUDENT;//如果age的值为NULL则不参与计算
/*求学生的平均年龄*/
SELECT AVG(AGE) FROM T_STUDENT;
/*求最大最小年龄*/
SELECT MAX(AGE),MIN(AGE) FROM T_STUDENT;
/*计算工资的总和*/
SELECT SUM(SALARY) FROM T_STUDENT;

八、分组查询

将数据库表中的记录按照指定的类别进行分组，分为小数据集

//[]中的可写可不写
SELECT 分组字段/聚合函数
FROM 表名
[WHERE条件(分组前的过滤条件)]
GROUP BY 分组列名 [HAVING 条件(分组后的过滤条件)]
[ORDER BY 排序字段]

//按性别分组，分别查询有多少人
SELECT SEX,COUNT(SEX) FROM T_STUDENT GROUP BY SEX;
//查询男女同学的平均工资
SELECT SEX,AVG(SALARY) FROM T_STUDENT GROUP BY SEX;
//查询每个部门不同城市的平均工资
SELECT DEPT_NAME,AVG(SALARY)
FROM T_EMPLOYEE
GROUP BY DEPT_NAME,CITY;

九、条件表达式

--CASE
--等值比较，如果等于某个value，则为某个result
CASE 表达式
	WHEN 值1 THEN 结果1
	WHEN 值2 THAN 结果2
	[ELSE 值3]--可以没有
END
--比如：性别存值为0和1，查询语句显示男或女
SELECT NAME,
CASE SEX
	WHEN '1' THEN '男'
	WHEN '0' THEN '女'
END
FROM T_EMPLOYEE;
--条件表达式比较
CASE
	WHEN 比较式1 THEN 结果1
	WHEN 比较式2 THEN 结果2
	[ELSE 结果3]--可以没有
END
--举例查询学生成绩，90以上是优秀，60-90是不及格，60一下是及格
SELECT NAME,
CASE
	WHEN SCORE<60 THEN '不及格'
	WHEN SCORE<90 THEN '及格'
	ELSE '优秀'
END
FROM T_STUDENT;

十、条件表达式+分组

题目：行列转换
1、什么是行列转换
期望的结果如下：
学生姓名|java|Python
张三 |100 |95
李四 |88 |61

SELECT 
student_name,
sum(CASE course_name
	WHEN 'java' THEN course_score ELSE 0
END) 'Java',
sum(CASE course_name
	WHEN 'Python' THEN course_score ELSE 0
END) 'Python'
FROM t_student_score GROUP BY student_name;

十一、多表关系及约束

1、一对一

一对一关系

学号-学生
身份证-公民

实现方式

如果对应的信息是经常需要一起展示的，放在一张表即可；否则最好分为多张表
在任意一方加入外键，关联另一方主键，并设置外键为unique即可

--举例
--商品表
CREATE TABLE T_PRODUCT_INFO(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(24),
	PRICE DECIMAL(18,6),
	IMAGE VARCHAR(255)--一般存储图片的地址信息
);
--商品详情表
CREATE TBALE T_PRODUCT_DESC(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	PRODUCT_DESC TEXT,--一般存储富文本（如<html></html>）
	PRODUCT_ID INT UNIQUE,
	CONSTRAINT FK_PRODUCT_ID FOREIGN KEY(PRODUCT_ID) REFERENCES T_PRODUCT_INFO(ID)
);

2、一对多

举例

比如部门和员工，一个部门对应多个员工，一个员工对应一个部门

实现方式

在多的一方建立外键，指向一的一方的主键。如在员工表建立dept_id外键

--员工表
CREATE TABLE T_EMPLOYEE(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(24) NOT NULL,
	SEX CHAR(1),
	DEPT_ID INT NOT NULL,
	CONSTRAINT FK_DEPT_ID FOREIGN KEY(DEPT_ID) REFERENCES T_DEPT(ID)
);
--部门表
CREATE TABLE T_DEPT(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(24) NOT NULL,
	MANAGER VARCHAR(24) NOT NULL
);

3、多对多

举例

比如商品和订单，一个订单可以包含多个商品，同款商品可以出现在多个订单中；
学生和课程，一个学生可以选择多门课程，一门课程可以被多个学生选择

实现方式

建立第三张中间表，建立两个外键，分别关联双方的主键

--学生表
CREATE TABLE T_STUDENT(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(24) NOT NULL,
	SEX CHAR(1)
);
--课程表
CREATE TABLE T_COURSE(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(24) NOT NULL
);
--中间表
CREATE TABLE T_STUDENT_COURSE(
	STUDENT_ID INT PRIMARY KEY,
	COURSE_ID INT PRIMARY KEY,
	CONSTRAINT FK_STUDENT_ID FOREIGN KEY(STUDENT_ID) REFERENCES T_STUDENT(ID),
	CONSTRAINT FK_COURSE_ID FOREIGN KEY(COURSE_ID) REFERENCES T_COURSE(ID)
);

十二、外键约束

删除问题

比如：部门——员工是一对多的关系，如果直接删除部门，可能会报错。因为有外键约束

解决方法1

1、先将对应部门的员工id修改为其他部门
2、删除部门

解决方法2

级联删除
将外键的on_delete属性设为cascade（通常不建议这样使用，会删除不应该删除的数据）

开发过程中对于删除的处理

并不会直接delete数据，而是进行逻辑删除，给一个标识字段，通过修改这个字段的值来判断这个数据是否被删除

级联更新

将on_update修改为cascade，这样当外键修改，则关联的记录也会被修改

十三、多表联查

1、内连接（inner join）

内连接相当于查询两个表的交集数据（共有的数据）

SELECT D.NAME,E.NAME FROM T_DEPT D INNER JOIN T_EMPLOYEE ON D.ID=E.DEPT_ID;

2、左连接（left join）

左外连接：相当于查询左边表的所有数据以及和右边表的交集部分数据

SELECT D.NAME,E.NAME FROM T_DEPT D LEFT JOIN T_EMPLOYEE ON D.ID=E.DEPT_ID;

3、右连接（right join）

右外连接：相当于查询右边表的所有数据以及和左边表的交集部分数据

SELECT D.NAME,E.NAME FROM T_DEPT D RIGHT JOIN T_EMPLOYEE ON D.ID=E.DEPT_ID;

十四、子查询

1、什么是子查询

子查询是一个select语句，是嵌套在另一个select语句中的子句。
子查询的特点如下：
（1）子查询在主查询之前执行
（2）子查询的结果被用于主查询

2、单行子查询

子查询的结果只返回一行

SELECT NAME,SEX FROM T_EMPLOYEE WHERE DEPT_ID=(SELECT DEPT_ID FROM T_EMPLOYEE WHERE NAME='zhaoge');

3、多行子查询

子查询的结果返回多行

SELECT NAME,SEX FROM T_EMPLOYEE WHERE DEPT_ID IN(SELECT ID FROM T_DEPT WHERE NAME='JAVA');

十五、视图

1、为什么要用视图

1、安全性

实现只开放表中部分数据的效果

2、便捷性

可以将复杂的多表关联查询放到视图中，屏蔽底层的复杂性

2、创建视图

CREATE VIEW 视图名 AS 查询语句
--创建视图例子
CREATE VIEW T_EMPLOYEE_INFO
AS
SELECT e.ID,e.NAME,e.CITY FROM T_EMPLOYEE e;

3、视图的特性

视图的本质是一张虚拟表，实际的数据来源于数据表；
如果源数据发生变化，视图的查询结果也会变化；
如果通过视图更新数据，也会影响源数据表的数据，所以一般只给视图开放查询的权限；

4、修改视图

--方式1
CREATE OR REPLACE VIEW 视图名
AS 
查询语句
--方式2
ALTER VIEW 视图名
AS
查询语句

5、删除视图

--删除视图
DROP VIEW 视图名;

十六、索引

1、什么是索引

索引是一种提高查询效率的数据结构；
索引的优点是可以提高查询效率，缺点是会占用磁盘空间，且降低更新操作的效率；
可以将索引理解为一本书的目录。Mysql的索引有Hash和B+树两种结构；等值查找较多用Hash结构的索引；范围查找较多的用B+树结构的索引。

2、什么时候需要创建索引

1、频繁作为查询条件的字段添加索引
2、查询中频繁作为排序的字段添加索引
前提条件：索引的区分度高，索引的效率越高，比如ID、手机号。这样的列审核创建索引

3、什么时候不能创建索引

1、频繁更新的字段不适合创建索引
2、写多读少的表不适合创建索引
3、区分度低的字段，比如性别、删除标识字段等
4、数据量小的表不适合创建索引

4、索引的分类

1、普通索引
2、唯一索引——允许有空值，但是必须唯一
3、主键索引——唯一且不能为空
4、联合索引/组合索引
（1）什么时候要使用组合索引？
——比如要经常用多个字段组合查询信息时，将两个字段一起建一个组合索引。
（2）创建组合索引时要注意什么？
——将区分度高的字段放在前面。

--查看当前的索引
SHOW INDEX FROM 表名;
--创建普通索引——默认为普通索引
CREATE INDEX 索引名 ON 表名(字段名);
--创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
--创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
--组合索引(联合索引)
ALTER TABLE 表名 ADD INDEX 索引名(字段名1,字段名2);--先按前面的字段进行排列
--删除索引
DROP INDEX 索引名 ON 表名;

5、如何查看索引是否生效

查看执行计划
主要看possible_key（可能用上的索引），key（实际查询的索引），rows（扫描的记录数）；
尽量不要使用%在前的like，如果要这样查找，可以使用全文搜索引擎来解决

EXPLAIN 要执行的sql;