数据库笔记整理（一）

（Ⅰ）数据库及表的创建

1. 数据库的创建

create database Teacher -- 创建一个名为Teaacher的数据库

2. 表的创建

CREATE TABLE Teacher(
id INT(11) NOT NULL  auto_increment COMMENT '主键' PRIMARY KEY , 
userid INT(11) NOT NULL  COMMENT '用户ID' ,
age INT(3)  COMMENT '年龄',
stu_name VARCHAR(50) COMMENT '名称',
createTime datetime(0) DEFAULT  CURRENT_TIMESTAMP(0)  COMMENT '创建时间',
updateTime datetime(0)  ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
FOREIGN KEY(userid)	references  `info`(userid)
)

在上述代码中，NOT NULL 表示该字段的值不能为空； auto_increment
表示该字段的值自增，这里需要注意的是：每个表只能有一个字段的值是自增的；COMMENT表示该字段的描述；PRIMARY KEY表示设置该字段为主键，这里还可以这样设置主键：PRIMARY KEY(id)；FOREIGN KEY表示设置该字段为外键，这需连接另一张表的字段

3. 表的字段常用命令

alter table `user`   add  card_Id varchar(255) NULL COMMENT '身份证' ;  -- 添加字段
alter table `user`   add  primary key(id);  -- 添加主键
alter table `user`   ALTER	age SET DEFAULT 11;  -- 修改默认值
alter table `user`   CHANGE  card_Id  cardId varchar(255) NULL COMMENT '身份证' ; -- 更新列名
alter table `user`  modify id INT(11)  auto_increment COMMENT '主键';    -- 修改列的约束	
alter table `user`  drop cardId;  -- 删除某一列  
alter table `user` rename user2  -- 重命名
alter table `user2`   add pay4 int(11) AFTER age;  -- 在某个值后面添加
alter table `user2`   add pay5 int(11) FIRST;  -- 添加到第一个

4. 数据的添加

-- 添加英语系到学院表
INSERT INTO dept ( `name` ) VALUES ( '英语系' );-- 添加完整的一条记录
-- 添加多条记录
INSERT INTO teacher ( `name`, gender, age, e_mail, dept_id, phone ) VALUES ( '周玲', '男', 20, '3334@qq.com', 5, '18726598630' );

INSERT INTO teacher ( `name`, gender, age, e_mail, dept_id, phone )
VALUES
	( '周玲', '女', 23, '8752@qq.com', 4, '13168647943' ),
	( '吴达', '男', 22, '3674@qq.com', 4, '15626598630' ),
	( '吴孟', '男', 20, '0824@qq.com', 2, '13626598630' );
	
-- 添加时间
INSERT INTO teacher ( `name`, gender, age, e_mail, dept_id, phone, createtime )
VALUES
	( '王大海', '男', 20, '6554379647@qq.com', 3, '18726598630', '2021-01-15 10:20:02' );

-- 删除某个数据（多条件、单条件）
DELETE  FROM teacher  WHERE age = 20;	-- 单条件删除

DELETE  FROM teacher WHERE age = 20  AND gender = '女';	-- 多条件删除

在sql语法中，用；来分割语句，如果不加；，则可能会将它错误的判断为一个语句，从而试运行时报错

5. 数据的更新

在更新我们想要更新的数据之前，我们需要先查询数据；数据的更新可以用关键字set来更新；下面演示一下简单的数据更新

-- 将名字里面有“辉”的人的电话号码改为123456789
UPDATE teacher  SET phone = '123456789'  WHERE `name` LIKE '%辉%';
-- 将名字第一个字有“辉”的人的电话号码改为123456789
UPDATE teacher  SET phone = '123456789'  WHERE `name` LIKE '辉%';
-- 名字最后一个字有“辉”的人的电话号码改为123456789
UPDATE teacher  SET phone = '123456789'  WHERE `name` LIKE '%辉';
-- 名字第二个为辉的人的电话号码改为123456789
UPDATE teacher  SET phone = '123456789'  WHERE `name` LIKE '_辉%';
-- 修改多条记录，多条件修改
UPDATE teacher  SET phone = '987654321', e_mail = '445789@qq.com'  WHERE age = 20  AND gender = '男' ;

数据的查询分为模糊查找和精准查找；模糊查找有 LIKE 和 NOT LIKE 两种，精准查找则用“=”；%代表0个或多个字符 ， _代表一个字符；修改多个字段中间用，隔开，条件则用 and 或者 or 连接

（Ⅱ）数据的查询

数据的查询分为单表查询和多表查询两种，单表查询和多表查询都有单条件查询和多条件查询，多表查询还有连接查询

1. 单表查询

SELECT*FROM stu;-- 查询学生所有信息
SELECT `name`,age FROM stu;-- 查询学生姓名年龄
SELECT s.`name`,s.age FROM stu AS s;-- 查询学生姓名年龄并创建表别名
SELECT s.`name`,s.age FROM stu s WHERE age<=20;-- 查询年龄小于20岁的学生
SELECT s.`name`,s.age FROM stu s WHERE age BETWEEN 10 AND 30;-- BETWEEN AND 在某个范围
SELECT s.`name`,s.age FROM stu s WHERE age NOT BETWEEN 10 AND 30;-- NOT BETWEEN AND 不在某个范围
SELECT s.`name`,s.age FROM stu s WHERE `name` LIKE '%辉%'-- 找出所有名字里面带辉的
SELECT s.`name`,s.age FROM stu s WHERE `name` NOT LIKE '%辉%'-- 找出所有名字里面不带辉的
SELECT s.`name`,s.age FROM stu s WHERE dept_id IS NOT NULL;-- 系别不为空
SELECT s.`name`,s.age FROM stu s WHERE dept_id IS NULL;-- 系别为空
SELECT s.`name`,s.age FROM stu s WHERE dept_id IN (1,2)-- 查询在部门1,2中的学生
SELECT s.`name`,s.age FROM stu s WHERE dept_id NOT IN (1,2)-- 查询不在部门1,2中的学生
SELECT s.`name`,s.age,s.dept_id FROM stu s WHERE s.`name` LIKE '%王%' AND age=20;-- 找出名字里面有王的 并且 年龄为20 岁
SELECT s.`name`,s.age,s.dept_id FROM stu s WHERE s.`name` LIKE '%王%' OR age=20;-- 找出名字里面有王的 或者 年龄为20 岁
SELECT s.`name`,s.age,s.dept_id FROM stu s WHERE s.`name` LIKE '%王%' OR (age=19 OR age=20);-- 找出名字里面有王的 或者 （年龄为19 或者 20）
SELECT s.`name`,s.age,s.dept_id FROM stu s WHERE s.`name` LIKE '%王%' AND age IN (19,20);-- 找出名字里面有王的 并且 年龄为19 或者 20
SELECT s.`name`,s.age,s.dept_id FROM stu s WHERE s.`name` LIKE '%王%' AND (age=19 OR age=20);-- 找出名字里面有王的 并且 （年龄为19 或者 20）
SELECT s.`name`,s.age FROM stu s ORDER BY s.age ASC;-- 年龄正序排序（默认自带的排序）
SELECT s.`name`,s.age FROM stu s ORDER BY s.age DESC;-- 年龄倒序排序
SELECT s.`name`,s.age,s.dept_id FROM stu s ORDER BY s.age DESC,s,dept_id DESC;-- 年龄正序排序，ID倒序排序
SELECT s.`name`,s.age,s.dept_id FROM stu s LIMIT 2;-- 第一条开始筛选出链条
SELECT s.`name`,s.age,s.dept_id FROM stu s LIMIT 2,8;-- 第一个数字代表了标的下表，第二个数字代表提取的行数

上面的几个简单的例子列举了单表查询的常用查询，其中包括模糊查询、范围查询、条件查询、多条件查询，查询结果的排序以及筛选等
“---------------------------------------------------------------------------------------------------------”
下面介绍几个常用的聚合函数：
– 主要的聚合函数：
– 记数函数： count(列名) 计算元素的个数
– 求和函数： sum(列名) 对某一列的值求和，但属性必须是整型
– 计算平均值：avg(列名)对某一列的值计算平均值
– 求最大值： max(列名) 找出某一列的最大值
– 求最小值： min(列名) 找出某一列的最小值

SELECT COUNT(1) AS num FROM stu-- 统计学生数量,并且重命名列
SELECT COUNT(1) AS num,gender FROM stu s GROUP BY s.gender;-- 统计男女学生数量
SELECT count(s.dept_id) AS num,s.gender FROM stu s GROUP BY s.gender-- 统计男女学生数量，排除 dept_id 为null 的数
SELECT count(1) AS num,s.gender FROM stu s GROUP BY s.gender-- 统计男女学生数量
SELECT avg(s.age) avgAge FROM stu s;-- 统计说有学生的年龄
SELECT avg(s.age) avgAge,s.gender FROM stu s GROUP BY s.gender-- 统计男女学生平均年龄
SELECT sum(s.age) sumAge FROM stu s;-- 所有学生年龄和
SELECT sum(s.age) sumAge,s.gender FROM stu s GROUP BY s.gender-- 统计男女学生年龄之和
SELECT max(s.age) maxAge FROM stu s;-- 所有学生最大年龄
SELECT max(s.age) maxAge,s.gender FROM stu s GROUP BY s.gender;-- 统计男女学生年龄之最
SELECT max(s.age) maxAge,s.gender,s.class_id FROM stu s GROUP BY s.gender,s.class_id-- 统计出每个班级男女生年龄之最
SELECT AVG(s.age) AS avgage,s.gender FROM stu AS s WHERE s.age> 20 GROUP BY s.gender;-- 查询年龄大于18岁的人的平均年龄
SELECT AVG(s.age) AS avgage,s.gender FROM stu AS s GROUP BY s.gender HAVING s.gender='女';-- 查询性别为女的平均年龄

2. 多表查询

多表之间的连接方式有多种，比如内连接、左连接、右连接、自连接，下面将依次介绍常用的几种多表之间的连接

-- 内连接
-- 内连接要求关联的两张表关系的字段都必须存在，只展示关联的数据
SELECT*FROM stu s INNER JOIN class c ON s.class_id=c.id;
-- 内连接的条件查询
SELECT*FROM stu s INNER JOIN class c ON s.class_id=c.id WHERE s.age=20 AND c.id=1;-- 查询年龄为20岁且班级为1班的学生
-- 自连接
SELECT*FROM stu s,class c WHERE s.class_id=c.id;

-- 左连接
-- 左连接显示左边的所有数据，右边的数据不存在则置空(join的左右边)
SELECT*FROM stu s LEFT JOIN class c ON s.class_id=c.id;

-- 右连接
-- 右连接显示右边的所有数据，左边的数据不存在则置空
SELECT*FROM stu s RIGHT JOIN class c ON s.class_id=c.id;

表之间的连接可能不只是两张表之间的连接，还可能是三张表或者更多长膘之间的连接，下面展示简单的三表连接和子查询

-- 三表连接查询
-- 查询学生班主任信息
SELECT s.`name`,c.`name`,t.`name` FROM stu s LEFT JOIN class c ON s.class_id=c.id LEFT JOIN teacher t ON c.teacher_id=t.id;

-- 查询年龄大于20的学生班主任信息
SELECT s.`name`,c.`name`,t.`name` FROM stu s LEFT JOIN class c ON s.class_id=c.id LEFT JOIN teacher t ON c.teacher_id=t.id WHERE s.age> 20;

-- 查询数学系一班的所有学生
SELECT*FROM stu s WHERE s.class_id IN (
SELECT c.ID FROM class c WHERE c.`name`='数学系一班');

-- 查询出所有年龄大于18并且班级为数学系一班人员名称（这边使用子查询）
SELECT*FROM stu s WHERE s.class_id IN (SELECT c.ID FROM class c WHERE c.`name`='数学系一班') AND s.age> 19;

-- 查询出所有班主任为‘李海生′的学生信息（这边同样适用子查询）
SELECT*FROM stu WHERE stu.class_id IN (
SELECT class.id FROM class INNER JOIN teacher ON class.teacher_id=teacher.id WHERE teacher.`name`='李海生');

SELECT*FROM stu INNER JOIN class ON stu.class_id=class.id INNER JOIN teacher ON class.teacher_id=teacher.id WHERE teacher.`name`='李海生';-- 连接查询

“---------------------------------------------------------------------------------------”