第一章数据库 MySQL
1. 数据库
DataBase:DB
数据库特点:
1.持久化存储数据;数据存储到文件中,数据库就是一个文件系统。
2.方便存储和管理数据。
3.使用统一的方式来操作数据库–>SQL
数据库软件:Oracle MySQL Microsoft SQL Server
2.MySQL
1.MySQL启动:电脑-》管理-》服务-》启动
cmd->service.msc 打开服务窗口
net stop mysql net start mysql //停止/启动mysql服务
mysql -uroot -proot //查询mysql服务状态
2. 登录: mysql -uroot -p // 密文显示密码 root
推出:mysql > exit
登录同桌mysql: mysql -hip -uroot -p***
退出:quit
登录: mysql --host=ip --user=root --password=root
3.MySQL目录结构
1.MySQL安装目录:配置文件 my.ini
2.MySQL数据目录:数据库 --》 表 --》数据
3. SQL
SQL: Structered Query Language 结构化查询语言
定义了一种操作所有关系型数据库的规则语言。每一种数据库存在不一样的地方,方言。
SQL通用语法:
1. 单行/多行书写,以分号结尾。
查看所有数据库:show databases;
查看所有表:
2.空格或缩进增强语句可读性。
3.不区分大小写,关键字建议大写。
4.三种注释。
/* 单行注释 */ -- 注释内容 或者 # 注释内容(#注释可以不加空格)
/* 多行注释 */ 同java注释/* */
SQL分类:
1.DDL(Data Definition Language)
定义数据库对象:数据库,表,列。关键字:create,drop,alter
2.DML(Data Manipulation Language)
对数据库中表的数据增删改。关键字:insert delete update
3.DQL(Data Query Language)
查询数据库中表的记录数据。关键字:select where
4.DCL(Data Control Language)
定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT REVOKE
1.DDL
DDL:
操作数据库:CRUD
1.C(create):创建
创建数据库:create database DB1;
先判断是否存在数据库再创建: create database if not exists db1;
创建指定字符类型的数据库:create database db3 character set gbk;
(create database if not exists db4 character set gbk;)
2.R(Retrieve):查询
查询所有数据库名称:SHOW DATABASES;
查看某个数据库的字符集/查看某个数据库的创建语句:show create database mysql;
3.U(Update):修改
修改数据库的字符集:alter database db1 character set utf8;
4.D(Delete):删除
删除数据库:drop database db1;
先判断在删除:drop database if exists db3;
5.使用数据库:
查询当前正在使用的数据库:select database();
更改目前在用的数据库:use db1;
操作表:
1.C(create):创建
创建表:create table 表名 (
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n
); //最后一列不写,
数据库类型:
1.int:整数类型 age int,
2.double:小数类型 score double(5,2),
3.date:日期类型,只包含年月日,yyyy-MM-dd
datatime:日期 年月日时分秒,yyyy-MM-dd HH:mm:ss,
timestampe:时间戳类型,包含年月日时分秒
如果将来不给次字段赋值,默认使用系统时间赋值。
4.varchar:字符串类型 name varchar(20) //姓名最大20个字符
张三 2个字符 zhangsan 8个字符
创建一个学生表:
create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP //????
);
复制表/创建一个和已经存在的表一样的表:create table stu like student;
2.R(Retrieve):查询
查询某个数据库中所有表的名称:show tabels;
查询表结构:desc 表名;
3.U(Updata):更改
修改表名:alter table student rename to stu;
查看表的信息/查看表的创建语句:show create table stu;
修改表的字符集:alter table stu character set gbk;
添加一列:alter table stu add 列名 数据类型;
修改列名称:alter table stu change gender sex varchar(20); //改名字 改数据类型
alter table stu modify sex varchar(10); //只改数据类型
[修改MySQL中的字段,去掉唯一约束]
删除列:alter table stu drop sex;
4.D(Delete):删除
先判断再删除:drop table if exists student;
5.使用数据库:
客户端图形化工具:SQLYog
2.DML
DML:
1.添加数据
insert into 表名(列名1,列名2...列名n)values(value1,value2...valuen)
【注意】1.列名和值要一一对应。
2.如果表名后,不定义列名,则默认给所有列添加值。(默认所有,不可以有的不添加。)
3.除了数字类型,其他类型需要引号。
2.删除数据
delete from 表名 [where 条件];
【注意】如果不加条件,删除表中所有数据。
truncate table stu; //把表删除,再创建一个一模一样的空表。推荐使用,效率更高。
3.修改数据
update 表名 set 列名1=值1,列名2=值2...列名n=值n where 修改条件;
UPDATE stu SET age=223 WHERE id=2; //根据条件修改
【如果不加任何条件,将表中所有数据全部修改】
3.DQL
DQL:查询表中的数据
1.查询表中所有数据:select * from 表名;
2.语法:
select
字段列表
from
表明列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
3.基础查询
1.多个字段查询
2.去重复
3.计算列
4.起别名
# 源码示例:
CREATE TABLE student3 (
id INT,/*--编号*/
NAME VARCHAR(20), /*--姓名*/
age INT, /*--年龄*/
sex VARCHAR(5), /*--性别*/
address VARCHAR(100),/* --地址*/
math INT, /*--数学*/
english INT /*--英语*/
) ;
INSERT INTO student3(id,NAME, age, sex,address,math, english) VALUES(1,'马云',55,'男',
'杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);*/
1.只想查询姓名,年龄:selecct name,age from student3;
查询所有字段:select * from student3;
2.去除重复的结果集:select distinct address from student3;
select distinct name,address from student3;
3.计算math 和 english 之和(一般可以使用四则运算)
select name,math,english,math + english from student3;
select name,math,english,math + ifnull(english,0) from student3;
如果有null参与的计算结果都为null;
4.起别名: select name,math,english,math + ifnull(english,0) as 总分 from student3;
select name,math 数学,english 英语,math + ifnull(english,0) as 总分 from student3;
4.条件查询:
1.where字句后跟条件
查询年龄大于20岁的人:select * from student3 where age>=20;
查询年龄大于20小于30的人:select * from student3 where age >= 20 and age <= 30;
select * from student3 where age between and 30;
查询年龄等于特定年龄的人:select * from student3 where age=18 or age=22 or age=25;
select * from student3 where age in (18,22,25);
select * from student3 where english is null; //null值不可以使用等号,不等号判断
2.运算符
* > < <= >= = <>
* between...and
* in(集合)
* is null
* and &&
* or ||
* not !
3.like模糊查询
查询姓 马 的同学:select * from student3 where name like '马%';
查询姓名中第二个字是 化 的同学:select * from student3 where name like '_马%';
查询姓名中姓名是三个子的同学:select * from student3 where name like '___';
查询姓名中包含 马 的同学:select * from student3 where name like '%马%';
【% 多个占位符 _ 单个占位符】
5.排序查询
语法:order by 排序字段1,排序字段2 排序方式1,排序方式2
select * from student order by math; 排序方式
升序方式- ASC(默认的) 降序的-DESC
按照数学成绩排名,如果数学成绩一样按照英语成绩排序:select * from student3 order by math ASC, english desc;
如果有多个排序条件,按顺序,先排前面的,再拍后面的。
6.聚合函数:将一列数据作为一个整体,进行纵向的计算。
1.count,计算个数
select count(name) from student3;
(如果数据中包含null数据):select count(ifnull(english,0)) from student3;
一般选择非空的列,--主键
只要这一排数据有一个不为null,就算入其中:select count(*) from student3;
2.max,计算最大值
select max(math) from student3;
3.min,计算最小值
select min(math) from student3;
4.sum,计算和
select sum(math) from student3;
5.avg,计算平均值
select avg(math) from student3;
【注意】聚合函数会排除null数据。
7.分组查询
group by 分组字段
【注意】分组之后查询的字段:分组字段 / 聚合函数。 否则没有意义。
按性别分组,查询数学平均分,人数:select sex,avg(math),count(id) from student3 group by sex;
分组之前对数据筛选,要求:分数低于70分的同学不参与分组:
select sex,avg(math),count(id) from student3 where math>70 group by sex;
再加要求:分组之后人数大于2:
select sex,avg(math),count(id) from student3 where math>70 group by sex having count(id)>2;
【where 和 having的区别】:
1. where 在分组之前进行限定。having 在分组之后进行限定。
2. where 后不可以跟聚合函数,having可以进行聚合函数的判断。
select sex,avg(math),count(id) 人数 from student3 where math>70 group by sex having 人数>2;
8.分页查询 limit 开始的索引,每页查询的条数;
每页显示3条数据:select * from student3 limit 0,3; --0条开始,第1页显示3条
select * from student3 limit 3,3;--3条开始,第2页显示3条
select * from student3 limit 6,3;--6条开始,第3页显示3条
公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
分页操作(limit语法)是MySQL方言。
4. 约束
约束:对表中数据机型限定,保证数据的正确性,有效性和完整性。
mysql中约束有四种:
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
//创建代码
DROP TABLE department;
DROP TABLE employee;
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_locationion VARCHAR(20)
)
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, -- 主键对应的外键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id)
)
-- 添加部门信息
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
-- 添加员工信息
-- truncate table employee;
INSERT INTO employee VALUES(NULL,'张三',20,1);
INSERT INTO employee VALUES(NULL,'李四',21,1);
INSERT INTO employee VALUES(NULL,'王五',22,1);
INSERT INTO employee VALUES(NULL,'大王',20,2);
INSERT INTO employee VALUES(NULL,'老王',22,2);
INSERT INTO employee VALUES(NULL,'小王',18,2);
SELECT * FROM department;
SELECT * FROM employee;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk ;
-- 创建表之后,添加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id);
-- 级联更新,级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id)
REFERENCES department (id) ON UPDATE CASCADE;
1.主键约束:primary key
1.注意:非空且唯一;一张表只能有一个字段为主键;主键就是表中记录的唯一标识。
2.在创建表时,添加主键约束:id int primary key,
3.表创建后,添加主键:alter table stu1 modify id int primary key;
4.删除主键:alter table stu1 drop primary key ; --删除主键约束
5.自动增长:如果某一列是int类型的,使用auto_increment可以来完成值的自动增长。
5.1创建表时,添加主键约束,完成主键自增长:id int primary key auto_increment,
5.2删除自动增长:alter table stu1 modify id int; --主键删不掉,删除了自动增长
2.非空约束:not null 值不能为空
1.创建表时添加约束:name varchar(20) not null;
2.创建表后,添加非空约束:先创建,name varchar(20); --> alter table stu1 name varchar(20) not null; /* 想要修改,必须确保表中现有数据全是非null的才可以。 */
3.删除 name 的非空约束: alter table stu1 modify name varchar(20);
3.唯一约束:unique 值不能重复
1.创建表时添加唯一约束: phone_name varchar(20) unique;
2.创建表后添加唯一约束:alter table stu modify phone_number varchar(20) unique;
3.删除唯一约束:alter table stu1 drop index phone_number; --删除唯一约束
4.外键约束:foreign key 让表与表产生关系,从而保证数据的正确性。
1.在创建表时,可以添加外键列。
create table employee(
...
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
constraint emp_dept_fk foreign key (deop_id) references department (id)
)
2.创建表之后,添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id);
3.删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk ;
4.级联操作
添加外键,设置级联更新,设置级联删除:ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id) ON UPDATE CASCADE;
级联跟新:on update cascade;
级联删除:on delete cascade; -- 非常小心使用!!!
#第二部分 数据库的设计
5. 多表之间的关系
1.一对一:人和身份证。
在任一方添加唯一外键,指向另一方的主键。
2.多对一:部门和员工。
在多的一方建立外键,指向另一方的主键。
3.多对多:学生和课程。
关系实现,需要借助第三张中间表。
中间表至少包含两个字段,作为第三张表的主键,分别指向这两张表的主键。
联合主键。
6. 范式
概念:设计数据库时遵循的规范。要遵循后面的范式要求,必须先遵循前面的范式要求。
各种范式呈递次规范,越高的范式数据库冗余越小。
目前数据库范式有六种:1NF,2NF,3NF,BCNF,4NF,5NF(完美范式)
1NF:每一列都是不可分割的原子数据项。
1.数据冗余严重。
2.数据添加存在问题。添加新的某几列的新数据不合法。
3.数据删除存在问题。删人会删系。
2NF:1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖。)
1.函数依赖: A->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A。
2.完全函数依赖:如果A是一个属性组,B属性的确定依赖A属性中的所有属性值。
3.部分函数依赖:如果A是一个属性组,B属性的确定只依赖A属性中的部分属性值。
4.传递函数依赖:A->B,B->C,如果通过A属性(属性组)的值,可以确定唯一B属性的值;再通过B属性(属性组)的值,可以确定唯一C属性的值,则称C传递函数依赖于A。
5.码:如果在一张表中,一个属性(属性组),被其他所有属性完全依赖,则称这个属性(属性组)为该表的码。
主属性:码属性组中的所有属性。
非主属性:出国码属性组中的属性。
3NF:在2NF的基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)。
BCNF:
4NF:
5NF(完美范式):
7. 数据库的备份和还原
命令行
命令行:
备份:mysqldump -u用户民 -p密码 数据库名称 >保存的路径
mysqldump -uroot -proot db1 > d://MySQL//a.sql
还原: 1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件 source 文件路径
(前提是之前的步骤都要操作实现)source d://MySQL//a.sql
图形化工具
图形化工具:
8. DQL: 多表查询
查询语法:
select
字段列表
from
表明列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
1.多表查询:select * from emp, dept;
-- 查到的称之为 笛卡尔积 --》 集合A 集合B 中所有元素的组合项
-- 会产生无用的数据
2.为消除无用的数据,多表查询分类,有3中多表查询方法。
【内连接查询 外连接查询 子查询】
# 附创建代码:
# 创建部门表
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,
dept_id INT, -- 部门id
FOREIGN KEY (dept_id)REFERENCES dept(id) -- 外键,关联部门表(部门表的主键)
);
DESC emp;
DESC dept;
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES('孙悟空', '男', 7200, '2013-02-24', 1);
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES( '猪八戒', '男', 3600, '2010-12-02', 2);
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES('唐僧', '男', 9000, '2008-08-08', 2);
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES('白骨精', '女', 5000, '2015-10-07', 3);
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES('蜘蛛精', '女', 4500, '2011-03-14', 1);
2.1.内连接查询
2.1.1隐式内连接
查询所有员工信息和对应的部门信息:select * from emp,dept where emp.dept_id = dept.id;
查询员工表的姓名,性别和部门表的id:
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1, -- 取别名
dept t2
WHERE
t1.dept_id = t2.id -- 限制条件
2.1.2显式内连接
select * from emp [inner] join dept on emp.dep_id = dept.id;
【内连接查询注意:】1.从哪些表中查询数据
2.查询条件
3.查询哪些字段
2.2.外连接查询
2.2.1 左外连接
语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
select * from emp left [outer] join dept on emp.dep_id = dept.id;
SELECT * FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id; -- 左外连接查询 查询到小白龙
查询的是左表所有数据以及其交集部分。
2.2.2 右外连接
语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
select * from emp right [outer] join dept on emp.dep_id = dept.id;
SELECT * FROM emp t1 RIGHT JOIN dept t2 ON t1.dept_id = t2.id; -- 右外连接查询
查询的是右表所有数据以及其交集部分。
2.3子查询
查询中嵌套查询。
2.3.1
SELECT MAX(salary) FROM emp; SELECT * FROM emp WHERE emp.salary = 9000;
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
子查询的不同种情况:
1.子查询结果单行单列
子查询可以作为条件,使用运算符去判断。运算符:不等号
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2.子查询结果多行单列
子查询结果可以作为条件,使用运算符in来判断。
SELECT id FROM dept WHERE NAME = '市场部' OR NAME = '财务部';
SELECT * FROM emp t1 WHERE t1.dept_id = 2 OR t1.dept_id = 3;
SELECT * FROM emp t1 WHERE t1.dept_id IN (2,3);
SELECT * FROM emp t1 WHERE t1.dept_id IN (SELECT id FROM dept WHERE NAME IN('市场部','财务部'));
3.子查询结果多行多列
子查询可以作为一张【虚拟表】,作为一张表的查询
-- 查询入职日期在'2011-11-11'之后员工信息和对应部门名称
SELECT * FROM emp WHERE join_date > '2010-11-11';
SELECT * FROM emp t1, dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2010-11-11';
SELECT * FROM dept t1,(SELECT * FROM emp WHERE join_date > '2010-11-11') t2 WHERE t1.id = t2.dept_id;
# 多表查询练习:
# 创建代码
CREATE DATABASE db_test1;
USE db_test1;
SHOW TABLES;
# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY, -- 部门id
dname VARCHAR(20), -- 部门名称
loc VARCHAR(20) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
# 创建工作种类表
CREATE TABLE job(
id INT PRIMARY KEY,
jname VARCHAR(20),
DESCRIPTION VARCHAR(20)
);
-- 添加4 条工作种类
INSERT INTO job(id,jname,DESCRIPTION) VALUES
(1,'董事长','管理公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','推销产品'),
(4,'文员','使用文具');
# 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY,
ename VARCHAR(20), -- 员工姓名
job_id INT, -- 员工身份id
mgr INT, -- 上级id
join_date DATE,
salary DECIMAL(7,2),
bonus DECIMAL(7,2),
dept_id INT, -- 部门id
CONSTRAINT emp_job_id_fk FOREIGN KEY (job_id) REFERENCES job(id),
CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(id)
);
-- 插入员工信息
INSERT INTO emp (id,ename,job_id,mgr,join_date,salary,bonus,dept_id)VALUES
(1001,'孙悟空',4,1004,'2000-12-27','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,'2004-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',4,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 -- 最高工资
);
-- 插入工资等级
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.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置。
/* 员工姓名,工资,职务名称 - emp 职务描述 - job 部门名称,部门位置 - dept
查询条件:emp.job_id = job.id and emp.dept_id = dept.id */
SELECT
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.查询员工姓名,工资,工资等级。
/* 员工姓名,工资 - emp 工资等级 - salarygrade
条件:emp.salary between salarygrade.losalary and salarygrade.hisalary */
SELECT t1.ename,
t1.salary,
t2.grade
FROM emp t1,
salarygrade t2
WHERE t1.salary BETWEEN t2.losalary AND t2.hisalary
-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级。
/* 员工姓名,工资 -emp 职务名称,职务描述 -job 部门名称,部门位置 -dept 工资等级 -salarygrade。
条件:emp.job_id = job.id and emp.dept_id = dept.id and emp.salary between salarygrade.losalary and salarydrade.hisalary */
SELECT t1.ename,
t1.salary,
t3.jname,
t3.description,
t2.dname,
t2.loc,
t4.grade
FROM emp t1,
dept t2,
job t3,
salarygrade t4
WHERE t1.job_id = t3.id AND
t1.dept_id = t2.id AND
t1.salary BETWEEN t4.losalary AND t4.hisalary
-- 5.查询部门编号,部门名称,部门位置,部门人数。
/* 1.部门编号,部门名称,部门位置 -dept 部门人数 - dept & emp
2.使用分组查询。按照emp.dept_id分组,查询count(id)。
3.使用子查询将第2组的查询结果和dept表进行关联查询。*/
SELECT t1.id,
t1.dname,
t1.loc,
t2.num
FROM dept t1,
(SELECT dept_id,COUNT(id) num
FROM emp
GROUP BY dept_id) t2
WHERE t1.id = t2.dept_id
-- 6.查询所有员工姓名及其直接上级姓名,没有领导的员工也需要查询。
/* 1.所有员工姓名 -emp 直接上级姓名 -emp
emp表的id 和 mgr是自关联
2.条件:emp.id = emp.mgr
3.查询坐表的所有数据, 和 交集数据。 使用左外连接查询。*/
SELECT t1.ename,
t1.mgr,
t2.id,
t2.ename
FROM emp t1
LEFT JOIN emp t2
ON t1.mgr = t2.id
9.事务
- 事物的基本介绍
事务:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
操作:
1.开启事务:start transaction;
2.回滚:rollback;
3.提交:commit;
# 事务演示源码:
# 创建账户表
CREATE TABLE ACCOUNT(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
balance DOUBLE
);
-- 添加两个用户
INSERT INTO ACCOUNT(id,NAME,balance) VALUES(1,'zhangsan',1000),(2,'lisi',1000);
SELECT * FROM ACCOUNT;
UPDATE ACCOUNT SET balance = 1000; -- 账户还原为1000
-- zhangsan 给 lisi 转账500元
START TRANSACTION; -- 开启事务
-- 1.zhangsan账户-500
UPDATE ACCOUNT SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2.lisi 账户+500
UPDATE ACCOUNT SET balance = balance + 500 WHERE NAME = 'lisi';
ROLLBACK; -- 出错回归
COMMIT; -- 正常提交 */
4.mysql数据库中事务默认自动提交
一条DML(增删改)语句会自动提交一次事务。
事务提交的2种方式: * 自动提交 mysql就是自动提交的。一条DML(增删改)语句会自动提交一次事务。(oracle默认是手动提交事物的。)
*手动提交 先开启事务,再提交。
查看事物的默认提交方式:select @@autocommit; -- 1代表自动提交 0代表手动提交
修改事物的默认提交方式:set @@autocommit = 0; -- 1代表自动提交 0代表手动提交
- 事物的四大特征
1.原子性:不可分割的最小操作单位。要么同时成功,要么同时失败。
2.持久性:事务一旦提交或者回滚,数据库会持久化保存数据。
3.隔离性:多个事物之间。相互影响。
4.一致性:事务操作前后数据总量不变。
- 事物的隔离级别
多个事物之间是隔离的,相互独立的。如果多个事务操作同一批数据,会引发一些问题,设置不同的隔离级别就可以解决这些问题。
存在的问题:
1.脏读:一个事务读取到另一个事务中没有提交的数据,脏读。
2.不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到的自己的修改。
隔离级别,4种:
1.read uncommitted:读未提交
产生的问题:脏读 不可重复读 幻读
2.read committed:读已提交(oracle默认)
产生的问题:不可重复读 幻读
3.repeatable read:可重复读(MySQL默认)
产生的问题:幻读
4.serializable:串行化
可以解决所有的问题。
【隔离级别从小到大,安全性越来越高,效率越来越低。】
数据库查询隔离级别:
SELECT @@transaction_isolation;
数据库设置隔离级别
set global transaction isolation level 级别字符串;
- 隔离级别演示:
* 演示:
-- 设置隔离界别 读未提交
set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
select * from account; -- 两边都读
10.DCL:
1. DDL:操作数据库和表
2. DML:增删改表中数据
3. DQL:查询表中数据
4. DCL:管理用户,授权
* DBA:数据库管理员
DCL:管理用户,授权。DBA数据库管理员。
1.管理用户
1.1 添加用户
-- 创建用户
-- create user '用户名'@'主机名' identified by '密码';
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123'; -- 本机访问
CREATE USER 'zhangsan'@'%' IDENTIFIED BY '123'; -- 远程访问
1.2 删除用户
-- 删除用户
-- drop user '用户名'@'主机名';
DROP USER 'zhangsan'@'localhost';
DROP USER 'zhangsan'@'%';
1.3修改用户密码
-- 修改用户密码
-- ALTER USER 用户名@主机名 IDENTIFIED BY '系密码' ;
ALTER USER zhangsan@localhost IDENTIFIED BY '123';
-- 5.0失效 update user set password = password('新密码') where user = '用户名';
-- 8.0失效 5.0有效 set password for 'zhangsan'@'localhost' = password('abc');
【root用户密码忘记,百度!】
* mysql中忘记了root用户的密码?
1. cmd -- > net stop mysql 停止mysql服务
* 需要管理员运行该cmd
2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4. use mysql;
4.1 flush privileges;
5. SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
6. 关闭两个窗口
7. 打开任务管理器,手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录
1.4 查询用户
-- 查询用户
-- 1. 切换到mysql数据库,查询user表
USE MYSQL;
SELECT * FROM USER;
2.授权
2.1 查询权限
-- 查询权限
-- show grants for '用户名'@'主机名';
SHOW GRANTS FOR 'zhangsan'@'localhost';
SHOW GRANTS FOR 'root'@'localhost';
2.2 授予权限
-- 授予权限
-- grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
GRANT SELECT,UPDATE ON db_test1.account TO 'zhangsan'@'localhost';
-- 给 zhangsan 用户授予所有权限
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
2.3 撤销权限
-- 撤销权限
-- revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE SELECT ON db_test1.account FROM 'zhangsan'@'localhost';
REVOKE ALL ON *.* FROM 'zhangsan'@'localhost'; -- 撤销所有权限