34_92from后面使用子查询
#from后面
/*
将结果当作一张表,所以需要起别名不然用不了
*/
#案例:查询每个部门的平均工资的工资等级
#首先把每个部门的平均工资查出来
SELECT AVG(salary)
FROM `employees` e
GROUP BY `department_id`;#通过部门id进行分组,算出每一个组的平均工资
SELECT `grade_level`,平均工资
FROM (
SELECT AVG(salary) 平均工资
FROM `employees` e
GROUP BY `department_id`) d
INNER JOIN `job_grades` j
ON d.平均工资 BETWEEN `lowest_sal` AND `highest_sal`;
**========================================================================================================================================================================================================================================================================================================================================**
35_93exists后面使用子查询
#exists后面(相关子查询)
/*
语法:
exists(完整的查询语句)
结果:
0或1
*/
SELECT EXISTS(
SELECT `employee_id`
FROM `employees`);
#案例1:查询有员工的部门的名字
#查询是否存在员工
SELECT `employee_id`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
SELECT `department_name`
FROM `departments`
WHERE EXISTS(
SELECT `employee_id`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`);
#上面这种写法有个问题,他是又拿了一个部门表,导致子查询一直都是有值的
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT `employee_id`
FROM `employees` e
WHERE e.`department_id`=d.`department_id`);#当前部门号
#就是说当前部门号是否有员工
#使用in的方式来代替
SELECT `department_name`
FROM `departments` d
WHERE d.`department_id` IN (
SELECT DISTINCT `department_id`
FROM `employees`
);
**========================================================================================================================================================================================================================================================================================================================================**
36_96案例查询子查询
#exists后面(相关子查询)
/*
语法:
exists(完整的查询语句)
结果:
0或1
*/
SELECT EXISTS(
SELECT `employee_id`
FROM `employees`);
#案例1:查询有员工的部门的名字
#查询是否存在员工
SELECT `employee_id`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
SELECT `department_name`
FROM `departments`
WHERE EXISTS(
SELECT `employee_id`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`);
#上面这种写法有个问题,他是又拿了一个部门表,导致子查询一直都是有值的
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT `employee_id`
FROM `employees` e
WHERE e.`department_id`=d.`department_id`);#当前部门号
#就是说当前部门号是否有员工
#使用in的方式来代替
SELECT `department_name`
FROM `departments` d
WHERE d.`department_id` IN (
SELECT DISTINCT `department_id`
FROM `employees`
);
**========================================================================================================================================================================================================================================================================================================================================**
37_95分页查询
#进阶八:分页查询
/*
应用场景:
当要显示的数据一页显示不全,需要分页提交sql请求
语法:
select 查询列表 7
from 表 1
[join type] join 表2 2
on 连接条件 3
where 筛选条件 4
group by 分组条件 5
having 筛选条件 6
order by 排序方式 8
limit offest,size; 9
offest 要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
特点:
--limit放在查询语句的最后
--公式:
要显示的页数page,每页的条目数size
select 查询列表
from 表名
limit (page-1)*size,size;
*/
#案例1:查询前5条员工信息
SELECT *
FROM `employees`
LIMIT 0,5;
#支持省略
SELECT *
FROM `employees`
LIMIT 0,5;
#案例2:查询第11条到第25条员工信息
SELECT *
FROM `employees`
LIMIT 10,15;
#案例3:有奖金的员工信息,并且工资较高的前10名显示出来
#首先有奖金
SELECT *
FROM `employees`
WHERE NOT ISNULL(`commission_pct`)
ORDER BY salary DESC
LIMIT 10;
**========================================================================================================================================================================================================================================================================================================================================**
38_98子查询经典案例
#1.查询工资最低的员工信息: last_name, salary
#2.查询平均工资最低的部门信息
#3.查询平均工资最低的部门信息和该部门的平均工资
#4.查询平均工资最高的job信息
#5.查询平均工资高于公司平均工资的部门有哪些?# 6.查询出公司中所有manager的详细信息.
#7.各个部门中最高工资中最低的那个部门的最低工资是多少
#8.查询平均工资最高的部门的 manager 的详细信息:last_name,department_id,email,salary
**========================================================================================================================================================================================================================================================================================================================================**
39_99作业详解
——、查询每个专业的学生人数
二、查询参加考试的学生中,每个学生的平均分、最高分三、查询姓张的每个学生的最低分大于60的学号、姓名
四、查询每个专业生日在"1988-1-1"后的学生姓名、专业名称五、查询每个专业的男生人数和女生人数分别是多少
六、查询专业和张翠山—样的学生的最低分
七、查询大于60分的学生的姓名、密码、专业名八、按邮箱位数分组,查询每组的学生个数
九、查询学生名、专业名、分数
十、查询哪个专业没有学生,分别用左连接和右连接实现十一、查询没有成绩的学生人数
**========================================================================================================================================================================================================================================================================================================================================**
40_100联合查询
#联合查询
/*
union联合,合并:将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
....
将所有的结果合并起来
应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致
注意:
--联合查询,查询列数需要一致
--要求多条查询语句的查询的每一列的类型和顺序最好是一致的
--会自动去重,如果想不去重可以使用union all
*/
#引入的案例:查询部门编号>90或邮箱包含a的员工信息
#之前的做法
SELECT *
FROM `employees`
WHERE `department_id`>90
OR `email` LIKE '%a%';
#联合查询的做法
SELECT * FROM `employees` WHERE `department_id`>90
UNION
SELECT * FROM `employees` WHERE `email` LIKE '%a%';
#案例:查询中国用户中男性的信息以及外国用户中男性的信息
**========================================================================================================================================================================================================================================================================================================================================**
41_102插入语句的方式
#DML语言
/*
数据操作语言:
插入:insert
修改:update
删除:delete
*/
#一:插入语句
/*
语法:
insert into 表名(列名,....) values(值1,....)
*/
#1.插入的值的类型要与列的类型一致后兼容
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUE(13,'陈春鹏','女','1992-02-03 00:00:00','19939939495',NULL,2);
#2.不可以为null的列必须插入值
#可以为null的列如何插入值?
#方式一:
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUE(13,'陈春鹏','女','1992-02-03 00:00:00','19939939495',NULL,2);
#方式二:
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`boyfriend_id`)
VALUE(14,'金星','女','1992-02-03 00:00:00','19939939495',2);
INSERT INTO `beauty`(`id`,`name`,`sex`,`phone`)
VALUE(15,'金星','女','19939939495');#有默认值的可以不填
#列的顺序可以颠倒
#列数和值的个数必须一致
#可以省略列名,默认所有列,而且列的顺序和表中列的顺序是顺序一致
INSERT INTO `beauty`
VALUE (16,'你好','女','1993-02-03 00:00:00','18209876579',NULL,11);
#方式二:
/*
语法:
insert into 表名
set 列名=值,列名=值,...;
*/
INSERT INTO beauty
SET `id`=19,NAME='刘涛',phone='999';
#两种方式大pk
#方式一支持插入多行,方式二不支持
INSERT INTO beauty#默认,所有列
VALUE(18,'aadd','女','1993-02-03 00:00:00','1341124',NULL,10)
,(20,'aaddd','女','1993-02-03 00:00:00','1341124',NULL,10)
,(21,'aadd','女','1993-02-03 00:00:00','1341124',NULL,10);
#方式一支持子查询,方式二不支持
INSERT INTO `beauty`(id,NAME,phone)
SELECT 22,'dwas','1344';#结果直接放进去
INSERT INTO `beauty`(id,NAME,phone)
SELECT id,boyName,'1111'
FROM `boys`
WHERE id<36;
SELECT *
FROM `beauty`;
SELECT *
FROM `boys`;
**========================================================================================================================================================================================================================================================================================================================================**
42_105修改表记录
#修改语句
/*
1.修改单表的记录*****
语法:
update 表名 1
set 列=新值,列=新值,.... 3
where 筛选条件; 2
执行顺序:
先去找到对应的表,进行过滤筛选,再去修改值
2.修改多表的记录[补充]
92语法:
update 表1 别名,表2 表名
set 列= ,列=
where 连接条件
and 筛选条件;
99语法:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,.....
where 筛选条件
先把表连接在一起,然后进行筛选
*/
#修改单表的记录
#案例1:修改`beauty`表中姓a的女神的电话为110
UPDATE `beauty`#修改beauty表
SET phone='110'
WHERE SUBSTR(`name`,1,1)='a';
SELECT `name`
FROM `beauty`
WHERE `name` LIKE 'a%';
#案例2:修改boys表中id号为37的名称为张飞,对应的userCP=10
UPDATE `boys`
SET `boyName`='张飞',`userCP`=10
WHERE id=37;
SELECT *
FROM `boys`;
#修改多表的记录
#案例1:修改张无忌的女朋友的手机号为114
#首先我需要知道谁是张无忌的女朋友,所以需要连表
UPDATE `beauty` meinv
INNER JOIN `boys` chou
ON meinv.`boyfriend_id`=(
#查出张无忌对应的id
SELECT `id`
FROM `boys`
WHERE `boyName`='张无忌')
#对于这样一张表,开始进行修改
SET `phone`='114';
SELECT *
FROM `beauty`;
#案例2:修改没有男朋友的女神的男朋友编号都为张飞(37号)
SELECT *
FROM `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`;#一个都没匹配上的会以空值的形式存在
UPDATE `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
SET `boyfriend_id`=37
WHERE ISNULL(bo.`id`);
**========================================================================================================================================================================================================================================================================================================================================**
43_107删除语句介绍
#删除语句
/* 1.单表的删除
方法一:delete
语法:
delete from 表名 where 筛选条件;
注意:
删除的是一整行
方法二:truncate
语法:
truncate table 表名;
注意:
无法加筛选条件
2.多表的删除
sql92语法:
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
sal99语法:
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件;
*/
#案例1:删除手机号以9结尾的女神信息
DELETE FROM `beauty`
WHERE `phone` LIKE "%9";
SELECT *
FROM `beauty`;
#案例2:多表的删除
#案例:删除张无忌的女朋友的信息
DELETE b #需要进行删除的表
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`#证明他是她的男朋友
WHERE `boyName`='张无忌';
SELECT *
FROM `boys`;
#案例:删除黄晓明的信息,以及他女朋友的信息
DELETE b,bo
FROM `beauty` b
INNER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE `boyName`="黄晓明";
#方式二:truncate语句
#案例:将魅力值>100的男神信息删除(truncate无法做到)
#delete pk truncate
/*
1.delete可以加筛选条件,truncate不可以
2.truncate删除,效率高一丢丢
3.假如说要删除的表中有自增长列,如果用delete删除后,再插入数据
自增长的值从断点开始,而truncate删除后,再插入数据,自增长列的值
从1开始
4.truncate的删除没有返回值,delete的删除有返回值
5.truncate删除不能回滚,delete的删除可以回滚
*/
**========================================================================================================================================================================================================================================================================================================================================**
44_110案例详解
#1.运行以下脚本创建表my_employees
CREATE TABLE my_employees (
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR (10),
department_id INT
);
#2.显示表my employees的结构(注意是显示结构,不是数据)
DESC `my_employees`;
SELECT *
FROM `my_employees`;
/*3. 向my _employees表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman chad cnewman 750
5 Ropeburn Audrey Aropebur 1550
*/
INSERT INTO `my_employees`#默认是所有列
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','chad','cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
DELETE FROM `my_employees`;
INSERT INTO `my_employees`
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','chad','cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
/*
4. 向users表中插入数据
1 Rpate 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/
INSERT INTO `users`
VALUES(1,'Rpate',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
SELECT *
FROM `users`;
#5.将3号员工的last_name修改为"drelxer"
UPDATE `my_employees`
SET `Last_name`="drelxer"
WHERE `Id`=3;
#6.将所有工资少于900的员工的工资修改为1000
UPDATE `my_employees`
SET `Salary`=1000
WHERE `Salary`<900;
#7.将userid 为Bbiri的uSER表和my_employees表的记录全部删除
#这个题目是一个多表删除
DELETE u,m
FROM `users` u
INNER JOIN `my_employees` m
ON u.`id`=m.`Id`#证明是同一个人
WHERE u.`userid`='Bbiri';
#8.删除所有数据
DELETE FROM `my_employees`;
#9.检查所作的修正
SELECT *
FROM `my_employees`;
#10.清空表my _employees
TRUNCATE TABLE `my_employees`
**========================================================================================================================================================================================================================================================================================================================================**
45_112库的管理
#DDL
/*
数据定义语言
库和表的管理
1.库的管理
创建,修改,删除
2.表的管理
创建,修改,删除
创建:
create
修改:
alter
删除:
drop
#库的管理
#1.库的创建
语法:
create database 库名;
*/
#案例1:创建库books
CREATE DATABASE IF NOT EXISTS books;#可以在数据库已经创建时不报错
#2.库的修改(库一般不修改,容易使数据出现问题)
#rename database books to 新库名;
#以前的语法现在不被允许
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#库的删除
DROP DATABASE IF EXISTS `books`;#判断目标是否存在
#存在则删除,不存在则不动
**========================================================================================================================================================================================================================================================================================================================================**
46_113表的操作
#表的管理
#1.表的创建
/*
create table 表名(
列名 列的类型[(长度)约束],
列名 列的类型[(长度)约束],
列名 列的类型[(长度)约束],
列名 列的类型[(长度)约束],
...
列名 列的类型[(长度)约束]
*/
#创建books数据库
CREATE DATABASE books;
#案例:创建book表
CREATE TABLE book(
id INT, #编号
bname VARCHAR(20),#图书名
price DOUBLE,#价格
autherid INT,#作者
publishDate DATETIME#出版日期
);
DESC book;
#案例:创建表auther
CREATE TABLE auther(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC book;
#2.表的修改
/*
语法:
alter table 表名
添加 修改列名 修改列的类型 删除
add | change |modify | drop column
修改表名
rename to
*/
#--修改列名
ALTER TABLE book
CHANGE COLUMN publishDate pubDate DATETIME;#注意这里需要给该列的数据类型
#--修改列的类型或约束
ALTER TABLE book
MODIFY COLUMN pubdate TIMESTAMP;
#--添加新列
ALTER TABLE `b_auther`
ADD COLUMN annual DOUBLE;
#--删除列
ALTER TABLE b_auther
DROP COLUMN IF EXISTS annual;#不被允许的语法,无法判断该列是否存在
ALTER TABLE b_auther
DROP COLUMN annual;
#--修改表名
ALTER TABLE `auther`
RENAME TO b_auther;
#3.表的删除
DROP TABLE `b_auther`;
SHOW TABLES;#查看当前库的所有表
#通用的写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE DATABASE 新表名();
#4.表的复制
INSERT INTO `b_auther`
VALUES(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
#仅仅复制表的结构
CREATE TABLE copy LIKE `b_auther`;
#复制表的结构外加数据
CREATE TABLE copy2
SELECT * FROM `b_auther`;
#只复制部分数据
CREATE TABLE copy3
SELECT id,`au_name`
FROM `b_auther`
WHERE `nation`='中国';
#仅仅复制某些字段(不要数据)
CREATE TABLE copy4
SELECT id,`au_name`
FROM `b_auther`
WHERE 1=2;
**========================================================================================================================================================================================================================================================================================================================================**
47_117案例详解库和表的管理
/*
1. 创建表dept1
name NULL? TYPE
id iNT(7)
name VARCHAR(25)*/
CREATE TABLE dept1(
id INT(7),
NAME VARCHAR(25)
);
#2.将表departments中的数据插入新表dept2中
INSERT INTO `dept1`#默认所有数据
SELECT `department_id`,`department_name`
FROM `departments`;
CREATE TABLE dept2
SELECT `department_id`,`department_name`
FROM `departments`;
#如果说dept2和departments不在同一个数据库下面可以这样写
CREATE TABLE dept2
SELECT `department_id`,`department_name`
FROM `myemployees`.`departments`;
/*
3.创建表emp5
NAME NULL? TYPE
id iNT (7)
First_name VARCHAR(25)
Last_name VARCHAR(25)
Dept_id iNT(7 )*/
CREATE TABLE emp5(
id INT (7),
First_name VARCHAR(25),
Last_name VARCHAR(25),
Dept_id INT(7)
);
#4.将列Last_name的长度增加到50
#更改约束
ALTER TABLE `emp5`
MODIFY COLUMN Last_name VARCHAR(50);
#5.根据表employees创建employees2
CREATE TABLE employees2 LIKE employees;
#6.删除表emp5
DROP TABLE `emp5`;
#7.将表employees2重命名为emp5
ALTER TABLE `employees2`
RENAME TO emp5;
#8在表dept1和emp5中添加新列test_column,并检查所作的操作
ALTER TABLE dept1
ADD COLUMN test_column INT;
DESC `dept1`;
ALTER TABLE emp5
ADD COLUMN `test_column` INT;
DESC `emp5`;
#9.直接删除表emp5中的列dept_id
ALTER TABLE `emp5`
DROP COLUMN `department_id`;
DESC `emp5`;
**========================================================================================================================================================================================================================================================================================================================================**
48_119整型
#常见的数据类型
/*
数值型:
整型
小数:
定点数
浮点数
字符型:
较短的文本:
char,varchar
较长的文本:
text,blob(较长的二进制数据)
日期型:
*/
#整型
/*
分类:
tinyint,smallint,mediumint,int(integer),bigint
1 2 3 4 8
特点:
--默认是有符号,如果想设置无符号,需要添加unsigned关键字
--如果插入的数值超出了我们整型的范围怎么办?
看的视频是将临界值插入,且不报错
但是我写的时候是会报错的,不允许这样的操作
--如果不设置长度,会有默认的长度,当然也可以自己设置
注意:
长度代表了显示的最大宽度,如果不够会用0在左边填充
但必须搭配zerofill使用,并且会变成无符号
真正的数值范围是看""""数据的类型""""
*/
#1.如何设置无符号和有符号(就是二进制保存的时候是否有符号位,符号位用来表示正负)
DROP TABLE IF EXISTS `tab_int`;
CREATE TABLE tab_int(
t1 INT(10) ZEROFILL,
t2 INT ZEROFILL UNSIGNED
);
DESC `tab_int`;
SELECT *
FROM `tab_int`;
INSERT INTO `tab_int`
VALUES(100,100);
INSERT INTO `tab_int`
VALUES(100);
INSERT INTO `tab_int`
VALUES(9999999999,999999999999);#直接报错
**========================================================================================================================================================================================================================================================================================================================================**
49_120浮点型
/*
小数:
1.浮点型:
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)
特点:
--m:
整数部位+小数部位(总长度)
--d.
小数部位
对于超出范围的会报错(与视频中不相同)
--m和d都可以省略
如果是decimal,则m默认为10,d默认为0
如果是float和double,则会根据插入的数值的精度来决定精度(只要不超出
范围,原封不动的保存)
--定点型的精度较高,如果要求插入数值的精度较高,如货币运算等
则考虑使用
*/
DROP TABLE `tab_float`;
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2)
);
SELECT *
FROM `tab_float`;
INSERT INTO `tab_float`
VALUE(123.45,123.45,123.45);#运行成功
INSERT INTO `tab_float`
VALUE(123.456,123.456,123.456);#没有报错但是警告了,自动的四舍五入
INSERT INTO `tab_float`
VALUE(123.4,123.4,123.4);#自动填充成两位小数
INSERT INTO `tab_float`
VALUE(1234.4,1234.4,1234.4);#报错,我不知道为什么和视频里面不一样
#在视频从会自动向下取最大值,但在我这里会报错,可能是为了准确性
CREATE TABLE tab_float(
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL
);
SELECT *
FROM `tab_float`;
INSERT INTO `tab_float`
VALUE(123.4522,123.452456,123.45);#只有f3会有一个警告
#原则:
/*
所选择的类型越简单越好,能保存的数值类型越小越好
*/
**========================================================================================================================================================================================================================================================================================================================================**
50_121字符型
#字符型
/*
较短的文本:
char
varchar
较长的文本:
text
blob(较大的二进制)
特点:
写法 m的意思 特点 效率比较
char char(m) 最大的字符数 固定的字符长度 高
(可以省略,默认为1)
varchar varchar(m) 最大的字符数 可变长度的字符 低
(不可以省略)
enum:
插入指定在列表中的值,列表中需要放相同类型元素
注意:
只能是单个元素,不能出现列表中没有'a,b',而添加
'a,b'的情况
set:
随意插入出现在集合中的值,集合中需要放相同类型元素
注意:
可以是多个元素,可以出现列表中没有'a,b',而添加
'a,b'的情况(只要有'a','b')
其他:
binary和varbinary用于保存较短的二进制
*/
DROP TABLE `tab_char`;
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
SELECT *
FROM `tab_char`;
INSERT INTO `tab_char`
VALUES('a');
INSERT INTO `tab_char`
VALUES('b');
INSERT INTO `tab_char`
VALUES('c');
INSERT INTO `tab_char`
VALUES('111');
INSERT INTO `tab_char`
VALUES('a');
INSERT INTO `tab_char`
VALUES('a,b');#这回报错,不被允许的操作
DROP TABLE tab_set;
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO `tab_set`#插入第一个的时候没有任何问题
VALUES('a');
INSERT INTO `tab_set`
VALUES('a');#插入第二个的时候也没有问题,所以在该列是可以出现重复元素的
INSERT INTO `tab_set`
VALUES('a,b,c');#可以执行,插入a,b,c
INSERT INTO `tab_set`
VALUES('a,b,b');#可以执行,插入a,b(集合不出现重复元素)
SELECT *
FROM `tab_set`;
**========================================================================================================================================================================================================================================================================================================================================**
51_122日期型
/*
date(4个字节):年-月-日(没有时间)
datetime(8个字节):年-月-日 当前时间
timestamp(4):返回当前时间戳
time(3):当前时间
year(1):当前年份
特点:
字节 范围 时区等的影响
datetime 8 1000-9999 不受影响
timestamp 4 1970-2038 受时区影响
*/
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO `tab_date`
VALUES(NOW(),NOW());#不同时区对于的值不同,即使已经插入,时区改变已插入的值也改变
SELECT *
FROM `tab_date`;
SHOW VARIABLES LIKE 'time_zone';#查看当前时区
SET time_zone='SYstem';
**========================================================================================================================================================================================================================================================================================================================================**
52_125约束介绍
#常见约束
/*
分类:六大约束
--not null: 非空,用于保证该字段的值不能为空
比如:姓名,学号
--default: 默认,用于保证该字段有默认值
比如性别
--primary key:主键,用于保证该字段的值具有唯一性,并且非空
比如学号,员工编号
--unique:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号
--check:检查约束[mysql中不支持]
比如年龄,性别
--foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自主表的关联字段
在从表添加外键约束,用于引用主表中某列的值
比如:学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束
除了非空,默认,其他的都支持
*/
CREATE TABLE 表名(
字段名 字段类型 约束
);
**========================================================================================================================================================================================================================================================================================================================================**
53_126创建表时添加约束
#创建表时添加约束
#1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可
只支持:
默认,非空,主键,唯一
*/
CREATE DATABASE students;
USE students;
CREATE TABLE major(
id INT PRIMARY KEY,
majorname VARCHAR(20)
);
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuname VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查约束,在sqlserver中有效果
seat INT UNIQUE,#唯一约束
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键约束
);
DESC stuinfo;#查看表的结构
#这两个可以混合使用
SHOW INDEX FROM stuinfo;#查看表中所有的索引
DROP TABLE stuinfo;
SELECT *
FROM major;
#2.添加表级约束
/*
语法:
在各个字段的最下面
[constraint 约束名] 约束类型(字段名)
可以省略,会有默认的名字
*/
DROP TABLE IF EXISTS stuinfo;#判断是否存在,再以此来判断是否删除
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一
CONSTRAINT ck CHECK(gender IN ('男','女')),#检查约束,不支持\
CONSTRAINT fk FOREIGN KEY(majorId) REFERENCES major(id)#外键
);
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
majorid INT,
PRIMARY KEY(id),#主键
UNIQUE(seat),#唯一
CHECK(gender IN ('男','女')),#检查约束,不支持\
FOREIGN KEY(majorId) REFERENCES major(id)#外键
);
SHOW INDEX FROM `stuinfo`;
#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,#id
stuname VARCHAR(20) NOT NULL,#stuname
sex CHAR(1) CHECK(sex IN ('男','女')),#sex
age INT DEFAULT 18,#age
seat INT UNIQUE,#seat
majorid INT,#majorid
#表级别约束
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)
);
/*
表的数据的修改
insert
update
delete
/*
/*
表的改变
create
alter
drop
*/
**========================================================================================================================================================================================================================================================================================================================================**
54_128主键和唯一键的区别
/*
主键和唯一的大对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ 不允许 至多有一个 允许
唯一 √ 允许 可以有多个 允许
注意:
唯一允许为空,且允许多个空值]
什么叫是否允许组合:
PRIMARY KEY(id,stuname)
unique(a,b,c)
这样只有在组合起来的列同时都一样时才算重复
*/
DROP TABLE IF EXISTS `stuinfo`;
#报错,多重主键定义
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,#id
stuname VARCHAR(20) PRIMARY KEY,#stuname
sex CHAR(1) CHECK(sex IN ('男','女')),#sex
age INT DEFAULT 18,#age
seat INT UNIQUE,#seat
majorid INT,#majorid
#表级别约束
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)
);
CREATE TABLE IF NOT EXISTS stuinfo(
id INT,#id
stuname VARCHAR(20),#stuname
sex CHAR(1) CHECK(sex IN ('男','女')),#sex
age INT DEFAULT 18,#age
seat INT UNIQUE,#seat
majorid INT,#majorid
#表级别约束
PRIMARY KEY(id,stuname),
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)
);
INSERT INTO `stuinfo`(id,stuname,`seat`)
VALUES(1,'a',NULL);
INSERT INTO `stuinfo`(id,stuname,`seat`)
VALUES(1,'hh',NULL);
DESC `stuinfo`;
SELECT *
FROM `stuinfo`;
**========================================================================================================================================================================================================================================================================================================================================**
55_129外键的特点
/*
外键:
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3.主表的关联列必须是一个key(一般是主键或唯一)
4.插入数据时,先插入主表,再插入从表
不然主表都没的数据,从表凭什么有
5.删除数据时,先删除从表,在删除主表
*/
DROP TABLE IF EXISTS major;
DROP TABLE IF EXISTS `stuinfo`;
#如果id不是主键,试一下,stuinfo中的majorid是否还能关联
CREATE TABLE major(
id INT,
majorname VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS stuinfo(
id INT,#id
stuname VARCHAR(20),#stuname
sex CHAR(1) CHECK(sex IN ('男','女')),#sex
age INT DEFAULT 18,#age
seat INT UNIQUE,#seat
majorid INT,#majorid
#表级别约束
PRIMARY KEY(id,stuname),
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)
#可以看到这里majorid的类型和其主表的id列类型相同
);
#不能关联
**========================================================================================================================================================================================================================================================================================================================================**
56_130修改表时添加约束
#修改表时,添加约束
/*
语法:
添加列级约束
alter table 表名
modify column 字段名 字段类型 新约束;
添加表级约束
alter table 表名
add 新约束(字段名)
*/
#1.添加非空约束
DROP TABLE IF EXISTS `stuinfo`;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT,#id
stuname VARCHAR(20),#stuname
sex CHAR(1),#sex
age INT,#age
seat INT,#seat
majorid INT#majorid
);
#1.添加非空约束
ALTER TABLE `stuinfo`
MODIFY COLUMN seat INT NOT NULL;
#2.添加默认约束
ALTER TABLE `stuinfo`
MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#列级约束写法
ALTER TABLE `stuinfo`
MODIFY COLUMN id INT PRIMARY KEY;
#表级约束写法
ALTER TABLE `stuinfo`
ADD PRIMARY KEY(id);
#4.添加唯一
#列级写法
ALTER TABLE `stuinfo`
MODIFY COLUMN seat INT UNIQUE;
#表级写法
ALTER TABLE `stuinfo`
ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE `stuinfo`
ADD FOREIGN KEY(majorid) REFERENCES `major`(id);
DESC `stuinfo`;
SHOW INDEX FROM `stuinfo`;
**========================================================================================================================================================================================================================================================================================================================================**
57_131修改表示删除约束
#修改表时删除约束
DROP TABLE IF EXISTS `stuinfo`;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT,#id
stuname VARCHAR(20),#stuname
sex CHAR(1),#sex
age INT,#age
seat INT,#seat
majorid INT#majorid
);
#1.删除非空约束
ALTER TABLE `stuinfo`
MODIFY COLUMN seat INT NULL;
#2.删除默认约束
ALTER TABLE `stuinfo`
MODIFY COLUMN age INT;
#3.删除主键
ALTER TABLE `stuinfo`
DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE `stuinfo`
DROP INDEX seat_2;#这个要给你创建的时候给定的名字,如果忘记了可以SHOW INDEX FROM `stuinfo`
#看一下
#删除外键
ALTER TABLE `stuinfo`
DROP FOREIGN KEY fk;#这里要不删除掉,要不报错,先放着
DESC `stuinfo`;
SHOW INDEX FROM `stuinfo`;
**========================================================================================================================================================================================================================================================================================================================================**
58_132案例详解常见约束
CREATE TABLE emp2(
id INT
);
DROP TABLE IF EXISTS `dept2`;
CREATE TABLE dept2(
id INT
);
#1.向表emp2的id列中添加PR工MARY KEY约束(my_emp_id_pk)
ALTER TABLE `emp2`
ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(`id`);
SHOW INDEX FROM `emp2`;
#2向表dept2的id列中添加PRINARY KEY约束(my_dept_id_pk>
ALTER TABLE `dept2`
ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(`id`);
SHOW INDEX FROM `dept2`;
#3.向表emp2中添加列dept_id,并在其中定义FOREIGN KEY约束,
#与之相关联的列是dept2表中的id列.
ALTER TABLE `emp2`
ADD COLUMN dept_id INT;
ALTER TABLE `emp2`
ADD CONSTRAINT ddd FOREIGN KEY(dept_id) REFERENCES dept2(id);
SHOW INDEX FROM `emp2`;
**========================================================================================================================================================================================================================================================================================================================================**