MySql教程

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. 数据结构相对复杂，复制查询方面欠缺

nosql

相关概念

sql语句分类

名称 解释 命令
DDL （数据定义语言) 定义和管理数据对象， 如数据库，数据表等 CREATE、DROP、ALTER
DML （数据操作语言） 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL （数据查询语言） 用于查询数据库数据 SELECT
DCL （数据控制语言） 用来管理数据库的语言，包括管理权限及数据更改 GRANT、COMMIT、ROLLBACK

数据库引擎(表类型)

名称 MyISAM InnoDB
事务处理 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大,约2倍

命令行操作数据库

> 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
操作符名称 语法 描述
AND或&& a AND b 或 a && b 逻辑与，同时为真，结果才为真
OR或|| a OR b 或 a||b 逻辑或，只要一个为真，则结果为真
NOT或！ NOT a 或 !a 逻辑非，若操作数为假，结果则为真
操作符名称 语法 描述
IS NULL a IS NULL 若操作符为NULL，则结果为真
IS NOT NULL a IS NOT NULL 若操作符不为NULL，则结果为真
BETWEEN a BETWEEN b AND c 若a范围在b与c之间则结果为真
LIKE a LIKE b SQL模式匹配，若a匹配b，则结果为真
IN a IN (a1,a2,a3,….) 若a等于a1，a2…中的某一个，则结果为真

数据库操作

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

创建（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 多个表属于隐式
各个连接的对比
操作符名称 描述
INNER JOIN ( JOIN ) 如果表中有至少一个匹配，则返回行
LEFT JOIN 不论右表是否有匹配，都会返回左表的所有行
RIGHT JOIN 不论左表是否有匹配，都会返回右表的所有行

image-20210621192327125

自连接查询
  • 数据表与自身进行连接
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统计函数
函数名称 描述
COUNT( ) 返回满足SELECT条件的记录总和数，COUNT()只会返回非空的数据列，如果要返回所有的数据列可以使用count（*）
SUM( ) 返回数字字段或表达式列作统计，返回一列的总和
AVG( ) 通常为数值字段或表达列作统计，返回一列的平均值
MAX( ) 可以为数值字段、字符字段或表达式列作统计，返回最大的值
MIN( ) 可以为数值字段、字符字段或表达式列作统计，返回最小的值

  • 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
数值函数
函数名称 描述
ROUND() 四舍五入；SELECT ROUND(5,745, 1) 括号里第一项是要处理的数字，第二项是要保留的位数，不写就取整数
TRUNCATE() 截断数字；SELECT TRUNCATE(5.7563, 2) 第一项待处理，第二项是保留的位数
CEILING() 向上取整，只有一个参数
FLOOR() 向下取整
ABS() 取参数的绝对值
RAND() 返回 0-1 的随机浮点数
字符串函数
函数名称 描述
LENGTH() 返回字符串的长度；SELECT LENGTH('sky')
UPPER() 字符串转大写
LOWER() 转小写
LTRIM() 去除传入参数左侧的空格；RTRIM()会去除右侧；TRIM() 会同时去除两侧的空格
LEFT() LEFT('Hello,World', 5) 返回前五个字符 ；RIGHT() 同理反之
SUBSTRING() SUBSTRING('Hello,World', 2, 5) 从第二位开始，返回五个字符，第三参数不传就会取到最后
LOCATE() LOCATE('e', 'hello') 第一个参数是要查找的字符，后面是查找的对象；返回值是最先发现的下标，如果不存在返回 0 ，不区分大小写
CONCAT() 传入多个字符串参数，函数会将他们进行拼接

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 数据库的自动提交

事务处理步骤

image-20210624192943835

四种隔离级别

  • 越往上性能损失越大
  • 开启了事务，修改数据时不提交或者回滚其他线程对数据的可见性
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 学习笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航