[MySQL]学习笔记一(总)

MySQL

1.MySQL的安装

懒得自己搞了,就拿一个我当初安装的时候参考的教程连接吧~

link:https://blog.csdn.net/qq_42128266/article/details/90672829

2.MySQL的卸载

• 去 MySQL 的安装目录找到 my.ini 文件

复制 ”datadir=D:\ . . . \MySQL\data"

• 到 控制面板\程序\程序和功能 去卸载 MySQL
• 删除"D:\ . . . \ "下的 MySQL 文件夹

3.MYSQL 服务的配置

懒得搞哦，直接附上我写的另一篇文章

link：https://blog.csdn.net/qq_44764792/article/details/104268746

4.MySQL 的登录

[ 以下皆以管理员身份打开 cmd ，其实好像直接打开 cmd 也是可以的，但不同电脑有些不同的情况就不做说明了]

1.打开自己的 MySQL

• 首先，以管理员身份打开 cmd ，然后输入 mysql -u root -p
  
  输入 password : 密码
  成功进入 MySQL
  

• 以管理员身份打开 cmd ，然后输入 mysql -u root -p密码

2.A用户去登录B用户的 MySQL

• 以管理员身份打开 cmd ，然后输入 mysql -h IP -u root -p密码，密码这里一定要和 -p 紧连着

• 以管理员身份打开 cmd ，然后输入 mysql --host=IP --user=root --password=密码
  

3.MySQL 登录权限的修改

但是存在一种情况就是 B 的MySQL是不准别人连接上的，可以采用如下方式更改 MySQL 的权限设置

以管理员身份打开 cmd ,并登录需要修改的MySQL

1. 改表法

可能是你的帐号不允许从远程登陆，只能在 localhost 。这个时候只要在 localhost 的那台电脑，登入 MySQL 后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从"localhost"改称"%"

->use mysql

->update user set host=’%’ where user=‘root’;

->flush privileges; //这个可以保证不用重新打开窗口就已经自动更新

关闭远程访问

->use mysql

->update user set host=‘localhost’ where user=‘root’ and host=’%’;

->flush privileges;

2.授权法

->grant all privileges on *.* to ‘root’@’%’ with grant option;

->flush privileges;

例如，你想用户A_user使用A_password从任何主机连接到 MySQL 服务器的话。

->grant all privileges on *.* to ‘A_user’@’%’ identified by ‘A_password’ with grant option;

->flush privileges;

或者，你想如果允许用户A_user从IP为192.168.0.6的主机连接到 MySQL 服务器，并使用A_password作为密码**->grant all privileges on *.* to ‘A_user’@‘192.168.0.6’ identified by ‘A_password’ with grant option;**

->flush privileges;

SQL [ Structured Query Language]

结构化查询语言

定义了操作所有关系型数据库的规则

1. SQL 的通用语法

• SQL 语句可以单行或多行书写，以分号结尾；
• 可使用空格和缩进来增强语句的可读性；
• MySQL 数据库的 SQL 语句不区分大小写；
• 有三种注释形式：
  • 单行注释
    • – 注释语句 注意这里的‘–’后面还有一个空格
    • #注释语句 MySQL 特有
  • 多行注释
    • /*注释语句*/

2. SQL 语言的分类

• DDL [Data Definition Lauguage] 数据定义语言

  用来定义数据库对象：数据库，表，列等

• DML [Data Manipulation Language] 数据操作语言

  用来对数据库中表的数据增删改

• DQL [Data Query Language] 数据查询语言

  用来查询数据库中表的记录

• DCL [Data Control Language] 数据控制语言

  用来定义数据库的访问权限和安全语言‘

3.DDL : 操作数据库，表

操作数据库：CRUD

• C [ Create ]：创建

  • 创建数据库

    create database 数据库名称；

  • 判断不存在后，才创建数据库

    create database if not exists 数据库名称;

  • 创建指定字符集的数据库

    create database 数据库名称 character set 字符集名;

  • 判断不存在后，创建指定字符集的数据库

    create database if not exists 数据库名称 character set 字符集名;

• R [ Retrieve ]：查询

  • 查询所有数据库的名称

    show databases;

  • 查询某个数据库的字符集 ：查询数据库的创建语句

    show create database 数据库名称;

• U [ Update ]：修改

  • 修改数据库的字符集

    alter database 数据库名称 character set 字符集名;

• D[ Delete ]：删除

  • 删除数据库

    drop database 数据库名称;

  • 判断数据库存在，再删除

    drop database if exists 数据库名称;

• 使用数据库

  • 查询当前正在使用的数据库的名称

    select database();

  • 使用数据库

    use 数据库名称; //注意这里就database了

操作表

• C [ Create ]：创建

  • 语法：

    create table 表名(

    ​ 列名1 数据类型1,

    ​ 列名2 数据类型2,

    ​ … …

    ​ 列名n 数据类型n

    );

    注意：最后一列没有逗号(,)

    数据类型

    1.int：整数类型

    ​ * age int,

    2.double：小数类型

    ​ * score double(5,2) // 五位数，其中小数位占两位，最大值是999.99

    3.date：日期 ，只包含年月日，yyyy–mm–dd

    4.datetime：日期 ，包含年月日时分秒，yyyy–mm–dd hh:mm:ss

    5.timestamp：时间错类型 ，包含年月日时分秒，yyyy–mm–dd hh:mm:ss

    ​ 如果不给这个字符段赋值，或者赋值为NULL，则默认当前使用系统时间来自动赋值。

    6.varchar：字符串

    ​ * name varchar(20) // 姓名 ，最大字符为20个

    ​ * zhangsan 8个字符 ； 张三 2个字符

    举个栗子~

    /*创建表

    ​ create table student(

    ​ id int,

    ​ name varchar(20),

    ​ age int,

    ​ score double,

    ​ birthday date,

    ​ insert_time timestamp

    ); */

    注意：创建表的时候不要随意的在mysql等这样的数据库中创建，可以在自己创建的数据库中加表，其次在括号这一行中，’ ) '前面不得有空格，可能会报错

• R [ Retrieve ]：查询

  • 查询某个数据库中所有表的名称

    show tables;

  • 查询表的字符集

    show create table 表名;

  • 查询表结构

    desc 表名;

• U [ Update ]：修改

  • 修改表名

    alter table 表名 rename to 新表名;

  • 修改表的字符集

    alter table 表名 character set 新字符集;

  • 添加一列

    alter table 表名 add 列名 列的数据类型;

  • 修改表的 列名 列的数据类型

    alter table 表名 change 列名 新列名 新列名的数据类型;

    alter table 表名 modify 列名 新数据类型;

  • 删除列

    alter table 表名 drop 列名;

• D[ Delete ]：删除

  • 删除表

    drop table 表名;

  • 判断表存在，再删除

    drop table if exists 表名;

• 使用数据库

  • 查询当前正在使用的数据库的名称

    select database();

  • 使用数据库

4.DML：增删改表中的数据

• 添加数据：

  • 语法：

    • insert into 表名(列1,列2,…,列n) values(值1,值2,…,值n);

    注意：

    1. 列名和值要一一对应;

    2. 如果表民后，不定义列名，则默认给所有列添加值；

       insert into 表名 values(值1,值2,…,值n);

    3. 除了数字类型，其他数据类型需要使用引号[ 单双引号都可以 ]引起来

• 删除数据：

  • 语法：

    • delete from 表名 [ where 条件 ];

    1. 如果不加条件，则删除表中所有记录；
    2. 如果要删除所有记录
       • delete from 表名; – 不推荐，有多少记录就会执行多少次删除操作
       • truncate table 表名; – 先删除表，然后再创建一张一模一样的表。

• 修改数据：

  • 语法：

    • update 表名 set 列名1 = 值1，列名2 = 值2，… … [ where 条件];

    注意：

    1.如果不加条件，则改变表中所有记录；

    2.可能会出现

    ErrorCode: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

    这样的报错，这种情况是MySQL在保护模式, 无法进行修改操作，可以进行如下调试：

    show variables like ‘SQL_SAFE_UPDATES’; //这时候可以看到 sql_safe_updates 的 value 为 on

    set SQL_SAFE_UPDATES = 0;或者set SQL_SAFE_UPDATES = off;

    若要改回到保护状态，只要执行如下操作即可：

    set SQL_SAFE_UPDATES = 1;或者set SQL_SAFE_UPDATES = on;

5.DQL：查询表中的记录

select * from 表名;

• 语法：

  select

  ​ 字段列表

  from

  ​ 表明列表

  where

  ​ 条件列表

  group by

  ​ 分组字段

  having

  ​ 分组之后的条件

  order by

  ​ 排序

  limit

  ​ 分页限定

• 基础查询

  • 多个字段的查询

    select 字段名1,字段名2,… from 表名;

    注意：

    如果查询所有字段，则可以使用*来替代字段列表。

  • 去除重复 distinct

    select distinct 列名 from 表名 ; //删除表中这一列重复的元素

  • 计算列

    • 一般可以使用四则运算计算那一些列的值。[ 一般只会进行数值型的计算]

    • ifull(表达式1,表达式2)：null参与运算，计算结果都为null

      表达式1：哪个字段需要判断是否为null

      表达式2：如果该字段为null后的替换值

  • 起别名：

    as : as 也可以省略

• 条件查询

  • where 子句后跟条件

  • 运算符

    • > ，>=，< ，<= ，= ，!= ，<>[与 != 等价]

    • between … and … [大于等于…小于…]

    • in (集合)

    • like ：模糊查询

      占位符：

      ​ ‘_’：单个任意字符

      ​ ‘%’：任意个任意字符

    • is null / is not null

    • and 或 &&

    • or 或 ||

    • not 或 ！

• 排序查询

  • 语法： order by 子句;

    ​ order by 排序字段1 排序方式1,排序字段2 排序方式2, … ;

  • 排序方式：

    • ASC：升序，默认的；
    • DESC：降序。

  注意：

  如果有多个排序条件，则当前面的条件值一样时，才会判断后面的条件。

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

  • count：计算个数
    • 计算一般选择非空的列：主键
    • count(*)：*：表示只要这一行有一个列非空，就算一条数据
  • max：计算最大值
  • min：计算最小值
  • sum：计算和
  • avg：计算平均值

  注意：

  聚合函数的计算，是排除 NULL 值在外进行计算

  解决方案：

       1. 选择不包含非空的列进行计算
       2. IFNULL函数
  

• 分组查询：

  • 语法：

    ​ group by 分组字段

  注意：

  1.分组之后查询的字段只能是：分组字段，聚合函数；

  2.where 和 having 的区别？

  • where 在分组之前进行限定，如果不满足条件，则不参与分组；having 在分组之后进行限定，如果不满足条件，则不会被查询出来。

  • where 后不可以跟聚合函数，having 后可以进行聚合函数的判断。

• 分页查询

  • 语法：limit 开始的索引,每页查询的条数;
  • 公式：开始的索引 =(当前页码数-1)*每页显示的条数
  • limit 操作是一个"方言"

CREATE TABLE IF NOT EXISTS s(
id INT,--  编号
name VARCHAR(10),--  姓名
age INT,--  年龄
sex VARCHAR(5),--  性别
address VARCHAR(10),--  地址
math INT,-- 数学
english INT-- 英语
);

INSERT INTO s VALUES
(1,'南京大学',18,'男','南京',98,99),
(2,'武汉大学',19,'男','武汉',98,89),
(3,'中南大学',28,'女','长沙',99,99),
(4,'复旦大学',38,'女','上海',98,100),
(5,'北京大学',100,'女','北京',98,100),
(6,'清华大学',110,'女','北京',98,99),
(7,'北航',28,'男','北京',null,99),
(8,'上海交大',58,'女','上海',98,97);

#查询表中所有数据
SELECT * FROM s;

#基础查询综合运用
SELECT name 大学,address AS 地址,math 数学,english 英语,IFNULL(math,0)+english 总分 FROM s;

#条件查询
-- 查询年龄大于28岁
SELECT * FROM s WHERE age >28;
SELECT * FROM s WHERE age >=28;
-- 查询年龄等于28岁
SELECT * FROM s WHERE age =28;
-- 查询年龄不等于28岁
SELECT * FROM s WHERE age !=28;
SELECT * FROM s WHERE age <>28;
-- 查询年龄在28到38岁之间
SELECT * FROM s WHERE age >=28 && age <=38;
SELECT * FROM s WHERE age >=28 AND age <=38;
SELECT * FROM s WHERE age between 28 AND 58;
-- 查询年龄等于28或38或100岁
SELECT * FROM s WHERE age =28 OR age =38 OR age =100;
SELECT * FROM s WHERE age =18 || age =38 || age =100;
SELECT * FROM s WHERE age IN(28,38,100);
-- 查询数学没有成绩的数据
/*SELECT * FROM s WHERE math = NULL;该语句是错误的，无效的*/
SELECT * FROM s WHERE math IS NULL;
-- 查询数学有成绩的数据
SELECT * FROM s WHERE math IS NOT NULL;
-- 查询姓名倒数第二个字是“大”的数据
SELECT * FROM s WHERE name LIKE '%大_';
-- 查询姓名中包含“大”的数据
SELECT * FROM s WHERE name LIKE '%大%';
-- 查询姓名中第二个字是“京”的数据
SELECT * FROM s WHERE name LIKE '_京%';
-- 查询英语成绩为两位数的数据
SELECT * FROM s WHERE english LIKE '__';

#排序查找
-- 按照英语成绩排名
-- 默认为升序 ASC
SELECT * FROM s ORDER BY english;
SELECT * FROM s ORDER BY english ASC;
-- 降序 DESC
SELECT * FROM s ORDER BY english DESC;
-- 先按数学升序，后按英语降序
SELECT * FROM s ORDER BY  math ASC , english DESC;

#聚合函数
-- count
SELECT COUNT(id) FROM s;
SELECT COUNT(math) FROM s;
SELECT COUNT(IFNULL(math,0)) FROM s;
SELECT COUNT(*) FROM s;
-- max/min
SELECT MAX(english) FROM s;
SELECT MIN(english) FROM s;
-- sum
SELECT SUM(english) FROM s;
-- avg
SELECT AVG(english) FROM s;

#分组查询
-- 按照性别分组，分别查询其数目
SELECT sex,COUNT(id) FROM s GROUP BY sex;
-- 按照性别分组，分别查询其数目，要求：地址位于北京或上海的才参与分组
SELECT sex,COUNT(id) FROM s WHERE address='北京'OR address='上海' GROUP BY sex;
-- 按照按照性别分组，分别查询其数目，要求：地址位于北京或上海的才参与分组，且分组以后，数据数要大于2个。两种方法...
SELECT sex,COUNT(id) FROM s WHERE address='北京'OR address='上海' GROUP BY sex HAVING COUNT(id)>2;
SELECT sex,COUNT(id) AS 数据数 FROM s WHERE address='北京'OR address='上海' GROUP BY sex HAVING 数据数>2;

#分页查询
-- 每页显示3条记录
SELECT * FROM s LIMIT 0,3;
SELECT * FROM s LIMIT 3,3;
SELECT * FROM s LIMIT 6,3;

约束

概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性。

主键约束：primary key

注意：

1. 含义：非空且唯一；
2. 一张表只能有一个字段作为主键，但可以有复合主键 primary key (列1,列2,…)；
3. 主键就是表中记录的唯一标识。

• 创建表时添加主键约束

  CREATE TABLE stu(
  id INT PRIMARY KEY,-- 添加了主键约束
  phone_number VARCHAR(20) 
  );
  

• 创建表完后，添加主键约束

  ​ alter table 表名 modify 列名 数据类型 primary key;

  ​ *alter table stu modify id primary key;

• 删除主键约束

  ​ alter table 表名 drop primary key; /* 这里不需要加列名，因为一张表中主键唯一 */

  ​ *alter table stu drop primary key;

  ​ *alter table stu modify id int;

• 自动增长：

  • 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长
  • 在创建表时，添加主键约束，使用auto_increment 可以来完成值的自动增长

  CREATE TABLE stu(
  id INT PRIMARY KEY AUTO_INCREMENT,-- 添加了主键约束和自动增长
  phone_number VARCHAR(20) 
  );
  

  • 添加自动增长

    ​ alter table 表名 modify 列名 数据类型 auto_increment;

    ​ *alter table stu modify id int auto_increment;

  • 删除自动增长

    ​ alter table 表名 modify 列名 数据类型 – 不会删除主键约束

    ​ *alter table stu modify id int;

非空约束：not null

• 创建表时添加非空约束

  CREATE TABLE stu(
  id INT,
  phone_number VARCHAR(20) NOT NULL -- 添加了非空约束
  );
  

• 创建表完后，添加非空约束

  ​ alter table 表名 modify 列名 数据类型 not null;

  ​ *alter table stu modify phone_number varchar(20) not null;

• 删除 phone_number 的非空约束

  ​ alter table 表名 modify 列名 数据类型

  ​ *alter table stu modify phone_number varchar(20);

唯一约束：unique，不能重复

• 创建表时添加唯一约束

  CREATE TABLE stu(
  id INT,
  phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
  );
  

  注意：MySQL 中，唯一约束限定的列的值可以有多个 NULL。

• 创建表完后，添加非空约束

  ​ alter table 表名 modify 列名 数据类型 unique;

  ​ *alter table stu modify phone_number varchar(20) unique;

• 删除唯一约束

  ​ alter table 表名 drop index 列名;

  ​ *alter table stu drop index phone_number;

  ​ *alter table stu modify phone_number varchar(20);

外键约束：foreign key，让表与表产生关系，从而保证数据的正确性。

使表与表中关联的数据关系始终保持不变，准确来说，限定了删除和修改的操作，但没有限定添加操作。

• 在创建表时，可以添加外键

  • 语法：

    creat table 表名(

    ​ … …

    ​ 外键列,

    ​ … …

    ​ constraint 外键名称 foreign key (外键列名称) references 主表名称(主列表名称)

    /* constraint 外键名称 可省，系统可以自动分配 */

    );

• 创建表之后，添加外键

  ​ alter table 表名 add constraint 外键名称 foreign key (外键列名称) peferences 主表名称(主列表名称);

• 删除外键

  ​ alter table 表名 drop foreign key 外键名称;

• 级联操作：

  • 添加级联操作

    ​ alter table 表名 add constraint 外键名称 foreign key (外键列名称) peferences 主表名称(主列表名称) on update cascade on delete cascade;

  • 分类：

    • 级联更新：on update cascade
    • 级联删除：on delete cascade

数据库的设计

1. 多表之间的关系

1.一对一

如：人和身份证

分析：一个人只有一个身份证，一个身份证只能对应一个人

实现方式：可以在任意一方添加唯一外键指向另一方的主键。

2.一对多(多对一)

如：部门和员工

分析：一个部门有多个员工，一个员工只能对应多个部门

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

3.多对多

如：学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

2. 数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

设计关系型数据库时，遵从不同的规范要求，射出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式(1NF)，第二范式(2NF)，第三范式(3NF)，巴斯-科德范式(BCNF)，第四范式(4NF)，第五范式(5NF,又称完美范式)

1. 第一范式(1NF)：

每一列都是不可分割的原子项

2.第二范式(2NF)：

在 1NF 的基础上，非码属性必须完全依赖于码，即在 1NF 的基础上消除非主属性对主码的部分函数依赖

3.第三范式(3NF)：

在 3NF 的基础上，任何非主属性不依赖于其他非主属性，即在 2NF 的基础上消除传递依赖

• 函数依赖：A → B，如果通过 A 属性(属性组)的值，可以确定唯一 B 属性的值，则称 B 依赖于 A 。

  ​ 如：学号 → 姓名 (学号，课程名称) → 分数

• 完全函数依赖：A → B，如果 A 是一个属性组，则 B 属性值得确定需要依赖于 A 属性组中的所有属性值。

  ​ 如：(学号，课程名称) → 分数

• 部分函数依赖：A → B，B → C，如果通过 A 属性(属性组)的值，可以确定唯一 B 属性的值，再如果通过 B 属性(属性组)的值，可以确定唯一 C 属性的值,则称 C 传递函数依赖于 A

  ​ 如：学号 → 系名 系名 → 系主任

• 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码

  • 主属性：码属性组中的所有属性
  • 非主属性：除码属性组的属性

数据库的备份和导入

• 命令行

  语法：

  • 备份： mysqldump -u 用户名 -p密码 要备份的数据库的名称 > 保存路径\保存名字.sql
  • 还原：
    • 登录数据库
    • 创建数据库
    • 使用数据库
    • 执行文件：sourcr 文件路径\保存名字.sql

• 图形化工具：略

导出备份时可能出现 “Access is denied.” 拒绝访问的问题

给个解决连接 link ~

另外，在还原数据库的时候可能出现打不开文件的情况，这种情况可能是还原路径里存在中文等…

多表查询

1. 查询语法：

​ select

​ 字段列表

​ from

​ 表明列表

​ where

​ … … 和单表查询类似

2. 笛卡尔积：

• 有两个集合 A ，B 。取这两个集合的所有组成情况。
• 要完成多表查询，需要消除无用的数据。

3.多表查询的分类：

1. 内连接查询：

• 隐式内连接：用 where 条件删除无用数据

  语法：

  • select * from 表1,表2,…[ where 条件];
  • select 表1.列1 ,表2.列2,… from 表1,表2,…[ where 条件]; – 对于不重复的列名，前面的’ 表名. '可以省去。
  • select 别名1.列1 ,别名2.列2,… from 表1 别名1,表2 别名2,…[ where 条件];

• 显式内连接：

  语法：

  • select 字段列表 from 表1 [ inner ] join 表2 on 条件;

• 内连接查询

  • 从哪些表中查询数据
  • 条件是什么
  • 查询哪些字段

2. 外连接查询：

• 左外连接：
  • 语法：select 字段列表 from 表1 left [ outer ] join 表2 on 条件；
  • 查询的是左表所有数据以及其交集部分。
• 右外连接：
  • 语法：select 字段列表 from 表1 right [ outer ] join 表2 on 条件；
  • 查询的是右表所有数据以及其交集部分。

3. 子查询：

概念：查询中签到查询，称嵌套查询为子查询。

• 子查询的结果是单行单列的：

  子查询可以作为条件，使用运算符 [ < , <= , >= , > , = ] 去判断。

• 子查询的结果是多行单列的：

  子查询可以作为条件，使用运算符 in 去判断。

• 子查询的结果是多行多列的：

  子查询可以作为一张虚拟表参与查询

/* 创建 class 表 */
CREATE TABLE class(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 班级序列，主键，自动增长
    class_number INT UNIQUE, -- 班级编号
    teacher VARCHAR(20) -- 班主任
);
INSERT INTO class VALUES(1,1502,'卢海燕'),(NULL,1503,'吴淞'),(NULL,1501,'徐珺');

/* 创建 student 表 */
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 学号
    name VARCHAR(10), -- 学生姓名
    insert_time DATE, -- 入学时间
    grade INT, -- 入学成绩
    class_id INT, -- 班级编号
    FOREIGN KEY (class_id) REFERENCES class(id) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO student VALUES
	(1,'苏东坡','2016--9--2',789,1),(NULL,'王安石','2013--9--2',777,1),
	(NULL,'沈括','2016--2--2',666,1),(NULL,'白居易','2014--6--29',985,2),
	(NULL,'李煜','2018--3--12',576,2),(NULL,'李清照','2020--5--17',543,2),
    (NULL,'欧阳修','2012--4--8',622,2),(NULL,'晏殊','2019--4--9',453,3),
    (NULL,'严晓倩','2011--11--12',211,3);

/* 内连接查询 */
#隐式内连接
SELECT * FROM student,class;
SELECT * FROM student,class WHERE class_id=class.id;
SELECT * FROM student,class WHERE student.class_id=class.id;
SELECT student.id,name,class_number,teacher FROM student,class WHERE student.class_id=class.id;
-- 起别名
SELECT n1.id,n1.name,n2.class_number,n2.teacher FROM student n1,class n2 WHERE n1.class_id=n2.id;
#显式内连接
SELECT * FROM student INNER JOIN class ON class_id=class.id;
SELECT * FROM student JOIN class ON class_id=class.id;
SELECT student.id,name,class_number,teacher FROM student INNER JOIN class ON class_id=class.id;

INSERT INTO student VALUES(NULL,'朱淑珍','2020--12--9',973,4); 
-- 用 class_id=class.id 进行双表关联时用内连接查询是查询不出来的

/* 外连接查询 */
#左外连接
SELECT * FROM student LEFT OUTER JOIN class ON class_id=class.id;
SELECT * FROM student LEFT JOIN class ON class_id=class.id;
#右外连接
SELECT * FROM student RIGHT OUTER JOIN class ON class_id=class.id;
SELECT * FROM student RIGHT JOIN class ON class_id=class.id;
-- 起别名
SELECT n1.*,n2.class_number,n2.teacher FROM student n1 LEFT JOIN class n2 ON n1.class_id=n2.id;

/* 子查询 */
#单行单列
-- 查询成绩在平均成绩以下的学生的学生信息
SELECT * FROM student WHERE grade <= (SELECT AVG(grade) FROM student);
#多行单列
-- 查询班主任是卢海燕老师或徐珺老师的学生的学生信息
SELECT * FROM student WHERE class_id IN(SELECT class.id FROM class WHERE teacher='卢海燕' OR teacher='徐珺');
#多行多列
-- 查询班主任是卢海燕老师或徐珺老师的学生的部分信息
SELECT n1.id,name,insert_time,grade,class_number,teacher FROM student n1,(SELECT * FROM class WHERE teacher='卢海燕' OR teacher='徐珺') n2 WHERE class_id= n2.id;

4.综合练习

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);			
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

#需求：
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT 
	n1.id 员工编号 ,n1.ename 员工姓名,
	n1.salary 工资 ,n2.jname 职务名称,
	n2.description 职务描述 
FROM 
	emp n1, job n2 
WHERE 
	n1.job_id = n2.id;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT 
	n1.id 员工编号 ,n1.ename 员工姓名,
	n1.salary 工资 ,n2.jname 职务名称,
	n2.description 职务描述,n3.dname 部门名称,
	n3.loc 部门位置 
FROM 
	emp n1, job n2, dept n3 
WHERE 
	n1.job_id = n2.id AND n1.dept_id=n3.id;
	
-- 3.查询员工姓名，工资，工资等级
SELECT 
	n1.ename 员工姓名,n1.salary 工资,
	n4.grade 工资等级 
FROM 
	emp n1, salarygrade n4 
WHERE 
	n1.salary>=n4.losalary AND n1.salary<=n4.hisalary;
	
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT 
	n1.ename 员工姓名,n1.salary 工资,
	n2.jname 职务名称,n2.description 职务描述,
	n3.dname 部门名称,n3.loc 部门位置,
	n4.grade 工资等级 
FROM 
	emp n1, job n2, dept n3, salarygrade n4 
WHERE 
	n1.job_id = n2.id AND n1.dept_id=n3.id AND
    n1.salary>=n4.losalary AND n1.salary<=n4.hisalary;
    
-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT 
	t.a 部门编号,t.b 部门名称,
	t.c 部门位置,COUNT(b) 部门人数 
FROM 
	(SELECT 
     	n3.id a, n3.dname b, n3.loc c -- 这里一般情况下不能用中文别名，会出现错误
     FROM 
     	dept n3 , emp n1 
     WHERE 
     	n3.id = n1.dept_id) t -- 派生表一定要取别名
     GROUP BY t.a;	-- group by 只能对一张表进行操作
     
SELECT 
	n3.id 部门编号, n3.dname 部门名称,
    n3.loc 部门位置, n4.total 部门人数 
FROM 
	dept n3 ,
	(SELECT 
     	dept_id,COUNT(id) total 
     -- 在派生表里的聚合函数在主 表中要查询到必须取别名，而非t.COUNT(id)
     FROM 
     	emp 
     GROUP BY 
     dept_id) n4 
WHERE 
	n3.id = n4.dept_id;
	
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT 
	n1.ename 员工姓名,n2.ename 直接上级 -- 通过起别名把一张表变为两张表
FROM 
	emp n1 
LEFT JOIN 
	emp n2 
ON 
	n1.mgr = n2.id;

事务

1. 事务的基本介绍

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

1.操作：

• 开启事务：start transaction;
• 回滚：rollback;
• 提交：commit;

举个栗子 ~

CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
-- 0. 开启事务
START TRANSACTION;
 -- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;

2. MySQL 数据库中事务默认自动提交

• 事务提交的两种方式：
  • 自动提交：
    • MySQL 就是自动提交的
    • 一条 DML (增删改)语句会自动提交一次事务
  • 手动提交：
    • 需要先开启事务，再提交
    • Oracle 数据库默认是手动提交事务
• 修改事务默认提交方式：
  • 查看事务的默认提交方式：select @@autocommit; – 1 代表自动提交，0 代表手动提交
  • 修改默认提交方式：set @@autocommit = 0;

2. 事务的四大特征

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
• 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
• 隔离性：多个事务之间，相互独立。
• 一致性：事务操作前后，数据总量不变。

3.事务的隔离级别

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

1. 存在的问题：

• 脏读：一个事务，读取到另一个事务中没有提交的数据。
• 不可重复读(虚读)：在同一个事务中，两次读取的数据不一样。
• 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

2. 隔离级别：

• read uncommitted：读未提交

  ​ 产生的问题：脏读，不可重复读，幻读

• read committed：读已提交 ( Oracle 默认 )

  ​ 产生的问题：不可重复读，幻读

• repeatable read：可重复读 ( MySQL 默认 )

  ​ 产生的问题：幻读

• serializable：串行化

  ​ 可以解决所有的问题

注意：

隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：

​ select @@tx_isolation;

数据库设置隔离级别：

​ set global transaction isolation level 级别字符串;

DCL

1.管理用户

1.添加用户：

• 语法：create user ‘用户名’@‘主机名’ idenified by ‘密码’;

2.删除用户：

• 语法：drop user ‘用户名’@‘主机名’;

3.修改用户密码：

• 语法：update user password = password(‘新密码’) where user = ‘用户名’;

  ​ set password for ‘用户名’@‘主机名’ = password(‘新密码’);

• mysql中忘记了root用户的密码？

  1. 以管理员身份打开cmd

  ​ – > net stop mysql – 停止mysql服务

  2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables /* 这里必须 my.ini 里有这条语句*/

  3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

  4. use mysql;

  5. update user set password = password(‘你的新密码’) where user = ‘root’;

  6. 关闭两个窗口

  7. 打开任务管理器，手动结束mysqld.exe 的进程

  8. 启动mysql服务

  9. 使用新密码登录。

4.查询用户：

• 切换到mysql数据库
  use myql;
• 查询user表
  select * from user;

• 通配符： % 表示可以在任意主机使用用户登录数据库

注意：5.7以上版本的 MySQL 做出了一点修正

set password for root@localhost = ‘123456’;

alter user ‘root’@‘localhost’ identified by ‘123456’;

注，有一些 MySQL 设置密码是用
set password for ‘root’@‘localhost’ = password(‘123456’);
MySQL 版本的问题，导致 password() 不可用。

2. 权限管理：

1.查询权限：

​ show grants for ‘用户名’@‘主机名’;

2.授予权限：

​ grant 权限列表[如：select,update,…] on 数据库名.表名 to ‘用户名’@‘主机名’;

grant all on *.* ‘用户名’@‘主机名’; – 给该用户授予所有权限

3. 撤销权限：

​ revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

：create user ‘用户名’@‘主机名’ idenified by ‘密码’;

2.删除用户：

• 语法：drop user ‘用户名’@‘主机名’;

3.修改用户密码：

• 语法：update user password = password(‘新密码’) where user = ‘用户名’;

  ​ set password for ‘用户名’@‘主机名’ = password(‘新密码’);

• mysql中忘记了root用户的密码？

  1. 以管理员身份打开cmd

  ​ – > net stop mysql – 停止mysql服务

  2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables /* 这里必须 my.ini 里有这条语句*/

  3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

  4. use mysql;

  5. update user set password = password(‘你的新密码’) where user = ‘root’;

  6. 关闭两个窗口

  7. 打开任务管理器，手动结束mysqld.exe 的进程

  8. 启动mysql服务

  9. 使用新密码登录。

4.查询用户：

• 切换到mysql数据库
  use myql;
• 查询user表
  select * from user;

• 通配符： % 表示可以在任意主机使用用户登录数据库

注意：5.7以上版本的 MySQL 做出了一点修正

set password for root@localhost = ‘123456’;

alter user ‘root’@‘localhost’ identified by ‘123456’;

注，有一些 MySQL 设置密码是用
set password for ‘root’@‘localhost’ = password(‘123456’);
MySQL 版本的问题，导致 password() 不可用。

2. 权限管理：

1.查询权限：

​ show grants for ‘用户名’@‘主机名’;

2.授予权限：

​ grant 权限列表[如：select,update,…] on 数据库名.表名 to ‘用户名’@‘主机名’;

grant all on *.* ‘用户名’@‘主机名’; – 给该用户授予所有权限

3. 撤销权限：

​ revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;