MySQL__速成一套（part1）

简介

• MySQL是关系型数据库管理系统
• 目前相对热门且流行的数据库工具之一
• 有免费版也有收费版

特点
 

效率	关系数据库将数据保存在不同的表中,相对于所有数据存放在一个大仓库,不仅提高了速度也提高了灵活度。
开源	平民玩家也能使用MySQL,可免费可收费
方便	线上线下都能能操作数据库，支持离线保存或线上保存数据
底层	MySQL使用 C和 C++编写
API	几乎任何编程语言都支持MySQL提供的API
提供	提供TCP/IP,ODBC和JDBC等多种数据库连接途径

安装步骤

1. 主页 - https://www.mysql.com/cn/downloads/
2. 选项 - MySQL Community Server

安装之后

1.数据库端口号默认:3306

2.环境变量设置

新建变量

• 变量名（MYSQL_HOME）
• 变量值 （C:\\Program Files\\MySQL\\MySQL Server 8.0）

路径添加

• Path路径添加 - C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin

连接数据库

终端 ：mysql -u root -p

SQL通用方法

格式

• SQL语句可以单行或多行书写,以分号结尾

• SQL语句可以使用空格/缩进来增强语句的可读性

• SQL语句不区分大写,关键字建议使用大写

注释

• 单行注释（--注释内容 或 #注释内容）
• 多行注释（/*注释内容*/）

体系结构

1.连接层

最上层是一些客户端和链接服务,主要完成一些类似于连接处理

授权认证,及相关的安全方案。

2.服务层

核心服务功能,如SQL接口,并完成缓存的查询，

SQL的分析和优化,部分内置函数的执行,所有跨存储引擎的功能也在这一层实现。

3.引擎层

负责数据的存储和提取,服务器通过API和存储引擎进行通信,

不同的存储引擎具有不同的功能。

4.存储层

主要是将数据存储在文件系统上,并完成与存储引擎的交互。

常用指令表

终端开启

1.终端（连接数据库）

>>> mysql [-h 127.0.0.1] [-p 3306] -u root -p

2.终端（打开数据库）

>>> mysql -u root -p

数据库（操作）
创建数据库	【语法】  CREATE DATABASE  [IF NOT EXISTS]  数据库名  [DEFAULT CHARSET 字符集]   [COLLATE 排序规则] 【默认创建 】  create database items 【不存在则创建】  create database if not exists items
切换数据库	use 数据库名
查询所有数据库	SHOW DATABASES
查询当前数据库	select database()
删除	【语法】   DROP DATABASE  [IF EXISTS]  数据库名 【存在数据库则删除】  DROP DATABASE  if exists  items2
数据库（用户）
查询用户	USE mysql SELECT * FROM user
创建用户	CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'
修改用户密码	ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'
删除用户	DROP USER '用户名'@'主机名'
注释：主机名可以使用%通配
数据库（权限）
查询权限	【语法】   SHOW GRANTS FOR '用户名'@'主机名' 【例子】  SHOW GRANTS FOR 'piter'@'localhost
授予权限	【语法】  GRANT 权限列表 ON 数据库名.表名 TO   '用户名'@'主机名'; 【例子】  GRANT ALL ON root.* TO   'piter'@'localhost
撤销权限	【语法】  REVOKE 权限列表 ON 数据库名.表名   FROM '用户名'@'主机名' 【例子】  REVOKE ALL ON root.* from   'piter'@'localhost'
数据库（字符集）
设置字符集	create database items default charset utf8mb4; (不推荐utf8,存储长度只有3个字节)
数据库（字段）
添加字段	【语法】  ALTER TABLE 表名  ADD 字段名  类型(长度)   [COMMENT 注释] [约束] 【例子】 alter table emp add nickname VARCHAR(20) COMMENT '昵称'
修改字段	【修改字段（语法）】  修改数据类型 ALTER TABLE 表名  MODIFY 字段名 新数据类型(长度) 【修改字段名和字段类型（语法）】  ALTER TABLE 表名  CHANGE 旧字段名 新字段名  类型(长度)  [COMMENT 注释] [约束] 【修改字段和字段类型（例子）】  ALTER TABLE emp  CHANGE nickname  username   VARCHAR(30)  COMMENT '用户名'
删除字段	【语法】  ALTER TABLE 表名  DROP 字段名 【例子】  alter table emp  DROP else_info
数据表（操作）
创建表	【查询该表的SQL语句结构】  show create table 表名
查询所有表	show tables
查询表结构	desc 表名
多表查询	【方法1】  select * from  emp,dept  where emp.id = dept.id 【方法2】  select emp.name,dept.name   from emp,dept  where emp.id = dept.id
修改表名	【语法】  ALTER TABLE 表名  RENAME TO 新表名
删除表名	【语法】  DROP TABLE  [IF EXISTS] 表名 【删除指定表,并重新创建该表】  TRUNCATE TABLE 表名
语句（查询）
查询所有内容	【语法】  SELECT * FROM 表名 【例子】  SELECT * FROM person
查询指定内容	【语法】  SELECT 字段1,字段2,字段3...   FROM 表名 【例子】  SELECT id,name,age  FROM person
筛选查询内容	【重复的去除掉（语法）】  SELECT DISTINCT 字段列表   FROM 表名 【重复的去除掉（例子）】  SELECT DISTINCT age  FROM person
分页查询	【语法】  SELECT 字段列表  FROM 表名  LIMIT 起始索引,查询记录数 【注释】  起始索引从0开始,  起始索引=(查询页码-1)*每页显示记   录数，  分页查询是数据库的方言,  不同的数据库由不同的实现,  MySQL中式LIMIT，  如果查询的是第一页数据,起始索引   可以省略,直接简写为limit 10 【例子】  SELECT *  FROM person  LIMIT   10,5  //从索引10开始 显示5条数据
条件查询	【语法】  SELECT 字段列表  FROM 表名  WHERE 条件列表 【大小判断】  select *  from person  where age<=20 【是否非NULL】  select *  from PERSON  where gender is not null 【逻辑运算符判断】  select * from person  where age>=20 and age<25 【BETWEEN方法    (min与max之间)】  SELECT *  FROM person  WHERE age  BETWEEN 25 AND 30  【IN方法(类似于OR)】  SELECT *  FROM person  WHERE age in(20,25,30) 【LIKE(类似于正则表达式)】  SELECT *  FROM person  WHERE name like '__'  (占位符2个,匹配占2个字符的姓名) 【LIKE方法2】  SELECT *  FROM person  WHERE name like   '黄%' （匹配姓名开头为黄的数据）
排序查询	【语法】  SELECT 字段列表  FROM 表名  ORDER BY  字段1 排序方式1 ,  字段2 排序方式2 【排序方式】  ASC:升序(默认值)  DESC:降序 【方法1】  SELECT *  FROM person  ORDER BY  age ASC;   (升序排序 DESC降序) 【方法2】  SELECT *  FROM person  ORDER BY  id DESC ,  age ASC
分组查询	【语法】 SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HANG 分组后过滤条件] 【参数】  (count 统计数量,max 最大值,min 最小   值,avg 平均值,sum 总和)  【例子1】  SELECT gender,avg(age)  FROM person  WHERE age < 50  GROUP BY gender 【例子2】  SELECT gender,count(*)  FROM person  GROUP BY gender  (对性别进行分组,统计男女各有多少数量) 【例子3】  SELECT avg(age) FROM person; (统计平均年龄) 【例子4】  SELECT count(*) FROM person
语句（插入）
插入数据	【给指定字段添加数据（语法）】  INSERT INTO 表名  (字段名1,字段名2...)  VALUES(值1,值2...) 【给指定字段添加数据（例子）】  INSERT INTO person   (id,name,age)  VALUES(1,'joe','20') 【给全部字段添加数据（语法）】  INSERT INTO 表名  VALUES(值1,值2...) 【批量添加数据（语法）】  INSERT INTO 表名  (字段名1,字段名2,...)    VALUES(值1,值2),(值1,值2)
语句（修改）
修改数据	【语法】  UPDATE 表名  SET  字段名1=值1,字段名2=值2,  [WHERE 条件] 【例子】 UPDATE person SET age='25' WHERE name='joe';
修改别名	【语法】  SELECT  字段1[AS 别名1],  字段2[AS 别名2]...     FROM 表名
语句（删除）
删除数据	【语法】  DELETE  FROM 表名  [WHERE 条件]

四大语句

DDL语句

简介：Data Definition Language 数据定义语言,用来定义数据对象(数据库,表,字段)

数据类型

数值类型
类型	大小(byte)	有符号(SIGNED)	无符号(UNSIGNED)	描述值
注释：数值作为大约参考 例子：-32768,32767 约等于 -3万 ~ 3万
TINYINT	1	-128,127	0,255	小整数
SMALLINT	2	-3万 ~ 3万	0 ~ 6万	大整数
MEDIUMINT	3	-838万 ~ 838万	0 ~ 1677万	大整数
INT或INTEGER	4	-21亿 ~ 21亿	0 ~ 42亿	大整数
FLOAT	4	(-3.4E+38 ~ 3.4E+38)	(0 和 1.1E-38 ~ 3.4E+38)	单精度浮点数值
BIGINT	8	(-2^63,2^63-1)	(0,2^64-1)	极大整数
DOUBLE	8	(-1.79 E+308 ~ 1.79 E+308)	(0和2.2E-308,1.79E+308)	双精度浮点数值
DECIMAL	依赖于M(精度)和D(标度)的值 例子:123.45 (精度:123 标度:45)			小整值(精确定点数)

字符串类型
类型	大小(byte)	描述
CHAR	0~255	定长字符串
注释：相对于VARCHAR 使用CHAR性能更高,短字符串推荐使用CHAR
VARCHAR	0~65535	变长字符串
注释:长字符串使用VARCHAR
TINYBLOB	0~255	不超过255个字符的二进制数据
TINYTEXT	0~255	短文本字符串
BLOB	0~65535	二进制形式的长文本数据
TEXT	0~65535	长文本数据
MEDIUMBLOB	0~1677万	二进制形式的中等长度文本数据
MEDIUMTEXT	0~1677万	中等长度文本数据
LONGBLOB	0~42亿	二进制形式的极大文本数据
LOONGEXT	0~42亿	极大文本数据

日期类型
类型	大小(byte)	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值,时间戳

DML语句

简介：Data Manipulation Language 数据操作语言,用来对数据库表中的数据进行增删改

DQL语句

简介：Data Query Language 数据查询语言,用来查询数据库中表的记录

基本查询
字段查询	SELECT
表查询	FROM
条件查询	WHERE
分组查询	GROUP BY
排序查询	ORDER BY
分页查询	LIMIT
聚合函数	COUNT
其它查询	HANG

条件参数

• 【大于或大于等于】>，>=
• 【小于或小于等于】<，<=
• 【等于】=
• 【不等于】<>，!=
• 【某个范围之内(含最小,最大值)】BETWEEN...AND...
• 【列表中的值,多选一】IN(...)
• 【占位符-匹配方式】LIKE
• 【IS_NULL 是NULL】
• 【同】AND，&&
• 【或】OR，||
• 【非】NOT，!

DCL语句

简介：Data Control Language 数据控制语言,用来创建数据库用户,控制数据库的访问权限

进阶操作

函数列表

字符串
concat	CONCAT(S1,S2,...Sn)	字符串拼接,将S1...Sn 拼接成一个字符串
lower	LOWER(str)	将字符串str全部转换为小写
upper	UPPER(str)	将字符串str全部转换为大写
LPAD	LPAD(str,n,pad)	左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
RPAD	RPAD(str,n,pad)	右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
TRIM	TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING	SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

数值函数
ceil	CEIL(x)	向上取整
floor	FLOOR(x)	向下取整
mod	MOD(x,y)	x/y的模
rand	RAND()	返回0~1内随机数
round	ROUND(x,y)	四舍五入,保留y位小数

日期函数
curdate	CURDATE()	返回当前日期
curtime	CURTIME()	返回当前时间
now	NOW()	返回当前日期和时间
year	YEAR(date)	获取指定date的年份
month	MONTH(date)	获取指定date的月份
day	DAY(date)	获取指定date的日期
date_add	DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff	DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

流程函数
if	IF(value,t,f)	如果value为true , 则返回t,否则返回f
ifnull	IFNULL(value1,value2)	如果value1不为空,返回value1,否则返回value2
case_1	CASE WHEN [val1] THEN [res1] ...ELSE[default] END	如果val1为true,返回res1,...否则返回default默认值
case_2	CASE [expr] WHEN [val1] THEN [ res1] ...ELSE[default] END	如果expr的值等于val1,返回res1,...否则返回default默认值

约束

约束是作用于表中字段上的规则,用于限制存储在表中的数据

约束类型
非空约束	NOT NULL	限制该字段的数据不能为null
唯一约束	UNIQUE	保证该字段的所有数据都是唯一的,不重复的
主键约束	PRIMARY KEY	主键是一行数据的唯一标识,要求非空且唯一
默认约束	DEFAULT	保存数据时,如果未指定该字段的值,则采用默认值
检查约束	CHECK	保证字段值满足某一个条件
外键约束	FOREIGN KEY	两张表,进行关联 || 进行同步更新 用来让两张表的数据之间建立连接,保证数据的一致性和完整性

外键
添加外键	CREATE TABLE 表名(          字段名 数据类型         ...         [CONSTRAINT] [外键名称]         FOREIGN KEY(外键字段名)         REFERENCES 主表(主表列名) ) ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
删除外键	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
删除/更新行为
NOT ACTION	当在父表中删除/更新对应记录时, 首先检查该记录是否有对应外键, 如果有则不允许删除/更新, (与RESTRICT一致)
RESTRICT	当在父表中删除/更新对应记录时, 首先检查该记录是否有对应外键, 如果有则不允许删除/更新 (与NO ACTION 一致)
CASCADE	当在父表中删除/更新对应记录时, 首先检查该记录是否有对应外键, 如果有,则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除对应记录时, 首先检查该记录是否有对应外键, 如果有则设置子表中该外键值为null (这就要求外键允许取null)
SET DEFAULT	父表有变更时, 子表将外键列 设置成一个默认的值 (Innodb不支持)