mysql知识点

A7Feather

已于 2023-02-01 13:20:34 修改

阅读量59

点赞数

文章标签： mysql 数据库 sql

于 2022-10-11 19:20:12 首次发布

本文链接：https://blog.csdn.net/m0_61852712/article/details/127269796

版权

一、关系型数据库

· 概念

建立在关系型模型基础上的数据库
由多张能相互连接的二维表组成的数据库

· 优点

使用表结构，格式一致，易于维护
使用sql语言操作，可用于复杂查询
数据存储在磁盘中，安全

###二、sql

· 简介

结构化查询语言 Structured Query Language ，简称SQL
定义所有关系型数据库的统一标准

· 语法

可以单行或多行书写，以分号结尾
不区分大小写，建议大写
注释：

单行：-- 或 # (–和注释内容之间要有空格)

多行：/* */

show database; -- 查询数据库

· 分类

DDL (Data Definition Language)数据定义语言，定义数据库对象：数据库，表，列等
DML(Data Manipulation Language)数据操作语言，用来对数据表中的数据进行增删改
DQL(Data Query Language)数据查询语言，用来查询数据库中记表的记录(数据)
DCL(Data Control Language)数据控制语言，定义数据库的访问权限和安全级别及创建用户

· DDL-操作数据库

######· 查询数据库

show database;

######· 创建数据库

create database db1;-- 创建名为db1的数据库

######· 删除数据库

drop database db2;-- 删除db2数据库

######· 使用数据库

use db1;-- 使用数据库

· DDL-操作表

######· 查询表

show tables;

desc 表名称;-- 查询表的结构信息

######· 创建表

create table 表名 (
	字段名1 数据类型1,
	字段名2 数据类型2,
	...
	字段名n 数据类型n
);

######· 删除表

drop table 表名;
drop table if exists 表名;-- 删除时判断表是否存在

· 修改表

alter table 表名 rename to 新的表名;-- 修改表名

alter table 表名 add 列名 数据类型;-- 修改列名

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

alter table 表名 change 列名 新列名  新数据类型;-- 修改列名和数据类型
实例：
ALTER TABLE emp CHANGE jondate joindate DATE NOT NULL;

alter table 表名 drop 列名;-- 删除列

· DML-增删改数据

· 添加数据

insert into 表名(列名1，列名2，...) values(值1，值2，...);-- 给指定列添加数据

insert into 表名 values(值1，值2,...);-- 给全部列添加数据

insert into 表名(列名1，列名2,...) values(值1，值2,...),(值1，值2,...),(值1，值2,...)...;
insert into 表名 values(值1，值2,...),(值1，值2,...),(值1，值2,...);-- 批量添加数据

批量添加数据的实例：
INSERT INTO stu2(
id,NAME,age,sex,address,math,english,hire_date
) VALUES
(1,'杨志凌',20,'男','重庆',50,66,'2001-9-9'),
(2,'张天志',21,'男','北京',52,70,'1999-9-4'),
(3,'周杰伦',22,'男','上海',53,61,'1998-9-9'),
(4,'陈奕迅',23,'男','广东',54,62,'2001-1-1'),
(5,'林俊杰',24,'男','江苏',55,63,'1889-3-9'),
(6,'迪丽热巴',2,'女','新疆',5,64,'2019-1-1'),
(7,'欧阳娜娜',5,'女','西藏',6,65,'2020-1-1'),
(8,'兰博基尼',200,'无','海外',100,69,'1800-1-1'),
(9,'玛莎拉蒂',100,'无','海外',50,68,'1900-1-1'),
(10,'爱在西元前',20,'无','中国',20,62,'2000-1-1');

· 修改数据

update 表名 set 列名1=值1,列名2=值2,...[where 条件];-- 修改语句中不加条件则所有语句都修改

· 删除数据

delete from 表名 [where 条件];-- 删除语句不加条件则所有数据都删除

· DQL查询数据

· 语法

select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组后字段 order by 排序字段 limit 分页限定

· 基础查询

select 字段列表 from 表名;
实例：SELECT NAME,age FROM stu2;
select * from 表名;-- 查询所哟数据，大部分公司不让用'*'号

select distinct 字段列表 from 表名;-- 去除重复数据

as: as-- 起别名，可以省略
实例：SELECT NAME,math AS 数学成绩,english AS 英语成绩 FROM stu2;

· 条件查询(where)

条件	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>或!=	不等于
between…and…	在某个范围之间
in(…)	多选一
like 占位符	模糊查询 _单个任意字符 %多个任意字符
is null	是null
is not null	不是null
and 或 &&	并且
or 或 \|\|	或者
not 或！	非，不是

实例：
SELECT * FROM stu2 WHERE age>20;

SELECT * FROM stu2 WHERE age>=20;

SELECT * FROM stu2 WHERE age>=20 && age<=30;
SELECT * FROM stu2 WHERE age>=20 AND age<=30;

SELECT * FROM stu2 WHERE hire_date BETWEEN '1999-1-1' AND '2020-1-1';

SELECT * FROM stu2 WHERE age=20;

SELECT * FROM stu2 WHERE age<>20;
SELECT * FROM stu2 WHERE age!=20;

SELECT * FROM stu2 WHERE age=20 OR age=21 OR age=22;
SELECT * FROM stu2 WHERE age IN (20,21,22);

-- null值的比较不能使用= ,!= 比较，需要使用 is , is not
SELECT * FROM stu2 WHERE english IS NOT NULL;

SELECT * FROM stu2 WHERE NAME LIKE '杨%';-- 查询姓'杨'的同学

SELECT * FROM stu2 WHERE NAME LIKE '_志%';-- 查询第二个子为'志'的同学

SELECT * FROM stu2 WHERE NAME LIKE '%志%';-- 查询名字中包含'志'的同学

· 排序查询(order by)

语法：
select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段2 [排序方式2]...;
排序方式：
1. ASC 升序排序
2. DESC 降序排序
注意：如果有多个排序条件，当前面的条件之一样时，才会执行第二条件进行排序

实例：
-- 排序方式默认ASC升序排序
SELECT * FROM stu2 ORDER BY age;
SELECT * FROM stu2 ORDER BY age ASC;

SELECT * FROM stu2 ORDER BY math DESC;

-- 按照数学成绩降序排列，如果数学成绩一样则按照英语成绩降序排序
SELECT * FROM stu2 ORDER BY math DESC , english DESC;

· 聚合函数

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

函数名功能
count(列名) 统计数量(一般不选含null的列)
max(列名) 最大值
min(列名) 最小值
num(列名) 求和
avg(列名) 平均值

函数名	功能
count(列名)	统计数量(一般不选含null的列)
max(列名)	最大值
min(列名)	最小值
num(列名)	求和
avg(列名)	平均值

聚合函数语法：

select 聚合函数名(列名) from 表;-- null不能参与聚合函数运算

实例：
-- 统计有多少个id的数量
SELECT COUNT(id) FROM stu2;
SELECT COUNT(*) FROM stu2;

SELECT MAX(math) FROM stu2;
SELECT MIN(math) FROM stu2;

SELECT SUM(math) FROM stu2;

SELECT AVG(math) FROM stu2;

· 分组查询(group by)

语法：
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];

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

实例：
-- 正确查询
SELECT sex,AVG(math) FROM stu2 GROUP BY sex;
SELECT sex,AVG(math),COUNT(*) FROM stu2 GROUP BY sex;
-- 把数学成绩大于50的同学进行分组查询
SELECT sex,AVG(math),COUNT(*) FROM stu2 WHERE math>50 GROUP BY sex;
-- 分组之后统计人数要大于2
SELECT sex,AVG(math),COUNT(*) FROM stu2 WHERE math>50 GROUP BY sex HAVING COUNT(*)>2;

-- id为无意义字段(不是聚合函数或分组字段)，加上id会报错
SELECT id,sex,AVG(math) FROM stu2 GROUP BY sex;

where和having 的区别：
1. 执行时机不一样，where是分组之前限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤；
2. 可判断的条件不一样：where不能对聚合函数进行判断，而having可以；

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

· 分页查询(limit)

语法：
select 字段列表 from 表名 limit 起始索引,查询条数目;-- 起始索引从0开始

计算公式：起始索引=(当前页码-1)*(每页显示条数)

实例：
-- 每页显示3条数据
SELECT * FROM stu2 LIMIT 0,3;-- 第一页
SELECT * FROM stu2 LIMIT 3,3;-- 第二页
SELECT * FROM stu2 LIMIT 6,3;-- 第三页
SELECT * FROM stu2 LIMIT 9,3;-- 第四页

注意：
1. 分页查询是mysql的方言；
2. oracle分页查询使用rownumber
3. sql server分页查询使用top

三、数据类型

#####· 表格

分类	数据类型	大小	描述
数值类型	tinyint	1byte	小整数值
	smallint	2bytes	大整数值
	mediumint	3bytes	大整数值
	int/integer	4bytes	大整数值
	bigint	8bytes	极大整数值
	float	4bytes	单精度浮点数
	double	8bytes	双精度浮点数
	decimal		小数值
日期和时间类型	date	3	日期值
	time	3	时间值或持续时间
	year	1	年份值
	datetime	8	混合日期和时间值
	timestamp	4	混合日期和时间值、时间戳
字符串类型	char	0-255bytes	定长字符串
	varchar	0-65535bytes	变长字符串
	tinyblob	0-255bytes	不超过255个字符的二进制字符串
	tinytext	0-255bytes	短文本字符串
	blob	0-65 535bytes	二进制形式长文本数据
	text	0-65 535bytes	长文本数据
	mediumblob	0-16 777 215bytes	二进制形式的中等长度文本数据
	mediumtext	0-16 777 215bytes	中等长度文本数据
	longblob	0-4 294 967 295bytes	二进制形式的极大文本数据
	longtext	0-4 294 967 295bytes	极大文本

· 注释

double(总长度,小数点后保留的位数)
定长字符串和变长字符串的区别：

用char(10)存储’张三’，占用10个字符空间；存储的性能高；浪费空间；

用varchar(10)存储’张三’，占用2个字符空间；存储性能低；节约空间；

四、图形化客户端工具

· Navicat for MySQL

管理和开发MySQL或MariaDB的理想解决方案
为数据库管理、开发和维护提供了直观的图形界面
网址：http://www.navicat.com.cn

五、约束

· 概念

约束是作用于表中列上的规则，用于限制加入表中的数据；
约束的存在保证了数据库中数据的正确性、有效性和完整性；

· 分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	not null
唯一约束	保证列中所有数据各不相同	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
检查约束	保证列中的值满足某一条件	check
默认约束	保存数据时，未指定值则采用默认值	default
外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	foreign key

注意：mysql不支持检查约束

· 添加约束

实例：
1. 创建表时添加约束
CREATE TABLE emp(
id INT PRIMARY KEY,-- 员工ID，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
jondate DATE NOT NULL,-- 入职日期，非空
salary DOUBLE(7,2) NOT NULL,-- 工资，非空
bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);
2. 创建表之后添加约束
alter table 表名 modify 字段名 数据类型 not null;
实例：
ALTER TABLE emp MODIFY id INT AUTO_INCREMENT;

· 删除约束

alter table 表名 modify 字段名 数据类型;
实例：
ALTER TABLE emp MODIFY id INT;

· 外键约束

· 概念

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性；

· 添加约束

1. 创建表时添加约束
create table 表名(
	列名 数据类型,
	...
	[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);
实例：
CREATE TABLE emp2(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加外键
CONSTRAINT fk_emp2_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

2. 建完表后添加约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称)
实例：
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept FOREIGN KEY(dep_id) REFERENCES dept(id);

######· 删除约束

alter table 表名 drop foreign key 外键名称;
实例：
ALTER TABLE emp2 DROP FOREIGN KEY fk_emp2_dept;

六、数据库设计

· 软件研发步骤

需求分析–>设计–>编码–>测试–>安装部署

· 数据库设计概念

根据业务系统的具体需求，结合DBMS，构造最优数据存储模型
建立数据库表结构以及表与表之间的关联关系的过程
有哪些表？表里有哪些字段？表与表之间有哪些关系？

· 数据库设计步骤

需求分析(有哪些数据？数据有哪些属性？数据与属性的特点是什么？)
逻辑分析(通过ER图对数据库进行逻辑建模，不需要考虑所选的数据库管理系统)
物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
维护设计(1.对新的需求进行建表；2.表优化)

· 表关系

· 一对一

例如：用户表和用户详情表

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

实现方式：在任意一方加入外键，关联另一方主键，并设置外键为唯一(unique)

· 一对多(多对一)

例如：部门和员工

一个部门对应多个员工，多个员工对应一个部门

实现方式：在多的一方建立外键关联一的一方主键

· 多对多

例如：商品和订单

一个商品对应多个订单，一个订单包含多个商品

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

实例：
-- 订单表
CREATE TABLE tb_order(
id INT PRIMARY KEY AUTO_INCREMENT,
payment DOUBLE(10,2),
payment_type TINYINT,
STATUS TINYINT
);

DESC tb_order;

-- 商品表
CREATE TABLE tb_goods(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
price DOUBLE(10,2)
);

DESC tb_goods;

-- 订单商品中间表
CREATE TABLE tb_order_goods(
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
goods_id INT,
COUNT INT
);

DESC tb_order_goods;

-- 建完表后添加外键
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);

七、数据库设计案例

八、多表查询

· 笛卡尔积

取A，B集合所有组合情况

· 多表查询

实例：
SELECT * FROM emp3,dept3;

· 连接查询

内连接

相当于查询A，B交集数据

语法：
-- 隐式内连接
select 字段列表 from 表1，表2... where 条件;
实例：
-- 查询emp3和dept3表中当emp3.dep_id=dept3.id的数据
SELECT * FROM emp3,dept3 WHERE emp3.`dept3_id`=dept3.`id`;
-- 查询emp3的name，joindate，dept3表的dname
SELECT emp3.`ename`,emp3.`joindate`,dept3.`dname` FROM emp3,dept3 WHERE emp3.`dept3_id`= dept3.`id`; 
-- 给表起别名
SELECT t1.`ename`,t1.`joindate`,t2.`dname` FROM emp3 t1,dept3 t2 WHERE t1.`dept3_id`= t2.`id`;

-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;
实例：
SELECT * FROM emp3 INNER JOIN dept3 ON emp3.`dept3_id` = dept3.`id`;
SELECT * FROM emp3 JOIN dept3 ON emp3.`dept3_id` = dept3.`id`;

外连接

左外连接：相当于查询A表所有数据和交集部分数据

select 字段列表 from 表1 left [outer] join 表2 on 条件;
实例：
-- 查询emp3表所有数据和对应的部门信息
SELECT * FROM emp3 LEFT JOIN dept3 ON emp3.`dept3_id` = dept3.`id`;

右外连接：相当于查询B表所有数据和交集部分数据

select 字段列表 from 表1 right [outer] join 表2 on 条件;
实例：
-- 查询dept3表所有数据和对应的员工信息
SELECT * FROM emp3 RIGHT JOIN dept3 ON emp3.`dept3_id` = dept3.`id`;

· 子查询

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

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

· 单行单列：作为条件值，使用 = , != , > ,< 等进行判断

语法：
select 字段列名 from 表 where 字段名 = (子查询);

实例：
-- 查询工资高于猪八戒的员工信息
SELECT * FROM emp3;
-- 1. 查询猪八戒的工资
SELECT salary FROM emp3 WHERE ename = '猪八戒';
-- 2.查询工资高于猪八戒的员工信息
SELECT * FROM emp3 WHERE salary > 30000;
-- 3. 将两个过程结合起来形成嵌套查询
SELECT * FROM emp3 WHERE salary > (SELECT salary FROM emp3 WHERE ename = '猪八戒');

· 多行单列：作为条件值，使用in等关键字进行条件判断

语法：
select 字段列名 from 表 where 字段名 in (子查询);
实例：
-- 查询学工部和教研部的所有员工信息
-- 1. 查询财务部或教研部的员工信息
SELECT id FROM dept3 WHERE dname = '学工部' OR dname = '教研部';
-- 2. 查询dept3_id等于20/10的员工信息
SELECT * FROM emp3 WHERE dept3_id IN (10,20);
-- 将上两个步骤结合
SELECT * FROM emp3 WHERE dept3_id IN (SELECT id FROM dept3 WHERE dname = '学工部' OR dname = '教研部');

· 多行多列：作为虚拟表

语法：
select 字段列表 from (子查询) where 条件;
实例：
-- 查询入职日期是’ 2002-2-2 ’之后的员工信息和部门信息
-- 1. 查询入职日期是’2002-2-2’之后的员工信息
SELECT * FROM emp3 WHERE joindate > '2002-2-2';
-- 2. 查询员工信息和部门信息
SELECT * FROM emp3,dept3 WHERE emp3.`dept3_id` = dept3.`id`;
-- 将上两个步骤结合
SELECT * FROM (SELECT * FROM emp3 WHERE joindate > '2002-2-2') t1,dept3 WHERE t1.`dept3_id` = dept3.`id`;
-- 注意这里要给子查询中的表起别名，否则报错：Every derived table must have its own alias
错误写法：SELECT * FROM (SELECT * FROM emp3 WHERE joindate > '2002-2-2') ,dept3 WHERE emp3.`dept3_id` = dept3.`id`;

#####· 案例分析

建立表并添加数据
CREATE TABLE dept3 (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept3(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job3 (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  DESCRIPTION VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job3 (id, jname, DESCRIPTION) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp3 (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job3_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept3_id INT, -- 所在部门编号
  CONSTRAINT emp3_jobid_ref_job3_id_fk FOREIGN KEY (job3_id) REFERENCES job3 (id),
  CONSTRAINT emp3_deptid_ref_dept3_id_fk FOREIGN KEY (dept3_id) REFERENCES dept3 (id)
);

-- 添加员工
INSERT INTO emp3(id,ename,job3_id,mgr,joindate,salary,bonus,dept3_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 salarygrade3 (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade3(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

-- 需求：
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT * FROM emp3;
SELECT * FROM job3;
/*
	分析：
	1. 员工编号、姓名、工资的信息在emp3表中
	2、职务名称、职务描述的信息在job3表中
	3、job3职务表和emp3员工表是一对多的关系 emp3.job3_id=job3.id
*/
-- 隐式内连接
SELECT emp3.`id`,emp3.`ename`,emp3.`salary`,job3.`jname`,job3.`description` FROM emp3,job3 WHERE emp3.`job3_id` = job3.`id`;
-- 显示内连接
SELECT emp3.`id`,emp3.`ename`,emp3.`salary`,job3.`jname`,job3.`description` FROM emp3 INNER JOIN job3 ON emp3.`job3_id` = job3.`id`;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
	分析：
	1. 员工编号、姓名、工资的信息在emp3表中
	2、职务名称、职务描述的信息在job3表中
	3、job3职务表和emp3员工表是一对多的关系 emp3.job3_id=job3.id
	4、部门名称和部门位置来自dept3表
	5、dept3和emp3是一对多的关系，dept3.id = emp3.dept3_id
*/
-- 隐式内连接
SELECT emp3.`id`, emp3.`ename`, emp3.`salary`, job3.`jname`, job3.`description`, dept3.`dname`, dept3.`loc` FROM emp3,job3,dept3 WHERE emp3.`job3_id` = job3.`id` AND emp3.`dept3_id` = dept3.`id`;
-- 显示内连接
SELECT emp3.`id`,emp3.`ename`,emp3.`salary`,job3.`jname`,job3.`description`,dept3.`dname`,dept3.`loc` FROM emp3 INNER JOIN job3 ON emp3.`job3_id` = job3.`id` INNER JOIN dept3 ON emp3.`dept3_id` = dept3.`id`;
   
-- 3.查询员工姓名，工资，工资等级
SELECT * FROM salarygrade3;
/*
	分析：
	1. 员工姓名、工资的信息在emp3表中
	2、工资等级信息在salarygrade3表中
	3、emp3.salary >= salarygrade.losalary and emp.salery <= salarygrade3.hisalary
*/
SELECT emp3.`ename`,emp3.`salary`,t3.`grade` FROM emp3,salarygrade3 t3 WHERE emp3.`salary` >= t3.`losalary` AND emp3.`salary` <= t3.`hisalary`;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
	分析：
	1. 员工编号、姓名、工资的信息在emp3表中
	2、职务名称、职务描述的信息在job3表中
	3、job3职务表和emp3员工表是一对多的关系 emp3.job3_id=job3.id
	4、部门名称和部门位置来自dept3表
	5、dept3和emp3是一对多的关系，dept3.id = emp3.dept3_id
	6、工资等级信息在salarygrade3表中
	7、emp3.salary >= salarygrade.losalary and emp.salery <= salarygrade3.hisalary
*/
-- 隐式内连接
SELECT emp3.`id`, emp3.`ename`, emp3.`salary`, job3.`jname`, job3.`description`, dept3.`dname`, dept3.`loc`,t3.`grade` FROM emp3,job3,dept3,salarygrade3 t3 WHERE emp3.`job3_id` = job3.`id` AND emp3.`dept3_id` = dept3.`id` AND emp3.`salary` >= t3.`losalary` AND emp3.`salary` <= t3.`hisalary`;
-- 显示内连接
SELECT emp3.`id`, emp3.`ename`, emp3.`salary`, job3.`jname`, job3.`description`, dept3.`dname`, dept3.`loc`, t3.`grade` FROM emp3 INNER JOIN job3 ON emp3.`job3_id` = job3.`id` INNER JOIN dept3 ON emp3.`dept3_id` = dept3.`id`INNER JOIN salarygrade3 t3 ON emp3.`salary` >= t3.`losalary` AND emp3.`salary` <= t3.`hisalary`;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
	分析：
	1、部门编号、部门名称、部门位置信息在dept3表
	2、部门人数：在emp3表中按照dept3_id进行分组，然后count(*)统计数量
	3、使用子查询让部门表盒分组后的表进行内连接
*/
SELECT * FROM dept3;

SELECT  dept3_id,COUNT(*) FROM emp3 GROUP BY dept3_id;

SELECT dept3.`dname`,dept3.`id`,dept3.`loc`,t3.count FROM dept3,(SELECT  dept3_id,COUNT(*) COUNT FROM emp3 GROUP BY dept3_id) t3 WHERE dept3.`id` = t3.dept3_id;

· 事务

· 简介

数据库的事务是一种机制，一个操作序列，包含了一组数据库操作命令
事务把所有命令作为一个整体一起向系统提交或撤销操作请求，即这一数据库命令要么同时成功，要么同时失败
3. 事务是不可分割的工作逻辑单元

· 语法

-- 开启事务
start transaction;
或者 begin;

-- 提交事务,各个步骤全部成功触发
commit;

-- 回滚事务,出现异常触发
rollback;

######· 案例

实例：
创建表和数据
-- 创建账户表
CREATE TABLE ACCOUNT(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
money DOUBLE(10,2)
);
-- 添加数据
INSERT INTO ACCOUNT(NAME,money) VALUES('张三',1000),('李四',1000);

-- 事务操作：转账操作
-- 开启事务
BEGIN;

-- 1、查询李四的余额
-- 2、李四余额-500
UPDATE ACCOUNT SET money = money - 500 WHERE NAME = '李四';

-- 出错了...

-- 3、张三余额+500
UPDATE ACCOUNT SET money = money + 500 WHERE NAME = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

· 事务的四大特征(A C I D)

原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性(Conistency)：事务完成时，必须所有的数据都保持一致
隔离性(Isolation)：多个事务之间，操作的可见性
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

· mysql事务提交方式

-- 查看事务的默认提交方式
select @@autocommit;

-- 1表示自动提交 0表示手动提交

-- 修改事务提交方式
set @@autocommit = 0;
-- 改为手动提交之后，如果语句最后不加commit;提交事务语句，那么sql语句实质上并没有改变数据值

A7Feather

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

mysql知识点

一、关系型数据库

· 概念

建立在关系型模型基础上的数据库

由多张能相互连接的二维表组成的数据库

· 优点

· 简介

· 语法

· 分类

· DDL-操作数据库

· DDL-操作表

· 修改表

· DML-增删改数据

· 添加数据

· 修改数据

· 删除数据

· DQL查询数据

· 语法

· 基础查询

· 条件查询(where)

· 排序查询(order by)

· 聚合函数

· 分组查询(group by)

· 分页查询(limit)

三、数据类型

· 注释

四、图形化客户端工具

· Navicat for MySQL

五、约束

· 概念

· 分类

· 添加约束

· 删除约束

· 外键约束

· 概念

· 添加约束

六、数据库设计

· 软件研发步骤

· 数据库设计概念

· 数据库设计步骤

· 表关系

· 一对一

· 一对多(多对一)

· 多对多

七、数据库设计案例

八、多表查询

· 笛卡尔积

· 多表查询

· 连接查询

· 子查询

· 事务

· 简介

· 语法

· 事务的四大特征(A C I D)

· mysql事务提交方式