MySQL语言
特点 :持久化储存、更加安全的权限管理访问机制
常见数据库 : MySQL、oracle、postgre SQL 、SQL Server、redis
下载完MySql 在终端连接数据库 ,前提是添加环境变量 进入终端输入 mysql -u root -p
注意 :utf-8的储存为三个字节,而mysql数据库中有些特殊字符占四个字符,所以mysql一般用utf-8mb4编码,因为它支持四个字节
1. 启动与停止
-
方式一: win + r,输入services.msc 进入window系统服务,因为安装mysql时,他是自动注册为系统服务,进入后找到mysql可以点启动或者停止。 mysql安装时是默认开机自动启动的
-
方式二: 打开命令指示符,输入 net stop mysql 80 -->停止mysql 其中80是指mysql的版本
输入 net start mysql80 -->启动mysql
该方式执行命令指示符时,必须是以管理员身份运行
2.客户端连接
要想操作mysql,必须先启动mysql,在连接mysql
- 方式一:mysql提供的客户端命令行工具----->Mysql 8.0 commend Line Client
- 方式二:系统自带的命令指示符 输入 mysql [-h 127.0.0.1] [-p 3306] -u root -p exit 断开连接
mysql 语法
- sql语句可以单行或者多行书写,以分行结尾
- sql语句可以使用空格或者缩进来增强语句的可读性
- mysql数据库的sql语句不区分大小写,关键字建议大写
- 单行注释:–或者#
- 多行注释:/*…*/
3.DDL
3.1 DDL-数据库操作
- show databases; -->查询所有数据库
- select database(); —>查询当前使用的数据库
- create database [if not exits] 数据库名 [default charset 字符集] [collate 排序规则]; —>创建数据库
- drop database [if exits] 数据库名; 删除数据库,如果存在则删除
- use 数据库名; -->使用数据库
create database if not exit test default charset utf8mb4;
3.2 DDL-表操作
注意 要操作表得先使用库
- show tables; —> 查询当前数据库里面有哪些表
- desc 表名; ----->查询表结构,查看表中的字段和类型
- show create table 表名 ----> 查询指定表的建表的语句,就是当时建表的sql语句
- create table 表名(字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释])
…
字段n 字段n类型 [comment 字段n注释])[comment 表注释]; ------>表创建
例如:
create table tb_user(
id int comment '编号',
name varchar(10) comment '姓名',
gander char(1) comment '性别'
)comment ’用户表‘;
3.3 DDL-表修改
- 添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释];
- 修改数据类型
alter table 表名 modlfy 字段名 新数据类型(长度);
- 修改字段和类型
alter table 表名 change 旧字段名字 新字段名字 类型(长度);
- 删除字段
alter table 表名 drop 字段名;
- 修改表名
alter table 表名 rename to 新表名;
- 删除表
drop table [if exits] 表名;
4. DML
4.1 添加数据
- 给指定字段添加数据
insert to 表名 (字段1...字段n) values (value1,...valuesn);
- 给全部字段添加数据
insert to 表名 values (值1,值2,....值n);
- 批量添加数据
insert to 表名 values (值1,值2....值n),....(值1,值2,...值n);
- 字符串和日期类型数应该包含在引号中
4.2 字符串类型
-
char 定长字符串 性能好 varchar 变长字符串 性能较差
4.3 日期类型
date | YYYY-MM-DD | 日期值 |
---|---|---|
time | HH:MM:DD | 时间值持续时间 |
year | YYYY | 年份值 |
---|---|---|
datetime | YYYY-MM-DD HH:MM:SS | 混合时间和时间值 |
4.4DML-修改数据
update 表名 set 字段名1=值1,字段名2=值2,... [where 条件];
4.5 DML-删除数据
delete from 表名 [where 条件];
注意 : 条件没有则会删除整张表的所有数据
5.DQL
5.1 基本查询
语法
- 查询多个字段
select 字段1,字段2,... from 表名;
- 查询所有字段
select * from 表名;
5.2 条件查询
语法
select 字段列表 from 表名 where 条件列表;
条件类型
- > < != =
- between…and… 在某个范围之内(含最大 最小值)
- in(…) 在in之后的列表中的值多选一
例如:选出年龄在18,20,40的
select * from emp where age in(18,20,40);
- like 占位符,模糊匹配( __ 匹配单个字符,**%**匹配任意字符)
例如: 查询名字为俩个字的
select * from emp where name like'_ _';
例如:查询身份证最后为x的
select * from emp where idcard like '%x';
- is null 是空
- and or
- not 或者!
5.3 聚合函数
将一列数据作为一个整体,进行纵向计算
语法
select 聚合函数(字段列表) from 表名;
注意 null是不参与计算的
例如:统计表有多少条数据
select count(*) from emp ;
例如:统计西安员工的年龄之和
select sum(age) from emp where workaddress = '西安' ;
5.4 分组查询
语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤的条件] ;
- having :指分组完再进行筛选的条件
例如
#按性别分组,统计男性和女性员工的平均年龄
select gander,avg(age) from emp group by gender ;
#查询年龄小于45员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age < 45 goup by workaddress having count(*) >= 3;
5.5 排序查询
语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2 ;
- asc:升序(默认值) desc : 降序
- 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
例如
select * from emp order by age asc ;
5.6 分页查询
语法
select 字段列表 from 表名 limit 起始索引,查询记录数 ;
注意 : 起始索引从0开始, 起始索引=(查询页码-1)* 每页显示记录数
例如
select * from emp limit 10,10;
6. 字符串函数
- concat(s1,s2,…,s3) : 字符串拼接,将s1,s2…sn拼接成一个字符串
select concat('Hello','mysql');
>>> Hellomysql
- lower(str) : 将字符串str全部转为小写
- upper(str) : 将字符串str全部转为大写
- lpad(str,n,pad) : 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
例如:变更员工工号,统一为五位数,不足五位数的在前面补0
id | name | workno |
---|---|---|
1 | 小明 | 101 |
2 | 小红 | 102 |
3 | 小刚 | 103 |
4 | 小王 | 104 |
update emp set workno=lpad(workno,5,'0');
>>>
id | name | workno |
---|---|---|
1 | 小明 | 00101 |
2 | 小红 | 00102 |
3 | 小刚 | 00103 |
4 | 小王 | 00104 |
- rpad(str,n,pad) : 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
- trim(str) : 去掉字符串头部和尾部的空格
- substring(str,start,len) : 返回从字符串str从strat起的len个长度的字符串,起始索引为1
select substring('Hello Mysql',1,5);
>>>Hello
7.数值函数
- ceil(x) : 向上取整
- floor(x) : 向下取整
- mod(x,y) : 返回x/y的取余
- rand() : 返回0到1内的随机数
- round(x,y) :求参数x的四舍五入的值,保留y位小数
例如:生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');
8.日期函数
- curdate() : 返回当前日期
- curtime() : 返回当前时间
- now() : 返回当前日期和时间
- year(date) :获取指定date的年份
- month(date) : 获取指定date的月份
- day(date) : 获取指定date的日期
- date_add(date,interval expr type) : 返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add('2021-10-12 23:20:20',interval 50 year);
>>>2071-10-12 23:20:20
- datediff(date1,date2) : 返回起始时间date1和结束时间date2之间的天数
select datediff('2021-12-01','2021-10-01');
>>>61
9.流程函数
很常用,在sql语句中实现条件筛选,从而提高语句的效率
- if(value, t, f) : 如果value为True,则返回t,否则返回f
select if(false,'ok','no')
>>>no
- ifnull(value1, value2) : 如果value1不为空,返回value1,否则返回f
select ifnull('ok','default') -------> ok
select ifnull(null,'default') -------> default
- case when [val1] then [res1]…else [default] end : 如果val1为True,返回res1,… 否则返回default默认值 when可写多个
例如:各科成绩大于等于85为优秀,大于等于60为及格,否则为不及格
select id,
name,
(case when math>=85 then '优秀' when math >=60 then '及格' else '不及格' end) as '数学',
(case when english>=85 then '优秀' when english >=60 then '及格' else '不及格' end) as '英语'
from emp;
- case [expr] when [val1] then [res1],…else [default] end : 如果expr的值等于val1,返回res1,… 否则返回default默认值 when可写多个
例如:查看员工姓名和工作地址(北京/上海–>一线城市,其他–>二线城市)
select name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
10.约束
10.1 概念
约束是作用于表中字段上的,可以在创建表或者修改表的时候添加约束,违法约束会报错
目的 : 保证数据库中数据的正确、有效性和完整性
注意 : 多个约束之间用空格分开即可
自动增长:auto_increment
10.2 非空约束
关键字 :not null
限制该字段的数据不能为空
10.3 唯一约束
关键字:unique
保证该字段的所有数据都是唯一,不重复的
10.4 主键约束
关键字: primary key
主键是一行数据的唯一标识,要求非空并且唯一
10.5 默认约束
关键字: default
保存数据时,如果未指定该字段的值,则采用默认值
10.6 检查约束
关键字 : check
保证字段值满足某一个条件
create table user(
in int primary key aoto_increment comment '主键', #主键,并且自动增长
name varchar(10) not null unique comment '姓名', #不为空,并且唯一
age int check(age>0 and age<=120) comment '年龄', #大于0并且小于等于120
status char(2) default '1' comment '状态', #如果没有指定该值,默认为1
gender char(1) comment '性别' #无条件
)comment '用户表' ;
10.7 外键约束
关键字: foreign key
用来让俩张表的数据之间建立连接,保证数据一致性和完整性
具有外键的表称为子表,主键的表称为父表
- 建表时创建外键的语法
create table 表名(
字段名 数据类型,
....
[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);
- 建表完后创建外键的语法
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
- 删除外键
alter table 表名 drop foreign key 外键名称 ;
例:
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id); #创建外键
alter table emp drop foreign key fk_emp_dept_id ; #删除外键
10.7.1 外键约束:删除/更新行为
- no action : 当在父表删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则不允许删除/更新
- cascade : 当在父表删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录
- set null : 当在父表删除对应记录时,首先检查该记录是否有对应外键,如果有,则设置子表中该外键值为null(这要求外键允许取null值)
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表字段名)
on update cascade on delete cascade ;
11. 多表关系
-
一对多(多对一): 案例如部门与员工的关系,一个部门对应多个员工,一个员工对应一个部门,
实现 : 再多的一方表中建立外键,指向另一方的主键 -
多对多的关系: 如学生与课程的关系,一个学生可以选修多门课程,一门课程可以供多个学生选择
实现 : 建立第三张中间表,中间表至少包含俩个外键,分别关联俩方主键 -
一对一关系 : 如用户与用户之详情的关系, 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升效率
实现 :在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)
12. 多表查询
从多张表中查询数据
多卡尔积 : 指俩个集合A与集合B的所有组合情况
emp 表:
id | name | dept_id |
---|---|---|
1 | 小明 | 1 |
2 | 香杠 | 2 |
3 | 小红 | 1 |
4 | 小王 | 3 |
5 | 小李 | 4 |
6 | 小鹏 | 3 |
7 | 小光 | 5 |
8 | 小康 | null |
dept 表:
id | name |
---|---|
1 | 项目部 |
2 | 学习部 |
3 | 管理部 |
4 | 人事部 |
5 | 宣传部 |
6 | null |
12.1 连接查询
12.1.1 内连接
相当于查询A、B交集部分的数据
- 隐式内连接 :
select 字段列表 from 表1,表2 where 条件 ;
- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ;
#查询每一个员工的姓名,以及关联部门的名称
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
或者 select e.name,d.name from emp e,dept d where e.dept_id = d.id;
或者 select e.name,d.name from emp e join dept d on e.dept_id = d.id;
>>>
name | name |
---|---|
小明 | 项目部 |
小红 | 项目部 |
香杠 | 学习部 |
小王 | 管理部 |
小鹏 | 管理部 |
小李 | 人事部 |
小光 | 宣传部 |
12.1.2 外连接
- 左外连接: 查询左表所有数据和俩张表交集的部分数据
- 右外连接: 查询右表所有的数据和俩张表交集的部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
#查询emp表所有数据,和相对应的部门信息
select e.*,d.name from emp e left join dept d on e.dept_id = d.id ;
>>>
id | name | dept_id | name |
---|---|---|---|
1 | 小明 | 1 | 项目部 |
2 | 香杠 | 2 | 学习部 |
3 | 小红 | 1 | 项目部 |
4 | 小王 | 3 | 管理部 |
5 | 小李 | 4 | 人事部 |
6 | 小鹏 | 3 | 管理部 |
7 | 小光 | 5 | 宣传部 |
8 | 小康 | null | null |
12.1.3 自连接
自连接查询可以是内连接,也可以是外连接
注意 : 必须给表取别名
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件...;
12.2 联合查询—union,union all
把多次的查询结果合并起来形成一个新的查询结果集
select 字段列表 from 表1...
union [all]
select 字段列表 from 表2... ;
注意 : 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
- union all : 会将全部的数据直接合并在一起,
- union : 会对合并后的数据去重