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:n)和多对一联系(n:1):商品、订单信息表:一人有多条订单(1:n);多条订单对应一个商品(n:1)。
- 多对多联系(n:n):一般通过建立中间表,让他变成多对一或者一对多的关系。
使用联系的优缺点:
- 优点:更有效储存、更方便处理、具有更大的可伸缩性。
- 缺点:数据分布在多个表中,需要使用联结实现。
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 触发器分类
- DDL触发器:对表进行操作时的触发器。
- DML触发器:对表数据进行操作时的触发器。
- 登陆触发器:对LOGIN事件的触发器。
3.2 触发器语法
创建触发器:
CREATE TRIGGER 触发器名称 # 需要在每个表中唯一,可以数据库中不唯一
触发时机 # BEFORE/AFTER
触发事件 ON 表名 # INSERT/UPDATE/DELETE
FOR EACH ROW # 对每一行操作
删除触发器:DROP TRIGGER 触发器名称
3.3 MySQL中的触发器
- 仅支持DML触发器
- 只有表支持触发器,视图和临时表不行
- 每个表每个时间只允许有一个触发器,最多有2*3个触发器。(前 / 后) * (增 / 删 / 改)
4. 游标
游标提供了一种对从表中检索出的数据进行操作的灵活手段,实际上一种从多条数据记录的结果集中每次提取一条的机制。当决定对结果集进行处理时,必须声明一个指向该结果集的游标。
MySQL中的游标只能用用于存储过程或存储函数。
4.1 使用游标
- 声明游标,定义需要使用游标的SELECT语句(此时未进行检索)。
- 打开游标。
- 根据需要取出各行。
- 结束操作后,关闭游标。
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 用户名