SQL命令大全

文章目录

SQL命令大全1 管理MySQL2 操作数据库3 操作表4 操作数据4.1 基本查询4.1.1 排序4.1.2 分页查询4.1.3 聚合查询4.1.4 多表查询--连接查询 * 4.2 修改数据4.2.1 插入 INSERT4.2.2 更新 UPDATE4.2.3 删除 DELETE 其他WHERE, GROUP BY, HAVING的使用顺序 参考

1 管理MySQL

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

2 操作数据库

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

查询：

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

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

修改：

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

3 操作表

查询：

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

修改：

创建表: 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中的关键字冲突

删除表：DROP TABLE students;修改表结构： 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 基本查询

查询所有列（可指定行–条件查询） SELECT * FROM students WHERE id>=20;

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

查询指定列–投影查询 SELECT name, score FROM students;

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

从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 多表查询–连接查询 *

用条件直接查多表 SELECT s.id, s.name, s.class_id, c.name class_name FROM students s, classes c WHERE s.class_id = c.id 连接查询 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

全列插入 INSERT INTO students VALUES('朱蕊','女',24,'武汉');

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

部分列插入 INSERT INTO students(name,age) VALUES('朱蕊',24); 多行插入 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