一、库操作
1,创建库 : create database 库名;
2,删除库: drop database 库名;
3,查看当前有多少数据库:show databases;
4,查看当前使用的数据库: select datebase();
5,切换到这个数据下:use 库名;
二、表操作(增删改查)
增
创建表：create table 表名(字段名 数据类型(长度));
create table day (id int,name char(4)); mysql5.6版本默认是engine=innodb
create table day (id int,name char(4)) engine=myisam;
create table day (id int,name char(4)) engine=memory;
create table 表名(
id int auto_increment primary key,
name varchar(20) not null default ''
)engine=innodb charset=uft8;
删
删除表：drop table 表名;
drop table student;
改
修改:
添加字段：alter table 表名 add 字段名 数据类型(宽度) 约束;
删除字段：alter table 表名 drop 字段名;
修改字段：
alter table 表名 add|modify|drop|change
add 列名 类型
modify 列表 类型
drop 列名
change 旧列名 新列名 类型
修改已经存在的字段的类型,长度,约束:使用modify
alter table 表名 modify name varchar(12) not null;
修改已经存在的字段的类型,长度,约束和字段名字:使用change
alter table 表名 change name new_name varchar(12) not null;
特殊：有三个字段依次是：id name age
交换name和age的位置：alter table 表名 modify age int not null after id;
将age放在第一位：alter table 表名 modify age int not null first;
修改表名:
rename from 旧表名 to 新表名
查
查询:
查看当前有多少张表: show tables;
查看表结构:
查看表的创建过程: show create table 表名;
查看基础信息: desc 表名;
表与表之间的关系
1,两张表中的的数据之间的关系
多对一:
forerign key 永远是在多的那张表中设置外键
多个学生都是同一个班级的,学生是多,班级是一
两张表:学生表和班级表,学生表关联班级表
一对一:
foreign key + unique 后出现的后一张表中的数据作为外键,并且要约束这个外键是唯一的
一个学生是一个客户,两张表:学生表和客户表
客户表作为外表,在学生表中设置外键
多对多:
产生第三张表,把两个关联关系的字段作为第三张表的外键,并且要约束这两个外键是唯一约束
一本书有多个作者,一个作者有多本数,两张表:书名表和作者表
三,数据操作(增删改查)
增：insert
insert into 表名 values (值....);
所有的在这个表中的字段都需要按照顺序被填写在这里
insert into 表名(字段名，字段名。。。) values (值....);
所有在字段位置填写了名字的字段和后面的值必须是一一对应
insert into 表名(字段名，字段名。。。) values (值....),(值....),(值....);
所有在字段位置填写了名字的字段和后面的值必须是一一对应
value单数：一次性写入一行数据，values复数：一次性写入多行数据
写入角度：
第一个角度：写入一行内容还是写入多行
insert into 表名 values (值....);
insert into 表名 values (值....)，(值....)，(值....);
第二个角度：
是把这一行所有的内容都写入：insert into 表名 values (值....);
还是按指定字段写入：insert into 表名(字段1，字段2) values (值1，值2);
删：delete
delete from 表名 where 条件d;
删除id=1那行的数据：delete from student where id=1;
delete from 表名;
删除所有的数据：delete from student;
改：update
update 表名 set 字段名=新值;
修改所有的数据 : update student set name='nana';
updata 表名 set 字段名=新值
修改id=2的数据: update student set name='nana' where id =2;
查：select
单表查询
select 语句 :
select * from 表名;
select 列名,.. from 表名;
distinct 去重:
select distinct 列名,... from 表名
select distinct tname from teacher 表名;
对int类型四则运算:
select tid*5 from 表名;
重命名:
select 列名 as 新列名,.. from 表名;
select tname as '姓名' from teacher;
字符串拼接: concat()函数
select concat('姓名:',name,'年薪:',salary) as 表名;
select concat('编号:',tid,'姓名:',tname) from teacher;
where 语句:
比较运算:
大于：>，小于： <，等于： =，大于等于：>=，小于等于：<=，不等于：!= 或 <>
逻辑运算 :
与:and 或:or 非:not
身份运算:
is null : 查看为null 的信息
is not null: 查看不为null 的信息
select * from employee where salary not in (2000,1800)
范围筛选:
多选一: 列名 in (值1,值2...)
select * from score where number in (60,90);
在一个模糊的范围里:
在一个数值区间: between + and
select * from score where number between 60 and 100;
字符的模糊查询: like + 通配符
通配符 % : 匹配任意长度的任意内容
select * from student where sname like'a%';
通配符 _ : 匹配一个字符长度的任意内容
正则匹配: regexp , 更加细粒度的匹配的时候
select * from 表名 where 字段 regexp 正则表达式
select * from employee where emp_name regexp '^j[a-z]{5}';
查看岗位是teacher 且名字是jin开头的员工姓名,薪资
. select emp_name,salary from employee where post ='teaccher' and emp_name like'jin%';
. select emp_name,salary from employee where post = 'teacher' and emp_name regexp'^jin.*';
group by 分组:
分组:
会把在group by 后面的这个字段中的每一个不同的项都保留下来,并且把值是这一项的所有行归为一组
. select * from teacher group by sname;
.可以完成去重 : select 字段名 from 表名 group by 字段名;
.相当于 : select distinct 字段名 from 表名;
聚合:
把很多行的同一字段进行一些统计,最终的到一个结果
.count(字段):统计这个字段的数值的和
.avg(字段):统计这个字段对应的数值的平均值
.min(字段):统计这个字段对应的数值的最小值
.max(字段):统计这个字段对应的数值的最大值
分组聚合: 总是根据会根据重复的项来进行分组,分组总是和聚合函数一起用
.求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到
.但是要得到对应的人,就应该通过多表查询
.求最晚入职的员工,实际上是最大的入职日期,即使用 max(), 反之亦然
实例:
求各个部们的人数:
select count(*) from student group by sname;
求公司里男生和女生的人数:
select gender,count(*) from student group by gender;
求各部门年龄最小的
select post,min(age) from employee group by post;
having 语句 : 过滤组
执行顺序:
.总是先执行where 在执行group by分组
.所以相关先分组,之后再根据分组做某些条件筛选的时候,where都用不上
.只能用having来完成
建议: 普通的条件判断用 where, 不要用having
示例: 部门人数大于3的部门
select post from employee group by post having count(*) > 3;
order by 排序:
默认是升序 asc 从小到大 : order by 某一个字段 asc;
指定降序排序 desc 从小到大: order by 某一个字段 desc;
指定先根据第一个字段升序排列,在第一个字段相同的情况下,在根据第二个字段排列:
order by 第一个字段 asc , order by 第二字段 desc;
limit 限制查询数量:
取前 n个 : limit == limit o,n
.考试成绩的前三名
.入职时间最晚的前三个
分页:
.limit m,n 从m+1开始去n个
.员工展示的网页
limit n offset m == limit m,n 从m+1开始取n个
分页核心SQL:
select * from t3 limit (page-1)*offset, offset;
单表查询顺序:
from 表
where 条件
group by 分组
having 过滤组
select 需要显示的列
order by 排序
limit 前n条
多表查询
两张表连在一起 : select * from emp,department;
连表查询: 把两张表连在一起查
内连接:
inner join
.两张表条件不匹配的项不会出现在结果中
.select * from course as c inner join teacher t on c.tearch_id=t.tid;
外连接:
左外连接: left join
显示全左表中的数据
.select * from teacher as t left join course as c on t.tid=c.tearch_id;
右外连接: right join
显示全右边的数据
.select * from teacher as t right join course as c on t.tid=c.tearch_id;
全外连接: mysql中没有全外连接
要实现全外连接,就使用左外连接 union 右外连接
.select * from teacher as t left join course as c on t.tid=c.tearch_id union
select * from course as c right join teacher as t on t.tid=c.tearch_id;
连接的语法:
.select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
.常用: 内连接和左连接
示例:
查询大于部门内平均年龄的员工名,年龄
select * from emp inner join (select dep_id,age(age)avg_age from emp group by dep_id)
as d on emp.dep_id=d.dep_id where emp.age > d.avg_age;
子查询 : 常用逻辑查询拼接
示例一: 找到技术部门所有人的姓名
先找到部门表技术部门的部门id
select id from department where name='技术';
在找到表中部门id=200
select name from emp where dep_id=查询结果;
子查询:
select name from emp where dep_id=(select id from department where name='技术');
示例二:查看不足1人的部门名 (子查询得到的是有人的部门id)
查emp表中有哪些部门id
select dep_id from emp group by dep_id;
在看department表中
select * from department where id not in(查询结果);
子查询:
select * from department where id not in (select dep_id from emp group by dep_id);
四、索引
索引的定义：
就是建立起的一个在存储表阶段就有的一个存储结构，能在查询的时候加速
索引的重要性：
读写比例 ：10:1
读（查询）的速度就至关重要了
索引的原理：block 磁盘预读原理
读硬盘的io操作的时间非常的长，比CPU执行指令的时间长很多
尽量的减少IO次数才是读写数据的主要要解决的问题
数据库的存储方式 :
新的数据结构 —— 树
平衡树 balance tree - b树
在b树的基础上进行了改良 - b+树
分支节点和根节点都不再存储实际的数据了，让分支和根节点能存储更多的索引的信息，就降低了树的高度，所有的实际数据都存储在叶子节点中
在叶子节点之间加入了双向的链式结构，方便在查询中的范围条件
mysql当中所有的b+树索引的高度都基本控制在3层
io操作的次数非常稳定
有利于通过范围查询
什么会影响索引的效率 —— 树的高度
对哪一列创建索引，选择尽量短的列做索引
对区分度高的列建索引，重复率超过了10%那么不适合创建索引
聚集索引和辅助索引 :
在innodb中，聚集索引和辅助索引并存的，在myisam中，只有辅助索引，没有聚集索引
聚集索引 - 主键
查询速度更快
数据直接存储在树结构的叶子节点
辅助索引 - 除了主键之外所有的索引都是辅助索引
查询速度稍慢
数据不直接存储在树中
索引的种类 :
primary key 主键：聚集索引，约束的作用：非空 + 唯一，联合主键
unique 自带索引：辅助索引，约束的作用：唯一，联合唯一
index：辅助索引，没有约束作用，联合索引
创建索引：
create index 索引名字 on 表(字段)
删除索引：
drop index 索引名 on 表名字;
索引是如何发挥作用的？
select * from 表 where id = xxxxx
在id字段没有索引的时候，效率低
在id字段有索引的之后，效率高
查询的字段不是索引字段，效率也低
联合索引 :
创建联合索引：create index ind_mix on s1(id,name,email);
在联合索引中如果使用了or条件索引就不能生效
最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列
select * from s1 where id =1000000; 能命中索引
select * from s1 where email = 'eva1000000@oldboy'; 不能命中索引
在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了
select * from s1 where id >1000000 and email = 'eva1000001@oldboy'; 能命中索引
select * from s1 where id =1000000 and email like 'eva%'; 不能命中索引
什么时候用联合索引？
只对a或与a有关的，如abc等条件进行索引，而不会对b或c进行单列的索引时，使用联合索引
单列索引 :
选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符
使用or来连接多个条件时，在满足上述条件的基础上，对or相关的所有列分别创建索引
覆盖索引：
如果我们使用索引作为条件查询，查询完毕之后，不需要回表查，这就是覆盖索引
合并索引：
对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这两个索引就成为了合并索引
执行计划 explain :
如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划
情况1：假设30000000条数据，sql：20s
使用explain + sql语句 --> 并不会真正的执行sql，而是会给你列出一个执行计划
情况2：数据不足时，使用explain + sql语句 --> 并不会真正的执行sql，而是会给你列出一个执行计划
建表、使用sql语句的时候注意：
char代替varchar
连表代替子查询
创建表的时候，固定长度的字段放在前面
索引不生效的原因
1, 要查询的数据的范围大，索引不生效
比较运算符：> < >= <= !=
between and
select * from 表 limit 1000000，5
select * from 表 where id between 1000000 and 1000005;
like，结果的范围大，索引不生效
如果 abc% 索引生效，%abc索引就不生效
2, 如果索引列内容的区分度不高，索引不生效
3, 索引列在条件中参与计算，索引不生效
select * from s1 where id*10 = 1000000;
4, 对两列内容进行条件查询
and
and条件两端的内容，优先选择一个有索引的，并且树形结构更好的，来进行查询
两个条件都成立才能完成where条件，先完成范围小的缩小后面条件的压力
select * from s1 where id =1000000 and email = 'eva1000000@oldboy';
or
or条件的，不会进行优化，只是根据条件从左到右依次筛选
条件中带有or的要想命中索引，这些条件中所有的列都是索引列
select * from s1 where id =1000000 or email = 'eva1000000@oldboy';
5, 不满足联合索引的最左前缀原则，索引不生效
最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列