数据库的增删改查

-- 创建数据库
CREATE DATABASE IF NOT EXISTS exercise DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
-- 使用数据库
USE exercise;
-- 创建数据表
CREATE TABLE IF NOT EXISTS stu_course(
    `number` INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '课程编号',
    name VARCHAR(20) NOT NULL COMMENT '课程名称',
    score DOUBLE(5,2) NOT NULL COMMENT '学分'
) ENGINE=InnoDB CHARSET=UTF8 COMMENT='课程表';
-- 修改数据表
ALTER TABLE stu_course RENAME AS course;
-- 增加字段
ALTER TABLE course ADD `time` INT(3) NOT NULL COMMENT '学时';
-- 修改字段
ALTER TABLE course MODIFY score DOUBLE(3,1) NOT NULL COMMENT '学分';


DML语句（Data Mainpulation Language）:数据操作语言，对数据操作，而非数据表

INSERT语句
-- 需要注意，VALUES后的字段值必须与表名后的字段名一一对应
INSERT INTO 表名(字段名1,字段名2.....字段名n) VALUES (字段值1,字段值2.....字段值n);
INSERT INTO course(`number`,name,score,`time`) VALUES (1,'MySQL基础',4,40);
-- 需要注意，VALUES后的字段值必须与创建表时的字段顺序一一对应
INSERT INTO 表名 VALUES (字段值1,字段值2.....字段值n);
INSERT INTO course VALUES (2,'c++基础',3,20);
-- 一次性插入多条数据
INSERT INTO 表名(字段名1,字段名2.....字段名n) VALUES (字段值1,字段值2.....字段值n),...,(字段值1,字段值2.....字段值n);
INSERT INTO 表名 VALUES (字段值1,字段值2.....字段值n),...,(字段值1,字段值2.....字段值n);

UPDATE语句
    1.WHERE条件子句
    AND <----> &&
    OR  <----> ||
    WHERE time > 20 AND time < 40;
    2.UPDATE语句
    -- 将MySQL基础学分改为3，学时改为30
    UPDATE course SET score=3,`time`=30 WHERE name='MySQL基础'; 
DELETE语句
--
DELETE FROM 表名 [WHERE 删除条件]
-- 删除课程表中编号为一的数据
DELETE FROM course WHERE `number`=1; 

TRUNCATE语句
-- 清空表中数据
TRUNCATE [TABLE] course;

TRUNCATE语句和DELETE语句区别
    1.DELETE语句根据条件删除表中数据,而TRUNCATE语句则是清空表中数据,如果DELETE语句要删除表中所有数据,那么在效率上要低于TRUNCATE语句
    2.如果表中有自增长列,TRUNCATE语句会重置自增长计数器,但DELETE语句不会
    3.TRUNCATE语句执行后,数据无法恢复,而DELETE语句执行后,可以使用事务回滚进行恢复
-- 当列为自增长列或者有默认值时，可以不给该列赋值
INSERT INTO course(name,score,`time`) VALUES ('MySQL提高',4,40);




-- 创建数据库
CREATE DATABASE IF NOT EXISTS exercise DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
-- 使用数据库
USE exercise;
-- 创建数据表
CREATE TABLE IF NOT EXISTS stu_course(
    `number` INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '课程编号',
    name VARCHAR(20) NOT NULL COMMENT '课程名称',
    score DOUBLE(5,2) NOT NULL COMMENT '学分'
) ENGINE=InnoDB CHARSET=UTF8 COMMENT='课程表';
-- 修改数据表
ALTER TABLE stu_course RENAME AS course;
-- 增加字段
ALTER TABLE course ADD `time` INT(3) NOT NULL COMMENT '学时';
-- 修改字段
ALTER TABLE course MODIFY score DOUBLE(3,1) NOT NULL COMMENT '学分';


DQL语句（Data Query Language）:数据查询语言

SELECT语句
SELECT ALL/DISTINCT * | 字段名1 AS 别名1[字段名1 AS 别名1,...,字段名n AS 别名n] FROM 表名 WHERE 查询条件

ALL表示查询所有满足条件的记录，可以省略;DISTINCT表示去掉查询结果中重复的记录;
AS可以给数据列，数据表起一个别名(AS可以省略)
-- 从课程表中查询课程编号小于2的课程名称
SELECT name FROM course WHERE `number`<2;
-- 从课程表中查询课程名称为"c++基础"的学分和学时
SELECT score,`time` FROM course WHERE name='c++基础';
-- 列的别名
SELECT score AS '学分',`time` AS '学时' FROM course WHERE name='c++基础';
-- 表的别名
SELECT c.name '课程名称',c.score '学分',c.time '学时' FROM course c WHERE c.name='c++基础';

比较操作符
IS NULL  字段名 IS NULL  如果字段值为NULL,则条件满足
IS NOT NULL 字段名 IS NOT NULL 如果字段值不为NULL,则条件满足
BETWEEN...AND  字段名 BETWEEN 最小值 AND 最大值 如果字段值在最大值和最小值之间,则条件满足
LIKE 字段名 LIKE '%匹配内容%' 如果字段值包含匹配内容,则条件满足
IN 字段名 IN(值1,值2,...,值n) 如果字段值在值1,值2,...,值n中,则条件满足

SELECT * FROM course WHERE name IS NOT NULL;

SELECT * FROM course WHERE score BETWEEN 2 AND 3;
-- 查询课程名包含SQL的课程
SELECT * FROM course WHERE name LIKE '%SQL%';
-- 查询课程名以SQL结尾的课程
SELECT * FROM course WHERE name LIKE '%SQL';
-- 查询课程名以SQL开始的课程
SELECT * FROM course WHERE name LIKE 'SQL%';
-- 查询课程名只有七个字符的课程
SELECT * FROM course WHERE name LIKE '_______';
-- 查询课程名以M开始且只有七个字符的课程
SELECT * FROM course WHERE name LIKE 'M______';
-- 从课程表查询课程编号为2，3的课程信息
SELECT * FROM course WHERE `number` IN(2,3);

-- 新建一个学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id BIGINT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号，主键',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
    age INT(3) DEFAULT 0 COMMENT '年龄',
    score DOUBLE(5,2) COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表'; 
-- 插入测试数据
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '张三', '男', 20,59);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '李四', '女', 19,62);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '王五', '其他',21, 62);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '龙华', '男', 22,75);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '金凤', '女', 18,80);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '张华', '其他',27, 88);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '李刚', '男', 30,88);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '潘玉明', '女',28, 81);
INSERT INTO student(id, name, sex, age, score) VALUES (DEFAULT, '凤飞飞', '其他',32, 90);

分组查询
SELECT ALL/DISTINCT * | 字段名1 AS 别名1[字段名1 AS 别名1,...,字段名n AS 别名n] FROM 表名 
WHERE 查询条件 GROUP BY 字段名1,...,字段名n;
-- 从学生表查询成绩在80分以上的学生信息并按性别分组
SELECT * FROM student WHERE score>80 GROUP BY sex;
-- 注意:分组查询所得的结果只是该组中的第一条数据
-- 从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组
SELECT * FROM student WHERE score BETWEEN 60 AND 80 GROUP BY sex,age;

聚合函数
1.COUNT():统计满足条件的数据总条数
-- 从学生表查询成绩在80分以上的学生人数
SELECT COUNT(*) FROM student WHERE score>80;
-- 给COUNT(*)取一个别名
SELECT COUNT(*) tatal FROM student WHERE score>80;
2.SUM():只能用于数值类型的字段或者表达式，计算该满足条件的字段值的总和
-- 从学生表查询不及格的学生人数和总成绩
SELECT COUNT(*) tatalCount,SUM(score) tatalScore FROM student WHERE score<60;
3.AVG():只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值
-- 从学生表查询男生、女生、其他类型的学生的平均成绩
SELECT sex,AVG(score) avgScore FROM student GROUP BY sex;
4.MAX():只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值
-- 从学生表查询学生的最大年龄
SELECT MAX(age) FROM student;
5.MIN():只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值
-- 从学生表查询学生的最低分
SELECT MIN(score) FROM student;

分组查询结果筛选
SELECT ALL/DISTINCT * | 字段名1 AS 别名1[字段名1 AS 别名1,...,字段名n AS 别名n] FROM 表名 
WHERE 查询条件 GROUP BY 字段名1,...,字段名n HAVING 筛选条件;
-- 从学生表查询年龄在20~30之间的学生信息并按性别分组，找出组内平均分在74分以上的组
SELECT * FROM student WHERE age BETWEEN 20 AND 30 GROUP BY SEX HAVING AVG(score)>74;
-- 注：分组后如果还需要满足其他条件，则需要使用HAVING实现

排序
SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 ORDER BY 字段名1 ASC|DESC,字段名2 ASC|DESC,..., 字段名n
ASC|DESC
-- 注：ASC为升序，DESC为降序
-- 从学生表查询年龄在18~30岁之间的学生信息并按成绩从高到低排列，如果成绩相同，则按年龄从小到大排列
SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC,age ASC;
-- 注:ORDER BY 必须位于 WHERE 条件之后 

分页
SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数;
-- 从学生表分页查询成绩及格的学生信息，每页显示3条，查询第2页学生信息
SELECT * FROM student WHERE score>=60 LIMIT 3,3;
-- LIMIT的第一个参数表示偏移量，也就是跳过的行数
-- LIMIT的第二个参数表示查询返回最大行数，可能没有给定数量那么多行
-- 注：如果一个查询中包含分组、排序和分页，那么它们之间必须按照分组->排序->分页的先后顺序排列。