SQL语句整理笔记

MySQL常用SQL语句整理

基本语句

-- 显示所有数据库
SHOW DATABASE;
-- 创建数据库
CREATE DATABASE <数据库名>;
-- 使用数据库
USE <数据库名>;
-- 显示数据库中的所有表
SHOW TABLES;
-- 删除数据库
DROP DATABASE <数据库名>;
-- 创建一个数据表
~~CREATE TABLE TB_STUDENT(
	# PRIMARY KEY-主键  AUTO_INCREMENT-自增  COMMENT '备注、注释'
	ROW_ID BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
	#UNIQUE-表示唯一
	CLAZZ_ID BIGINT UNIQUE COMMENT '班级主键#关联班级表的主键',
	# NOT NULL-不为空
	STU_NAME VARCHAR(20) NOT NULL COMMENT '学生姓名',
	STU_AGE INT COMMENT '学生年龄',
	ACTIVE_FLAG INT COMMENT '数据是否有效 #1：是 0：否',
    #FOREIGN KEY(字段值)  REFERENCES 表名(字段值)-外键约束
	FOREIGN KEY (CLAZZ_ID) REFERENCES TB_CLAZZ(ROW_ID)
)COMMENT '学生表';~~ 
-- 查询表
SELECT * FROM <表名>;
-- 修改表
ALTER TABLE <表名> ADD COLUMN <字段值> <数据类型> COMMENT'添加一列';
ALTER TABLE <表名> ADD COLUMN <字段值> <数据类型> COMMENT'在某字段值之后添加'AFTER <字段值>;
ALTER TABLE <表名> ADD COLUMN <字段值> <数据类型> COMMENT'在第一列添加'FIRST <字段值>;
ALTER TABLE <表名> DROP COLUMN <字段值> COMMENT'删除字段值';
ALTER TABLE <表名> MODIFY COLUMN <字段值> <数据类型> COMMENT'修改字段值类型';
ALTER TABLE <表名> RENAME <表名> COMMENT'修改表名';
--重置表 （drop table+重新创建表）主键会重置
TRUNCATE TABLE <表名>;
-- 删除表
DROP TABLE <表名>;
-- 查询数据
SELECT * FROM <表名> WHERE <条件>;
SELECT <字段值>... FROM <表名> WHERE <条件>;
-- 去重
SELECT DISTINCT <表名> FROM <字段值>;
-- 判断null
SELECT * FROM <表名> WHERE <字段值> IS NULL;
SELECT * FROM <表名> WHERE <字段值> IS NOT NULL;
-- 排序
# ORDER BY
#自然排序  升序 ASC  默认不写就是ASC   
SELECT * FROM <表名> ORDER BY <字段值>;
#自然排序  降序  DESC  倒叙
SELECT * FROM <表名> ORDER BY <字段值> DESC;
#多条件排序
SELECT * FROM <表名> ORDER BY <字段值> ASC,<字段值> DESC;
--别名
SELECT <表名> AS <表名2> FROM <表名>;
# 效果 MY_NAME 下全是王小明  AS可省略
SELECT *,'王小明' AS MY_NAME FROM <表名>;
-- 插入数据
#VALUES中的顺序要和表的保持一致
INSERT INTO <表名>(字段值,字段值,字段值) VALUES (数据, 数据,数据) COMMENT'插入单条';
INSERT INTO <表名>(字段值,字段值,字段值) VALUES (数据, 数据,数据),(数据, 数据,数据),(数据, 数据,数据),(数据, 数据,数据)COMMENT'插入多条';
INSERT INTO TB_STUDENT2(CLAZZ_ID,STU_NAME,STU_AGE,STU_SEX,ACTIVE_FLAG)
SELECT CLAZZ_ID,STU_NAME,STU_AGE,STU_SEX,ACTIVE_FLAG FROM TB_STUDENT COMMENT'通过查询的方式插入数据';
-- 修改数据
UPDATE <表名> SET <字段值> = <值> WHERE <条件>;
#  CONCAT()  字符串连接  设置目前时间
UPDATE TB_STUDENT2 SET STU_NAME = CONCAT(STU_NAME,'A'), CREATE_DATE = CURRENT_DATE() WHERE STU_SEX=0 COMMENT'字符串连接';
-- 删除数据
DELETE FROM <表名> WHERE <条件> COMMENT'物理删除';
UPDATE <表名> SET ACTIVE_FLAG = 0 WHERE ROW_ID = 1 COMMENT'逻辑删除';
#如果项目使用了逻辑删除，则每个查询语句后边都要加上
SELECT * FROM TB_CLAZZ WHERE ACTIVE_FLAG = 1;

聚合函数

#查看每个学生的总成绩  分组 GROUP BY  先通过GROUP BY进行数据分组,分组完成后，再根据分组的数据来统计  
SELECT STU_ID,SUM(SCORE) FROM MS_STUDENT_SUBJECT GROUP BY STU_ID;
SELECT SUM(SCORE) FROM MS_STUDENT_SUBJECT;

#统计出每个班级的学生数量
SELECT CLASS_ID,COUNT(ID) AS STU_COUNT FROM MS_STUDENT GROUP BY CLASS_ID;

#查看每个学生的平均成绩
SELECT STU_ID,AVG(SCORE) FROM MS_STUDENT_SUBJECT GROUP BY STU_ID;

#查看每个学生的平均成绩超过75分的
SELECT STU_ID,AVG(SCORE) AS AVG_SCORE FROM MS_STUDENT_SUBJECT 
GROUP BY STU_ID HAVING AVG_SCORE>75;

• where 条件子句

  • AND　OR
  • ＝　　＜　　　＞　　＜＝　＞＝　！＝　＜＞
  • is　null　　is　not null

• order by 排序子句

  • ASC 升序
  • DESC 降序

• group by 分组子句

  • having by + 条件 对分组后的数据进行约束

分页-LIMIT

#分页  limit 开始查询的数据的下标,查询的数量
SELECT * FROM MS_STUDENT LIMIT 0,3;
SELECT * FROM MS_STUDENT LIMIT 3,3;
#规律 （页号-1）*每页显示的条数  
SELECT * FROM MS_STUDENT LIMIT 6,3;

模糊查询

• like % _
• 模糊查询效率不高 一般不会使用

#模糊查询
#查询出所有姓张的  LIKE 模糊查询    % 匹配任意字符
SELECT * FROM MS_STUDENT WHERE STU_NAME LIKE '张%';
#查询出  ’张某某‘  一个_匹配一个任意字符
SELECT * FROM MS_STUDENT WHERE STU_NAME LIKE '张__';

• in(值，值，…) 效率也不高 少使用

#查看年龄等于18，20，22，24    IN
SELECT * FROM MS_STUDENT WHERE STU_AGE IN(18,20,22,24);

多表查询-子查询

• 子查询
  • 嵌套查询，一个查询语句的结果是另一个查询语句的条件的值。
  • 子查询的语句放在小括号内。
• 子查询的结果
  • 结果只有一个值，则主查询使用 “=” 来判断
  • 结果有多个值，则主查询使用 “in” 来判断

UPDATE MS_CLASS SET STU_COUNT=(SELECT COUNT(ID) FROM MS_STUDENT WHERE CLASS_ID = 1)  WHERE ID=1;
#查询出学生数量大于10的班级的所有学生的信息  单个条件用=   多个数据用  IN  
SELECT * FROM MS_STUDENT WHERE CLASS_ID IN(SELECT ID FROM MS_CLASS WHERE STU_COUNT>=10);

多表查询-关联查询

• 两个或两个以上的表连接成一个数据源

• 注意:关联之后，并不是形成了一个新的数据表，而只是一种”内存状态“

• INNER JOIN----查询量表共有的

• LEFT JOIN----查询量左表的全都和俩表共有的

• RIGTN JOIN----查询量右表的全都和俩表共有的

• FULL JOIN----查询两表中所有的数据-----MySQL不支持

• 可以使用多个join 别超过三个

#关联查询
SELECT MS_STUDENT.*,MS_CLASS.*
         #左关联  所有的数据以左边为主   一般从表数据多放左边
	FROM MS_STUDENT LEFT JOIN MS_CLASS
	ON MS_STUDENT.CLASS_ID = MS_CLASS.ID;
	
#关联查询   起别名
SELECT S.ID,S.CLASS_ID,C.CLASS_NAME,s.STU_NAME
	FROM MS_STUDENT S LEFT JOIN MS_CLASS C
	ON S.CLASS_ID = C.ID;

#INNER JOIN 查询两个表中都有的数据
SELECT S.*,C.*
FROM TB_STUDENT2 S
INNER JOIN TB_CLAZZ C
ON S.CLAZZ_ID = C.ROW_ID;

#LEFT   以左表为准
SELECT S.*,C.*
FROM TB_STUDENT2 S
LEFT JOIN TB_CLAZZ C
ON S.CLAZZ_ID = C.ROW_ID;

#RIGHT  以右表为准
SELECT S.*,C.*
FROM TB_STUDENT2 S
RIGHT JOIN TB_CLAZZ C
ON S.CLAZZ_ID = C.ROW_ID;
#FULL JOIN 两表中所有的数据  mysql 不支持