mysql
什么是数据库
- 存储数据的仓库
- 本质上是一个文件系统,还是以文件的方式存在服务器的电脑上的。
- 所有的关系型数据库都可以使用通用的 SQL 语句进行管理 DBMS( DataBase Management System)
SQL 的概念
什么是 SQL
Structured Query Language 结构化查询语言
SQL 作用
- 是一种所有关系型数据库的查询规范,不同的数据库都支持。
- 通用的数据库操作语言,可以用在不同的数据库中。
- 不同的数据库 SQL 语句有一些区别
SQL 语句分类
- Data Definition Language (DDL 数据定义语言) 如:建库,建表
- Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改
- Data Query Language(DQL 数据查询语言),如:对表中的查询操作
- Data Control Language(DCL 数据控制语言),如:对用户权限的设置
MySQL 的语法
- 每条语句以分号结尾,如果在 SQLyog 中不是必须加的。
- SQL 中不区分大小写,关键字中认为大写和小写是一样的
- 3 种注释:
DDL 操作数据库
创建数据库
-
创建数据库
CREATE DATABASE 数据库名;
-
判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
-
创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
查看数据库
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db3;
show create database db1;
修改数据库
修改数据库默认的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
删除数据库
DROP DATABASE 数据库名;
使用数据库
查看正在使用的数据库
# 使用的一个 mysql 中的全局函数
SELECT DATABASE();
使用/切换数据库
USE 数据库名;
MySQL 数据类型
常使用的数据类型
详细的数据类型
DDL 操作表结构
创建表
CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);
查看表
# 查看某个数据库中的所有表
SHOW TABLES;
# 查看表结构
DESC 表名;
# 查看创建表的 SQL 语句
SHOW CREATE TABLE 表名;
复制表
CREATE TABLE 新表名 LIKE 旧表名;
删除表
# 直接删除表
DROP TABLE 表名;
# 判断表是否存在,如果存在则删除表
DROP TABLE IF EXISTS 表名;
修改表
# 添加表列 ADD
ALTER TABLE 表名 ADD 列名 类型;
# 修改列类型 MODIFY
ALTER TABLE 表名 MODIFY 列名 新的类型;
# 修改列名 CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
# 删除列 DROP
ALTER TABLE 表名 DROP 列名;
# 修改表名
RENAME TABLE 表名 TO 新表名;
# 修改字符集 character set
ALTER TABLE 表名 character set 字符集;
DML 操作表中的数据
插入数据
插入全部字段
# 所有的字段名都写出来
INSERT INTO 表名 ( 字段名 1, 字段名 2, 字段名 3…) VALUES (值 值 1, 值 值 2, 值 值 3);
# 不写字段名
INSERT INTO 表名 VALUES (值 值 1, 值 值 2, 值 值 3…);
插入部分数据
INSERT INTO 表名 ( 字段名 1, 字段名 2, ...) VALUES (值 值 1, 值 值 2, ...);
注:没有添加数据的字段会使用 NULL
更新数据
# 不带条件修改数据
UPDATE 表名 SET 字段名= 值; -- 修改所有的行
# 带条件修改数据
UPDATE 表名 SET 字段名= 值 WHERE 字段名=值;
删除数据
# 不带条件删除数据
DELETE FROM 表名;
# 带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
# 使用 truncate 删除表中所有记录
TRUNCATE TABLE 表名;
truncate 和 delete 的区别?
truncate 相当于删除表的结构,再创建一张表。
查询数据
简单查询
# 查询表所有行和列的数据
SELECT * FROM 表名;
# 查询指定列
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
# 指定列的别名进行查询
# 对列指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
# 对列和表同时指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
清除重复值查询
# 查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;
查询结果参与运算
# 某列数据和固定值运算
SELECT 列名 1 + 固定值 FROM 表名;
# 某列数据和其他列数据参与运算
SELECT 列名 1 + 列名 2 FROM 表名;
案例
- 需求:
准备数据:添加数学,英语成绩列,给每条记录添加对应的数学和英语成绩,查询的时候将数学和英语的成绩相加 - 实现:
select * from student;
-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student;
-- as 可以省略
select *,(math+english) 总成绩 from student;
多列相加有逗号
条件查询
SELECT 字段名 FROM 表名 WHERE 条件;
-
运算符
-
逻辑运算符
-
通配符
排序
单列排序
# ASC升序,DESC降序
SELECT 字段名 FROM 表名 WHERE 字段= 值 ORDER BY 字段名 [ASC|DESC];
组合排序
第一个字段相等,就按第二个排序,以此类推
SELECT 字段名 FROM 表名 WHERE 字段= 值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
聚合函数
聚合函数查询是纵向查询,
它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。
五个聚合函数
-
求这一列的最大值
max( 列名)
-
求这一列的最小值
min( 列名)
-
求这一列的平均值
avg( 列名)
-
统计这一列有多少条记录
count( 列名)
-
对这一列求总和
sum( 列名)
注意:聚合函数排除了null值
解决方案:
- 选择不包含非空的列进行计算
- IFNULL函数
eg:
# 如果icon为NULL,就替换为0
SELECT count(IFNULL(icon,0)) FROM pms_category
# * 代表所有列
select count(*) from 表明
分组
GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。
分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
语法
SELECT 字段 1, 字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
分组加上条件
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
注意:
- 分组查询的字段要么为分组字段,要么为聚合函数,查其他没有意义,会返回第一条数据
- where 和 having的区别?
分页查询
语法:
SELECT 字段列表 FROM 表名 LIMIT 开始索引,尺寸
分页的公式:
开始的索引 = (当前的页码-1) *每页显示的条数
约束
约束的作用
对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无
法插入到表中。约束在创建表的时候添加比较合适。
约束种类
非空约束
某一列的值不能为NULL
创建表时添加非空约束
create table st8 (
id int,
name varchar(20) not null, -- 添加非空约束
gender char(1)
)
创建表后添加非空约束
alter table st8 modify name varchar(20) not null;
删除非空约束
alter table st8 modify name varchar(20);
唯一约束
某一列每个值都是唯一,可以有NULL值,但只能有一个为NULL
# 创建时添加唯一约束
create table st8 (
id int,
name varchar(20) unique, -- 添加非空约束
gender char(1)
)
# 创建后添加唯一约束
alter table st8 modify name varchar(20) unique ;
# 删除唯一约束
alter table st8 drop index name;
主键约束
用来唯一标识数据库中的每一条记录
注意:
通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用
的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
如:身份证,学号不建议做成主键
添加主键
1.在创建表的时候给字段添加主键
语法:
# 字段名 字段类型 PRIMARY KEY
create table st5 (
id int primary key, -- id 为主键
name varchar(20),
age int
)
2.在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
ALTER TABLE 表名 MODIFY PRIMARY KEY(字段名);
删除主键
-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
主键自动增长
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
添加删除与其他相同
外键约束
外键约束:foreign key,让表于表产生关系,从而保证数据的正确性。
-
在创建表时,可以添加外键
语法
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 CASCADE ON DELETE CASCADE ;
-
分类:
级联更新:ON UPDATE CASCADE
级联删除:ON DELETE CASCADE
数据库设计
多表之间的关系
- 一对一
- 一对多
- 多对多
一对多实现
多对多关系实现
多对多关系需要中间表来实现
一对一关系实现(了解)
一般一对一关系直接一张表就可以了
多表关系案例
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 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)
);
/*创建用户表 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)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id,外键
date 收藏时间
uid 用户 id,外键
rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
rid INT, -- 线路id
DATE DATETIME,
uid INT, -- 用户id
-- 创建复合主键
PRIMARY KEY(rid,uid), -- 联合主键
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);
三大范式
为什么需要范式?
-
第一范式(1NF):每一列都是不可分割的原子数据项
-
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
- 几个概念:
-
函数依赖: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
例如:学号–>系名,系名–>系主任
-
码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
- 主属性:码属性组中的所有属性
- 非主属性:除过码属性组的属性
-
-
- 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
- 几个概念:
数据库备份和还原
- 命令行:
-
语法:
-
备份:
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
-
还原:
-
登录数据库
-
创建数据库
-
使用数据库
-
执行文件。
source 文件路径
-
-
- 图形化工具:
多表查询
内连接
隐式内连接
查询语法:
select 列名列表 from 表名列表 where....
返回的结果是一个笛卡尔积(所有的组合情况)
例子:
文章表与分类表进行联合查询
SELECT article.id,article.article_title,category.category_name
FROM tb_article as article,tb_category as category
where article.category_id = category.id
显示内连接
语法:
select 字段列表 from 表名1 [inner] join 表名2 on 条件
查询的结果与隐式内连接相同
外连接
左外连接
查询的是左表所有数据以及其交集部分。
语法
select 字段列表 from 表1 left [outer] join 表2 on 条件;
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;
子查询
一个简单的子查询
以一个子查询的结果当作其他查询的条件
SELECT * FROM `ms_article` where ms_article.view_counts= (SELECT MAX(view_counts) from ms_article)
子查询的不同情况
- 子查询的结果是单行单列的
- 子查询的结果是多行单列的
- 子查询的结果是多行多列的
-
单行单列
子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= =
– 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
-
多行单列
子查询可以作为条件,使用运算符in来判断
– 查询’财务部’和’市场部’所有的员工信息SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'; 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; -- 普通内连接 SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
多表查询案例
– 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 >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT
t1.ename ,
t1.`salary`,
t2.*
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 and emp.salary BETWEEN salarygrade.losalary and salarygrade.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(id) total
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, 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`;
事务
事务的四大特征:
-
原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
-
持久性:当事务提交或回滚后,数据库会持久化的保存数据。
-
隔离性:多个事务之间。相互独立。
-
一致性:事务操作前后,数据总量不变(转账后总的钱数相同)
事务的隔离级别(了解)
-
概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
-
存在问题:
- 脏读:一个事务,读取到另一个事务中没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
-
隔离级别:
-
read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
-
read committed:读已提交 (Oracle)
产生的问题:不可重复读、幻读
-
repeatable read:可重复读 (MySQL默认)
产生的问题:幻读
-
serializable:串行化
可以解决所有的问题
- 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- 数据库查询隔离级别:
- select @@tx_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;
DCL
-
SQL分类:
- DDL:操作数据库和表
- DML:增删改表中数据
- DQL:查询表中数据
- DCL:管理用户,授权
-
DBA:数据库管理员
-
DCL:管理用户,授权
管理用户
-
添加用户
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
删除用户:
语法DROP USER '用户名'@'主机名';
-
修改用户密码:
语法UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
- 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服务
- 使用新密码登录。
-
查询用户:
-- 1. 切换到mysql数据库 USE myql; -- 2. 查询user表 SELECT * FROM USER;
-
权限管理:
-
查询权限:
-- 查询权限 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'@'%';