MySQL
文章目录
1、DDL
DDL(数据定义语言),定义和管理数据对象,如数据库、数据表等。
-- 命令
CREATE DROP ALTER
数据库
-
创建数据库 : create database [if not exists] 数据库名;
-
删除数据库 : drop database [if exists] 数据库名;
-
查看数据库 : show databases;
-
使用数据库 : use 数据库名;
数据表
- 创建表
create table [if not exists] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
--...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];
-
修改表
修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
-
删除表
语法:DROP TABLE [IF EXISTS] 表名
- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
2、DML
数据操纵语言(Data Manipulation Language, DML)是用于数据库操作。
包括:
- INSERT (添加数据语句)
- UPDATE (更新数据语句)
- DELETE (删除数据语句)
2.1、INSERT命令
语法:
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
可同时插入多条数据,values 后用英文逗号隔开
VALUES('1'),('2')
例
-- 插入当行数据
INSERT INTO `student` (`name`,`pwd`,`sex`)
VALUES('张三','123','男')
-- 插入多行数据
INSERT INTO `student` (`name`,`pwd`,`sex`)
VALUES('张三','123','男'),('李四','456','女'),('王五','789','男')
2.2、UPDATE命令
语法:
UPDATE 表名 SET column_name = value [,column_name2=value2,...] [WHERE condition];
例
UPDATE `student` SET `name` = '陈九' WHERE id = 2;
-- between
UPDATE `student` SET `name` = 'cht' WHERE id BETWEEN 1 AND 3; -- 介于1和3之间
2.3、DELETE命令
语法:
DELETE FROM 表名 [WHERE condition];
例
-- 删除最后一个数据
DELETE FROM `student` WHERE id = 1;
2.4、TRUNCATE命令
作用:用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;
语法:
TRUNCATE [TABLE] table_name;
例
TRUNCATE `student`;
注意:区别于DELETE命令
- 相同:都能删除数据,不删除表结构,但TRUNCATE速度更快。
- 不同:
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器。
- 使用TRUNCATE TABLE不会对事务有影响。
3、DQL
DQL( Data Query Language 数据查询语言 )
查询数据库数据 , 如SELECT语句。简单的单表查询或多表的复杂查询和嵌套查询。
select语法:
-- [ ] 括号代表可选的 , { }括号代表必选得
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
AS 子句
作用:
- 可给数据列取一个新别名
- 可给表取一个新别名
- 可把经计算或总结的结果用另一个新名称来代替
语法:
-- 这里是为列取别名(当然as关键词可以省略)
SELECT 字段名 AS 别名 FROM 表名;
-- 使用as也可以为表取别名
SELECT 字段名 AS 别名 FROM 表名 as 表别名;
-- CONCAT()函数拼接字符串,在字段前面追加一个姓名:
SELECT CONCAT('姓名:', 字段名) AS 新姓名 FROM student;
DISTINCT关键字
作用:去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ),只返回一条
语法:
-- 去掉重复的项
SELECT DISTINCT 字段名 FROM 表名
3.1、WHERE语句
用于检索数据表中 符合条件 的记录
搜索条件可由一个或多个逻辑表达式组成,结果一般为真或假
模糊查询
操作符名称 | 描述 |
---|---|
LIKE | 若a匹配b,则结果为真 |
语法:结合%和_
-- 查询姓张的同学
-- like 结合 %(代表0到任意个字符) _(一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张%'
-- 查询姓张的同学,名字后面只有一个字
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张_'
-- 查询姓张的同学,名字后面只有两个字 两个_
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张__'
-- 查询名字中有伟的同学,%伟%
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%伟%'
操作符名称 | 描述 |
---|---|
IS NULL | 若操作符为NULL,则结果为真 |
IS NOT NULL | 若操作符不为NULL,则结果为真 |
IN | 若a为集合中的某一个,则结果为真 |
语法:
-- IN语法
SELECT 字段名1, 字段名2 FROM 表名
WHERE 字段名1 IN (1,2,3);
SELECT 字段名1, 字段名2 FROM 表名
WHERE 字段名1 IN ('北京','上海','武汉');
3.2、连接查询
在用Join进行多表联合查询时,我们通常使用on来建立两个表的关系。
3.2.1、内连接
关键字:inner join on
语句:
select * from a_table a inner join b_table b on a.a_id = b.b_id;
说明:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集部分。
例
/* student 为学生表,result 为成绩表
查询参加了考试的同学信息(学号,姓名,科目编号,成绩)
studentno为student和result表共有字段
subjectno,studentResult 为result表的字段
studentname 为student表的字段
*/
-- 采用内连接,查找两个表的共有字段,也就是学号相同的字段,然后进行拼接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
3.2.2、左连接
关键字:left join on / left outer join on
语句:
SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;
说明:
- left join 是left outer join的简写,它的全称是左外连接,是外连接中的一种。
- 左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录,右表记录不足的地方均为NULL。
例
/* student 为学生表,result 为成绩表
查询了所有同学,不考试的也会查出来(学号,姓名,科目编号,成绩)
studentno为student和result表共有字段
subjectno,studentResult 为result表的字段
studentname 为student表的字段
*/
-- 采用左连接查询会检索左表的所有信息,右表中如没有该信息则显示为null。
-- 以下检索结果会显示所有学生的相关信息,即使该学生没有参加考试。
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
3.2.3、右连接
关键字:right join on / right outer join on
语句:
SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;
说明:
- right join是right outer join的简写,它的全称是右外连接,是外连接中的一种。
- 与左(外)连接相反,右(外)连接,左表(a_table)只会显示符合搜索条件的记录,而右表(b_table)的记录将会全部表示出来,左表记录不足的地方均为NULL。
例
/* student 为学生表,result 为成绩表
查询了所有同学,不考试的也会查出来(学号,姓名,科目编号,成绩)
studentno为student和result表共有字段
subjectno,studentResult 为result表的字段
studentname 为student表的字段
*/
-- 采用左连接查询会检索右表的所有信息,左表中如没有该信息则显示为null。
-- 一下检索结果会显示所有参加了考试的学生的相关信息,没有参加考试的不会显示
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
3.2.4、自连接
说明:
- 自连接查询其实等同于连接查询,需要两张表,只不过它的左表(父表)和右表(子表)都是自己。
- 做自连接查询的时候,是自己和自己连接,分别给父表和子表取两个不同的别名,然后附上连接条件。
例
-- 数据表
CREATE TABLE category (
categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
pid INT(10) NOT NULL COMMENT '父ID',
categoryName VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY(categoryid)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;
--插入数据
INSERT INTO category(categoryid,pid,categoryName)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','PS技术'),
('8','2','办公信息');
-- 查询父子信息:把一张表看为两个一模一样的表
SELECT a.`categoryName` AS '父栏目', b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`
- 结果如下
父栏目 | 子栏目 |
---|---|
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | PS技术 |
信息技术 | 办公信息 |
分析
- 数据表
categoryid | pid | categoryName |
---|---|---|
2 | 1 | 信息技术 |
3 | 1 | 软件开发 |
4 | 3 | 数据库 |
5 | 1 | 美术设计 |
6 | 3 | web开发 |
7 | 5 | PS技术 |
8 | 2 | 办公信息 |
-
将两张表进行拆分,pid为1的代表为父表(pid为1表示其父节点为根节点,可以将表看成为一颗树)
父表:为树的第二层,categoryid表示为当前字段的节点编号。
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子表:为树的第三层,pid表示为父节点的编号(也就是父表中的categoryid),根据父表的categoryid和子表的pid就可以将表连接。
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | 美术设计 |
3.3、排序和分页
分页处于SQL语句的最后一行。
在有分页的时候,排序处于SQL语句的倒数第二行;在没有分页的时候,排序处于最后一行。
-
排序
-
语法:ORDER BY 字段名 ASC/DESC(排序方式)
-
排序方式:升序 ASC, 降序DESC
-
语句默认按照ASC升序对记录进行排序。
-
SELECT * FROM 表名
WHERE ...
ORDER BY 字段名 DESC --降序,默认为升序
- 分页
- 语法:LIMIT 数字a 数字b
- a代表起始位置,b代表每页几条数据
- 第N页 : limit (pageNo-1)*pageSzie, pageSzie
- pageNo:页码,pageSize:单页面显示条数。
SELECT * FROM 表名
WHERE ...
LIMIT 数字, 数字
4、MySQL函数
4.1、常用函数
-
数学运算
-- 数学运算 SELECT ABS(-6) -- 绝对值 SELECT CEILING(5.3) -- 向上取整 SELECT FLOOR(5.3) -- 向下取整 SELECT RAND() -- 返回一个0~1之间的随机数 SELECT SIGN(10) -- 判断一个数的符号,负数返回 -1,整数返回 1
-
字符串函数
-- 字符串函数 SELECT CHAR_LENGTH('MySQL函数') -- 返回字符串的长度 SELECT CONCAT('M','y','S','Q','L') -- 拼接字符串 SELECT INSERT('MySQL_', 1, 2, '你好') -- 替换,从某个位置开始替换某个长度 SELECT LOWER('SELECT') -- 转换为小写 SELECT UPPER('select') -- 转换为大写 SELECT INSTR('MySQL','S') -- 返回第一次出现的子串索引 SELECT SUBSTR('MySQL', 1, 3) -- 返回指定的字符串
-
时间与日期
-- 时间与日期 SELECT CURRENT_DATE() -- 获取当前日期 SELECT NOW() -- 获取当前的时间 SELECT LOCALTIME() -- 获取本地时间 SELECT SYSDATE() -- 获取系统时间
-
系统
-- 系统 SELECT SYSTEM_USER() -- 获取系统用户 SELECT VERSION() -- 获取数据库版本
4.2、聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 返回满足Select条件的记录总和数 |
SUM() | 返回数字字段或表达式列作统计,返回一列的总和。 |
AVG() | 通常为数值字段或表达列作统计,返回一列的平均值 |
MAX() | 可以为数值字段,字符字段或表达式列作统计,返回最大的值。 |
MIN() | 可以为数值字段,字符字段或表达式列作统计,返回最小的值。 |
COUNT()函数
- count(字段),count(1)与count(*)
- 从含义上讲,count(1) 与 count(*) 都表示对全部数据行的查询。
- count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
- count(*) 包括了所有的列,相当于行数,在统计结果的时候,包含字段为null 的记录;
- count(1) 用1代表代码行,在统计结果的时候,包含字段为null 的记录 。
5、事务
什么是事务
- 事务指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功。
- 事务就是将一组SQL语句放在同一批次内去执行。
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行。
可以保证数据的一致性和完整性(避免异常和错误等导致的数据信息异常) 。
ACID原则
原子性(Atomic)
- 整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性(Consist)
- 事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。
- 以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。
隔离性(Isolated)
- 如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
持久性(Durable)
- 在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
隔离所导致的一些问题
脏读:
- 指一个事务读取了另外一个事务未提交的数据。
不可重复读:
- 在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读):
- 是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
基本语法
-- MySQL是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认)
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
/*
执行语句
*/
-- 提交:持久化(成功)
COMMIT
-- 回滚:回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
测试
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务,就被持久化
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
6、索引
什么是索引
-
索引是存储引擎用于快速找到记录的一种数据结构。
-
对数据库表中一列或多列的值进行排序的一种结构。
-
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
-
数据库如同书籍,索引如同书籍目录,通过目录能快速定位到指定内容,能大大提高效率,索引也是如此。
索引的作用
- 提高查询速度。
- 确保数据的唯一性。
- 可以加速表和表之间的连接,实现表与表之间的参照完整性。
- 使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间。
- 全文检索字段进行搜索优化。
索引分类
- 主键索引 (Primary Key)
- 一张表只能有一个主键索引,不允许重复、不允许为 NULL。
- 最常见的索引类型。
- 确保数据记录的唯一性。
- 确定特定数据记录在数据库中的位置。
- 一张表只能有一个主键索引,不允许重复、不允许为 NULL。
- 唯一索引 (Unique)
- 数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,索引列的值必须唯一,但允许有空值。
- 与主键索引的区别
- 主键索引只能有一个。
- 唯一索引可能有多个。
- 常规(普通)索引 (Index)
- 一张表可以创建多个普通索引,一个普通索引可以包含多个字段**,允许数据重复,允许 NULL 值插入**。
- 注意 :
- index 和 key 关键字都可以设置常规索引。
- 应加在查询找条件的字段。
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作。
- 全文索引 (FullText)
- 它查找的是文本中的关键词,主要用于全文检索。
- 注意 :
- 只能用于CHAR,VARCHAR,TEXT数据列类型。
- 适合大型数据集。
索引准则
- 索引不是越多越好。
- 不要对经常变动的数据加索引。
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段。
7、用户管理
基本命令
-- 创建用户
CREATE USER 用户名 IDENTIFIED BY 密码
-- 修改密码 (修改当前用户密码)
SET PASSWORD = PASSWORD('新密码')
-- 修改密码 (修改指定用户密码)
SET PASSWORD FOR 用户名 = PASSWORD('新密码')
-- 重命名
RENAME USER 用户名 TO 新用户名
-- 用户授权
-- ALL PRIVILEGES :全部权限,*.* :全部的库和全部的表
GRANT ALL PRIVILEGES ON 库.表 TO 用户名
-- 查看权限
SHOW GRANT FOR 用户名
-- 撤销权限
REVOKE ALL PRIVILEGES ON 库.表 TO 用户名
-- 删除用户
DROP 用户名
权限
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器(从主服务器中读取二进制日志事件)
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句,mysqladmin debug命令;允许您连接(一次),即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
8、MySQL备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件和相关配置文件
mysqldump客户端
作用:
-
转储数据库
-
搜集数据库进行备份
-
将数据转移到另一个SQL服务器,不一定是MySQL服务器
-- 导出 1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) 3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql) 4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql) 可以-w携带备份条件 -- 导入 1. 在登录mysql的情况下:-- source D:/a.sql source 备份文件 2. 在不登录的情况下 mysql -u用户名 -p密码 库名 < 备份文件
9、三大范式
第一范式 (1st NF)
- 第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式。
第二范式 (2nd NF)
-
满足第二范式(2NF)必须先满足第一范式(1NF)。
-
表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系。
-
一个表只描述一件事情。
第三范式 (3rd NF)
-
满足第三范式 (3rd NF) 必须先满足第二范式(2NF)。
-
非主键列必须直接依赖于主键,不能存在传递依赖。
-
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范化和性能的关系
-
为满足某种商业目标,数据库性能比规范化数据库更重要。
-
在数据规范化的同时,要综合考虑数据库的性能。
-
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间。
-
通过在给定的表中插入计算列,以方便查询。