十一,DML语言
1,插入语法:insert into 表名(字段名,...) values(值1,...)
2,修改语法:update 表名 set 字段=新值,字段=新值
【where 条件】
修改多表语法:
update 表1 别名1,表2 别名2
set 字段=新值,字段=新值
where 连接条件
and 筛选条件
3.1,delete语句
单表的删除: ★
delete from 表名 【where 筛选条件】多表的删除:
delete 别名1,别名2
from 表1 别名1,表2 别名2
where 连接条件
and 筛选条件;
3.2,truncate语句: truncate table 表名
十二, DDL语句
1,库操作
一、创建库:create database 库名
二、删除库:drop database 库名
三、修改库:rename database 旧库名 to 新库名
四、更改库的字符集:alter database 库名 character set gbk;
2,表操作
2.1,创建表
CREATE TABLE IF NOT EXISTS stuinfo(
stuId INT,
stuName VARCHAR(20),
gender CHAR,
bornDate DATETIME
);2.2,修改表 alter
ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名【字段类型】;
2.3,修改字段名
ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;2.4,添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;2.5,删除字段
ALTER TABLE studentinfo DROP COLUMN email;2.6,删除表
DROP TABLE [IF EXISTS] studentinfo;2.7,修改字段属性
ALTER TABLE [表名] MODIFY COLUMN [列名] [数据类型]
2.8,表的复制
十三,常见类型
1,整型:tinyint(8bit)、smallint(16bit)、mediumint(24bit)、int(32bit)、bigint(64bit)
2,小数:
浮点型:float(M,D)(单精度4个字节)、double(M,D)(双精度8个字节)
定点型:decimal(M,D),DEC(M,D)(M=2个字节)
3,字符型:短文本:char(固定长度,费空间,效率高)、varchar(变长度,省空间,效率低)
长文本:text、blob(较长的二进制数据)
4,日期型:year(1字节,最小值1901,2155)
date(4字节,最小值1000-01-01,最大值9999-12-31)
datetime(8字节,最小值1000-01-01 00:00:00,最大值9999-12-31 23:59:59)
timestamp(4字节,最小值19700101 080001,最大值2038)
time(3字节,最小值-838:59:59,最大值838:59:59)
//5代表数据长度不超过5,2表示有两个小数位
5,枚举
6,set
十四,常见约束
NOT NULL
DEFAULT
UNIQUE(智能数值型)
CHECK
PRIMARY KEY
FOREIGN KEY :magorId int foreign key beferences major(id)
十五,数据库事务
1,含义:通过一组逻辑操作单元(一组DML——sql语句),将数据从一种状态切换到
另外一种状态
2,特点:
原子性:要么都执行,要么都回滚
一致性:保证数据的状态操作前和操作后保持一致
隔离性:多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰
持久性:一个事务一旦提交,则数据将持久化到本地,除非其他事务对其进行修改
3,相关步骤
1、开启事务
2、编写事务的一组逻辑操作单元(多条sql语句)
3、提交事务或回滚事务
4,分类
a,隐式事务,没有明显的开启和结束事务的标志
比如
insert、update、delete语句本身就是一个事务
b,显式事务,具有明显的开启和结束事务的标志1、开启事务
取消自动提交事务的功能
2、编写事务的一组逻辑操作单元(多条sql语句)
insert
update
delete
3、提交事务或回滚事务
5,使用到的关键字
set autocommit=0;
start transaction;
commit;
rollback;
savepoint 断点
commit to 断点
rollback to 断点
6, 事务的隔离级别
6.1,事务并发问题如何发生?
当多个事务同时操作同一个数据库的相同数据时
6.2,事务的并发问题有哪些?脏读:一个事务读取到了另外一个事务未提交的数据
不可重复读:同一个事务中,多次读取到的数据不一致
幻读:一个事务读取数据时,另外一个事务进行更新,导致第一个事务读取到了没有更新的数据
6.3,如何避免事务的并发问题?通过设置事务的隔离级别
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免脏读
3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE可以避免脏读、不可重复读和幻读
6.4,设置隔离级别:set session|global transaction isolation level 隔离级别名;
6.5,查看隔离级别:select @@tx_isolation;
十六,视图
1,含义:理解成一张虚拟的表
2,视图和表的区别:
使用方式 占用物理空间
视图 完全相同 不占用,仅仅保存的是sql逻辑
表 完全相同 占用
3,好处
1、sql语句提高重用性,效率高
2、和表实现了分离,提高了安全性
4,创建
语法:CREATE VIEW 视图名 AS 查询语句;
5,视图的增删改查
1、查看视图的数据 ★
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';
2、插入视图的数据
INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);
3、修改视图的数据
UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';
4、删除视图的数据
DELETE FROM my_v4;
6,某些视图不能更新
6.1,包含以下关键字的sql语句:分组函数、distinct、group by、having、union
或者union all
6.2,常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
7,视图逻辑的更新
方式一:
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;#方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;
SELECT * FROM test_v7;
8,删除:DROP VIEW test_v1,test_v2,test_v3;
9,视图结构的查看
DESC test_v7;
SHOW CREATE VIEW test_v7;
十七,存储
1,存储过程
含义:一组经过预先编译的sql语句的集合
好处:1、提高了sql语句的重用性,减少了开发程序员的压力
2、提高了效率
3、减少了传输次数分类:
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个
2,创建存储过程
语法:
create procedure 存储过程名(in|out|inout 参数名 参数类型,...)
begin
存储过程体end
类似于方法:
修饰符 返回类型 方法名(参数类型 参数名,...){
方法体;
}注意:
1、需要设置新的结束标记
delimiter 新的结束标记
示例:
delimiter $CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
sql语句1;
sql语句2;END $
2、存储过程体中可以有多条sql语句,如果仅仅一条sql语句,则可以省略begin end
3、参数前面的符号的意思
in:该参数只能作为输入 (该参数不能做返回值)
out:该参数只能作为输出(该参数只能做返回值)
inout:既能做输入又能做输出
3,调用存储过程:call 存储过程名(实参列表)
4,删除:drop procedure p1,p2;
5,查看信息:desc myp3; show create procedure myp3;
十八,函数
1,创建函数
学过的函数:LENGTH、SUBSTR、CONCAT等
语法:CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体
END
2,调用函数:SELECT 函数名(实参列表)
3,函数和存储过程的区别
关键字 | 调用语法 | 返回值 | 应用场景 | |
函数 | FUNCTION | SELECT 函数() | 只能是一个 | 一般用于查询结果为一个值并返回时,当有返回值而且仅仅一个 |
存储过程 | PROCEDURE | CALL 存储过程() | 可以有0个或多个 | 一般用于更新 |
4,查看函数:show create function myf3;
5,删除:drop function myf3;
十九,流程控制结构
1,系统变量
作用域:针对于所有会话(连接)有效,但不能跨重启
查看所有全局变量
SHOW GLOBAL VARIABLES;
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
查看指定的系统变量的值
SELECT @@global.autocommit;
为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
2,会话变量
作用域:针对于当前会话(连接)有效
查看所有会话变量
SHOW SESSION VARIABLES;
查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
3,自定义变量
一、用户变量
声明并初始化:
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
赋值:方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;使用:
select @变量名;
二、局部变量
声明:
declare 变量名 类型 【default 值】;
赋值:方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;使用:
select 变量名
4,二者的区别
作用域 | 定义位置 | 语法 | |
用户变量 | 当前会话 | 会话的任何地方 | 加@符号,不用指定类型 |
局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |
5,分支
一、if函数
语法:if(条件,值1,值2)
特点:可以用在任何位置二、case语句
语法:
情况一:类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句,需要加分号)
when 值2 then 结果2或语句2(如果是语句,需要加分号)
...
else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)
情况二:类似于多重if
case
when 条件1 then 结果1或语句1(如果是语句,需要加分号)
when 条件2 then 结果2或语句2(如果是语句,需要加分号)
...
else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)
特点:
可以用在任何位置三、if elseif语句
语法:
if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;
特点:
只能用在begin end中!!!!!!!!!!!!!!!
6,三者比较:
应用场合 | |
if 函数 | 简单双分支 |
case 结构 | 等值判断 的多分支 |
if 结构 | 区间判断 的多分支 |
7,循环
语法:
【标签:】WHILE 循环条件 DO
循环体
END WHILE 【标签】;
特点:while:先判断后执行
repeat:先执行后判断
loop:没有条件的死循环
只能放在BEGIN END里面
如果要搭配leave跳转语句,需要使用标签,否则可以不用标签
leave类似于java中的break语句,跳出所在循环!!!