MYSQL(基础篇)
数据模型
SQL
通用语法
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释:
- 单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
- 多行注释: /* 注释内容 */
DDL
数据定义语言,用来定义数据库对象(数据库,表,字段)
数据库操作
1.查询所有数据库:
show databases;
2.查询当前数据库:
select database();
3.创建:
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
4.删除:
drop database [if exists] 数据库名;
5.使用:
use 数据库名;
表操作
一、查询
1.查询当前数据库所有表:
show tables;
2.查询表结构:
desc 表名;
3.查询指定表的建表语句:
show create table 表名;
二、创建
create table 表名(
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释],
字段3 字段3类型 [comment 字段3注释],
......
字段n 字段n类型 [comment 字段n注释]
) [comment 表注释];
注意:[…]为可选参数,最后一个字段后面没有逗号
eg:
create table message(
num int unsigned comment '编号',
per_num char(10) comment '员工工号',
name varchar(10) comment '员工姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
id_num char(18) comment '身份证号',
join_time date comment '入职时间'
) comment '员工信息表';
数据类型
MySQl中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型。
1.数值类型
注意:字段名 double(数值最大长度,小数位数)
2.字符串类型
char(字符串长度):不超过设定的长度一律以设定的长度空间储存,字符串未到该长度则自动用空格补全
varchar(字符串长度):设定长度最大值,以字符串实际长度空间储存
3.日期时间类型
三、修改
1.添加字段:
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
2.修改数据类型:
alter table 表名 modify 字段名 新数据类型(长度);
3.修改字段名和字段类型:
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
4.删除字段:
alter table 表名 drop 字段名;
5.修改表名:
alter table 表名 rename to 新表名;
四、删除
1.删除表:
drop table [if exists] 表名;
2.删除指定表,并重新创建该表:
truncate table 表名;
注意:在删除表时,表中的数据也会被全部删除(表结构及其列、约束、索引等保持不变)
DML
数据操作语言,用来对数据库表中的数据进行增删改
- 添加数据(insert)
- 修改数据(update)
- 删除数据(delete)
添加数据
1.给指定字段添加数据:
insert into 表名 (字段名1, 字段名2,...) values (值1, 值2,...);
2.给全部字段添加数据:
insert into 表名 values (值1, 值2,...);
3.批量添加数据:
insert into 表名 (字段名1, 字段名2,...) values (值1, 值2,...), (值1, 值2,...), (值1, 值2,...);
insert into 表名 values (值1, 值2,...), (值1, 值2,...), (值1, 值2,...);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
修改数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2,... [where 条件];
删除数据
delete from 表名 [where 条件];
注意:
- delete语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
- delete语句不能删除某一个字段的值(可以使用update)。
DQL
数据查询语言,用来查询数据库中表的记录
基本查询
1.查询多个字段:
select 字段1, 字段2, 字段3... from 表名;
select * from 表名;
2.设置别名
select 字段1 [as 别名1], 字段2 [as 别名2] ... from 表名;
3.去除重复记录
select distinct 字段列表 from 表名;
条件查询
1.语法
select 字段列表 from 表名 where 条件列表;
2.条件
聚合函数
1.介绍
将一列数据作为一个整体,进行纵向运算。
2.常见聚合函数
3.语法
select 聚合函数(字段列表) from 表名;
分组查询
1.语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
eg:
select gender, count(*) from user where age < 20 group by gender having count(*) > 1;
2.where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
- 判断条件不同:where不能对聚合函数进行判断,而having可以
注意:
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其它字段无任何意义
排序查询
1.语法
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
2.排序方式
- ASC:升序(默认值)
- DESC:降序
注意:如果是多字段排序,当第一个字段值相同是,才会根据第二个字段进行排序
分页查询
1.语法
select 字段列表 from 表名 limit 起始索引, 查询记录数;
注意:
- 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit10
DCL
数据控制语言,用来创建数据库用户、控制数据库的访问权限
管理用户
1.查询用户:
use mysql;
select * from user;
2.创建用户:
create user '用户名'@ '主机名' identified by '密码';
3.修改用户密码:
alter user '用户名'@ '主机名' identified with mysql_native_password by '新密码';
4.删除用户:
drop user '用户名'@ '主机名';
注意:
- 主机名可以使用%通配,表示可以在任意主机访问。而使用localhost表示只能在当前主机访问。
- 这类SQL开发人员操作的比较少,主要是DBA(数据库管理员)使用。
权限控制
MySQL中定义了很多种权限,常用的有以下几种:
1.查询权限:
show grants for '用户名'@ '主机名';
2.授予权限:
grant 权限列表 on 数据库.表名 to '用户名'@ '主机名';
3.撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@ '主机名';
注意:
- 多个权限之间,使用逗号分隔。
- 授权时,数据库名和表名可以使用*进行统配,代表所有。
函数
select 函数(参数);
字符串函数
MySQL中内置了很多字符串函数,常用的如下:
应用实例:
update user set id = lpad(id,5,0); //将id统一为5位,不足5位前面补0
数值函数
常见的数值函数如下:
应用实例:
select rpad(round(rand() * 1000000,0),6,0); //生成6位的随机验证码
日期函数
应用实例:
select name, datediff(now(),workdate) as 'entry base' from user order by `entry base` asc;
//查询所有员工的入职天数并按照升序排列
流程函数
流程函数也是很常用的函数,可以在SQL语句中实现筛选,从而提高语句的效率。
应用实例1:
select name,
case workadress
when '上海' then '一线城市'
when '北京' then '一线城市'
else '其他城市' end as '工作地址'
from user; //查询所有员工的工作地址,若为北京或上海显示一线城市,否则为其它城市
应用实例2:
约束
概述
1.概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
2.目的:包装数据库中数据的正确、有效性、一致性和完整性。
3.分类
注意:约束是作用与表中字段上的,可以在创建表/修改表的时候添加约束。
演示
按以上需求创建表结构:
create table emp (
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) comment '约束测试'
外键约束
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
员工表中的dept_id关联的是部门表中的主键,dept_id称为外键。子表指的是具有外键的这张表,父表指的是外键所关联的这张表,有时候也称为主表和从表。
1.添加外键:
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
) //创建表结构时添加
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名); //创建表结构之后添加
2.删除外键:
alter table 表名 drop foreign key 外键名称;
外键删除更新行为
删除/更新行为:
语法:
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;
多表查询
多表关系
1.一对多(多对一)
eg:一个部门对应多个员工,一个员工对应一个部门
实现:在多的亿方建立外键,指向一的一方的主键
2.多对多
eg:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张表,中间表至少包含两个外键,分别关联两方主键
3.一对一
eg:用户宇用户详情的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(UNIQUE)
多表查询概述
笛卡尔积:笛卡尔乘积是指在数学中两个集合的所有组合情况(在多表查询时,需要消除无效的笛卡尔积)
多表查询语法:
select * from 表名, 表名, ... [where 条件];
多表查询分类
连接查询
内连接
相当于查询A、B交集部分数据
1.隐式内连接:
select 字段列表 from 表1 [别名1], 表2 [别名2] where 条件 ...;
2.显式内连接:
select 字段列表 from 表1 [别名1] [inner] join 表2 [别名2] on 连接条件 ...;
注意:如果为表起了别名,那么我们就不能再通过表名来限定字段
外连接
左外连接:查询左表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 [别名1] left [outer] join 表2 [别名2] on 条件 ...;
右外连接:查询右表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 [别名1] right [outer] join 表2 [别名2] on 条件 ...;
自连接
当前表宇自身的连接查询,可以是内连接查询,也可以是外连接查询。自连接必须使用表别名,将一张表看为两张表。
语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;
联合查询
对于union联合查询,就是把多次查询的结构合并起来,形成一个新的查询结果集。
语法:
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
子查询
SQL语句中嵌套select语句 称为嵌套语句,又称子查询。
语法:
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是insert / update / delete / select 的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:where之后、from之后、select之后。
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式。
常用的操作符:= <> > >= < <=
eg:
//查询研发部的员工
select * from emp where id = (select id from dept where name = '研发部');
列子查询
子查询返回的结果是一列·(可以是多行)
常用的操作符:in、not in、any、some、all
eg:
select * from emp where age < all (select age from emp where dept_id in (select id from dept where name = '研发部' or name = '市场部'));
select * from emp where age < some (select age from emp where dept_id in (select id from dept where name = '研发部' or name = '市场部'));
select * from emp where age < any (select age from emp where dept_id in (select id from dept where name = '研发部' or name = '市场部'));
行子查询
子查询返回的结果是一行(可以是多列)
常用的操作符:=、<>、in、not in
eg:
select * from emp where (age,dept_id) = (select age,dept_id from emp where name = '小胜');
表子查询
子查询返回的结果是多行多列的,将表子查询返回的结果作为一张临时表再和其它表进行连查操作。
常用的操作符:in
eg1:
select * from emp where (age,dept_id) in (select age,dept_id from emp where name = '小胜' or name = '小宇');
eg2:
select e.*, d.* from (select * from emp where age <= 20) e left join dept d on e.dept_id = d.id;
事务
事务的简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQl的事务是自动提交的,也就是说,当执行一条DML语句,MySQl会立即隐式的提交事务。如果我们想要把若干个操作放在一个事务的范围内,那么我们就需要手动地去开启事务,手动地提交事务。如果出现异常,要手动地进行事务的回滚。
事务操作
方式一:
查看/设置事务提交方式:
select @@autocommit;
set @@autocommit = 0;
提交事务:
commit;
回滚事务:
rollback;
方式二:
开启事务:
start transaction 或 begin;
提交事务:
commit;
回滚事务:
rollback;
事务的特性
- 原子性:事务是不可风格的最小操作单元,要么全部成功,要么全部失败。
- 一致性:事务完成时,必须使所有的数据都保持一致状态。
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
多个并发事务在事务的执行过程当中出现的问题。
**脏读:**一个事务读到另外一个事务还没有提交的数据
**不可重复读:**一个事务先后读取同一条记录,但两次读取的数据不同
**幻读:**一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好出现了幻影
事务隔离级别
1.查看事务隔离级别
select @@transaction_isolation;
2.设置事务隔离级别
set [session|global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];
注意:事务隔离级别越高,数据越安全,但是性能越低。