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:SS
2-查看表
-- 查看某个数据库中的所有表
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张表的乘积数据,简称笛卡尔积数据,有关系的数据和没关系的数据都查出来了