数据库
数据库的基本概念
1.数据库(DataBase):简称DB
2.定义：数据库是用于存储和管理数据的仓库。
3.数据库的特点：
- 1.持久化存储数据。其实数据库就是一个文件系统。
- 2.方便存储和管理数据。
- 3.使用了统一的方式操作数据库。— SQL
4.常见的数据库软件：参见《MySQL基础.pdf》
5.MySQL数据库软件
- 1.安装：参见《MySQL基础.pdf》
- 2.卸载
1.datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
2.卸载MySQL
3..删除C:/ProgramData目录下的MySQL文件夹
- 3.配置
MySQL服务
1.手动。
2.cmd-->services.msc 打开服务窗口
3.使用管理员打开cmd
net start mysql :启动mysql的服务
net stop mysql：关闭mysql的服务
MySQL登录
1.mysql -uroot -p密码
2.mysql -hip -uroot -p连接目标的密码
3.mysql --host=ip --user=root --password=连接目标的密码
MySQL退出
1.exit
2.quit
MySQL目录结构
1.MySQL安装目录
配置文件 my.ini
2.MySQL数据目录
几个概念
数据库：文件夹
表：文件
数据：
SQL
概念：SQL(Structured Query Language)结构化查询语言。
其实就是定义了操作所有关系型数据库的规则，每一种数据库操作的方式存在不一样的地方，称为“方言”。
SQL通用语法
- 1.SQL语句可以单行或多行书写，以分号结尾。
- 2.使用空格和缩进来增强语句可读性。
- 3.MySQL的SQL语句不区分大小写，关键字建议使用大写。
- 4.3种注释
单行注释：-- 注释内容 或 # 注释内容(mysql 特有) 注意空格是一定要有的。
多行注释：/* 注释 */
SQL分类
- DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库、表、列等。关键字：create，drop，alter等 - DML(Data Mainpulation Language)数据操作语言。
用来对数据库中表的数据进行增删改。关键字：insert，delete，update等 - DQL(Data Query Language)数据查询语言。
用来查询数据库中表的记录(数据)。关键字：select，where等 - DCL(Data Control Language)数据控制语言(了解)
用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等
DDL:操作数据库、表
1.操作数据库：CRUD
1.C(Create):创建
Create database 数据库名称;
Create database if not exists 数据库名称;
创建数据库，判断是否已经存在，并指定字符集为gbk
Create database if not exists 数据库名称 character set gbk;
2.R(Retrieve):查询
查询所有数据库名称：show databases;
查询某数据库的字符集： show create database 数据库名称;(即查询某个数据库的创建语句)
3.U(Update):修改
修改数据库的字符集：alter database 数据库名称 character set 字符集名称;
4.D(Delete):删除
删除数据库：drop database 数据库名称;
判断数据库存在，存在再删除：drop database if exists 数据库名称;
5.使用数据库
查询当前正在使用的数据库名称：select database();
使用数据库：use 数据库名称;
2.操作表
1.C(Create):创建
ctreate table 表名(
列名1 数据类型1,
列名2 数据类型2,
...
列名n 数据类型n
);
#注意：最后一列，不需要加逗号(,)
常见数据库类型：
1.int :整数类型 age int,
2.double:小数类型 score double(5,2) 指定小数最多有五位，但是保留2位小数
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(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
);
复制表：create table 表名 like 被复制的表名（无法复制表中的内容，是一张空表）
2.R(Retrieve):查询
查询某个数据库中所有的表名称：show tables;
查询表结构：desc 表名;
3.U(Update):修改
修改表名：alter table 表名 rename to 新的表名
修改表的字符集：alter database 表名称 character set 字符集名称;---->alter table s character set utf8
添加一列：alter table 表名 add 列名 数据类型;
修改列名称 类型:alter table 表名 change 被修改的列名 新的列名 新的类型 #这个方法既可以该列名又可以改类型，
将change改成modify只能修改数据类型
删除列 ：alter table 表名 drop 列名
4.D(Delete):删除
删除表：drop table 表名;
判断表存在，存在再删除：drop table if exists 表名;
5.使用数据库
查询当前正在使用的数据库名称：select database();
使用数据库：use 数据库名称;
客户端图形化工具：SQLYog
DML:增删改表中数据
1.添加数据：
语法：insert into 表名(列名1,列名2...列名n) values(值1,值2,...值n);
注意：
1.列名和值要一一对应。
2.如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3.除了数字类型，其他类型需要使用(单双都可以)引号引起来
2.删除数据：
语法：delete from 表名 [where 条件];
注意：
1.如果不加条件,则删除表中所有记录。
2.如果要删除所有记录
a.delete from 表名; #不推荐使用，有多少记录就会执行 多少次删除操作
b.truncate table 表名; #推荐使用，效率更高先删除表，然后在创建一张一样的空表
3.修改数据：
语法：
update 表名 set 列名1 =值1,列名2=值2,....列名n=值n [where 条件]
注意：
1.如果不加任何条件，会将表中所有记录全部修改
DQL:查询表中记录
1.语法：
select 字段列表
from 表名列表
where 条件列表
group by 分组字段
having 分组之后的条件限定
order by 排序
limit 分页限定
2.基础查询
1.多个字段的查询：select 字段名1,字段名2....from 表名;
注意：如果查询所有字段，则可以使用*来替代字段的列表。
2.去除重复:：distinct
3.计算列:一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
ifnull(表达式1,表达式2)：
表达式1：哪个字段需要判断是否为null,
表达式2:该字段为null后的替换值。
4.起别名
as:as也可以省略。
例子：
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);
TRUNCATE TABLE student3;
SELECT * FROM student3;
-- 去除重复的结果集
SELECT DISTINCT address FROM student3;
SELECT NAME,address FROM student3;
-- 计算math 和english之和
SELECT NAME,math,english,english+math FROM student3;
-- 如果有null参与的计算，计算结果都为null
SELECT NAME,math,english,math+IFNULL(english,0) FROM student3;
-- 起别名
SELECT NAME,math,english,math+IFNULL(english,0) AS 总分 FROM student3;
SELECT NAME,math 数学,english 英语,math+IFNULL(english,0) 总分 FROM student3;
3.条件查询
1.where句子后面跟条件
2.运算符
*>、<、<=、>=、<>
*between...and
*in(集合)
*LIKE：模糊查询
占位符：_:单个任意字符 %：0-多个任意字符
is null
and 或 && or 或 || not 或 ！
DQL:查询语句
1.排序查询
语法：order by 子句。
order by 排序字段1,排序方式2, 排序字段2 排序方式2....
排序方式：
ASC：升序，默认的
DESC：降序
如果有多个排序条件，则当前边的条件值一样时，才回去判断第二条件
2.聚合函数：将一列数据作为一个整体，进行纵向的计算。
1.count:计算个数 #1.选择非空的列：主键 #2.count(*)
2.max:计算最大值 min；计算最小值
3.sum:计算和
4.avg:计算平均值
注意：聚合函数的计算，是排除null值
解决方案：1.选择不包括非空的列进行计算 2.IFNULL函数
3.分组查询
1.语法：group by 分组字段;
2.注意：
1.分组之后查询的字段：分组字段、聚合函数
2.where 和having的区别？
where在分组之前进行限定，如果不满足条件则不进行分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
where后不能跟聚合函数，having可以进行聚合函数的判断
4.分页查询
1.语法：limit开始的索引,每页查询的条数
2.公式:开始的索引 =(当前的页码-1)*每页显示的条数
3.limit分页是一个MySQL“方言”。
例子：
SELECT * FROM student3 ORDER BY math DESC-- 排序方法;
-- 按照数学成绩排名，如果数学成绩一致，则按英语成绩排名
SELECT * FROM student3 ORDER BY math ,english ;
SELECT COUNT(NAME)AS 总人数 FROM student3;
SELECT COUNT(NAME)AS 总人数 FROM student3;
SELECT COUNT(IFNULL(english ,0)) FROM student3;
-- 按照性别分组，分别查询男、女的平均分
SELECT sex,AVG(math),AVG(english),COUNT(id) FROM student3 GROUP BY sex;
-- 按照性别分组，分别查询男、女的平均分要求：分数低于70分的人，不参与分组
SELECT sex,AVG(math),AVG(english),COUNT(id) FROM student3 WHERE math>70 GROUP BY sex;
-- 按照性别分组，分别查询男、女的平均分要求：分数低于70分的人，不参与分组,分组之后，人数要大于2人
SELECT sex,AVG(math),AVG(english),COUNT(id) FROM student3 WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;
-- 按照性别分组，分别查询男、女的平均分要求：分数低于70分的人，不参与分组,分组之后，人数要大于2人
SELECT sex,AVG(math),AVG(english),COUNT(id) 人数 FROM student3 WHERE math>70 GROUP BY sex HAVING 人数>2;
-- 每页显示三条记录
SELECT * FROM student3 LIMIT 0,3; -- 第一页
SELECT * FROM student3 LIMIT 3,3; -- 第2页
-- 公式:开始的索引 =(当前的页码-1)*每页显示的条数
约束
概念：对表中数据进行限定，保证数据的正确性、有效性和完整性。
分类：
1.主键约束：primary key
2.非空约束：not null
3.唯一约束;unique
4.外键约束;foreign key
1.非空约束：not null，值不能为null
1.创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
2.删除name 的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
3.创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) Not Null;
2.唯一约束：unique,值不能重复。
1. 创建表时，添加唯一约束
CREATE TABLE stu(
id INT,
phone_num VARCHAR(20) UNIQUE -- 添加了唯一约束
);
-- 注意msq中唯一约束限定的列的值，可以有多个null
2. 删除唯一约束
-- alter table stu modify phone_num varchar(20);没用
ALTER TABLE stu DROP INDEX phone_num;
3.在创建表之后，添加唯一约束
ALTER TABLE stu MODIFY phone_num VARCHAR(20) UNIQUE;
3.主键约束：primary key
注意：
1.含义：非空且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识
1.在创建表时，添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, -- 给id添加主键约束
NAME VARCHAR(20)
);
SELECT * FROM stu;
2.删除主键
-- 错误的方式alter table stu modify id int,
ALTER TABLE stu DROP PRIMARY KEY;
3.创建完表后，添加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长一般和主键一起使用
1.概念：如果某一列是数值类型的，使用auto_increment 可以来完成值的自动增长
2.在创建表时，添加主键约束，并完成主键自增长
id int primary key auto_increment
3.删除自动增长。alter table stu modify id int
3.建表后添加自动增长。alter table stu modify id int auto _increment
4.外键约束：foreign key，让表与表产生关系，从而保证数据的正确性
1.在创建表时，可以添加外键
create table 表名(
...
外键列
constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)
-- 例子：CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id);
)
2.删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;
3.添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id);
4.级联操作
添加级联操作：alter table 表名 add constraint 外键名称 foreign key 外键列名称 references 主键名称(主键列名称)
分类：
级联更新：on update cascade
级联删除：on delete cascade
-- 添加外键,并设置级联更新
ALTER TABLE employee ADD CONSTRAINT e FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE; -- cascader
-- 添加外键,并设置级联更新,设置级联删除
ALTER TABLE employee ADD CONSTRAINT e FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE -- cascade
ON DELETE CASCADE ;
注意：关联外键约束时，必须要现有主表
数据库的设计
1.分类
案例:分类->旅游路线<|>用户
-- 创建旅游线路分类表tab_category
-- cid旅游线路分类主键，自动增长
-- cname旅游线路分类名称非空，唯一，字符串100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
) ;
-- 添加旅游线路分类数据：
INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');
-- select * from tab_category;
-- 创建旅游线路表tab_route
/* rid旅游线路主键，
自动增长 rname旅游线路名称非空，唯一，
字符串100 price价格 rdate
上架时间，日期类型 cid 外键，所属分类 */
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)
) ;
-- 添加旅游线路数据
INSERT INTO tab_route VALUES (NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁3天 惠贵团】尝味友鸭面线 住1晚鼓浪屿', 1499, '2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁3天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),
(NULL, '【爆款￥1699秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州 往返 特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典•狮航 ￥2399秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23', 2),
(NULL, '香港迪士尼乐园自由行2天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店 暨会议中心标准房1晚住宿】', 799, '2018-04-10', 4);
/*创建用户表tab_user uid用户主键，
自增长 username用户名长度100，唯一，非空
password密码长度30，非空
name真实姓名长度100
birthday生日
sex性别，定长字符串1
telephone手机号，字符串11
email邮箱，字符串长度100 */
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)
) ;
-- 添加用户数据
INSERT INTO tab_user VALUES (NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
-- select * from tab_user;
/*创建收藏表tab_favorite
rid 旅游线路
id，外键
date 收藏时间
uid用户id，外键 rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次 */
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)
) ;
-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(6, '2018-01-01', 1), -- 老王选择厦门
(6, '2018-02-11', 2), -- 老王选择桂林
(7, '2018-03-21', 1), -- 老王选择泰国
(8, '2018-04-21', 2), -- 小王选择桂林
(9, '2018-05-08', 1), -- 小王选择泰国
(10, '2018-06-02', 2); -- 小王选择迪士尼
SELECT * FROM tab_category;
2.数据库设计的范式
概念：
设计关系型数据库时，遵从不同的规范要求，设计出合理的关系型数据库。要遵循后面的范式，必须先遵循前边的范式的所有要求。
这些规范被称作范式。越高的范式数据库的冗余度就越低。
关系数据库中的关系必须满足一定的要求，即满足不同的范式。
关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式（4NF）和第五范式（5NF）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式（3NF）就行了。
分类：
1.第一范式：数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项
2.第二范式：在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.码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
例如：该表的码为 (学号,课程名称)
主属性：码属性组中的所有属性
非主属性：除码属性组的属性
3.第三范式：属性不能传递依赖于主属性（属性不依赖于其它非主键属性）即消除传递依赖
数据库的备份和还原
1.命令行：：
语法:
备份：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径（d://a.sql）
还原：
1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件。source文件路径
2.图形化工具：
多表查询
数据准备：
# 创建部门表
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, -- 入职日期
ept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
) ;
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,'200808-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);
笛卡尔积：
有两个集合A,B 去这两个集合的所有组成情况。
要完成多表查询，需要消除无用数据。
多表查询的分类：
- 1.内连接查询
1.隐式内连接:使用where条件消除无用的数据
SELECT * FROM emp,dept WHERE emp.`ept_id`=dept.`id`;
-- 查询员工表的名称,性别，部门表名称
SELECT emp.`id`,emp.`name`,dept.`name` FROM emp,dept WHERE emp.`ept_id`=dept.`id`;
-- 使用别名替换表名
SELECT t1.`id`,t1.`name`,t2.`name` FROM emp t1,dept t2 WHERE t1.`ept_id`=t2.`id`;
2.显式内连接: （语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件）
SELECT * FROM emp INNER JOIN dept ON emp.`ept_id` = dept.`id`;-- inner 可以 省略
SELECT * FROM emp JOIN dept ON emp.`ept_id` = dept.`id`;
3.内连接查询：
·从哪些表中查询
·条件是什么
·查询那些字段
- 2.外连接查
1.左外连接;（语法: select 字段列表 from 表1 left [outer] join 表2 on 条件;）
查询的是左表所有数据以及其交集(满足条件)部分
2.右外连接;（语法: select 字段列表 from 表1 right [outer] join 表2 on 条件;）
查询的是右表的所有记录和左右表交集的部分
SELECT * FROM emp INNER JOIN dept ON emp.`ept_id` = dept.`id`;-- inner 可以 省略
SELECT * FROM emp JOIN dept ON emp.`ept_id` = dept.`id`;
SELECT * FROM emp;
SELECT t1.*,t2.`name` NAME FROM emp t1 LEFT JOIN dept t2 ON t1.`ept_id`=t2.`id`;
SELECT t1.*,t2.`name` FROM emp t1 RIGHT JOIN dept t2 ON t1.`ept_id`=t2.`id`;
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`ept_id`=t2.`id`;
- 3.子查询
概念：查询中嵌套查询，称嵌套查询为子查询。
例子：
-- 查询工资最高的员工信息
-- 1.查询工资最高是多少 7200
SELECT MAX(salary) FROM emo;
-- 查询员工信息，并且工资等于最高 7200
SELECT * FROM emp WHERE emp.`salary` =7200 ;
-- 一条语句完成
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 * FROM emp WHERE emp.`ept_id` IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部' );
.子查询的结果是多行多列的
·子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT * FROM emp WHERE emp.`join_date`>'2011-11-11';
SELECT * FROM dept t1,( SELECT * FROM emp WHERE emp.`join_date`>'2011-11-11') t2 WHERE t1.`id` = t2.ept_id;
案例
-- 部门表
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 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`,t1.`salary`,t2.*
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.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`
AND t1.`dept_id`=t3.`id`
AND t1.`job_id` =t2.`id`;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
分析： 1.部门编号 部门名称 部门位置 都在 dept中 部门人数 emp中
2.使用分组查询。按照emp.dept_id完成分组，查询count(id)
3.使用子查询将第二步的查询结果和dept表进行关联查询
*/
SELECT
t2.`id`,t2.`dname`,t2.`loc`,t1.`num`
FROM dept t2,(SELECT emp.`dept_id`, COUNT(id) num FROM emp GROUP BY emp.`dept_id`) t1
WHERE t2.`id` = t1.`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,emp t2
where t1.`mgr` = t2.`id`;
*/
SELECT
t1.`ename`,
t1.`mgr`,
t2.`id`,
t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr` = t2.`id`;
事务
1.事务的基本介绍
- 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
- 操作：
1.开启事务：start transaction
2.回滚：rollback
3.提交：commit
4.MySQL数据库中默认自动提交
·事务提交的两种方式：
·自动提交：mysql就是自动提交的，·一条DML(增删改)语句会自动提交一次事务。
·手动提交：需要先开启事务，再提交 (Oracle 数据库是默认手动提交事务的)
·修改事务的默认提交方式：
·查看事务的默认提交方式：select @@autocommit; -- 1代表自动提交 0代表手动
·修改默认提交方式：set @@autocommit = 0;
2.事务的四大特征：
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
- 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
- 隔离性：多个事务之间。相互独立。
- 一致性：事务操作前后，数据总量不变
3.事务的隔离级别
概念：
多个事务之间隔离的，相互独立的。但如果多个数据操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
存在问题：
- 脏读：一个事务，读取到另一个事务中没有提交的数据。
- 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作(DML)数据表中所有记录另一个事务添加了一条数据，则第一个事务查询不到自己的修改 。
隔离级别：
- 1.read uncommitted:读未提交
产生问题：脏读、不可重复读、幻读 - 2.read committed：读已提交的
产生的问题，不可重复度、幻读 - 3.repeatable read:可重复度
幻读MySQL默认 - 4.serializable:串行化
可以解决所有问题
注意：隔离级别从小到大安全性越来高，但是效率越来越低
数据库查询隔离级别：select @@tx_isolation
数据库设置隔离解绑:set global transaction isolation level 级别字符串
DCL:
- SQL分类：
1.DDL：操作数据库和表。
2.DML：增删改表中数据。
3.QDL：查询表中数据
4.DCL：管理用户，授权。
*DBA：数据库管理员
DCL:管理用户，授权
管理用户
- 1.添加用户：
create user '用户名'@'主机名' identifide by '密码' - 2.删除用户:
drop user '用户名'@'主机名' - 3.修改用户密码：
UPdate user set password = password ('新密码') where user ='用户名'；
还有一种set password for '用户名'@'主机名'=password(''新密码)
mysql中忘记了root用户的密码？
1.cmd - -> net stop mysql （停止mysql服务，需要使用管理员权限运行cmd)
2.使用无验证方式启动mysql服务：mysql --skip-grant-tables
3.打开新的cmd窗口，直接输入mysql命令，敲回车。就可以登陆成功
4.user mysql;
5.update user set password = password(‘你的新密码’) where user = ‘root’;
6.关闭 两个窗口
7.打开任务管理器，手动结束mysql.exe 的进程
8.启动mysql服务
9.使用新密码登录 - 4.查询用户：
1.切换到mysql数据库 use mysql; 2.查询user表 select * from user ;
注意：主机名可以改为%(通配符，表示可以在任意主机使用用户登录数据库)
授权
- 查询权限：
show grants for '用户名'@'主机名'; - 授予权限：
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';所有权限grant * on *.* to '用户名'@'主机名'; - 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
扫一扫
56万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
