MySQL基础

Hnx
133 阅读11分钟

MySQL

一、MySQL概述

数据库

数据库管理系统

SQL

二、SQL

sql通用语法

1.sql语句可以单行或多行书写，以分号结尾。

2.sql语句mysql数据库的sql语句不区分大小写，关键字建议使用大写

3.注释：

- 单行注释：--内容 或 # 内容（mysql）特有
- 多行注释： /* 内容 */

SQL分类

  • DDL 数据定义语言，定义数据库对象
  • DML 数据操作语言，对数据库表中的数据进行增删改
  • DQL 数据查询语言，查询数据库中表的记录
  • DCL 数据控制语言，创建数据库用户、控制数据库的访问权限

DDL 数据定义语言

  1. 数据库操作
  • 查询

​ 查询所有数据库： SHOW DATABASES;

​ 查询当前数据库： SELECT DATABASE();

  • 创建 CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则];
  • 删除 DROP DATABASE [IF EXISTS] 数据库名;
  • 使用 USE 数据库名;
  1. 表操作-查询
  • 查询当前数据库所有表：SHOW TABLES;
  • 查询表结构：DESC 表名;
  • 查询指定表的建表语句：SHOW CREATE TABLE 表名;
  1. 表操作-创建
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释]，
	字段2 字段2类型[COMMENT 字段1注释]，
  ...
  字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

create table tb_user( 
  id int comment '编号', 
  name varchar(50) comment '姓名', 
  age int comment '年龄', 
  gender varchar(1)  comment '性别'
) comment '用户表';

  1. 表操作-数据类型

  2. 表操作-修改 删除

    添加字段 ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释][约束];

    修改数据类型 alter table 表名 modify 字段名 新数据类型（长度）;

    修改字段名和字段类型 alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

    删除字段 alter table 表名 drop 字段名;

    修改表名 alter table 表名 rename to 新表名;

    删除表 drop table [if exists] 表名;

    删除指定表并重新创建 truncate table 表名；

DML数据操作语言

  • 添加数据 insert
  • 修改数据 update
  • 删除数据 delete

  1. 添加数据

    • 给指定字段添加数据 insert into 表名（字段名1，字段名2，） values （值1，值2，）；

    • 给全部字段添加数据 insert into 表名 values （值1， 值2，）；

    • 批量添加数据 insert into 表名（字段名1，字段名2，） values （值1，值2，），（值1，值2，），（值1，值2，）；

      insert into 表名 values （值1， 值2，），（值1， 值2，），（值1， 值2，）；

    字符串和日期要包含在引号中

  2. 修改数据

    update 表名 set 字段名1=值1, 字段名2=值2, ... [where 条件];

  3. 删除数据

    delete from 表名 [where 条件];
truncate table 表名; # 删除整个表的数据，实际是删除表后重新创建一个表

DQL数据查询语言

select
	字段列表 [[as] 别名]
from
	表名列表
where 
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

  1. 基础查询

    • 查询多个字段

      select 字段1，字段2， from 表名;

      select * from 表名;

    • 设置别名

      select 字段1[as 别名], ... from 表名;

    • 去除重复记录

      select distinct 字段列表 from 表名;

  2. 条件查询

    • 语法

      select 字段列表 from 表名 where 条件列表

      占位符 _ 一个字符，%任意个字符 like _%

  3. 聚合函数

    将一列数据作为一个整体

    函数功能
    count统计数量
    max最大值
    min最小值
    avg平均值
    sum求和

  4. 分组查询

    select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

    where与having的区别

    执行实际不同：where分组前过滤，having分组后过滤

    判断条件不同：where不能对聚合函数进行判断，having可以

  5. 排序查询

    select 字段列表 from 表名 order by 字段1 排序方式，字段2 排序方式；

    asc升序 desc降序 升序排序可以省略

  6. 分页查询

    select 字段列表 from 表名 limit 起始索引，查询记录数；# 起始索引从0开始

  7. 执行顺序

    from where

DCL数据控制语言

管理数据库用户、控制数据库的访问权限。

  1. 管理用户

    • 查询用户

      use mysql;
select * from user;

    • 创建用户

      create user '用户名'@'主机名' identified '密码';
create user '用户名'@'%' identified '密码'; # 任意主机都能登录

    • 修改用户密码

      alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

    • 删除用户

      drop user '用户名'@'主机名';

  2. 权限控制

    '''
权限		说明
all, all privileges		所有权限
select 	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter		修改表
drop		删除数据库、表、视图
create	创建数据库、表
'''

    • 查询权限

      show grants for '用户名'@'主机名';

    • 授予权限

      grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

    • 撤销权限

      revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

三、函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

函数功能
concat(s1,s2,..,sn)字符串拼接，将s1,s2,...sn拼接成一个字符串
lower(str)将字符串str全部转为小写
upper(str)将字符串str全部转为大写
lpad(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)右填充
trim(str)去掉字符串头部和尾部的空格
substring(str, strat, len)返回从字符串str从start位置起的len个长度的字符串，索引从1开始
select concat('hello', 'mysql');
-- 更新工号，统一为5位，不足补0
update emp set workno = lpad(workno, 5, '0');

数值函数

函数功能
ceil(x)向上取整
floor(x)向下取整
mod(x, y)返回x/y的模
rand()返回0~1内的随机数
round(x, y)求参数x的四舍五入的值，保留y位小数
select ceil(1.1);
-- 生成6位随机数
select lpad(round(rand()*1000000, 0), 6, '0');

日期函数

函数功能
curdate()返回当前日期
curtime()返回当前时间
now()返回当前日期和时间
year(date)获取指定date的年份
month(date)获取指定date的月份
day(date)获取指定date的日期
date_add(date, interval expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1, date2)返回起始时间date1和结束时间date2之间的天数
select YEAR(now());
select date_add(now(), INTERVAL 70 DAY);
select datediff('2021-12-01', '2021-12-02') -- 第一个日期减第二个日期
-- 查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

函数功能
if(value, t, f)如果value为true，则返回t，否则返回f
ifnull(value1, value2)如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] ... else [default] end如果val1为true，返回res1，...否则返回default默认值
case [expr] when [val1] then [res1] ... else [default] end如果expr的值等于val1，返回res1，...否则返回default默认值
select if(true, 'ok', 'error');
-- 查询emp表的员工姓名和工作地址（北京/上海 --》一线城市，其他--->二线城市）
select name,
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

四、约束

  1. 概述

    • 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

    • 目的：保证数据库中数据的正确、有效性和完整性。

    • 分类

      约束描述关键字
      非空约束限制该字段的数据不能为nullnot null
      唯一约束保证该字段的所有数据都是唯一、不重复的unique
      主键约束主键是一行数据的唯一标识，要求非空且唯一primary key
      默认约束保存数据时，如果未指定该字段的值，则采用默认值default
      检查约束保证字段值满足某一个条件check
      外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性foreign key

  2. 约束演示

  3. 外键约束

    外键是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

    • 语法

      create table 表名(
	字段名 数据类型，
	
	[constraint] [外键名称] foreign key（外键字段名） references 主表（主表列名）
)；

alter table 表名 add constraint 外键名称 foreign key（外键字段名） references 主表（主表列名）；
-- 删除外键
alter table 表名 drop foreign key 外键名称;

    • 删除/更新行为

      行为说明
      no action当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新。
      restrict当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新。
      cascade当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有，则也删除/更新外键在字表中的记录。
      set null当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置字表中该外键值为null（这就要求该外键允许取null）。
      set default父表有变更时，字表将外键设置成一个默认的值。
      alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

五、多表查询

  1. 多表关系

    • 一对多

      在多的一方建立外键

    • 多对多

      建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

    • 一对一

      在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的（unique）

  2. 多表查询概述

    -- 多表查询
select * from emp, dept where emp.dept_id = dept.id;

    • 连接查询

      内连接

      外连接

      自连接

    • 子查询

  3. 内连接

    内连接查询的是两张表交集的部分

    • 隐式内连接

      select 字段列表 from 表1， 表2 where 条件;

    • 显示内连接

      select 字段列表 from 表1 [inner] join 表2 on 连接条件;

      演示：

      -- 1. 查询每一个员工的姓名及关联的部门的名称（隐式）
-- 表结构：emp, dept
-- 连接条件: emp.dept_id = dept.id
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;

-- 查询每一个员工的姓名及关联的部门名称
-- 表结构 emp, dept
-- 连接条件 emp.dept_id, dept_id
select emp.name dept.name from emp inner join dept on emp.dept_id = dept.id;

  4. 外连接

    • 左外连接

      完全包含左表数据

      select 字段列表 from 表1 left [outer] join 表2 on 条件;

select * from emp e left outer join dept d on e.dept_id = d.id

    • 右外连接

      select 字段列表 from 表1 right [outer] join 表2 on 条件;

  5. 自连接

    select 字段列表 from 表A 别名A join 表A 别名B on 条件;
-- 查询员工及其领导名字
select a.name, b.name from emp a join emp b on a.manageid = b.id;
select a.name, b.name from emp a left join emp b on a.manageid = b.id;

  6. 联合查询

    select 字段列表 from 表A
union [all]
select 字段列表 from 表B;

  7. 子查询

    SQL语句中嵌套select语句，称为嵌套查询，又称子查询

    select * from t1 where column1 = (selcect column1 from t2);

    根据子查询结果不同，分为：

    • 标量子查询

    • 列子查询

      常用操作符：

      操作符描述
      in在指定的集合范围之内，多选一
      not in不在指定的集合范围之内
      any子查询返回列表中，有任意一个满足即可
      some与any等同
      all子查询返回列表的所有值都必须满足
      select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部')

    • 行子查询

      常用操作符：=，<>, in, not in

    • 表子查询

  8. 多表查询案例

六、事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些要求要么同时成功，要么同时失败。

事务操作

  • 查看设置事务提交方式

    select @@autocommit;
set @@autocommit=0;

  • 提交事务

    commit;

  • 回滚事务

    rollback;

  • 开启事务

    start transaction;
-- 或
begin;

事务四大特性

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须是所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改编就是永久的。

并发事务问题

  • 脏读：一个事务读到另外一个事务还没有提交的数据
  • 不可重复度：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，有发现这行数据已经存在。

事务隔离级别

隔离级别脏读不可重复度幻读
read uncommittedVVV
read committedXVV
repeatable read(默认)XXV
serializableXXX
-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable}
avatar
文章
阅读
粉丝