MySQL
数据库就是存储数据的一种仓库!
数据库存储数据--->
1)存储空间非常大,可以存储百万条,千万条甚至上亿条数据,用户可以对数据库中数据进行新增,查询,更新,删除等操作
2)数据独立性高
3)实现数据共享
4)减少数据的冗余度
5)通过数据库里面"事务"--->实现数据的一致性以及维护性!
数据库分为两大类:
- 关系型数据库—>MySQL,SqlServer,Oracle,SQLite,MariaDB(和mysql是同一个数据库引擎)
sqlServer,oracle:收费
Oracle–>中大型公司使用居多—>金融方面互联网公司oracle
Mysql—>免费产品----> 中小型公司使用居多 - 非关系型数据库—>Redis,Memcache,MongoDb,HBase
redis:后面讲(做数据缓存用的)
数据库的操作
创建库
- create database if not exists 库名 ;
如果没有这个库,则创建该库 - create database 库名;
直接创建库
查询库
- show databases;
查询所有库 - show create database 库名;
查询指定库的信息–包括它的字符集 - show tables ;
查询库中有那些表
修改库
-
alter database 库名 default(省略) character set 字符集格式;
- 修改库的字符集
删除库
- drop database if exists 库名;
如果存在库名,则删除 - drop databse 库名 ;
直接删除库
使用库
- use 库名;
表的操作
创建表
- create table 表名(
字段名称1 字段类型1,
字段名称2 字段类型2,
字段名称3 字段类型3,
…
字段名称n 字段类型n
) ;
查询表
-
desc 表名;
- 看到表的字段有哪些
-
show create table 表名;
- 查询表的字符集
-
alter table 表名 character set 字符集格式;
- 修改表的字符集
修改表
-
alter table 表名 modify 字段名称 修改后字段类型;
- 修改表的字段类型
-
alter table 表名 change 以前的字段名称 现在的字段名称 以前的字段类型;
- 修改字段名称, (注意这个语法不要直接同时修改字段类型)
-
alter table 表名 add 字段名称 字段类型;
- 添加一个新的字段
-
alter table 表名 drop 字段名称 ;
- 删除表中某个字段
-
alter table 表名 rename to 新表名;
-
也可以用 rename table 以前表名 to 新表名;
- 修改表名
-
删除表
-
drop table if exists 表名;
- 如果存在表删除
-
drop table 表名;
- 直接删除表名
复制表
- create table 新表名 like 旧表名;
- 作用：快速去创建有一个结构相同的表
常见的数据类型
- int
-
int
- 默认11位,当前这个字段(列的名称) 值本身的实际位数 (推荐)
-
int()
- 例int(3);给用户ID编号，不够3位补0
例：1------->001
- 例int(3);给用户ID编号，不够3位补0
-
varchar(字符长度)
字符串类型 -
date:日期类型
仅仅表示日期 -
datetime:日期+时间
-
timestap:时间戳
当前插入数据或则修改/删除数据的即时时间 :2022-12-3 11:30:00 -
double:小数类型
double(3,2): 这个小数一共有3个数字,小数点后保留2位
例：double(4,2);---->12.56
double(3,2);---->2.56
double(5,2);---->132.56 -
clob:大字符类型
某个表中某个字段, 使用clob来存储大文本! -
blob
大字节类型:存储大图片文件,大字节类型
表的操作语句
插入数据
-
insert into 表名 values(值1,值2,值3,值3…值n);
一次插入一条数据 -
insert into 表名 values(值1,值2,值3,值3…值n),(值1,值2,…值n),(…) ;
一次插入多条数据 -
insert into 表名(字段名称1,字段名称2.,等部分字段)values(值1,值2,值3…),(值1,值2,值3…),(…);
插入部分字段,没有插入的字段值就是null(空值)
修改数据
-
update 表名 set 字段名称= 值 where 字段名称= 值;
一次修改一个 -
update 表名 set 字段名称1= 值1,字段名称2=值2,… where 字段名称= 值;
一次修改多个 -
注意事项：修改的时候如果不带where条件,属于批量修改(很少用)
删除数据
-
delete from 表名 where 字段名称 = 值;
条件一般使用非业务字段删除 -
– delete from 表名 where 字段名称1 = 值1 and 字段名称2 = 值2 …;
多个条件删除 -
删除全表数据
-
delete from 表名;
是删除全表数据,表结构还在 -
truncate table 表名;
删除全表
-
查询数据
模糊查询 where
-
查询全表
select * from 表名;
代表全部字段 (实际开发中禁用*)
select 全部字段名称 from表名; -
带条件查询
select 全部字段名称 from 表名 where 条件语句; -
where
比较运算符
<=,>=,<,>,!=(在mysql使用 <>)
< 小于
<= 小于等于
>= 大于等于
>大于
!=或<> 不等于
between……and…… 在……和……之间
in 在……中
like 模糊查询
is null 查询某一列为null的值
-- 需求:查询年龄是20或者年龄是18或者年龄是45的学生所有信息
-- where条件可以使用 or 或者java语言||,in(值1,值2.....)
SELECT
*
FROM
student2
WHERE age = 20
OR age = 18
OR age = 45 ;
-- 另一种写法
SELECT
*
FROM
student2
WHERE age = 20 || age = 18 || age = 45 ;
-- 另一种写法:in(值1,值2.....)
SELECT
*
FROM
student2
WHERE age IN (20, 18, 45) ;
- 逻辑运算符
- 与
- and
- &&
- 或
- or
- ||
- 非
- not
- !
-- 需求:查询年龄不是20岁的学生所有信息
SELECT
*
FROM
student2
WHERE age != 20 ;
-- 另一种写法
SELECT
*
FROM
student2
WHERE age <> 20 ;
- 通配符
- % 匹配多个字符
- _（下划线） 匹配一个字符
-- 需求:查询学生中所有姓马的学生信息
SELECT
*
FROM
student2
WHERE
NAME LIKE '%马%'
-- 查询学生的姓名是三个字符的人的信息
SELECT
*
FROM
student2
WHERE NAME LIKE '___' ;
聚合查询
- select 聚合函数(例名) from 表名
- select 全部字段名称 from 表名 where 条件语句(select 聚合函数(列名) from 表名);
- 聚合函数
- 最大值 max(列名)
- 最小值 min(列名)
- 平均值 avg(列名)
- 统计 count(列名)
- 统计 sum(列名)
- 聚合函数
排序查询 order by
- select 字段列表 from 表名 order by 字段名称 排序规则;
- 排序规则
- asc ,升序排序
- desc 降序
- 排序规则
分组查询 group by
- 单独使用
- select 字段列表 from 表名 group by 分组字段名称;
- 带where条件
- select 字段列表 from 表名 where 查询条件 group by 分组字段名称 ;
- 带having
- select 字段列表 from 表名 where 查询条件 group by 分组字段名称 having 筛选条件 ;
筛选查询 having
- select 字段列表 from 表名 where 查询条件 group by 分组字段名称 having 筛选条件 ;
注意
having的后面可以跟聚合函数,having在group by ,
where是gruop by的前面;
-- 需求:按照性别分组,查询数学成绩平均分,
-- 条件:学生数学成绩大于70的人参与分组,同时筛选出人数大于2的一组!
SELECT
sex '性别',
AVG(math) '数学平均分',
COUNT(id) total
FROM
student2
WHERE math > 70
GROUP BY sex
HAVING total > 2 ;
分页查询 limit
select 字段列表 from 表名 limit 起始行数,每页显示的条数;
-- 起始行数= (当前页码数-1)*每页显示的条数;
-- 每页显示2条,
-- 查询第一页的数据
SELECT * FROM student2 LIMIT 0,2 ;
-- 查询第二页的数据
SELECT * FROM student2 LIMIT 2,2 ;
-- 查询第三页的数据
SELECT * FROM student2 LIMIT 4,2 ;
-- 查询第四页的数据
SELECT * FROM student2 LIMIT 6,2 ;
-- 查询第五页的数据
SELECT * FROM student2 LIMIT 8,2 ;
-- select 字段列表 from 表名 limit 值;
SELECT * FROM student2 LIMIT 3 ;
--如果复合查询:有where条件,还有limit,where在limit前面
SELECT * FROM student2 WHERE id > 2 LIMIT 4;
数据库约束
-- 创建一张表
CREATE TABLE test1(
id INT, -- 编号
NAME VARCHAR(10), -- 姓名
age INT, -- 年龄
gender VARCHAR(3) DEFAULT '女' -- 姓名
) ;
默认约束 default
INSERT INTO test1 VALUES(1,'张三',35,'男'),(2,'张思',30,'女') ;
-- 插入部分字段
INSERT INTO test1(id,NAME,age) VALUES(3,'张武',29) ;
没有插入字段的值默认值null,为了防止这种数据出现,可以在创建表的时候
加入默认约束default,当没有插入这个字段,默认约束起作用!
sql添加,默认约束
ALTER TABLE 表名 MODIFY 字段名称 数据类型 DEFAULT 数据 ;
ALTER TABLE test1 MODIFY gender VARCHAR(3) DEFAULT '女' ;
非空约束 not null
当前这个值不能为null,不能直接添加数据给一个null
将默认约束删除了
alter table 表名 modify 字段名称 数据类型;
ALTER TABLE test1 MODIFY NAME VARCHAR(10) ;
添加非空约束
ALTER TABLE 表名 MODIFY 字段名称 数据类型 NOT NULL ;
ALTER TABLE test1 MODIFY NAME VARCHAR(10) NOT NULL ;
INSERT INTO test1 VALUES(3,NULL) ;
唯一约束 unique
当前这个值不能重复
限制id字段/有效身份信息(邮箱/手机号/身份证…)
CREATE TABLE test1(
id INT ,
NAME VARCHAR(10),
telephone VARCHAR(11) UNIQUE -- 唯一约束:存在唯一索引inedex
) ;
INSERT INTO test1 VALUES(1,'张思','13366668888'),(2,'王五','13322226666') ;
添加手机号码重复
INSERT INTO test1 VALUES(3,'文章','13366668888'); -- 手机号重复,防止这些字段重复(有效身份信息,可以在创建表的时候添加唯一约束)
Duplicate entry '13366668888' for key 'telephone' 这个字段有多个重复值出现
删除唯一约束----(删除唯一索引的名称)
alter table 表名 drop index 索引名(索引名如果没有起名字默认是列的名称);
ALTER TABLE test1 DROP INDEX telephone ;
INSERT INTO test1 VALUES(3,'文章','13366668888');
添加唯一约束
alter table 表名
add constraint(声明)
唯一约束的索引名称
unique(列名);
ALTER TABLE test1 ADD CONSTRAINT index_telephone UNIQUE(telephone) ;
-- uplicate entry '13366668888' for key 'index_telephone'
DROP TABLE test1;
主键约束 primary key
非空且唯一
通过sql删除主键
alter table 表名 drop primary key ;
ALTER TABLE test1 DROP PRIMARY KEY ;
通过sql添加主键
alter table 表名 add primary key (列名称) ;
ALTER TABLE test1 ADD PRIMARY KEY(id) ;
自增长约束 auto_increment
每一张表的id
自增长主键的值一定插入之后产生的而不是修改!
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键约束 + 自增长
NAME VARCHAR(10),
gender VARCHAR(3)
);
外键约束 foreign key
问题:
1)查询员工的所有信息---部门名称字段 冗余度大(重复度高)
2)一张表描述了两个事情(员工信息,又有部门信息!)
解决方案:将这张表拆分两张表
一张表员工信息
一张表描述部门信息
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3), -- 性别
dept_id INT ,-- 部门编号
CONSTRAINT -- 声明
dept_emp_fk -- 外键名称
FOREIGN KEY(dept_id) -- 作用在指定外键字段上
REFERENCES -- 关联
dept(id) -- 主表的主键字段
);
外键作用的表—从表,另一张表:主表
创建表的时候添加外键
constraint (声明)
外键名--->起名字 主表名_从表名_fk
foriegn key (从表的字段名称)
references -- (关联)
主表名(主键字段的名称)
有了外键之后,直接修改或者删除主表数据,前提需要让从表的数据跟主表没有关联，这个时候才能修改和删除主表数据!
有外键约束,修改–先修改从表,然后在修改主表
sql删除外键约束
alter table 表名 drop foreign key 外键名;
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
sql 添加外键约束
alter table 表名 add constraint 外键名称 foreign key (从表字段名称) references 主表的(主键字段) ;
级联操作 cascade
级联删除/级联修改 on delete cascade /on update casade
当修改/删除主表的数据,从表数据随之改动
将上面这个语句放在外键的后面
添加外键的同时—添加级联修改和级联删除
ALTER TABLE employee
ADD CONSTRAINT dept_emp_fk
FOREIGN KEY (dept_id)
REFERENCES dept(id)
ON UPDATE CASCADE
ON DELETE CASCADE ;
-- 直接修改主表数据,
-- 将1号部门改成2号部门,这个时候1号部门的员工,它部门编号变成2
UPDATE dept SET id = 2 WHERE id =1 ;
-- 直接删除主表数据,从表随之变化
-- 将3号部门解散
DELETE FROM dept WHERE id = 3 ;
表和表的关系
设计一个库,满足数据库的范式(就是数据库设计的一种规范要求)
一对一
人和身份证
一个人对应一个身份证
一个身份证从属于某个人的
一对多
用户和订单的关系
一个用户可以有多个订单,
某个订单从属于某个用户
员工和部门的关系
一个部门有多个员工
一个员工属于某个部门的
多对多
订单表和商品表
一个订单包含多个商品
一个商品被多个订单包含
学生表和选课表
一个学生可以选多个课程
一个课程被多个学生选择
范式
第一范式
- 数据库表中每一列都是不能再拆分的原子性
第二范式
- 在满足1NF的基础上
- 一张表描述一件事情
- 非主键字段必须完全依赖于主键字段
第三范式
- 在满足2NF的基础上
- 非主键字段中间不能产生依赖
多表查询
创建数据表
-- 设计部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT ,-- 部门编号
NAME VARCHAR(10) -- 部门名称
) ;
-- 添加部门表数据
INSERT INTO dept(NAME) VALUES('开发部'),('市场部'),("销售部") ;
-- 设计员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT , -- 部门编号
-- 设置外键
CONSTRAINT -- 声明
dept_emp_fk -- 外键名
FOREIGN KEY (dept_id) -- 指定外键名作用从表的指定字段上
REFERENCES -- 关联
dept(id)
);
-- 添加员工数据
INSERT INTO employee(NAME,gender,salary,join_date,dept_id) VALUES
('张三','男',12000.0,'2021-11-11',1),
('李四','男',8000.0,'2022-05-28',1),
('王五','女',7000.0,'2021-12-31',2),
('赵六','女',8000.0,'2021-10-30',3) ,
('宋七','男',3000.0,'2022-10-15',3),
('钱八','男',15000.0,'2022-9-30',1),
('孙九','女',6000.0,'2021-10-10',3);
针对多表查询
1)查询哪张表!
2)查询指定表的哪些字段!
3)他们表和表之间的关系!
笛卡尔乘积
SELECT
e.*,
d.*
FROM
employee e,
dept d ;
上面查询的结果不符合实际要求,7个员工,现在出现了21个
这个就是笛卡尔乘积,针对A表的记录 和B的记录数,两个表的记录相乘
出现笛卡尔乘积的原因:没有设置连接条件!
内连接
-
隐式内连接
- select 字段列表 from 表名1,表名2…表名n where 这些表之间的连接条件;
-
内连接
- select 字段列表 from 表名1 (inner) join 表名2 on 连接条件;
外连接
-
左外连接(通用)
- select 字段列表 from 左表名 left (outer) join 表名2 on 连接条件;
-
右外连接
-
子主题 1
- select 字段列表 from 左表名 righter (outer) join 表名2 on 连接条件;
-
SELECT
e.*,
d.`name` "部门名称"
FROM
employee e
LEFT OUTER JOIN dept d -- outer可以省略
ON e.`dept_id` = d.`id` ;
子查询
-
select 嵌套 select……
-
情况1：单行单列数据
- 使用where 后面条件表达式=,<=,>=…
-
情况2：多行多列
- 利用in集合语句 (使用最多)
-
情况3：通过俩个表的关系查询出结果集，当做虚表在和其他之间关联查询
- 当前"虚表"继续和其他进行关联查询(按照连接条件)
-
多表查询语法
- select 字段列表 from 表名1,表名2;
数据库事务
什么是数据库事务(Transaction)
在一个业务中执行多个sql(多张表的sql),这多个sql句要么同时执行成功,
要么同时执行失败!
举例
转账操作 多个账户同时操作,一个减钱,一个加钱
提交订单
订单表和订单项表 同时添加数据
事务特点 ACID (关系型数据库传统事务)
原子性,一致性,隔离性,持久性!
原子性:在使用事务管理的时候,执行多个sql增删改,要么同时执行成功,要么同时失败
一致性:高并发的时候,需要保证事务多次读写,保证数据一致性!
隔离性:事务和事务是独立的,相互不影响!
举例
购物车业务--->购物车表和购物车项表同时添加数据
订单业务---->订单表和订单项表同时添加数据
持久性: 事务一旦提交,对数据的影响是永久性,即使关机了,数据还是要更新的!
事务的隔离级别
四个级别:从小到大,安全性—从低到高,效率性:从高到低!
- read uncommitted :读未提交
- read committed :读已提交
- repeatable read :可重复读 (mysql的默认隔离级别)
- serializable : 串行话
SELECT @@tx_isolation; – – 查看隔离级别:5.7以及5.7以前
select trasaction_isolation; – – mysql8.0以后的查看事务的隔离级别
设置隔离级别
set global transaction isoloation level 级别的名称;
-
第一种级别read uncommitted:读未提交 会造成问题"脏读"
“脏读”:是事务管理最严重的问题:一个事务读取到另一个没有提交的事务! -
第二种级别:read committed; 读已提交 有效防止脏读,出现了一个问题"不可重复读"
事务(当前本身这个事务提交)多次读取另一个提交事务的前后到的数据不一致! -
mysql的默认级别 repeatable read
可重复读,有效脏读,不可重复读,出现幻读!(一般有更新操作影响了数据) -
最高级别:serializable:串行话 (一个事务读取到另一个没提交事务,数据查不到的,这个必须提交,才能操作数据!)
-- 创建一个账户表
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT , -- 账户id
NAME VARCHAR(10), -- 账户名称
balance INT -- 余额
);
INSERT INTO account (NAME,balance) VALUES('zhangsan',1000),('lisi',1000);
-- zhangsan给lis转账500
-- 没有数据库事务管理转账操作
/*
update account set balance = balance - 500 where id = 1 ;
-- 转账出问题了 -- 这块不执行了,前面执行了!
update account set balance = balance + 500 where id = 2;
*/
-- 使用数据库事务管理整个转账的业务操作
-- 开启事务
START TRANSACTION ;
-- 操作多个sql语句或多张表的sql(同时增删改)
UPDATE account SET balance = balance - 500 WHERE id = 1;
-- 出问题了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;
-- 事务回滚
ROLLBACK ;
-- 提交事务
COMMIT;
UPDATE account SET balance = 1000 ;
-- 查询账户
SELECT * FROM account ;
练习
CREATE DATABASE 2211_test;
USE 2211_test ;
CREATE TABLE exam(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
math INT,
chinese INT,
english INT
) ;
INSERT INTO exam(NAME,math,chinese,english)
VALUES
('小毛',88,78,70),
('小邓',90,91,92),
('小江',65,66,98),
('小胡',88,77,66);
-- 1)查询表中所有的学生,需要给表以及字段起别名
SELECT
e.`id` '学号',
e.`name` '姓名',
e.`math` '数学',
e.`chinese` '语文',
e.`english` '英语'
FROM
exam e; -- 表的别名
-- 2)查询表中所有学生的姓名和对应的英语成绩
SELECT
NAME '姓名',
english '英语成绩'
FROM
exam ;
-- 3)统计每个学生的总分
SELECT
NAME '姓名',
(math+chinese+english) '总分'
FROM exam ;
-- 查询姓名为小江的学生成绩
SELECT
math '数学',
chinese '语文',
english '英语'
FROM
exam
WHERE NAME = '小江' ;
-- 5)查询英语成绩大于90分的同学
SELECT
*
FROM
exam
WHERE english > 90 ;
-- 6)查询总分大于200分的所有同学
SELECT
*
FROM
exam
WHERE (math + chinese + english) > 200 ;
-- 查询英语分数在80-90的同学
SELECT
*
FROM
exam
WHERE english BETWEEN 70
AND 80;
-- 查询数学分数为89,90,91的同学
SELECT
*
FROM
exam
WHERE math IN (89, 90, 91) ;
-- )查询数学>80,语文>80的同学
SELECT
*
FROM
exam
WHERE math > 80
AND chinese > 80 ;
-- 对总分排序按从高到底的顺序输出
SELECT
(math + chinese + english) '总分'
FROM
exam
ORDER BY (math + chinese + english) DESC ;
-- )对学生成绩按照英语降序,英语相同学员按照数学降序
SELECT
*
FROM
exam
ORDER BY english DESC,
math DESC ;
-- 统计一个班级共有多少学生
SELECT
COUNT(id) 'total'
FROM exam ;
-- 查询数学成绩最高分的同学
SELECT
*
FROM
exam
WHERE math =
(SELECT
MAX(math)
FROM
exam) ;
-- 统计数学成绩大于90分的学生有多少个
SELECT
COUNT(id) 'total'
FROM
exam
WHERE
english > 90;
-- 统计一个班级数学总成绩
SELECT SUM(math) '数学总成绩' FROM exam ;
-- 统计班级语文,英语,数学的各科的总成绩
SELECT
SUM(chinese),
SUM(english),
SUM(math)
FROM
exam ;
SELECT * FROM exam ;
面试题
1.delete from 表名和truncate table 表名的区别?
- 前者:仅仅是删除全表数据
表的结构还在,如果表中主键自增长的字段,不会影响这个自增主键的值,下一次插入数据,依然在上一次删除的最后一个自增长主键的值继续自增; - 后者:删除表的全部数据,同时删除了表,然后创建一张空表
直接影响了自增长主键的值,下一次插入数据,从1开始继续自增
- 关于group by和having的区别
- having:在满足where以及group by之后查询的结果集中,进行过滤(帅选)声明;having后面可以使用聚合函数
- group by 后面 使用分组字段或者表达式—>后面不能使用聚合函数,where必须放在group by之前
where…group by…having…
336
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
