MySQL学习笔记

数据库：是存储数据的仓库。数据库管理系统：是操作和管理数据库的软件

SQL：是操作关系型数据库的编程语言，是一套标准

关系型数据库（RDBMS）：通过表存储数据的数据库，就称为关系型数据库

MySQL不区分大小写，语句以 ";" 结尾

一、SQL分类

DDL（数据定义语言）：用来定义数据库对象（数据库、表、字段）

DML（数据操作语言）：用来对数据库表中的数据进行 增删改

DQL（数据查询语言）：用来查询数据库中表的记录

DCL（数据控制语言）：用来创建数据库用户、控制数据库的访问权限

二、语法

查询：

查询所有数据库  

SHOW DATABASE;

查询当前操作数据库    

SELECT DATABASE();

查询当前数据库所有表

SHOW TABLES;

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

SELECT查询语法：

SELECT 字段列表 FROM 表名列表 WHERE 条件列表

GROUP BY 分组字段列表 HAVING 分组后条件列表

ORDER BY 排序字段列表 LIMIT 分页参数

执行顺序 FROM>WHERE>GROUP BY>SELECT>ORDER BY>LIMIT

查询多个字段

SELECT 字段1，字段2，… FROM 表名;

SELECT *FROM 表名;

设置别名

SELECT 字段1 [AS 别名1]，字段2 [AS 别名2]… FROM 表名;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

DISTINCT：加上则为去重

聚合函数：将一列数据作为一个整体，进行纵向计算

常见聚合函数

count：统计数量   max：最大值 min：最小值  avg：平均值  sum：求和

语法：

SELECT 聚合函数（字段列表）FROM 表名;

注意：

          null值不参与所有聚合函数运算

分组查询

语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where与having区别

          执行时机不同：where是分组之前进行过滤，不满足where条件的不参与分组。而having是分组之后对结果进行过滤

          判断条件不同：where不能对聚合函数进行判断，而having可以

注意：

          执行顺序：where>聚合函数>having

          分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;

排序方式：ASC升序（默认）、DESC降序

注意：

          如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引，每页查询记录数;

注意：

          起始索引从0开始，起始索引 = （查询页码 - 1）*每页显示记录数

          分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是LIMIT

          如果查询的是第一页数据，起始索引可以省略，直接简写成 limit 10

创建：

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

[IF NOT EXISTS]：数据库存在则不创建，不存在则创建

创建表

CREATE TABLE 表名(

          字段1 字段1类型 [COMMENT 字段1注释],

字段2 字段2类型 [COMMENT 字段2注释],

…

字段n 字段n类型 [COMMENT 字段n注释]

) [COMMENT 字段1注释];

删除：

删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

删除字段

ALTER TABLE 表名 DROP 字段名;

删除表

DROP TABLE [IF EXISTS] 表名;

删除指定表

TRUNCATE TABLE 表名;

删除数据

DELETE FROM 表名 [WHERE 条件];

注意：

          DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据

          DELETE语句不能删除某一个字段的值（可以使用UPDATE），只能删除一整条

修改：

添加字段

ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释] [约束];

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释] [约束];

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

修改数据

UPDATE 表名 SET 字段名1 = 值1，字段名2 = 值2，…[WHERE 条件];

注意：

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

使用：

使用数据库

USE 数据库名;

添加：

给指定字段添加数据

INSERT INTO 表名（字段名1，字段名2，…）VALUES （值1，值2）;

给全部字段添加数据

INSERT INTO 表名 VALUES（值1，值2…）;

批量添加数据

INSERT INTO 表名（字段名1，字段名2…）VALUES（值1，值2…），（值1，值2…），（…）;

INSERT INTO 表名VALUES（值1，值2…），（值1，值2…），（…）;

注意：

          插入数据时，指定的字段顺序需要与值得顺序一一对应

          字符串是日期数据应该包含在引号中

          插入的数据大小，应该在字段的规定范围内

管理：

查询用户

USE mysql

SELECT * FROM user;

创建用户

CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’；

修改用户密码

ALTER USER ‘用户名’@’主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;

删除用户

DROP USER ‘用户名’@’主机名’;

注意：

          主机名可以使用 % 通配

查询权限

SHOW GRANTS FOR ‘用户名’@’主机名’;

授予权限

GRANT 权限列表 ON 数据库名.表名 TO  ‘用户名’@’主机名’;

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM  ‘用户名’@’主机名’

注意：

          多个权限之间，使用逗号分隔

          授权时，数据库名和表名可以使用 * 进行通配，代表所有

函数：是指一段可以直接被另一段程序调用的程序或代码

SELECT 函数（参数）

 三、约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确、有效性和完整性

分类：

        非空约束：限制该字段的数据不能为null                                                                  NOT NULL

        唯一约束：保证该字段的所有数据都是唯一、不重复的                                              UNIQUE

        主键约束：主键是一行数据的唯一标识，要求非空且唯一                                PRIMARY KEY

        默认约束：保存数据时，如果未指定该字段的值，则采用默认值                              DEFAULT

        检查约束：保证字段满足某一个条件                                                                            CHECK

        外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性   FOREIGN KEY

注意：约束是作用于表中地段上的，可以在创建表/修改表的时候添加约束

外键约束：用来让两张表的数据之间建立连接，从而保证数据的一致性、完整性

语法

添加外键

CREATE TABLE 表名（

          字段名 数据类型，

          …

          [CONSTRAINT] [外键名称] FOREIGN KEY （外键字段名） REFERENCES 主表 （主表列名）;

）

ALERT TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY（外键字段名）REFERENCES 主表（主表列名）;

删除外键

ALERT TABLE 表名 DROP FOREIGN KEY 外键名称;

ALERT TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY（外键字段）REFERENCES 主表名 （主表字段名）ON UPDETE CASCADE ON DELETE CASCADE;

多表关系

一对多（多对一）

实现：在多的一方建立外键，指向一的一方的主键

多对多

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

用于单表拆分，提升效率

实现：在任意一方加入外键，关联另一方主键，并且设置外键为唯一的（UNIQUE）

多表查询

连接查询

内连接

          隐式内连接

          SELECT 字段列表 FROM 表1，表2 WHERE 条件…;

          显示内连接

          SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…;

内连接是查询两张表的交集的部分

外连接

          左外连接

          SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;

          相当于查询表1（左表）的所有数据 包含 表1和表2交集部分的数据

          右外连接

          SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…;

相当于查询表2（右表）的所有数据 包含 表1和表2交集部分的数据

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

自连接查询，可以使内连接查询，也可以是外连接查询

联合查询

SELECT 字段列表 FROM 表A…

UNION [ALL]

SELECT 字段列表 FROM 表B…;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all会将全部的数据直接合并在一起，union 会对合并之后的数据去重

子查询：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

SELECT *FROM t1 WHERE column1 = （SELECT column1 FROM t2）;

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

根据子查询结果不同分为：

         标量子查询（子查询结果为单个值）

         列子查询（子查询结果为一列）

         行子查询（子查询结果为一行）

        

         表子查询（子查询结果为多行多列）        

根据子查询位置不同，分为：

         WHERE之后、FROM之后、SELECT之后

事务

是一组操作的集合，是一个不可分割的工作单位，会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

MySQL默认的事务是自动提交的

查看/设置事务提交方式

SELECT @@autocommit;

SET @@aotucommit = 0;

0为不自动提交，1为自动提交

提交事务

COMMIT;

回滚事务

ROLLBACK;

开启事务

START TRANSACTION 或 BEGIN

事务的四大特性（ACID）：

         原子性（Atomicity）：事务是不可分割的最小操作单元，要么全成功，要么全失败

         一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态

         隔离性（Isolation）：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

         持久性（Durability）：事务一旦提交或回滚，他对数据库中数据的改变就是永久的

√代表会出现的（即未解决）、×代表不会出现的（即已解决）

从上至下性能逐渐下降，从下至上数据安全性逐渐下降

读未提交（RU）：一个事务还没提交时，他做的变更就能被别的事务看到

读已提交（RC）：一个事务提交后，他做的变更才会被别的事务看到

可重复读（RR）：一个事务中，对同一份数据的读取结果总是相同的，无论是否有其他事物对其进行操作，也不管这个事务是否提交    InnoDB默认

串行化（Serializable）：事务串行化执行，每次读都需要获得表级共享锁，读写互相阻塞

Session当前会话窗口有效、global所有客户端会话窗口有效