Mysql代码练习--增删改查

# ------------ DDL-创建数据表 -------------
# PRIMARY KEY - 主键
# auto_increment - 自动增长
CREATE TABLE student(
id INT(3) PRIMARY KEY auto_increment,
name VARCHAR(32),
sex VARCHAR(32),
age INT(3),
salary FLOAT(8,2),
course VARCHAR(32)
)

# ------------ 添加数据 -------------

# 添加数据方式1：
INSERT INTO student VALUES(1,"叶涛","男",22,12000,"Java");

# 添加数据方式2：
INSERT INTO student(name,sex,age,salary,course) VALUES("李鑫","男",25,15000,"Java");
INSERT INTO student(name,sex,age,salary,course) VALUES("赵招","女",18,15000,"Java");
INSERT INTO student(name,sex,age,salary,course) VALUES("刘德华","男",64,8000,"Java");
INSERT INTO student(name,sex,age,salary,course) VALUES("周华健","男",60,6000,"HTML");
INSERT INTO student(name,sex,age,salary,course) VALUES("华晨宇","男",32,12000,"HTML");
INSERT INTO student(name,sex,age,salary,course) VALUES("霍建华","男",45,8000,"Python");
INSERT INTO student(name,sex,age,salary,course) VALUES("任达华","男",50,9000,"Python");
INSERT INTO student(name,sex,age,salary,course) VALUES("欧阳震华","男",48,10000,"Python");

# ------------ 修改数据 -------------
# 把指定字段上所有数据都修改
UPDATE student SET salary=10000;

# 依据条件修改数据
UPDATE student SET salary=11000 WHERE id<4;

# 依据多条件修改数据
UPDATE student SET salary=15000 WHERE id>3 AND id<7;# AND - 与
UPDATE student SET salary=18000 WHERE id>6 or sex="女";# OR - 或

# 修改多个数据
UPDATE student SET age=46,course="HTML" WHERE id=7;

# ------------ 删除数据 -------------
# 清空表中所有的数据
TRUNCATE TABLE student;# 清空表后，再添加数据时，id从1开始
DELETE FROM student;# 清空表后，再添加数据时，id从上次末尾开始
# 注意1：推荐使用DELETE FROM student;去清空表，因为把之前删除的数据恢复时，id不冲突
# 注意2：一般情况下数据不会使用DELETE去删除，删除数据不会真正删除，可以利用一个字段变量去说明该数据行的数据是否可用

# ------------ 查询数据 -------------
# 查询所有字段
SELECT * FROM student;

# 查询指定字段
SELECT name,sex FROM student;

# 查询指定字段 + 别名
SELECT name AS "姓名",sex AS "性别" FROM student;

# 去重
SELECT DISTINCT name AS "姓名",sex AS "性别" FROM student;

# 依据条件查询
SELECT * FROM student WHERE id = 2;
SELECT * FROM student WHERE id > 3 AND id<6;
SELECT * FROM student WHERE id > 6 OR sex="女";

# 模糊查询 - 需求：查询出姓名中带'华'的数据
# %表示没有或多个字符
SELECT * FROM student WHERE name LIKE "%华%";

# 模糊查询 - 需求：查询出姓名中最后带'华'的数据
# %表示没有或多个字符
SELECT * FROM student WHERE name LIKE "%华";

# 模糊查询 - 需求：查询出姓名中最后带'华'的数据，名字是四个字
# _表示一个字符
SELECT * FROM student WHERE name LIKE "___华";

# 排序查询 - 需求：按照年龄排升序
# ASC表示升序
SELECT * FROM student ORDER BY age ASC;

# 排序查询 - 需求：按照工资排降序
# ASC表示升序
# DESC表示降序
SELECT * FROM student ORDER BY salary DESC;

# 多字段排序 - 需求：按照年龄排升序，年龄相同按照工资排降序
SELECT * FROM student ORDER BY age ASC,salary DESC;

# 限制查找
SELECT * FROM student LIMIT 3;

# 限制查找 - 需求：查询出年龄最大的前三的学生信息
SELECT * FROM student ORDER BY age DESC LIMIT 3;

# 分页查找
# 公式：SELECT * FROM student LIMIT (页数-1)*数据条数,数据条数;
# SELECT * FROM student LIMIT 偏移量,获取数据条数;
SELECT * FROM student LIMIT 0,4;# 第一页
SELECT * FROM student LIMIT 4,4;# 第二页
SELECT * FROM student LIMIT 8,4;# 第三页

# 聚合查询 - 利用MySQL给我们提供的函数去查询数据
# 需求：查询工资的总和
SELECT SUM(salary) FROM student;
# 需求：查询最高的工资
SELECT MAX(salary) FROM student;
# 需求：查询最低的工资
SELECT MIN(salary) FROM student;
# 需求：查询平均工资
SELECT AVG(salary) FROM student;
# 需求：查询平均工资 + 保留两位小数
SELECT ROUND(AVG(salary),2) FROM student;
# 需求：查询学生表数据个数/条数
# 注意：统计个数建议使用主键，如果使用其他字段，字段中的数据为null的话则不计入最后的结果
SELECT COUNT(id) FROM student;

# 子查询 - 需求：查询出工资最高的学生信息
SELECT * FROM student WHERE salary=(SELECT MAX(salary) FROM student);

# 分组过滤
# 需求：查询出各个学科学生的平均工资
SELECT course,AVG(salary) FROM student GROUP BY course;

# 分组过滤 + 条件
# 需求：查询出各个学科学生平均工资并且平均工资大于10000
SELECT course,AVG(salary) FROM student GROUP BY course HAVING AVG(salary) > 10000;