MySQL数据库(二):DML、DDL、TCL、存储过程

目录

1 数据操作语言(DML)

 1.1 插入语句

1.2 修改语句

1.2.1 修改单表记录

1.2.2 修改多表记录

1.3 删除语句 

1.3.1 删除部分

1.3.2 删除整张表

 2 数据定义语言(DDL)

 2.1 库的管理

 2.2 表的管理

 2.3 常见的数据类型

2.3.1 整型

 2.3.2 浮点型

2.3.4 字符型

2.3.5 日期类型

2.4 常见的约束

3 事务控制语言(TCL)

3.1 事务的创建 

3.2 为什么需要使用事务?

3.3 数据库提供的4中事务隔离级别:

3.4 在MySQL中设置隔离级别

 4 存储过程

4.1 什么是存储过程

4.2 基本使用

4.3 为何要使用存储过程


1 数据操作语言(DML)

用于添加、删除、修改数据库记录,并检查数据库完整性;

关键字:insert、delete、update等

 1.1 插入语句

方式一:

语法: INSERT INTO 表名(字段名1,字段名2...) VALUES(值1,值2...)

       1  插入的值的类型与相应字段的类型一致或兼容;

       2  可以为NULL的列如何插入值:

                2.1.在对应的字段名位置写入null;

                2.2.字段名和值都不写;

       3  字段的顺序可以改变;

       4  字段和值的个数必须一致;

       5  可以省略字段名,这样默认添加所有的字段;

 方式二:

语法:INSERT INTO 表名 SET 字段名=值,....

方式一支持多行插入:多条插入语句间用逗号分隔;

方式一支持子查询;

1.2 修改语句

1.2.1 修改单表记录

语法:UPDATE 表名 SET 列1=新值,列2=新值,....

WHERE 筛选条件;

1.2.2 修改多表记录

sql92语法:

UPDATE 表1 别名,表2 别名 SET 列1=新值,列2=新值,...

WHERE 连接条件 AND 筛选条件;

 sql99语法:

UPDATE 表1 别名

【inner || left || right】 JOIN 表2 别名

ON 连接条件

SET 列1=新值,列2=新值,...

WHERE 筛选条件;

1.3 删除语句 

1.3.1 删除部分

单表删除

语法:DELETE FROM 表名 WHERE 筛选条件

多表的删除

sql92语法:

DELETE 表1别名,表2别名(需要删除谁的就加上谁的别名)

FROM 表1 别名,表2 别名

WHERE 连接条件  AND  筛选条件  

sql99语法:

DELETE 表1别名,表2别名

FROM 表1 别名

【inner || left || right】 JOIN 表2 别名

ON 连接条件

WHERE 筛选条件; 

1.3.2 删除整张表

 TRUNCATE TABLE 表名;(不加where条件)

DELETE与TRUNCATE区别

1.truncate不能加where条件,而delete可以加where条件
2.truncate的效率高
3.truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始
4.delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
5.truncate删除不能回滚,delete删除可以回滚

 2 数据定义语言(DDL)

关键字:create、drop、alter

 2.1 库的管理

库的创建(create):

语法: CREATE DATABASE【IF EXISTS(如果该库不存在,则创建)】 库名;

库的修改(alter):

修改库的字符串:ALTER DATABASE 库名 CHARACTER SET【gdk、utf8.....】;

库的删除(drop):

DROP DATABASE IF EXISTS 库名;  

创建库和表的通常写法:

先删除,在创建 

DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名;

DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名;

 2.2 表的管理

表的创建

语法:CREATE TABLE 表名(

        列名 列的类型【(长度) 约束】,

        列名 列的类型【(长度) 约束】,

        ....

        列名 列的类型【(长度) 约束】

表的修改/删除

 ALTER TABLE 表名【add || drop || modify || change】 COLUMN 列名 【列的类型 约束】;

COLUMN可以省略、add-添加、drop-删除、modify-修改、change-改列名

表的复制 

1.复制表的结构

CREATE TABLE 表名 LIKE 旧表;

2.复制表的结构+数据

CREATE TABLE 表名

SELECT 查询列表 FROM 旧表 【WHERE 筛选】;

 2.3 常见的数据类型

2.3.1 整型

MySQL的整型类型一共五种:tinyint、smallint、mediumint、int/integer、bigint;以上所占的字节分别为:1、2、3、4、8。

特点:

  1. 都可以设置无符号和有符号,默认有符号,通过unsigned(加在数据类型后)设置无符号
  2. 如果超出了范围,会报out or range异常,插入临界值
  3. 长度可以不指定,默认会有一个长度
  4. 长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill(加在数据类型后),并且默认变为无符号整型

 2.3.2 浮点型

定点数:decimal(M,D),用于保存精度要求较高的数

浮点数:float(M,D)、double(M,D)

特点:

D:代表小数点后保留几位;

M:小数部位与整数部位的和;

如果超过范围,则插入临界值。

原则:所选择的数据类型越简单越好,能保存数值的类型越小越好 

2.3.4 字符型

char:固定长度的字符,写法 CHAR(M),最大长度不能超过M,其中M可以省略,默认M为1

varchar:可变长度的字符串,写法VARCHAR(M),最大长度不能超过M,M不可省略

区别: char中存放的内容即使为达到M,也会分配长度M的空间给这个数据,比较消耗空间,而varchar中会根据存放的内容分配存储空间,对空间的消耗比较节省。

char和varchar都只是用来保存MySQL中较短的字符串,如果需要保存较长的字符串可用‘text’

binary和varbinary类型和char、varchar类似,只是它们用来保存较短的二进制字符串,较长的二进制字符串用 blob。

 枚举(enum)

要求插入的值必须属于列表中指定的值之一

列表成员为1~255时,需要1个字节存储;

列表成员为255~65535时,需要2个字节存储;

语法 CREATE TABLE 表名(

        c1 ENUM('a','b','c')

);

INSERT INTO 表名 VALUES('a');只能插入列表中有的值,否则会报错,且不区分大小写;

集合(set)

和enum类型类似,里面可以保存0~64个成员。但是set类型可以一次选取多个成员,成员数量的不同,存储所占的字节数也不一样

enum就像选择题中的单选一样,而set就是多选

2.3.5 日期类型

日期和时间类型
字节最小值最大值
date41000-01-019999-12-31
datetime81000-01-01 00:00:009999-12-31 23:59:59
timestamp4197001010800012038年的某个时刻
time3-838:59:59838:59:59
year119012155

timestamp和实际时区有关,更能反应实际的日期。

2.4 常见的约束

约束:用于限制表中的数据,保证数据的准确性和可靠性

NOT NULL:非空,该字段的值必填
UNIQUE:唯一,该字段的值不可重复
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空  unique+not null
FOREIGN KEY:外键,该字段的值引用了另外的表的字段  (在从表添加外键约束,用于引用主表中某列的值)

约束的分类

  • 列级约束:六大约束语法上都支持,但外键约束没有效果,一个字段可添加多个列级约束,多个约束用空格隔开
  • 表级约束:除了非空、默认,其他都支持(外键除外)

主键和唯一键的区别

1.区别:

①、一个表至多有一个主键,但可拥有多个唯一键

②、主键不允许为空,唯一键可以为空,但也只能有一个为空

2.相同点:

①、都具有唯一性

②、都支持组合键

外键

要求从表设置外键关系

主表的关联必须是一个key(一般是主键或唯一键)

插入数据时,先插入主表中、在插入从表

删除数据时,先删除从表中的数据、在删除主表中的数据

创建表时添加约束

语法:

CREATE TABLE 表名(

        字段名 字段类型 【NOT NULL || PRIMARY || DEFAULT】

        CONSTAINT 约束名 FORREIGH KEY(字段名) REFERENCES 主表(被引用列)        

 修改表时添加或删除约束

添加非空/默认
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 【not NULL || DEFAULT 值】;
删除非空/默认
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 ;


添加主键/唯一
ALTER TABLE 表名 add【 CONSTRAINT 约束名】 【primary KEY(字段名) || UNIQUE(字段名)】;
删除主键/唯一
ALTER TABLE 表名 DROP 【primary KEY || INDEX 索引名】;


添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;

3 事务控制语言(TCL)

事务:一个或一组sql语句组成的最小执行单元,它们综合在一起才是一个完整的工作单元,这些动作必须全部完成,如果有一个失败的话,那么事务就会回滚到最开始的状态,仿佛什么都没发生过一样。

数据库事务是保证在并发情况下能够正确执行的重要支撑,MySQL常见的数据库引擎中支持事务的是InnoDB。

事务就是一系列操作,正确执行并提交,如果中途出现错误就回滚。事务要保证能够正常的执行,就必须要保持ACID特性。

事务的特征(AICD)

原子性(Atomicity):一个事务不可在被分割,要么都执行、要么都不执行;

一致性(Consistency):一个事务会使数据从一个一致状态切换到另一个一致状态;

隔离性(Isolation):一个事务的执行不受其他事务的干扰

持久性(Durability):一个事务一旦提交,在会永久的改变数据库的数据。

3.1 事务的创建 

隐式事务

事务没有明显的开启和结束的标记

比如:insert、pudate、delete语句 即:执行DML无需手动开启和提交事务

但在MyBatis持久层框架中,进行DML操作时必须要手动开启事务,并且手动提交事务!!因为在MyBatis持久层框架中,它们处理DML语句的时候会自动设置autocommit=0;如果DML中不进行手动提交事务,那么最后事务就会进行回滚。

在写SSM项目的时候,不用手动提交呢?

spring boot 自动提交事务

org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(Object, TransactionDefinition)

显示事务

事务具有明显的开启和结束的标记

前提:必须先设置自动提交为禁用

步骤一:开启事务 set autcommit = 0;(默认值是1,也就是默认自动开启提交)

                              start transation;

步骤二:编写事务中的sql语句(select、insert、update、delete)

语句1;

语句2;

...

步骤三:结束事务 根据情况选择是提交事务还是回滚事务

commit;提交事务

rollback;回滚事务

3.2 为什么需要使用事务?

当同时存在多个事务时,这些事务共同访问数据库中的相同数据时,如果不采取必要的隔离机制,就有极大的可能出现各种并发问题:

脏读:对于两个事务T1、T2,T1读取了已被T2更新但还未提交的字段,之后若T2回滚,T1  读取的内容就是临时且无效的。

不可重复读对于两个事务T1、T2,T1读取了一个字段,然后T2更新了该字段之后,T1再次读取同一个字段,值就不同了。

幻读对于两个事务T1、T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新数据之后,T1再次读取同一个表,就会多出几行来。

不可重复读与幻读的区别:

不可重复读主要体现在update,即事务前后对特定字段的内容的修改;而幻读体现在insrt和delete,即事务前后对整个数据结果集的对比

数据库的隔离性:数据库必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免发生各种并发问题

隔离级别:一个事务与其他事务隔离的程度;隔离级别越高,数据一致性就越好,并发性越弱,但性能相对就越低

3.3 数据库提供的4中事务隔离级别:

隔离级别脏读不可重复读幻读描述

READ UNVCOMMITED

(读未提交数据)

允许事务读取未被其他事务提交的变更

READ COMMITED

(读已提交数据)

×只允许事务读取已经被其他事务提交的变更

REPEATABLE READ

(可重复读)

××确保事务可以从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新

 SERIALIZABLE

(串行化)

×××确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入、删除、更新和操作,可以避免所有的并发问题,但性能十分低下

MySQL支持以上四种事务隔离级别,且将repeatable read设置为默认隔离级别。

Oracle支持两种事务级别read commited,serializable,Oracle默认的事务隔离级别是read commited。

3.4 在MySQL中设置隔离级别

  • 每启动一个mysql程序,就会获得一个单独的数据库连接,每个数据库连接都会有一个全局变量@@tx_isolation,表示当前的事务隔离级别。
  • 查看当前的隔离级别:SELECT @@tx_isolation;
  • 设置当前mysql连接的隔离级别: SET TRANSACTION ISOLATION LEVEL 隔离级别名称;

 4 存储过程

4.1 什么是存储过程

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时,需要编写一组复杂的SQL语句才能实现的时候,那么我们就可以将这组复杂的SQL语句集提前编写在数据库中,由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程:(PROCEDURE)是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是很有好处的。

也就是数据库SQL语言层面的代码封装与重用。

好处:1.提高代码的重用性;

           2.简化操作;

           3.减少编译的次数和连接数据库服务器的连接次数,提高了效率。

4.2 基本使用

创建语法:

CREATE PROCEDURE 存储过程名(参数列表)

BEGIN

        存储过程体

END 结束标记

参数列表

参数列表包含三个部分:参数模式 参数名 参数类型

如:in stuname varchar(20)

参数模式:

in :该参数需要调用时需传入值

out:该参数可以作为返回值

inout:该参数既需要传入值,又可以返回值

存储过程体只有一句话时,begin end可以省略,每条sql语句的结尾必须加分号。

设置存储过程的结尾

语法:delimiter 结束标记

如: delimiter $; 此时'$'就为当前存储过程的结束标记,也就是说 '$' 替代了 ';' 的功能

调用语法:CALL 存储过程名(实参列表) 结束标记

删除存储过程:DROP PROCEDURE 存储过程名 结束标记

                          不可一条语句删除多个存储过程,一次只能删除一个 

查看存储过程信息:SHOW CREATE PROCEDURE 存储过程名;

4.3 为何要使用存储过程

  1. 简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。

  2. 通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后,就存储在数据库中,可重复使用,提高代码重用率。

  3. 存储过程有助于减少应用程序和数据库服务器之间的流量。 因为应运程序不必发送多个冗长的SQL语句,只用发送存储过程中的名称和参数即可。

  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值