MySQL数据库
数据(Data)
数字、字母、图片、视频、音频等
元数据(MetaData)
数据的数据
数据库(DataBase)
存储数据的仓库
数据库管理系统(DBMS)
用来管理数据/数据库的一个系统(软件)，保证数据库的安全性、一致性、完整性。
常用的关系型数据库
mysql数据库
5.0版本、5.5版本、5.6版本、5.7版本、8版本
oracle数据库
sqlserver数据库
db2数据库
sybase数据库
SQL语言
结构化的查询语言，是用来操作关系型数据的标准语言
分类
DDL语句 (数据定义语句)：针对数据库对象的操作
create、 drop、alter
DML语句(数据操作语句)：对数据做增删改查操作，默认都是对表数据的操作
insert、update、delete
查询语句
select
DCL语句（数据控制语句）：对用户授权及删除权限
grant、revoke
事务控制语句
commit、rollback、savepoint
SQL语句写法：
1. SQL语句的标准语法中没有分号，在mysql的命令行中使用分号确定一条sql语句的结束。
2. SQL语句不区分大小写，包括：关键字、表名、列名，但是数据区分大小写
3. SQL语句虽然是标准的关系型数据库操作语句，但是在不同的数据库系统中SQL语句略有不同
SQL语句的数据类型
-
数值类型
整数类型：
int 类型：标准的整数 4字节
浮点类型：
float类型：单精度 4字节 限定整数及小数的位数： float (M,N) M位数字，N位小数
double类型：双精度 8字节 限定整数及小数的位数
decimal类型：长度和限定的位数相关的
-
字符串类型
char类型：定长字符串，字符串的长度一定是限制的长度。读写速度快，但占空间
varchar类型：变长字符串 ，读写速度比char慢，节省空间 最大长度 65535
text类型：文本串
blob类型：二进制 (图片、音频、视频)
-
日期时间类型
date类型：年月日
time类型: 时分秒
datetime类型：年月日时分秒
create语句
创建表的语法：
create table 表名 （
字段名 数据类型 [其他设置]
[ , 字段名 数据类型 [其他设置] ]
）[字符集和表引擎]
表名用英文，尽量表述其存储数据的含义
列的属性修饰：
-
自增长属性：如果为列设置了自增长属性，这列的插入数据为空时，列的值自动+1，内部维护了一个序号生成器，自增长属性的列一定是主键，类型一定是整数
employee_id int auto_increment, -
默认值 ：在定义列的后面 : default 值 ，为这列添加了默认值
workyear int default 0 -
二进制存储： 区分大小写，mysql在存储varchar时默认是不区分大写的
-
oracle不支持自增长属性，序列维护主键
约束
约束数据，当存储的数据违反了约束，不能存储，为了保证数据的完整性、一致性
1. 主键约束
主键的特点是唯一且非空，通过主键来确定唯一的一条记录每个表都要有主键约束。
create table student(
-- 存储的id 与业务无关，是程序员用的 1~9999999
sid int primary key,
sno int ,
sheight float(3,2),
sname varchar(3) ,
sex char(1),
age int,
tel varchar(11),
birthday date
)
create table student(
-- 存储的id 与业务无关，是程序员用的 1~9999999
sid int ,
sno int ,
sheight float(3,2),
sname varchar(3) ,
sex char(1),
age int,
tel varchar(11),
birthday date,
constraint pk_student primary key (sid)
)
推荐使用第二种写法
提示：Duplicate entry ‘X’ for key PRIMARY
2. 外键约束
外键是用来关联表和表的关系的，一个表的外键要关联到另一个表的主键（一个表的外键值存在于另一个表的主键中），保证数据的正确性；外键允许为null
-- 部门表 deptno是主键
create table dept(
deptno int ,
deptname varchar(16),
constraint pk_dept primary key(deptno)
)
-- 员工表 编号(主键)、姓名、部门编号(外键)
create table emp(
empno int ,
ename varchar(16),
deptno int,
constraint pk_emp primary key(empno),
constraint fk_emp_dept FOREIGN key(deptno)
references dept(deptno)
)
思考问题：
1. 一个表可以有一个主键约束，可以有多个外键约束
2. 向两张表插入数据时，两个数据有关系，先插入主键表，再插入外键表
3. 删除数据时，先删除外键表，再删除主键表。
3.唯一约束
约束的列可以是一列也可以是多列，约束的列的值具有唯一且允许为空的特性
通常有些数据在业务上是唯一的，但不是主键，例如电话号、订单号、订单号+序号组合
create table orders (
orderno varchar(12),
customerid int,
tel varchar(11),
constraint pk_orders primary key(orderno),
constraint unique_orders_tel unique(tel)
)
create table ordersdetail(
detailid int ,
orderno varchar(12),
seq int,
item varchar(12),
constraint pk_orderdetail primary key(detailid),
constraint fk_orderdetail_orderno foreign key (orderno)
references orders(orderno),
constraint unique_ordersdetail_noseq unique(orderno,seq)
)
4. 非空约束
约束某一列是否允许为空值，前面的约束都是表级约束，非空约束是列级约束
create table orders (
orderno varchar(12),
customerid int,
tel varchar(11) not null,
constraint pk_orders primary key(orderno),
constraint unique_orders_tel unique(tel)
)
###5. 检查约束
约束的列的值符合检查的条件，mysql默认是不支持检查约束
alter语句
更改数据库对象结构，例如表结构、视图、索引等
语法：
alter table 表名 action
action: 不是关键字 有一些动作组成的，包括add 、 modify/change、 drop 、rename
例如：
-
在locations表中 新建一个字段country_id
alter table locations add country_id char(2); -
在locations表中删除一个字段
alter table locations drop test_id; -
在locations表中 country_id 的类型改为varchar(2)
alter table locations change country_id country_id varchar(2); -
将testa表 改名为 testb表
alter table testa rename to testb; -
为 locations表增加主键约束 location_id列
alter table locations add constraint pk_locations primary key(location_id) -
删除locations的主键约束
alter table locations drop primary key; -
删除student表的cno的外键约束
alter table student drop foreign key fk_student_classno;
drop语句
删除表结构，表数据也移除
drop table if exists testb; -- 保证语句能成功执行
insert语句
是DML语句 语法中都是对 table的默认操作
语法：
insert into 表名 [(字段名, 字段名…)]
values (值, 值…)
解释：插入的值 和 表名后面指定的字段 顺序要对应 ，向表中插入一条记录
如果表名的后面 没有写字段名，字段的顺序按照建表时字段的顺序排列
insert into locations
values(1009,'北京市海淀区2',100043,'北京','北京',86)
insert into locations(street_address,postal_code)
values('北京市朝阳区2',100046)
insert into locations(location_id,street_address)
values(default,'北京市海淀区2')
-- mysql支持
insert into locations(street_address,postal_code)
values('北京市朝阳区5',100046),
('北京市朝阳区6',100046),
('北京市朝阳区7',100046)
可能出现的错误：
-
指定的列名不存在
Unknown column ‘xxxx’ in ‘field list’
- 列数量和值数量 不一致
Column count doesn’t match value count at row 1
select语句
查询语句可以查询部分列内容 ，查询部分数据行内容，表和表的联合查询
简单查询
语法：
select * from 表名
或
select 字段名，字段名… from 表名
其中 * 代表所有列 ，在项目开发时不建议写 * ，因为效率慢
select 和from 之间可以写：
-
别名
检索出的记录是结果集 ，对结果集的列起的别名
select first_name fn,phone_number tel from employee -
运算表达式
每个员工的年薪 全文检索 没有加条件查询 select first_name,salary*12 from employee -
distinct 关键字 去重复
检索员工表的部门和岗位 select distinct department_id,job_id from employee select distinct department_id from employee -
使用函数
直接通过函数名调用 ，每个函数一定有结果
参照：资料：《MySQL常用函数》
条件查询
查询出表中的部分行数据
语法：
select * from 表名 where boolean_expression
检索条件包括：
-
比较条件 ： = > < >= <= <>
. 逻辑运算 ：and or not 注意：and的优先级高于or
select * from employee where first_name <> 'jack' or salary > 5000 select * from employee where first_name <> 'jack' select * from employee where not first_name = 'jack'- 区间范围条件：between … and …
select * FROM employee where salary >=5000 and salary <=10000 select * from employee where salary between 5000 and 10000- 匹配查询条件/模糊查询： like关键字和两个符号组合使用，分别是%和_
其中% 代表0或n个任意字符 ， _代表单个任意字符
-- 查询姓名以t开头的 select * from employee where binary first_name like 't%' -- 查询姓名第二个字母是o的员工 select * from employee where first_name like '_o%' -- 查询姓名第二个字母不是o的员工 select * from employee where first_name not like '_o%'- 包含查询条件：in关键字 （内容）
-- 查询出姓名是tom jim rose jack 的员工 select * from employee where first_name in ('tom','jim','rose','jack') -- 查询出姓名不是tom jim rose jack 的员工 select * from employee where first_name not in ('tom','jim','rose','jack')- null值的查询： 在sql中null值和空字符串是不同的值 ，在null值检索时要使用 is null或is not null
-- 没有部门的员工 select * from employee where department_id is null -- 有部门的员工 select * from employee where department_id is not null
排序查询
对查询的结果集进行排序，使用 order by 字段名 [,字段名] [asc](默认的升序) [desc](降序)修饰的是当个字段
select * from 表名 [where …] order by 字段名 [,字段名] [asc](默认的升序) [desc](降序)
-- 查询出所有员工 按工资排序
select first_name,salary from employee order by salary
-- 查询出所有员工 按部门升序、工资降序排序
select first_name,department_id,salary from employee
order by department_id,salary desc
思考：order by 后面使用别名？ where的后面使用别名？
select first_name fn,salary s from employee
where salary > 5000
order by s
分组函数/聚合函数
sum() 总和
avg() 平均
max() 最大
min() 最小
count() 记录数
select sum(salary),avg(salary),max(salary),min(salary),count(salary) from employee
分组函数和null值的使用，分组函数运算不包括 null 值
ifnull (m ，n） 如果m是null 结果是n 如果m不是null 结果是m
分组语句
可以对查询结果进行分组，配合分组函数可以进行一些统计分析的查询
select * from 表名 where … group by 字段名,字段名 order by …
查询各个部门的平均工资
select avg(salary) from employee group by department_id
注意事项：
1. 分组函数和字段一同查询时，字段一定要在group by语句中
2. 当分组函数作为查询条件时，where语句中不能包含分组函数，使用having完成分组函数的条件限定
查询工资大于4000的平均工资大于5000的部门
select department_id,salary,avg(salary)
from employee
where salary > 4000
group by department_id
having avg(salary) > 5000
order by department_id
过程：查询出工资大于4000的员工 形成结果集
对结果集数据进行分组，在结果集中限定having的条件，排序显示
- mysql的分组函数不能嵌套
- count (一个或一些字段) 获得字段不为null的记录数
有多少个员工
select count(employee_id) from employee
select count(department_id) from employee
select count(1) from employee
select count(null) from employee
select count(*) from employee
select 1 from employee
计算表的记录数
select count(*) from employee
select count(1) from employee
select count(employee_id) from employee
多表连接查询
查询的数据或条件分布在多张表中，通过多表连接查询实现。
例如：查询员工姓名(employee)和部门名称(departments),
查询工资等级是2级(job_grades)的员工姓名(employee)
等值连接和内连接
等值连接：
两个表的等值连接时，会将两个表行数据的所有组合查询到结果集中，乘积叫笛卡尔积，但是这样的组合并不正确，通过where语句过滤出正确的组合
select * from employee e,departments d
where e.department_id = d.department_id
不等值连接
查询员工姓名和工资等级
select first_name,grade_level
from employee e,job_grades g
where salary >= lowest_sal and salary < highest_sal
缺点：只能查询出符合where条件的数据
等值连接的结果和内连接的结果相同
内连接：
查询财务部员工姓名和部门名称 结果和等值连接相同
select * from employee e inner join departments d
on e.department_id = d.department_id
and department_name = '财务部'
select * from employee e inner join departments d
on e.department_id = d.department_id
where department_name = '财务部'
on不是查询条件 而是 连接条件
外链接：左外连接和右外连接
查询出左表的所有数据
A 左外连接 B 那么 A表就是 左表
结果显示A表的所有数据
A 右外连接 B 那么 B表就是 左表
结果显示B表的所有数据
查询出所有的员工姓名和部门名称
select * from employee e left outer join departments d
on e.department_id = d.department_id
查询出所有员工的姓名、部门名称、工作地点
select * from employee e
left outer join departments d
on e.department_id = d.department_id
left outer join locations loc
on d.location_id = loc.location_id
where ...
group by ....
having ...
order by ...
自连接
表自己连接自己，可以左外连接的自连接、内连接的自连接
查询员工姓名和员工管理者姓名
select e.first_name,e.salary,m.first_name,m.salary from employee e
left outer join employee m on e.manager_id = m.employee_id
查询员工姓名部门名称所在地、其管理者姓名部门名称所在地
select e.first_name,d.department_name,loc.city,m.first_name,md.department_name,mloc.city
from employee e
left outer join employee m on e.manager_id = m.employee_id
left outer join departments d on e.department_id = d.department_id
left outer join departments md on m.department_id = md.department_id
left outer join locations loc on d.location_id = loc.location_id
left outer join locations mloc on md.location_id = mloc.location_id
子查询
包含在一个查询语句中的另一个查询
select * from employee
where job_id = (select job_id from employee where first_name = 'jack')
-
几乎所有的查询需求都能用子查询解决，效率低，原因是每检索外查询的一条记录执行where中的子查询，例如上面的语句总共会检索 12*12次
-
根据子查询的结果 分为单行子查询和多行子查询 ，在where语句中 单行子查询只能用 = > < <> 限定条件 ，多行子查询可以用 any all in exist限定
-
any : 任意一个，哪个都行 ； all 全部都符
select first_name,salary from employee where salary > any(select salary from employee where department_id = 5001) select first_name,salary from employee where salary > all(select salary from employee where department_id = 5001) -
exist修饰子查询 结果是true/false ，如果exist修饰的子查询有记录则结果是true，没有记录结果是false，exist修饰子查询的效率 比 in修饰子查询的效率高
select * from employee where department_id in (select department_id from departments) 效率高,在exists的子查询执行时，如果找到了记录就立刻返回true select * from employee e where exists (select 1 from departments d where e.department_id = d.department_id); select * from departments;例如：select * from A where aid in (select bid from B)
select * from A a where exists(select 1 from B b where a.aid= b.bid)
-
子查询不仅可以写在where中，也可以写在from的后面，对子查询的结果集再次进行查询
select max(a.avgsal) from (select avg(salary) avgsal from employee group by department_id) a -
union和unionall 修饰子查询
可以将多个子查询的结果合并
- 合并的两个结果集 一定要有相同数量的列，（对应顺序的列类型相同oracle数据库）。
- union 去掉重复的数据合并 union all 不去重合并。
- 应用场景：报表查询中结果集的联合查询或聚合操作、列转行操作。
限定记录行查询
限定从哪行开始查询，查询多少行。写在查询SQL语句的最后
查询员工表的前5条记录 (应用程序界面：第一页 每页显示5条)
select * from employee order by first_name limit 0,5
查询员工表的前6~10条记录 (第二页 每页显示5条)
select * from employee order by first_name limit 5,5
查询员工表的前11~15条记录 (第三页 每页显示5条)
select * from employee order by first_name limit 10,5
假设 java变量 pageNum当前页数 pageSize 每页记录数
select * from employee limit (pageNum-1)*pageSize,pageSize
update语句
更新数据语句
语法：
update 表名 set 字段名=值 [, 字段名=值] [where …]
update employee set salary = 26000,job_id='财务总监' where employee_id = 103
在mysql中 update后面的表可以写多表连接
update employee set salary = salary + 1000 where employee_id
in (select employee_id from employee e
left outer join departments d on e.department_id = d.department_id
left outer join locations loc on d.location_id = loc.location_id
where city = '北京')
update employee e
left outer join departments d on e.department_id = d.department_id
left outer join locations loc on d.location_id = loc.location_id
set salary=salary+1000 where loc.city='北京'
delete语句
删除数据的，可以按照条件删除
语法：
delete from 表名 [where …]
delete和truncate和drop的区别？
delete是 DML语句, 删除数据
drop是 DDL语句，删除表结构和所有数据
truncate是清空表数据操作,删除的速度比delete快,但是不能按条件删除，不是事务回滚。
truncate table score
908
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
