库表管理
创建: create
修改: alter
删除: drop
1.库管理
1.创建:
#create database [if not exists]库名;
CREATE DATABASE IF NOT EXISTS books ;
2.修改:
RENAME DATABASE books TO 新库名; #更改库名
ALTER DATABASE books CHARACTER SET gbk; #更改库的字符集
3.删除:
DROP DATABASE IF EXISTS books;
2.表管理
1.创建:
create table 表名(
列名 列的类型【(长度) 约束】,
…
列名 列的类型【(长度) 约束】
)
#案例:创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
DESC author; #查询表结构(列+类型+约束)
2.1数据类型
1.数值型
整型:默认是有符号,无符号徐添加unsigned关键字;插入的数值超范围,则返回边界值;
小数:
M为整数部分+小数部分的总位数 D为小数部分的位数
浮点型:float(M,D) double(M,D) 默认是根据插入数值的精度来决定精度
定点型:decimal(M,D) 其中M默认为10,D默认为0 PS该类型小数精度较高
字符型:
char(M) 其中M为最大字符数,默认为1。一旦指定空间就固定为M
varchar(M) 其中M为最大字符数,不可以省略。根据插入字符长度动态调整
日期型:
data保存日期;time保存时间;year保存年;
datatime以及timestamp是保存日期+时间,那有什么不同呢?
timestamp受时区影像:SHOW VARIABLES LIKE 'time_zone';SET time_zone='+9:00';
2.2约束
NOT NULL:非空,用于保证该字段的值不能为空,比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认值,比如性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空,如学号、员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空,比如座位号
CHECK:检查约束【mysql中不支持】比如年龄、性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中某列的值,比如学生表的专业编号
约束名 | 保证唯一性 | 允许内容为空 | 一个表可以添加多个 |
---|---|---|---|
PRIMARY KEY | Yes | No | 至多一个 |
UNIQUE | Yes | Yes | 可以有多个 |
外键:要求在从表设置外键关系;主表的关联列一般是主键或唯一;插入数据,先主后从,删除数据,先从后主。
列级约束:六大约束语法上都支持,但外键约束没有效果
表级约束:除了非空、默认,其他的都支持
SHOW INDEX FROM stuinfo; #查看约束
创建表时:
CREATE TABLE stuinfo( #列级约束的方式
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
)
CREATE TABLE stuinfo( #表级约束的方式
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
修改表时:
alter table 表名 modify column 字段名 字段类型 新约束; #添加列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】; #添加表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
删除约束:
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
2.3标识列
即自增长列,可以不用手动的插入值,系统提供默认的序列值
约束 AUTO_INCREMENT
特点:
标识列的类型只能是数值型,不一定非得和主键搭配,但通常是;一个表中至多一个标识列;设置步长:SET auto_increment_increment=3;起始值可以在插入数据时指定,默认为1
CREATE TABLE tab_identity(
id INT ,
NAME FLOAT UNIQUE AUTO_INCREMENT,
seat INT
)
2.修改语法:alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
④删除列
ALTER TABLE book_author DROP COLUMN annual;
⑤修改表名
ALTER TABLE author RENAME TO book_author;
3.删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES; #查该库中表单名
4.复制
仅复制表结构:
CREATE TABLE copy LIKE author;
仅复制某些结构字段:
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
复制表的结构+数据:
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
3.视图
含义:虚拟表,和普通表一样使用
创建关键字 | 是否实际占用物理空间 | 使用 | |
---|---|---|---|
视图 | create view | 只是保存了sql逻辑 | 查,一般不能增删改 |
表 | create table | 保存了数据 | 增删改查 |
PS:包含以下关键字的sql语句构建的视图不可以增删改更新:分组函数、distinct、group by、having、union或者union all
1.创建视图:
create view 视图名
as
查询语句;
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
2.视图的修改
方式一:create or replace view 视图名 as 查询语句;
方式二:alter view 视图名 as 查询语句;
3.删除视图:drop view 视图名,视图名,…;
4.查看:DESC myv3;或者SHOW CREATE VIEW myv3;
事务
特性:ACID
原子性Atomic:一个事务不可再分割,要么都执行要么都不执行
一致性Consistency:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性Isolation:一个事务的执行不受其他事务的干扰
持久性Durability:一个事务一旦提交,则会永久的改变数据库的数据.
显示事务:
步骤1:开启事务
set autocommit=0; #禁用自动提交功能
start transaction;可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…
步骤3:结束事务
commit;提交事务 或 rollback;回滚事务 PS:savepoint 节点名;设置保存点 可以回滚至指定节点
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点 要不回滚该事务操作前的状态
隔离级别:
允许类型 | 脏读 | 不可重复读 | 幻读 | 数据库默认 | |
---|---|---|---|---|---|
read uncommitted | 允许事务读取未被其它事务提交的变更 | √ | √ | √ | |
read committed | 只允许事务读取已经被其它事务提交的变更 | × | √ | √ | Oracle |
repeatable read | 确保事务可以多次从一个字段中读取相同的值 | × | × | √ | MySQL |
serializable | 确保事务可以从一个表中读取相同的行 | × | × | × |
PS:对于A,B两事务重叠,访问数据库中相同的数据。A在更新数据data,B要获取数据data。
脏读:A还没提交,B读取了A未提交的字段变更。若A回滚,则B读取了临时无效的内容
不可重复读:B只读取已提交的字段,若A提交该字段变更,B再次读取同一个字段,值不一样
幻读:B从表中读取了一个字段,行插入变更,B再读同一个表,发现多了几行
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
存储过程+函数
1.变量
1.1系统变量
系统变量 | 关键字 | 作用域 |
---|---|---|
全局变量 | global | 所有会话(连接)有效,但不能跨重启 |
会话变量 | session 默认不写 | 当前会话(连接)有效 |
1.查看系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like ‘%char%’;
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一:set global|【session】系统变量名=值;
方式二:set @@global|【session】系统变量名=值;
1.2自定义变量
作用域 | 定义位置 | 语法 | 声明并初始化 | |
---|---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 加@符号,不用指定类型 | SET @变量名=值 |
局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 | DECLARE 变量名 类型 【DEFAULT 值】; |
①声明并初始化
②赋值(更新变量的值):SET 自定义变量=值;或SELECT 字段 INTO 自定义变量 FROM 表;
③查看:SELECT 自定义变量;
2.存储过程
一组预先编译好的SQL语句的集合,理解成批处理语句
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
参数列表中参数包含三部分:参数模式 参数名 参数类型
参数模式:in(输入)、out(输出)、inout(输入输出)
PS:存储过程体仅仅只有一句话,begin end可以省略;存储过程体中的每条sql语句的结尾要求必须加分号;存储过程的结尾可以使用 delimiter 重新设置
#根据输入的女神名,返回对应的男神名和魅力值 注cmd中好使
DELIMITER $
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$
#查看存储过程的信息
SHOW CREATE PROCEDURE myp7;
#删除存储过程
DROP PROCEDURE myp7;
3.函数
一组预先编译好的SQL语句的集合。
PS函数有且仅有1 个返回,适合做处理数据后返回一个结果。而过程存储可以有0个或多个返回,适合做批量插入、批量更新
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
函数的参数列表中的参数只有(参数名 参数类型)这两部分;函数体肯定会有return语句
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
#调用
SELECT myf3('IT')$
#查看函数
SHOW CREATE FUNCTION myf3;
#删除函数
DROP FUNCTION myf3;
流程控制
1.分支结构
1.if函数: if else 的效果
SELECT IF(10<5,‘大’,‘小’); #if(条件,值1,值2)
2.1 case函数的使用一: switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
/*案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salarEND AS 新工资
FROM employees;
2.2 case 函数的使用二:类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end
/*案例:查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
3.if结构:功能类似于多重if PS只能应用在begin end 中
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
#创建存储过程,如果工资<2000,则删除,如果5000>工资>2000,则涨工资1000,否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
END IF;
END $
CALL test_if_pro(2100)$
2.循环结构
iterate类似于 continue,继续,结束本次循环,继续下一次
leave 类似于 break,跳出,结束当前所在的循环
1.while循环:
【标签:】while 循环条件 do
循环体;
end while【标签】;
#案例:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`)
VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)$