MySQL基础——SQL语句（3）

一、引言

MySQL是目前最流行的关系型数据库管理系统之一，被广泛应用于Web开发、数据分析等领域。为了更好地使用MySQL，我们需要先了解什么是数据库，以及如何使用SQL语句对数据库进行操作。本文将为您介绍部分常用的SQL语句，帮助您更好地使用MySQL。

二、SQL语句（3）

（1）SQL的分类

1. DQL：数据库查询语言，进行查询操作的SQL
2. DCL：数据库控制语言，对数据库用户进行权限设定和更改的SQL
3. DDL：数据库定义语言，进行库、表、视图、索引等创建和销毁的SQL
4. DML：数据库操作语言，对数据进行增删改的SQL
5. TCL：事务控制语言，控制事务操作的SQL

DCL：通常由数据库超级管理员操作

（2）DDL-定义

1. 数据库的创建和销毁

1-1创建

create database [if not exists] 数据库名 [default charset 字符集]

• if not exists：表示如果不存在才会创建
• default charset 字符集：设置数据库字符集

create DATABASE 2302demo2 -- 如果数据库已经存在，会报错
create DATABASE if not exists 2302demo2 -- 如果数据库已经存在，不会报错
create DATABASE if not exists 2302demo2 default charset utf8mb4 -- 直接设置字符集

1-2销毁

drop database [if exists] 数据库

• if exists：如果存在才会销毁

drop database 2302demo2 -- 如果不存在，报错
drop database if exists 2302demo2 -- 如果不存在，不做操作，不会报错

2.表的创建和销毁

（一）创建

create table [if not exists] 表名(
	字段名	数据类型	[约束],
   字段名	数据类型	[约束],
   ...[最后一行内容末尾不能添加,]
)

1. 命名

   • 不区分大小写，不能使用关键字，每部分之间使用_连接
   • 表名：通常以t_开头（如：t_student）
   • 字段名：关键列通常以**表名缩写_**开头（如：学生id：stu_id）

2. 数据类型

• 需要掌握的数据类型：
  • 整数：tinyint、int、integer
  • 小数：decimal
  • 字符串：varchar
  • 枚举：enum
  • 日期：datetime
• 实战建议：
  1. 只有数字一定大于等于0并且一定不会进行减运算时，才会用到无符号数，否则都是默认的有符号数
  2. float和double存在精度问题，后续MySQL将不再支持
  3. char和varchar底层实现完全一致，只是空间分配的模式不同，推荐使用varchar
  4. date和time不够实用，timestamp下限太高上限太低，推荐datetime
  5. varchar存储时会进行预存，所以为了节省空间，长度不是越长越好，要适中

-- person表：id(唯一标识)、姓名、性别、年龄、身高、出生日期
create table t_person(
	 person_id  int,
	 person_name varchar(30),
	 sex enum('男','女','奥特曼'),
	 age  tinyint UNSIGNED,-- 无符号数  没有负数
	 height decimal(4,1),
	 birthday datetime
)

（二）约束

约束名	解释	说明
primary key	主键	标志一列为主键列
not null	非空	标志一列的值不能为null
unique	唯一	约束一列的值不能重复
default	默认	给一列设置默认值
foreign key	外键	标志一列为外键列

check：检查约束|自定义约束 MySQL不支持

普通约束

1. 主键

   • 作用：作为唯一标识辅助锁定某行数据
   • 特点：非空+唯一
   • 使用：一张表只能存在一个主键列

2. 非空：

   • 使用：字段值不能为null，必须进行值的设定

3. 唯一：

   • 特点：字段值不能重复，值可以为null，并且可以存在多个null
     • 不约束null值，只约束已经存在的值

   非空+唯一 != 主键

4. 默认：

   • 特点：当设置了默认约束，则在该字段未显式赋值时，值默认为设置的默认值

列级约束

• 将约束声明在字段的声明语法中

create table t_student(
		stu_id	 int	primary key, -- 主键
		stu_name	varchar(30)		not null  unique,-- 非空   唯一
		sex		enum('男','女','奥特曼')	default'奥特曼',
		age	 tinyint	unsigned,
		score		decimal(3,1),
		birthday	datetime
)

表级约束

• 将约束单独的声明到所有字段的下方，not null和default不能设置表级约束

约束类型(字段名)

create table t_student(
		stu_id	 int, 
		stu_name	varchar(30)		not null,-- 非空
		sex		enum('男','女','奥特曼')	default'奥特曼',
		age	 tinyint	unsigned,
		score		decimal(3,1),
		birthday	datetime,
		-- 添加表级约束
		primary key(stu_id),-- 给学生id添加主键约束
		unique(stu_name) -- 给学生姓名添加唯一约束
)

外键约束

• 作用：用来表示两张表之间的关联关系，必须结合另一张表使用

  不是所有的关系字段都必须存在外键约束

  • 外键字段一定是关系字段
  • 关系字段并不一定存在外键约束

• 特点：

  1. 必须连接另一张表的主键列或唯一列

  2. 外键列的值不可自定义，必须来自于关联字段已经存在的值

     • 只对外键列中值不为null的做约束

  3. 外键列值可以为null

  4. 必须使用表级约束创建

       foreign key(需要添加外键约束的字段名) references 其他表的表名(其他表中的连接字段)
  

• 使用：

  1. 建表顺序：先建没有外键的表，再建有外键的表
  2. 删表顺序：先删有外键的表，再删没有外键的表
  3. 命名：外键字段的名字与数据类型应与关联字段保持一致

-- 班级表：id、班级名称
create table t_class(
		class_id  int  primary key  auto_increment, -- 主键、自增
		class_name  varchar(10)  not null  unique -- 唯一+非空
)

-- 老师表：id、姓名、所属的班级id
create table t_teacher(
		teacher_id  int  primary key  auto_increment,-- 主键、自增
	  teachet_name  varchar(30) not null,-- 非空
	  class_id  int ,-- 所属班级id
	  -- 通过表级约束添加外键约束
		FOREIGN key(class_id) REFERENCES t_class(class_id)

)

（三）销毁

drop table [if exists] 表名

-- 先删老师表
drop table t_teacher
-- 删除班级表
drop table t_class

（3）DML-增删改

1.增

insert into 表名(列名1,列名2,...) values(值1,值2,...)  -- 给指定字段添加数据，值的顺序应与语法中的列名保持一致
insert into 表名 values(值1,值2,...) -- 给所有字段添加数据，值的顺序应与建表顺序保持一致

-- 给person表添加数据
insert into t_person(person_id,person_name,sex,age,height,birthday) values(1,'zhangsan','男',20,180.5,'2000-01-01')

-- 增：insert into 表名 values(值1,值2,..)
-- 注意：默认手动给表中所有字段添加数据，值的顺序必须与表中字段定义保持一致
insert into t_person values(2,'lisi','男',21,170.7,'2000-02-02')

-- 给person表中的部分字段添加值
insert into t_person(person_name,age) values('wangwu',23)

-- 同时添加多条数据：insert into 表名(列名1,列名2..) values(值1,值2,..),(值1,值2,..),(值1,值2,..),..
insert into t_person(person_name,age) values('zhaoliu',22),('sunqi',21)

• 可以为某个数据类型为整型的列设置自增约束，使其自动维护，每次新增数据值+1，通常主键列会设置自增

    create table 表名(
    	字段名 数据类型 auto_increment	--设置自增
    )
  

    create table t_student(
    		stu_id	 int auto_increment, -- 设置自增
    		...,
    		-- 添加表级约束
    		primary key(stu_id),-- 给学生id添加主键约束
    		...
    )
  

2.删

delete from 表名 [where 条件]

• 不添加where条件则删除表中所有数据，添加where条件只会删除符合条件的数据

-- 删除姓名为zhangsan的学生信息
delete from t_student where stu_name='zhangsan'
-- 删除表中所有数据
delete from t_student

• 也可以使用表截断的方式删除表中所有数据

    truncate table 表名
  

  • 与delete删除所有数据的区别：
    • delete是在物理空间中将数据一条一条的删除，效率低，自增不会重新开始
    • 表截断是在物理空间中直接将表的空间删除，效率高，自增会重新开始

3.改

update 表名 set 列名=新值,列名=新值,... [where 条件]

• 不添加where条件，会修改整张表的指定字段
• 添加where条件，只会修改符合要求的行数据

-- 修改李四的年龄为22
update t_student set age=22 where stu_name='lisi'
-- 将所有学生的年龄改为20
update t_student set age=20

（4）DDL-补充

索引

• 是数据库为了提高查询效率所提供的一种存储机制

1.查看

show index from 表名

2.创建

create index 索引名 on 表名(字段名)

3.销毁

drop index 索引名 on 表名

-- 查看员工表的索引
show index from employees
-- 为first_name创建索引
create index emp_name on employees(first_name)
-- 根据first_name字段进行查询：查询起始名为neena的员工信息
-- explain：用来查看SQL语句的索引使用情况
explain select * from employees where first_name='Neena'
-- 销毁索引
drop index emp_name on employees

4.特点-面试点

1. 使用：索引无须手动使用，当使用某个索引列作为筛选条件时，则自动使用其索引
2. 作为：提高查询效率
3. 索引并不是越多越好，会占用和浪费空间并且会影响增删改的效率，所以只需要给必要字段添加索引即可
4. 主键，外键和唯一列会自动创建索引
5. 索引在特定情况下会失效，如空值查询、占位符在第一位的模糊查询等

（5）TCL-事务

• 作用：是保证SQL执行完整性的机制，可以确保事务中的多条SQL操作要么同时成功，要么同时失败

1.开启事务

begin|start transation

2.结束事务

提交：commit -- 确认操作
回滚：rollback  -- 撤销操作

-- 创建一张账户表id、username、password、余额
create table t_account(
		acc_id	int primary key auto_increment,
		username	varchar(20)	not null,
		password	varchar(10),
		balance	decimal(5,1)
)

-- 添加信息
insert into t_account(username,password,balance) values('王璐瑶','123123',1000),('何宇航','123123',1000)

-- 模拟转账操作
-- 王璐瑶给何宇航转520  
-- 流程：王璐瑶余额-520  480  何宇航余额+520  1520

-- 开启事务
begin
update t_account set balance=balance-520 where username='王璐瑶'
update t_account set balance=balance+520 where username='何宇航'
-- 结束事务
-- 1. 回滚事务：当SQL执行有异常时  撤销
rollback
-- 2. 提交事务：SQL执行无异常时  确认
commit

-- 结合后台代码的逻辑判断

开启事务
执行SQL：
	update t_account set balance=balance-520 where username='王璐瑶'
	update t_account set balance=balance+520 where username='何宇航'
if(sql执行无异常){
	commit;
}else{

	rollback;
}

3.特性-面试点

• ACID
• A：原子性。多条SQL要么同时成功，要么同时失败
• C：一致性。事务执行前后，数据状态是一致的
• I：隔离性。每个事务都是单独的个体，事务之间各自的执行结果互不影响
• D：持久性。事务一旦结束，对数据库的改变将是永久性的，无法再次撤回

Atomicity, Consistency, Isolation,Durability

三、结语

MySQL是一种功能强大的关系型数据库管理系统，可以用于各种应用场景，如Web开发、数据分析等。通过学习MySQL基础知识和SQL语句，可以更加高效地管理和维护数据库，提升工作效率。