MySQL 学习笔记

本文主要是介绍MySQL 学习笔记，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

简介

• 如果起名与数据库关键字重复，就使用 `` 包起来
• IFNULL(NULL, 1);
• 截取字符串 substring(studentName, 1,1) from student;
• [ IF EXISTS ] 此关键字用于判断表或数据库等是否存在
• FIELD1 CHAR(4), // 最多四个字符，固定长度，不够会自动设置空格四位自动补齐
  FIELD2 VARCHAR(10), // 最多10个字符，不够10位实际长度去调整
• auto_increment 设置字段值为自增长类型
• distinct 查询时去重
• COLLATE utf8_general_ci mysql 的排序规则，ci Case Insensitive的缩写，即是大小写无关，不区分大小写

常见的关系型与非关系型数据库及其区别

关系型数据库

MySQL、SQLServer、SQLite、ORACLE、MariaDB...

  关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织

优点：

1. 易于维护：都是表结构，格式统一
2. 使用方便：SQL语句通用，可用于复杂查询
3. 复杂操作：支持SQL，可用于一个表以及多个表之间非常复制的查询

缺点：

1. 读写性能比较差，尤其是数据量大的高效率读写
2. 固定的表结构，灵活度稍欠
3. 高并发读写需求，传统关系数据库来说，硬盘 I/O是一个很大的麻烦从

非关系型数据库

redis、mongoDB、Neo4j...

  非关系型数据库严格来说不是一种数据库，应该是一种结构化存储方法集合，可以是文档或者键值对等

优点：

1. 格式灵活：存储数据的格式可以是 key,value 形式、文档形式等，使用场景广泛，而关系数据库则只支持基础类型
2. 速度快：nosql 可以使用硬盘或者随机存储器作为载体，而关系数据库只能使用硬盘
3. 高扩展性
4. 成本低： nosql 数据库部署简单，基本都是开源软件

缺点;

1. 不提供 sql 支持，学习和使用成本较高
2. 无事务处理
3. 数据结构相对复杂，复制查询方面欠缺

相关概念

sql语句分类

数据库引擎(表类型)

命令行操作数据库

> net start user_name; #启动数据库
> net stop user_name; #关闭数据库
> mysql -u[user_name] -p[password] #命令行运行数据库

CREATE DATABASE if not exists database_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci # 创建数据库并规定字符集
SELECT user FROM mysql.user; # 查看数据库所有用户
show databases; #查看所有的数据库
use database_name #进入数据库
show tables # 查看当前数据库里的所有表
desc table_name; #查看表结构,等价于describe table_name;
show full columns from table_name; #查看当前表的所有列信息
show create table table_name; #显示表创建语句

where条件限制

• 若不设置条件限制则会影响额外数据

• 数值类型的值才能做运算

• 相同数据类型的值才能作比较

• 运算符	含义	范例	结果
  =	等于	5=6	false
  <> 或 !=	不等于	5!=6	true
  >	大于	5>6	false
  <	小于	5<6	true
  >=	大于等于	5>=6	false
  <=	小于等于	5<=6	true
  BETWEEN	在某个范围之间	BETWEEN 5 AND 10	-
  AND	并且	5>1 AND 1>2	false
  OR	或	5>1 OR 1>2	true

数据库操作

• 数据库基本操作主要就是 增删改查 四个操作，其中查询较困难

创建（create）

• 属于 DDL 的一种
• TIMESTAMP DEFAULT CURRENT_TIMESTAMP 数据类型为 timestamp 默认值为 current_timestamp 是字段默认获取当前系统时间插入

数据表创建

 CREATE   TABLE [ IF NOT EXISTS ]    `表名`   (  # ``反引号用于引入关键字
        `字段名1`    列类型 [ 属性 ]  [ 索引 ] [注释] ,
        `字段名2`   列类型 [ 属性 ]  [ 索引 ] [注释] , 
         … …    
         `字段名n`   列类型 [ 属性 ]  [ 索引 ] [注释] 
)  [  表类型 ] [ 表字符集 ] [注释] ;

列类型

• 规定数据库中该列存放的数据类型

• 其中分为以下几种类型：

• 数值类型

  • 类型	说明	取值范围	存储需求
    tinyint	非常小的数据	有符值： -27 ~ 27-1 无符号值：0 ~ 28-1	1字节
    smallint	较小的数据	有符值： -215 ~ 215-1 无符号值： 0 ~ 216-1	2字节
    mediumint	中等大小的数据	有符值： -223 ~ 223-1 无符号值： 0 ~ 224-1	3字节
    int	标准整数	有符值： -231 ~ 231-1 无符号值：0 ~ 232-1	4字节
    bigint	较大的整数	有符值： -263 ~263-1 无符号值：0 ~264-1	8字节
    float	单精度浮点数	±1.1754351e -38	4字节
    double	双精度浮点数	±2.2250738585072014e -308	8字节
    decimal	字符串形式的浮点数	decimal(m, d)	m个字节

• 字符串类型

  • 类型	说明	最大长度
    char[(M)]	固定长字符串，检索快但费空间， 0 <= M <= 255	M字符
    varchar[(M)]	可变字符串 0 <= M <= 65535	变长度
    tinytext	微型文本串	28–1字节
    text	文本串	216–1字节

• 日期和时间型数值类型

  • 类型	说明	取值范围
    DATE	YYYY-MM-DD，日期格式	1000-01-01~ 9999-12-31
    TIME	Hh:mm:ss ，时间格式	-838:59:59~838:59:59
    DATETIME	YY-MM-DD hh:mm:ss	1000-01-01 00:00:00 至 9999-12-31 23:59:59
    TIMESTAMP	YYYYMMDDhhmmss 格式表示的时间戳	197010101000000 ~2037年的某个时刻
    YEAR	YYYY 格式的年份值	1901~2155

• NULL类型

  • 理解为 “没有值” 或 “未知值”
  • 不能用 NULL 进行算术运算，结果是NULL

• 选择数据类型

  • 整数和浮点数
  • 日期类型
  • char 和 varchar

• 复合类型

  • ENUM 类型
    • ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
    • ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。
    • MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。
  • SET 类型
    • SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。
    • 一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。
    • 希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

数据字段类型

• unsigned

  • 无符号的
  • 申明该列数据不允许负数

• zerofill

  • 0填充的
  • 不足位数的用0来填充，如 int(3) 值为5时，插入表内为 005

• auto_increment

  • 自动增长的，每添加一条数据，自动在上一个记录数上加 1
  • 通常用于设置主键，且为整数类型
  • 可以定义起始值得=和步长

• NULL 和 NOT NULL

  • 数据值默认为 NULL ，即没有插入该列的值
  • 如果设置为 NOT NULL ， 则该列必须有值

• default

  • 默认的，用于设置默认值
  • 例如：性别默认为男，若插入值不特别指定则性别都为男

• comment

  • 注释，此关键字加上注释内容，内容用引号引起来
  • 例： create table student (id int(6) comment "学号" ) comment "学生表";

表类型

• 表类型设置CREATE TABLE 表名(省略一些代码) ENGINE = InnoDB;

• 常见表类型：MyISAM、InnoDB 、HEAP、BOB、CSV等

  • 使用MyISAM: 节约空间及相应速度

  • 使用InnoDB: 安全性，事务处理及多用户操作数据表

  • InnoDB类型数据表只有一个*.frm文件，数据文件为上一级目录的 ibdata1 文件

  • MyISAM类型数据表对应三个文件：

    *.frm -- 表结构定义文件

    *.MYD -- 数据文件

    *.MYI -- 索引文件

表字符集

• 设置方法与表类型设置一致，例：CHARSET= utf8;

复制表

CREATE TABLE student_demo 
AS SELECT * FROM student

复制 student 表数据到 student_demo 表，只会复制数据，不会带主键以及自动增长等属性

alert

# 修改表名
ALTER TABLE demo2 RENAME AS demo3;
# 修改字段
ALTER TABLE demo3 CHANGE NAME username VARCHAR(32) NOT NULL; 
# 添加字段
ALTER TABLE demo3 ADD PASSWORD VARCHAR(32) NOT NULL;
# 删除字段
ALTER TABLE demo3 DROP PASSWORD;

增（insert）

• INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( '值1', '值2', '值3', …)
  • 插入单行使用 values 效率高，插入多行时使用 value 效率更好
• 字段部分可省略，但是顺序与类型必须一致
• 插入多行数据只需要在values后面加括号传值
• 其他方式：INSERT INTO 表名 SET 字段名1=值1[,字段名2=值2，…]
• 插入列是自动增长或又默认值可以使用 'default' 占位，系统会自动生成对应的值

插入分层行

​ 插入母表数据同时插入子表

通过 LAST_INSERT_ID() 函数来获取前面一次插入数据的 id 来作为子表的 id 插入

-- 案例
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
INSERT INTO order_items
VALUES(LAST_INSERT_ID(), 1, 1, 2.95);

使用子查询插入

​ 从其他表查询数据插入到目标表

INSERT INTO order_demo
SELECT * FROM orders
WHERE order_date < '2021-01-01'

删（delete）

• DELETE FROM 表名 [ WHERE condition ];

• delete 只是在数据文件中吧删除字段进行标记

• truncate 在物理文件上操作，彻底删除

  • TRUNCATE [TABLE] table_name
  • 完全清空表数据，表结构、索引、约束不变。且速度比 delete 快

# 删除数据表；if exists为可选，会判断是否存在该表，如不存在则抛出错误 
DROP  TABLE  [ IF  EXISTS ]   表名

改（update）

• UPDATE 表名 SET column_name = value [ , column_name2 = value2, …. ] [ WHERE condition ];
• ncolumn_name 为要更改的数据列
• values 内的数据是更新后的值
• 若不加 where 限制会改变整个表的数据

批量修改表内数据

使用 case 语法，对符合要求的 id 数量进行运算

UPDATE batch_update SET amount = 
	CASE id
		WHEN 1 THEN amount + 10
        WHEN 3 THEN amount + 20
	END
WHERE id in (1, 3);

查（select）

查询合并行并求对应值

SELECT moneyType AS '操作裂类型', SUM(transactionBalance) AS '总金额' FROM transtable GROUP BY moneyType;

跨数据库查询

SELECT id, name FROM school.student 只需要在表名前加上 '数据库名.' 即可切换到对应数据库

select 语法

• {} 里面的内容是必须有的；[] 部分内容则是可选的

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
    [ left|out|inner  join  table_name2 ]    #联合查询
	[ WHERE  … ]   	#指定结果需满足的条件
	[ GROUP BY …]	#指定结果按照哪几个字段来分组
	[ HAVING …]	#过滤分组的记录必须满足的次要条件
	[ ORDER BY… ]	#指定查询记录按一个或者多个条件排序
	[ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  #指定查询的记录从哪条至哪条

• 查询数据表中的所有内容：select * from table_name; 此方法效率低，不推荐
• 指定列查询： select studentName, studentNo from student; 只查询表中对应列的数据
• 查询多个表中有相同字段时：select student.studentNo, from student; 使用 表名.列名 来指定

AS 子句

• 可以给数据列取一个别名
  • select studentNo as '学号' from student;
• 也可以给表起一个别名来进行调用
  • select s.studentNo from student as s;
• 可以把计算或者总结的结果用另外一个名称来代替
  • select num + 1 AS nums from student;
• AS 可以省略即：select name '名字' from student;

DISTINCT 去重关键字

• 去掉 select 查询返回的记录结果内重复的记录（重复的数据只返回一条）
• select distinct 字段名1, 字段名2... FROM 表名
• ALL 是默认的关键字，返回所有的记录
• 可以写在函数的括号内去掉重复项

ALL() 关键字

• all 后面可以接上多个数值，mysql 会使用前面的值对其进行逐个比较

 -- 取出所以满足条件的值逐个进行比较
SELECT invoice_total FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
	FROM invoices
	WHERE client_id = 3
)

--  等价于
--  取出最大值，直接比较
SELECT invoice_total FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id = 3
)

ANY 关键字

• ANY 需要搭配等号使用 = ANY
• 等价于 IN 关键字

EXISTS 关键字

• 判断子查询条件是否满足查询要求

• 可以使用 NOT EXTST 来判断不存在

-- 查询发票表里面所有的用户
-- exists 接收到的是布尔值
SELECT * 
FROM clients c
WHERE EXISTS(
	SELECT client_id
    FROM invoices
    WHERE c.client_id = client_id
)

-- 等价于

-- IN 接受到的是一个结果集
SELECT * 
FROM clients
WHERE client_id IN(
	SELECT DISTINCT client_id
    FROM invoices
)

-- 等价于

SELECT 
	DISTINCT c.*
FROM invoices
LEFT JOIN clients c
	USING(client_id)

IF 关键字

• 判断查询项是否满足对应条件，传对应的值

SELECT
	id,
	name,
    if(is_spices = 0, '否', '是') is_spices
FROM tb_material_type

between and 范围查询

• 根据一个范围值进行查询
• SELECT 字段列1，字段2 ,…FROM 表名 WHERE 字段x BETWEEN 值1 AND 值2
• 相当于 >= 和 <= 一起使用
• 使用 between and 他的两个边界值也是被包含的

like 模糊查询

• 与 "%" 一起使用，表示匹配0或任意多个字符
• 与 "_" 一起使用，表示匹配单个字符；可以写多个 ‘-’ ，每个‘-’表示一个占位符

regexp 正则表达式查询（可以使用别名 RLIKE）

• WHERE name REGEXP 'a' 表示名字中包含 ‘a' 的人；等同于 ’%a%‘
• WHERE name REGEXP '^a' 使用 ^ 符号开头，表示名字必须以 ’a‘ 开头，等价于 'a%'
• WHERE name REGEXP 'a$' 等价于 '%a'
• WHERE name REGEXP 'a|b' '|' 符号有 or 的作用，表示名字中含有 'a' 或者 'b' 的用户
  • 注意：| 符号两侧不能加空格
  • 可以使用多个 | 进行多次判断，每个判断项可以加上 '^' or '$'
• WHERE name REGEXP '[abc]o' 表示名字中含有 'ao' or 'bo' or 'co' 的用户；[]也可以写后面，查询结果顺序同理
  • [a-g] 表示 a~f 的字母都能匹配，不包括 g；- 代表一个范围
  • [][]a 前面使用多个小括号，可以匹配多位

使用 in 进行范围查询

• SELECT 字段列1，字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…)
• 查询的字段x的值，至少与括号中的一个值相等
• 多个值之间用英文逗号隔开

NULL

• null 代表 ”无值“，区别于 0 或者 ”“
• null只能出现在允许为null的字段，必须使用 is null 或者 is not null 去判断

SELECT CONCAT("姓名：", StudentName) AS 新姓名 FROM student;
# 去重复 distinct
SELECT DISTINCT StudentNo FROM result;

SELECT * FROM subject WHERE subjectname LIKE "%数学%";
# 下划线占位，后面必须有两个的才能被查出来
SELECT studentno, studentname FROM student WHERE studentname LIKE "李__";

SELECT * FROM subject WHERE classhour = 100 OR classhour = 110 OR classhour = 120;
# 使用in查询方式，更为简洁效率也更高
SELECT * FROM subject WHERE classhour IN (100, 110, 120);

SELECT  *  FROM  subject  where    ClassHour = 100  OR ClassHour =110 OR ClassHour  = 120;  #普通处理方式
SELECT  *  FROM  subject  where ClassHour  IN ( 100, 110,120 );
#使用IN进行查询方式，更为简洁，效率更高

union查询

​ 合并多个查询结果

​ union 前面的查询语句决定列名的呈现

SELECT 
	order_id,
    order_date,
    'active' status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
    order_date,
    'archived' status
FROM orders
WHERE order_date < '2019-01-01'

连接查询

• 需要多张数据表的数据进行查询，可以通过连接运算符进行查询
• [inner/outter] JOIN ON 如果连接两个表的列名一致可以使用 using (column_name) 来替代 on cloumn_name = column_name
  • 当有多个条件相同时，使用 using (cloumn_name, clounmn_name) 来简化代码
• 分类为
  • 内连接（inner join）
    • 等值和非等值的连接查询
    • 自身连接查询
    • 可以有多个 join on 连接多个表进行查询
  • 外连接（out join）
    • 左外连接 (left join)
    • 右外连接 (right join)

内连接查询

• inner join 内连接；在表中至少有一个匹配时，则返回数据；on 后面跟条件

SELECT   字段1,字段2,…  FROM  table_1 
INNER  JOIN   table_2    ON  table_1.字段x   =  table_2.字段y;
# INNER JOIN  与 JOIN 是相同的；
#  如table_1中的行在table_2中没有匹配，则不返回；

#要求：从subject和grade数据表查询课程名称和所属年级名称
SELECT SubjectName,GradeName FROM   subject   INNER   JOIN  grade   ON   subject.GradeID= grade.GradeID;  

• 等值和非等值的连接查询

#要求：从subject和grade数据表查询课程名称和所属年级名称

#非等值连接查询 (返回记录为两表记录数的乘积)
SELECT  SubjectName, GradeName  FROM  subject,  grade;  

#等值查询 (等效于内连接)
SELECT  SubjectName, GradeName  FROM  subject,  grade 
WHERE  subject.GradeID = grade.GradeID;  

自连接

• 需要使用不同的别名来输出对应的列数据
• 选取对应列时需要加上对应的别名
• join 后面可以跟多个条件，条件之间使用 and 连接

SELECT * 
FROM sql_hr.employees e
JOIN sql_hr.employees m
	ON e.reports_to = m.employee_id

外连接（left/right outer join outer可以省略）

左外连接（left join）

• 从左表（table_1）中返回所有的记录，即便在右（table_2）中没有匹配的行
• SELECT 字段1,字段2,… FROM table_1 LEFT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

右外连接（right join）

• 从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行
• SELECT 字段1,字段2,… FROM table_1 RIGHT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

自然连接（natural join）；

• 不带连接条件，数据库引擎会自动连接；不建议使用

交叉连接（cross join）

• 不带连接条件，返回笛卡尔积
• 在查询时查询多个表不加限制条件同样能实现交叉查询，带join属于显式查询，from 多个表属于隐式

各个连接的对比

自连接查询

• 数据表与自身进行连接

SELECT  c1.categoryName AS "父栏目名称",c2.categoryName AS  "子栏目名称" 
FROM category AS c1,category AS c2 
WHERE c1.categoryId = c2.pid; 
SELECT  c1.categoryName AS "父栏目名称",c2.categoryName AS  "子栏目名称" 
FROM category AS c1 LEFT JOIN category AS c2 
ON c1.categoryId = c2.pid WHERE c1.pid = 1; 

子查询

• 嵌套查询即是由多个子查询组成的，求解方式是由内向外执行

• 相关子查询（普通子查询子查询代码只会执行一次）

  • 主查询和子查询使用了同一张表，给主查询赋别名与子查询对应列进行关联

  • 相关子查询内存占用高，但是实际使用情况较多

  • 不能使用 ANY 关键字来替代相关子查询的部分操作

  • SELECT * 
    FROM employees e
    WHERE salary >  (
    	SELECT AVG(salary)
    	FROM employees
    	WHERE office_id = e.office_id
    )
    
    -- 不等价， > any 会逐个比较，不是比较互相对应的
    
    SELECT * 
    FROM employees e
    WHERE salary > ANY  (
    	SELECT AVG(salary)
    	FROM employees
    	GROUP BY office_id
    )
    

• 在选择语句中使用子查询（子查询可以直接调用新定义的列）

  • SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) 
    	FROM invoices)  AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference  -- 不能直接减别名，使用 SELECT 查询
    FROM invoices;
    

  • -- demo
    
    SELECT 
    	c.client_id,
    	c.name,
    	SUM(i.invoice_total) AS total_sales,
    	(SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    	SUM(i.invoice_total) - (SELECT invoice_average) AS difference
    FROM clients c
    LEFT JOIN invoices i
    	USING(client_id)
    GROUP BY c.client_id
    
    -- 等价于
    
    SELECT 
    	c.client_id,
    	c.name,
    	(SELECT SUM(invoice_total) 
    	 FROM invoices i
    	 WHERE client_id = c.client_id) AS total_sales,
    	 (SELECT AVG(invoice_total)
    	  FROM invoices i) AS average,
    	 (SELECT total_sales - average) AS difference
    FROM clients c
    

• 在 from 中也可以使用子查询；需要给 from 语句设置一个别名，否则会报错

  • 在 from子句使用子查询仅限于简单查询

    SELECT *
    FROM(
    	SELECT 
    		c.client_id,
    		c.name,
    		(SELECT SUM(invoice_total) 
    		 FROM invoices i
    		 WHERE client_id = c.client_id) AS total_sales,
    		 (SELECT AVG(invoice_total)
    		  FROM invoices i) AS average,
    		 (SELECT total_sales - average) AS difference
    	FROM clients c
    ) AS sales_summary
    WHERE total_sales IS NOT NULL
    

ORDER BY排序

• order by 是放在语句的最后

• 与 select 语句查询得到结果，按照指定字段进行排序

• 与 DESC(降序) 或 ASC 搭配使用，默认为 ASC

• order by colum_name1, column_namw2 可以接多个列名，逐个排序

• select id, name from student order by 1, 2 这里的1，2指的是 id, name

Limit 限制

• limit [m, ] n 或 limit n offset m
  • 限制select 返回结果的行数
  • m 限制第一个返回记录行的偏移量
  • n 限制返回记录行的最大数目
  • m 不指定默认为0
• limit 属于分页显示
• limit 子句永远放在最后
• select * from student limit 5; 返回前5条数据
• select * from student limit 5, 10; 返回 6-15条数据

group by 分组

• 使用GROUP BY关键字对查询结果分组
  • 在from 和 where 语句的后面，在order by 的前面
  • 对所有的数据进行分组统计
  • 分组的依据可以有多个，并依次分组 GROUP BY date, payment_method 直接在后面写多个，用逗号隔开
  • 与HAVING结合使用，进行分组后的数据筛选，where语句的顺序在group之前，所以只能用HAVING进行筛选；HAVING 后面可以接多个条件，用AND连接；但是条件必须是 select 语句里面有的字段

# 分组查询，having作为条件限制，group进行分组
SELECT 
	s.subjectName AS "课程名",
	MAX(studentResult) AS "最高分",
	MIN(studentResult) AS "最低分",
	AVG(studentResult) AS "平均分",
	SUM(studentResult) AS "分数和",
	COUNT(1) AS "人数"
FROM result AS r
LEFT JOIN
	subject AS s
	ON s.subjectNo = r.subjectNo
GROUP BY r.subjectNo
HAVING AVG(studentResult) >= 60;

MYSQL函数

• 数学函数、字符串函数、日期时间函数、系统统计函数

MYSQL统计函数

• WITH ROLLUP 运算符，自动在列最后生成一个存储聚合函数的和值，只能适用于聚合函数；且跟在 group by 条件后可以实现分类汇总，

    SELECT 
    	IFNULL(city, '合计') city,
    	IFNULL(state, '小计') state,
    	SUM(invoice_total)
    FROM invoices
    JOIN clients USING(client_id)
    GROUP BY city, state WITH ROLLUP
  

数值函数

字符串函数

MYSQL事务处理

• 事务就是一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错，则该批次的所有语句都将被取消执行
• mysql 事务处理只支持 innoDB 和 BDB 数据表类型

事务 ACID 属性

• 事务必须具备以下四个属性
• 一个事务一般是指多个操作的集合，比如插入数据库分为两段插入，第二次插入错误，第一次插入操作也需要回退
• 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务
• 一个完整的业务需要批量的DML（insert，update，delete） 语句共同联合完成
• 事务只和 DML 语句有关，或者说只有 DML 语句才有事务

原子性（Atomicity）

• 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
• 指的是整个事务是一个独立的单元，要么操作成功要么不成功

一致性（Consistency）

• 事务必须使数据库从一个一致状态转换到另外一个一致性状态，执行事务前后状态都必须一致。不一致包含三点：脏读、不可重复读、幻读
• 事务必须要保持和系统处于一致的状态

隔离性（Isolation）

• 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
• 事务是并发控制机制，他们交错也需要一致性，隐藏隔离，一般通过悲观或乐观锁实现

持久性（Durability）

• 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
• 一个成功的事务将永久改变系统的状态，在他结束之前所有导致状态变化的记录都在一个持久的事务日志中

mysql事务实现

• 使用 set 语句来改变自动提交模式
  • set autocommit = 0; 关闭自动提交模式
  • set autocommit = 1; 开启自动提交模式
• MySQL中默认是自动提交
• 使用事务时应该先关闭自动提交

事务实现方法

• start transaction : 开始一个事务，标记事务的起始点
• commit : 提交一个事务给数据库
• rollback : 将事务回滚，数据回到本次事务的起始状态
• set autocommit = 1 : 开启 MySQL 数据库的自动提交

事务处理步骤

四种隔离级别

• 越往上性能损失越大
• 开启了事务，修改数据时不提交或者回滚其他线程对数据的可见性

SELECT @@tx_isolation; # 查看当前会话的隔离级别
SELECT @@global.tx_isolation; # 查看系统当前的隔离级别
SET SESSION TRANSACTION isolation LEVEL REPEATABLE READ; # 设置当前会话当前隔离级别
set global transaction isolation level repeatable read; # 设置系统当前隔离级别
set autocommit = off 或者 start transaction; # 命令行，开始事务时

脏读 可以看到未提交的数据（read uncommit）

• 一个线程读到另一个线程未提交的数据，这个数据可能是存在问题的
• 如果程序处理的时候，有问题需要回滚，不应该被别人看到

不可重复读（read ）（Oracle 默认级别）

• 解决了脏读问题，但是在一个事务里面，两次查询看到的结果是不一致的
• 修改后没有提交的数据是看不到的

可重复读（mysql 默认级别）

• 解决了不可重复读的问题，但是可能会产生幻象。他认为不存在的记录可能在他操作期间被别人插入了。在他插入的时候就可能会报错

串行事务、序列化事务

• 解决了上面的所有问题，但是性能较差。因为他要独占所有资源
• 其他人要操作只能等他提交或回滚释放掉资源才能操作

MySQL锁

• update delete 这种操作，MySQL 内置了隐藏的独占锁，具体某个时刻只能有一个线程修改，这隐性独占锁的效率是最高的

行锁

• 我们在写update delete insert 时，会执行一个内部隐含的独占锁

行级共享锁

• 出现死锁的概率较高，一般不建议使用

行级独占锁

• 使用的时候被线程独占，回滚或提交时释放

表级锁

• 使用表级锁在退出的时候最好释放，不然容易造成死锁

表级写锁

表级读锁

悲观锁

• 容易造成性能问题，频繁加锁影响并发

乐观锁

约束

作用：是为了保证数据的完整性而实现的摘自一套机制，它具体的根据各个不同的数据库的实现而有不同的工具（约束）；

外键约束

• ALTER TABLE tradeInfo ADD CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID);
• 设置外键：CONSTRAINT t_id_fk FOREIGN KEY (t_id) REFERENCES t1 (id)

索引

• 有索引的字段尽量写在前面
• mysql使用指定索引语法，避免mysql自行优化使用其他索引
  • select * from table_name force index(index_name) where conditions;
• 作用
  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表与表之间的连接，实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化

主键索引（primary key）

• 主键是最常见的索引类型

• 确保数据记录的唯一性

• 确定数据记录的唯一性

• 主键的值必须是唯一的且主键的值不能为空

• 默认情况下，mysql会自动尝试使用索引，不需要我们手动干预

• 主键只能声明一次，可以一次声明多个字段为主键

主键的声明方法

create table student(
	studentNo int(11) primary key, # 第一种声明方式，直接在字段后面声明
    StudentName varchar(5),
    StudentPhone int(11),
    primary key(studentNo, studentPhone) # 第二种声明方式，可以同时设置多个字段为主键
); 

唯一索引（unique）

• 作用：避免同一个表中某数据列中的值重复

• 被修饰的字段具有唯一性

• 字段值为空是不被检查，可以插入多个 NULL

• unique 可以设置给多个字段，也可以一次给多个字段声明

• 可以同时设置对各字段为唯一索引

唯一索引声明方法

create table student(
	studentNo int(11) unique, # 第一种设置方式，为单个字段设置索引
    studentName varchar(6),
    studentPhone int(11) unique,
    unique key (studentNo, studentName) # 第二种创建方式，同时设置多个字段
);

常规索引 （ index ）

• 作用：快速定位特点数据

• index 和 key 关键字都可设置常规索引

• 应加在查找字段

• 不宜添加过多的常规索引，影响数据的插入、删除和修改操作

• 删除index约束 drop index index_table_index on index_table;

创建常规索引

create table student(
    studentNo int(11), 
    StudentName varchar(5),
    StudentPhone int(11),
	index/key (studentNo, studentPhone)  # 创建表时添加
);

alter table student add index (studentNo, studentPhone); # 创建后追加

全文检索（fulltext）

• 作用：快速定位特定数据
• 只能用于 MyISAM 类型的数据表
• 只能用于 char、varchar、text 数据列类型
• 适合大型数据集

创建全文索引

create table student(
	fulltext (studentName)  # 创建表时添加
);

alter table student add fulltext (studentName);  # 创建后添加

管理索引

创建索引

• 床表时添加：直接在相应字段添加即可
• 创表后追加：alter table table_name add index_name (column_name)

删除索引

• drop index index_name on table_name
• alter table table_name drop index index_name
• alter table table_name drop primary key 删除主键

查看索引

• show index/key from table_name;

数据库备份与恢复

备份

• D:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -p myschool > D:/temp/myschool.sql
• 数据库 bin 文件路径 > mysqldump -u 用户名 -p 数据库名 > 备份路径
• 备份数据库内的表：数据库bin文件路径 > mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份路径

这篇关于MySQL 学习笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！