001-MySql笔记

最新推荐文章于 2024-07-25 16:06:56 发布

朱良浩

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量113

点赞数

分类专栏： Java后台文章标签： mysql sql 数据库数据库学习 mysql学习

本文链接：https://blog.csdn.net/zlhyy666666/article/details/115186159

版权

Java后台专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

1.服务启动和关闭
- 1.1 启动
- 1.2 停止
2.登录和退出MySQL
- 2.1登录
- 2.2 退出MySQL
3.MySQL的目录
- 3.1 安装目录
- 3.2 数据文件
- 3.3 三个字带数据库
4.SQL通用语法
- 4.1 什么是SQL
- 4.2 通用语法
5.MySQL四种语句
- 5.1 DDL 数据库定义语言
- 5.2 DML 数据库操作语言
- - 5.2.1 操作表中的数据
- 5.3 DQL 数据库查询语言
- - 5.3.1 作用于整个数据库
- 5.4 DCL 数据库控制语言
6.约束
- 6.1 分类
7.多表之间的关系
- 7.1 关系
- 7.2 实现
8.数据库设计的范式
9.数据库的备份和还原，数据库管理员DBA来操作的
- 1命令行
10.多表查询
- 10.1查询语法：
- - 10.1.1多表查询笛卡尔积(多表交集)
- 10.2多表查询的分类
- 10.3 多表查询练习
11.事务
- 11.1 手动提交事务
- 11.2 事务的特性
- 11.3 事务隔离的四个级别(了解)
- - 11.3.1 查询隔离级别 select @@tx_isolation;
  - 11.3.2 设置隔离级别 set global transaction isolation level 级别字符串;

1.服务启动和关闭

1.1 启动

net start mysql

1.2 停止

net stop mysql

2.登录和退出MySQL

2.1登录

1) 登录自己的

mysql -uroot -proot

2) 登录别人的

mysql -h127.0.0.1 -uroot -proot

3) 键值对形式登录

mysql --host=127.0.0.1 --user=root --password=root

2.2 退出MySQL

exit 或者 quit

3.MySQL的目录

3.1 安装目录

目录	作用
bin	二进制可执行文件,exe文件
data	数据,日志文件,数据文件
include	C语言的头文件
lib	mysql的jar包
share	错误文件
my.ini	配置文件 ,文件目录,数据目录,编码字符集

3.2 数据文件

C:\ProgramData\MySQL\MySQL Server 5.5\data

三种	对应
数据库	对应文件夹
表	对应文件
数据	对应文件中的内容

3.3 三个字带数据库

数据库	提示
mysql	核心数据库(不能动)
performance_schema	对性能提升操作的(不要动)
test	空数据库,用来随便玩

4.SQL通用语法

4.1 什么是SQL

Structured Query Language 结构化查询语言

其实就是定义了操作所有关系型数据库的规则，每一种数据库操作的方式存在不一样的地方，称为“方言”。

4.2 通用语法

sql语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
语句不区分大小写,关键字建议大写
三种注释
- 单行注释
  - 语句后面 – 空格后面加文字,–后面一定要有一个空格
  - ```
  show databases; -- 查询所有数据库
```
- 语句后面 #注释内容,#后面不用加空格
- ```
show databases; #查询所有数据库
```
- 多行注释

5.MySQL四种语句

5.1 DDL 数据库定义语言

Data Definition Language

5.1.1 操作数据库

1) Create创建

语法	作用
create database 数据库名;	创建数据库
create database if not exists 数据库名;	创建数据库前进行判断是否为空
create database 数据库名 character set 编码格式;	创建数据库时设置编码
create database if not exists 数据库名 character set 编码格式;	创建数据库前判断并设置编码

2) Retrieve查询

语法	作用
show databases;	查询所有数据库名称
show create database 数据库名称;	查询创建数据库的语法(创建语句,字符集等)

3) Update修改

语法	作用
alter database 数据库名 character set 字符集;	修改字符集

4) Delete删除

语法	作用
drop database 数据库名;	删除数据库
drop database if exists 数据库名;	删除数据库前判断

5) 使用数据库

语法	作用
select database();	查询当前正在使用的数据库名称
use 数据库名;	使用某个数据库

5.1.2 操作表

1) Create创建

创建表 create table 表名(
	列名1 数据类型1,
	列名2 数据类型2,
	...
	列名n 数据类型n
);
create table student(
	id int,
	name varchar(32),
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
);
=========================================
复制表create table 新表名 like 被复制的表名;
create table newstudent like student;
数据类型:
	int 整数类型; age int
	double 小数类型; score double(5,2)  小数一共有5位,小数点后面保留两位,所以最大是999.99
	date 日期;只包含年月日 yyyy-MM-dd
	datetime 日期;包含年月日时分秒 yyyy-MM-dd HH:mm:ss
	timestamp 时间戳;格式和datetime一样,如果不给这个字段赋值为null,则默认使用当前系统时间
	char 字符串类型; 能确定字符串长度.
	varchar 字符串类型; name varchar(20) name 最大长度是20个字符
	text 文本; 大型文本存储使用
	BINARY(M) 二进制类型;用来存储文件(一般不用)

2) Retrieve查询

语法	作用
show tables;	查询某个数据库中所有的表名称
show create table 表名;	查询表的创建语句
desc 表名;	查询(description)表结构

3) Update修改

语法	作用
alter table 表名 rename to 新表名;	修改表名
alter table 表名 character set 字符集;	修改表的字符集
alter table 表名 add 列名数据类型;	添加一列
alter table 表名 modify 列名新的数据类型;	修改列的类型
alter table 表名 change 列名新列名数据类型;	修改列名和类型
alter table 表名 drop 列名;	删除列

4) Delete删除

语法	作用
drop table 表名;	删除表
drop table if exists 表名;	删除表前确认

5.2 DML 数据库操作语言

Data Manipulation Language

5.2.1 操作表中的数据

1) 增 insert

列名和值名一一对应

如果不写列名, 默认是给所有列添加值

语法	作用
insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);	添加数据
insert into 表名 values(值1,值2,…值n);	给所有列添加数据

2) 删 delete

如果不加条件,默认删除表中所有数据 delete from 表名;

如果要删除表中所有数据,建议用truncate table 表名,因为delete from 表名;有多少条记录就会执行多少次删除操作.

条件语法: 比如 id=1; name=“张三”; age=23;

语法	作用
delete from 表名 where 条件;	删除数据
delete from 表名;	删除表中所有数据
truncate table 表名;	删除表,然后再创建一个一模一样的空表

3) 改 update

语法	作用
update 表名 set 列名1=值1,列名2=值2,… where 条件;	修改数据
update 表名 set 列名=值;	修改列中所有数据

5.3 DQL 数据库查询语言

Data Query Language

5.3.1 作用于整个数据库

1) 查询表的所有记录

select * from 表名

2) 语法

语法	作用
select	字段列表
from	表名列表
where	条件列表
group by	分组字段
order by	排序
limit	分页限定

3) 基础查询

查询	语句
多个字段的查询	select 字段1,字段2…字段n from 表名;
去除重复(distinct关键字)	select distinct 字段名 from 表名;
计算列	select 字段1,字段2,字段3,字段4+ifnull(字段3,0) from 表名;
起别名	select 字段1,字段2,字段3,字段4+ifnull(字段3,0) as 总分 from 表名;
	select 字段1,字段2,字段3,字段4+ifnull(字段3,0) 总分 from 表名; (不加as)
	select 字段1,字段2 别名,字段3 别名,字段4+ifnull(字段3,0) 总分 from 表名;

4) 条件查询 where 后面跟条件

运算符: >、<、<=、>=、=、<>

条件	语法	作用
BETWEEN 范围1 AND 范围2	SELECT * FROM student WHERE age BETWEEN 17 AND 23;	在一个范围之内,包头又包尾
IN(集合)	SELECT * FROM student WHERE age IN(18,22,30);	集合表示多个值,使用逗号隔开
LIKE ‘张%’	_代表一个字符 %代表多个字符	模糊查询张某某
IS NULL	SELECT * FROM student WHERE english IS NULL;	查询某一列为空的值
IS NOT NULL	SELECT * FROM student WHERE english IS NOT NULL;	查询某一列为不空的值
and 或 && 与		与
or 或 \|\| 或		或

5) 排序查询

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

第二排序条件只有当第一排序条件一样的时候才会去使用

排序方式 asc 默认的升序, desc 降序

排序方式	语法
升序(默认)	SELECT * FROM student ORDER BY age;
降序	SELECT * FROM student ORDER BY age DESC;

6) 聚合查询

将一列数据作为一个整体,进行纵向的计算,看班级平均分什么的

注意:所有的聚合函数,都是排除了非空的

函数	作用	语法
count	计算个数,一般选择主键列	SELECT COUNT(NAME) FROM student; – 计算name列的个数 SELECT COUNT(*) FROM student; – 只要这一列数据对应的那一行,有一个不为null,就算一条数据,
max	计算最大值	SELECT MAX(english) FROM student;
min	计算最小值	SELECT MIN(english) FROM student;
sum	计算和	SELECT SUM(english) FROM student;
avg	计算平均值	SELECT AVG(english) FROM student;

7) 分组查询

把相同特征数据作为一个整体,来统计整体信息

语法:group by 要分组的字段

where 和 having 的区分
where 分组之前限定,不满足则不参与分组
having分组之后进行限定,如果不满足结果,则不会被查询出来
where后面不可以跟聚合函数
having后面可以跟聚合函数

	按照性别分组,分别查询男,女同学的平均分
	SELECT sex,AVG(english) FROM student GROUP BY sex;
	按照性别分组,分别查询男,女同学的平均分,男女同学的人数
	SELECT sex,AVG(english),COUNT(id) FROM student GROUP BY sex;
	按照性别分组,分别查询男,女同学的平均分,男女同学的人数,分数低于70分的不参与分组
	SELECT sex,AVG(english),COUNT(id) FROM student WHERE english>70 GROUP BY sex;
	按照性别分组,分别查询男,女同学的平均分,男女同学的人数,分数低于70分的不参与分组,只显示id分组>2的,小于的就不显示了
	SELECT sex,AVG(english),COUNT(id) FROM student WHERE english>70 GROUP BY sex HAVING COUNT(id)>2;
	SELECT sex,AVG(english),COUNT(id) 人数 FROM student WHERE english>70 GROUP BY sex HAVING COUNT(人数)>2;

8) 分页查询

limit 开始的索引,每页查询的条数;

SELECT * FROM student LIMIT 0,3;
SELECT * FROM student LIMIT 3,3;
SELECT * FROM student LIMIT 6,3;
-- 公式: 开始的索引 = (当前页码 - 1) * 每页显示的条数

5.4 DCL 数据库控制语言

Data Control Language

5.4.1 管理用户

1) 添加用户

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

-- 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 12345
create user 'user1'@'localhost' identified by '12345';

-- 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '12345';

2) 删除用户

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

DROP USER 'zhangsan'@'localhost';

3) 修改用户密码

UPDATE USER SET PASSWORD = PASSWORD (‘新密码’) WHERE USER = ,户名’;

UPDATE USER SET PASSWORD= PASSWORD ('abc') WHERE USER = 'lisi',

或者

SET PASSWORD FOR ‘用户名’’@,‘主机名’ = PASSWORD (‘新密码’);

SET PASSWORD FOR 'root '@ 'localhost' = PASSWORD ( '123');

修改root用户的密码

停止mysql服务

net stop mysql停止mysq1服务

以管理员方式运行cmd启动mysql(使用无验证方式启动)

mysqld --skip-grant-tables

再打开一个新的cmd窗口(上一个窗口保留),直接敲回车就登录成功病了

USE mysql;
UPDATE USER SET PASSWORD= PASSWORD ('root') WHERE USER = 'root',

改完后关闭两个窗口,打开任务管理器

找到mysqld.exe 结束掉,就好了

4) 查询用户

-- 1.切换到mysql数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;

5.4.2 权限管理

1) 查询权限

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

SHOW GRANTS FOR 'lisi'@'loaclhost';
SHOW GRANTS FOR 'lisi'@'%';

2) 授予权限

GRANT 权限 1, 权限 2… ON 数据库名**.**表名 TO ‘用户名’@‘主机名’;

给 user1 用户分配对 test 这个数据库操作的权限:创建表，修改表，插入记录，更新记录，查询

grant create,alter,insert,update,select on test.* to 'user1'@'localhost';

给 user2 用户分配所有权限，对所有数据库的所有表

grant all on *.* to 'user2'@'%';

3) 撤销权限

REVOKE 权限 1, 权限 2… ON 数据库**.**表名 revoke all on test.* from ‘user1’@‘localhost’; ‘用户名’@‘主机名’;

revoke all on test.* from 'user1'@'localhost';
revoke all on test.* from 'user1'@'% ';

6.约束

对表中的数据进行限定、保证数据的正确性、有效性、完整性

6.1 分类

6.1.1 主键约束 primary key

含义：非空且唯一

1) 创建带有主键的表

CREATE TABLE stu(
	id INT PRIMARY KEY, -- 给id添加主键
	NAME VARCHAR(20)
);

2) 创建表之后添加主键

ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY;

3) 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

4) 创建带有自增长的主键的表 AUTO_INCREMENT

自动增长的时候数据只跟上一条数据有关系

CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加自增长主键
	NAME VARCHAR(20)
);

5) 删除自动增长，其实是用了更改

ALTER TBALE 表名 MODIFY 字段名 数据类型;

6.1.2 非空约束 NOT NULL

含义: 值不能为空

1) 创建时候设置非空约束：在字段后面加上 NOT NULL

CREATE TABLE stu(
	字段名 数据类型 NOT NULL
);

2) 把某个字段设置为非空

ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

3) 删除非空约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE stu MODIFY NAME VARCHAR(20);

6.1.3 唯一约束 unique

含义: 值不能重复

1) 创建时候设置唯一：在字段后面加上 UNIQUE

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

2) 把某个字段设置为唯一约束 UNIQUE

ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

3) 删除唯一约束 DROP INDEX

ALTER TABLE 表名 DROP INDEX 字段名;
ALTER TABLE stu DROP INDEX phone_number;

6.1.4 外键约束 foreign key

含义: 关联外部表

1) 创建表时，添加外键

create table 表名(
	...
	外键列
	constraint 外键名称 foreign key (要关联的本表列名) references 外表名（外表列名）
);
============示例============
-- 部门表
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20), 
	age INT,
	dep_id INT, -- 外键对应主表的主键 
	CONSTRAINT waijian_name FOREIGN KEY (dep_id) REFERENCES department(id)
) 
============================

2) 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 自己设置的外键名;
ALTER TABLE employee DROP FOREIGN KEY waijian_name;

3) 添加外键

ALTER TABLE employee ADD constraint 外键名称 foreign key (要关联的本表列名) REFERENCES 外表名（外表列名）;
ALTER TABLE employee ADD CONSTRAINT waijian_name FOREIGN KEY (dep_id) REFERENCES department(id);

4) 级联操作

ON DELETE CASCADE 级联删除
ON UPDATE CASCADE 级联更新
级联更新 ON UPDATE CASCADE:
=========================
ALTER TABLE employee ADD constraint 外键名称 foreign key (要关联的本表列名) references 外表名（外表列名） ON UPDATE CASCADE;
ALTER TABLE employee ADD CONSTRAINT waijian_name FOREIGN KEY (dep_id) REFERENCES department(id)  ON UPDATE CASCADE;

5) 级联更新和级联删除

删除外键的一个，关联的自动被删除掉

ALTER TABLE employee ADD constraint 外键名称 foreign key (要关联的本表列名) references 外表名（外表列名） ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE employee ADD CONSTRAINT waijian_name FOREIGN KEY (dep_id) REFERENCES department(id)  ON UPDATE CASCADE ON DELETE CASCADE;

7.多表之间的关系

7.1 关系

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

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

多对多：学生和课程，一个学生可以选择多门课程，一个课程可以被多个部门选择

7.2 实现

一对多：部门和员工，实现方式：在多的一方建立外键，指向一的一方的主键

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

一对一：人和身份证，实现方式：在任意一方添加唯一外键指向另一方的主键。

旅游线路分类
旅游线路
用户

分类  -----  线路   是一对多
线路  -----  用户   是多对多



-- 创建旅游分类表
CREATE TABLE tab_category (
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

-- 创建线路表
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

-- 创建用户表
CREATE TABLE tab_user (
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) UNIQUE NOT NULL,
	PASSWORD VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT '男',
	telephone VARCHAR(11),
	email VARCHAR(100)
);

-- 用户和线路表之间的多对应，中间表
CREATE TABLE tab_favorite (
	rid INT,
	DATE DATETIME,
	uid INT,
	-- 创建复合主键
	PRIMARY KEY(rid,uid),
	-- 创建
	FOREIGN KEY(rid) REFERENCES tab_route(rid),
	FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

8.数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。

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

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

分类：

  第一范式（1NF）：每一列都是不可分割的原子数据项 

• 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部门函数依赖）
  概念：
  1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
            例如：学号-->姓名。  （学号，课程名称） --> 分数
  2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
            例如：（学号，课程名称） --> 分数
  3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
            例如：（学号，课程名称） -- > 姓名
  4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
            例如：学号-->系名，系名-->系主任
  5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
            例如：该表中码为：（学号，课程名称）
            * 主属性：码属性组中的所有属性
            * 非主属性：除过码属性组的属性
  
• 第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

9.数据库的备份和还原，数据库管理员DBA来操作的

1命令行

语法：

备份：mysqldump -u用户名 -p密码 要保存的数据库 > 保存的路径
还原：登录数据库
   创建数据库
   使用数据库
   source 文件路径;  -- 就能恢复了

10.多表查询

10.1查询语法：

10.1.1多表查询笛卡尔积(多表交集)

SELECT * FROM emp,dept;

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

select 
  列表名称
from
   表名列表
where ...
======================================
-- 创建部门表
CREATE TABLE dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

-- 插入部门
INSERT INTO dept (NAME) VALUES('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1), -- 性别
  salary DOUBLE, -- 工资
  join_date DATE, -- 入职日期
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键
);
-- 插入员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('张三','男',3000,'2012-03-10',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('李四','女',5000,'2013-07-11',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('王五','女',3600,'2016-04-16',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('赵六','男',2000,'2011-03-15',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('郑七','男',2800,'2010-10-14',3);


-- 多表查询 笛卡尔积
SELECT * FROM emp,dept;
======================================

10.2多表查询的分类

10.2.1 内连接查询

从哪些表中查询数据

条件是什么

查询哪些字段

1) 隐式内连接

使用where条件消除无用数据

前提是 由部门dept  和员工emp  两张表
-- where进行消除无用数据
SELECT * FROM emp,dept WHERE emp.`dept_id`=dept.`id`;
-- 筛选出来
SELECT emp.`name`,emp.`gender`,emp.`salary`,dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 设置表别名

SELECT
  t1.name, --员工表姓名
  t1.`gender`, -- 员工表性别
  t2.`name` -- 部门表名
FROM
  emp t1, -- 员工表
  dept t2 -- 部门表
WHERE
  t1.`dept_id`=t2.`id`;

2) 显式内连接

语法： select 字段列表 from 表1 inner join 表名2 on 条件;
    select 字段列表 from 表1 join 表名2 on 条件;
inner是可选的

示例：
  -- 显式内连接
  SELECT * FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id`;
  SELECT * FROM emp JOIN dept ON emp.`dept_id`=dept.`id`;

10.2.2 外连接查询

左外连接

select 字段列表 from 表1 LEFT [outer] join 表2 on 条件;
查询左表所有数据及其交际部分

右外连接

select 字段列表 from 表1 RIGHT [outer] join 表2 on 条件;
查询右表所有数据及其交际部分

10.2.3 子查询, 查询嵌套查询

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

情况一: 单行单列的
  
-- 查询最高工资
SELECT MAX(salary) FROM emp;

-- 根据最高工资查询是哪个员工 子查询
SELECT * FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);

-- 查询员工工资小于平均工资的
SELECT * FROM emp WHERE emp.`salary`<(SELECT AVG(salary) FROM emp);

情况二: 多行单列的

-- 查询部门名字对应的部门ID
SELECT dept.`id` FROM dept WHERE dept.`name`="开发部" OR dept.`name`="市场部";

-- 根据多个部门id查询所有员工
SELECT emp.`dept_id`,emp.`name` FROM emp WHERE emp.`dept_id` IN(SELECT dept.`id` FROM dept WHERE dept.`name`="开发部" OR dept.`name`="市场部");

情况三: 多行多列的
  如果是多行多列的,子查询可以作为一张虚拟表来查询
  
-- 查询入职时间大于 2011-11-11 的员工信息
SELECT * FROM emp WHERE emp.`join_date`>'2011-11-11';

-- 查询入职时间大于 2011-11-11 的员工信息 和 部门信息
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id=t2.dept_id;


也可以用隐式内连接查询

-- 内连接方式查询入职时间大于 2011-11-11 的员工信息 和 部门信息

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id`=t2.`id` AND t1.`join_date` > '2011-11-11';

10.3 多表查询练习

-- 部门表
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), 
(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.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
  -- emp表 员工编号 员工姓名 工资, job表 职务名称,职务描述
  -- 查询条件emp.job_id = job.id
SELECT
  t1.'id',t1.'ename',t1.'salary',t2.'jname',t2.'description'
FROM
  emp t1,job t2
WHERE
  t1.'job_id' = t2.'id';

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
  t1.id,-- 员工编号
  t1.ename,-- 员工名字
  t1.salary,-- 工资
  t2.jname,-- 职务名称
  t2.description,-- 职务描述
  t3.dname,-- 部门名称
  t3.loc -- 部门位置
  
FROM
  emp t1,
  job t2,
  dept t3 
WHERE
  t1.job_id = t2.id 
  AND t1.dept_id = t3.id;
-- 3.查询员工姓名，工资，工资等级
SELECT
  t1.ename,
  t2.grade 
FROM
  emp t1,
  salarygrade t2 
WHERE
  t1.salary BETWEEN t2.losalary 
  AND t2.hisalary;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
  t1.ename,
  t1.salary,
  t2.jname,
  t2.description,
  t3.dname,
  t3.loc,
  t4.grade 
FROM
  emp t1,
  job t2,
  dept t3,
  salarygrade t4 
WHERE
  t1.job_id = t2.id 
  AND t1.dept_id = t3.id 
  AND t1.salary BETWEEN t4.losalary 
  AND t4.hisalary;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT 
  t1.id,
  t1.dname,
  t1.loc,
  t2.`人数`
FROM
  dept t1,
  (SELECT dept_id, COUNT( id ) 人数 FROM emp GROUP BY dept_id ) t2 -- 部门人数
WHERE t1.id  = t2.dept_id; 
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT
  t1.ename,
  t1.mgr,
  t2.id,
  t2.ename
FROM
  emp t1,
  emp t2
WHERE
  t1.mgr= t2.id;  -- 这样查询出来没有  null的
  
  -- 利用外连接查询,查询出没有 mar的
SELECT
  t1.ename,
  t1.mgr,
  t2.id,
  t2.ename
FROM
  emp t1 
LEFT JOIN emp t2
ON  t1.mgr = t2.id;

11.事务

开启事务: start transaction

提交: commit

回滚: rollback

在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。事务执行是一个整体，所有的 SQL 语句都必须执行成功。

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：张三账号-500 李四账号+500



-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。

使用事务后

-- 开启事务
START TRANSACTION;

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

-- 没问题 提交事务
COMMIT;

-- 有问题 回滚事务
ROLLBACK;

11.1 手动提交事务

MYSQL 中可以有两种方式进行事务的操作：

手动提交事务
自动提交事务默认就是自动提交事务

开启事务 start transaction;

提交事务 commit;

回滚事务 rollback;

修改事务的默认提交方式

*查看事务的默认提交方式:
SELECT @@autocommit,
–1代表自动提交。0代表手动提交

*修改默认提交方式: set @@autocommit =0;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U7OlIVtj-1616587691773)(/Users/zhulianghao/Library/Application Support/typora-user-images/截屏2021-02-02 上午11.31.25.png)]

11.2 事务的特性

原子性（Atomicity）每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。

一致性（Consistency）事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是 2000，转账后 2 个人总金额也是 2000

隔离性（Isolation）事务与事务之间不应该相互影响，执行时保持隔离的状态。

持久性（Durability）一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

11.3 事务隔离的四个级别(了解)

脏读一个事务读取到了另一个事务中尚未提交的数据

不可重复读 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update 时引发的问题

幻读一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题

11.3.1 查询隔离级别 select @@tx_isolation;

11.3.2 设置隔离级别 set global transaction isolation level 级别字符串;

朱良浩

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
001-MySql笔记

文章目录1.服务启动和关闭1.1 启动1.2 停止2.登录和退出MySQL2.1登录1) 登录自己的2) 登录别人的3) 键值对形式登录2.2 退出MySQL3.MySQL的目录3.1 安装目录3.2 数据文件3.3 三个字带数据库4.SQL通用语法4.1 什么是SQL4.2 通用语法5.MySQL四种语句5.1 DDL 数据库定义语言5.1.1 操作数据库1) Create创建2) Retrieve查询3) Update修改4) Delete删除5) 使用数据库5.1.2 操作表1) Create创建2)
复制链接

扫一扫