MySQL基础篇-DML及DDL

little_Punk

于 2023-10-28 11:18:33 发布

阅读量75

点赞数

文章标签： mysql oracle 数据库

本文链接：https://blog.csdn.net/weixin_54848834/article/details/134088946

版权

DDL数据定义语句

DDL：Data Define Language 数据定义语言，用于对数据库和表的管理和操作

一.库的管理

1.1 查看所有数据库

#查看所有数据库名称
SHOW DATABASES;

1.2 切换数据库

#切换到mydb1数据库
USE mydb1;

1.3 创建数据库

#创建数据库，创建一个名为mydb1的数据库。如果这个数据库已经存在，那么会报错。
CREATE DATABASE mydb1;

#在名为mydb1的数据库不存在时创建该库，这样可以避免报错。
CREATE DATABASE IF NOT EXISTS mydb1;

1.4 删除数据库

#删除数据库，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。
DROP DATABASE mydb1;

#就算mydb1不存在，也不会的报错。
DROP DATABASE IF EXISTS mydb1;

二.数据类型

MySQL与Java一样，也有数据类型。MySQL中数据类型主要应用在列上。

2.1 数值型

int：整型

double/float：浮点数类型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

decimal：定点数类型，在表示钱方面使用该类型，因为不会出现精度缺失问题；

案例：

#数值型  int  double/float  decimal
drop table if EXISTS testdb;
create table if not EXISTS testdb(
  id int,
  salary double(5,3),
  d1 float(5,3),
  d2 decimal(5,3)
);

insert into testdb(id,salary,d1,d2) values(111,123.456,123.456,123.456);  -- 报错
insert into testdb(id,salary,d1,d2) values(222,12.45,12.45,12.45);
insert into testdb(id,salary,d1,d2) values(333,12.4567,12.4567,123.4567);

说明：

①.浮点数和定点数都可以用类型名称后加“（M,D）”的方式来表示。

M：精度，该值的整数位+小数位一共显示M位数字

D：标度，小数位数一共显示D位数字，如果不够后面用0补齐，如果超过，则四舍五入

2.浮点数和定点数的区别：

a) 定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用于表示货币等精度高的数据；

b) 在不指定精度时，浮点数默认会按照实际的精度来显示，而定点数在不指定精度时，默认M=10，D=0；

在这里插入图片描述

2.2 字符型

char：固定长度字符串类型；char(n) n范围是0-255之间的整数

varchar：可变长度字符串类型；varchar(n) n范围是0~65535之间的整数

text：字符串类型；表示存储较长文本

	意思	格式	n的解释	特点	时间效率
char	固定长度字符	char(n)	最大的字符个数，可选填，默认：1	不管实际存储，开辟的空间都是n个字符	时间效率高(空间成本高)
varchar	可变长度字符	varchar(n)	最大的字符个数，必填	根据实际存储决定开辟的空间	时间效率低(空间成本低)

案例：

#字符型  char varchar
drop table if EXISTS testdb;
create table if not EXISTS testdb(
  c1 char(9),
  c2 varchar(9)
);
insert into testdb values('   abc   ','   abc   ');
select * from testdb;

-- char类型会自动去除末尾空格，varchar类型不会去除末尾空格
select concat(c1,'###'),concat(c2,'###') from testdb;

说明：

①存储的列长度

char：列的长度固为创建表时声明的长度

varchar:列的长度为可变，只要不超过声明的长度n

②检索的时候

char：删除了尾部的空格

varchar：保留这些空格

在这里插入图片描述

2.3 时间类型

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

datetime: 日期+时间格式为：yyyy-MM-dd hh:mm:ss 或者 yyyyMMddhhmmss

timestamp：时间戳类型；（除非自己写入数据，否则将直接获取当前平台时间进行写入）

	保存范围	所占字节
Datetime	1900-1-1~xxxx年	8
Timestamp	1970-1-1~2038-12-31	4

案例：

#时间类型  date time  timestamp  datetime
drop table if EXISTS testdb;
create table if not EXISTS testdb(
  d1 date,
  d2 time,
  d3 timestamp, -- 时间戳
  d4 datetime
);
insert into testdb(d1) values('2019-12-12');
insert into testdb(d2) values('15:15:15');
insert into testdb(d3) values('2019-12-12 15:15:15');
insert into testdb(d4) values('2019-12-12 17:15:15');
insert into testdb(d3) values('20191212161616');
insert into testdb(d4) values('20191212161616');
select * from testdb;

在这里插入图片描述

2.4 JSON类型

mysql5.7以上提供了json的存储类型，在此之前如果想在表中保存JSON格式类型的数据，则需要依靠varchar或者text之类的数据类型。5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。

官网：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

1）、JSON类型的特性

1、保证了JSON数据类型的强校验，JSON数据列会自动校验存入此列的内容是否符合JSON格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。

2、MySQL同时提供了一组操作JSON类型数据的内置函数。

3、更优化的存储格式，存储在JSON列中的JSON数据会被转成内部特定的存储格式，允许快速读取。

4、可以基于JSON格式的特征支持修改特定的键值。（即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去，MySQL内置的函数允许你通过一条SQL语句就能搞定）

2）、JSON类型的用法

	Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。

创建t_user表

-- ----------------------------
-- Table structure for `t_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(25) DEFAULT '' COMMENT '名称',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `login_info` json DEFAULT NULL COMMENT '登录方式json格式，有phone,wechat,alipay,qq,email',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

JSON 类型的列存储的数据要么是 NULL，要么必须是 JSON 格式数据，否则会报错。
JSON 数据类型默认值只能是 NULL。

插入数据

insert into t_user values (1,'Rose', 25, '{"email": "123@qq.com", "phone": "13200001111", "wechat": "123456789"}');
insert into t_user values (2,'Jack', 30, '{"phone": "13500001111"}');
insert into t_user values (3,'Sun', 18, '{"qq": "136458333", "phone": "13600001111"}');
insert into t_user values (4,'Sun', 45, '{"wechat":"133555888"}');

查询数据

查询JSON中的字段

JSON_EXTRACT 函数作用是提取json值，通过$.key

JSON_UNQUOTE 函数作用是去除json字符串的双引号，将值转成string类型

-- 案例1：查询列login_info的json串中的键为phone的值
-- JSON_EXTRACT(json_doc, path[, path] ...)：从json中返回想要的字段
-- 用法：JSON_EXTRACT(json格式数据, '$.json字段名',...)

-- 用法：简化写法，通过 json格式数据 -> '$.json字段名'


-- 案例2：查询列login_info的json串中的键为phone的值
-- JSON_UNQUOTE(json_val)：去除json字符串的双引号，将值转成string类型
-- 用法：JSON_UNQUOTE(JSON_EXTRACT(json格式数据, '$.json字段名',...))

-- 用法：简化写法，通过 json格式数据 ->> '$.json字段名'

简化 -> 表达式 等同于 JSON_EXTRACT(login_info, ‘$.phone’)

简化 ->> 表达式 等同于 JSON_UNQUOTE(JSON_EXTRACT(login_info, ‘$.wechat’))

-- 案例3：查询列login_info的json串中的键为phone、键为wechat的值


-- 简化写法

使用JSON中的字段作为查询条件

-- 案例：根据指定键phone的数据，查询用户信息


-- 或者
-- JSON_CONTAINS(target, candidate[, path]) JSON格式数据是否在字段中包含特定对象
-- 用法：JSON_CONTAINS(json格式数据, '"数据"', '$.json字段名')


-- 或者
-- JSON_OBJECT([key, val[, key, val] ...]) 将一个键值对列表转换成json对象
-- 用法：JSON_OBJECT('键','值')

JSON_PRETTY 查询格式化的json数据

-- 案例：查询t_user表中的json数据，要求以json格式输出
-- JSON_PRETTY(json_val) 查询格式化的json数据
-- 用法：JSON_PRETTY(json格式数据)

修改数据

修改JSON串中指定字段的值

-- 案例1：将列login_info中的json串中的键为phone的值设置为‘13600001122’
-- JSON_SET(json_doc, path, val[, path, val] ...) 设置JSON串中的字段值，若有则修改，若无则添加
-- 用法：JSON_SET(json格式数据, json中字段, 值,...)


-- 案例2：将列login_info中的json串中的键为phone的值替换为‘13600001133’
-- JSON_REPLACE(json_doc, path, val[, path, val] ...) 替换JSON串中的字段值，若有则替换，若无则不操作
-- 用法：JSON_REPLACE(json格式数据, json中字段, 值,...)

删除数据

删除JSON串中指定字段的值

-- 案例：将列login_info中的json串中的键为wechat的值删除
-- JSON_REMOVE(json_doc, path[, path] ...) 从JSON串中删除数据
-- 用法：JSON_REMOVE(json格式数据 ,json中字段 )

3）、JSON数组类型的用法

#创建商品标签表
CREATE TABLE `t_product_tag` (
  `product_id` int NOT NULL COMMENT '商品id',
  `tag_id` json NOT NULL COMMENT '商品标签id',
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

#插入数据，使用JSON数组的形式存储
insert into t_product_tag values (1,'[1,2,3]');
insert into t_product_tag values (2,'[3,4,7]');
insert into t_product_tag values (3,'[8,10,9]');

-- 1.MEMBER OF 搜索出JSON 文档中含有指定数据的元素
-- 用法：数值 MEMBER OF(json格式数据 -> '$')

-- 2.JSON_CONTAINS(json_doc1, json_doc2) 分别指定两个用于比较的 JSON 文档。要求搜索的数组的所有元素都存在于被搜索的数组中
-- 用法：JSON_CONTAINS(json格式数据 -> '$' , '[数据]') 对搜索键执行 AND 运算

-- 3.JSON_OVERLAPS(json_doc1, json_doc2) 分别指定两个用于比较的 JSON 文档。如果两个参数都是标量，则函数执行简单的相等性测试。
-- 用法：JSON_OVERLAPS(json格式数据 -> '$' , '[数据]') 执行 OR 运算

4）、JSON类型的总结

使用 JSON数据类型，推荐用MySQL8.0.17以上的版本性能更好，同时也支持 Multi-Valued Indexes 多值索引。

1、JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；

2、不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；

3、JSON 数据类型推荐用于存储不经常更新的静态数据。

5）、课堂练习

CREATE TABLE `dept` (
`id` int(11) NOT NULL,
`dept` varchar(255) DEFAULT NULL,
`json_value` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

-- 1、使用 json字段名->’$.json属性’ 进行查询条件 ， 查询deptLeaderId为5的数据

-- 2、查dept为“部门3”和deptLeaderId=5的数据

-- 3、查询json格式中deptLeaderId=5和deptId=5的数据

CREATE TABLE `dept_leader` (
`id` int(11) NOT NULL,
`leaderName` varchar(255) DEFAULT NULL,
`json_value` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入一些测试数据
insert into dept_leader VALUES(1,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(2,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(3,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(4,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(5,'leader5','{"name": "王五", "id": "5", "leaderId": "5"}');

-- 4、连表查询在dept 表中部门leader在dept_leader 中的详情

6）、其它JSON类型的常见函数

-- 1、函数json_extract()：从json中返回想要的字段
-- 用法：json_extract(字段名,$.json字段名)
select id,json_extract(json_value,'$.deptName') as deptName from dept;

-- 2、函数JSON_CONTAINS()：JSON格式数据是否在字段中包含特定对象。
-- 用法: JSON_CONTAINS(target, candidate[, path])
-- 案例：如果我们想查询包含deptName=部门5的对象
select * from dept where JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"));

-- 3、函数JSON_OBJECT():将一个键值对列表转换成json对象
-- 比如我们添加这么一组数据到dept表中：
insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');
-- 用法：JSON_OBJECT([key, val[, key, val] …])
-- 案例：比如我们想查询某个对象里面的值等于多少
select * 
from (
	select *,json_value->'$.deptName' as deptName FROM dept
) t 
where JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

-- 4、函数JSON_ARRAY():创建JSON数组
-- 比如我们添加这么一组数据到dept表中：
insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(8,'部门9','{"deptName": ["3","6","7"], "deptId": "5", "deptLeaderId": "5"}');
-- 用法：JSON_ARRAY([val[, val] …])
-- 案例：我们要查询deptName包含1的数据
select * from dept where JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"));

-- 5、函数JSON_TYPE()：查询某个json字段属性类型
-- 用法：JSON_TYPE(json_val)
-- 案例：比如我们想查询deptName的字段属性是什么
SELECt json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept;

-- 6、函数JSON_KEYS() ：JSON文档中的键数组
-- 用法：JSON_KEYS(json_value)
-- 案例：比如我们想查询json格式数据中的所有key
SELECt JSON_KEYS(json_value) FROM dept;

-- 7、函数JSON_SET() ：将数据插入JSON格式中，有key则替换，无key则新增
-- 用法：JSON_SET(json_doc, path, val[, path, val] …)
-- 案例：比如我们想针对id=2的数据新增一组：newData:新增的数据,修改deptName为新增的部门1
update dept 
set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') 
where id=2;

update dept 
set json_value=JSON_SET(json_value,'$.deptName','新增的部门2','$.newData','新增的数据') 
where id=2;

-- 修改前：2 部门2 {"deptId": "2", "deptName": "部门2", "deptLeaderId": "4"}
-- 修改后：2 部门2 {"deptId": "2", "newData": "新增的数据", "deptName": "新增的部门1", "deptLeaderId": "4"}
select * from dept where id =2;

-- 8、函数JSON_INSERT():插入值(往json中插入新值，但不替换已经存在的旧值)
-- 用法：JSON_INSERT(json_doc, path, val[, path, val] …)
-- 案例：
UPDATE dept 
set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
where id=2;

-- 修改前：2 部门2 {"deptId": "2", "newData": "新增的数据", "deptName": "新增的部门1", "deptLeaderId": "4"}
-- 修改后：2 部门2 {"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}
select * from dept where id =2;

-- 9、函数JSON_REPLACE()：
-- 用法：JSON_REPLACE(json_doc, path, val[, path, val] …)
-- 案例：如果我们要更新id=2数据中newData2的值为：更新的数据2
UPDATE dept 
set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') 
where id =2;

-- 修改前：2 部门2 {"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}
-- 修改后：2 部门2 {"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}
select * from dept where id =2;

-- 10、函数JSON_REMOVE() ：从JSON文档中删除数据
-- 用法：JSON_REMOVE(json_doc, path[, path] …)
-- 案例：删除key为a的字段。
UPDATE dept 
set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') 
where id =2;

-- 修改前：2 部门2 {"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}
-- 修改后：2 部门2 {"b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}
select * from dept where id =2;

2.5 二进制型

Blob：字节类型；可以用来存储图片数据jpg、音乐mp3 、视频avi。在MySQL中 Blob是一个二进制大型对象，可以是存储大量数据的容器，他能容纳不同大小的数据，插入Blob类型的数据必须使用PrepareStatement 因为Blob类型的数据无法使用字符串拼接。

类型	大小（字节）
TinyBlob	255
Blob	65K
MediumBlob	16M
LongBlob	4G

三.表的管理

3.1 创建表

语法：

CREATE TABLE [IF NOT EXISTS] 表名(

 	字段名 字段类型 [字段约束],

 	字段名 字段类型 [字段约束],

 	......

    字段名 字段类型 [字段约束]
);

案例：

CREATE TABLE stus(	
    sid	    CHAR(6),	
    sname	VARCHAR(20),	
    age		INT,	
    gender	VARCHAR(10) 
);


CREATE TABLE emps(	
    eid		CHAR(6),	
    ename	VARCHAR(50),	
    age		INT,	
    gender	VARCHAR(6),	
    birthday	DATE,	
    hiredate	DATE,	
    salary	DECIMAL(7,2),	
    resume	VARCHAR(1000)
);

练习：

#设计一张员工信息表，要求如下
1.编号（整型)
2.员工工号(长度不超过10位)
3.员工姓名（长度不超过255位)
4.性别（存储男/女)
5.年龄（不能存储负数)
6.身份证号（18位，身份证中可能会出现X字符)
7.入职时间（年-月-日)
8.家庭地址（json数据存储省市区详细地址）

3.2 查看表

-- 查看当前数据库中所有表名称
SHOW TABLES;

-- 查看指定表的创建语句
SHOW CREATE TABLE emps;

-- 查看表结构
DESC emps;

3.3 删除表

-- 删除表
DROP TABLE emps;

3.4 修改表结构

-- 修改之 添加列ADD：给stus表添加classname列：
ALTER TABLE stus ADD (classname varchar(100));
alter table stus add COLUMN borndate datetime not null;


-- 修改之 修改列类型MODIFY：修改stus表的gender列类型为CHAR(2)：
ALTER TABLE stus MODIFY gender CHAR(2);


-- 修改之 修改列名change：修改stus表的gender列名为sex：
ALTER TABLE stus change gender sex CHAR(2);


-- 修改之 删除列DROP：删除stus表的classname列：
ALTER TABLE stus DROP classname;


-- 修改之 修改表名称RENAME：修改stus表名称为student：
ALTER TABLE stus RENAME TO student;

3.5 复制表

-- 仅仅复制表的结构
CREATE TABLE 新表名 LIKE 表;

-- 复制表的结构+数据
CREATE TABLE 新表名 SELECT * FROM 表;

-- 案例：复制employees表中的last_name,department_id,salary字段到新表emp表，但不复制数据

四.完整性约束

完整性约束是为了表的数据的正确性！如果数据不正确，那么一开始就不能添加到表中。

用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的！

-- 非空约束：not null
	用于限制该字段为必填项

-- 默认约束：default
	严格来说，并不算约束。用于限制该字段没有显示插入值时，则直接使用默认值。
    
-- 主键约束：primary key
	用于限制该字段值不能重复，设置为主键的字段默认不能为空。
	一个表只能有一个主键，也可以是组合主键。

-- 唯一约束：unique
	用于限制该字段值不能重复。
	
	主键和唯一键的区别：其中主键字段不能为空，一个表中只能有一个主键；唯一键字段可以为空，一个表中可以有多个唯一键。
    
-- 检查约束：check
	用于限制该字段值必须满足指定条件。
	check (age between 1 and 100)，检查此时操作的年龄必须在1~100之间，但是MySQL没有检查约束，在mysql中加上不报错，但是没有效果。

-- 外键约束：foreign key
	用于限制两个表的关系，要求外键的字段值必须来自主表的关联列。
	要求
	1.主表的关联列和从表的关联列的数据类型一致，含义一样，命名无要求
	2.主表的关联列必须为主键

4.1 主键

当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识，而学生的姓名是不能做唯一标识的，因为学习有可能同名。

主键列的值不能为NULL，也不能重复！

指定主键约束使用PRIMARY KEY关键字

4.1.1 创建表：定义列时指定主键

CREATE TABLE stu(
		sid	  CHAR(6) PRIMARY KEY,

		sname	VARCHAR(20),

		age		INT,

		gender	VARCHAR(10) 
);

4.1.2 创建表：定义列之后独立指定主键

CREATE TABLE stu(
   	sid	  CHAR(6),

   	sname	VARCHAR(20),

   	age		INT,

   	gender	VARCHAR(10),

   	PRIMARY KEY(sid)
);

4.1.3 修改表时指定主键

ALTER TABLE stu ADD PRIMARY KEY(sid);

4.1.4 删除主键（只是删除主键约束，而不会删除主键列）

 ALTER TABLE stu DROP PRIMARY KEY;

4.2 主键自增长

MySQL提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

4.2.1 创建表时设置主键自增长（主键必须是整型才可以自增长）

CREATE TABLE stu(
		sid INT PRIMARY KEY AUTO_INCREMENT,

		sname	VARCHAR(20),

		age		INT,

		gender	VARCHAR(10)
);

4.2.2 修改表时设置主键自增长

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

4.2.3 修改表时删除主键自增长

ALTER TABLE stu CHANGE sid sid INT;

4.3 非空

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。

指定非空约束：

CREATE TABLE stu(
		sid INT PRIMARY KEY AUTO_INCREMENT,

		sname VARCHAR(10) NOT NULL,

		age		INT,

		gender	VARCHAR(10)
);

当为sname字段指定为非空后，在向stu表中插入记录时，必须给sname字段指定值，否则会报错：

INSERT INTO stu(sid) VALUES(1);

插入的记录中sname没有指定值，所以会报错！

4.4 唯一

还可以为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！

例如给stu表的sname字段指定唯一约束：

CREATE TABLE tab_ab(
	sid INT PRIMARY KEY AUTO_INCREMENT,

	sname VARCHAR(10) UNIQUE
);

INSERT INTO sname(sid, sname) VALUES(1001, ‘zs’);

INSERT INTO sname(sid, sname) VALUES(1002, ‘zs’);

当两次插入相同的名字时，MySQL会报错！

4.5 外键

主外键是构成表与表关联的唯一途径！

外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

创建dept表，指定deptno为主键列：

CREATE TABLE dept(
	deptno  INT PRIMARY KEY,

	dname	varchar(14),

	loc		varchar(13)
);

创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键：

CREATE TABLE emp(
	empno		INT,

	ename		VARCHAR(50),

	job		VARCHAR(50),

	mgr		INT,

	hiredate	DATE,

	sal		DECIMAL(7,2),

	comm		decimal(7,2),

	deptno		INT,

	CONSTRAINT fk_e_d FOREIGN KEY(deptno) REFERENCES dept(deptno)
)

案例1：

-- 案例1
/*
	建表stu学生表，
	字段id为整型，且是主键；
	字段name为字符型长度为20，且是唯一不为空；
	字段gender为字符型长度为1，且默认为男；
	字段email为字符型长度为20，且不为空；
	字段age为整型，且是检查约束（0-100）；
	字段majorid整型，外键关联major表中的id主键。

	建表major专业表，
	字段id为整型，且是主键；
	字段name为字符型长度为20，且是不为空。
*/

案例2：

-- 创建表qqinfo，里面包含qqid，添加主键约束、昵称nickname，添加唯一约束、邮箱email（添加非空约束）、性别gender

-- 删除表qqinfo

案例3：

/*
 设置自增长列
	1、自增长列要求必须设置在一个键上，比如主键或唯一键
	2、自增长列要求数据类型为数值型
	3、一个表至多有一个自增长列
*/

4.6 表与表之间的关系

**一对一：**例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。

设计从表可以有两种方案：

在t_card表中添加外键列（相对t_person表），并且给外键添加唯一约束；
给t_card表的主键添加外键约束（相对t_person表），即t_card表的主键也是外键。

**一对多（多对一）：**最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

**多对多：**例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

三范式：

第一范式：表中的字段不可再分割

例如：家庭住址(国家省市区县) --》可在分割成：国家 | 省 | 市 | 区 | 县

第二范式：当表与表之间有多对多的关系时，会建议创建关系表存储

角色可以拥有多个权限；每个权限可以属于多个角色；角色与权限表是多对多的关系。

设计：t_role 角色表 t_permission 权限表 role_permission 角色和权限关系表（遵循的原则：角色表和权限表中设计外键，关联关系表中的联合主键）

role_permission 中，设计role_id 和 permission_id字段，可以将其设计成联合主键。

第三范式：当表与表之间有一对多的关系时，会建议主表中设置外键关联从表的主键

用户可以拥有多个订单；用户与订单：一对多关系

设计：t_user 用户表 t_order 订单表；将订单表中设置一个外键字段user_id，外键关联用户表的主键

DML数据操作

一.插入数据

1.1 指定插入的字段

语法

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

INSERT INTO stus(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');

INSERT INTO stus(sid, sname) VALUES('s_1001', 'zhangSan');

1.2 插入全部字段

语法

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值

INSERT INTO stus VALUES('s_1002', 'liSi', 32, 'female');

注意：所有字符串数据必须使用单引号

二.修改数据

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stus SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001'; 

UPDATE stus SET sname='liSi', age='20' WHERE age>50 AND gender='male'; 

UPDATE stus SET sname='wangWu', age='30' WHERE age>60 OR gender='female';

三.删除数据

DELETE FROM 表名 [WHERE 条件]

DELETE FROM stus WHERE sid='s_1001';

DELETE FROM stus WHERE sname='chenQi' OR age > 30;

DELETE FROM stus;

TRUNCATE TABLE 表名

TRUNCATE TABLE stus;

【面试题】delete和truncate的区别

1.delete可以添加WHERE条件
TRUNCATE不能添加WHERE条件，一次性清除所有数据
2.truncate的效率较高
3.如果删除带自增长列的表，
使用DELETE删除后，重新插入数据，记录从断点处开始
使用TRUNCATE删除后，重新插入数据，记录从1开始

4.delete 删除数据，会返回受影响的行数
TRUNCATE删除数据，不返回受影响的行数

5.delete删除数据，可以支持事务回滚
TRUNCATE删除数据，不支持事务回滚

, sname) VALUES(‘s_1001’, ‘zhangSan’);




### 1.2 插入全部字段

**语法**

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值

```sql
INSERT INTO stus VALUES('s_1002', 'liSi', 32, 'female');

注意：所有字符串数据必须使用单引号

二.修改数据

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stus SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001'; 

UPDATE stus SET sname='liSi', age='20' WHERE age>50 AND gender='male'; 

UPDATE stus SET sname='wangWu', age='30' WHERE age>60 OR gender='female';

三.删除数据

DELETE FROM 表名 [WHERE 条件]

DELETE FROM stus WHERE sid='s_1001';

DELETE FROM stus WHERE sname='chenQi' OR age > 30;

DELETE FROM stus;

TRUNCATE TABLE 表名

TRUNCATE TABLE stus;

【面试题】delete和truncate的区别

4.delete 删除数据，会返回受影响的行数
TRUNCATE删除数据，不返回受影响的行数

5.delete删除数据，可以支持事务回滚
TRUNCATE删除数据，不支持事务回滚

little_Punk

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫