基础SQL命令记录、以及一个简单的例子作为参考

一、基础

SQL（Structured Query Language)：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。

1 启动MySQL

cmd打开运行窗口键入,启动mysql服务

1	net start mysql

打开SQLyog，连接数据库、进入本地数据库
创建一个数据库并使用

1
2
3

CREATE DATABASE mysqlWk; -- 直接点击建库更好、字符集选择utf-8 排序选择utf_general_ci
USE mysqlWk; ##如果已有数据库 直接使用即可
drop database if exists mysqlWk； ##删库跑路

2 MySQL三种注释

# 注释
-- 注释
/* 注释1
   注释2 */

3 MySQL数据类型

# 整型/字节数
tinyint smallint mediumint int/integer bigint
1         2        3          4            8

# 浮点数 D位存小数部分，剩下M-D部分存整数部分
float(M,D) 4    double(M,D) 8

# 字符型
char  varchar 定长/变长
-- varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样。
-- 但建议选择合适的 varchar长度，因为 order by排序时，计算的是可变的长度、会占用更多的内存

# 时间和日期
datetime 8 -- 与时区无关，存从1000年到9999年的日期和时间，精度s
timestamp 4 -- 和时区有关,表示从 1970 年到 2038 年,使用效率更高

二、创建和修改表

这里所有的表名、属性都用小写字母_ 的组合

1 创建表

# 创建 student_info 表
create table student_info (
s_id int primary key not null,
s_name varchar(30) not null,
s_gender int not null default 1,
s_hometown varchar(50),
s_tuition int
);
# 创建 student_subject 表
create table student_subject(
s_id int primary key not null,
s_grade int not null default 60,
sub_id int not null
);
# 创建 subject_info表
create table subject_info(
sub_id int primary key not null,
sub_name varchar(20) not null
);

2 修改表

1
2
3

alter table student_info add age int; -- 添加列
alter table student_info drop age int; -- 删除列
drop table student_info; -- 删除表

三、增删改

1 插入数据

insert into student_info(s_id,s_name,s_gender,s_hometown,s_tuition) values(1,'吴康',1,'湖南长沙',8000);
insert into student_info values(2,'落风',1,'湖北武汉',5000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(3,'用户1',1,6000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(4,'用户2',1,6000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(5,'妹子1',2,8000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(6,'妹子2',2,5000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(7,'妹子3',2,4000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(8,'妹子4',2,5000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(9,'妹子5',2,9000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(10,'妹子6',2,5000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(11,'用户3',1,4000);
insert into student_info(s_id,s_name,s_gender,s_tuition) values(12,'用户4',1,5000);

insert into student_subject(s_id,s_grade,sub_id) values(1,80,1);
insert into student_subject(s_id,s_grade,sub_id) values(2,59,1);
insert into student_subject(s_id,s_grade,sub_id) values(3,60,2);
insert into student_subject(s_id,s_grade,sub_id) values(4,40,2);
insert into student_subject(s_id,s_grade,sub_id) values(5,80,3);
insert into student_subject(s_id,s_grade,sub_id) values(6,75,4);
insert into student_subject(s_id,s_grade,sub_id) values(7,80,4);
insert into student_subject(s_id,s_grade,sub_id) values(8,90,4);
insert into student_subject(s_id,s_grade,sub_id) values(9,80,3);
insert into student_subject(s_id,s_grade,sub_id) values(10,95,3);
insert into student_subject(s_id,s_grade,sub_id) values(11,96,4);
insert into student_subject(s_id,s_grade,sub_id) values(12,80,1);

insert into subject_info(sub_id,sub_name) values(1, 'Math');
insert into subject_info(sub_id,sub_name) values(2, 'English');
insert into subject_info(sub_id,sub_name) values(3, 'Computer');
insert into subject_info(sub_id,sub_name) values(4, 'News');
insert into subject_info(sub_id,sub_name) values(5, 'civil enginering');

2 更新数据

1	update student_info set s_hometown = '湖北荆州' where s_id = 1;

3 删除数据

1	delete from subject_info where sun_id = 5; -- 垃圾土木毁我青春

四、查询语句

书写顺序和执行顺序！！语法：
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引，条目数; ⑨

1 limit i，j; 返回第i行开始的共j行数据

查询subject_info的前三行数据

1	select * from subject_info limit 0,3;

查询student_info的4到7行数据

1	select * from student_info limit 3,4;

2 order by 排序:ASC升序（默认） DESC降序【重要】

student_info表中按学费的降序排序，如果学费相同按id升序排序

1	select * from student_info order by s_tuition DESC, s_id ASC;

3 where 过滤：（= > < between is null and or in not）

查询s_tuition>=8000并且s_gender=1的数据

1	select * from student_info where s_tuition>=8000 and s_gender = 1;

查询tuition在6000到8000范围内的数据

1	select * from student_info where s_tuition between 6000 and 8000;

查询s_hometown不为null的数据

1	select * from student_info where s_hometown is not null;

4 like通配符： %匹配任意数量的字符 _匹配一个字符 [ ]匹配括号内的元素

查询s_name是“用”开头的数据

1	select * from student_info where s_name like '用%';

5 数学函数：count()求数量 max()最大值 sum()求和..等

查询学费=8000的人的数据和数量

1 2	SELECT * FROM student_info WHERE s_tuition = 8000; -- 显示两条数据 select ,count() from student_info where s_tuition = 8000; -- 只显示一条数据，count列=2

五子查询、分组和连接、分页

考虑到分组和连接比较重要，单独拿出一章来介绍和了解

5.1 子查询

子查询中只能返回一个字段的数据。

==1 可以将子查询的结果作为 WHRER 语句的过滤条件。这个懂==

比如我要查出来sub_id=4的专业的所有学生姓名和家乡
- 从student_subject中找到这个sub_id对应的学生s_id
- 然后从student_info表中根据这个s_id找到姓名和家乡

1
2
3

SELECT s_name,s_hometown FROM student_info WHERE s_id IN
(SELECT s_id FROM student_subject  WHERE sub_id = 4
);

如果要进一步查具体某个专业，就要再嵌套一层子查询

比如我要查出来sub_name=News专业的所有学生姓名和家乡
- 要先从subject_info中找到News专业对应的sub_id
- 然后从student_subject中找到这个sub_id对应的学生s_id
- 最后从student_info表中根据这个s_id找到姓名和家乡

select s_name,s_hometown from student_info where s_id in
(select s_id from student_subject where sub_id = 
 	( select sub_id from subject_info where sub_name = 'News')
);

也就是说：我最后查得的数据，只能是一个表内的，子查询顶多给我做了一个筛选的功能。（不知道这个理解对不对）

==2 子查询还有一个功能！！就是将子查询放在查询字段中时，会对每一行查询的结果都做一次子查询！！==

比如我要查每个专业分别有几个同学
- 我可以先写查某一个具体的专业有几个人
- 然后嵌套进查所有专业的语句中、实现差所有专业的人数

## 查Math专业有几个人
select count(*) nums from student_subject where sub_id = 1;

## 查所有的专业
select sub_name, sub_id from subject_info;

## 嵌套子查询 每个抓专业有多少人 用where连接两个表
select sub_name, sub_id,
(select count(*) from student_subject where student_subject.sub_id= subject_info.sub_id) nums
from subject_info;

5.2 分组group by

1 group by理解

我之前一直疑惑一个问题：group by 排完序之后到底变成了一个怎样的表？

==分组操作执行之后：每一行代表一个分组，每个分组只显示第一个数据==
可以用count(*) nums求每一个分组内元素的个数
还可以用HAVING nums>6; 过滤分组中的字段
如果group by后面只有一个字段就是只按这一个字段的来分组，如果group by后面有多个字段，那需要这些字段都相同才是一个分组！！

两个重要的要求：

除了count()等字段外，SELECT 语句中要查的每一字段都必须在 GROUP BY 子句中给出
- 关系数据库就是基于关系的，单元格中是不允许有多个值的，只对一个字段分组了，那么就只有该列不是重复的！！如果执行select * 就会报错！！
- 聚合函数比如count()、sum()、就是将一组内的多个数据计算输出一个数据
where要用在group之前，having要用在group之后
- WHERE 过滤行数据，HAVING 过滤分组，行过滤应当先于分组过滤。

2 group by练习

按s_tuition分组，学费相同的为一组

1 2	select s_tuition from student_info group by s_tuition; SELECT s_name,s_tuition,COUNT(*) nums FROM student_info GROUP BY s_tuition;

按s_gender分组，性别相同的为一组

select s_gender from student_info group by s_gender;
-- 我用的这个版本没有报错，显示了该分组第一行的所有数据。
select * from student_info group by s_gender; 
select * ,count(*) nums from student_info group by s_gender; -- 显示数据个数

5.3 连接 join【重点】

连接用于连接多个表，使用 join 关键字，并且条件语句使用 on ！！！连接一般可以用来替代子查询！！使用超级频繁

三种连接方式：内连接、左连接、右连接

内连接：就是只有两个表都不为null的数据才能显示出来（取交集）inner join
左连接：以左表为主表（左边表的数据全显示）、右表中无对应数据的显示为null
右连接：以右表为主表（右边表的数据全显示）、左表中无对应数据的显示为null

一般会一个就好了，就练习左连接吧

查询所有学生的s_id、sub_id和sub_name，很显然要连接student_subject和表subjct_info

1
2
3

select b.s_id,b.sub_id,c.sub_name from
	student_subject b left join subject_info c
    on b.sub_id = c.sub_id;

再看看把subject_info放在前面会怎么样

SELECT b.s_id,b.sub_id,c.sub_name FROM
	subject_info c LEFT JOIN student_subject b
	ON b.sub_id = c.sub_id;
-- 发现结果基本一样（因为都不为空）
-- 只是数据的排序方式不一样（似乎是按副表的字段排序的）

我试一下吧三个表都连接起来看看!!

#先连接bc两个表
(select b.s_id, b.s_grade,b.sub_id,c.sub_name from
	student_subject b left join subject_info c
	on b.sub_id = c.sub_id) bc
#然后连接a表和bc表
select a.s_id,a.s_name,a.s_gender,a.s_hometown,a.s_tuition,
bc.s_grade,bc.sub_id,bc.sub_name from
student_info a left join 
(select b.s_id, b.s_grade,b.sub_id,c.sub_name from
	student_subject b left join subject_info c
	on b.sub_id = c.sub_id) bc
on a.s_id=bc.s_id;
-- 居然还成功了

5.4 分页查询

参考文章https://www.cnblogs.com/endv/p/12275043.html

六终极查询练习

找男生人数最多的两个专业和人数！！

其实就可以用我上面那个查出来的大表，先筛选出男生的行，然后以专业分组、计算数量排序然后取前两个就好

1 先连接形成大表
2 where筛选出s_gender=1，所有的男生
3 用group by对sub_name分组，并求每个分组的数量count(*) nums
4 用order by对nums降序排列
5 limit 0,2找出前两行数据！！

SELECT a.s_id,a.s_name,a.s_gender,a.s_hometown,a.s_tuition,
bc.s_grade,bc.sub_id,bc.sub_name,COUNT(*) nums FROM
student_info a LEFT JOIN 
(SELECT b.s_id, b.s_grade,b.sub_id,c.sub_name FROM
	student_subject b LEFT JOIN subject_info c
	ON b.sub_id = c.sub_id) bc
ON a.s_id=bc.s_id
WHERE s_gender = 1
GROUP BY sub_name
ORDER BY nums DESC
LIMIT 0,2;
-- 总感觉这么直接连接不太优雅

七表的索引

数据量较大时，建立和使用提高效率。

本机创建千万级别的数据表单，用于测试

附录：所有sql语句

## 创建student_info表并插入数据
CREATE TABLE student_info (
s_id INT PRIMARY KEY NOT NULL,
s_name VARCHAR(30) NOT NULL,
s_gender INT NOT NULL DEFAULT 1,
s_hometown VARCHAR(50),
s_tuition INT
);
INSERT INTO student_info(s_id,s_name,s_gender,s_hometown,s_tuition) VALUES(1,'吴康',1,'湖南长沙',8000);
INSERT INTO student_info VALUES(2,'落风',1,'湖北武汉',5000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(3,'用户1',1,6000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(4,'用户2',1,6000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(5,'妹子1',2,8000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(6,'妹子2',2,5000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(7,'妹子3',2,4000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(8,'妹子4',2,5000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(9,'妹子5',2,9000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(10,'妹子6',2,5000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(11,'用户3',1,4000);
INSERT INTO student_info(s_id,s_name,s_gender,s_tuition) VALUES(12,'用户4',1,5000);


# 创建 student_subject 表
CREATE TABLE student_subject(
s_id INT PRIMARY KEY NOT NULL,
s_grade INT NOT NULL DEFAULT 60,
sub_id INT NOT NULL
);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(1,80,1);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(2,59,1);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(3,60,2);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(4,40,2);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(5,80,3);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(6,75,4);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(7,80,4);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(8,90,4);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(9,80,3);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(10,95,3);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(11,96,4);
INSERT INTO student_subject(s_id,s_grade,sub_id) VALUES(12,80,1);

# 创建 subject_info表
CREATE TABLE subject_info(
sub_id INT PRIMARY KEY NOT NULL,
sub_name VARCHAR(20) NOT NULL
);
INSERT INTO subject_info(sub_id,sub_name) VALUES(1, 'Math');
INSERT INTO subject_info(sub_id,sub_name) VALUES(2, 'English');
INSERT INTO subject_info(sub_id,sub_name) VALUES(3, 'Computer');
INSERT INTO subject_info(sub_id,sub_name) VALUES(4, 'News');
INSERT INTO subject_info(sub_id,sub_name) VALUES(5, 'civil enginering');
# 更新和删除
UPDATE student_info SET s_hometown = '湖北荆州' WHERE s_id = 1;
DELETE FROM subject_info WHERE sub_id = 5;


## limit
SELECT * FROM subject_info LIMIT 0,3;
SELECT * FROM student_info LIMIT 3,4;
## order by
SELECT * FROM student_info ORDER BY s_tuition DESC, s_id ASC;
## where
SELECT * FROM student_info WHERE s_tuition>=8000 AND s_gender = 1;
SELECT * FROM student_info WHERE s_tuition BETWEEN 6000 AND 8000;
SELECT * FROM student_info WHERE s_hometown  IS  NOT NULL;
SELECT * FROM student_info WHERE s_name  LIKE '用%';

SELECT * FROM student_info WHERE s_tuition = 8000;
SELECT *,COUNT(*) FROM student_info WHERE s_tuition = 8000;

## group by
SELECT s_name,s_tuition,COUNT(*) nums FROM student_info GROUP BY s_tuition;
SELECT s_gender FROM student_info GROUP BY s_gender;
SELECT *  nums FROM student_info GROUP BY s_gender;
SELECT * ,COUNT(*) nums FROM student_info GROUP BY s_gender;

## 子查询
SELECT s_name,s_hometown FROM student_info WHERE s_id IN
(SELECT s_id FROM student_subject  WHERE sub_id = 4
);

SELECT COUNT(*) nums FROM student_subject WHERE sub_id = 1;
SELECT sub_name, sub_id FROM subject_info;

SELECT sub_name, sub_id,
(SELECT COUNT(*) FROM student_subject WHERE student_subject.sub_id= subject_info.sub_id) nums
FROM subject_info;

## left join...on
SELECT b.s_id,b.sub_id,c.sub_name FROM
	student_subject b LEFT JOIN subject_info c
	ON b.sub_id = c.sub_id;

SELECT b.s_id,b.sub_id,c.sub_name FROM
	subject_info c  LEFT JOIN student_subject b
	ON b.sub_id = c.sub_id;

SELECT a.s_id,a.s_name,a.s_gender,a.s_hometown,a.s_tuition,
bc.s_grade,bc.sub_id,bc.sub_name FROM
student_info a LEFT JOIN 
(SELECT b.s_id, b.s_grade,b.sub_id,c.sub_name FROM
	student_subject b LEFT JOIN subject_info c
	ON b.sub_id = c.sub_id) bc
ON a.s_id=bc.s_id;

## 终极查询：找男生人数最多的两个专业和人数
SELECT a.s_id,a.s_name,a.s_gender,a.s_hometown,a.s_tuition,
bc.s_grade,bc.sub_id,bc.sub_name,COUNT(*) nums FROM
student_info a LEFT JOIN 
(SELECT b.s_id, b.s_grade,b.sub_id,c.sub_name FROM
	student_subject b LEFT JOIN subject_info c
	ON b.sub_id = c.sub_id) bc
ON a.s_id=bc.s_id
WHERE s_gender = 1
GROUP BY sub_name
ORDER BY nums DESC
LIMIT 0,2;

落风的风

mysql语法&练习

一、基础

二、创建和修改表

三、增删改

四、查询语句

五子查询、分组和连接、分页

5.1 子查询

5.2 分组group by

5.3 连接 join【重点】

5.4 分页查询

六终极查询练习

七表的索引

附录：所有sql语句

一、基础

二、创建和修改表

三、增删改

四、查询语句

五 子查询、分组和连接、分页

5.1 子查询

5.2 分组group by

5.3 连接 join【重点】

5.4 分页查询

六 终极查询练习

七 表的索引

附录：所有sql语句

五子查询、分组和连接、分页

六终极查询练习

七表的索引