一，SQL的分类
数据查询语言(DQL---Data Query Language)
代表关键字:select
数据操纵语言(DML---Data Manipulation Language)
代表关键字:insert,delete,update
数据定义语言(DDL---Data Definition Language)
代表关键字:create ,drop,alter,
事务控制语言(TCL---Transactional Control Language)
代表关键字:commit ,rollback;
数据控制语言(DCL---Data Control Language)
代表关键字:grant,revoke.
二，常用命令
- 查看mysql版本：
mysql --version或 mysql -V
- 连接数据库：
mysql -uroot - p111（账户为root，密码为111）
- 创建数据库：
create database(数据库名称)
- 使用数据库：
use database
注：在数据库中建立表，因此创建表的时候必须要先选择数据库。
- 查询当前使用的数据库：
select database
- 终止一条语句 ：
想要终止一条正在编写的语句，可键入\c或同时按下ctrl和c键。
- 退出mysql
可使用\q、QUIT或EXIT：
三，数据查询语言——DQL
3.1条件查询
条件查询需用到where语句，where语句必须放到from语句表的后面，常用运算符如下
| 运算符 | 说明 |
| = | 等于 |
| <>或!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between … and …. | 两个值之间,等同于 >= and <= |
| is null | 为null（is not null 不为空） |
| and | 并且 |
| or | 或者 |
| in | 包含，相当于多个or（not in不在这个范围中） |
| not | not可以取非，主要用在is 或in中 |
| like | like称为模糊查询，支持%或下划线匹配%匹配任意个字符下划线，一个下划线只匹配一个字符 |
3.2排序 (降序 ：desc ，升序： asc)
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，
order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面
select * from emp order by sal; //在员工emp表中按照工资sal值的降序进行排序
select * from emp order by sal desc;
select * from emp order by job desc, sal desc; //如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序
select * from emp order by 6; //使用字段的位置来排序，不建议，采用数字含义不明确
3.3分组函数
|
count |
取得记录数 |
|
sum |
求和 |
|
avg |
取平均 |
|
max |
取最大的数 |
|
min |
取最小的数 |
注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
count select count(*) from emp; //取得所有的员工数 sum select sum(sal) from emp; //取得薪水的合计 avg elect avg(sal) from emp; //取得平均薪水 max select max(sal) from emp; //取得最高薪水 min select min(sal) from emp; //取得最低薪水
3.4分组查询
(1) group by
按照某个字段或某些字段进行分组
注：语句中有group by分组，select只能参与 分组的字段以及 分组函数
select max(sal),job from emp group by job;
select ename ,max(sal),job from emp group by job; //报错，ename 不是分组的字段以及分组函数
(2) having
对分组之后的数据再次过滤
取得每个岗位的平均工资大于2000 select job, avg(sal) from emp group by job having avg(sal) >2000;
一个完整的select语句格式如下：
select 字段 5 from 表名 1 where ……. 2 group by …….. 3 having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现) 4 order by …….. 6
limit..... 7
以上语句的执行顺序：
- 首先执行where语句过滤原始数据
- 执行group by进行分组
- 执行having对分组数据进行操作
- 执行select选出数据
- 执行order by排序
注：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。
3.5连接查询
连接查询：也可以叫跨表查询，需要关联多个表进行查询
根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
3.6子查询
子查询就是嵌套的select语句，可以理解为子查询是一张表
select......
from......
where...... //以上三个语句后面都可以加上select语句进行嵌套
(1),在where语句中使用子查询
// 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名 // 1、首先取得管理者的编号，去除重复的 select distinct mgr from emp where mgr is not null; // 2、查询员工编号包含管理者编号的 select empno, ename from emp where empno in(select mgr from emp where mgr is not null);
（2),在from语句中使用子查询
//查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名 //首先取得管理者的编号，去除重复的(distinct) select distinct mgr from emp where mgr is not null; //将以上查询作为一张表，放到from语句的后面 select e.empno, e.ename
from emp e
join (select distinct mgr from emp where mgr is not null) m
on e.empno=m.mgr;
(3),在select语句中使用子查询
//查询员工信息，并显示出员工所属的部门名称 //在select语句中再次嵌套select语句完成部分名称的查询 select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
3.7 union
union可以合并集合（相加）
//查询job包含MANAGER和包含SALESMAN的员工
1,select * from emp where job = 'MANAGER' or 'SALESMAN';
2,select * from emp where job in('MANAGER', 'SALESMAN');
3,select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN'
3.8 limit的使用
MySQL提供了limit ，主要用于提取前几条或者中间某几行数据，分页查询
3.8.1 语法机制
limit startIndex, length //startIndex 表示起始位置;length表示取n个
例1：select * from tablename limit 2,4
即取出第3条至第6条，4条记录
例2：取得薪水最高的前5名
select * from emp e order by e.sal desc limit 5;
3.8.2 通用的标准分页sql
pageno页：(pageno-1) * pagesize,pagesize;
Java代码： int pageno = 2;
int pagesize = 10;
limit (pageno -1) * pagesize,pagesize;
四，数据操纵语言------DML
4.1 创建表：建表语句，语法格式
create table 表名(
字段名1，数据类型(length),
………………..
字段名2，数据类型(length)
);
set character_set_results='gbk';
show variables like '%char%';
/* 创建表的时候，表中有字段，每一个字段有：
* 字段名
* 字段数据类型
* 字段长度限制
* 字段约束
*/
//建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3)
)
4.2 MySQL常用的数据类型
|
类型 |
描述 |
|
Char(长度) |
定长字符串，存储空间大小固定，适合作为主键或外键 |
|
Varchar(长度) |
变长字符串，存储空间等于实际数据空间 |
|
double(有效数字位数，小数位) |
数值型 |
|
Float(有效数字位数，小数位) |
数值型 |
|
Int( 长度) |
整型 |
|
bigint(长度) |
长整型 |
|
Date |
日期型 年月日 |
|
DateTime |
日期型 年月日 时分秒 毫秒 |
|
time |
日期型 时分秒 |
|
BLOB |
Binary Large OBject（二进制大对象） |
|
CLOB |
Character Large OBject（字符大对象） |
//插入数据 insert
insert into 表名 （字段名1，字段名2）
vlues （值1，值2） //顺序可以打乱，但数量必须保持一致
//向t_student表中加入数据，
insert into t_student(student_id, student_name, sex, birthday,email, classes_id)
values(1001, 'zhangsan', 'm', '1999-01-01', '[email protected]', 10);
//删除数据 delete
delete from 表名 where 条件
//删除部门表中部门号等于10的
delete from dept where deptno = 10；
//删除大表，表被截断，不可回滚，永久丢失：
truncate table 表名；
//修改数据 update
update 表名 set 字段名1 = 值1，字段名2 = 值2 .... where 条件。
update dept set loc = 'shanghai',dname = 'xiaoshoubu' where = 10;
//表的复制，将查询结果当作表创建出来
create table 表名 as select语句；
五，数据定义语言----DDL
采用alter table来增加/删除/修改表结构，不影响表中的数据
添加字段
//向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40) alter table t_student add contact_tel varchar(40);
修改字段
//student_name无法满足需求，长度需要更改为100 alter table t_student modify student_name varchar(100) ;
删除字段
//删除联系电话字段 alter table t_student drop contact_tel;
六，约束
常见的约束
a) 非空约束，not null
非空约束，针对某个字段设置其值不为空
b) 唯一约束，unique
唯一性约束，它可以使某个字段的值不能重复
c) 主键约束，primary key
每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，
复合（联合）主键是由多个字段构成的
d) 外键约束，foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键
e) 自定义检查约束，check（不建议使用）(在mysql中现在还不支持)
七，存储引擎
7.1存储引擎的使用
-
- 数据库中的各表均被（在创建表时）指定的存储引擎来处理。
- 服务器可用的引擎依赖于以下因素：
- MySQL的版本
- 服务器在开发时如何被配置
- 启动选项
- 为了解当前服务器中有哪些存储引擎可用，可使用SHOW ENGINES语句：mysql> SHOW ENGINES\G
7.2常见的存储引擎
MyISAM存储引擎：
-
- MyISAM存储引擎是MySQL最常用的引擎。
- 它管理的表具有以下特征：
使用三个文件表示每个表：
-
-
-
- 格式文件 — 存储表结构的定义（mytable.frm）
- 数据文件 — 存储表行的内容（mytable.MYD）
- 索引文件 — 存储表上索引（mytable.MYI）
-
-
InnoDB存储引擎
-
-
- InnoDB存储引擎是MySQL的缺省引擎。
- 它管理的表具有下列主要特征：
-
- 每个InnoDB表在数据库目录中以.frm格式文件表示
- InnoDB表空间tablespace被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
- 提供全ACID兼容
- 在MySQL服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
八 .事务
8.1什么是事务
1.事务是一个完整的业务逻辑单元，不可再分
例如：银行转账，张三给李四转账100，此时张三账户余额减少100，而李四账户余额增加100，二者缺一不可
2.事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。
事务具有四个特征 ACID
a) 原子性（Atomicity）
整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
b) 一致性（Consistency）
在事务开始之前与结束之后，数据库都保持一致状态。
c) 隔离性(Isolation)
一个事务不会影响其他事务的运行。
d) 持久性(Durability)
在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。
3.事务中存在一些概念：
a) 事务（Transaction）：一批操作（一组DML）
b) 开启事务（Start Transaction）
c) 回滚事务（rollback）
d) 提交事务（commit）
e) SET AUTOCOMMIT：禁用或启用事务的自动提交模式
注：当执行DML语句是其实就是开启一个事务
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回
滚select没有任何意义），对于create、drop、alter这些无法回滚.
事务只对DML有效果。
rollback，或者commit后事务就结束了。
4.事务的隔离性
InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：
- 读未提交（READ UMCOMMITTED）
允许一个事务可以看到其他事务未提交的修改，数据不稳定，会出现脏读现象
- 读已提交（READ COMMITTED）
允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
- 可重复读（REPEATABLE READ）
确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改，即读到的数据其实是幻象
-
- 串行化（SERIALIZABLE） 【序列化】
将一个事务与其他事务完全地隔离。即事务排队执行，效率低
九 . 索引
9.1，什么是索引
什么时候需要给字段添加索引：
表中该字段中的数据量庞大
经常被检索，经常出现在where子句中的字段
经常被DML操作的字段不建议添加索引
索引等同于一本书的目录
主键会自动添加索引，所以尽量根据主键查询效率较高。
9.2 如何建立索引
建立索引如下：
1、create unique index 索引名 on 表名(列名);
create unique index u_ename on emp(ename);
2、alter table 表名 add unique index 索引名 (列名);
查看索引：
show index from emp;
使用索引：
explain select sal from emp where sal > 1500;
删除索引：
DROP INDEX index_name ON talbe_name
删除掉table_name中的索引index_name。
9.3 索引底层采用的数据结构及其实现原理：
索引底层采用的数据结构是：B + Tree
索引的实现原理：通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
是最高的。例如：
select ename from emp where ename = 'zhangsan';
通过索引转换为：
select ename from emp where 物理地址 = xxx;
9.4索引的分类？
单一索引：给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引
9.5 索引什么时候失效？
select ename from emp where ename like '%A%';
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。
十.视图
10.1什么是视图
-
- 视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
- 视图有时也被成为“虚拟表”。
- 视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
- 相对于从基表中直接获取数据，视图有以下好处：
访问数据变得简单
可被用来对不同用户显示不同的表的内容
10.2视图的作用
提高检索效率
隐藏表的实现细节【面向视图检索】
保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。
10.3怎么创建视图？删除视图？
create view myview as select empno,ename from emp; drop view myview; //注意：只有DQL语句才能以视图对象的方式创建出来。
对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）
可以对视图进行CRUD操作。
补充：MySQL不区分大小写，语法相对比较宽松
去除重复记录 distinct 关键词
标签：语句,笔记,查询,学习,索引,emp,MYSQL,where,select From： https://www.cnblogs.com/qymblogs/p/17090367.html