SQL语句、DQL查询语句、DQL查询语句格式、数据库约束、多表查询

SQL语句

什么是SQL：

Stuctrued Query Language   结构化查询语言。sql语句就是对数据库进行操作的一种语言。

SQL的作用：

用来操作数据库，数据表以及表中的数据。执行增删改查操作（CRUD）

SQL语句分类：

DDL: Data Definition Language  数据定义语言

• 主要用于增删改数据库或者是表结构的。
• 关键字：create/drop/alter

DML: Data Manipulation Langauage   数据操作语言

• 用来对数据库中表的数据进行增删改。
• 关键字： insert/update/delete

DCL：Data Control Language   数据控制语言

• 用来定义数据库的访问权限和安全级别，及创建用户。
• 关键字：grank/revoke

TCL ：Transaction Control Language 事务控制语言

• 用于控制数据库的事务操作。
• 关键字：commit/savepoint/rollback/set transaction

DQL：Data Query Language 数据查询语言

• DQL语言并不是属于MYSQL官方的分类，但是对数据库的操作最多就是查询，所以我们把查询语句的语言称作为DQL查询语言。
• 关键字：select

SQL是数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容：

SQL通用语法：

1.  SQL语句可以单行或多行书写，以分号结束。
2. 可使用空格和缩进来增强语句的可读性。
3. SQL语句中的关键字不区分大小写，关键字建议使用大写。
4. MYSQL中的注释，注释有两种。

单行注释：

• #  MYSQL特有。
• --  所有数据库共有的语法。

多行注释：

• /*  注释内容  */

DDL

DDL操作数据库

创建数据库：

1）直接创建数据库

• create database 数据库名;

2）创建数据库时判断是否存在，不存在则创建

• create database if not exists 数据库名;

3）创建数据库同时指定编码方式

• create database 数据库名 default character set gbk;
• 注意：数据库中，只承认utf8,不承认utf-8

查看数据库：

1）查看所有数据库

• show databases;

2）查看某个数据库的定义信息

• show create database 数据库名;

修改数据库：

修改数据库字符集格式：

• alter database 数据库名 default character set 字符集;
• 数据库不能改名字

删除数据库：

删除指定某个数据库：

• drop database 数据库名;

使用数据库：

1）使用/切换数据库

• use 数据库名；

2）查看正在使用的数据库

• select database();

DDL操作表

• 前提先使用某个数据库

创建表：

create table 表名( -- 表名都是小写字母，如果是多个单词则使用_分割，比如：student_info
字段名1 数据类型,
字段名2 数据类型,
.......
字段名n 数据类型 -- 最后一个不能有逗号
);

示例代码：

-- 创建 student 表包含 id,name,sex, birthday
create table student(
id int,
name varchar(20),
sex char(1),
birthday date
);

常用的类型：

分类	类型名称	说明
整数类型	tinyInt	很小的整数 (占8位二进制)
	smallint	小的整数 (占16位二进制)   shor
	mediumint	中等大小的整数 (占24位二进制)
	int(integer)	普通大小的整数 (占32位二进制)   int
小数类型	float	单精度浮点数 , 单精度的浮点型保留的小数位7位。
	double	双精度浮点数, 双精度浮点型保留的小数位： 15位 用法一： salary  double   最多保留15位小数位 用法二： salary  double(5,2) salary总长是5位，其中小数位占2位.
	decimal（m,d）	压缩严格的定点数
日期类型	year	YYYY 1901~2155
	time	HH:MM:SS -838:59:59~838:59:59
	date	YYYY-MM-DD 1000-01-01~9999-12-3  只会保留日期
	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59  保留日期与时间
	timestamp	YYYY-MM-DD HH:MM:SS 1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC   时间戳，保留日期与时间。  如果timesamp不插入数据的情况下，默认会使用当前的系统时间作为当前的值。datetime是会使用null值。
文本	CHAR(M)	固定长度,为0~255之间的整数
	VARCHAR(M)	可变长，为0~65535之间的整数
二进制类型	TINYBLOB	允许长度0~255字节
	BLOB	允许长度0~65535字节
	MEDIUMBLOB	允许⻓度0~167772150字节
	LONGBLOB	允许⻓度0~4294967295字节
	TINYTEXT	允许⻓度0~255字节
	TEXT	允许⻓度0~65535字节
	MEDIUMTEXT	允许⻓度0~167772150字节
	LONGTEXT	允许⻓度0~4294967295字节
	VARBINARY(M)	允许⻓度0~M个字节的变长字节字符串
	BINARY(M)	允许⻓度0~M个字节的定长字节字符串

查看表：

1）查看某个数据库中的所有表：

• show tables;

2）查看表结构：

• desc 表名；

3）查看创建表的SQL语句：

• show create table 表名；

快速创建一个表结构相同的表：

• create table 新表名 like 旧表名;

删除表：

1）直接删除表

• drop table 表名；

2）判断表是否存在并删除表

• drop table if exists 表名；

修改表结构：

1）添加表列 ADD

• Alter table 表名 add 字段名 数据类型;

2）修改列类型 MODIFY

• alter table 表名 modify 字段名 数据类型;

3）修改列名和类型 CHANGE

• alter table 表名 change 旧字段名 新字段名 数据类型;

4)删除列DROP

• alter table 表名 drop 字段名;

5）修改表名

• rename table 旧表名 to 新表名;
• alter table 表名 rename to 新表名；

6）修改字符集

• alter table 表名 default character set 字符集;

DML语句

插入记录：

1）插入一个表中指定列的数据

• insert into 表名(字段1,字段2....) values(值1,值2...);
• 没有添加数据的字段会使用null。

2）插入一个表中的所有列数据

• -- 所有的字段名都写出来
  insert into 表名(字段1,字段2....) values(值1,值2...);
  -- 不写字段名
  insert into 表名 values(值1,值2....);

拓展：

获取插入语句主键值

• select last_insert_id();

注意事项：

1. 值与字段必须对应，个数相同，类型相同。
2. 值的数据大小必须在字段的长度范围内。
3. 除了数值类型外，其他的字段类型的值必须使用引号引起。（单引号，双引号都可以，推荐使用双引号）
4. 如果要插入空值，可以不写字段，或者插入null。

蠕虫复制：

把一个表中的数据完全复制到另一个表结构相同的表中：

• insert into 新表表名 select * from 旧表表名;

更新表记录：

• 字段名=值 ，如果有多个，则用逗号分割。例：字段名1=值1 ，字段名2=值2 
• 如果值为空，不能用=null，要用 is null

1）不带条件修改数据

• update 表名 set 字段名=值;

2）带条件修改数据

• update 表名 set 字段名=值 where 字段名=值;

删除表记录：

1）不带条件删除数据

• delete from 表名;

2）带条件删除数据

• delete from 表名 where 字段名=值;

3）使用truncate删除表记录

• truncate 表名;

truncate和delete的区别：

1. delete是将表中的数据一条一条删除，不影响表结构。
2. truncate是将整个表摧毁，重新创建一个新的表，新的表结构和原来表结构一模一样。

DQL语句

• 查询不会对数据库中的数据进行修改.只是一种显示数据的方式。

简单查询：

1）查询指定列

• select 字段名1，字段名2... from 表名;

2）查询所有列

• select * from 表名;
• * 表示所有列

别名查询：

• 注意：as 关键字可以省略

1）对列指定别名的语法

• select 字段名 as 别名, 字段名 as 别名,... from 表名;

2）对列和表同时指定别名的语法

• select 字段名 as 别名, 字段名 as 别名,... from 表名 as 别名;

清除重复值：

• 查询指定列并且结果不出现重复数据。

• select distinct 字段名,.. from 表名;
• 注意：字段名可以有多个，用逗号分隔。

查询结果参与运算：

• 注意：参与运算的必须是数值类型。

1）某列数据和固定值运算

• SELECT 列名1 + 固定值 FROM 表名;

2）某列数据和其他列数据参与运算

• SELECT 列名1 + 列名2 FROM 表名;

运算符：

• 等于： = （与java不一样，没有==，只有=）
• 不等于：  <>、!=  （两种写法）
• 与：&& （DQL语句没有&）
• 或：|| （DQL语句没有 |）

in关键字：

• SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...);
• in里面的每个数据都会作为一次条件，只要满足条件的就会显示。

范围：

• SELECT 字段名 FROM 表名 WHERE 字段 BETWEEN 值1 AND 值2；
• 表示从值1到值2范围，包头又包尾。

like（模糊查询）：

• SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
• %: 表示0个或多个字符。（任意个字符）
• _:  表示一个字符。

排序：order by

• 排序只是显示方式，不会影响数据库中数据的顺序。

• SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
• asc：升序（默认）
• desc：降序

组合排序：

• 先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。

• WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

聚合函数：

count：统计指定列记录数，记录为null的不统计。

sum：计算指定列的数值和，如果不是数值类型，那么计算结果为0。

max：计算指定列的最大值。

min：计算指定列的最小值。

avg：计算指定列的平均值，如果不是数值类型，那么计算结果为0。

• SELECT 聚合函数(列名) FROM 表名;

对聚合函数的拓展：

ifnull(列名，值)

• 如果查询的结果出现了null值，那么使用ifnull里的 值 代替，该 值 可以使用与原来的列数据类型不同的数据类型。

使用ifnull与聚合函数嵌套查询的示例代码：

select count(ifnull(english,0)) as 总人数 from student;

分组（group by）

• group by 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没有什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

• SELECT 字段1,字段2...FROM 表名 GROUP BY 分组字段 [HAVING 条件];

​​​​​​​注意事项：

1. 分组后聚合函数操作不再是行数据，而是组数据了。
2. 对于分组后的条件需要使用having子句。
3. having语句中的每一个非聚合函数的元素都必须出现在select列表中，有点数据库例外，如Oracle。

​​​​​​​​​​​​​​having与where的区别：

1. having是在分组后对数据进行过滤。
2. where是在分组前对数据进行过滤。
3. having后面可以使用聚合函数。
4. where后面不可以使用聚合函数。

limit语句：

• 限制查询记录的条数。

• ​​​​​​​select 列名 from 表名 where 条件 limit 跳过多少行, 返回的行数;

​​​​​​​注意：​​​​​​​

1. 如果第一个参数是0，可以简写。
2. 返回的行数太大的话，不会报错，有多少显示多少。

DQL查询语句格式

select * from 表名 [where子句] [group by子句][having子句][order by子句][limit子句];

数据库约束

约束的作用：

​​​​​​​对表中的数据进行限制，保证数据的正确性，有效性，完整性。

约束的种类：

约束名	约束关键字
主键约束	primary key
唯一约束	unique
非空约束	not null
默认值约束	default 默认值
外键约束	foreign key...
检查约束	MYSQL不支持，Oracle支持

主键约束

主键的关键字：

• primary key

主键的作用：

• 在实际开发中其实数据库表中的每一条记录都应该具备唯一的标识符，主键的作用就是标记每一条数据的唯一性。
• 主键不具备任何的业务含义。

哪个字段应该作为表的主键？

• 通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终客户使用的。所以主键有没有含义没有关系，主要不重复，非空就行。

主键的特点：

1. 唯一
2. 非空
3. 被引用

在创建表的时候给字段添加主键：

• 字段名 字段类型 PRIMARY KEY

在已有表中添加主键：

• ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除主键：

• alter table 表名 drop primary key;

主键自增长

概述：

• 主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值。
• 自增也属于一种类型。

自增长关键字：

• auto_increment​​​​​​​

在创建表的时候设置主键，并且让表的主键自增：

• 字段名 字段类型  primary key auto_increment;

后期添加主键自增：

• alter table 表名 modify 字段名 字段类型 auto_increment;

后期取消主键自增：

• alter table 表名 modify 字段名 字段类型;

​​​​​​​DELETE和TRUNCATE的区别：

• DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。
• TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1。

扩展 :

• 默认地AUTO_INCREMENT 的开始值是1，如果希望修改起始值

• ​​​​​​​ALTER TABLE 表名 AUTO_INCREMENT=起始值;

唯一约束

什么是唯一约束：

• 用来约束某一列的值不能重复。

唯一约束的基本格式：

• 字段名 数据类型 unique;

出现多个null的时候会怎样？

• 用为null是没有值，所以不存在重复的问题。

非空约束

什么是非空约束：

• 用来约束某一个列的值不能为null。

非空约束的基本格式：

• 字段名 数据类型 not null ;

如果一个字段设置了非空与唯一约束，该字段与主键的区别：

1. ​​​​​​​一张表只有一个字段可以设置为主键。
2. 一张表中可以多个字段非空与唯一约束。
3. 主键可以自动增长，非空与唯一约束的字段不能自动增长。
4. 主键可以被引用。

注意事项：

• 唯一约束、非空约束都使用，没有前后顺序。

默认值约束

什么是默认值约束：

• 用来约束某一个值如果没有赋值时使用默认值。

默认值约束的基本格式：

• 字段名 数据类型 default 默认值 ;

外键约束

单表的缺陷：

表中出现了很多重复的数据（数据冗余）。

• 解决方案：将一张表分成两张表。

什么是外键约束：

一张表中的某个字段引用另一个表的主键。

• 主表：约束别人
• 副表\从表： 使用别人的数据，被别人约束。

外键约束的作用：

• ​​​​​​​在实际开发中我们有些表的数据是要受到另一个表的数据的约束的。这时候我们就可以使用外键约束。

新增表时增加外键：

• [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
• 外键的名称是可以随意的名字，但是一般都是FK开头或者结尾。

已有表增加外键：

• [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

删除外键：

• alter table 表名 drop foreign key 外键名称;

外键的级联

什么是级联操作：

• 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。

外键的级联关系：

1. 删除
2. 更新

级联更新，主键发生更新时，外键也会更新：

• on update cascade

级联删除，主键发生删除时，外键也会删除：

• on delete cascade

可以同时使用，中间不需要逗号隔开：

• on update cascade on delete cascade

多表查询

什么是多表查询：

从多张表中查询数据的过程。

多表查询的分类：

• 内连接查询

1. ​​​​​​​隐式内链接
2. 显式内链接

• ​​​​​​​外连接查询

1. ​​​​​​​左外连接
2. 右外连接

笛卡尔积现象

概述：

• 多表查询的时候，A表的每一条数据都会与B表的每一条数据组合，查询数量等于多张表的记录数之积则称为笛卡尔积。

如果清除笛卡尔积现象：

清楚笛卡尔积现象需要我们添加条件过滤： 从表.外键 = 主表.主键

• 多表查询时，清楚笛卡尔积条件的个数 = 表数量 - 1

内链接

内链接概述：

左边的记录和右边的记录进行匹配，只有满足条件的记录才会查询出来。

• 一般查询条件： 从表.外键 = 主表.主键

内链接分类：

1. ​​​​​​​隐式内链接
2. 显式内链接

隐式内链接：使用where语句连接条件

• select 字段 from表1，表2... where 清楚笛卡尔积的条件 and 其他条件；

显式内链接：inner join 表 on 条件

• select 字段 from 表1 inner join 表2 on 清楚笛卡尔积的条件 and 其他的条件;

显示内链接和隐式内链接哪一个的速度比较快？

• 显示内链接的查询速度更加快。本质在于on与where的区别，on编写条件是先限制条件，再取数据。where是先查询再过滤。

当使用 显式内链接或外连接时，每连接一个表就要立刻用on清除笛卡尔积：

select 字段 from 表1 inner join 表2 on 条件
                    inner join 表3 on 条件
                    ...;

总结多表连接查询步骤：

1. 先确定要查询的表。
2. 然后再确定要查询的字段。
3. 最后确定查询的条件。

外连接

外连接的分类：

1. 左外连接
2. 右外连接

左外连接

概述：

• 使用左表的每一条数据与右边的每一条数据组合，不管条件是否满足，左表的数据都会显示出来。

语法：

• select 字段 from 表1 left join 表2 on 清除笛卡尔积的条件 and 其他条件；

右外连接

概述：

• 使用右表的每一条数据与左边的每一条数据组合，不管条件是否满足，右表的数据都会显示出来。

语法：

• select 字段 from 表1 right join 表2 on 清除笛卡尔积的条件 and 其他条件；

拓展：如果想要左外连接和右外连接一起使用：

• 可以使用union合并结果集。

union作用：

• 会把两个sql语句的查询结果合并，如果是重复的数据只会显示一次。

当使用 显式内链接或外连接时，每连接一个表就要立刻用on清除笛卡尔积：

select 字段 from 表1 left join 表2 on 条件
                    inner join 表3 on 条件
                    ...;

子查询

子查询概述：

• 一条查询语句的执行结果作为另一条查询语句的条件或结果使用则称为子查询。
• 内部的查询是子查询，外面的查询是父查询。

子查询的分类：

1. 单列单行子查询
2. 单列多行子查询
3. 多行多列子查询

说明：

• 自查询结果只要是单列，肯定在where后面作为条件。
• 子查询结果只要是多列，肯定在from后面作为表。
• 子查询语句必须使用小括号括起来。

单列多行子查询

概述：

• 如果子查询的结果是一个值（单列）的时候，那么子查询一定会出现在where条件后面，并且往往是使用比较运算符。

格式：

select 查询字段 from 表 where 字段 = (子查询);

单列多行子查询

概述：

• 如果子查询的结果是单列多行的时候，那么子查询的结果应该在where条件后面，并且使用in语句。

格式：

select 查询字段 from 表 where 字段 in (子查询);

多行多列子查询

概述：

• 如果子查询的结果是多行多列，那么这就是一张表，这种子查询我们都应该放在from后面。

格式：

select 查询字段 from(子查询) 表别名 where 条件；

注意：

• 子查询作为表需要取别名，否则这张表没有名称无法访问表中的字段。

总结：

• 看查询表中的一个表里是否包含另一个表的内容，若包含，则多行多列，否则单列。