文章目录
数据库(DataBase)
常见的数据库管理系统
- Oracle（全球最强大的数据库管理系统，收费，政府或者银行以及相关行业常用）
- MySQL（免费开源，互联网公司常用）
- DB2
- Sybase
- Microsoft SqlServer（支持标准sql的数据库管理系统）
MySQL的安装步骤
- 安装默认路径：C:\Program Files (x86)\MySQL
- 一直默认点到该页面
- 根据需求选择是在什么机器上进行开发
- 对事务的支持
- 默认安装位置
- 默认
-
端口设置（计算机网络知识）
-
选择字符集
-
设置Service Name（服务名称），
- 设置超管密码
- enable root access from remote machines（远程访问）
- execute
查看服务
登录MySQL
- 按住win+R 运行 cmd
- 输入
mysql -u+用户名 + -p密码- 比如：mysql -uroot -p123
- 或者输入
mysql -u+用户名 + -p- 这样密码会以" ***** "表示
登陆成功
退出MySQL
exitquit
修改密码
方法1： 用SET PASSWORD命令
-
首先登录MySQL。
-
格式：
- mysql> set password for 用户名@localhost = password(‘新密码’);
-
例子：
- mysql> set password for root@localhost = password(‘123’);
方法2：用mysqladmin
- 格式：
- mysqladmin -u用户名 -p旧密码 password 新密码
- 例子：
- mysqladmin -uroot -p123456 password 123
方法3：用UPDATE直接编辑user表
- 首先登录MySQL。
- mysql> use mysql;
- mysql> update user set password=password(‘123’) where
- user=‘root’ and host=‘localhost’;
- mysql> flush privileges;
方法4：在忘记root密码的时候
- 以windows为例：
- 关闭正在运行的MySQL服务。
- 打开DOS窗口，转到mysql\bin目录。
- 输入mysqld --skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
- 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
- 输入mysql回车，如果成功，将出现MySQL提示符 >。
- 连接权限数据库： use mysql; 。
- 改密码：update user set password=password(“123”) where user=“root”;（别忘了最后加分号） 。
- 刷新权限（必须步骤）：flush privileges; 。
- 退出 quit。
- 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。
参考资料：https://www.bilibili.com/video/BV1fx411X7BD?p=4
卸载mySQL
- 打开最开始的安装程序
- 选择remove
- 将C:\Program Files (x86)\MySQL 删除
- 将C:\ProgramData\MySQL（隐藏目录） 删除
MySQL目录结构
安装目录
- 配置文件 my.ini
数据目录
- 几个概念
- 数据库：文件夹
- 表：文件
- 数据
SQL
- **SQL **全称：结构化查询语言(Structured Query Language)
- SQL用来和数据库进行通信
- SQL是一套标准，大多数数据库的SQL是通用的
- 每一个数据库管理系统都有自己的一些特性，当使用这个特性的时候，SQL语句就可能不是标准了
SQL通用语法
-
SQL 语句可以单行或多行书写，以 分号结尾
-
可使用 空格和缩进 来增强语句的可读性
-
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
-
三种注释
-
单行注释：
--+空格 + 注释内容 或#+ 注释内容-
SHOW databases; – 查询所有数据库名称
SHOW databases; #查询所有数据库名称
-
-
多行注释：
/* 注释 */
-
SQL分类
-
DDL（Data Definition Language）数据定义语言- 用来定义数据库对象：数据库，表，列等
- 关键字：create、drop、alter 等
-
DML（Data Manipulation Language）数据操作语言- 用来对数据库中的表的数据进行增删改
- 关键字：insert、delete、update 等
-
DQL（Data Query Language）数据查询语言
- 用来查询数据库中表的记录（数据）
- 关键字：select、where 等
-
DCL（Data Control Language）数据控制语言（了解）- 用来定义数据库的访问权限和安全级别，以及创建用户
- 关键字：GRANT、REVOKE 等
参考资料：https://www.bilibili.com/video/BV1qv4y1o79t
DDL:操作数据库(CRUD)
- DDL（Data Definition Language），即数据定义语言，例如建数据库、建表等，都属于数据定义语言，也是今天本文的主角；
C（create）：创建
-
创建数据库
-
create database 数据库名称; -
-
-
创建数据库，判断是否存在
-
create database if not exists 数据库名称; -
-
-
创建数据库，并指定字符集
-
create database 数据库名称 character set 字符集名; -
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vqKW8O7h-1646631186925)(D:\Learning\学习笔记\Java\JavaWeb\数据库\数据库.assets\image-20220119231407355.png)]
-
-
练习：创建db1数据库，判断是否存在，并且制定字符集为gbk
-
create database if not exists db1 character set gbk; -
-
R（Retrieve）：查询
-
查询所有数据库的名称
-
show databases; -
-
-
查询某个数据库的字符集：查询某个数据库的创建语句
-
show create database 数据库名称 -
-
U（Update）：修改
-
修改数据库的字符集
-
alter database 数据库名称 character set 字符集名; -
-
D（Delete）：删除
-
删除数据库
-
drop database 数据库名称; -
-
-
判断数据库存在，存在则删除
-
drop database if exists 数据库名称; -
-
使用数据库
-
查询正在使用的数据库名称
-
select database(); -
-
-
使用数据库
-
use 数据库名称; -
-
DDL:操作表
C（create）：创建
-
语法
-
create table 表名( 列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n );注意：最后一列，不需要加逗号（ , ）
-
-
数据类型：
-
int：整数类型-
age int
-
-
double：小数类型-
mysql score double(5,2) -- 最多有5位，保留小数点两位
-
-
date：日期，只包含年月日，yyyy-MM-dd -
datetime：日期，包含年月日时分，yyyy-MM-dd HH:mm:ss -
timestamp：时间戳类型，包含年月日时分，yyyy-MM-dd HH:mm:ss-
如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
-
-
varchar：字符串-
name varchar(20); -- 姓名最大20个字符
-
-
-
复制表：
- create table 表名 like 被复制的表名
- create table 表名 like 被复制的表名
-
示例：创建下图对应的表
-
-
create table student( id int, name varchar(32), age int, score double(4,1), birthday date, insert_time timestamp ); -
-
R（Retrieve）：查询
-
查询某个数据库中所有表的名称
-
show tables;
-
-
查询表结构
-
desc 表名
-
U（Update）：修改
-
修改表名
-
alter table 表名 rename to 新的表名;
-
-
修改表的字符集
-
alter table 表名 character set 字符集名称; -
-
-
添加一列
-
alter table 表名 add 列名 数据类型
-
-
修改列名称 类型
-
alter table 表名 change 列名 新列名 新数据类型; -- 修改列名称 + 数据类型 -
alter table 表名 modify 列名 新数据类型; -- 修改数据类型
-
-
删除列
-
alter table 表名 drop 列名;
-
D（Delete）：删除
-
drop table 表名; -
drop table if exists 表名;
客户端图形化工具：SQLyog
DML：增删改表中的数据
- DML（Data Manipulation Language），即数据操纵语言，最常用的增删改查就属于DML，操作对象是数据表中的记录；
添加数据
-
语法
-
insert into 表名(列名，列名，....列名n) values(值1，值2，...值n);
-
-
注意：
-
列名和值要一一对应
-
如果表名后，不定义列名，则默认给所有列添加值
-
insert into 表名 values(值1，值2，...值n);
-
-
除了数字类型，其他类型需要使用引号（单双都可以）引起来
-
删除数据
-
语法
-
delete from 表名 (where 条件); -
注意：
- 如果不加条件，则删除表中的所有记录
- 如果要删除所有记录
-
delete from 表名; – 不推荐使用。有多少记录就会执行多少次删除操作，效率低
-
truncate table 表名; – 推荐使用。先删除表，再创建一张一模一样的空表，效率高
-
-
修改数据
-
语法
-
update 表名 set 列名1 = 值1, 列名2 = 值2,...(where 条件);
-
-
注意
- 如果不加任何条件，则会将表中所有记录 全部修改
- 如果不加任何条件，则会将表中所有记录 全部修改
DQL：查询表中的数据
- DQL（Data Query Language）
语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
基础查询
-
准备数据：
-
CREATE TABLE student ( id int, -- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math int, -- 数学 english int -- 英语 ); INSERT INTO student(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);
-
-
多个字段的查询
-
select 字段名1，字段名2... from 表名; -
注意：
- 如果查询所有字段，可以使用 ***** 来替代字段列表
- 如果查询所有字段，可以使用 ***** 来替代字段列表
-
-
去除重复
-
distinct
-
-
计算列
-
一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
-
ifnull(表达式1，表达式2)使用原因：null参与的运算，计算结果都为null- 表达式1：哪个字段需要判断是否为 null
- 表达式2：该字段为 null 后的 替换值
-
-
起别名
-
as：可省略
-
条件查询
-
select * from 表名 where 条件 -
运算符
-
>、<、<=、>=、=、<>
-
BETWEEN…AND
-
BETWEEN 值 1 AND 值 2
-
-
IN(集合)
-
SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
-
-
LIKE
-
占位符
_：单个任意字符%：多个任意字符
-
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
-
-
IS NULL
-
SELECT * FROM 表名 WHERE 字段名 IS NULL;
-
-
and 或 &&
-
SELECT * FROM 表名 WHERE 条件1 AND 条件2 ...;
-
-
or 或 ||
-
SELECT * FROM 表名 WHERE 条件1 OR 条件2 ...;
-
-
not 或 !
-
排序查询
-
语法
-
排序方式
ASC：升序，默认DESC：降序
-
order by 排序字段1 排序方式1, 排序字段2 排序方式2 ... -
注意：如果有多个排序条件，则只有前面的条件值相等时，才会进行第二条件判断
-
-
聚合函数
- 聚合函数：将一列数据作为一个整体，进行逆向的计算
-
count：计算个数-
一般选择非空的列：主键
-
count(*)
-
-
max：计算最大值 -
min：计算最小值 -
sum：计算和 -
avg：计算平均值
-
- 注意：聚合函数的计算，排除 null 值
- 解决方案：
-
选择不包含空的列进行计算
-
IFNULL函数
-
- 解决方案：
分组查询
-
语法
-
group by 分组字段
-
- 注意：
- where 和 having 的区别？
- where 在分组之前进行限定，如果不满足条件，则不参与分组。
- having 在分组之后进行限定，如果不满足条件，则不会被查询出来
- where 后不可以跟聚合函数，having 后可以进行聚合函数的判断
- where 和 having 的区别？
分页查询
-
语法：
-
limit 开始的索引，每页查询的条数
-
-
公式：开始的索引 = （当前的页码 - 1）* 每页显示的条数
-
limit 是一个“方言”（不具有通用性，只能在MySQL上使用）
约束
- 概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
主键约束
- 主键约束：primary key
- 注意
- 含义：非空且唯一
- 一张表 只能 有一个字段为 主键
- 主键 就是表中记录的 唯一标识
-
在创建表时，添加主键约束
-
create table stu( 需要设置主键的列名 int primary key, -- 给“需要设置主键的列” 添加主键约束 name varchar(20) );-
-
说明添加 主键约束 成功
-
-
-
删除主键
-
ALTER TABLE 表名 DROP PRIMARY KEY;
-
-
创建完表之后，添加主键
-
ALTER TABLE 表名 MODIFY 需要添加的列名 INT PRIMARY KEY;
-
-
自动增长
-
概念：如果某一列是数值类型的，使用
auto_increment可以来完成值的自动增长 -
在创建表时，添加主键约束，并且完成 主键自增长
-
CREATE TABLE stu( id INT PRIMARY KEY AUTO_INCREMENT, -- 给 id 添加主键约束 NAME VARCHAR(20) );
-
-
删除 自动增长
-
ALTER TABLE 表名 MODIFY 主键 INT;
-
-
添加 自动增长
-
ALTER TABLE 表名 MODIFY 主键 INT AUTO_INCREMENT;
-
-
非空约束
- 非空约束：not null
-
在创建表时添加约束
-
-
实现了 name 不能为 null 的约束
-
-
创建表之后，添加非空约束
-
唯一约束
-
唯一约束：unique，某一列的值不能重复
-
在创建表时，添加 唯一约束
-
删除 唯一约束
-
在表添加完后，添加 唯一约束
-
-
注意：唯一约束 可以有 null 值，但是只能有一条记录为 null
外键约束
-
外键约束：foreign key，让表与表之间产生关系，从而保证数据的正确性
-
准备数据
-
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- 添加数据 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
- 数据冗余
- 后期还会出现增删改的问题
-
-
解决办法：
-
-- 解决方案：分成 2 张表 -- 创建部门表(id,dep_name,dep_location) -- 一方，主表 CREATE TABLE department( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(20) ); -- 创建员工表(id,name,age,dep_id) -- 多方，从表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT -- 外键对应主表的主键 ) -- 添加 2 个部门 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); SELECT * FROM department; -- 添加员工,dep_id 表示员工所在的部门 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
-
-
在创建表时，添加外键
-
语法
-
CREATE TABLE 表名( ... 外键列, CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名(主表列名称); );
-
-
-
删除 外键
-
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
-
-
添加 外键
-
ALTER TABLE 表名 ADD CONSTRAINT 外键 FOREIGN KEY (外键列名称) REFERENCES 主表名(主表列名称);
-
-
级联操作：级联操作 是存在于主表 (父表)和从表 (子表)关系之中，当 操作 了主表中的数据则要求子表中的数据也随之更新或者删除，这样的 操作 叫做 级联操作
-
添加 级联操作
-
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表列名称) ON UPDATE ON DELETE CASCADE;
-
-
分类
级联更新：ON UPDATE CASCADE级联删除：ON DELETE CASCADE
-
数据库的设计
多表之间的关系
一对一（了解）
- 例如：人和身份证
- 分析：一个人只有一个身份证，一个身份证也只能对应一个人
- 实现方式：一对一关系实现，可以在任意一方添加 唯一外键(UNIQUE) 指向另一方的主键
一对多（多对一）
-
例如：部门和员工
-
分析：一个部门可以有多个员工，一个员工只能属于一个部门
-
实现方式：在多的一方建立外键，指向一的一方的主键
多对多
- 例如：学生和课程
- 分析：一个学生可以选择多门课程，一个课程可以被多个学生选择
案例
-
-- 创建旅游线路分类表 tab_category -- cid 旅游线路分类主键，自动增长 -- cname 旅游线路分类名称非空，唯一，字符串 100 CREATE TABLE tab_category( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(100) UNIQUE NOT NULL ); -- 添加旅游线路分类数据： 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) UNIQUE NOT NULL, 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); SELECT * FROM tab_route; /* 创建用户表 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 (1, '2018-01-01', 1), -- 老王选择厦门 (2, '2018-02-11', 1), -- 老王选择桂林 (3, '2018-03-21', 1), -- 老王选择泰国 (2, '2018-04-21', 2), -- 小王选择桂林 (3, '2018-05-08', 2), -- 小王选择泰国 (5, '2018-06-02', 2); -- 小王选择迪士尼 SELECT * FROM tab_favorite; -
范式
- 概念：好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
- 六种范式：
- 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF、第四范式(4NF）和第五范式（5NF，又称完美范式）
第一范式（1NF）
-
第一范式（1NF）：- 数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。
- 简而言之，第一范式每一列 不可再拆分，称为 原子性。
-
例如
- 该表存在的问题：
- 存在非常严重的数据冗余（重复）问题：姓名、系名、系主任
- 数据添加存在问题：添加新开设的系和系主任时，数据不合法（原因是学号和其他列为空）
- 数据删除存在问题：某同学毕业了，删除数据时会将系的数据一并删除（系不会因为学生的毕业而删除）
第二范式（2NF）
第二范式（2NF）：在满足第一范式的前提下，表中的每一个字段都完全依赖于主键- 简而言之，第二范式就是在第一范式的基础上 所有列 完全依赖于 主键列。
函数依赖：A —> B，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称 B依赖于A- 例如：学号 —> 姓名
属性组：（A，C）—> B，A、C称为属性组完全函数依赖：A —> B，如果A是一个 属性组，则B属性的确定需要依赖于A属性组所有的属性值- 例如：（学号，课程名称）—> 分数
部分函数依赖：A —> B，如果A是一个 属性组，则B属性的确定只需要依赖于A属性组的部分属性- 例如：（学号，课程名称）—> 姓名，只需要学号就可以确认姓名
传递依赖：A —> B，B —> C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递依赖于 A- 例如：学号 —> 系名，系名 —> 系主任
码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的 码- 例如：上述的表中 码 为：（学号，课程名称）
主属性：码属性组中的所有属性非主属性：除了码属性的属性
第三范式（3NF）
第三范式（3NF）：在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键- 简而言之，第三范式就是所有列 不依赖 于其它 非主键列 ，也就是在满足 2NF 的基础上，任何非主列不得 传递依赖 于 主键
数据库的备份与还原
-
命令行
-
语法
-
备份
-
mysqldump -u用户名 -u密码 数据库名称 > 保存的路径
-
-
还原
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件（source 文件路径）
-
-
-
图形化工具
-
备份
-
-
还原
-
-
多表查询
-
准备数据
-
# 创建部门表 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('孙悟空','男',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);
-
-
笛卡尔积：- 笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尔积（Cartesian product），又称直积，表示为X × Y （即取这两个集合的所有组合情况）
- 要完成 多表查询，需要消除无用的数据
内连接查询
-
隐式内连接：使用 where 条件消除无用数据
-
例子
-
-- 查询员工表的名称、性别、部门 select t1.name, -- 员工表的名称 t1.gender, -- 员工表的性别 t2.id -- 部门表的名称 from emp t1, dept t2 where t1.dept_id = t2.id; -
-
-
-
显式内连接：
-
语法（INNER可省略）
-
SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 条件;
-
-
- 注意：
- 从哪些表查询数据
- 条件是什么
- 查询哪些字段
外链接查询
-
左外连接：
-
语法
-
select 字段列表 from 表1 left outer(可省略) join 表2 on 条件;
-
-
查询的是左表 所有数据 以及其 交集 部分
-
-
右外连接：
-
语法
-
select 字段列表 from 表1 right outer(可省略) join 表2 on 条件;
-
-
子查询
-
概念：查询中 嵌套查询，称为子查询
-
例子
-
-- 查询工资最高的员工消息 -- 1 查询最高的工资 SELECT MAX(salary) FROM emp; -- 2 查询员工信息，并且工资为9000 SELECT * FROM emp WHERE emp.salary = 9000; -- 子查询 SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
-
-
-
子查询的不同情况
-
查询结果 单行单列：
-
子查询 可以作为条件，使用 运算符 去判断
-
例子：
-
-- 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
-
-
-
-
查询结果 多行单列：
-
子查询可以作为条件，使用运算符 in 来判断
-
例子：
-
-- 查询'财务部'和'市场部'所有的员工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
-
-
-
-
查询结果 多行多列
-
子查询可以作为一张 虚拟表
-
例如：
-
-- 查询员工入职日期是2011-11-11之后的员工信息和部门信息 SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id; -- 使用内连接 SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11';
-
-
-
-
练习
-
-- 部门表 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); SELECT * FROM dept; SELECT * FROM emp; SELECT * FROM job; SELECT * FROM salarygrade; -- 需求： -- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述 /* 分析 1. 员工编号，员工姓名，工资 利用emp表查询 职务名称，职务描述 利用job表 2. 查询条件：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.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置 /* 分析 1.员工编号，员工姓名，工资 emp表 职务名称，职务描述 job表 部门名称，部门位置 dept表 2.查询条件：emp.job_id = job.id and emp.dept_id = dept.id */ 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.查询员工姓名，工资，工资等级 /* 分析 1.员工姓名，工资 emp表 工资等级 salarygrade 2.查询条件：emp.salary between salarygrade.losalary and hisalary */ SELECT t1.ename, -- 员工姓名 t1.salary, -- 员工工资 t2.grade -- 工资等级 FROM emp t1,salarygrade t2 WHERE t1.salary BETWEEN t2.losalary AND t2.hisalary; -- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级 /* 分析 1.员工姓名，工资 emp 职务名称，职务描述 job 部门名称，部门位置 dept 工资等级 salarygrade 2.查询条件：emp.job_id = job.id and emp.dept_id = dept.id emp.salary between salarygrade.losalary and hisalary */ 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.查询出部门编号、部门名称、部门位置、部门人数 /* 分析 1.部门编号、部门名称、部门位置 dept 部门人数 emp 2.使用分组查询，按照emp.dept_id完成分组，查询count(id) 3.使用子查询将第2步的查询结果和dept表进行关联查询 */ SELECT t1.id,t1.dname,t1.loc,t2.total FROM dept t1, (SELECT dept_id,COUNT(dept_id) total FROM emp GROUP BY dept_id) t2 WHERE t1.id = t2.dept_id ; -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询 /* 分析 1.所有员工的姓名及其直接上级的姓名 emp 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;
事务
事务的基本介绍
- 概念：
- 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么 同时成功 ，要么 同时失败
-
操作：
-
开启事务：
-
start transaction
-
-
回滚：
-
rollback
- 提交：
-
commit
-
-
例子
-
-- 创建数据表 CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); UPDATE account SET balance = 1000; SELECT * FROM account; -- 0.开启事务 START TRANSACTION; -- 张三账号-500 UPDATE account SET balance = balance - 500 WHERE NAME='zhangsan'; -- 李四账号+500 -- 出错了 UPDATE account SET balance = balance + 500 WHERE NAME='lisi'; -- 发现没有问题，提交事务 COMMIT; -- 发现出问题了，回滚事务 ROLLBACK;
-
-
MySQL数据库中 事务默认自动提交
-
事务提交的两种方式
- 自动提交
- 执行一条 DML（增删改） 语句会自动提交一次事务
- 手动提交
- 需要 开启 事务，再提交
- Oracle 数据库是 手动提交 事务
- 自动提交
-
修改事务的 默认提交方式
-
查看 默认提交方式
-
SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
-
-
修改 默认提交方式
-
SET @@autocommit = 0；
-
-
-
事务的四大特征
- 原子性（Atomicity）：每个事务都是一个整体，不可再拆分，事务中所有的 SQL*语句要么都执行成功，要么都失败。
- 一致性（Consistency）：事务在 执行前 数据库的状态与 执行后 数据库的 状态保持一致。如：转账前2个人的总金额是 2000，转账后 2 个人总金额也是 2000
- 隔离性（Isolation）：事务与事务之间不应该 相互影响，执行时保持 隔离 的状态
- 持久性（Durability）：一旦事务 执行成功，对数据库的修改是 持久 的。就算关机，也能保存下来
事务的隔离级别
-
概念：多个事务之间相互隔离，相互独立。如果多个事务操作同一批数据，则可能会引发一些问题，设置不同的隔离级别可以解决相关问题
-
存在问题：
-
并发引发的问题 含义 脏读 一个事务读取到了另一个事务中尚未提交的数据 不可重复读（虚读） 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update 时引发的问题 幻读 一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题
-
-
隔离级别（隔离级别越高，安全性越好，性能越低）
-
级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别 1 读未提交 read uncommitted 是 是 是 2 读已提交 read committed 否 是 是 Oracle 和 SQL server 3 可重复读 repeatable read 否 否 是 MySQL 4 串行化 serializable 否 否 否
-
-
MySQL隔离级别相关 命令 数据库查询隔离级别 SELECT @@tx_isolation;
新版用：SELECT @@transaction_isolation数据库设置隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
演示
-
读未提交：
-
未提交
-
其它事务能读取
-
-
读已提交：
-
未提交
-
其它事务不能读取
-
当前提交后其它事务可以读取
-
-
- 可重复读:
-
当前提交后其它事务不能读取
-
-
其它事务提交后可以读取
-
- 串行化:
- 类似于 java中的锁
- 如果前一个事务 未提交，后一个事务就 不执行语句
DCL
- DBA：数据库管理员
- DCL（Data Control Language），数据控制语言，如Grant、Rollback等等，常见于数据库安全管理，多数人一般很少用。
用户管理
-
添加用户
-
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
-
删除用户
-
DROP USER '用户名'@'主机名';
-
-
修改用户密码
-
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
-
-
查询用户
-
-- 切换到mysql数据库 USE mysql; -- 查询user表 SELECT * FROM USER; -
通配符：
%表示可以在任意主机使用用户登录数据库
-
-
MySQL中忘记了root用户的密码
- cmd – > net stop mysql 停止mysql服务（需要管理员运行该cmd）
- 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
- 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
- use mysql;
- update user set password = password(‘你的新密码’) where user = ‘root’;
- 关闭两个窗口
- 打开任务管理器，手动结束mysqld.exe 的进程
- 启动mysql服务
- 使用新密码登录
权限管理
-
查询权限
-
-- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';
-
-
授予权限
-
-- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 给张三用户授予所有权限，在任意数据库任意表上 GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-
-
撤销权限
-
-- 撤销权限： revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
-
SQL、DB、DBMS
概念
-
DB:
- DataBase（数据库，数据库实际上在硬盘上是以文件的形式存在）
-
DBMS:
- DataBase Management System（数据库管理系统，常见的有：MySQL，Oracle，DB2，Sybase，sqlServer…）
-
SQL:
结构化查询语言。标准的sql适用于所有的数据库产品- SQL语句属于高级语言，同样在执行时，同样也是
先编译再执行
关系
- DBMS负责执行sql，通过sql来操作DB里面的数据
- DBMS -> SQL -> DB
表（table）
概念
- Table是数据库的基本组成单元，所有的数据都以表格的形式组织，这样做的目的是可读性强
组成
- 一个表包括行和列
- 行：被称为数据/记录(data)
- 列：被称为字段(column)
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
