数据库的基本概念
- 数据库的英文:DataBase, 简称DB
- 什么是数据库:用于存储和管理数据的仓库
- 数据库的特点:
- 持久化存储数据。其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库—SQL
MySQL软件
服务启动
- 手动
- cmd–>services.msc 打开服务的窗口
- 使用管理员打开cmd
- net start mysql:启动mysql服务
- net stop mysql:关闭mysql服务
登录
- cmd–>mysql -uroot -p密码
- cmd–>mysql -hip -uroot -p连接目标的密码
- cmd–>mysql --host=ip --user=root --password=连接目标的密码
退出
- exit
- quit
SQL
什么是SQL
Structured Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则
每一种数据库操作的方式存在不一样的地方,称为“方言”
SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- 可使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 3种注释
- 单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
- 多行注释:/* 注释 */
SQL分类
- DDL:数据定义语言 (Data Definition Language) 是SQL语言集中,负责数据结构定义与数据库对象定义的语言,由CREATE、ALTER与DROP三个语法所组成,最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始,现在被纳入 SQL 指令中作为其中一个子集。
- DML:数据操纵语言(Data Manipulation Language)是SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除。
- DQL:数据查询语言(Data Query Language,)是SQL语言中,负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有FROM,WHERE,GROUP BY,HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。
- DCL:数据控制语言 (Data Control Language) 在SQL语言中,是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。
DDL:操作数据库、表
操作数据库
-
C(Create):创建
-
创建数据库:
create database 数据库名称;
-
创建数据库,判断不存在再创建:
create database if not exists 数据库名称;
-
创建数据库,并指定字符集:
create database 数据库名称 character set 字符集名;
-
-
R(Retrieve):查询
-
查询所有数据库的名称:
show databases;
-
查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
-
-
U(Update):修改
-
修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
-
-
D(Delete):删除
-
删除数据库
drop database 数据库名称;
-
判断数据库存在再删除
drop database if exists 数据库名称;
-
-
使用数据库
-
查询当前正在使用的数据库名称
select databse();
-
使用数据库
use 数据库名称;
-
操作表
-
C(Create):创建
-
创建表:
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
…
列名n 数据类型n
);
注意:最后一列不需要加逗号
数据类型:
-
int:整数类型
-
double:小数类型
scroe double(5, 2):表示最大长度为5,保留2位小数
-
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 被复制的表名;
-
-
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 表名;
DML:增删改表中数据
添加数据
语法:insert into 表名(列名1, 列名2, …列名n) values(值1,值2…值n);
注意:
- 列名和值要一一对应
- 如果表名后不定义列名,则默认给所有列添加值
- 除了数字类型,其他类型需要使用引号(单双都可以)
删除数据
语法:delete from 表名 where 条件;
注意:
- 如果不加条件,则删除表中所有记录
- 如果要删除所有记录
- delete from 表名; (不推荐使用,有多少条记录就会执行多少次删除操作,效率低)
- truncate table 表名; (推荐使用,效率更高,先删除表,然后再创建一张一样的表)
修改数据
语法:update 表名 set 列名1 = 值1, 列名2 = 值2…列名n = 值n where 条件;
注意:
- 如果不加任何条件,则会将表中所有记录全部修改
DQL:查询表中的记录
语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
基础查询
-
多个字段的查询
select 字段名1, 字段名2 …字段名n from 表名;
如果查询所有字段,则可以使用*来替代字段列表 select * from 表名;
-
去除重复
select distinct 字段名1, 字段名2 …字段名n from 表名;
-
计算列
一般可以使用四则运算计算一些列的值(一般只会进行数值型的计算)
ifnull(表达式1,表达式2)
表达式1:哪个字段需要判断是否为null
表达式2:如果表达式1字段为null,用表达式2替换
SELECT stuname, math , english , math + IFNULL(english, 0) FROM stu;
-
起别名
as(也可以省略)
SELECT stuname, math 数学, english 英语, math + IFNULL(english, 0) 总分 FROM stu;
条件查询
-
where后跟条件
-
运算符
-
<、>、<=、>=、=、<>(相当于!=)
-
BETWEEN…AND(左右均为闭区间)
-
IN(集合) (满足其中一个条件即可)
-
LIKE 模糊查询
占位符:
- —:单个任意字符
- %:多个任意字符
-
IS NULL和IS NOT NULL
-
and(&&) or(||) not(!)
-
排序查询
语法:order by 排序字段1 排序方式1, 排序字段2 排序方式2…
排序方式:
- ASC:升序排序,默认
- DESC:降序
如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件
聚合函数
将一列数据作为一个整体,进行纵向的计算
- count:计算个数
- 一般选择非空的列:主键
- count(*)
- max:计算最大值
- min:计算最小值
- sum:计算和
- avg:计算平均值
语法:select 聚合函数(字段) from 表名;
聚合函数会排除null值
分组查询
语法:group by 分组字段;
-- 按性别分组,分别查询男女生的数学平均分
SELECT sex, AVG(math) FROM stu GROUP BY sex;
-- 按性别分组,分别查询男女生的数学平均分,人数
SELECT sex, AVG(math),COUNT(id) FROM stu GROUP BY sex;
-- 按性别分组,分别查询男女生的数学平均分,人数,分数低于70分的不参与分组
SELECT sex, AVG(math),COUNT(id) FROM stu WHERE math > 70 GROUP BY sex;
-- 按性别分组,分别查询男女生的数学平均分,人数,分数低于70分的不参与分组,分组之后人数大于2个的显示
SELECT sex, AVG(math),COUNT(id) FROM stu WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
-- 按性别分组,分别查询男女生的数学平均分,人数,分数低于70分的不参与分组,分组之后人数大于2个的显示
SELECT sex, AVG(math),COUNT(id) 人数 FROM stu WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
注意:
- 分组之后查询的字段:分组字段、聚合函数
- where和having的区别
- where在分组前进行限定,如果不满足条件,则不参与分组。having在分组后进行限定,如果不满足结果,则不会被查询出来
- where后不可以跟聚合函数,having可以进行聚合函数的判断
分页查询
语法:limit 开始的索引,每页查询的条数;
公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
分页操作是一个“方言”
约束
概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性
分类:
- 主键约束:primary key
- 非空约束:not null
- 唯一约束:unique
- 外键约束:foreign key
非空约束
某一列的值不能为null
-
在创建表时添加非空约束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 );
-
创建完表后添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
-
删除非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束
某一列的值不能重复
唯一约束可以有null,但只能有一列为null
-
在创建表时添加唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE );
-
创建完表后添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
-
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
主键约束
非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识
-
在创建表时添加主键约束
CREATE TABLE stu( id INT PRIMARY KEY, NAME VARCHAR(20) );
-
创建完表后添加主键约束
ALTER TABLE stu MODIFY ID INT PRIMARY KEY;
-
删除主键约束
ALTER TABLE stu DROP PRIMARY KEY;
-
自动增长
概念:如果某一列是数值类型的,使用auto_increment可以完成值的自动增长
-
在创建表时添加主键约束,并完成主键自动增长
CREATE TABLE stu( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) );
-
创建完表后添加主键约束
ALTER TABLE stu MODIFY ID INT AUTO_INCREMENT;
-
删除主键约束
ALTER TABLE stu MODIFY ID INT;
-
外键约束
让表与表产生关系,从而保证数据的正确性
-
在创建表时,可以添加外键
语法:
create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
)
-
创建表后添加外键
ALTER TABLE 表名 ADD constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
-
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
级联操作
-
级联更新
ALTER TABLE 表名 ADD constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade;
-
级联删除
ALTER TABLE 表名 ADD constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on delete cascade;
-
数据库的设计
多表之间的关系
-
一对一:
如:人和身份证,一个人只有一个身份证,一个身份证只能对应一个人
实现方式:可以在任意一方添加唯一外键指向另一方的主键
-
一对多(多对一):
如:部门和员工,一个部门有多个员工,一个员工只能对应一个部门
实现方式:在多的一方建立外键,指向一的一方的主键
-
多对多:
如:学生和课程,一个学生可以选择多个课程,一个课程也可以被很多学生选择
实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
###数据库设计的范式
概念:设计数据库时,需要遵循的一些规范
几个概念
-
函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A
例如:学号–>姓名 (学号,课程名称)–>分数
-
完全函数依赖:A–>B,如果A是一个属性组,则B属性值得确定需要于A属性组中所有的属性值
例如:(学号,课程名称)–>分数
-
部分函数依赖:A–>B,如果A是一个属性组,则B属性值得确定只需要于A属性组中某一些值即可
例如:(学号,课程名称)–>姓名
-
传递函数依赖:A–>B, B–>C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B的属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A
例如:学号–>系名, 系名–>系主任
-
码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:(学号,课程名称)
-
主属性:码属性组中的所有属性
-
非主属性:除过码属性组的属性
三大范式
- 第一范式(1NF):每一列都是不可分割的原子数据项
- 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
- 第三范式(3NF):在2NF的基础上,任何非主属性不依赖于其它非主属性(在2NF的基础上消除传递依赖)
数据库的备份和还原
-
命令行:
语法:
备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原:登录数据库,创建数据库,使用数据库,执行文件 source 文件路径
-
图形化工具
多表查询
select 字段名 from 表名1,表名2;
多表查询的分类:
- 内连接查询
- 外链接查询
- 子查询
内连接查询
隐式内连接
使用where条件消除无用数据
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp, dept WHERE emp.'dept_id' = dept.'id';
-- 查询员工表的姓名,性别,部门表的名称
SELECT emp.'name',emp.'gender',dept.'name' FROM emp, dept WHERE emp.'dept_id' = dept.'id';
-- 简化写法(起别名)
SELECT
t1.'name',
t1.'gender',
t2.'name'
FROM
emp t1,
dept t2
WHERE
t1.'dept_id' = t2.'id';
显式内连接
语法:select 字段列表 from 表名1 inner join 表名2 on 条件
select * from emp inner join dept on emp.'dept_id' = dept.'id';
inner可以省略
select * from emp join dept on emp.'dept_id' = dept.'id';
内连接查询:
- 从哪些表中查询数据
- 条件是什么
- 查询哪些字段
外连接查询
左外连接
语法:select 字段列表 from 表1 left (outer) join 表2 on 条件;
查询的是左表所有数据以及其交集部分
右外连接
语法:select 字段列表 from 表1 right (outer) join 表2 on 条件;
查询的是右表所有数据以及其交集部分
子查询
概念:查询中嵌套查询,称嵌套查询为子查询
-- 查询工资最高的员工信息
-- 第一步 查询最高工资 9000
select max(salary) from emp;
-- 第二步 查询工资等于9000的员工信息
select * from emp where emp.salary = 9000;
-- 一条sql语句完成
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
子查询的不同情况
-
结果是单行单列的
子查询可以作为条件,使用运算符 > >= < <= =去判断
-- 查询工资小于平均工资的员工信息 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
-
结果是多行单列的
子查询可以作为条件,使用运算符in来判断
-- 查询'财务部'和'市场部'的员工信息 select id from dept where name = '财务部' or name = '市场部'; -- 2,3 select * from emp where dept_id = 3 or dept_id = 2; --子查询 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;
事务
事务的基本介绍
- 概念:如果一个包含多个步骤的业务操作被事务管理,那么这些操作要么同时成功,要么同时失败
- 操作:
- 开启事务:start transaction
- 回滚:rollba
- 提交:commit
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
money DOUBLE
);
INSERT INTO account VALUES(1,"张三", 1000),(2,"李四", 1000);
SELECT * FROM account;
-- 张三给李四转账500
-- 0.开启事务
START TRANSACTION;
-- 1.张三减500
UPDATE account SET money = money - 500 WHERE NAME = "张三";
出错了。。。
-- 2. 李四加500
UPDATE account SET money = money + 500 WHERE NAME = "李四";
-- 提交
COMMIT;
-- 回滚
ROLLBACK;
- MySQL数据库中事务默认自动提交
- 自动提交:一条DML语句会自动提交一次事务
- 手动提交:需要先开启事务再提交
- 修改事务的默认提交方式
- 查看事务的默认提交方式:SELECT @@autocommit; – 1代表自动提交 0代表手动提交
- 修改默认提交方式:SET @@autocommit = 0;
事务的四大特征
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
- 持久性:当事务提交或回滚后,数据库会持久化的保存数据
- 隔离性:多个事务之间相互独立
- 一致性:事务操作前后,数据总量不变
事务的隔离级别
概念:多个事务之间隔离,相互独立。但是如果多个事务操作同一批数据,则会引发一些问题。设置不同的隔离级别就可以解决这些问题
存在问题:
- 脏读:一个事务读取到另一个事务中没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别:
-
read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
-
read committed:读已提交
产生的问题:不可重复读、幻读
-
repeatable read:可重复读(MySQL默认)
产生的问题:幻读
-
serializable:串行化
可以解决所有的问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
数据库查询隔离级别:select @@tx_isolation;
数据库设置隔离级别:set global transaction isolation level 级别字符串;
DCL:管理用户,权限管理
管理用户
-
添加用户
create user ‘用户名’ @‘主机名’ identified by ‘密码’;
-
删除用户
drop user ‘用户名’ @'主机名;
-
修改用户密码
-
第一种
update user set password = password(‘新密码’) where user = ‘用户名’;
-
第二种
set password for ‘用户名’ @‘主机名’ = password(‘新密码’);
mysql中忘记了root用户的密码怎么办
- cmd(管理员) -->net stop mysql 停止mysql服务
- 使用无验证方式启动Mysql服务:mysqld --skip-grant-tables
- 打开新的cmd窗口,直接输入mysql命令,回车
- use mysql;
- update user set password = password(‘新密码’);
- 关闭两个窗口
- 打开任务管理器,手动结束mysqld.exe的进程
- 启动mysql服务
- 使用新密码登录
-
-
查询用户
-
切换到mysql数据库
use mysql;
-
查询user表
select * from user;
通配符:% 表示可以在任意主机使用用户登录数据库
-
权限管理
-
查询权限
show grant for ‘用户名’ @‘主机名’;
-
授予权限
grant 权限列表 on 数据库名.表名 to ‘用户名’ @‘主机名’;
给某用户授予所有权限在任意数据库任意表上
grant all on *.* to ‘用户名’ @‘主机名’;
-
撤销权限
revoke 权限列表 on 数据库名.表名 from ‘用户名’ @‘主机名’;