MYSQL学习数据库约束--Java学习之数据库

/*
CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int , -- 数学
english int -- 英语
);
--ALTER TABLE student3 ADD math INT;
--ALTER TABLE student3 ADD english INT;
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (******);

*/
###########################DQL查询语句的使用#############################

SQL排序查询

--SELECT 字段 FROM 表名 ORDER BY 排序的字段 [ASC|DESC];
SELECT * FROM student3 ORDER BY age ; #默认升序查询
SELECT * FROM student3 ORDER BY age ASC; #升序查询
SELECT * FROM student3 ORDER BY age DESC;#降序查询

组合排序查询

--SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
--查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
SELECT * FROM student3 ORDER BY age DESC , math ASC;

SQL聚合函数查询

/*
count：统计指定列记录数，记录为NULL的不统计sum：计算指定列的数值和，如果不是数值类型，那么计算结果为0max：计算指定列的最大值min：计算指定列的最小值avg`：计算指定列的平均值
/
SELECT COUNT() 学生总数 FROM student3;
SELECT COUNT(*) 学生总数 FROM student3 WHERE age > 20;
SELECT MAX(age) 最大年龄 FROM student3 ;
SELECT MIN(age) 最小年龄 FROM student3 ;
SELECT AVG(age) 平均年龄 FROM student3 ;
SELECT SUM(math) 数学总和 FROM student3;

DQL分组查询

--SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段名;
--如果直接分组查询是没有意义的，默认是拿每组的第一个数据展示出来
SELECT * FROM student3 GROUP BY sex;
--分组是为了按照组进行数据的分析和需求操作
--1.查询男人和女人每组的数学分数总和
SELECT SUM(math) , sex FROM student3 GROUP BY sex;
--2.查询男女各多少人
SELECT COUNT() 总人数 , sex FROM student3 GROUP BY sex;
--3.查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT COUNT() 人数 , sex FROM student3 WHERE age > 25 GROUP BY sex;
--4.查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
--a.查询出年龄大于25岁的人
--b.性别分组
--c.统计每组的人数
--d.分组以后的条件应该用having跟在最后面
/*
having与where的区别

having是在分组后对分组后的结构数据进行过滤.
where是在分组前对表数据进行过滤
having后面可以使用聚合函数，查询分组后数据条数>2
where后面不可以使用聚合函数
/
SELECT COUNT() , sex FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT() > 2;
--5.找出男人和女人中的数学最高分
SELECT MAX(math) , sex FROM student3 GROUP BY sex;
--6.查询出男人和女人中分数超过70分的总人数
SELECT COUNT() 人数, sex FROM student3 WHERE math > 70 GROUP BY sex;

DQL分页查询

DQL查询语句-limit语句

/*
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
*/

SELECT 字段 FROM 表名 WHERE 条件 LIMIT offset, length ;

参数一：起始数据行位置的索引（从0开始计数）

参数二：一页展示的数据量

--第一页数据
SELECT * FROM student3 LIMIT 0 , 5;
SELECT * FROM student3 LIMIT 5;#第一页可以省略起始位置索引0
--第二页数组
SELECT * FROM student3 LIMIT 5 , 5;
--第三页
SELECT * FROM student3 LIMIT 10 , 5;
--查询出男人全部数据中的第二页

Limit必须放在SQL语句的最后面，永远是最后才能分页查询!

SELECT * FROM student3 WHERE sex = '男' LIMIT 5 , 5 ;

SQL备份与恢复

--备份在cmd中(只备份了表和数据)
mysqldump -uroot -proot day22 > C:\WORK\CODE\bak.sql
--恢复，必须先创建数据库，再使用数据库，再倒入脚本
source C:\WORK\CODE\bak.sql;
--也可以使用工具备份与恢复（导入）

-################建表约束###########################

id主键约束

主键:用来唯一标识一条记录:主键必须是唯一的，不能为空。
哪个字段作为表的主键：通常不用业务字段作为主键，主键是单独列一般叫id.

建表的时候给主键

CREATE TABLE dog (
id INT PRIMARY KEY, -- id是主键 PRIMARY KEY是主键约束！
NAME VARCHAR(20),
age INT
);
INSERT INTO dog(id , NAME , age) VALUES (1 , '泰迪',2);
INSERT INTO dog(id , NAME , age) VALUES (2 , '泰迪',2);
--建表后给主键
ALTER TABLE student3 ADD PRIMARY KEY(id);
-- 删除该主键约束，不是删除字段！只是删除主键约束!
ALTER TABLE student3 DROP PRIMARY KEY;

主键自增的约束（只有主键才有自增约束）

--主键值由数据库自动维护
CREATE TABLE cat(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键AUTO_INCREMENT自增
NAME VARCHAR(23),
sex CHAR(2)
)
INSERT INTO cat(NAME , sex) VALUES('叮当猫',1);
INSERT INTO cat(NAME , sex) VALUES('汤姆',1);
DELETE FROM cat WHERE id = 3;
修改自增起始值（通常用不上，用默认值即可！）
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
ALTER TABLE cat AUTO_INCREMENT= 300;

delte不会影响自增的数字
TRUNCATE删除表后，自增数字会回到1
TRUNCATE TABLE cat;

DROP TABLE st1;
唯一约束 UNIQUE，约束与约束间存在包含关系，主键包含（唯一，非空约束）
CREATE TABLE st1 (
id INT PRIMARY KEY AUTO_INCREMENT , --
NAME VARCHAR(20) UNIQUE
);

INSERT INTO st1(NAME) VALUES ( '貂蝉');
INSERT INTO st1(NAME) VALUES ( '西施');
INSERT INTO st1(NAME) VALUES ( '王昭君');
INSERT INTO st1(NAME) VALUES ( '杨玉环');
INSERT INTO st1(NAME) VALUES ( NULL);

非空约束 not null(很弱的约束)

CREATE TABLE st2 (
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20) NOT NULL,
gender CHAR(2)
);
INSERT INTO st2(NAME , gender) VALUES('孙悟空','男');
INSERT INTO st2(NAME , gender) VALUES('孙悟空','男');
INSERT INTO st2(gender) VALUES('男');
--这里就报错了，不能存储null
INSERT INTO st2(NAME , gender) VALUES(NULL,'男');
INSERT INTO st2(NAME , gender) VALUES('null','男');

--默认值约束
CREATE TABLE st3 (
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20),
address VARCHAR(50) DEFAULT '广州'
);
INSERT INTO st3(NAME , address) VALUES('孙悟空','深圳');
INSERT INTO st3(NAME ) VALUES('蜘蛛精');

##################外键约束################

外键约束至少是两张表的关系

主键是一张表中数据的关系。

-- 创建部门表
CREATE DATABASE dleiDB;
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT # 外键
);

-- 添加2个部门
INSERT INTO dept (dep_name, dep_location) VALUES
('研发部', '广州'), ('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);
('波仔', 18, 3);

结论：我们把部门表的主键作为了员工表的一个外键字段的关系。

减少了数据的冗余，但是不受约束，可能出现数据失真。此时员工表中的部门id字段就是外键。

什么是外键约束(重点，一定会用到)
希望外键的值一定要来自于对应其他表主键的值。这样数据就可以实现正确性，一致性。
加了外键外键约束后的作用：减少了数据的冗余，提高了数据的正确性和一致性。
.建表后加外键约束
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
ALTER TABLE employee ADD CONSTRAINT emp_dep_id_fk FOREIGN KEY (dep_id) REFERENCES dept(id);
INSERT INTO employee (NAME, age, dep_id) VALUES ('波仔', 18, 2);
ALTER TABLE 表名 DROP FROEIGN KEY 外键名称; 删除外键约束
ALTER TABLE employee DROP FOREIGN KEY emp_dep_id_fk;

2.建表时加外键约束

DROP TABLE employee;
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加一个外键约束。本表中dept_id作为外键参考部门表的主键id.
-- 外键取名公司要求,一般fk结尾
CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES dept(id)
);
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

###########################外键的级联操作#########################
--删除研发部门
--如果这条数据有被其他表外键关联的话那么是不能直接删除的
DELETE FROM dept WHERE dep_name='研发部';
--如果这条数据的这个字段是被其他表外键关联的话那么是不能直接修改
UPDATE dept SET id = 3 WHERE id = 2;
--如果修改的这个字段与其他表没有被外键关联是可以直接修改的
UPDATE dept SET dep_name='超级销售部' WHERE id = 2;

--级联操作的特性应该在建表的时候就申明
ON UPDATE CASCADE -- 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE -- 级联删除，主键发生删除时，从表关联的全部数据都会被直接删除。

CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT,
-- 添加外键约束,并且添加级联更新和级联删除
CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES
dept(id) ON UPDATE CASCADE ON DELETE CASCADE
);
--可以开始级联修改，主表改了，从表也会被自动修改该字段值
UPDATE dept SET id = 3 WHERE id = 2;
--可以开始级联删除，主表数据删除了以后，从表关联的整个数据都会被直接删除!!
--级联删除慎用！
DELETE FROM dept WHERE dep_name='研发部';
DELETE FROM dept WHERE dep_name='超级销售部';

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,482评论 6赞 481
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,377评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 152,762评论 0赞 342
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,273评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,289评论 5赞 373
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,046评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,351评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,988评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,476评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,948评论 2赞 324
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,064评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,712评论 4赞 323
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,261评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,264评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,486评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,511评论 2赞 354
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,802评论 2赞 345