本篇文章的思维导图如下:
MySQL 命令
表中具有几个概念:列、行、主键。 列叫做字段(Column),行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度
那么 SQL 语句包括增删改查,SQL 语句怎么分类呢?
- DQL(数据查询语言 Data Query Language): 查询语句,凡是 select 语句都是 DQL。
- DML(数据操作语言 Data Manipulation Language):insert delete update,对表当中的数据进行增删改。
- DDL(数据定义语言 Data Definition Language):create drop alter,对表结构的增删改。
- TCL(事务控制语言 Transactional Control Language):commit 提交事务,rollback 回滚事务。(TCL 中的 T 是 Transaction)
- DCL(数据控制语言 Data Control Language): grant 授权、revoke 撤销权限等。
常见 SQL 命令 show datebases; create database ; use ; show tables; 删除数据库:drop database ; 查看表结构: desc ; 查看表内容: select * from ; 查看 MySQL 版本: select version(); 查看当前数据库名称: select database(); 退出: exit; 查看别的数据库的表: show tables from ; 查看创建表的语句:show create table ;
什么是 sql 脚本呢? 当一个文件的扩展名是.sql,并且该文件中编写了大量的 sql 语句,我们称这样的文件为 sql 脚本。 注意:直接使用 source 命令可以执行 sql 脚本。 sql 脚本中的数据量太大的时候,无法打开,请使用 source 命令完成初始化。
基本 SELECT
简单的查询语句: 语法格式: select 字段名 1,字段名 2,字段名 3,…. from 表名;
sql 语句不区分大小写。
查询员工的年薪?(字段可以参与数学运算。) select ename,sal * 12 from emp;
给查询结果的列重命名? select ename,sal * 12 as yearsal from emp;
别名中有中文? select ename,sal * 12 as 年薪 from emp; // 错误
select ename,sal * 12 as '年薪' from emp;
注意:标准 sql 语句中要求字符串使用单引号括起来。虽然 mysql 支持双引号,尽量别用。 as 关键字可以省略? mysql> select empno,ename,sal * 12 yearsal from emp;
条件查询需要用到 where 语句,where 必须放到 from 语句表的后面
在数据库当中 NULL 不是一个值,代表什么也没有,为空。 空不是一个值,不能用等号衡量。 必须使用 is null 或者 is not null
and 和 or 联合起来用:找出薪资大于 1000 的并且部门编号是 20 或 30 部门的员工。select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30; // 错误的
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30); // 正确的。
注意:当运算符的优先级不确定的时候 加小括号 。
in 等同于 or: select ename,job from emp where job in('SALESMAN', 'MANAGER');
模糊查询 like 找出名字当中含有 O 的?(在模糊查询当中,必须掌握两个特殊的符号,一个是%,一个是*) %代表任意多个字符,*代表任意 1 个字符。 select ename from emp where ename like '%O%';
找出名字中第二个字母是 A 的?
select ename from emp where ename like '_A%';
找出名字中最后一个字母是 T 的?
select ename from emp where ename like '%T';
查询结果去重 distinct
distinct 只能出现在所有字段的最前面。
select distinct job from emp;
排序 ORDER BY
默认是升序. asc 表示升序,desc 表示降序。
select ename , sal from emp order by sal; // 升序
select ename , sal from emp order by sal asc; // 升序
select ename , sal from emp order by sal desc; // 降序。
按照工资的降序排列,当工资相同的时候再按照名字的升序排列。
select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal desc , ename asc;
注意:越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。 order by 是最后执行的
分组/聚合函数
count 计数, sum 求和, avg 平均值, max 最大值, min 最小值
select sum(sal) from emp where JOB='CLERK';
所有的分组函数都是对“某一组”数据进行操作的。
找出工资总和? select sum(sal) from emp;
找出最高工资? select max(sal) from emp;
找出最低工资? select min(sal) from emp;
找出平均工资? select avg(sal) from emp;
找出总人数? select count(*) from emp; select count(ename) from emp;
分组函数一共 5 个。 分组函数还有另一个名字:多行处理函数。 多行处理函数的特点:输入多行,最终输出的结果是 1 行。 分组函数自动忽略 NULL。
所有数据库都是这样规定的, 只要有 NULL 参与的运算结果一定是 NULL。 使用 ifnull 函数:ifnull(可能为 NULL 的数据,被当做什么处理) : 属于单行处理函数。 select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
sum 函数自动忽略 null
分组函数也能组合起来用: select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
count(*)和 count(具体的某个字段),他们有什么区别?
- count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
- count(comm): 表示统计 comm 字段中不为 NULL 的数据总数量。
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
记住一个规则:当一条语句中有 group by 的话,select 后面只能跟分组函数和参与分组的字段。
分组查询
分组查询主要涉及到两个子句,分别是:group by 和 having 一个完整的 select 语句格式如下
select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..
以上语句的执行顺序
- 首先执行 where 语句过滤原始数据
- 执行 group by 进行分组
- 执行 having 对分组数据进行操作
- 执行 select 选出数据
- 执行 order by 排序 原则:能在 where 中过滤的数据,尽量在 where 中过滤,效率较高。having 的过滤是专门对分组之后的数据进行过滤的。
连接查询 JOIN
在表的连接查询方面有一种现象被称为:笛卡尔积现象。 笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。
关于表的别名: select e.ename,d.dname from emp e,dept d;
表的别名有什么好处?
- 第一:执行效率高。
- 第二:可读性好。
内连接中的等值连接
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
内连接的非等值连接 工资->工资等级
mysql> select e.ename , e.sal,s.grade
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal;
自连接: 一张表看出两张不同的表 什么是外连接,和内连接有什么区别?
- 内连接: 假设 A 和 B 表进行连接,使用内连接的话,凡是 A 表和 B 表能够匹配上的记录查询出来,这就是内连接。 AB 两张表没有主副之分,两张表是平等的。
- 外连接: 假设 A 和 B 表进行连接,使用外连接的话,AB 两张表中有一张表是主表,一张表是副表,主要查询主表中 的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出 NULL 与之匹配。
外连接的分类?
- 左外连接(左连接):表示左边的这张表是主表。
- 右外连接(右连接):表示右边的这张表是主表。
左连接有右连接的写法,右连接也会有对应的左连接的写法。
外连接:(右外连接/右连接)
select
a.ename '员工', b.ename '领导'
from
emp b
right join
emp a
on
a.mgr = b.empno;
UNION 并集
select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN'
合并结果集的时候,需要查询字段对应个数相同。在 Oracle 中更严格,不但要求个数相同,而且还要求类型对应相同。
LIMIT 的使用
select * from table limit m,n
其中 m 是指记录开始的 index,从 0 开始,表示第一条记录 n 是指从第 m+1 条开始,取 n 条。
select * from tablename limit 2,4
即取出第 3 条至第 6 条,4 条记录
limit是sql语句最后执行的一个环节:
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...;
分页的 sql 语句 limit (PageNo-1)*PageSize, PageSize;
表
创建表
create table tableName(
columnName dataType(length),
………………..
columnName dataType(length)
);
set character_set_results='gbk';
show variables like '%char%';
创建表的时候,表中有字段,每一个字段有:
* 字段名
* 字段数据类型
* 字段长度限制
* 字段约束
数据类型
说明: 如果需使用Java中的 Boolean 类型可以使用 bit(1)
增/删/改 DDL
//删除表
drop table if exists t_student;
//添加字段
alter table t_student add contact_tel varchar(40);
//修改字段
alter table t_student modify student_name varchar(100) ;
alter table t_student change sex gender char(2) not null;
//删除字段
alter table t_student drop contact_tel;
增/删/改 DML
//添加记录
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);
如何插入日期: 第一种方法,插入的日期格式和显示的日期格式一致 第二种方法,采用 str_to_date 第三种方法,添加系统日期(now())
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);
表复制
create table emp_bak as select empno,ename,sal from emp;
以上方式,会自动创建表,将符合查询条件的数据自动复制到创建的表中 如何将查询的数据直接放到已经存在的表中,可以使用条件
insert into emp_bak select * from emp where sal=3000;
数据更新 可以修改数据,可以根据条件修改数据
update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where ……
数据删除
delete from表名 where 。。。。。
创建表加入约束
常见的约束 a) 非空约束,not null b) 唯一约束,unique c) 主键约束,primary key d) 外键约束,foreign key e) 自定义检查约束,check(不建议使用)(在 mysql 中现在还不支持)
非空约束, 针对某个字段设置其值不为空,如:学生的姓名不能为空
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30),
classes_id int(3)
)
//加入不符合约束条件的内容会报错
唯一约束 unique 唯一性约束,它可以使某个字段的值不能重复,如:email 不能重复:[每个字段一个约束-列级约束]
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) unique,
classes_id int(3)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
给两个列或者多个列添加 unique [多个字段联合添加一个约束-表级约束]
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);
注意:not null 约束只有列级约束。没有表级约束。 主键约束: 不为空且不能重复
drop table if exists t_user;
create table t_user(
id int primary key, // 列级约束
username varchar(255),
email varchar(255)
);
主键约束, 主键值, 主键字段 主键有什么作用?
- 表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。
- 主键的作用:主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号码一样。)
主键的分类: 根据 主键字段的字段数量 来划分:
- 单一主键(推荐的,常用的。)
- 复合主键 (多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式。)
根据 主键性质 来划分:
- 自然主键:主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)
- 业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
一张表的主键约束只能有 1 个。(必须记住) mysql 提供主键值自增:(非常重要。)
drop table if exists t_user;
create table t_user(
id int primary key auto_increment, // id字段自动维护一个自增的数字,从1开始,以1递增。
username varchar(255)
);
外键约束: 子表引用父表的某一字段 关于外键约束的相关术语:
- 外键约束: foreign key
- 外键字段:添加有外键约束的字段
- 外键值:外键字段中的每一个值。
删除先删子, 再删除父表 创建的时候先创建父表, 后创建子表 外键可以为 NULL。 外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗? 注意:被引用的字段不一定是主键,但至少具有 unique 约束。
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
constraint student_id_pk primary key(student_id),
// 外键约束
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)
MySQL 函数
数字函数
日期函数
字符函数
条件函数
事务 TCL
SET AUTOCOMMIT=false;
START TRANSACTION ;
SELECT * FROM dept;
UPDATE dept set LOC='BEIJIN' WHERE LOC='NEW YORK';
SELECT * FROM dept;
ROLLBACK ;
# COMMIT
SELECT * FROM dept;
索引
什么是索引?有什么用? 索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。 在数据库方面,查询一张表的时候有两种检索方式: 第一种方式:全表扫描 第二种方式:根据索引检索(效率很高) 索引为什么可以提高检索效率呢? 其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护。是有维护成本的。比如,表中的数据经常被修改这样就不适合添加索引, 因为数据一旦修改,索引需要重新排序,进行维护。 添加索引是给某一个字段,或者说某些字段添加索引。
select ename,sal from emp where ename = ‘SMITH’; 当 ename 字段上没有添加索引的时候,以上 sql 语句会进行全表扫描,扫描 ename 字段中所有的值。 当 ename 字段上添加索引的时候,以上 sql 语句会根据索引扫描,快速定位。
什么时候考虑 给字段添加索引 ?(满足什么条件)
- 数据量庞大。(根据客户的需求,根据线上的环境)
- 该字段很少的 DML(插入,删除)操作。(因为字段进行修改操作,索引也需要维护)
- 该字段经常出现在 where 子句中。(经常根据哪个字段查询)
- 该字段经常出现在连接操作的连接条件中.
注意:主键和具有 unique 约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。
给薪资sal字段添加索引:
create index emp_sal_index on emp(sal);
索引底层采用的数据结构是:B+ Tree 索引的分类
- 单一索引:给单个字段添加索引
- 复合索引: 给多个字段联合起来添加 1 个索引
- 主键索引:主键上会自动添加索引
- 唯一索引:有 unique 约束的字段上会自动添加索引
索引的实现原理? 通过 B Tree 缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
select ename from emp where ename = 'SMITH';
通过索引转换为:
select ename from emp where 物理地址 = 0x3;
索引什么时候失效? select ename from emp where ename like '%A%';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
视图
什么是视图? 站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待)。
怎么创建视图?怎么删除视图?
create view myview as select empno,ename from emp;drop view myview;
注意:只有 DQL 语句才能以视图对象的方式创建出来。 对视图进行增删改查,会影响到原表数据。(通过视图影响原表数据的,不是直接操作的原表) 可以对视图进行 CRUD 操作。
create table emp_bak as select * from emp;
create view myview1 as select empno,ename,sal from emp_bak;// 创建视图
update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据。
delete from myview1 where empno = 7369; // 通过视图删除原表数据。
视图的作用 视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java 程序员只对视图对象进行 CRUD。 通过多表连接产生的视图, 每次修改只能修改来自其中一张表的数据.
数据库设计三范式
参考:数据库逻辑设计之三大范式通俗理解,一看就懂,书上说的太晦涩 - SegmentFault 思否
一、第一范式
1NF是对属性的原子性
,要求属性具有原子性,不可再分解;
表:字段1、 字段2(字段2.1、字段2.2)、字段3 ……
如学生(学号,姓名,性别,出生年月日),如果认为最后一列还可以再分成(出生年,出生月,出生日),它就不是一范式了,否则就是;
二、第二范式
2NF是对记录的唯一性
,要求记录有唯一标识,即实体的唯一性,即 不存在部分依赖**;
表:学号、课程号、姓名、学分;
这个表明显说明了两个事务:学生信息, 课程信息;由于非主键字段必须依赖主键,这里 学分依赖课程号 , 姓名依赖与学号 ,所以不符合二范式。
可能会存在问题:
数据冗余:
,每条记录都含有相同信息;删除异常:
删除所有学生成绩,就把课程信息全删除了;插入异常:
学生未选课,无法记录进数据库;更新异常:
调整课程学分,所有行都调整。
正确做法:
学生:Student
(学号, 姓名);
课程:Course
(课程号, 学分);
选课关系:StudentCourse
(学号, 课程号, 成绩)。
三、第三范式
3NF是对字段的冗余性
,要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即 不存在传递依赖;
表: 学号, 姓名, 年龄, 学院名称, 学院电话
因为存在 依赖传递 : (学号) → (学生)→(所在学院) → (学院电话) 。
可能会存在问题:
数据冗余:
有重复值;更新异常:
有重复的冗余信息,修改时需要同时修改多条记录,否则会出现 数据不一致的情况 。
正确做法:
学生:(学号, 姓名, 年龄, 所在学院);
学院:(学院, 电话)。
四、反范式化
一般说来,数据库只需满足第三范式(3NF
)就行了。
没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余, 达到以空间换时间的目的
。
〖例〗:如订单表,“金额”这个字段的存在,表明该表的设计不满足第三范式,因为“金额”可以由“单价”乘以“数量”得到,说明“金额”是冗余字段。但是,增加“金额”这个冗余字段,可以提高查询统计的速度,这就是以空间换时间的作法。
在Rose 2002
中,规定列有两种类型: 数据列 和 计算列 。“金额”这样的列被称为“计算列”,而“单价”和“数量”这样的列被称为“数据列”。