mysql基础——数据类型、约束、事务、视图

最新推荐文章于 2022-09-16 14:37:51 发布

皮卡qiu~

最新推荐文章于 2022-09-16 14:37:51 发布

阅读量238

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Sruggle/article/details/105120921

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、常见的数据类型

1.1 整型

-- 一、整型
/*
  分类         字节
1.tinyint        1
2.smallint       2
3.mediumint      3
4.int/integer    4
5.bigint         8

特点:
1.符号类型默认为有符号，设置无符号需添加unsigned关键字
2.插入的数值超过了整型的范围，有的版本会显示临界值
3.不设置长度会有默认的长度，代表你显示结果的样子
  比如t1 INT(7) ZEROFILL，添加123，结果为0000123，
  一旦被ZEROFILL修饰，就表示无符号类型
4.关于数据类型的选择，数据类型越简单越好
*/
-- 1.如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);

DESC tab_int;

INSERT INTO tab_int VALUES(-1,0);

SELECT * FROM tab_int;

1.2 小数

-- 小数
/*
1.分类
  1.浮点型
	float(M,D)
	double(M,D)
  2.定点型
	dec(M,D)
	decimal(M,D)
2.特点
  1.M:整数部位+小数部位总共的长度
  2.D:小数部位的长度
  3.若M,D均省略，则浮点型无影响，定点型M为10，D为0
  4.定点型的精确度较高
*/

1.3 字符型

-- 三.字符型
/*
1.较短的文本
	char
	varchar
	binary、varbinary,保存二进制文件
	enum,保存枚举
	set,保存集合
2.较长的文本
	text
	blob(较大的二进制)
特点

类型	 写法        M的意思                      特点         效率
char     char(M)     最大的字符数,可省略      固定长度的字符    高
varchar  varchar(M)  最大的字符数,不可省略    可变长度的字符    低

*/
SELECT * FROM tab_char;
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');-- 超过了列表值就不能插入
INSERT INTO tab_char VALUES('A');

SELECT * FROM tab_set;
CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('a,c,d');

1.4 日期型

-- 四、日期型
/*
1.分类
   1.date       只保存日期
   2.time       只保存时间
   3.year       只保存年
   4.datetime   保存日期+时间
   5.timestamp  保存日期+时间
2.特点
     类型        字节     范围     时区等影响
   1.datetime     8     1000-9999    不受
   2.timestamp    4     1970-2038     受
   
*/
SELECT * FROM tab_date;
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());

二、常见约束

2.1 基本介绍

-- 常见约束
/*
1.含义
    一种限制，用于限制表中的数据，保证数据的准确可靠
    
2.分类
    1.NOT NULL非空约束
        用于保证该字段的值不能为空，比如学号
    2.default默认
        用于保证该字段有默认值，比如性别
    3.PRIMARY KEY主键约束
        用于保证该字段的值具有唯一性,并且非空,比如学号、员工编号
    4.UNIQUE:唯一约束
        用于保证该字段的值具有唯一性，可以为空,比如座位号
    5.CHECK:检查约束[mysql不支持]，比如限制年龄、性别
    6.FOREIGN KEY:外键约束
      用于限制两个表的关系，保证该字段的值必须来自于主表关联的值，
      从表中添加外键约束，引用主表中某列的值,比如专业编号，部门编号

3.添加约束的时机
    1.创建表时
    2.修改表时

4.约束的分类
    1.列级约束
	六大约束语法上都支持，但外键约束没有效果
    2.表级约束
	除了非空和默认，其它都支持

5.主键和唯一的区别
             唯一性     可以为空    一个表中有多少个   是否允许组合
    主键	是          否            至多1个           是
    唯一	是          是            可以多个	    是

6.外键的特点
    1.要求在从表设置外键关系
    2.从表的外键列的类型和主表的关联列类型一致或兼容
    3.主表中的关联列必须是一个key(一般是主键或唯一)
    4.插入数据时，先插入主表，再插入从表，删除则反之。
      比如多个学生对应一个专业，那么专业表是主表
      
*/

2.2 列级约束(创建表)

-- 1.添加列级约束
/*
1.语法
  直接在字段名和类型后面追加 约束类型即可
*/
CREATE DATABASE students;
USE students;
-- 学生表
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,-- 主键
	stuName VARCHAR(20) NOT NULL,-- 非空
	gender CHAR(1) CHECK(gender = '男' OR gender = '女'),-- 检查
	seat INT UNIQUE,-- 唯一
	age INT DEFAULT 18,-- 默认
	majorId INT REFERENCES major(id) -- 外键
);
-- 主键表
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

DESC stuinfo;
SHOW INDEX FROM stuinfo;

2.3 表级约束(创建表)

-- 2.添加表级约束
/*
1.语法:各个字段的最下方
     [constraint 约束名] 约束类型(字段名)
2.特点
  1.括号内的可省略  
  2.主键的默认名称一直为PRIMARY,不可修改   
*/
DROP TABLE IF EXISTS stuinfo;
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)
);
SHOW INDEX FROM stuinfo;

-- 3.通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_skuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

2.4 添加约束(修改表)

-- 二、修改表时添加约束
/*
1.添加列级约束
    alter table 表名 modify column 字段名 字段类型 新约束;
2.添加表级约束
    alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,-- 主键
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorId INT
);
DESC stuinfo;

-- 1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

-- 2.添加默认约束
ALTER TABLE stuinfo MODIFY age INT DEFAULT 18;

-- 3.添加主键(两种方式)
ALTER TABLE stuinfo MODIFY id INT PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

-- 4.添加唯一键
ALTER TABLE stuinfo MODIFY seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat);

-- 5.添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

2.5 删除约束(修改表)

-- 二、修改表时删除约束
/*
注意事项
    1.删除外键加的是约束名称，就算不加也有默认的约束名称
      需要SHOW CREATE TABLE stuinfo来查看约束名称
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,-- 主键
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorId INT
);
DESC stuinfo;


-- 1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

-- 2.删除默认约束
ALTER TABLE stuinfo MODIFY age INT;

-- 3.删除主键(两种方式)
ALTER TABLE stuinfo DROP PRIMARY KEY;

-- 4.删除唯一键
ALTER TABLE stuinfo DROP INDEX seat;

-- 5.添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorId) REFERENCES major(id);

-- 5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY stuinfo_ibfk_1;

三、标识列

3.1 基本介绍

-- 标识列(自增长列)
/*
含义
   1.可以不用手动的插入值，系统提供默认的序列值
特点
   1.和标识列搭配的必须是一个key，比如主键，唯一键，外键
   2.一个表至多一个自增长列
   3.标识列的类型只能是数值型
   4.标识列可以设置步长，也可以通过手动插入值，设置起始值
*/

3.2 设置、修改、删除标识列

-- 一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
SELECT * FROM tab_identity;

CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20),
	seat INT
);

INSERT INTO tab_identity VALUES(NULL,'john');
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'lucy'); 
INSERT INTO tab_identity(NAME) VALUES('jack');

-- 显示和设置自增长值
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment = 3;

-- 从10开始，每次添加id自增3
INSERT INTO tab_identity VALUES(10,'john');
INSERT INTO tab_identity VALUES(NULL,'john');

-- 二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

-- 三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

四、事务

4.1 基本介绍

-- TCL(Transaction Control language)
/*
事务
   1.一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，
     要么全部不执行。
分类
   1.隐式事务
       1.事务本身没有明显的开启和结束的标记,如insert、update、delete语句    
   2.显式事务
       1.事务具有明显的开启和结束标记,必须先设置自动提交功能为禁用
         set autocommit
       2.步骤
         1)开启事务
           set autocommit = 0;
           start transaction;(可选)
         2)编写事务中的sql语句(select insert update delete)
           语句1
           ...
         3)结束事务
           commit;提交事务
           roolback;回滚事务
           
特点(ACID)
   1.原子性，不可分割
   2.一致性，联系两人转账理解
   3.隔离性，事务之间互不影响
   4.持久性，一旦提交，永久改变
   
回滚点
   1.savepoint 节点名
     设置保存点

不采取隔离机制引发的问题
   1.脏读:T2更新字段，T1读取字段，但T2未提交，若T2回滚，那么T1内容无效   
   2.不可重复读:T1读取字段，T2更新字段，T1再次读取字段值不同          
   3.幻读:T1读取表中字段，T2在表中插入新行，T1再次读取该字段会多出几行
  
事物的隔离级别
   1.read uncommited:出现脏读、幻读、不可重复读
   2.read committed:解决脏读
   3.repeatable read:解决脏读、不可重复读
   4.serializable:解决脏读、不可重复读、幻读
比较   
   mysql中默认repeatable 
   oracle中默认read committed
命令
   1.查看隔离级别 select @@tx_isolation;
   2.设置隔离级别 set session|global transaction isolation level 隔离级别;
   
*/

4.2 事务演示的步骤

-- 演示事务的步骤

-- 1.开启事务
SET autocommit = 0;
START TRANSACTION;

-- 2.编写一组事务的sql语句
UPDATE account SET balance = 500 WHERE username = '张无忌';
UPDATE account SET balance = 1500 WHERE username = '赵敏';

-- 3.结束事务
COMMIT;

4.3 delete和truncate在事务中的区别

-- 2.delete和truncate在事务使用时的区别

-- 演示delete(可回滚到删除之前)
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

-- 演示truncate(无法回滚到删除之前)
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

4.4 回滚点的设置

-- 3.演示savepoint的使用
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a;-- 设置保存点
DELETE FROM account WHERE id = 28;
ROLLBACK TO a;-- 回滚到保存点

SELECT * FROM account;

五、视图

5.1 基本介绍

-- 视图
/*
含义
   1.虚拟表，和普通版一样使用，mysql5.1版本的新特性，
     通过表动态生成的数据
优势
   1.重用了sql语句
   2.简化了复杂的sql操作
   3.保护数据，提高安全性
*/

5.2 视图的创建

-- 创建视图
/*
语法
   1.create view 视图名
     as
     查询语句
*/

-- 1.查询姓名中包含a字符的员工名、部门名和工种信息
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.查询各部门的平均工资级别
/*
分析
   1.创建视图查看每个部门的平均工资
*/
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

SELECT myv2.`department_id`,myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

-- 3.查询平均工资最低的部门id
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

-- 4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*,m.ag
FROM departments d
JOIN myv3 m
ON m.`department_id` = d.`department_id`;

5.3 视图的修改

-- 二、视图的修改
/*
方式1(类似于重新创建)
   create or replace view 视图名
   as
   查询语句;
方式2
   alter view 视图名
   as
   查询语句;
*/
SELECT * FROM myv3;

-- 方式1
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

-- 方式2
ALTER VIEW myv3
AS
SELECT * FROM employees;

-- 三、删除视图
/*
语法:drop view 视图名，视图名,...;
*/
DROP VIEW myv1,myv2,myv3;

5.4 视图的删除

-- 三、删除视图
/*
语法:drop view 视图名，视图名,...;
*/
DROP VIEW myv1,myv2,myv3;

5.5 视图的查看

-- 四、查看视图
DESC myv3;

SHOW CREATE VIEW myv3;

5.6 视图的更新

-- 五、视图的更新
/*
无法更新的视图
   1.视图中包含关键字:distinct、group by、having、union、union all
   2.常量视图
   3.select中含有子查询 
   4.join
   5.from一个不能更新的视图
   6.where子句的子查询引用了from子句中的表
*/
CREATE OR REPLACE VIEW myv1
AS
SELECT 
    last_name,
    email
FROM 
    employees;

SELECT * FROM myv1;

-- 1.插入(更新视图的同时，原始表也更新了)
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

-- 2.修改(同1)
UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞';

-- 3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';

皮卡qiu~

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql基础——数据类型、约束、事务、视图

一、常见的数据类型1.1 整型-- 一、整型/* 分类字节1.tinyint 12.smallint 23.mediumint 34.int/integer 45.bigint 8特点:1.符号类型默认为有符号，设置无符号需添加unsigned关键字2.插入的数值超过了整型的范围，有的版...
复制链接

扫一扫