数据库MySQL学习第二天—插入修改删除语句的介绍、DDL（数据定义语言），TCL(事物控制语言)、变量，存储过程和函数

最新推荐文章于 2022-01-22 09:16:31 发布

小蕊的程序猿

最新推荐文章于 2022-01-22 09:16:31 发布

阅读量266

点赞数

分类专栏：数据库学习文章标签： mysql

本文链接：https://blog.csdn.net/jxbjr/article/details/111587730

版权

数据库学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

标题：数据库MySQL学习第二天—DDL、TCL、增删改语句、以及存储过程和函数

学习内容：

1、插入修改删除语句的介绍
2、DDL（数据定义语言）
3，TCL(事物控制语言)
4，变量，存储过程和函数

内容详情：

1、插入修改删除语句的介绍
插入语法:

方式一
insert into 表名（列名1,列名2,…）
values(值1,值2,…) ;
方式二
insert into 表名
set列名=值,列名=值,…

一般用方式一较多，方式一支持插入多行并且支持子查询，而方式二不支持。

修改语法：

修改单表
update 表名
set 列=新值,列=新值,…
where筛选条件;
修改多表
sq192语法:
update表1别名,表2别名
set列=值,…
where连接条件
and筛选条件;
sql99语法:
update表1别名
inner / left right join表2别名
on连接条件
set列=值,…
where筛选条件;

删除语法：

方式一
delete from 表名
where筛选条件
方式二
truncate table表名
delete 可以加where条件, truncate不能加
truncate效率高一丢丢
假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后,再插入数据,自增长列的值从1开始。
truncate删除没有返回值，delete删除有返回值
truncate删除不能回滚,delete删除可以回滚

2、 DDL（数据定义语言）

库的管理

库的创建

CREATE DATABASE IF NOT EXISTS 库名称;

库的删除

DROP DATABASE IF EXISTS 库名称;

表的管理
表的创建

Create table 表名(
列名列的类型(长度) 约束，
列名列的类型(长度) 约束，
…
）

表的修改

修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名新列名列的类型;
修改列的类型或者约束
ALTER TABLE 表名 MODIFY COLUMN 列名列的类型约束;
添加新列
ALTER TABLE 表名 ADD COLUMN 新列名列的类型;
删除列
AALTER TABLE 表名 DROP COLUMN 列名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;

表的删除

DROP TABLE IF EXISTS 表名；

表的复制

复制表的结构
CREATE TABLE copy like 表名
复制表的结构和数据
CREATE TABLE copy
SELECT *FROM 表名;
只复制部分数据
CREATE TABLE copy
SELECT 部分结构
FROM 表名
WHERE 条件
仅仅复制某些字段
CREATE TABLE copy
SELECT 某些字段
FROM 表名
WHERE 0;

数据类型简要介绍

整形

一般都是int/integer，如果插入超过最大值的话，插入的就是最大值

浮点型

float (M, D) double(M, D)

定点型

dec (M,D)或者decimal(M,D)

M代表小数部分+整数部分，D代表小数部分
M和D都可以省略
dacimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

字符型
在这里插入图片描述
日期型

常见约束

六大约束
NOT NULL:非空，用于保证该字段的值不能为空比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空比如座位号
CHECK:检查约束【mysql中不支持】比如年龄、性别
FOREIGN KEY :外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束,用于引用主表中某列的值

约束的添加分类:

列级约束:六大约束语法上都支持,但外键约束没有效果
表级约束:除了非空、默认,其他的都支持

列级约束

在后面加上约束
create table idcard(
id int PRIMARY KEY，
name varchar（20）NOT NULL，
列名类型长度约束
)

表级约束

constraint 约束名约束类型(字段名) 约束名可加可不加
create table idcard(
id int ，
name varchar（20），
CONSTRA工NT idpk PRIMARY KEY(id) ,
CONSTRA工NT namenl NOT NULL(id) ,
)

添加外键

表级约束添加外键
CONSTRA工NT fk_从表名_主表名 FOREIGN KEY(从表的外段)REFERENCES 主表名 (主表的字段)
CONSTRA工NT fk_stuinfo_major FOREIGN KEY(majorid)REFERENCES major (id)
修改表时添加外键
ALTER TABLE stuinfo ADD CONSTRA工NT fk_stuinfo_major FOREIGN KEY(majorid)REFERENCES major (id)
多了一个ALTER TABLE 从表名 ADD
要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
主表的关联列必须是一个key(一般是主键或唯—）
插入数据时,先插入主表,再插入从表
删除数据时，先删除从表,再删除主表

删除约束

删除非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段名类型 NULL;
删除默认约束
ALTER TABLE 表名 MODIFY COLUMN 字段名类型 ;
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
删除唯—
ALTER TABLE 表名 DROP INDEX 字段名;
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY fk_stuinfo_major（添加时的名）;

主键和唯一的区别

都保证了唯一性，但主键一个表至多有一个，唯一可以有多个，主键不允许为空，唯一可以为空。

标识列

又称为自增长列含义:可以不用手动的插入值，系统提供默认的序列值
特点:
标识列必须和主键搭配吗?不一定，但要求是一个key
一个表可以有几个标识列?至多一个
标识列的类型只能是数值型
标识列可以通过SET auto_increment_increment=3;设置步长可以通过手动插入值,设置起始值

3，TCL(事物控制语言)
事务:

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行.

事务的特性

原子性:一个事务不可再分割，要么都执行要么都不执行
一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性:—个事务的执行不受其他事务的干扰
持久性:一个事务一旦提交，则会永久的改变数据库的数据.

事务的使用

步骤1:开启事务
set autocommit=0;
start transaction;可选的，不写也行
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
步骤3:结束事务
comnit;提交事务
rollback;回滚事务

数据库事务的隔离

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制,就会导致各种并发问题:
脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段之后,若T2回滚,T1读取的内容就是临时且无效的.
不可重复读:对于两个事务T1,T2,T1读取了一个字段,然后T2更新了该字段.之后,T1再次读取同一个字段,值就不同了.
幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行之后,如果T1再次读取同一个表,就会多出几行.
数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响,避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别.数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱.

MySQL支持4种事务隔离级别.MySQL默认的事务隔离级别为: REPEATABLE READ
在这里插入图片描述
视图

视图:MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果
应用场景:
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂

创建视图

create view 视图名
as
查询语句;

优点

重用sql语句
简化复杂的sql操作，不必知道它的查询细节
保护数据，提高安全性

视图和表的区别

视图没有占有内存，视图一般不被修改。

4，变量，存储过程和函数

变量

系统变量

变量由系统提供,不是用户定义，属于服务器层面
分为全局变量和会话变量

自定义变量

用户自定义的变量
分为用户变量和局部变量

用户变量

针对于当前会话(连接)有效，同于会话变量的作用域
声明并初始化
SET @用户变量名=值;
或SET @用户变量名:=值;
或SELECT e用户变量名:=值;
赋值(更新用户变量的值>
方式一:
SET@用户变量名=值;
或SET @用户变量名:=值;
或SELECT @用户变量名:=值;
方式二:
SELECT 字段 INTO @变量名
FROM 表;

局部变量

仅仅在定义它的begin end中有效，其它同用户变量一样

存储过程

含义:一组预先编译好的SQL语句的集合，理解成批处理语句提高代码的重用性

优点

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法

DELIMITER $
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的sQL语句)
END $
注意:
参数列表包含三部分
参教模式参数名参数类型
举例:
IN stuname VARCHAR(20)
参数模式:
IN:该参数可以作为输入,也就是该参数需要调用方传入值
OUT:该参数可以作为输出:也就是该参数可以作为返回值
INOUT:该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
如果存储过程体仅仅只有一句话,BEGIN END可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号，
存储过程的结尾可以使用DELIMITER重新设置
语法:
DELIMITER 结束标记
DELIMITER $