SQL命令汇总

SQL命令大全

参考

1 管理MySQL

1. 使用MySQL workbench可视化图形界面管理数据库
2. 使用MySQL commond line

2 操作数据库

在一个MySQL服务器上，可创建并管理多个数据库

查询：

1. 查看所有数据库：SHOW DATABASES;
2. 切换为当前数据库：USE cug;

对一个数据库操作首先要切换到当前数据库

修改：

1. 创建新的数据库：CREATE DATABASE cug;
2. 删除某个数据库：DROP DATABASE cug;

3 操作表

查询：

1. 查看数据库中的所有表：SHOW TABLES students;
2. 查看某个表的结构：DESC students;
3. 查看创建表的SQL语句：SHOW CREATE TABLE students;

修改：

1. 创建表: CREARE TABLE students(column_name column_type);

CREATE TABLE  `students`(
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `class_id` bigint(20) NOT NULL,
   `gender` varchar(1) NOT NULL,
   `score` int(11) NOT NULL,
   PRIMARY KEY ( `id` )
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;

①CMD创建表时至少增加一个列，否则会报错
②在创建表的时候，可加反引号来避免你自己的标识符（字段名，表名等）和数据库SQL中的关键字冲突

2. 删除表：DROP TABLE students;
3. 修改表结构：
   1)新增字段（列）：
   ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
   2)修改字段信息：
   ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
   3)删除字段：
   ALTER TABLE students DROP COLUMN birthday;

4 操作数据

4.1 基本查询

1. 查询所有列（可指定行–条件查询）

SELECT * FROM students
WHERE id>=20; 

在WHERE的条件上我们可以使用operator有：<, <=, >, >=, =
注意等于的判断只用一个等号

2. 查询指定列–投影查询

SELECT name, score FROM students; 

注意，这里的多个字段不用括号括起来

3. 从students表中查询成绩以9打头的所有记录

SELECT * FROM students WHRER score regexp '^9'

4.1.1 排序

SELECT查询时默认按id顺序排列，根据条件排序：使用ORDER BY

1.默认升序排列：

SELECT * FROM students ORDER BY score;

2.使用DESC可降序排列：

SELECT * FROM students ORDER BY score DESC;

3.如果有相同分数的，再按gender列排序：

SELECT * FROM students ORDER BY score DESC, gender;

PS：如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面：

SELECT * FROM students 
WHERE class_id = 1 
ORDER BY score DESC;

4.1.2 分页查询

LIMIT 3 OFFSET 0，从第0条开始查3条，作为一个分页

SELECT * FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;

4.1.3 聚合查询

聚合函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kN6ElHHH-1601907502723)(en-resource://database/2285:1)]

如：查询表中的男生总数

SELECT COUNT(*) boys FROM students 
WHERE gender = 'M';

为了方便处理结果，给列名COUNT(* )起别名num

• 使用GROUP BY 分组聚合

SELECT class_id, COUNT(*) population FROM students 
GROUP BY class_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LT3TlndK-1601907502725)(en-resource://database/2303:1)]@w=500

• 使用多个列进行分组：

SELECT class_id, gender, COUNT(*) num FROM students
GROUP BY class_id, gender;

上述查询结果集一共有6条记录，分别对应各班级的男生和女生人数。

4.1.4 多表查询–连接查询 *

1. 用条件直接查多表

SELECT s.id, s.name, s.class_id, c.name class_name
FROM students s, classes c
WHERE  s.class_id = c.id

2. 连接查询

SELECT s.id, s.name, s.class_id, c.name class_name
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

4.2 修改数据

4.2.1 插入 INSERT

1. 全列插入

INSERT INTO students VALUES('朱蕊','女',24,'武汉');

自增型字段和有默认值的字段在INSERT语句中可不出现

2. 部分列插入

INSERT INTO students(name,age) VALUES('朱蕊',24);

3. 多行插入

INSERT INTO students(name,age) VALUES('朱蕊',24),('宋超超',25);

4.2.2 更新 UPDATE

使用UPDATE，我们就可以一次更新表中的一条或多条记录。

UPDATE students SET name='大牛', score=66 WHERE id=1;
UPDATE students SET score=score+10 WHERE score<80;

如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。

最后，要特别小心的是，UPDATE语句可以没有WHERE条件，例如：UPDATE students SET score=60;这时，整个表的所有记录都会被更新。

所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

4.2.3 删除 DELETE

可以一次删除表中的一条或多条记录。

DELETE FROM students WHERE id>=5 AND id<=7;

其他

WHERE, GROUP BY, HAVING的使用顺序

在聚合查询时聚合函数会作用在多条记录上，GROUP BY分组聚合时有时需要加限定条件。

having是分组（group by）后的筛选条件，在分组后的数据组内再筛选；where则是在分组前筛选。

顺序是：
WHERE
GROUP BY
HAVING

查询男同学的均分大于80的班级：

SELECT AVG(score) average, class_id 
FROM students
WHERE gender='M' 
GROUP BY class_id 
HAVING AVG(score)>80