mysql简介
Mysql
Mysql介绍
mysql是一款Oracle公司出口的轻量级数据库软件，广泛应用于互联网场景，同时也是目前最主流的数据库。
Mysql的优点
Mysql相对于文件系统，优点如下:
- mysql的结构相对于文件系统更方便管理
- mysql支持各种编程语言
- mysql比文件系统支持更大规模的数据
- mysql更具备扩展性
- mysql数据能更快的检索
- mysql支持分布式
Mysql的下载与安装
mysql可视化工具
navicat,sqlyog
SQL分类
DDL：数据库定义语句，用于数据库，表，列的管理,create,drop,alter
DML：数据库表中的数据，表数据的增改删，insert,update,delete
DQL:表数据的查询: select
DCL:权限控制
TCL:事务控制
CCL:指针控制，指针管理
用户管理(DDL)
#查看用户
#select 列名,列名,... from ,mysql.user;
select host, user from mysql.user;
#查看密码
select host, user, authentication_string from mysql.user;
#创建用户
#host 是否允许远程访问
#create user '用户名'@'ip地址'IDENTIFIED by '密码';
create user 'zs'@'localhost' IDENTIFIED by '111';
#修改密码
alter user 'zs'@'localhost' IDENTIFIED by '123';
权限管理(DCL)
#用户授权
#grant 权限码 on 数据库名.表名 to 用户名@主机名
grant select on test.* to 'zs'@'localhost';
#查看用户权限
show grants for 'zs'@'localhost';
#取消用户权限
#REVOKE 权限码 ON 库名.表名 FROM 用户名@主机名
revoke all on *.* from 'zs'@'localhost';
#刷新权限
flush privileges;
软件:mysql是数据库软件
数据库:仓库，是一个数据仓库，通常一个项目对应一个数据库
数据表:一个数据库中有无数张表,表中存放了数据
列:数据表的表头,表头由一列列组成
数据库操作(DDL)
创建数据库
#创建数据库
#create database 数据库名称； #直接创建
#create database if not exists 数据库名称； #判断是否存在并创建数据库
#create database if not exists 数据库名称 character set 编码
#查看当前所有数据库
show databases;
#查看数据库定义语句
show CREATE DATABASE db1;
#修改数据库字符集
alter database db1 default character set 'utf8';
#删除数据库
#drop database 数据库名;
drop database db1;
#使用数据库
use mysql;(使用/切换数据库)
select * from user;
数据库表的操作
创建表
CREATE TABLE [IF NOT EXISTS] 表名 (
字段名 字段类型 [ primary key | unique | not null | auto_increment |
| binary | default 缺省值 | comment 注释语句],
......
) [ ENGINE=InnoDB | DEFAULT CHARSET=utf8 ];
#添加表
#s_系统的 重要的 t_普通的
#engine=innodb 代表数据库引擎 不同的引擎有不同的效果 默认是innodb innodb是一个支持事务的数据库引擎
create table if not exists t_user(
name varchar(255),
sex varchar(255),
age int(3)
) engine=innodb default charset 'utf8';
#快速创建一个表结构相同的表
#`CREATE TABLE 新表名 LIKE 旧表名;`
create table t_user2 like t_user1;
#查看表
#查看当前数据库下有那些表
#show tables;
show tables;
#查看表的结构
desc t_user;
#查看建表语句
show create table t_user;
#删除表
drop table t_user;
#修改表
1.添加列
#ALTER TABLE 表名 ADD 列名 类型
alter table t_user add address varchar(255);
2.修改列类型
#`ALTER TABLE 表名 MODIFY 列名 新的类型;`
alter table t_user modify sex char(2);
3.修改列名
#ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
alter table t_user change name username varchar(255);
4.删除列
#ALTER TABLE 表名 DROP 列名;
alter table t_user drop address;
5.修改表名
#`RENAME TABLE 表名 TO 新表名;`
rename table t_user to t_user1;
6.修改字符集
#`ALTER TABLE 表名 character set 字符集;
alter table t_user1 character set 'gbk';
mysql数据类型
整数
int(常用)
bigint(常用)
浮点
float(常用)
double(常用)
定点小数
decimal(常用)
时间日期
datetime(常用)
date(常用)
字符
char(定长字符串)(常用)
varchar(可变长度字符串)(常用)
longtext(通常用来存超大文本)(常用)
枚举（了解即可）
set（了解即可）
表数据的CRUD
约束
非空约束
not null
唯一性约束
unique
默认值约束
default
主键约束
primary key(保证数据的唯一性)
检查约束
check 检查数据是否符合要求
自动增长
auto_increment 该列的值自动增长，每次加1
#约束的添加方式1
create table if not exists t_test1(
id int primary key auto_increment,
username varchar(255) unique not null,
sex char(2) check(sex='男' or sex='女'),
age int check(age>=0 and age<=150) default 0
)engine=innodb character set 'utf8';
#约束的其他添加方式2
create table if not exists t_test2(
id int auto_increment,
name varchar(255),
sex char(2) default '男',
age int default 1,
CONSTRAINT a primary key(id),
unique(name),
check(age>0 and age<180)
)engine = innodb character set 'utf8';
#创建表后 添加修改约束3
#方式1 modify
alter table t_test2 modify age int check(age>0 and age<180) not null;
#方式2 add
alter table t_test2 add constraint a unique(age);
alter table t_test2 drop constraint a;
#查看表中有几个约束
select * from information_schema.table_constraints where table_schema='test' and table_name='t_test2';
SQL查询
简单查询
查询指定列
查询所有
#简单查询
select id a'学生编号' from t_test2;#给列取别名
select a.id '学生编号'from t_test2 a , t_test b;#给表取别名
select a.id '学生编号','zz'from t_test2 a , t_test b;#给表取别名
(select id a'学生编号' from t_test2)aaa;
#查询18-25岁
select * from t_test2 where age >= 18 and age <=25;
select * from t_test2 where age not between 18 and 25;
#查询名字叫：张三的人
select * from t_test2 where name='张三'
select * from t_test2 where name='张三' or name='李四';
select * from t_test2 where name in('张三','李四');
select * from t_test2 where name not in('张三','李四');
#like 查询10-19的人 模糊查询
select * from t_test2 where age like '1%';
select * from t_test2 where age>=10 and age<=19;
#查询所有姓陈的人
select * from t_test2 where name like '张%';
#查询名字中包含张的人
select * from t_test2 where name like '%张%';
#查询名字以五结尾的人
select * from t_test2 where name like '%四';
# is null is not null
select * from t_test2 where sex is null;
select * from t_test2 where sex is not null;
别名
#别名
select * from t_test2;
select id a'学生编号' from t_test2;#给列取别名
select a.id '学生编号'from t_test2 a , t_test b;#给表取别名
select a.id '学生编号','zz'from t_test2 a , t_test b;#给表取别名
(select id a'学生编号' from t_test2)aaa;
运算符
算数运算符
+-*/%
比较运算符
= < <= = != <>
逻辑运算符
与或非
select 1 xor 1,0 xor 0,1 xor null ,1 xor 1 xor 1;逻辑异或
聚合函数
Max：最大值
Min：最小值
Sum：和
Avg：平均值
Count：计数
distinct: 去重(底层就是分组)
#聚合函数
#Max 最大值
select Max(age) from t_test2
#min 最小值
select Min(age) from t_test2
#sum 和
select sum(age) from t_test2
#avg 平均值
select avg(age) from t_test2
#count 计数 null不加入计算
select count(*) from t_test2 where sex='男';
#去重 distinct
select distinct(sex) from t_test2;
排序
#按年龄从小到大
select * from t_test2 order by age asc;
#从大到小
select * from t_test2 order by age desc;
分页函数
limit
分组
-- 分组函数 group by 列名
select * from t_test2;
select distinct(sex) from t_test2;
select sex from t_test2 group by sex;
-- 聚合函数和分组函数一起使用时，聚合函数将作用于每个组
select sex,count(sex) from t_test2 group by sex;
-- 求各地区男女人数是多少
select address,sex,count(*) from t_test group by address,sex;
union/union all
-- union all 连接所有
select address,sex,count(id) from t_test where sex='男' group by address
union all
select address,sex,count(id) from t_test where sex='女' group by address
-- union 连接所有并去重(所有列相同才会去重)
select address from t_test where dapt='测试部'
union
select address from t_test where dept='aaa';
关联查询
一对一（任意一方）
多对一/一对多（关联关系放多的一方）
多对多（使用第三张表关联）
查询跨多表
1.左连接：以左表为主表
2.右连接
3.左外连接
4.右外连接
5.全连接
6.全外连接
7.笛卡尔积
--左连接 left join
select * from t_student s
left join t_class c on s.class_id = c.id;
--右连接 right join
select * from t_student s
right join t_class c on s.class_id = c.id;
--内连接 inner join
select * from t_student s
INNER JOIN t_class c on s.class_id = c.id;
--左外连接
select * from t_student s
left join t_class c on s.class_id = c.id
where c.id is null;
--右外连接
select * from t_student s
right join t_class c on s.class_id = c.id
where s.id is null;
--全连接
select * from t_student s
INNER JOIN t_class c on s.class_id = c.id
union
select * from t_student s
left join t_class c on s.class_id = c.id
where c.id is null
union
select * from t_student s
right join t_class c on s.class_id = c.id
where s.id is null;
--全外连接
select * from t_student s
left join t_class c on s.class_id = c.id
where c.id is null
union
select * from t_student s
right join t_class c on s.class_id = c.id
where s.id is null;
--笛卡尔积
select * from t_student s, t_class c
where s.class_id = c.id;
SQL子查询
select子查询
子查询语句在select后面的列中，执行顺序是先执行外查询，再依次根据外查询结果行去执行子查询。
select 子查询 n行 1列
Where子查询 根据操作符，非in操作符可以返回n行1列（n=1 或 0），如果是in操作符，可以返回n行1列（n=1或0或多个）。
From子查询 n行m列
Exists 子查询 n行m列
-- 子查询
--select 子查询(外语句先执行，后执行子语句)
-- 查询课程表并显示课程老师的名称
select *,(select username from t_test1 t1 where t1.id=t2.id)name from t_test2 t2
--where 子查询(先执行子查询，再执行外查询)
-- 查询学了体育课程的学生
select st.* from student st where st.sid in(
select sc.student_id from score sc
left join course co on co.cid=sc.course_id
where co.cname='体育'
)
--from 子查询(先执行子查询，再执行外查询)
-- 列出三年二班学了体育的学生
select a.* from (select * from score sc
left join course co on co.cid=sc.course_id
left join student st on st.sid=sc.student_id
left join class cl on cl.cid=st.class_id
where co.cname='体育') a where a.caption='三年二班'
--exists 子查询(可以用来代替in)(先执行外语句，再执行子查询，根据子查询是否)
-- 查询学了课程id为1的学生的姓名
select * from student st where st.sid in (
select sc.student_id from score sc where sc.course_id='1'
)
select * from student st where exists(
select * from score sc where sc.course_id='1' and sc.student_id=st.sid
)
--any
-- 查询比'01'课程成绩高的其他课程
select sc1.course_id from score sc1 where sc1.num <= any(select num from score sc where sc.course_id = '01')
-- 查询比'01'课程任意成绩高的其他课程
select sc1.course_id from score sc1 where sc1.num > all(select num from score sc where sc.course_id = '01')
嵌套查询
函数
常用函数
数字函数
select round(1.5)
select rand()
字符串函数
select concat('1','2')拼接字符串
select substring('helloword' from 6 for 5)截取字符串
select reverse('hello')字符串反转
日期和时间函数
select now() --取当前时间
select date_format(new(),'%y%m%d %H%i%s')--取年月日时分秒
条件判断函数
select st.sname,sc.course_id,ifnull(num,0) from student st
left join score sc on sc.student_id = st.sid
where st.class_id = '3'
select if(year(now())=2012,'是2022','不是')
系统信息函数
-- 系统版本信息
select VERSION()
加密函数
-- 加密函数
select password('aaa')
select MD5('123');--MD5加密是不可逆的
其他函数
-- 其他函数
select format('1.2345',1)
limit关键字与分页查询
-- 分页函数limit
-- n 第一页
-- m 3 m=(n-1)*3
select * from student limit 15,3
数据库事务
什么是事务
事务指的是程序的某一个操作的单元，通常来说，事务指的就是数据库的事务
事务的特性
原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
要么都成功要么都失败
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
最终的结果不会改变
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
事务之间互不影响
持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。
事务一旦提交 对数据做的更改就会永久保存
事务的实现方式
多线程：具有隔离性 一致性
日志：
事务的操作
innodb是支持事务的引擎，而myisam是不支持事务的
开启 begin
提交 commit
回滚 rollback
事务的隔离级别
并发事务带来的问题
1.更新丢失Lost Update：事务A和事务B同时操作，事务B覆盖了事务A做的操作，导致事务A的更新丢失
2.脏读Dirty Reads：事务A读取到事务B还未提交的事务
3.不可重复读Non-Repetable Reads：事务A在同一事务的不同时间段内，读取同一数据，得到的值不一样（读取到其他事务修改的值）
4.幻读Phantom Reads：事务A在同一事务的不同时间段内，第n次读取的结果行与第n+次读取的结果行数不一样（读取到其他事务新增的内容）
事务的隔离级别
读未提交(READ UNCOMMITTED)：能读取到其他事务未提交的数据
读已提交(READ COMMITTED)：只能读取到其他事务已提交的数据
可重复读(默认隔离)(REPEATABLE READ)：事务A在同一事务的不同时间段内，读取统一数据，得到的值一样（即使其他事务修改了值）
串行化(SERIALIZABLE)：事务依次执行，不存在并发问题
数据库锁
共享锁/排他锁：数据访问
读锁/写锁
乐观锁/悲观锁
乐观锁：对事务保持乐观态度，认为不会有其他事务来修改数据，仅在更新前查询是否修改
悲观锁：对事务保持悲观态度，认为一定会有其他事务来修改数据，所以操作时会加锁，不会让其他事务操作
表锁/行锁
行锁：一次锁1~n行数据
表锁：一次锁整张表
数据库的三大设计**
数据库的三大范式：重要***
1.设计表时，应该尽可能保证表中的属性不可再分割（单标如何设计）
2.非主属性必须完全依赖主属性（多对多怎么设计）
3.属性不依赖于其他非主属性（一对多怎么设计）
ER图与数据建模
ER（Entity，RelationShip）图又称实体关联图，实体联系图，是用来描述实体本身以及与其他实体关系的一张图
ER图核心要素：
- 实体：长方形
- 属性：椭圆形
- 关系：菱形
ER图实践
案例：班级考勤系统
数据建模
https://www.freedgo.com/erd-index.html
外键约束与连级操作
什么是外键
外键是用来建立两个表的关联关系的字段
什么是外键约束
用来约束表中的数据必须符合外键表的相关条件，以及在更新数据，自动对关联表做出相关处理
创建表时添加外键约束
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
添加了外键约束后对数据后的影响
- 添加时外键的值必须是在外键表存在的
- 修改时外键的值必须是在外键表存在的
- 删除时，不能先删除外键主表中外键所在行记录，只能先删除外键所在表的记录
外键约束的操作
1.建表后添加外键约束
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
2.查看表中的外键
show indexes from 表名;
3.删除表中的外键
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
级联操作
不做级联操作 RESTRICT、NO ACTION
级联更新 CASCADE
级联删除 CASCADE
设为null SET NULL
视图
什么是视图
视图是一张虚拟表，是一张逻辑表，本身没有数据，本质就是一段SQL，是一段保存在数据库，且可以重复利用的sql
为什么使用视图？
简单：不用关注视图背后的表结构和逻辑，就把视图当成一张表用。
安全：仅能访问到视图返回的数据。
视图的操纵
创建一个视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
create fo replace view v1 as select * from student;
使用视图
select * from v1;
修改视图
create fo replace view v1 as select * from student;
查看所有视图
SELECT * FROM information_schema.views where table_schema='test';
索引
什么是索引？
索引类似书籍中的目录，本质是一张表。
索引的分类
主键索引：表中的主键字段就是一个索引
唯一值索引：添加唯一值约束时就会添加唯一值索引
普通索引：能快速检索想要的数据
全文索引：通常用于varchar,char,text类型的内容快速查找（没有实际使用场景）
索引的底层实现原理
B+树
索引的操作
创建索引
CREATE INDEX i_tc9_a ON tc_9(bbb(15));
-- 创建索引
-- CREATE INDEX i_tc9_a ON tc_9(bbb(15));
create index index1 on t1(money)
-- 查看表上的索引
-- show index from tc_9;
show index from t1;
-- 删除索引
-- drop index i_tc_9a on tc_9;
drop index index1 on t1;
-- 创建唯一值索引
-- CREATE UNIQUE INDEX i_tc_9b ON tc_9(bbb);
create unique index index1 on t1(money);
触发器
什么是触发器
监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器的四大要素
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(内部执行逻辑)
存储过程
什么是存储过程？
存储过程是数据库的一段sql集合，是数据库的一个执行程序，也是一种数据库脚本
存储过程的优势？
存储过程可以写复杂的sql语句，可以处理复杂的业务逻辑。
存储过程的劣势？
不能转移
存储过程的语法
delimiter $
create procedure p_01()
begin
insert into ptest_01(pname) value('bbb');
end$
delimiter ;
in 入参 入参的值不会被改变
out 出参 出参的值会被改变
inout 出入参 出入参的值也会被改变
#存储过程
delimiter $
create procedure p1()
begin
select 123;
select 456;
end$
delimiter;
#调用存储过程
call p1();
#带入参的存储过程 入参的值不会被改变 in入 out出 inout出入
delimiter $
create PROCEDURE p2(in n int)
begin
select n; #显示参数值
set n = 66; #改变参数值
end$
delimiter;
set @n=5;
call p2(@n);
select @n;
#带出参的存储过程 出参的值会被改变
delimiter $
create PROCEDURE p3(out n int)
begin
select n; #显示参数值
set n = 88; #改变参数值
end$
delimiter;
set @n=8;
call p3(@n);
select @n;
#带出入参的存储过程 出入参的值会被改变
delimiter $
create PROCEDURE p4(inout n int)
begin
select n; #显示参数值
set n = 88; #改变参数值
end$
delimiter;
set @n=4;
call p4(@n);
select @n;
#存储过程中的循环
delimiter $
create PROCEDURE p5(in n int)
begin
while(n>0) do
insert into 学生(id,name) values(n,CONCAT('姓名',n));
set n = n-1;
end while;
end $
delimiter;
#查看存储过程
show PROCEDURE STATUS where db='test2';
#删除存储过程
drop procedure p6;
drop PROCEDURE if exists p6;
浙公网安备 33010602011771号