Mysql的SQL语句汇总

SQL语句总结！！！

常见错误

• group by
  select A where B group by C having D

1. 对数据先 where，再 group by，最后 having
2. B 中的列和 C 中的列无关
   A 中的列（除聚集函数外）均要出现在 C 中
   D 中的列均要出现在 C 中
3. C 中的所有列的 组合值 唯一（相当于多列主键），才能在一组

• having
  有 having 必须前面有 group by

• union
  select A unoin select B
  A 和 B 的列、聚集函数均要相同，但顺序可不一致

• join…on 联结表
  select 后建议加上on 的字段，select A...join...on t1.A = t2.A;

• delete、update
  mysql 中，delete、update 操作若出现 select子句过滤，不能为同一张表
  解决：将子语句作为 from 表再包裹一层，select * from（子句） as t，给表起别名

SQL语句优化

• distinct
  用 group by 代替 distinct，更高效
• 多表查询
  尽量用联结查询（join on）

数据库 DATABASE

# 查看所有数据库
SHOW DATABASES;

# 查看一个数据库的所有表
USE school;
SHOW TABLES;

# 查看一个表的所有列的属性信息
DESCRIBE student;
SHOW COLUMNS FROM student;		# 等效



# 创建
CREATE DATABASE IF NOT EXISTS school;
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER  SET utf8;	# 字符集为 utf8
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER  SET utf8 DEFAULT COLLATE utf8_chinese_ci;	# 校对规则为 utf8_chinese_ci（简体中文，不区分大小写）

# 删除
DROP DATABASE IF EXISTS school;

# 修改：数据库的参数
ALTER DATABASE IF EXISTS school DEFAULT CHARACTER  SET utf8;

# 修改：数据库的名字
RENAME DATABASE school TO new_school;

数据表 TABLE

# 查看一个表的所有列的属性信息
DESCRIBE student;
SHOW COLUMNS FROM student;		# 等效

# 创建
CREATE TABLE IF NOT EXISTS student(
	id int NOT NULL AUTO_INCREMENT,
	score float	NOT NULL DEFAULT 0,
	name varchar(20) NOT NULL,
	email char(100) NOT NULL DEFAULT '123@qq.com',
	birth date NOT NULL	DEFAULT '2000-01-01',
	others text	NULL,
	PRIMARY KEY(id),		# 主键
	ADD CONSTRAINT fk_course_student FOREIGN KEY score REFERENCES course(score)		# 外键
)ENGINE=InnoDB;		# 指定引擎InnoDB

CREATE TABLE IF NOT EXISTS course(
	id int	NOT NULL AUTO_INCREMENT,
	score float NOT NULL DEFAULT 0,
	course_name	varchar(20)	NOT NULL,
	PRIMARY KEY(id)
);

# 删除
DROP TABLE IF EXISTS student;

# 修改表的名字
RENAME student TO new_student;

列的增删改

# 增
ALTER TABLE student
ADD age int NULL DEFAULT 18;

# 删
ALTER TABLE student
DROP COLUMN age;

# 改

索引的增删改

# 增
ALTER TABLE student
ADD INDEX index_name(name);	

主键的增删改

外键的增删改

# 增
ALTER TABLE student
ADD CONSTRAINT fk_course_student			# 外键名fk_course_student
FOREIGN KEY score REFERENCES course(score);

---

操作数据 CRUD

增加

insert into … values …

# 一条数据
INSERT INTO student
VALUES(97,		
	'李华',
	...);			# 按列的顺序插入


# 批量：多条数据
INSERT INTO student
VALUES(97,
	'李华',
	...),
	(72,
	'王明',
	...);		# 按列的顺序插入


# 插入部分
INSERT INTO student(birth,
	name)						# 列不按顺序
VALUES('1998-01-01',
	'李华');


# 将其他表的数据插入
INSERT INTO student(score)
SELECT score
FROM course
WHERE course_name='数学';

删除

delete table
truncate table

# 删除一个表的所有数据
DELETE FROM student;

# 删除行
DELETE FROM student
WHERE id<10;

# 删除列
UPDATE student
SET others=NULL;


#DELETE TABLE student
#TRUNCATE TABLE student;

修改

update… set…
ignore

# 更新行
UPDATE student
SET others=NULL
	score=100.0;
WHERE id<10;


# 更新列
UPDATE student
SET others=NULL
	score=100.0;


# 强制更新（无论错误）
UPDATE IGNORE student
SET others=NULL
	score=100.0;

查询

单表

select distinct … from …
where
group by … with rollup having …
order by… desc
limit

SELECT *
FROM student;		

SELECT DISTINCT name, birth, email		# 两行数据的3列完全一致，才去重。若3列不完全一致，则都显示。
FROM student;

SELECT *
FROM student
ORDER BY name, score DESC 		# 先按 name 升序， 若相同则按 score 降序
LIMIT 10;

SELECT *
FROM student
ORDER BY name, score DESC 
LIMIT 2,1;		# 数据行：0、1、2...   从行2（第三行）开始取1个

SELECT *
FROM student
ORDER BY name, score DESC 
LIMIT 1 OFFSET 2;		# 效果同上

SELECT birth
FROM student
GROUP BY birth	with ROLLUP;		# 包括 NULL 分组

SELECT birth, AVG(score) as score_avg		# 除聚集函数中的列，select的所有列必须出现在group by中。
FROM student
WHERE age=18		
GROUP BY birth HAVING id>10;

SELECT * 
FROM student
WHERE others IS NOT NULL;

SELECT * 
FROM student
WHERE score NOT BETWEEN 60 AND 100;			# [60,100]闭区间

SELECT * 
FROM student
WHERE birth NOT IN ['2020-01-01', '2020-02-01'];

SELECT *
FROM student
WHERE (id=10 OR id=20) AND other IS NULL;		# 用括号改变优先级，实际中，同时使用AND和OR建议用括号，默认先处理 and，再处理 or

SELECT * 
FROM student
WHERE name LIKE '李%';			# 匹配整个列值，即以李开头

SELECT CONCAT('(', name, '_', score, ')')
AS name_score
FROM student
WHERE id>10;

SELECT id + score - id
AS new_score
FROM student
WHERE id>10;

# 聚集函数
SELECT Count(*)
FROM student;		# 返回表的行数（包括 NULL）

SELECT Count(name)
FROM student;		# 返回列的行数（排除 NULL）

SELECT Avg(DISTINCT score)
FROM student;			# 结合 DISTINCT

多表

join on
union
union all

# 子查询
SELECT *
FROM student
WHERE birth IN (SELECT birth
	FROM student 
	WHERE id>2);

SELECT score,
	(SELECT course_name 
	FROM course
	WHERE course.score>60)
FROM student
WHERE id>10;


# 联合查询
SELECT name, student.score, course.score
FROM student, course;			# 笛卡尔积
# JOIN ON 一共有 7种


# 组合查询
SELECT name, email
FORM student
WHERE id>10
UNION			# 两个select的列相同，顺序可不同
SELECT email, name
FORM student
WHERE id>10
ORDER BY id;	# 只能使用一个 order by，且在最后一条select后

SELECT name, email
FORM student
WHERE id>10
UNION ALL			# 组合结果不去重
SELECT email, name
FORM student
WHERE id>10;