MYSQL基本语法操作

– 创建数据库
CREATE DATABASE book;

– 删除数据库
DROP DATABASE book;

– 创建表
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,– 编号
studentname VARCHAR(20),– 学生姓名
sex VARCHAR(20), – 性别
birthday DATE, – 生日
tel VARCHAR(20)– 电话号码

);

– 删除表
DROP TABLE t_student;

– 删除元素
DELETE FROM t_student WHERE studentname LIKE ‘李%’; – 删除姓李的
DELETE FROM t_student WHERE id=1; – 删除ID为1的
DELETE FROM t_student WHERE studentname=’王丽’; – 删除王丽
DELETE FROM t_student WHERE studentname LIKE ‘%李%’; – 删除所有名字带李的

– 查询表中所有数据 *表示查询所有的列
SELECT * FROM t_student;

– 查询所有的男生
SELECT * FROM t_student WHERE sex=’男’;

– 查询所有姓张的人
SELECT * FROM t_student WHERE studentname LIKE ‘张%’;

– 查询姓李的两个字的学生
SELECT * FROM t_student WHERE studentname LIKE ‘王_’;

– 查询电话号码以189开头的用户
SELECT * FROM t_student WHERE tel LIKE ‘189%’;

– 查询姓张的男生
SELECT * FROM t_student WHERE studentname LIKE ‘张%’ AND sex=’男’;

– 查询九零后的学生
SELECT * FROM t_student WHERE birthday>=’1990-01-01’ AND birthday<’2000-01-01’;

– 查询所有的移动用户
SELECT * FROM t_student WHERE tel LIKE ‘13%’ OR tel LIKE ‘15%’;

– 查询第三条到第五天记录
SELECT * FROM t_student LIMIT 3,3;
– 第一个表示从第几条开始，第一条是零，3表示从第四条开始显示；第二个参数表示显示几条

– 按年龄排序 asc升序 desc降序 默认是升序
SELECT * FROM t_student ORDER BY birthday DESC;

– 查询年龄最小的
SELECT * FROM t_student ORDER BY birthday DESC LIMIT 0,1;

– 查询没有手机的
SELECT * FROM t_student WHERE tel IS NULL;

– 查询有手机的
SELECT * FROM t_student WHERE tel IS NOT NULL;
SELECT * FROM t_student WHERE tel LIKE ‘%%’;

– 查询ID为3和5的学生
SELECT * FROM t_student WHERE id=3 OR id = 5;
SELECT * FROM t_student WHERE id IN (3,5);

– 查询所有学生的性别
SELECT DISTINCT sex FROM t_student; – DISTINCT 表示去除重复记录

– 添加记录
INSERT INTO t_student(studentname,sex,birthday,tel)
VALUES(‘张三’,’男’,’1980-09-20’,’14509873455’),
(‘李四’,’男’,’1985-08-06’,’13357467765’),
(‘王五’,’男’,’1987-03-04’,’17894556376’),
(‘陈元’,’男’,’1992-03-04’,’1982376544’),
(‘王艳’,’女’,’1989-08-07’,’15648766677’),
(‘王丽’,’女’,’1993-03-04’,’18376546654’),
(‘李晓燕’,’女’,’1994-05-04’,’186784532’),
(‘李元霸’,’男’,’524-05-04’,’186784532’);

INSERT INTO t_student(studentname,sex,birthday) VALUES(‘刘军’,’男’,’1972-01-9’),
(‘朱勇’,’女’,’1994-03-29’);

– 修改张三的电话号码
UPDATE t_student SET tel=’15398897646’WHERE studentname=’张三’;

– 同时修改张三的电话号码和生日
UPDATE t_student SET tel=’18976874456’,birthday=’1994-05-06’
WHERE studentname=’张三’;

– 将T120班所有学生的成绩增加5分
UPDATE t_student SET s_grade =s_grade+5 WHERE s_class = ‘T120’;

– 显示所有的学生成绩，并且追加评价
SELECT g.*,(CASE WHEN s_grade >= 90 THEN ‘优’
WHEN s_grade >= 80 AND s_grade <= 90 THEN ‘良’
WHEN s_grade >= 60 AND s_grade <= 80 THEN ‘中’
WHEN s_grade < 60 THEN ‘差’
END
) ‘评价’ FROM t_student g;

– 显示两列评价
SELECT g.* ,(CASE WHEN s_grade >=90 THEN ‘优’

WHEN s_grade >=80 AND s_grade < 90 THEN '良'WHEN s_grade >=60 AND s_grade < 80 THEN '中'WHEN s_grade IS NULL THEN '缺考'ELSE '差' END) '评价' ,(CASE WHEN s_birthday >='1990-01-01' THEN '90后'ELSE '非90后'END ) '年龄评价' FROM t_student g;

– 给指定班添加成绩，其他班成绩不变
UPDATE t_student SET s_grade = (CASE WHEN s_class=’T121’ THEN s_grade+1
WHEN s_class = ‘T120’ THEN s_grade+2
ELSE s_grade
END
);

– 使用delete 删除
– 不加WHERE条件的DELETE语句就能删除所有数据

– 使用TRUNCATE
– 直接删除表中的所有数据 标准结构

– TRUNCATE 表名
– 二者的区别
– delete 会记录日志，删除后可以恢复数据，但是效率低
– TRUNCATE 不会记录日志，删除后无法恢复，但是效率高

– 聚合函数的分类 ：用于对一组数据进行统计分析

– COUNT 统计行数量
– SUM 获取单个列的合计值
– AVG 计算某个列的平均值
– MAX 计算列的最大值
– MIN 计算列的最小值

– count(*)表示统计所有的记录，count(s_grade)表示统计非空记录
SELECT COUNT(*)’学生总人数’,COUNT(s_grade)’参考人数’ FROM t_student;
– 统计指定班的总人数
SELECT COUNT(*)’学生总人数’ FROM t_student WHERE s_class = ‘T120’;

– 统计所有学生的总分
SELECT SUM(s_grade) ‘总分’ FROM t_student;

– 统计所有学生的平均分,avg 只计算非空列
SELECT AVG(s_grade) FROM t_student;
SELECT AVG(s_grade),SUM(s_grade)/COUNT(*) FROM t_student;

– 计算最高分和最低分
SELECT MAX(s_grade) ‘最高分’,MIN(s_grade) ‘最低分’ FROM t_student;

– 统计每个班的学生人数
SELECT s_class,COUNT(*) ‘学生总人数’ FROM t_student GROUP BY s_class;

– 统计每个班的最高分和最低分 GROUP BY 按照s_class分组
SELECT s_class ‘班级’,MAX(s_grade)’最高分’,MIN(s_grade) ‘最低分’ FROM t_student GROUP BY s_class;

– 统计每个班80分以上的人数，60-80的人数，不及格人数和缺考人数
SELECT s_class ‘班级’,COUNT(CASE WHEN s_grade>=80 THEN 1 END )’80分以上人数’,
COUNT(CASE WHEN s_grade>=60 AND s_grade<=80 THEN 1 END )’60-80人数’,
COUNT(CASE WHEN s_grade<60 THEN 1 END )’不及格人数’,
COUNT(CASE WHEN s_grade IS NULL THEN 1 END )’缺考人数’
FROM t_student GROUP BY s_class;

– 数据分组
– HAVING 和 WHERE的区别
– 二者都是过滤条件，WHERE 运行在分组前，因此不能执行任何聚合函数。HAVING是运行在分组后，只能用做聚合函数的过滤。

– 找出人数大于4个的班级
SELECT s_class’班级’,COUNT() FROM t_student GROUP BY s_class HAVING COUNT()>=4;

– SQL执行顺序
– 执行FROM
– WHERE条件过滤
– GROUP BY 分组(聚合函数只有分组之后才能用)
– 执行SELECT 投影列
– HAVING 条件过滤
– 执行ORDER BY 排序