SQL语言
概述与作用
概述：结构化查询语言(Structured Query Language)简称SQL，SQL语句就是对数据库进行操作的一种语言。
作用：通过Sql语言可以对数据库管理系统中的数据库，表，表中的数据进行增删改查。
sql分类和语法
分类：
- DDL（Data Definition Language）数据定义语言
用来操作数据库和表
关键字：create,drop,alter等
- DML（Data Manipulation Language）数据操作语言
用来对数据库中表的数据进行增删改
关键字：insert,delete, update等
- DQL（Data Query Language）数据查询语言
用来对数据库中表的数据进行查询
关键字:select，from，where等
- DCL（Data Control Language）数据控制语言（了解）
用来定义数据库的访问权限和安全级别，及创建用户。
关键字：grant， revoke等
- TCL（Transaction Control Language） 事务控制语言
用于控制数据库的事务操作
关键字: commit，rollback等
sql语法：
- SQL语句可以单行或多行书写，以分号结尾
- 可使用空格和缩进来增强语句的可读性
- QL关键字本身不区分大小写。表，列等的名称具有与数据库相关的区分大小写
- 可以使用 -- 或 # 或 /**/ 的方式完成注释
DDL（数据定义语言）
操作数据库
1-创建数据库
-- 创建数据库。默认：utf-8
create database 数据库名;
-- 判断是否存在并创建数据库。存在：不创建；不存在：创建。默认：utf-8
create database if not exists 数据库名;
-- 创建数据库指定字符集。一般不用
create database 数据库名 character set 字符集(gbk);2-查询数据库
-- 查询所有数据库
show databases;
-- 查看某个数据库的定义信息
show create database 数据库名称;3-删除数据库
-- 删除数据库
drop database 数据库名;4-数据库的其他操作
-- 查看当前使用的数据库
select database();
-- 切换数据库。注意：如果想要操作哪个库（创建表），就得先进入到该数据库中
use 数据库名;操作表
注意：如果想要操作哪个库（创建表），就得先进入到该数据库中：use 数据库名；
1-创建表（重要）
-- 如果表存在就删除该表
drop table IF EXISTS 表名;
-- 创建数据库表，同时定义表列属性。 -- ps：表名会有关键字 解决：换名。
create table 表名(
字段名1 字段类型1(长度), -- ps：只有字符串类型需要手动加长度，其它类型都不需要加长度(默认有)
字段名2 字段类型2(长度)
.....
)
-- 创建空表
create table 表名;
-- 示例: 创建一个学生表(id name sex)
create table stu(
id int,
name varchar(10),
sex varchar(2),
price double,
time date
)
-- 数据类型
java的数据类型 mysql数据库的数据类型
int int
float float
double double
char/string varchar
date datetime YYYY-MM-DD HH:MM:SS
date yyyy-MM-dd
time HH:MM:SS2-查看表
-- 查看某个数据库中的所有表
show tables;
-- 查看表结构
desc 表名称;
-- 查询建表语句
show create table 表名;
-- 快速创建表结构。特点：2张表的结构一样
create table 新表名 like 旧表名;3-修改表结构
-- 添加表列
alter table 表名 add 列名 类型;
-- 删除表列
alter table 表名 drop 列名;
-- 修改列名
alter table 表名 change 旧名称 新名称 类型;
-- 修改列类型
alter table 表名 modify 列名 新类型
-- 修改表名称
rename table 表名 to 新表名;4-删除表
-- 直接删除表
drop table 表名;
-- 当指定表名存在时删除该表。常用在初始化数据库建表前
drop table if exists 表名; DML（数据操作语言）
1-插入记录
-- 添加部分字段
insert into 表名 (字段名1, 字段名2, ...) values(值1, 值2, ...);
-- 按列定义顺序添加全部字段
insert into 表名 values (值1, 值2, 值3); 注意事项：
- 值与字段必须对应，个数相同，类型相同
- 值的数据大小必须在字段的长度范围内
- 除了数值类型外，其它的字段类型的值必须使用引号引起。
- 如果要插入空值，可以不写字段，或者手动插入null
2-蠕虫复制
-- 将表2中数据复制插入到表1中。前提：表结构得一致
insert into 表名1 select * from 表名2;3-修改记录
-- 根据条件修改数据。特点：按条件改
update 表名 set 字段名1 = 值1, 字段名2 = 值2 where 字段名=值 ;
-- 不带条件修改数据。特点：表中相应字段全改
update 表名 set 字段名1 = 值1, 字段名2 = 值2 ;4-删除记录
-- 根据条件删除数据。特点：按条件删
delete from 表名 where 字段名=值;
-- 不带条件删除数据。 特点：全删
delete from 表名; -- truncate删除表记录。
truncate table 表名; truncate和delete的区别：
- delete是将表中的数据一条一条删除。truncate是将整个表摧毁，重新创建一个新的表，新的表结构和原来表结构一模一样
- delete删除的数据能够找回。truncate删除的数据找不回来了
DQL（数据查询语言）
查询顺序、条件查询、运算查询
select....from...where..group by...having...order by...limit a,b 简单查询
-- 查询所有列
select * from 表名;
-- 查询指定列
select 字段名1,字段名2... from 表名;
-- 别名查询
select 字段名1 as 别名, 字段名2 as 别名... from 表名 as 表别名;
-- 清除重复值
select distinct 字段名 from 表名;
-- 查询结果参与运算。参数运算的字段必须为数值型
select 列名1 + 固定值 from 表名;条件查询：
select * from 表名 where 条件（各种运算符）; 运算查询：
> 大于
< 小于
<= 小于等于
>= 大于等于
= 等于
<> 、 != 不等于
and（&&） 多个条件同时满足
or（||） 多个条件其中一个满足
not（!） 不满足范围查询、模糊查询、排序查询
范围查询：
between 值1 and 值2
in(值1, 值2, 值3, ...)模糊查询 like
like
% : 模糊多位 通配符
_ : 模糊一位 通配符
is null
is not null排序查询 order by 字段
order by 字段名 [desc | asc] , 字段名 [desc | asc] ; -- asc(默认): 升序，desc: 降序聚合函数查询
count() : 计数
sum(字段) : 求和
max(字段) : 求最大值
min(字段) : 求最小值
avg(字段) : 求平均值
ifnull(字段, 备用值) : 第一个参数若不为NULL，则返回该值；若为NULL，则返回备用值，count(1)、count(*)与count(列名)的区别
执行效果：
- count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
- count(1) 包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
- count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
执行效率：
- 如果有主键，则 count(主键) 的执行效率是最优的
- 列名为主键，count(列名) 会比 count(1) 快
- 列名不为主键，count(1) 会比 count(列名) 快
- 如果表多个列并且有主键或索引，则 count(*) 系统会自动优化走主键或者索引
- 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(* )
分组查询、分页查询
分组查询
group by 分组字段 [having 条件]
-- 示例：查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示总人数大于2的数据
select sex, COUNT(*) from student where age>25 group by sex having COUNT(*)>2;having与where的区别
- having是在分组后对数据进行过滤；where是在分组前对数据进行过滤
- having后面可以使用聚合函数；where后面不可以使用聚合函数
分页查询
limit 跳过条数,查询条数;DCL（数据控制语言）
-- 创建用户
-- 命令格式：
CREATE USER '用户名'@'主机地址' IDENTIFIED BY '密码'; -- 默认没有任何权限 什么都做不了
-- 示例：
CREATE USER 'tom'@'localhost' IDENTIFIED BY '123';
-- 给用户分配权限
-- 命令格式：
GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
-- 示例：
GRANT ALL ON db3.* TO 'tom'@'localhost';
-- 查看权限
-- 命令格式：
SHOW GRANTS FOR '用户名'@'主机名';
-- 示例：
SHOW GRANTS FOR 'tom'@'localhost'
-- 撤销权限
-- 命令格式：
REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';
-- 示例：
REVOKE ALL ON db3.* FROM 'tom'@'localhost';
-- 删除用户
-- 命令格式：
DROP USER '用户名'@'主机名';
-- 示例：
DROP USER 'tom'@'localhost';
-- 给root用户修改密码
-- 特点：不需要登录 直接操作命令即可
-- 格式：
mysqladmin -uroot -p password 新密码
-- 给普通用户修改密码
-- 特点：需要root用户先登录 再使用命令去修改普通用户的密码
-- 格式：
set password for '用户名'@'主机名' = password('新密码');
-- 示例：
set password for 'aaa'@'localhost' = password('root');数据库约束
约束：对表中的数据可以进行进一步的限制，来保证数据的唯一性，正确性和完整性
约束种类：
- PRIMARY KEY ：主键约束。代表该字段的数据不能为空且不可重复
- NOT NULL ：非空。代表该字段的数据不能为空
- UNIQUE ：唯一。代表该字段的数据不能重复
主键约束：primary key
一个表中都得需要主键约束，用来标注一条记录的唯一性
特征：
- 主键字段值唯一不可重复
- 主键字段值不能包含NULL值
- 一个表中只能有一个主键，但主键可以是多个字段 (联合主键)
MySQL实现方式
添加主键
方式一：在创建表时，添加约束
格式：
create table 表名 ( 字段名称1 字段类型 primary key, 字段名称2 字段类型 约束 );方式二：在创建表时，结尾内添加约束
格式1：单一主键
create table 表名 ( 字段名称1 字段类型, 字段名称2 字段类型, primary key(字段名称1) );格式2：联合主键
create table 表名 ( 字段名称1 字段类型, 字段名称2 字段类型, primary key(字段名称1,字段名称2) );
删除主键
alter table 表名 drop PRIMARY KEY;主键自增
auto_increment -- 默认地AUTO_INCREMENT 的开始值是1
-- 修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;唯一约束：unique
特征：被修饰的字段唯一，不可重复
注意：一个表中可以有多个被unique修饰的字段，但对null不起作用
实现方式
-- 创建表时在字段后添加
create table 表名 (
字段名称1 字段类型 unique,
字段名称2 字段类型 约束
);非空约束：not null
特征：被修饰的字段不可为空
实现方式：直接在字段后面添加即可
create table 表名 (
字段名称1 字段类型 not null default '默认值', -- default：默认值
字段名称2 字段类型 unique not null default '默认值', -- 一个字段上可以同时出现唯一约束和非空约束的
字段名称3 字段类型 约束
);多表
多表的设计与实现
有三种：
- 一对一
实现：
1、让双方的主键作为外键一一对应
2 、在任意一方创建一个字段当成是外键指向另一方的主键，但是这个外键必须唯一
- 一对多
称一的一方为主表，称多的一方为从表
建立原则：只要是一对多，就在从表中（多的一方）创建一个字段为外键，然后让这个外键指向主表的（一的一方）主键
- 多对多
建立原则：需要在外部创建一张中间表。这个中间表至少需要2个字段，然后让这2个字段分别作为外键只向各自表的主键
多表约束（外键约束）
外键约束的作用：能够保证数据的完整性和有效性
特征：从表中如果关联了主表的数据则强制主表的数据不能删除，保证数据的完整性和有效性
外键的性能问题：
- 数据库需要维护外键的内部管理；
- 外键等于把数据的一致性事务实现，全部交给数据库服务器完成；
- 有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
- 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；
外键的使用策略：
- 在大型系统中（性能要求不高，安全要求高），使用外键；
在大型系统中（性能要求高，安全自己控制），不用外键；
小系统随便，一般不用外键。
- 用外键要适当，不能过分追求
- 不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后每个应用通过这个层来访问数据库。
添加外键约束
方式1：在已有表上添加外键约束
给从表的外键字段添加外键约束
alter table 从表 add constraint [外键别名] foreign key(外键字段) references 主表名称(主键字段)- 方式2：可视化图形方式
在架构设计器中，直接指定外键拖向主键即可，简单方便快捷
删除外键
方式一：
ALTER TABLE 从表 drop foreign key 外键名称;- 方式二：在架构设计器中，选中指向关系右键删除即可
多表查询
多表查询：从多张表中获取到有关系的数据。比如：查询分类信息以及分类下的所有商品
多表查询的语法分类：
- 内连接查询：只查询多张表之间有关系的数据
- 外连接查询
- 子查询
- 交叉查询：查询的是2张表的乘积（笛卡尔积），有关系的数据和没关系的数据都查出来了
内连接查询
特点：可以使用内连接去查询2张表之间有关系的数据
隐式内连接：
-- 语法格式： select * from 表1 表1别名,表2 where 关联条件 and 筛选条件; -- ps：可以使用表别名简化书写显示内连接
-- 语法格式： select * from 表1 [inner] join 表2 on 关联条件 where 筛选条件 and 筛选条件; -- on: 后跟的是关联条件 -- where: 后跟的是对结果的筛选条件
外连接查询
左外连接：以 join 左边的表为主，查询出来的是2张表之间有关系的数据以及左边表所有的数据。
如果右表中没有满足条件的对应数据，则填充 null
-- 语法格式： select * from 表1 left [outer] join 表2 on 关联条件 where 筛选条件;右外连接：以 join 右边的表为主，查询出来的是2张表之间有关系的数据以及右边表所有的数据
如果左表中没有满足条件的对应数据，则填充null
-- 语法格式： select * from 表1 rigth [outer] join 表2 on 关联条件 where 筛选条件;
子查询
语法格式：
-- 子查询作为where条件。适用于单列单值或者单列多值
select * from 表名 where (子查询结果);
-- 子查询作为临时表。适用于多列多值
select * from 子查询结果 where 条件;
-- 子查询作为结果集字段
select *,(子查询结果) from 表名 where 条件;示例：
子查询作为where条件 示例：查询工资最高的员工是谁（结果是单列单值）
-- 先查询最高的工资 select MAX(salary) from emp; -- 再查询最高工资的员工 select name from emp where salary=(select MAX(salary) from emp);子查询作为where条件 示例：查询工资大于5000的员工部门ID，来自于哪些部门（结果是单列多值）
-- 先查询工资大于5000的员工部门ID select dept_id from emp where salary>5000; -- 根据部门id获取部门名称 select name from dept where id in (select dept_id from emp where salary>5000);子查询作为临时表 示例：查询出2011-01-01以后入职的员工信息，包括部门名称（结果是多列多值）
select l.*,d.name from dept d,(select * from emp where join_date>'2011-01-01') l where d.id=l.dept_id;
交叉连接
select * from 表1,表2;会产生2张表的乘积数据，简称笛卡尔积数据，有关系的数据和没关系的数据都查出来了
