MOOCMySQL

本文内容大多从 14 ALTER 命令修改表_MySQL 入门教程-慕课网转载

端口号概念

所谓的端口，就好像是门牌号一样，客户端可以通过 ip 地址找到对应的服务器端，但是服务器端是有很多端口的，每个应用程序对应一个端口号

第一设计范式

第一设计范式要求表中字段都是不可再分的，如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息

.第二设计范式

第二设计范式要求表中必须存在业务主键，并且全部非主键依赖于业务主键。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）

第三设计范式

即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息、

反范式化设计

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于 DML 的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

修改数据表名称

命令 : ALTER TABLE 旧的表名 RENAME TO 新的表名;
查看建表SQL语句

 命令 : SHOW CREATE table_name;
ALTER TABLE `new_student` 
MODIFY COLUMN `name` char(50) CHARACTER 
SET utf8 COLLATE utf8_general_ci 
NOT NULL DEFAULT '无名' AFTER `id`;
 

其中 “CHARACTER SET utf8 COLLATE utf8_general_ci” 为新字段类型的字符集和编排方式，默认值为 “无名”，"AFTER id" 表示跟在 id 字段后面。

新增表字段

 ALTER TABLE `new_student` 
ADD COLUMN `sex` tinyint(2) UNSIGNED NOT NULL DEFAULT 1 COMMENT '性别 : 1:男 2:女' AFTER `id_number`;
删除表字段

ALTER TABLE `new_student` 
DROP COLUMN `sex`;
修改表字段名称

ALTER TABLE `new_student` 
CHANGE COLUMN `name` `new_name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '无名' AFTER `id`;
 

修改表字段默认值

修改表字段默认值命令，以修改 new_name 字段默认值为例 :

ALTER TABLE `new_student` 
MODIFY COLUMN `new_name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '小花' AFTER `id`;

删除数据表

 DROP TABLE new_student;
向数据表中插入数据

 INSERT INTO teacher (name,age,id_number) VALUES ('秦小贤',18,'42011720200604088X');
查询所有数据

 SELECT * FROM teacher;
新增多条数据

INSERT INTO teacher
(name,age,id_number)
VALUES
('王小花',19,'42011720200604077X'),
('张晓丽',18,'42011720200604099X'),
('刘美丽',20,'42011720200604020X'),
('吴帅',21,'42011720200604022X'),
('张平',22,'42011720200604033X')

删除一条数据

DELETE FROM teacher WHERE id = 8;
删除所有数据

DELETE FROM teacher;
TRUNCATE TABLE new_student;
清空表命令

TRUNCATE TABLE new_student;
查询所有数据

SELECT * FEOM teacher;
查询指定条数的结果集

SELECT * FROM teacher LIMIT 10;
查询指定起始位置条数的结果集

SELECT * FROM teacher LIMIT 10,10;
查询指定字段列的结果集

SELECT name,age FROM teacher LIMIT 6,5;
更新某一列字段的值

 UPDATE teacher SET age = 33 LIMIT 3;
更新多列字段的值

UPDATE teacher SET  age=18,id_number='44444444440604099X' WHERE id = 30;
LIKE模糊查询

% 表示指代任意内容,例如 '%小%' 表示包含 小 的表达式,且 小 前后都有内容, '%小' 表示以 小 结尾的表达式,王 前面有内容,后面没有内容,'小%' 表示以 小 开头的表达式,小 前面没有内容,后面有内容。

查询出姓 王 的教师信息结果集:

SELECT * FROM teacher WHERE name LIKE '王%';

 但条件查询

SELECT * FROM teacher WHERE age > 18;
and多条件查询

SELECT * FROM teacher WHERE age > 18 AND name LIKE  '王%';
or多条件查询

SELECT * FROM teacher WHERE age > 25 OR name LIKE  '王%';
查询 email 为 NULL 的教师信息结果集

SELECT * FROM teacher WHERE email IS NULL;
UNOIN ALL联合查询

SELECT * FROM teacher WHERE age > 20
UNION ALL
SELECT * FROM teacher WHERE age > 25;

UNION 查询

SELECT * FROM teacher WHERE age > 20
UNION
SELECT * FROM teacher WHERE age > 25;

UNION 将两种查询结果并到一起，可以看到结果集中已经去掉重复的数据，需要根据具体业务选择使用 UNION 还是 UNION ALL。

order by排序

asc有小到大 SELECT * FROM teacher ORDER BY age ASC;

desc由大到小 SELECT * FROM teacher ORDER BY id DESC;

将查询出来的结果集按照 age 从大到小排序之后，再按照 id 字段从小到大排序：

SELECT * FROM teacher ORDER BY age DESC,id ASC;

对字符串类型字段排序

使用 ASC 对结果集按照 name 字段升序，其实是对 name 字段字符串编码的排序，英文字符排序在前，中文在后，其中排序规则是按照字符 ASCII码 对应值的大小排序的。

 内连接：join inner左右两表都有对应的值

多表混合连接查询

多表混合连接查询时，后面可以把前面执行的结果集整体当成一个表，例如 SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id RIGHT JOIN course c ON b.course_id=c.id 可以理解为 student 和 student_course 左连接查询之后的结果集再对 course 右连接查询。

GROUP BY 分组是对指定一个或多个字段分组，使用分组可以较好地对数据结果分析和处理。

多字段分组

SELECT c.teacher_id,a.course_id,c.course_name,d.name
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN course c 
ON a.course_id=c.id 
INNER JOIN teacher d
ON c.teacher_id=d.id 
GROUP BY c.teacher_id,a.course_id;
单字段去重

SELECT DISTINCT student_id 
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id;
多字段去重

SELECT 
DISTINCT  a.course_id,b.course_name,b.teacher_id,c.name
FROM 
student_course a 
INNER JOIN 
course b 
ON a.course_id=b.id 
INNER JOIN teacher c 
ON b.teacher_id=c.id;
聚合函数

AVG（）平均值函数

SELECT AVG(age) FROM teacher;
统计数量

SELECT COUNT(*) FROM student;
总和

SELECT SUM(age) FROM teacher;
可以使用 MIN() 函数对结果集取年龄最小值的数据

SELECT MIN(age) FROM teacher;
可以使用 MAX() 函数对结果集取年龄最大值的数据：

SELECT MAX(age) FROM student;
可以使用 HAVING 对上述结果筛选，例如选出选课学生平均年龄大于 20 的课程数据：

SELECT a.course_id,c.course_name,AVG(age) 
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN course c 
ON a.course_id=c.id
GROUP BY a.course_id,c.course_name
HAVING AVG(age) >= 20;
IF函数

SELECT name,IF(age > 17,'成年','未成年') AS age_group,id_number FROM student;
Tips：如上图所示， IF(age > 17,'成年'，'未成年') 表示若 age 字段满足 age > 17 则展示为 成年，否则展示为 未成年。