MySQL常用SQL语句1

-- 创建表
CREATE TABLE USER ( -- id默认每个表都该拥有（非空 不重复）
-- （id是每一行的唯一标识）
-- 其他字段可能会重复，我们无法依赖其他的字段去确定某一行记录
id INT PRIMARY KEY auto_increment, 
NAME VARCHAR (20) NOT NULL, 
age INT, birthday DATE) 


-- 新增 -- 传值非数字都要加' '
INSERT INTO USER VALUES ( NULL, '小明', 30, '1995-12-13' );
INSERT INTO USER (NAME, age) VALUES ('花花', 99);
-- INSERT INTO 表名 VALUES 


-- 修改(一定要加条件，不然就是灾难)
UPDATE USER SET NAME = '爱因斯坦';-- 表中name都为爱因斯坦
UPDATE USER SET NAME = '霍金' WHERE id = 1;
UPDATE USER SET age = age + 10 WHERE id = 1;-- 支持表达式操作
-- UPDATE USER SET 列名 = 值 WHERE 条件表达式


-- 删除(生产中一定要加条件)
DELETE FROM USER;-- 灾难 删除表中所有数据
DELETE FROM USER WHERE id=2;
-- DELETE FROM 表名 WHERE 条件表达式

查询

-- 查询
SELECT * FROM USER;-- * 代表所有

SELECT NAME ,age FROM USER;-- 列名查询

SELECT age+10 FROM user;-- 表达式

SELECT NOW() FROM USER;-- 使用内置函数

SELECT NOW() FROM DUAL; -- DUAL它是个虚表，为了保证语法完整性

SELECT NOW() AS '现在时间' FROM DUAL;-- 别名(As可省略)

-- WHERE子句
SELECT * FROM USER WHERE id>4;

SELECT * FROM USER WHERE 0;

SELECT * FROM USER WHERE id!=3;

SELECT * FROM USER WHERE id>4 AND id<6;

SELECT * FROM USER WHERE id<4 OR id>5;

SELECT * FROM USER WHERE !(id<4);

SELECT *FROM USER WHERE `name` LIKE '%花';-- LIKE与%[通配符] 最后一位为花
SELECT * FROM USER WHERE `name` LIKE '%花%';-- 包含花字
SELECT * FROM USER WHERE `name` LIKE'花%';-- 第一位为花

SELECT * FROM USER WHERE name NOT LIKE '%花%';

SELECT * FROM USER WHERE id IN(1,5,7);

SELECT * FROM USER WHERE name IN('花花','荷花');

SELECT * FROM USER WHERE name IN('%花%','荷花')-- 百分号在in里不生效，会当做字符串 %在这里不用做通配符

SELECT * FROM USER WHERE birthday IN('1991/01/01','1992/01/01');-- IN里的必须写全，如不能只写年

SELECT * FROM USER WHERE name NOT IN('花花','荷花');



-- 非数字列都要加单引号
-- BETWEEN AND 数字和日期
SELECT * FROM USER WHERE id BETWEEN 4 AND 7;-- 前后包含SELECT * FROM USER WHERE birthday BETWEEN '2000-01-01' AND '2012-01-01'-- 日期不能反着写如（ BETWEEN '2012-01-01' AND '2000-01-01'），出不来

SELECT * FROM USER WHERE age is NULL；-- 一般和is连用，不要和等号连用如（age=NULL）

-- 问题 查非空
SELECT * FROM USER WHERE age is NOT NULL；-- 正确
SELECT * FROM USER WHERE age  NOT is NULL；-- 会报语法错误


-- GROUP BY子句  分组
SELECT * FROM USER GROUP BY id;-- 分组字段缺失意义 每个id都不同
SELECT * FROM USER GROUP BY name;-- (去重)只是把重复的名字去掉了，没有意义

-- 统计每个年龄有多少人（有意义的） -- 2一般来说分组常与聚合函数一起使用 -- 3查询列除了聚合函数一般只能包含被分组列
SELECT age COUNT(age) '人数' FROM USER GROUP BY age DESC-- 不写默认升序 [ASC升序|DESC降序]

-- 单表查询
CREATE TABLE grade(
id INT PRIMARY KEY auto_increment,
name VARCHAR(10),
subject VARCHAR(10),
score DOUBLE        -- 变蓝色说明与内置的变量重名，可正常使用
)
INSERT INTO grade VALUES(NULL,'aa','数学',90);
INSERT INTO grade VALUES(NULL,'bb','语文',80);
INSERT INTO grade VALUES(NULL,'aa','化学',70);
INSERT INTO grade VALUES(NULL,'cc','语文',60);
INSERT INTO grade VALUES(NULL,'bb','数学',90);
INSERT INTO grade VALUES(NULL,'cc','生物',80);
INSERT INTO grade VALUES(NULL,'cc','数学',90);
INSERT INTO grade VALUES(NULL,'aa','语文',80);
INSERT INTO grade VALUES(NULL,'bb','生物',80);

DROP TABLE grade;

SELECT * FROM grade

-- 统计每个人总分数 SUM(expr) 总分数
SELECT name,SUM(score) '总分数' FROM grade GROUP BY name;
-- AVG(expr) 平均数
SELECT name,AVG(score) '平均分' FROM grade GROUP BY name;


--  本次考试数学试卷有问题，要求（数学除外）统计每个人的总分数
-- SELECT * FROM grade WHERE subject!='数学';
SELECT name,SUM(score) '总分数' FROM grade WHERE subject!='数学' GROUP BY name;
SELECT name,SUM(score) 'scoreSum' FROM grade WHERE subject NOT IN('数学') GROUP BY name;

-- MIN(expr)最小值  MAX(expr)最大值
SELECT name,MIN(score) FROM grade GROUP BY name;
SELECT name,MAX(score) FROM grade GROUP BY name;

-- GROUP_CONCAT(subject)
SELECT name,GROUP_CONCAT(subject) FROM grade GROUP BY name;-- 统计每人考过哪些科目

-- WITH ROLLUP通常放在group by后面
-- 每个年龄多少人，对于聚合函数那一列向上回溯统计总人数（二次统计）
SELECT age COUNT(age) '人数' FROM USER GROUP BY age DESC WITH ROLLUP;


-- HAVING子句
--  本次考试数学试卷有问题，要求（数学除外）统计每个人的总分数 显示总分数大于100分的同学
-- where条件是对原始表的过滤条件，having条件是对已经过滤后生成的新表结果在过滤
-- WHERE不在时，HAVING 可以替代where[如果where GROUP BY HAVING 并列出现时有顺序]
-- 备注：别名使用没问题
SELECT name 'nnn',score 'sss' FROM grade HAVING score>60


-- ORDER BY子句   专业排序（支持多列排序,第一志愿第二志愿） ASC升序 DESC降序
SELECT * FROM user;-- 默认按id升序排序

SELECT * FROM user ORDER BY age ,id DESC;


-- LIMIT m,n[分页会用到] m开始位置(索引从0开始)，n条数

SELECT * from grade LIMIT 0,3;

SELECT * from grade LIMIT 3;-- 不写默认从头开始查

SELECT name,score FROM grade  WHERE subject='数学' ORDER BY score DESC LIMIT 2;


SELECT DISTINCT name FROM user;-- 去重 去重后不能在查询其他列，因为它不知道选哪一个人的age

-- 如SELECT age,DISTINCT name FROM user;会报错


-- UNION联合两张表的结果，最后合在一张表上显示
SELECT id,name FROM user UNION SELECT id,name FROM grade;


(SELECT id,name FROM user  ORDER BY id DESC) UNION (SELECT id,name FROM grade LIMIT 4);-- 如果两个查询中有oeder by LIMIT一类的子句，需要用小括号()括起来

SELECT id,name FROM user
UNION  DISTINCT 
SELECT id,name FROM grade;-- 联合时去除完全重复的一条记录


INSERT INTO grade VALUES(NULL,'张三','语文',81);
INSERT INTO grade VALUES(NULL,'张三','数学',75);
INSERT INTO grade VALUES(NULL,'李四','语文',76);
INSERT INTO grade VALUES(NULL,'李四','数学',90);
INSERT INTO grade VALUES(NULL,'王五','语文',81);
INSERT INTO grade VALUES(NULL,'王五','数学',100);
INSERT INTO grade VALUES(NULL,'王五','英语',90);

-- 每门课都大于80的学生姓名
SELECT  name,MIN(score) 'minscore' FROM grade GROUP BY name HAVING minscore>80