爱站程序员基地Mysql知识点总结（进阶2）
前言：笔记所需数据库在我的博客资源里面
#数据类型
#常见的数据类型
/*
数字型：
整数
小数：
定点数
浮点数
字符型：
较短文本：char,varchar
较长文本：text,blob(较长的二进制数据类型)
日期型：
*/
#测试M和D
#M:整数部位+小数部位
#D:小数部位
#M和D都可以省略
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2)
);
SELECT * FROM tab_float;
INSERT INTO tab_float
VALUES(123.45,123.45,123.45);
/*
原则：
所选择的类型越简单越好
*/
#字符型
/*
char:固定长度字符（比较耗费空间）（效率低）
varchar:可变长度字符（耗费空间比较小）（效率高）
enum：枚举型
*/
CREATE TABLE tab_char(
e1 ENUM(‘a’,‘b’,‘c’)
);
INSERT INTO tab_char VALUE(‘a’);
INSERT INTO tab_char VALUE(‘b’);
INSERT INTO tab_char VALUE(‘c’);
INSERT INTO tab_char VALUE(‘D’);
INSERT INTO tab_char VALUE(‘C’);
SELECT * FROM tab_char;
#四、日期
/*
分类：
date 只保存日期
time 只保存时间
year 只保存年份
datetime：保留日期加时间
timetamp：保留日期加时间
特点：
timetamp更加准确
datetime：范围更大
*/
#常见约束
#常见约束
/*
含义:
一种限制，用于限制表中的数据为了保证表中的数据准确和可靠性
分类：六大约束
not null 非空约束（姓名，学号等）
default 默认约束
primary 主键，用于保证该字段的值有唯一性并且非空
unique 唯一，用于保证字段值的唯一性，可以为空（座位号）
check 检查约束（mysql中不支持）
foreign key 外键，用于限制俩个表的关系，用于保证该字段的值必须
保证来自于主表的关联列的值（在从表添加外键约束，用于引用主表
中某列的值 比如专业编号和工种编号）
添加约束的时机：
1、创建表时
2、修改表时
约束的添加分类：
1、列级约束
六大约束语法上够可以用
但是外键约束没效果
2、表级约束
除了非空、默认，其他的都支持
*/
CREATE TABLE 表名
（
字段名 字段类型 约束,
字段名 字段类型,
表级约束
）
#创建表时添加约束
#1.添加列级约束
CREATE DATABASE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,
gender ENUM(‘男’,‘女’),
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
);
DESC stuinfo;
CREATE TABLE major(
id INT PRIMARY KEY,
majorname VARCHAR(20)
);
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,
gender ENUM(‘男’,‘女’),
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
);
#主键和唯一键的大对比：
/*
主键和唯一键的大对比：
主键： 可以确保唯一性 不可以为空 允许组合
唯一键：可以确保唯一性 可以为空 允许组合
*/
#外键
/*
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时先插入主表，再插入从表
删除数据时，先删除从表在删除主表
*/
#修改表时添加约束
#1、添加非空约束
CREATE TABLE stuinfo(
id INT ,#主键
stuName VARCHAR(20),
gender CHAR(1),
seat INT ,#唯一
age INT ,#默认约束
majorId INT #外键
);
DROP TABLE stuinfo;
#加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
#加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
DESC stuinfo;
#删除约束
#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;
DESC stuinfo;
ALTER TABLE dept1 MODIFY COLUMN id INT (4) PRIMARY KEY;
CREATE TABLE dept2 MODIFY COLUMN dept_id INT REFERENCES dept1(id);
ALTER TABLE dept2 ADD COLUMN dept_id INT;
ALTER TABLE dept2 MODIFY COLUMN dept_id INT FOREIGN KEY(dept_id)
REFERENCES dept1(id);
CREATE DATABASE play;
DROP DATABASE play;
#事务控制
#TCL
/*
transaction control language 事务控制语言
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行要么全部都不执行
案例：
转账：
张三丰：1000
郭襄：1000
update 表 set 张三丰的余额=500 where name=‘张三丰’；
updata 表 set 郭襄的余额=1500 where name =‘郭襄’；
*/
#查看存储引擎
SHOW ENGINES;
#事务的ACID属性
/*
1、原子性（不可分割）
2、一致性（从一个一致状态变成另一个一致状态）
3、隔离性（一个事务的执行不受其他事务的影响）
4、永久性（比如：删除了就再删除了 ）
/
#事务的创建
/
隐式事务： 事务没有明显的开启和结束标记
比如：insert update delete 语句。
显示事务： 事务具有明显的开启和关闭标记
前提：
必须设置好自动提交功能为禁用（set autocommit=0;）
步骤一、
set autocommit=0;
start transaction;可选的
步骤二（编写事务中sql语句）（select insert update delete）
语句1；
语句2；
步骤三、
commit；提交事务
rollback；回滚事务
*/
#查看变量的语法
SHOW VARIABLES LIKE ‘autocommit’;
#————————————操作—————————-
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance)
VALUE(‘张无忌’,1000),(‘赵敏’,1000);
SELECT * FROM account;
SET autocommit=0;
START TRANSACTION;
UPDATE account SET balance=1000 WHERE username=‘张无忌’;
UPDATE account SET balance=1000 WHERE username=‘赵敏’;
ROLLBACK;
COMMIT;
#结束事务
#视图
#一、创建视图
/*
create view 视图名
as
查询语句；
*/
#如下的例子，以后每次用它都要写一遍很烦
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON
e.
department_id
=d.
department_id
WHERE e.
last_name
LIKE ‘%e%’;
#新的法子：视图
CREATE VIEW ev
AS
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON
e.
department_id
=d.
department_id
WHERE e.
last_name
LIKE ‘%e%’;
SELECT * FROM ev
WHERE last_name LIKE ‘%a%’;
视图的修改
#视图的修改
#方式一、
/*
create or replace view 视图名
as
查询语句；
*/
CREATE OR REPLACE VIEW ev2
AS
SELECT last_name
FROM employees
WHERE last_name LIKE ‘%a%’;
#方式二、
/*
语法：
alter view 视图名
as
查询语句；
*/
ALTER VIEW ev2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM ev2;
#删除视图
/*
语法：drop view 视图名，视图名，视图名。。。。；
*/
DROP VIEW ev,ev2;
#查看视图
DESC ev2;
SHOW CREATE VIEW ev2;
#视图的更新
#建立一个视图
CREATE OR REPLACE VIEW ev2
AS
SELECT * FROM acount;
SELECT * FROM ev2;
#插入
ALTER TABLE acount MODIFY COLUMN userID INT(20) PRIMARY KEY;
DESC acount;
INSERT INTO ev2 VALUE (122354,55541);
#修改
UPDATE ev2 SET PASSWORD=159753 WHERE userID=12354;
#删除
DELETE FROM ev2 WHERE userID=12354;
#具备以下特点的视图是不允许更新的（一般不更新）
/*
1、包含以下关键字的sql语句：分组函数，distinct、group by、having、union、union all
2、
*/
INSERT INTO ev2 VALUE(235556,123456);
SELECT * FROM ev2;
SELECT * FROM acount;
SELECT * FROM acount GROUP BY PASSWORD=123456;
CREATE OR REPLACE VIEW ev2
AS
SELECT * FROM acount
GROUP BY PASSWORD=123456;
#更新不了
#变量
/*
系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量
*/
#一、系统变量
/*
说明：变量由系统提供的，不是用户定义，属于服务器后面
注意：
如果是全局变量，需要加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.系统变量名=值；
*/
#全局变量
/*
作用域：服务器每次启动将为所有的全局变量赋值初始值，针对所有的会话连接有效
，但不能跨重启；
*/
#查看所有的全局变量
SHOW GLOBAL VARIABLES;
#查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE ‘%char%’;
#为某个指定的全局变量赋值；
SET GLOBAL @@global.autocommit=0;
#自定义变量
#一、全局变量
#声明并初始化
SET @用户变量名=值；或
SET @用户变量名:=值；或
SELECT @用户变量名:=值；
#赋值（更新变量的值）
SET @ 用户变量名=值；或
SET @用户变量名:=值；或
SELECT @用户变量名:=值；
#查看某个用户变量的值（使用）
SELECT @用户变量名；
#案例：
SET @name=‘john’;
SET @name=100;
SET @count=1;
SELECT COUNT(*) FROM employees
INTO @count;
SELECT @count;
#二、局部变量
#注意：只能用于begin和end之间
#声明并初始化
DECLARE 局部变量名 类型；或
DECLARE 局部变量名 类型；或
SELECT @局部变量名 类型；
#赋值（更新变量的值）
SET 局部变量名=值；或
SET 局部变量名:=值；或
SELECT @局部变量名:=值；
#查看某个用户变量的值（使用）
SELECT 局部变量名；
