黑马MySQL数据库学习day02 表数据CRUD 约束CRUD
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 基础查询练习: 3 1.字段列表查询 4 当查询全部字段时,一种简便方式,使用*代替全部字段(企业中不推荐使用) 5 6 2.去除重复行 7 DISTINCT,注意修饰的是行,也就是整个字段列表,而不是单个字段。 8 DISTINCT的位置在字段列表前。 9 10 3.计算字段 11 字段运算,函数处理。 12 13 bug点: null值参与计算,得到的值是null 14 方案:IFNULL(含null值表达式, null值的代替值)函数处理。 15 16 4.别名 17 AS关键字 18 */ 19 DROP TABLE IF EXISTS student; 20 CREATE TABLE student( 21 id INT PRIMARY KEY, -- 编号 22 name VARCHAR(20), -- 姓名 23 age INT, -- 年龄 24 sex VARCHAR(5), -- 性别 25 address VARCHAR(100), -- 地址 26 math INT, -- 数学 27 english INT -- 英语 28 ); 29 INSERT INTO student(id, name, age, sex, address, math, english) 30 VALUES 31 (1,'马云',55,'男','杭州',66,78), 32 (2,'马化腾',45,'女','深圳',98,87), 33 (3,'马景涛',55,'男','香港',56,77), 34 (4,'柳岩',20,'女','湖南',76,65), 35 (5,'柳青',20,'男','湖南',76,NULL), 36 (6,'刘德华',57,'男','香港',99,99), 37 (7,'马德',22,'女','香港',99,99), 38 (8,'德玛西亚',18,'男','南京',56,65); 39 40 -- 1.字段列表查询 41 SELECT name, math, english FROM student; 42 43 -- 简便方式,*表示全部字段,进行查询 44 SELECT * FROM student; 45 46 -- 规范写法,并且企业中规定禁止用SELECT *,效率低。 47 SELECT 48 name, -- 姓名 49 math, -- 数学 50 english -- 英语 51 FROM 52 student; -- 学生表 53 54 -- 2.去除重复行,DISTINCT修饰的是字段列表,不是单个字段。 55 SELECT DISTINCT address from student; 56 57 SELECT name, DISTINCT address from student;-- 错误语句 58 59 60 -- 3.计算字段 61 SELECT name, math, english, math + english FROM student;-- bug: NULL值,参与计算,得到的结果是NULL。不符合要求。 62 63 SELECT name, math, english, IFNULL(math, 0) + IFNULL(english, 0) FROM student;-- 调用函数,对NULL进行处理 64 65 -- 4.起别名 66 SELECT name, math, english, IFNULL(math, 0) + IFNULL(english, 0) AS total_score FROM student;-- 对计算字段起别名 67 68 -- 5.bug 69 DROP TABLE IF EXISTS student; 70 CREATE TABLE student( 71 id INT PRIMARY KEY, -- 编号 72 name VARCHAR(20), -- 姓名 73 age INT, -- 年龄 74 sex VARCHAR(5), -- 性别 75 address VARCHAR(100), -- 地址 76 math INT, -- 数学 77 english INT -- 英语 78 ); 79 INSERT INTO student(id,name,age,sex,address,math,english) VALUES (1,'马云',55,'男',' 80 杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩 81 ',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港 82 ',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65); 83 84 -- bug1: NULL值,参与计算,得到的结果是NULL。不符合要求。 85 SELECT name, math, english, math + english FROM student; 86 87 -- bug2: 在字符串中间换行,导致字符串中会额外插入一个换行符。 88 SELECT DISTINCT address from student;-- bug演示:发现有2个香港,没有去重,其实其中1个有个额外的换行符。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 基础条件查询: 3 全部的比较运算符参看MySQL手册第12章运算符和函数 4 1.关系运算符 5 > < >= <= = != 6 <> 7 2.逻辑运算符 8 AND OR NOT 9 && || ! 不推荐 10 3.便捷运算符 11 BETWEEN AND, IN 12 NOT BETWEEN AND, NOT IN 13 4.null值判断 14 IS NULL 15 IS NOT NULL 16 5.模糊查询 17 LIKE 18 NOT LIKE 19 %(匹配n个任意字符) 20 _(匹配1个任意字符) 21 */ 22 DROP TABLE IF EXISTS student; 23 CREATE TABLE student( 24 id INT PRIMARY KEY, -- 编号 25 NAME VARCHAR(20), -- 姓名 26 age INT, -- 年龄 27 sex VARCHAR(5), -- 性别 28 address VARCHAR(100), -- 地址 29 math INT, -- 数学 30 english INT -- 英语 31 ); 32 INSERT INTO student(id, NAME, age, sex, address, math, english) 33 VALUES 34 (1,'马云',55,'男','杭州',66,78), 35 (2,'马化腾',45,'女','深圳',98,87), 36 (3,'马景涛',55,'男','香港',56,77), 37 (4,'柳岩',20,'女','湖南',76,65), 38 (5,'柳青',20,'男','湖南',76,NULL), 39 (6,'刘德华',57,'男','香港',99,99), 40 (7,'马德',22,'女','香港',99,99), 41 (8,'德玛西亚',18,'男','南京',56,65); 42 43 SELECT * FROM student; 44 45 -- 查询年龄大于20岁 46 SELECT * FROM student WHERE age > 20; 47 -- 查询年龄大于等于20岁 48 SELECT * FROM student WHERE age >= 20; 49 50 -- 查询年龄等于20岁 51 SELECT * FROM student WHERE age = 20; 52 -- 查询年龄不等于20岁 53 SELECT * FROM student WHERE age != 20; 54 SELECT * FROM student WHERE age <> 20; 55 56 57 -- 查询年龄在20岁到30岁的人 58 SELECT * FROM student WHERE age >= 20 AND age <= 30; 59 SELECT * FROM student WHERE age BETWEEN 20 AND 30; 60 -- 查询年龄不在20岁到30岁的人 61 SELECT * FROM student WHERE age NOT BETWEEN 20 AND 30; 62 63 -- 查询年龄为18岁,20岁,22岁的人 64 SELECT * FROM student WHERE age = 18 OR age = 20 OR age = 22; 65 SELECT * FROM student WHERE age IN(18, 20, 22); 66 -- 查询年龄不为18岁,20岁,22岁的人 67 SELECT * FROM student WHERE age NOT IN(18, 20, 22); 68 69 70 -- 查询英语成绩为null的人,即没有参加英语考试的人 71 SELECT * FROM student WHERE english = NULL; -- 错误 不能用 = !=判断null值 72 SELECT * FROM student WHERE english IS NULL; 73 74 -- 查询英语成绩不为null的人,即参加了英语考试的人 75 SELECT * FROM student WHERE english IS NOT NULL; 76 77 78 -- 查询姓马的人 79 SELECT * FROM student WHERE NAME LIKE '马%'; 80 -- 查询不姓马的人 81 SELECT * FROM student WHERE NAME NOT LIKE '马%'; 82 83 -- 查询名字中含德的人 84 SELECT * FROM student WHERE NAME LIKE '%德%'; 85 86 -- 查询名字中第2个字是化的人 87 SELECT * FROM student WHERE NAME LIKE '_化%'; 88 -- 查询名字为3个字的人 89 SELECT * FROM student WHERE NAME LIKE '___';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 分组聚合排序分页查询: 3 WHERE和HAVING的区别? 4 1.where在分组前过滤数据,不满足条件的,不参与分组。having在分组后过滤数据。 5 2.where中不能使用聚合函数,having中可以使用聚合函数。 6 7 1.分组 8 1.语法: 9 [GROUP BY {col_name | expr | position}[ASC | DESC], ... [WITH ROLLUP]] 10 2.过滤分组: 11 [HAVING where_condition] 12 分组后过滤数据。可以使用聚合函数。 13 3.注意点: 14 1.分组后查询目标:分组字段,聚合函数, 15 2.提供了ASC和DESC,这是提供的ORDER BY的一种简便方式。 16 17 2.聚合 18 5个聚合函数,排除null值。不对null值进行统计。 19 1.count() 20 2.max() 21 3.min() 22 4.avg() 23 5.sum() 24 特殊的:count(*),统计结果集中记录数,包括null行。 25 26 3.排序 27 语法: 28 [ORDER BY {col_name | expr | position} [ASC | DESC], ...] 29 排序方式: 30 ASC 升序 默认的 31 DESC 降序 32 注意点: 33 1.如果排序中有null值,则MySQL是nullmin的,即null值最小。 34 2.多条件排序,多个排序条件有先后次序。 35 3.如果进行了分组,则是对分组后的结果集进行排序 36 37 4.分页 38 语法: 39 [LIMIT {[offset,] row_count | row_count OFFSET offset}] 40 offset: 起始行 41 row_count: 行数 42 公式: 43 起始行 = (当前页 - 1)* 页大小 44 行数 = 页大小 45 */ 46 DROP TABLE IF EXISTS student; 47 CREATE TABLE student( 48 id INT PRIMARY KEY, -- 编号 49 NAME VARCHAR(20), -- 姓名 50 age INT, -- 年龄 51 sex VARCHAR(5), -- 性别 52 address VARCHAR(100), -- 地址 53 math INT, -- 数学 54 english INT -- 英语 55 ); 56 INSERT INTO student(id, NAME, age, sex, address, math, english) 57 VALUES 58 (1,'马云',55,'男','杭州',66,78), 59 (2,'马化腾',45,'男','深圳',98,87), 60 (3,'马景涛',55,'男','香港',56,77), 61 (4,'柳岩',20,'女','湖南',76,65), 62 (5,'柳青',20,'男','湖南',76,NULL), 63 (6,'刘德华',57,'男','香港',99,99), 64 (7,'马德',22,'女','香港',99,99), 65 (8,'德玛西亚',18,'男','南京',56,65); 66 67 SELECT * FROM student; 68 69 -- 按男女分组,查询平均分 70 SELECT sex, AVG(math) FROM student GROUP BY sex; 71 -- 按男女分组,查询平均分和男生女生人数 72 SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex; 73 -- 按男女分组,查询平均分和男生女生人数。要求:分数低于70的不参与分组 74 SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex; 75 -- 按男女分组,查询平均分和男生女生人数。要求:分数低于70的不参与分组,并且分组人数大于2 76 SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; 77 78 79 80 -- 统计学生人数,用count(*) 81 SELECT count(*) FROM student; 82 83 SELECT 84 MAX(english) max_score, 85 MIN(english) min_score, 86 AVG(english) avg_score, 87 sum(english) sumAll, 88 count(english) num 89 FROM student; 90 91 92 -- 按照(总分 desc,英语 desc)排序 93 SELECT *, (IFNULL(math, 0) + IFNULL(english, 0)) AS total_score from student 94 ORDER BY total_score DESC, english DESC; 95 96 97 -- 页大小为3,数据库中总共有8条数据 98 SELECT * FROM student LIMIT 0, 3; -- 第1页 返回3条 99 SELECT * FROM student LIMIT 3, 3; -- 第2页 返回3条 100 SELECT * FROM student LIMIT 6, 3; -- 第3页 返回2条 101 SELECT * FROM student LIMIT 9, 3; -- 第4页 返回0条
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 约束分类: 4种约束的CRUD 3 注意点: 4 唯一 主键 外键 都可以约束多列。 5 4种约束,建表后,添加约束,如果表中有数据,则可能出bug,导致约束添加失败。 6 7 查询约束: 8 查询约束信息:SHOW CREATE TABLE 表名; 9 查询约束语法:help create talbe; help alter table 10 11 1.非空 12 NOT NULL 非空 13 建表时加非空,删除非空,建表后加非空 14 15 建表后添加非空约束: 16 bug: 如果表中有数据,并且指定列有null值,则非空约束添加失败。 17 18 2.唯一 19 UNIQUE 唯一,可以有多个null值 20 建表时加UNIQUE,删除UNIQUE,建表后加UNIQUE 21 22 建表后添加唯一约束: 23 bug: 如果表中有数据,并且指定的列有重复数据,则UNIQUE约束添加失败 24 25 注意点: 26 UNIQUE约束允许多个null值。 27 28 3.主键 29 PRIMARY KEY 唯一非空,一张表只能有一个主键约束 30 建表时加PRIMARY KEY,删除PRIMARY KEY,建表后加PRIMARY KEY 31 32 建表后添加主键约束: 33 bug: 如果表中有数据,并且指定列含有null值或者重复数据,则添加主键失败。 34 35 注意点: 36 1.PRIMARY KEY != UNIQUE + NOT NULL(因为主键只能有一个,而UNIQUE + NOT NULL可以多个) 37 2.删除PRIMARY KEY后还会有一个NOT NULL约束 38 39 4.外键 40 FOREIGN KEY 外键约束可以设置级联更新和级联删除 41 建表时加FOREIGN KEY,删除FOREIGN KEY,建表后加FOREIGN KEY 42 43 建表后添加外键约束: 44 bug: 如果从表有数据,并且外键列引用了被引用列没有的值,则添加外键约束失败 45 46 注意点: 47 1.添加外键约束,主表必须存在 48 2.表数据约束: 49 1.从表不能引用主表中不存在的值 50 2.主表不能删除被从表引用的记录 51 3.删表:从表引用主表,不能直接删除主表。 52 53 5.综合: 54 唯一 主键 外键 都可以约束多列。 55 4种约束,建表后,添加约束,如果表中有数据,则可能出bug,导致约束添加失败。 56 57 6.自动增长 58 AUTO_INCREMENT 59 1.一张表只能有一个自动增长列,该列必须是数值类型 60 2.自增的新值= 表数据最大值 + 1 61 7.默认值 62 DEFAULT 63 64 */ 65 DROP TABLE IF EXISTS student; 66 67 -- 建表时添加NOT NULL约束 68 CREATE TABLE student( 69 name VARCHAR(50) NOT NULL 70 ); 71 72 -- 删除NOT NULL约束 73 ALTER TABLE student MODIFY name VARCHAR(50); 74 75 -- 建表后添加NOT NULL约束 76 -- bug: 如果表中有数据,并且指定列有null值,则非空约束添加失败。 77 ALTER TABLE student MODIFY name VARCHAR(50) NOT NULL; 78 DELETE FROM student WHERE name IS NULL; 79 80 81 82 DROP TABLE IF EXISTS student; 83 84 -- 建表时,添加UNIQUE约束 85 CREATE TABLE student( 86 name VARCHAR(50) UNIQUE 87 ); 88 89 CREATE TABLE student( 90 name VARCHAR(50), 91 CONSTRAINT uni_name UNIQUE(name) 92 ); 93 94 -- 删除UNIQUE约束 95 ALTER TABLE student DROP INDEX name; 96 97 -- 建表后,添加UNIQUE约束 98 -- bug: 如果表中有数据,并且指定的列有重复数据,则UNIQUE约束添加失败 99 ALTER TABLE student MODIFY name VARCHAR(50) UNIQUE; 100 ALTER TABLE student ADD CONSTRAINT uni_name UNIQUE(name); 101 102 -- 查看表信息 103 SHOW CREATE TABLE student; 104 105 /* 106 PRIMARY KEY 主键约束 107 唯一非空,一张表只能有一个主键约束 108 注意点: 109 PRIMARY KEY != UNIQUE + NOT NULL(因为主键只能有一个,而UNIQUE + NOT NULL可以多个) 110 */ 111 DROP TABLE IF EXISTS student; 112 113 -- 建表时,添加PRIMARY KEY约束 114 CREATE TABLE student( 115 name VARCHAR(50) PRIMARY KEY 116 ); 117 118 CREATE TABLE student( 119 name VARCHAR(50), 120 PRIMARY KEY(name) 121 ); 122 123 -- 删除主键 124 ALTER TABLE student DROP PRIMARY KEY; -- 删除后还有一个NOT NULL约束 125 126 -- 创建完表后,添加主键 127 -- bug: 如果表中有数据,并且指定列含有null值或者重复数据,则添加主键失败。 128 ALTER TABLE student MODIFY name VARCHAR(50) PRIMARY KEY; 129 ALTER TABLE student ADD PRIMARY KEY (name); 130 131 -- 查看表:约束 132 SHOW CREATE TABLE student; 133 134 135 /* 136 外键约束: 137 注意点: 138 1.添加外键约束,主表必须存在 139 2.表数据约束: 140 1.从表不能引用主表中不存在的值 141 2.主表不能删除被从表引用的记录 142 3.删表:从表引用主表,不能直接删除主表。 143 */ 144 DROP TABLE IF EXISTS employee; 145 DROP TABLE IF EXISTS department; 146 147 CREATE TABLE department( 148 id INT PRIMARY KEY, 149 name varchar(50) UNIQUE NOT NULL 150 ); 151 -- 建表时,添加外键 152 CREATE TABLE employee( 153 id INT PRIMARY KEY, 154 name varchar(50) NOT NULL, 155 dep_id INT, 156 CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id) 157 ); 158 -- 删除外键 159 ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk; 160 161 -- 建表后,添加外键 162 -- bug: 如果从表有数据,并且外键列引用了被引用列没有的值,则添加外键约束失败 163 ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id); 164 165 SHOW CREATE TABLE employee;