SQL语句(一)

// 查看当前链接的MySQL服务器的版本
select version(); 
// 查看当前链接的MySQL中所有的数据库
show databases;
// 输出指定内容
select  ‘xxxx’;
// 切换数据库
use test;
// 显示所有的表
show tables;
// 退出,在MySQL客户端使用,Navicat不能使用
exit;
基础的创建删除语句（DDL）
// 创建数据库
create database day01;
// 删除数据库
drop database day01;
// 切换到指定数据库
use day01;
create table student_info (
    id int,
		`name` varchar(20),
// 共18位, 其中2位小数,16位整数
		salary decimal(18,2)
)ENGINE = innodb default charset = utf8;
// 显示创建表语句
show create table student_info;
// 删除表
drop table student_info;
基础的查询语句（DQL）
use test;
select 列限定 from 表限定 where 行限定;
// 查询学生表中,成绩为100分的所有学生信息
select * from student where score = 100;
// 查询所有学生信息
select * from student;
// 查询所有学生的id和姓名
select id, name from student;

DDL-简单
DDL，Data Definition Language，数据定义语言。它通常用来创建、修改、删除数据库对象（库、表、视图、函数、存储过程、索引等）。涉及到的命令有create，alter，drop。
通常图形界面更多的是用来查询数据的，涉及到创建删除修改的时候我们更多的是使用SQL语句/命令在命令行中执行。
比如我们创建一个库create database my_db;
我们可使用drop database my_db;来删除一个库。执行删除的时候一定要慎重！！！！
create table teacher (
id int,
name varchar(100),
salary decimal(18,2)
);
通常一张表是可以和一个Java实体类对应起来的。这个表的一条记录通常就对应此实体类的一个对象。表的结构（有哪些字段，字段分别是什么类型）也与实体类定义对应。比如根据上面的表定义可以定义一个Java类：
public class Teacher {
private int id;
private String name;
private double salary;
}
使用drop table teacher;来删除一张表。
使用desc teacher;来查看一张表的表结构。

使用show create table teacher;来查看表的建表语句

CREATE TABLE teacher (
id int(11) DEFAULT NULL,
name varchar(100) DEFAULT NULL,
salary decimal(18,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

通常，对于SQL语法中的关键字比如CREATE等，使用大写，其他可以用小写。
DEFAULT NULL，代表这个字段默认值是NULL值。
ENGINE=MyISAM，代表这个表的存储引擎是MyISAM的。MYSQL的存储引擎有多种，常见的主要有2种：MyISAM，InnoDB。
前者适用读多写少的场景，它的读性能高。
如果我们想要支持外键，必须使用InnoDB，它的读性能相对于前者来说稍低（但是随着技术发展，现在InnoDB的性能已经提升了很多了，不比MyISAM差）。
如果想把一个已经建好的表的存储引擎修改为其他的，则使用
命令alter table teacher ENGINE=INNODB;;
另外MYSQL还有其他存储引擎，如果大家对存储引擎感兴趣可以自行百度去了解。这里不再深入。
DEFAULT CHARSET=utf8，代表这个表的字符集默认是UTF-8的。
另外，其中出现了一个特殊的符号：反引号（`），就是键盘上Tab键上面的那个（与波浪线在一个键位上的字符）。它的作用是为了区分开表名/字段名与SQL语法关键字的。虽然我们强烈不建议在定义表的时候使用SQL语法关键字，但是有时候可能不得不这么做，MYSQL给我们提供了反引号机制，让我们可以正常地操作表。比如
create table table (
id int,
name varchar(100),
salary decimal(18,2)
);
还有drop table table;
这2条命令，如果不使用反引号，是会报错的。
如何使用命令修改表名。
运行alter table teacher rename teacher_newname;
如何使用命令修改表结构。
给表加一个字段alter table teacher add birthday datetime;给teacher表加了一个字段，字段名是birthday，字段类型是datetime;

如果加字段的时候想添加到指定字段后面，可以使用
alter table teacher add birthday datetime after teacher_name;

添加到第一列
alter table t_student add sex2 char(2) first;

给表删除一个字段alter table teacher drop birthday;将字段名为birthday的字段删除

给表的字段改类型alter table teacher modify salary decimal(20,2);

改类型的时候要注意，尽量把类型扩大（比如varchar(100)改成varchar(500)），不要反过来改。尽量不要跨类型改（比如把一个decimal改成了int）。
给表的字段改名(同时也可以把类型改了)alter table teacher change name teacher_name varchar(200);

DML
DML，Data Manipulate Language，数据操作/操纵语言。它通常用来向一个表中插入、修改、删除一行记录。涉及到的命令有insert，update，delete。
比如我们要向teacher表中插入一条记录，运行
命令
insert into teacher (name, id, salary) values (‘老刘’,1,56.23);
insert into teacher (name, salary) values (‘老王’,56.23);

insert into t_student values(5,‘王军权’,100);
建议指定列,这样 就算表中又新添加了新的列,也不会出现问题
insert into t_student (name,id,score)values(‘张大寸’,7,120);

// 一次性添加多条数据,用逗号隔开
insert into t_student (name,id,score)values(‘张二寸’,7,120),(‘张三寸’,7,120);
命令delete from teacher where name=‘老王’;可以把符合where条件的记录全部删除。一定要加where条件，否则会删除全表的数据。
判断是否为null 用is , 判断不为null 用 is not null
命令delete from teacher where id is null也可以起到上面的作用。

命令
update teacher
set salary=1000.45,
name=‘dave’
where id = 1;
会将符合where条件的所有记录对应的字段都更新为新值
select * from 表名 where xxx = xxx; 查询

注意：无论是delete还是update，只要你确定不是全表操作，就一定要使用where条件！！！

DDL-增强
上面讲的DDL只是一系列基础操作，它让我们有库有表可以插入数据。但是对于插入的数据是否是有效数据，并不能保证。比如我们可以插入一条所有字段都是NULL的记录：
命令insert into student(id,name,score) values (null,null,null);
这种记录白白地占用了我们的存储空间，但是实际上并没有用处，为了防止表中插入这种数据，MYSQL提供了一系列的完整性验证机制。

实体完整性：主键
主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL的。
主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）
建表时添加主键：
create table person2(
id int not null default 0,
name varchar(100),
income decimal(18,2),
primary key (id,name,income)
);
建表后通过修改表结构添加主键：
alter table person add primary key(id,name,income);
如果存储引擎使用的是InnoDB，那么在select * from person;时，显示出的结果是有序的。排序的规则是：先按id升序，如果id相同，再按name升序，如果id,name相同，再按income升序。依此类推。而如果存储引擎是MyISAM，则依然是按记录插入的顺序显示。

关联完整性：外键
（概念，配置外键的前提条件，外键是怎么发挥作用的）
外键，顾名思义，外部主键。它通常用于将2个表的数据进行关联，以防止不当的数据插入/修改/删除。
比如，表student(id,name,score,teacher_id)与表teacher(id,name,salary)，我们通过建立外键，使student表中的学生与teacher表中的教师建立数据完整性的关联。
主表为teacher表，id是它的主键。
从表为student表，teacher_id是它的外键，用于与teacher.id字段做数据完整性关联。
配置外键时，一定要保证：
两个表的存储引擎必须是InnoDB。（如何修改表的存储引擎见DDL-简单中对存储引擎的描述）
两个表的字段teacher.id与student.teacher_id的数据类型一定要一致。
主表teacher的字段id一定要设置为主键。
建表时添加外键：
create table student(
id int not null default 0,
name varchar(100) not null default ‘’,
score decimal(18,2) not null default 0.0,
teacher_id int not null default 0,
primary key (id),
foreign key (teacher_id) references teacher(id)
) engine=InnoDB;
建表后添加外键：
alter table student add foreign key (teacher_id) references teacher(id);

其他约束：not null，default <默认值>。通常用在非主键的其他字段上。
约束not null保证字段不能有NULL，通常和default <默认值>联用。
在建表的时候添加约束：
create table person2(
id int not null default 0,
name varchar(100),
income decimal(18,2)
);
建表后通过修改表结构添加约束：
alter table person modify id int not null default 0;

所以，一个比较标准且能保证数据完整性的建表语句应该是：
create table student3(
id int not null default 0,
name varchar(100) not null default ‘’,
score decimal(18,2) not null default 0.0,
teacher_id int not null default 0,
primary key (id)
) engine=InnoDB default charset=utf8;
DQL-简单
DQL，Data Query Language，数据查询语言。它通常用来从一张表或者多张表（视图或者子查询等）中按指定的条件筛选出某此记录。涉及到的命令有select。
最简单粗暴的一条select命令就是select * from teacher;
里面的*代表查出所有字段。如果我们只查询指定字段，我们可以这么用
命令select id, name from teacher;

或者，use mysql;
select host, user, password from user;

查询全表所有记录，但是只显示我指定的字段。
如果我们只想查询某些记录，不想查全表（因为全表扫描是相当低性能一种操作），我们就要使用where条件了。
select host, user, password from user where user=‘root’ and host=‘localhost’;

MySQL常用系统函数
系统信息类
如select version();
如select database();
字符类
如select char_length(‘中国’);
如select length(‘中国’);
如select concat( ‘a’, ‘b’, ‘c’, ‘d’);
如select concat_ws( ‘=’, ‘a’, ‘b’, ‘c’);
如select upper(‘abcd’);
如select lower(‘ABCD’);
如select substring( ‘系统信息类’, 1, 3 );
如select trim(’ abc ');

日期时间类
select curdate();
select curtime();
select now();
select unix_timestamp();
select unix_timestamp(‘2018-05-24 20:00:00’);
select from_unixtime(1527163397);
select datediff( ‘2018-05-23’, now() );
select adddate( now(), -2 );

条件判断类
select if( <判断条件>, <条件为真时的返回值>, <条件为假时的返回值> );
如select if(1=1, 2, 3);
select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);

CASE WHEN THEN END语句第一种使用场景： 直接 case
示例数据 :
create table test_01 (
id int,
name varchar(20),
score decimal(18,2)
);
insert into test_01(id,name,score) values (1,‘小红’,99.8);
insert into test_01(id,name,score) values (2,‘小明’,23);
insert into test_01(id,name,score) values (3,‘小刚’,67);
insert into test_01(id,name,score) values (4,‘小黑’,45.32);

DQL-单表查询
常用的where条件
查询语句select * from student where A and B;筛选student表中既满足条件A又满足条件B的记录。
而select * from student where A or B;筛选的则是只要满足A或者B其中一个条件即可的记录。
命令select * from student where score > 80;筛选的是student表中score字段值大于80的记录。
大于等于使用 >=。小于等于使用 <=。小于使用 <。等于使用 =。不等于使用 <>。
命令select * from student where score>=60 and score<=80;等价于
命令select * from student where score between 60 and 80;
命令select * from student where score=10 or score=20;等价于
命令select * from student where score in (10, 20);

对score in (10, 20);的逻辑否是 score not in (10, 20);
命令select * from student where score is null;筛选student表中score字段值为null的记录。对is null的逻辑否是 is not null。

模糊查询 like ‘%%’
需求 : 把名字中 第一个字为’小’的查询出来
需求 : 把名字中 第一个字为’小’ 的,并且名字只有两个字的, 查询出来

去重distinct

select name from test_01;
select distinct name from test_01;

排序order by 字段名 [desc|asc]
降序 : desc
升序 : asc
需求 : 按照成绩 降序

需求 : 先按成绩降序 如果成绩相同 再按id升序

限制条数limit 条数。通常与order by 配合使用,因为我以某个条件升序/降序之后,再去前几条数据,才有意义
求成绩在前三名的学生

还可以做分页使用
从第一条开始 去两个 ,不包含第一行
select * from test_01 limit 1,2;

SQL语句
DQL单表查询
统计函数/聚合函数/分组查询
MYSQL中有一类特殊的函数，用于统计，或者分组统计。常用的有求数量的count(1)（等价于count()），求最大值的max(字段名)，求最小值的min(字段名)，求平均值的avg(字段名)，求总和的sum(字段名)。
如select count() from student;用于计算student表中一共有多少条记录。
而select count(*) from student where A;用于计算student表中满足条件A的记录的条数。

select teacher_id, count(*) as stu_count from student group by teacher_id;用于展示每个老师带了几个学生。老师0带了3个学生，老师2带了1个学生。

select teacher_id, count(*) as stu_count,max(score) as stu_max_score from student group by teacher_id;用于展示每个老师带的学生中的最高成绩。

select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id;用于统计每个老师所带学生的总成绩与平均分。

如果我们想基于统计结果，再做进一步的筛选，就要使用having关键字了。它与where不一样，这里要注意。
select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id having avg>=60;用于统计每个老师所带学生的平均成绩大于等于60分的记录。

DQL-子查询
子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面。3种使用场景。
场景一：

上面我们讲到了分组查询，可以查询每个老师所带学生的最低分，
但是我们刚才查出来之后,我们只能看到teacher_id,但是我们并不知道teacher_id对应的是那个老师,这个时候我们最好是显示老师的名字是比较好的,可以用子查询实现

需求 : 查询所有学生的信息并显示老师的名字
select *,(
select name from teacher where id=teacher_id
) as teacher_name from student ;

需求 : 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字

当位于SELECT后面时，要注意
1.一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）
2.子查询中只能有一个字段（子查询的结果必须是一行一列）

使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表.

场景二：
还是学生表student，我们要将成绩进行分级，并且显示汉字的分级与字母的分级。这里可以使用子查询。相当于给student“新增”了2个字段。
需求 : 使用子查询 对成绩划分等级, score<60 ,评级C 并且是差,score>=60 且 score<80 评级B并且是良,score>=8 评级是A并且是优
select *,
case rank
when ‘A’ then ‘优’
when ‘B’ then ‘良’
when ‘C’ then ‘差’
end rank_ch
from (
select *,
case
when score < 60 then ‘C’
when score >=60 and score <80 then ‘B’
when score >=80 then ‘A’
end as rank
from student
) a;

当位于FROM后面时，要注意
1.我们可以把子查询当成一张表
2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

场景三：
需求 : 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
select * from student where teacher_id in (
select id from teacher where name=‘张老师’
);

当位于WHERE后面时，要注意
1.多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
2.如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列
3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

DQL-union与union all
合并查询
需求 : 查询出 teacher_id = 1 的所有学生信息

需求 : 查询出 学生分数大于60的所有学生信息

需求 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)

需求 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(可重复)

union / union all
它俩的作用是把两张表或者更多表合并成一张表
前者会去重（去重的依据是，UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）
后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），
尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力

使用union / union all的时候要注意：
1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）
2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）
3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则）

DQL-连接查询
笛卡尔乘积,也有的叫笛卡尔积。
多表查询中,链接的where限定条件,不能少于 表的个数-1 , 否则就会发生笛卡尔乘积
笛卡尔乘积是一个很消耗内存的运算，笛卡尔积产生的新表，行数是原来两个表行数的乘积，列数是原来两个表列数的和。所以我们在表连接时要使用一些优化手段，避免出现笛卡尔乘积。
优化一：使用等值连接条件。
优化二：能使用inner join的就不要使用left join。
优化三：使用记录数更少的表当左表。
但是如果业务上有要求：
比如，我们有一张用户的基本信息表，我们还有一张用户的订单表
现在我们要求在页面上展示，所有用户的订单记录
这种情况下我们就必须使用left join了，因为inner join 会丢数据
假设基本信息表中有A B C三个用户（3条记录）
订单表中有A B两个人的100条订单记录
这种情况下，我们除了使用left join外，还必须要让基本信息表当左表，订单表当右表。
MYSQL支持的表连接查询有inner join，left join，right join（right join我们工作中基本不用）。
先看inner join
1
select *
from teacher tea
inner join student stu on tea.id = stu.teacher_id;

2
select *
from student stu
inner join teacher tea on tea.id = stu.teacher_id;

再看left join
1
select *
from teacher tea
left join student stu on tea.id = stu.teacher_id;

2
select *
from student stu
left join teacher tea on tea.id = stu.teacher_id;

最后我们看right join

1
select *
from teacher tea
right join student stu on tea.id = stu.teacher_id;

2
select *
from student stu
right join teacher tea on tea.id = stu.teacher_id;

以上可以看出，student right join teacher 显示的内容是与teacher left join student相同的。而teacher right join student 显示的内容又与student left join student相同。
所以，我们工作中，right join基本不用。用的最多的是inner join 与left join。
PS：外键与表连接没有任何关系，不要混淆。
外键是为了保证你不能随便删除/插入/修改数据，是数据完整性的一种约束机制。
而表连接是因为一张表的字段无法满足业务需求（你想查的字段来自于2张甚至多张表）
一个是为了增删改，一个是为了查，它俩之间没有联系。

数据导出导入
备份/导出 : 右键表/库 —> 转储SQL文件 —> 结构和数据

恢复/导入 : 右键 —> 运行SQL文件 就行
行转列
示例数据
有 id,名字,课程,成绩
create table test_9(
id int,
name varchar(22),
course varchar(22),
score decimal(18,2)
);
insert into test_9 (id,name,course,score)values(1,‘小王’,‘java’,99);
insert into test_9 (id,name,course,score)values(2,‘小张’,‘java’,89.2);
insert into test_9 (id,name,course,score)values(3,‘小李’,‘java’,88);
insert into test_9 (id,name,course,score)values(4,‘小王’,‘MySQL’,92.2);
insert into test_9 (id,name,course,score)values(5,‘小张’,‘MySQL’,42.2);
insert into test_9 (id,name,course,score)values(6,‘小李’,‘MySQL’,59.2);

根据我们以上的数据来说,如果我执行一下 select * from test_9;得到

可是我想要的结果是这样…

通过SQL语句 实现这样的功能,就叫行转列
场景一（多行转为一行多列）：

可以使用下面的SQL语句（group by 与 case when结合使用即可实现）：
select name,
max(
case course
when ‘java’ then score
else 0
end
) Java,
max(
case course
when ‘MySQL’ then score
else 0
end
) MySQL
from test_9
group by name;
思路分析 :
首先我们默认的情况 每个名字都输出两次,而最终结果只有一次名字,所以肯定是 以名字分组 group by

select * from test_9 group by name;

对我们来说 ,id,课程,和分数都不需要了,只需要有名字 然后再把java和mysql放上去
select name , 1 as java , 1 as MySQL from test_9 group by name;

然后再使用聚合函数聚合（此处理解“聚合”，相当于把多行数据压扁成一行）
select name,
max(
case course
when ‘java’ then score
else 0
end
) Java,
max(
case course
when ‘MySQL’ then score
else 0
end
) MySQL
from test_9 group by name;

场景二（多行转为一行一列）：

concat(值,’拼接符’,值 ) : 拼接,多行数据只会拼接一行
group_concat(值,’拼接符’,值 ) : 拼接,多行压扁到一行

第一步：拆分问题，先按分组的思路

select name,1 as ‘各科成绩’ from test_9 group by name;

第二步：将课程名与成绩拼接成一列
select name,
concat(course,’=’,score) as ‘各科成绩’
from test_9 group by name;

第三步：利用group_concat函数将多行压扁到一行
select name,
group_concat(course,’=’,score) as ‘各科成绩’
from test_9 group by name;

第四步：修改分隔符（默认是逗号）
select name,
group_concat(course,’=’,score separator ’ | ') as ‘各科成绩’
from test_9 group by name;

第五步：按课程名称排序
select name,
group_concat(course,’=’,score order by course asc separator ’ | ') as ‘各科成绩’
from test_9 group by name;