MySQL数据库简单了解

SQL

DDL -- 操作数据库

1、查询:

SHOW DATABASES;

2、创建

创建数据库:

CREATE DATABASE 数据库名称;

创建数据库(判断,如果不存在则创建):

CREATE DATABASE IF NOT EXISTS 数据库名称;	-- 如果存在就不创建了

3、删除

删除数据库:

DROP DATABASE 数据库名称;

删除数据库(判断,如果存在则删除):

DROP DATABASE IF EXISTS 数据库名称;

4、使用数据库

查看当前使用的数据库:

SELECT DATABASE();

使用数据库:

USE 数据库名称;

DDL -- 操作表

创建(create)

查询(retrieve)

修改(updata)

删除(delete)

查询表:

查询当前数据库下所有表名称:

SHOW TABLES;

查询表结构:

DESC 表名称;

查询表的详细建表语句:

SHOW CREATE TABLE 表名 [\G];	-- 此处\G可以使得输出的格式更好(但是navicat不支持可在黑窗口查看)
创建表:

注意:最后一行末尾,不能加逗号

CREATE TABLE 表名{
		字段1 数据类型1,
		字段2 数据类型2,
		...
		字段n 数据类型n
};
数据类型:

MySQl支持多种数据类型,可以分为三类:

​ 数值

​ 日期

​ 字符串

常用示例:

INT:表示整型数、DOUBLE:表示浮点数(DOUBLE(所占总位数,小数位数))

DATE:表示日期,只有年月日

CAHR:表示字符串类型(后面跟的()里面的数表示最大长度)[性能高,浪费空间]

VARCHAR:括号后的长度也是最大长度,但是会可以根据获取的长度改变长度(即传入 ‘张三‘ 表示占两个)[性能低,节约空间]

案例:设计一张学生表

CREATE TABLE student(
    -> id INT,
    -> name VARCHAR(10),
    -> gender CHAR(1),
    -> birthdy DATE,
    -> score DOUBLE(5,2),
    -> email VARCHAR(64),
    -> tel VARCHAR(15),
    -> status TINYINT
    -> );
删除表:

删除表:

DROP TABLE 表名;

删除表并判断表是否存在:

DROP TABLE IF EXISTS 表名;
修改表:

主要是对表中的字段进行操作和改本表名

修改表名:

ALTER TABLE 表名 RENAME TO 新表名;

添加一列:

ALTER TABLE 表名 ADD 列名 数据类型;

修改数据类型或者列级完整性约束条件:

ALTER TABLE 表名 MODIFY 列名 新数据类型;

修改列名和数据类型:

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

删除列:

ALTER TABLE 表名 DROP 列名;		-- 也可以通过:外键约束 外键名来删除外键后面会提及

DML

添加(insert)

修改(update)

删除(delete)

添加数据:

此处的列就是指的字段

给指定的列添加数据:

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,...)...;
修改数据:
UPDATE 表名 SET 列表1=值1,列名2=值2,...[WHERE 条件];
-- 示例:UPDATE stu SET id = '3' WHERE username = '李四';

注意:修改语句中如果不加条件,则将所有数据都修改(即对应行的对应数据改为=后的值)!

删除语句:
DELETE FROM 表名 [WHERE 条件]; 

注意:修改语句中如果不加条件,则将所有数据都删除!

DQL

查询语法:

基础查询、条件查询(WHERE)、分组查询(GROUP BY)、排序查询(ORDER BY)、分页查询(LIMIT)

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定
基础查询:

查询多个字段:

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名;	-- 查询所有数据(为了更好的观察字段可以尽量不使用)

去除重复记录:

SELECT DISTINCT 字段列表 FROM 表名;	-- 使用distinct关键词后,不会输出此字段的重复数据

起别名:

AS -- 也可以不写,使用是写在字段名后(原名 AS '别名')
条件查询:

条件查询语法:

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

条件:

符号 功能
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
<> 或 != 不等于
BETWEEN...AND... 在某个范围之内(都包含)
IN(...) 多选一(即符合括号中的其中一个条件即可)
LIKE 占位符 模糊查询 _单个任意字符,%多个任意字符
ISNULL 是NULL
IS NOT NULL 不是NULL
AND 或 && 并且
OR 或者 || 或者
NOT 或 ! 非,不是

参考代码:

SELECT * FROM stu;

-- 一、条件查询(清晰查询)

-- 1、查询年龄大于20岁的学员信息
SELECT * FROM stu WHERE age > 20;

-- 2、查询年龄大于等于20岁的学院信息
SELECT * FROM stu WHERE age >= 20;

-- 3、查询年龄大于等于20岁 并且年龄小于等于 30岁的学员信息
SELECT * FROM stu WHERE age >= 20 AND age <= 30;

-- 改进写法
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

-- 4、查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';

-- 5、查询年龄等于18岁的学员的信息
SELECT * FROM stu WHERE age = 18;

-- 6、查询年龄不等于18岁的学员信息
SELECT * FROM stu WHERE age != 18;

-- 另一种写法
SELECT * FROM stu WHERE age <> 18;

-- 7、查询年龄等于18岁 或者 年龄等于22岁的学员信息
SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22;

-- 改进写法
SELECT * FROM stu WHERE age IN(18, 20, 22);

-- 8、查询英语成绩为null的学员信息
-- 注意:null值的比较不能使用 “=”  “!=”符号来比较,需要使用 is is not 来进行比较

SELECT * FROM stu WHERE english = NULL;

SELECT * FROM stu WHERE english IS NULL; 

-- 如果不是NULL 就写 IS NOT NULL
SELECT * FROM stu WHERE english IS NOT NULL;

-- 二、条件查询 模糊查询
-- 注意!!使用模糊查询要使用关键字like来进行比较
/*
通配符:
		(1)_:代表任意单个字符
		(2)%:代表任意个数字符
*/

-- 1、查询姓'马'的学员信息
SELECT * FROM stu WHERE name LIKE '马%';

-- 2、查询第二个字是'花'的学员信息
SELECT * FROM stu WHERE name LIKE '_花%';

-- 3、查询名字中包含'德'的学员信息
SELECT * FROM stu WHERE name LIKE '%德%';
排序查询:

排序查询语法:

SELECT 字段列表 FROM 表名 ORDER BY 排序字段1[排序方式1], 排序字段2[排序方式2]...;

排序方式:

ASC:升序排列(默认值)

DESC:降序排序

注意:如果右多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序

代码示例:

-- 1、查询学生信息,按照年龄升序排列 
SELECT * FROM stu ORDER BY age ASC;	-- 如果不写也是ASC(即升序排列)

-- 2、查询学生信息,按照数学成绩降序排列
SELECT * FROM stu ORDER BY math DESC;


-- 3、查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
SELECT * FROM stu ORDER BY math DESC, english ASC;
了解聚合函数:

概念:

将一个数据作为一个整体,进行纵向计算

聚合函数分类:

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

聚合函数语法:

SELECT 聚合函数名(列名) FROM 表;

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

参考代码:

SELECT * FROM stu;

-- 1、统计班级一共有多少个学生
SELECT COUNT(id) FROM stu;	-- count 统计的列不能为空值null
-- 此处()中推荐使用主键或者*

-- 2、查询数学成绩的最高分
SELECT MAX(math) FROM stu;
  
-- 3、查询数学成绩的最低分
SELECT MIN(expr)(math) FROM stu;
  
-- 4、查询数学成绩的总分
SELECT SUM(math) FROM stu;

-- 5、查询数学成绩的平均分
SELECT AVG(math) FROM stu;

-- 6、查询英语成绩的最低分
SELECT MIN(english) FROM stu;	-- 可以看到null值并没有被读取
分组查询:

分组查询语法:

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

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

where 和 having 区别:

​ 执行时机不一样:where是分组之前进行限定,不满足where条件,则不会参与分组,而having是分组之后对结果进行过滤

​ 可判断的条件不一样:where不能对聚合函数进行判断,having可以(主要是因为执行顺序)

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

参考代码:

SELECT * FROM stu;

-- 1、查询男同学和女同学各自的数学平均分
SELECT sex, AVG(math) FROM stu GROUP BY sex;	-- 在显示字段处添加参与分组字段sex的情况下,不知道数据是那个具体分类的
-- !!!注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义(即那些字段数据还是单个保存并没有合并因此就不匹配)
SELECT name, sex, AVG(math)  FROM stu GROUP BY sex;

-- 2、查询男同学和女同学各自的数学平均分,以及各自人数
SELECT sex, AVG(math),COUNT(id) FROM stu GROUP BY sex;	 -- select 用于选中那些字段需要显示,from选择表,group by 用于根据字段分组


-- 3、查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
SELECT sex, AVG(math),COUNT(*) FROM stu WHERE math >= 70 GROUP BY sex;
 

-- 4、查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2个的
SELECT sex, AVG(math),COUNT(*) FROM stu WHERE math >= 70 GROUP BY sex HAVING COUNT(*) > 2;	-- 不大于2个人的男同学数据就没有显示了
分页查询:

分页查询的语法:

SELECT 字段列表 FROM LIMIT 起始索引, 查询条目数;
-- 起始索引:从0开始

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

tips:

​ 分页查询limit是MySQL数据库的方言

​ Oracle分页查询使用rownumber

​ SQL Server分页查询使用top

参考代码:

SELECT * FROM stu;

-- 从0开始查询,查询3条数据
SELECT * FROM stu LIMIT 0, 3;
 
-- 每页显示3条数据,查询第1页数据
SELECT * FROM stu LIMIT 0, 3;


-- 每页显示3条数据,查询第2页数据
SELECT * FROM stu LIMIT 3, 3;


-- 每页显示3条数据,查询第3页数据
SELECT * FROM stu LIMIT 6, 3;

-- 起始索引的计算
-- 起始索引 = (当前页码 - 1) * 每页显示的条数
DQL小结:
SELECT		-- 选择需要显示的字段
	字段列表
FROM		-- 指定表对象
	表名列表
WHERE		-- 最先的条件判断
	条件列表
GROUP BY	-- 可以根据字段进行分组
	分组字段
HAVING		-- 可以用于判断聚合函数值
	分组后条件
ORDER BY	--(ASC DESC)
	排序字段
LIMIT		--(起始下标, 数据个数)	起始下标 = (当前页码 - 1) * 每页显示的条数
	分页限定

约束

1、概念和分类

​ 约束是作用于表中列上的规则,用于限制加入表中数据

​ 约束的存在保证了数据库中数据的正确性、有效性和完整性

2、约束的分类:
约束名称 描述 关键字
非空约束 保证类中所有数据不能有null值 NOT NULL
唯一约束 保证列中所有数据各不相同 UNIQUE
主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY
检查约束 保证列中的值满足某一条件 CHECK
默认约束 保存数据时,未指定值则采用默认值 DEFAULT
外键约束 外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性 FOREIGN KEY

注意:MySQL中不支持检查约束

1、在创建表时,使用列级完整性约束条件来约束字段:

SQL参考:

-- 查看emp表是否已经存在
DROP TABLE IF EXISTS emp;

-- 创建员工表
CREATE TABLE emp(	-- 一开始没有添加 auto_increment关键字,测试时重新创建的表
	id INT PRIMARY KEY auto_increment,								-- 员工id,主键且自增长
	ename VARCHAR(50) NOT NULL UNIQUE,		-- 员工姓名,非空并且唯一
	joindate DATE NOT NULL,						-- 入职时间,非空
	salary DOUBLE(7, 2)  NOT NULL,		-- 工资,非空
	bonus DOUBLE(7, 2)	DEFAULT 0			-- 奖金,如果没有奖金默认为0
);

-- 插入数据
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (1, '张三', '1999-11-11', 8000, 5000);


-- 查看表中数据
SELECT * FROM emp;

-- 一、演示主键:非空且唯一
-- 可以看到错误提示,id cannot be null 
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (NULL, '李四', '2000-12-12', 7000, 4000);
-- Duplicate entry '1' for key 'emp.PRIMARY' 提示添加了一个重复数据  Duplicate 重复
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (1, '王五', '2000-12-20', 6000, 3000);
-- 可以看到在不考虑其它因素的条件下,只要满足了主键条件就能够插入
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (2, '李四', '2000-12-12', 7000, 4000);


-- 二、演示非空:
-- 可以看到,名字不能为空了
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (3, NULL, '2000-12-12', 7000, 4000);

-- 三、演示唯一约束:
-- 可以看到,名字不能重复了
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (3, '李四', '2000-12-12', 7000, 4000);

-- 四、演示默认约束:
-- 可以看到在不给字段bonus 赋值时(即只给前几个字段赋值)默认会给一个0值到bonus中
INSERT INTO emp(id, ename, joindate, salary) VALUES (3, '王五', '2000-12-12', 7000);
-- 可以看到如果给了bonus字段值,那么他将不再使用默认值
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(4, '赵六', '2001-4-8', 8000, 9000);

-- 五、演示自动增长:auto_increment(使用条件):“当列是数字类型并且 唯一约束”
-- 演示前请将未添加 auto_increment关键字的表删除并重建
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (1, '张三', '1999-11-11', 8000, 5000);
-- 可以看到,这样的情况下使用null给主键赋值,会自动增加一位
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES (null, '李四', '1999-11-11', 8000, 5000);

SELECT * FROM emp;	-- 用于查看表中数据

2、此外还可以在表创建完后,再补充约束条件:

以下两个都是使用的ALTER TABLE语句中修改表结构的语句来实现的

创建表后补充约束:

ALTER TABLE 表名 MODIFY 字段名 数据类型 约束条件;

创建表后删除约束条件:

ALTER TABLE 表名 MODIFY 字段名 数据类型;	-- 此处通过覆盖进行约束条件的删除,但是并没有删除该字段

3、外键约束:

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

创建表时添加外键约束:

-- 创建表时添加外键约束
CREATE TABLE 表名(
	列名 数据类型[],
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名); -- 此为表级完整性约束条件(用于约束表)
    -- 有时一个表中需要多个外键时,可以先使用	
    -- CONSTRAINT 约束名 PRIMARY KEY(字段一, 字段二...)	来先设置多个候选键(都能够唯一标识此行)
    -- 然后再将这些候选键和对应的外键链接  语法如上
);

创建后添加外键外键约束条件:

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称);

删除外键约束:

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;	-- 此次使用的是修改表结构中的DROP语句
-- 通过外键名删除外键,并没有删除字段

参考代码:

-- 一、设置外键约束:
-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,
	
	-- 添加外键约束条件 dep_id 关联 dept 表中的id主键
	-- 可以看到产生这个关联后,“无论是创建还是添加数据”,都要先将关联的外键所在的主表先操作好再执行本表操作
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
	-- 同时通过手动删除可以看到,需要先将员工表中对应的在某一部门的人员全部删除后才能够删除对应部门,否则直接删除某一部门会被约束条件限制
);

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

select * from dept;
select * from emp;

-- 二、删除外键:
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

-- 三、在表外,添加外键
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dep_id) REFERENCES dept (id);

多表查询

未添加任何条件的情况下:笛卡尔积,对两个表中的所有行进行组合,列直接拼接

多表查询:从多张表查询数据

​ 连接查询

​ 内连接:相当于查询A,B交集数据

​ 外连接:

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

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

​ 子查询

1、内连接:

内连接查询语法(内连接相当于查询A,B表的交集数据)

-- 隐式内连接
SELECT 字段列表 FROM 表1, 表2... WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;	
-- 注意这一个语句代表两个表的关联 如果还有另外的表则需要再用 表1 INNER JOIN 表3 ON 条件 来表明 

参考代码:

-- 隐式内连接
SELECT * FROM emp, dept WHERE emp.dep_id = dept.did;
	
-- 查询 emp 的 name, gender, dept表的dname
	
-- 	SELECT emp.name, emp.gender, dept.dname FROM emp, dept WHERE emp.dep_id = dept.did;
-- 给表取别名
SELECT
	t1.NAME,
	t1.gender,
	t2.dname 
FROM
	emp t1,	-- 给emp表取了一个别名 t1
	dept t2 -- 给dept表取了一个别名 t2
WHERE
	t1.dep_id = t2.did;
	
-- 显示内连接
SELECT * FROM emp INNER JOIN dept ON emp.dep_id = dept.did;
2、外连接:

外连接语法:

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

表1中的数据在表2中不一定有对应数据,此时使用左连接可以查看所有表1数据的同时也看到了这个没有对应数据的(但是在使用内连接时却不会显示无对应数据的行即不满足条件的行)

参考代码:

-- 左外连接
-- 查询emp表中所有数据和对应的部门信息
SELECT * FROM emp LEFT JOIN dept on emp.dep_id = dept.did;

-- 右外连接
-- 查询dept表中所有数据和对应的员工信息
SELECT * FROM emp RIGHT JOIN dept on emp.dep_id = dept.did;

-- 一般来讲左外连接使用较多,因为左外与右外是根据表的顺序而定的
SELECT * FROM dept LEFT JOIN emp on emp.dep_id = dept.did;
3、子连接:

即嵌套查询

子查询概念:

​ 查询中嵌套查询,称嵌套查询为子查询

子查询根据查询结果不同,作用不同:

此处的几行几列是针对于内层查询而言的

​ 单行单列:作为条件值,使用 = != > < 等进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

​ 多行单列:作为条件值,使用in等关键字进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

​ 多行多列:作为虚拟表

SELECT 字段列表 FROM(子查询) WHERE 条件;

参考代码:

-- 子连接

-- 查询工资高于猪八戒的员工信息
SELECT * FROM emp;

-- 1、先查询猪八戒的工资
SELECT salary FROM emp WHERE name = '猪八戒';

-- 2、再查询工资高于猪八戒的员工的信息
SELECT * FROM emp WHERE salary > 3600;

-- 单行单列:
-- 结合两个步骤
SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '猪八戒');

-- 多行单列(其中有一列数据并不一定):
-- 查询'财务部' 和 '市场部' 所有的员工信息
SELECT did FROM dept WHERE dname = '财务部' or dname = '市场部';

SELECT * FROM emp WHERE dep_id IN (SELECT did FROM dept WHERE dname = '财务部' or dname = '市场部');

-- 多行多列:
-- 查询入职日期时 '2011-11-11' 之后的员工信息和部门信息
SELECT * FROM emp WHERE join_date > '2011-11-11';	-- 把内层查询当作一张虚拟表

SELECT
	* 
FROM
	( SELECT * FROM emp WHERE join_date > '2011-11-11' ) t1,	-- 将内层查询当作表,并取别名
	dept 
WHERE
	t1.dep_id = dept.did;
4、多表查询案例:
-- 环境准备
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

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

-- 员工表
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 (did)
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
				
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

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


-- 添加员工
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);


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


-- 1、查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
/*
	分析:
			1、员工编号,员工姓名,工资 	在emp(员工表中)
			2、职务名称,职务描述		在job(职务表中)
			3、job职务表  和  emp员工表  是一对多的关系 emp.job_id = job.id
*/

-- 隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp,
	job 
WHERE
	emp.job_id = job.id 
ORDER BY
	id ASC;
	
SELECT * FROM emp;
SELECT * FROM job;

-- 显示内连接
SELECT 
	emp.id,	-- 需要打印的字段
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM			
	emp
INNER JOIN -- 显示内连接
	job
ON
	emp.job_id = job.id
ORDER BY
	id ASC;


-- 2、查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
/*
	分析:
			1、员工编号,员工姓名,工资 	在emp(员工表中)
			2、职务名称,职务描述		在job(职务表中)
			3、job职务表  和  emp员工表  是一对多的关系 emp.job_id = job.id
			4、部门名称,部门位置		在dept(部门表中)
			5、dept 和 emp 一对多的关系		dept.did = emp.dept_id 
*/

-- 隐式内连接
SELECT		-- 用于打印内容
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description, 
	dept.dname,
	dept.loc
FROM			-- 用于选择内容所在表
	emp,
	job,
	dept
WHERE			-- 用于进行条件判断
	emp.job_id = job.id 
	AND emp.dept_id = dept.did
ORDER BY	-- 用于排序
	id ASC;

-- 显示内连接
SELECT 
	emp.id,	-- 需要打印的字段
	emp.ename,
	emp.salary,
	job.jname,
	job.description, 
	dept.dname,
	dept.loc
FROM			
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.did
ORDER BY
	id ASC;

-- 3、查询员工姓名,工资,工资等级
/*
	分析:
		1、员工姓名,工资 信息在emp 员工表中
		2、工资等级 信息在 salarygrade 工资等级表中
		3、emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.ename,
	emp.salary,
	t2.grade 
FROM
	emp,
	salarygrade t2 
WHERE	-- 限定了该工资等级的最低和最高工资
	emp.salary >= t2.losalary 
	AND emp.salary <= t2.hisalary;


-- 4、查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
/*
		1、员工编号,员工姓名,工资 	在emp(员工表中)
		2、职务名称,职务描述		在job(职务表中)
		3、job职务表  和  emp员工表  是一对多的关系 emp.job_id = job.id
		4、部门名称,部门位置		在dept(部门表中)
		5、dept 和 emp 一对多的关系		dept.did = emp.dept_id 
		6、工资等级 信息在 salarygrade 工资等级表中
		7、emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
SELECT 
	emp.id,	-- 需要打印的字段
	emp.ename,
	emp.salary,
	job.jname,
	job.description, 
	dept.dname,
	dept.loc,
	t2.grade
FROM			
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.did
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary AND t2.hisalary
ORDER BY
	id ASC;


-- 5、查询出部门编号、部门名称、部门位置、部门人数
/*
	分析:
		1、部门编号、部门名称、部门位置 来自于部门 dept表
		2、部门的人数:	在emp表中 按照dept_id 进行分组然后用count(*)统计人数
		3、使用子查询,使得分组后的表和部门表进行内连接
*/
SELECT * FROM dept;
SELECT dept_id,count(*) FROM emp GROUP BY dept_id;

-- 结合
SELECT
	dept.did,
	dept.dname,
	dept.loc,
	t1.count 
FROM
	dept,
	( SELECT dept_id, count(*) count FROM emp GROUP BY dept_id ) t1 -- 子查询中的多对多
WHERE
	dept.did = t1.dept_id;

数据库设计

1、软件研发的步骤

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

2、数据库设计概念

​ 数据库设计就是根据系统的具体需求,结合我们所选的DBMS,为了这个业务系统构造出最优秀的数据存储模型

​ 建立数据库中的表结构以及表与表之间的关联关系的过程

​ 有哪些表?表里有哪些字段?表和表之间有什么关系?

3、数据库设计的步骤

​ 需求分析(数据是什么?数据具有那些属性?数据与属性的特点是什么)

​ 逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)

​ 物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)

​ 维护设计(1、对新的需求进行建表;2、表优化)

一、表关系

1、一对一:

​ 如:用户表 和 用户详情

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

2、一对多(多对一):

​ 如:部门 和 员工

​ 一个部门对应多个员工,一个员工对应一个部门

3、多对多:

​ 如:商品 和 订单

​ 一个商品对应多个订单,一个订单包含多个商品

1、一对多:

前面约束中的员工表...

2、多对多:

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

参考代码:

DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order(
	id int PRIMARY KEY auto_increment,
	payment DOUBLE(10, 2),
	payment_type TINYINT,
	status TINYINT
);

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

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

-- 建完表后,添加外键
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);
3、一对一:

实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE)

参考代码:

CREATE TABLE tb_user_desc (
	id INT PRIMARY KEY auto_increment,
	city VARCHAR(20),
	edu VARCHAR(10),
	income INT,
	status CHAR(2),
	des VARCHAR(100)
);

CREATE TABLE tb_user (
	id INT PRIMARY KEY auto_increment,
	photo VARCHAR(100),
	nickname VARCHAR(50),
	age INT,
	gender CHAR(1),
	desc_id INT UNIQUE,
	-- 添加外键
	CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)	
);

事务

一、事务简介:

数据库的事务(Transaction)是一种机制、一个操作序列,包含了一组数据库操作命令

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败

事务时一个不可分割的工作逻辑单元

举例:如果对数据库中的两个银行账户数据进行转账(张三 - > 李四)

​ 首先需要查询张三是否有足够的余额能够进行转账

​ 然后需要将张三账户余额 减掉对应数值

​ 最后将李四账户添加对应数值

但是,这样就会存在一个问题?如果中间操作时出现错误。例如进行到李四这一步时错误,那么就会损失金额。

所以需要利用事务将这几个动作进行绑定,如果其中一个出错,那么就全部一起不执行。

二、事务操作:

步骤:

开启事务 -> 回滚事务 -> 提交事务

注意开启事务(即使用START TRANSACTION; 或者 BEGIN;)后默认为手动提交

-- 开启事务
START TRANSACTION; -- 或者 BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

参考代码:

DROP TABLE IF EXISTS account;

-- 创建账户表
CREATE TABLE account(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(10),
	money DOUBLE(10, 2)
);

-- 添加数据
INSERT INTO account(name, money) VALUES ('张三', 1000),('李四',1000);

SELECT * FROM account;

UPDATE account SET money = 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;

注意事务的提交方式:

此处没有开启事务默认为自动提交

-- !!事务的提交方式

-- 1、查询事务的默认提交方式
SELECT @@autocommit;	-- 结果为1表示默认自动提交	如果是0表示手动提交

-- 2、修改事务的提交方式 手动提交
SET @@autocommit = 0;

-- 张三金额 -500
UPDATE account SET money = money - 500 WHERE name = '张三';
-- 相当于默认写了 commit;

COMMIT;	-- 当修改事务为 手动提交了

-- 查询数据
SELECT * FROM account;

三、事务的四大特征

​ 原子性(Atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败

​ 一致性(Consistency):事物完成时,必须使所有的数据都保持一致状态

​ 隔离性(Isolation):多个事务之间,操作的可见性

​ 持久性(Durability):事务一旦提交或者回滚,它对数据库中的数据的改变就是永久的

本文作者:编程初学者求大佬指点

本文链接:https://www.cnblogs.com/fragmentary/p/16840150.html

版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @ 2022-10-29 23:00  如此而已~~~  阅读(29)  评论(0编辑  收藏  举报
//雪花飘落效果