MySQL入门知识

一、数据库与SQL简述

1. 数据库简介

数据库就是用来存储电子文件的场所

1.1 数据库特点

1）数据共享
用户可以同时对数据库进行存取操作
2）数据一致性和可维护性
安全性控制、完整性控制、并发控制
3）数据恢复
有一定的故障修复措施，例如日志等

2. SQL简介

SQL是结构化查询语言的简称，是一种专门用来与数据库通信的语言

2.1 相关概念

数据库(database)：保存有组织的数据的容器
表(table)：数据库中存储某种特定类型数据的结构化清单
模式(schema)：schema是数据库对象的集合，这个集合包括了各种对象如：表、视图、存储过程、索引等
列(column)：又名字段(Field)，表都是由一个或者多个列组成的
行(rew)：表中的记录是按行存储的
数据类型：表所对应的数据类型，用于限制存储的数据。
主键(primary key)：可以将某一列或多个列作为主键，主键用于唯一表示表中的每个行

2.2 SQL特点

一体化：集数据定义、操作、查询、控制、事务一体
简单易学：语法简洁
使用方式灵活：命令交互、嵌入使用都可以
通用性强：几乎所有的数据库都支持

3. MySQL简介

MySQL是一个关系型数据库管理系统，输入Oracle旗下产品

3.1 MySQL特点

开源免费、支持多线程支持、大型数据库、为多种编程语言提供API接口，具有多种数据库连接途径

3.2 MySQL下载

下载链接: MySQL官网

3.3 MySQL常用操作

修改用户密码：mysqladmin -u 用户名 -p password
连接MySQL：mysql -h 主机地址 -u 用户名 -p 密码 -P 端口号
查看MySQL中所有的数据库：SHOW DATABASES
查看某个数据库的所有的表：SHOW TABLES
查看某个表所有的列：SHOW COLUMNS FROM 表名
退出MySQL：EXIT

二、数据定义语言DDL

1. 创建/删除数据库

1）创建
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET 默认编码集
或
CREATE DATABASE IF NOT EXISTS AMIGOXIE 数据库名称
例如：CREATE DATABASE test DEFAULT CHARACTER SET utf8
2）删除
DROP DATABASE 数据库名称
例如：DROP DATABASE test

2. 创建表

CREATE TABLE `数据表名`  (
  '列名1' int(6) COMMENT '注释信息',		# 数据类型（数据大小）
  '列名2' varchar(255) COMMENT '注释信息',
  '列名3' varchar(255) COMMENT '注释信息',
) ENGINE = InnoDB;		# 数据库引擎

数据库引擎：
1）InnoDB：一个可靠的事物处理引擎，不支持全文搜索
2）MEMORY：数据存储在内存中，速度更快更适合临时表
3）MyISAM：一个性能高的引擎，支持全文本检索，不支持事务处理。

3. 重命名和删除表

重命名表：RENAME TABLE 原表名 TO 新表名
删除表：DROP TABLE 表名

4. 更新表

4.1 更新注释

添加表的注释：ALTER TABLE 表名 COMMENT '注释信息'
添加字段注释：ALTER TABLE 表名 COLUMN 字段名 字段类型 COMMENT '修改后的字段注释'
查看字段注释：SHOW FULL COLUMNS FROM 表名

4.2 添加/删除字段

添加字段：ALTER TABLE 表名 ADD 字段名 字段类型
删除字段：ALTER TABLE 表名 DROP COLUMN 字段名

4.3 添加/删除外键

添加外键：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (字段名称) REFERENCES 被关联的表(被关联的字段) ON DELETE RESTRICT ON UPDATE RESTRICT
删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

三、数据操作语言DML

1. 插入数据

按顺序插入完整行：INSERT INTO 表名 VALUES(值1,值2,值3)
按字段插入完整行：INSERT INTO 表名(列名1,列名2,列名3) VALUES(值1,值2,值3)
按字段插入部分行：INSERT INTO 表名(列名1,列名2) VALUES(值1,值2)
批量插入多行：INSERT INTO 表名(列名1,列名2,列名3) VALUES(值11,值12,值13),(值21,值22,值23),(值31,值32,值33)

2. 更新数据

不带WHERE的更新语句 ：UPDATE 表名 SET 字段1=值1,字段2=值2
并或AND/OR的更新语句：UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE 字段3=值3 AND/OR 字段3=值3
模糊匹配LIKE更新语句：UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE 字段3 LIKE 值3
多条件匹配IN更新语句：UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE 字段3 IN (值3,值4,值5)

3. 删除数据

不带WHERE的删除语句 ：DELETE FROM 表名
并或AND/OR的删除语句：DELETE FROM 表名 WHERE 字段1=值1 AND/OR 字段2=值2
模糊匹配LIKE删除语句：DELETE FROM 表名 WHERE 字段1 LIKE 值1
多条件匹配IN更新语句：DELETE FROM 表名 WHERE 字段3 IN (值1,值2,值3)

四、数据查询语言DQL

1. 简单检索语句

检索单列语法SELECT 字段 FROM 表名
检索多列语法SELECT 字段1,字段2,字段3 FROM 表名
检索所有语法SELECT * FROM 表名
去重检索语法SELECT DISTINCT 字段 FROM 表名
限制检索语法SELECT 字段 FROM 表名 LIMIT 开始行,数量
指定表名检索语法：SELECT 表名.字段 FROM 表名

2. 简单查询结果排序

按照单列排序语法：SELECT 字段1,字段2 FROM 表名 ORDER BY 字段3
按照多列排序语法：SELECT 字段1,字段2 FROM 表名 ORDER BY 字段3,字段4
指定排序方向语法(DESC降序,ASC升序)：SELECT 字段1,字段2 FROM 表名 ORDER BY 字段3 DESC,字段4 ASC
查询最大/小值语法：SELECT 字段1,字段2 FROM 表名 ORDER BY 字段2 DESC/ASC LIMIT 1

3. 简单过滤数据

使用WHERE子句过滤语法：SELECT 字段1,字段2 FROM 表名 WHERE 字段3=值3
WHERE字段的操作符：

操作符	说明
<>	不等于
!=	不等于
<=	小于等于
IS NULL	记录为空
IS NOT NULL	记录不为空
BETWEEN a AND b	在ab两个值之间

4. 复杂过滤

4.1 使用AND、OR、IN、NOT IN和LIKE过滤数据

连接符	说明
AND	并
OR	或者
LIKE	模糊匹配
IN	集合中存在
INOT IN	集合中不存在

使用通配符%模糊匹配(%代表模糊的多个字符)：SELECT * FROM 表名 WHERE 字段 LIKE '%值'
使用通配符_模糊匹配(_代表模糊的单个字符)：SELECT * FROM 表名 WHERE 字段 LIKE '值_'
使用通配符会影响查询速度

4.2 使用正则表达式过滤数据

使用正则过滤匹配：SELECT * FROM 表名 WHERE 字段 REGEXP '正则表达式'

4.3 使用函数过滤数据

运算函数：字段可以使用算数运算符+，-，*，/ 进行加减乘除算术运算。

函数名称	说明
CONCAT(str)	用于将多个字符串拼接成较长的字符串
TRIM(str)	去除左右两侧空格
RTRIM(str)	去除右侧空格
LTRIM(str)	去除左侧空格
UPPER(str)	字符串转大写
LENGTH(str)	返回字符串长度
SUBSTRING(str,startIndex,endIndex)	返回从低startIndex位置到endIndex位置的字符串
COS/SIN/TAN(int)	返回数的余弦/正弦/正切值
ABS(int)	返回数的绝对值
SQRT(int)	返回数的平方根
EXP(int)	返回数的指数
RAND()	返回一个随机值
PI()	返回圆周率
NOW()	返回当前日期和时间
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE/TIME/YEAR/MONTH/DAY(time)	返回日期/时间/年份/月份/天数
HOUR/MINUTE/SECOND/DAYOFWEEK(time)	返回小时/分钟/秒/周几

4.4 使用聚类函数进行数据汇总

函数	说明
COUNT(字段/*)	求总数
SUM(字段)	求和
MAX(字段)	求最大值
MIN(字段)	求最小值
AVG(字段)	求平均值
MAX(字段)	求和

4.5 使用GROUP BY进行数据分组

GROUP BY多用于数据分组，类似求总数时。

#角色表 role
user_id |  role
  1     |  admin
  1     |   user
  2     |   user

SELECT user_id ,COUNT(*) AS count_number FROM role GROUP BY user_id

#结果
user_id |  count_number
  1     |       2
  1     |       1

注意事项

• GROUP BY 子句可以包含任意数量的列。
• GROUP BY 子句不能为聚合函数 。
• GROUP BY 子句必须出现在WHERE后，ORDER BY前。
• 如果分组内包含NULL，NULL也将作为一个分组返回。

HAVING 函数类似分组里面的WHERE，在分组中进行筛选过滤。
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
SELECT user_id ,COUNT(*) AS count_number FROM role GROUP BY user_id HAVING count(*)<=3

# SELECT子句的顺序 
SELECT 要返回的列表或者表达式
	FROM  要检索的数据表名
	WHERE 行级过滤
		GROUP BY 分组说明
		HAVING	 分组过滤
		ORDER BY 排序
		LIMIT	 要检索的行数

4.6 使用子查询

子查询是指在SELECT语句中嵌套SELECT语句
例：SELECT id,user_name FROM user WHERE id IN (SELECT DISTINCT basic_id FROM basic_info WHERE area='山东')
这里就是先从basic_info里面查找出area='山东’的basic_id然后在进行user表的一个查询。

• 当然也可以查询相反结果，只需要将IN改为NOT IN即可。
• 子查询可以嵌套多个，但是不好阅读和测试。

例：SELECT id,user_name (SELECT XXX) as temp_col FROM user

 id | user_name| temp_col 
  1       zjs	  temp

5. 联结查询

5.1 联系

两个表之间的关系成为联系，表之间的关系一般分为以下几种类型：

1. 一对一联系(1:1)：例如会员的基本信息表和会员的详信息表。
2. 一对多(1:n)和多对一联系(n:1)：商品、订单信息表：一人有多条订单(1:n)；多条订单对应一个商品(n:1)。
3. 多对多联系(n:n)：一般通过建立中间表，让他变成多对一或者一对多的关系。

使用联系的优缺点：

1. 优点：更有效储存、更方便处理、具有更大的可伸缩性。
2. 缺点：数据分布在多个表中，需要使用联结实现。

5.2 外键

外键为某个表中的某一列，它包括另一个表的主键值，定义了两个表之间的关系。

5.3 简单联结

简单联结，只需要联结所有的表以及相互之间的关联即可。

# 两表联结
SELECT b.id, u.user_name 
	FROM basic_info b,user_info u
	WHERE b.id = u.id;

# 三表联结
SELECT b.id, u.user_name, p.prodct_name
	FROM basic_info b, user_info u, prodct_name p
	WHERE b.id = u.id AND b.produc_id = p.id;

假设两表联结不加WHERE条件，表a有2条数据，表有6条数据，查出来就有2*6条数据。

注意事项：

• 处理联结比较耗费资源，不要添加不必要的表。
• 联结表越多，性能越差。
• 在写联结语句的时候存在多种SQL实现方式，找出最优解。

5.4 INNER JOIN内联结

INNER JOIN双表内联结：等值联结，只返回两个表中联结字段相等的行。

# 两表内联结
SELECT b.id, u.user_name 
	FROM basic_info b INNER JOIN user_info u
	ON b.id = u.id;

INNER JOIN多表内联结：只需要说明联结的所有的表，以及相互之间如何关系即可。

# 三表内联结
SELECT b.id, u.user_name 
	FROM basic_info b 
		INNER JOIN user_info u ON b.id = u.id
		INNER JOIN prodct_name p ON b.produc_id = p.id;

5.5 OUTER JOIN外联结

一个内联结的例子：
关联查询t_user(用户表)和t_order(订单表)，查询出：ID(用户id)、user_name(客户姓名)、login_name(登录名)和order_count(用户的订单数量)，并按照订单数量降序排列

# 内联结
SELECT
	u.id,
	u.user_name,
	u.login_name,
	o.buy_num AS order_count 
FROM
	t_user u
	INNER JOIN t_order o ON u.id = o.user_id 

内联结有一个特点，假如t_user(用户表)中有一个用户“没六”，联结的t_order订单表中没有““没六””的订单记录，那么联结查询不会包含这条记录。
这时候就用到外联结了：

# 左外联结
SELECT u.id, u.user_name,u.login_name, COUNT(o.id) AS order_count
	FROM t_user u LEFT OUTER JOIN t_order o ON u.id = o.user_id
	GROUP BY u.id
	ORDER BY order_count DESC;

这里左联了t_user(用户表)，以用户表为主，所以显示了右边为空的数据。
那么右联结就会以右边的表为主表

# 右外联结
SELECT u.id, u.user_name,u.login_name, COUNT(o.id) AS order_count
	FROM t_user u RIGHT OUTER JOIN t_order o ON u.id = o.user_id
	GROUP BY u.id
	ORDER BY order_count DESC;

总结：

• 左外联结：以左边表中的数据为基准，若左表有数据，右表没有数据，则右表中的数据显示为NULL。
• 右外联结：是左外连接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回NULL。

5.6 UNION组合查询

UNION用于将两条以上SELECT查询语句的结果拼接

	SELECT * FROM test_table WHERE id=1
	UNION
	SELECT * FROM test_table WHERE NAME LIKE "王%"

显然，这和以下语句功能相同
SELECT DISTINCT * FROM test_table WHERE id=1 AND NAME LIKE "王%"
UNION使用规则：

• 每个SELECT需要搜索相同的列、表达式或者聚合函数。
• 不同列的数据类型可以不同，但必须相互兼容
• UNION会自动去除重复的行，不想去除就改成 UNION ALL。
• 组合查询只能使用一条ORDER BY语句，跟在最后一个SELECT之后。

五、其他

1. 视图

视图是一个虚拟表，其内容由查询定义，包含一系列带有名称字段和行数据。
但是，视图并不在数据库中以存储的数据值集形式存在，行和列数据来自定义视图的查询所引用的表，并且在引用视图时动态生成。

1.1 视图的注意事项

• 视图可以嵌套，视图的查询SQL语句中可以包括另一个视图。
• 视图的SQL中可以包含ORDER BY进行排序，如果视图内部和外部同时存在排序，内部的ORDER BY会被覆盖。
• 视图和表不能同时使用。
• 不能在视图上创建索引，也不能有关联的触发器和默认值
• 视图可以正常使用SELECT语句进行检索，但是INSERT、UPDATE、DELETE语句会被限制，尽量不要对视图进行增删改操作。
• 每次查询视图时，实际上是执行视图中存在的语句，视图本身不包含数据，复杂视图会影响性能。

1.2 视图语法

创建视图语法：CREATE VIEW 视图 AS SELECT语句
查看视图定义语法：SHOW CREATE VIEW 视图
删除视图语法：DROP CREATE VIEW 视图
更新视图语法: CREATE OR REPLACE VIEW 视图 AS SELECT语句 或者先删除DROP后创建CREATE

1.3 视图应用场景

• 简化SQL，将复杂的SQL包装到已经定义好的视图中，简化查询时的复杂度。
• 重用某些SQL语句，可以在视图上再进行简单查询。
• 使用表的部分，让调用者不用应对复杂的表结构。
• 保护、过滤数据，对数据脱敏和信息保护有作用。
• 更改返回数据的格式，可以重新格式化检索出的数据。

2. 存储过程

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集。它存储在数据库中，编译过后会保存编译状态，用户通过指定存储过程的名称和参数来执行。
存储过程是数据库中一个重要对象，用在一些需要多条语句才能完成的场合。

2.1 储存过程的特点

• 把复杂的操作放在存储该过程中，简化用户操作，简化变动时修改的地方。
• 保持数据的完整性，提高数据库性能。
• 维护起来要求更高，但用户使用起来感觉更好。

2.2 储存过程语法

创建存储过程：

CREATE PROCEDURE 存储过程
BEGIN
SQL语句;
END;

但是这样存在一定的问题，如果从控制台输入，命令执行到第一个分号就会结束，就会报语法错误。
命令台输入应该改为以下方式，用两个 // 给他包起来就行：

DELIMITER //
CREATE PROCEDURE 存储过程
BEGIN
SQL语句;
END //

删除存储过程：DROP PROCEDURE 存储过程;
查看存储过程：SHOW PROCEDURE 存储过程;
调用存储过程：CALL 存储过程()

创建带输出的存储过程：
例子：创建一个get_product_price存储过程，查询输出商品信息表中的商品的最高价格、最低价格和平均价格

DELIMITER //
CREATE PROCEDURE get_product_price{
	OUT max_price DECIMIL(10,2),
	OUT min_price DECIMIL(10,2),
	OUT avg_price DECIMIL(10,2),
BEGIN
	SELECT MAX(current_price) INTO max_price FROM t_product;
	SELECT MIN(current_price) INTO max_price FROM t_product;
	SELECT AVG(current_price) INTO max_price FROM t_product;
END //
DELIMITER;

调用带输出的存储过程：
例子：调用刚刚那个get_product_price存储过程：CALL get_product_price(@max_price, @min_price, @avg_price)
调用带输入输出参数存储过程：
调用带输入输出参数存储过程语法：
Eg：调用order_product存储过程，插入一条订单数据，并更新订单对应商品的库存数量：

DELIMITER //
CREATE PROCEDURE order_product(
	IN user_id_in BIGINT,   
	IN prod_id_in BIGINT, 
	IN order_price_in DECIMAL(10, 2), 
	IN product_count_in INT,
	OUT return_value INT)
BEGIN 
	INSERT INTO t_order(user_id, prod_id, order_price, product_count, total_price, create_time) 
		VALUES (user_id_in, prod_id_in, order_price_in, product_count_in, order_price_in*product_count_in, NOW());
	UPDATE t_product SET current_count = current_count - product_count_in WHERE id= prod_id_in;
	SELECT COUNT(id) INTO return_value FROM t_order WHERE user_id= user_id_in AND prod_id= prod_id_in;
END //
DELIMITER ;

3. 触发器

触发器是用来保证数据完整性的一种方法，他是与表事件相关的特殊的存储过程。
触发器是由事件而非函数来触发的，比如对一个表进行增、删、改操作时可以激活触发器。因此触发器常用语加强数据完整性约束和业务规则等。

3.1 触发器分类

1. DDL触发器：对表进行操作时的触发器。
2. DML触发器：对表数据进行操作时的触发器。
3. 登陆触发器：对LOGIN事件的触发器。

3.2 触发器语法

创建触发器：

CREATE TRIGGER 触发器名称 # 需要在每个表中唯一，可以数据库中不唯一
触发时机 # BEFORE/AFTER
触发事件 ON 表名 # INSERT/UPDATE/DELETE
FOR EACH ROW # 对每一行操作

删除触发器：DROP TRIGGER 触发器名称

3.3 MySQL中的触发器

4. 仅支持DML触发器
5. 只有表支持触发器，视图和临时表不行
6. 每个表每个时间只允许有一个触发器，最多有2*3个触发器。(前 / 后) * (增 / 删 / 改）

4. 游标

游标提供了一种对从表中检索出的数据进行操作的灵活手段，实际上一种从多条数据记录的结果集中每次提取一条的机制。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。
MySQL中的游标只能用用于存储过程或存储函数。

4.1 使用游标

1. 声明游标，定义需要使用游标的SELECT语句(此时未进行检索)。
2. 打开游标。
3. 根据需要取出各行。
4. 结束操作后，关闭游标。

4.2 游标语法

声明游标：DECLARE 游标 CURSOR FOR SELECT语句
打开游标：OPEN 游标
关闭游标：CLOSE 游标

5 事务处理语言TPL

事物主要用于维护数据完整性，批处理要么全部完成、要么全部失败。
InnoDB支持事务处理，而MyISAM就不支持。
开启事务：START TRANSACTION;
回滚事物：ROLLBACK; 或 ROLLBACK TO 保存点;
提交事务：COMMIT;
设置保存点：SAVEPOINT 保存点;
自动提交：SET autocommit=0/1;

6 数据控制语言DCL

启动用户：USE 用户
创建用户：CREATE USER 用户名 IDENTIFIED BY 密码
重命名用户：RENAME USER 原用户名 TO 新用户名
删除用户：DROP USER 用户名
修改用户密码：SET PASSWORD FOR 用户名 = Password('新密码')
查看用户权限：SHOW GRANTS FOR 用户名
授予用户权限 GRANT 权限 ON 数据库.数据表.字段 FROM 用户名
撤销用户权限：REVOKE 权限 ON 数据库.数据表.字段 FROM 用户名