mysql的基本操作

一、库操作

1. 创建库：create database 数据库的名字;
2. 删除库：drop database 数据库的名字;
3. 查看当前有多少个数据库：show databases;
4. 查看当前使用的数据库：select database();
5. 切换到这个数据库(文件夹)下：use 数据库的名字;

二、表操作

2.1 增删改查

1. 增
   • 创建表：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;
2. 删
   • 删除表：drop table 表名;
     • drop table student;
3. 改
   • 添加字段：alter table 表名 add 字段名 数据类型(宽度) 约束;
   • 删除字段：alter table 表名 drop 字段名;
   • 修改字段：
     • 修改已经存在的字段的类型、宽度、约束：使用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;
4. 查：
   • 查看当前文件夹中有多少张表：show tables;
   • 查看表的结构
     • 能够看到和这张表相关的所有信息：show create table 表名;
     • 只能查看表的字段的基础信息：desc 表名; / describe 表名;

2.2 表与表之间的关系

1. 两张表中的数据之间的关系
   • 多对一 ：foreign key ，永远是在多的那张表中设置外键
     • 多个学生都是同一个班级的，学生是多，班级是一
     • 两张表：学生表和班级表，学生表关联班级表
   • 一对一 ：foreign key +unique，后出现的后一张表中的数据作为外键，并且要约束这个外键是唯一的
     • 一个学生是一个客户，两张表：学生表和客户表
     • 客户表作为外表，在学生表中设置外键
   • 多对多 ：产生第三张表，把两个关联关系的字段作为第三张表的外键
     • 一本书有多个作者，一个作者有多本书，两张表：书名表和作者表

三、 数据操作

3.1 增删改查

1. 增：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);
2. 删：delete
   • delete from 表名 where 条件;
     • 删除id=1那行的数据：delete from student where id=1;
   • delete from 表名;
     • 删除所有的数据：delete from student;
3. 改：update
   • update 表名 set 字段名=新值;
     • 修改所有的数据：update student set name = 'yuan';
   • update 表名 set 字段名=新值 where 条件；
     • 修改id=2那行的数据：update student set name = 'wusir' where id=2;
4. 查：
   • 单表查询
   • 多表查询

3.2 单表查询

1. select 语句
   • select * from 表名;
   • select 字段,字段.. from 表名:
   • distinct去重：select distinct 字段,字段.. from 表名;
   • 对int类型四则运算：select 字段*5 from 表名;
   • 重命名：
     • select 字段 as 新名字,字段 as 新名字 from 表名;
     • select 字段 新名字 from 表名;
   • 字符串拼接：使用concat()函数
     • select concat('姓名：',name,'年薪：',salary*12) as 表名;
2. where 语句
   • 比较运算：
     • 大于：>，小于： <，等于： =，大于等于：>=，小于等于：<=，不等于：!= 或 <>
   • 逻辑运算：条件的拼接
     • 与：and，或：or，非：not
   • 身份运算：关于null
     • is null：查看为null的信息
     • is not null：查看不为null的信息
       • select * from employee where salary not in (20000,30000,3000,19000,18000,17000)
   • 范围筛选
     • 多选一：字段名 in (值1,值2,值3)
       • select * from employee where salary in (20000,30000,3000,19000,18000,17000)
     • 在一个模糊的范围里
       • 在一个数值区间：between + and
         • select emp_name from employee where salary between 10000 and 20000;
       • 字符串的模糊查询：like + 通配符
         • 通配符 %：匹配任意长度的任意内容
         • 通配符 _ ：匹配一个字符长度的任意内容
       • 正则匹配：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='teacher' and emp_name like 'jin%';
         • select emp_name,salary from employee where post='teacher' and emp_name regexp '^jin.*';
3. group by 分组
   • 分组：会把在group by后面的这个字段中的每一个不同的项都保留下来，并且把值是这一项的的所有行归为一组
     • select * from employee group by post;
     • 可以完成去重：select 字段名 from 表名 group by 字段名;
       • 相当于：select distinct 字段名 from 表名;
   • 聚合：把很多行的同一个字段进行一些统计，最终的到一个结果
     • count(字段)：统计这个字段有多少项
       • 统计表有多少项：select count(*) from 表名;
     • sum(字段)：统计这个字段对应的数值的和
     • avg(字段)：统计这个字段对应的数值的平均值
     • min(字段)：统计这个字段对应的数值的最小值
     • max(字段)：统计这个字段对应的数值的最大值
   • 分组聚合：总是根据会重复的项来进行分组，分组总是和聚合函数一起用
     • 求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到
       • 但是要得到对应的人，就必须通过多表查询
     • 求最晚入职的员工，实际上是最大的入职日期，即使用max()，反之亦然
     • 示例：
       • 求各个部门的人数
         • select count(*) from employee group by post
       • 求公司里 男生 和女生的人数
         • select count(id) from employee group by sex
       • 求各部门年龄最小的
         • select post,min(age) from employee group by post
4. having 语句：过滤组
   • 执行顺序：
     • 总是先执行where，再执行group by分组
     • 所以相关先分组，之后再根据分组做某些条件筛选的时候，where都用不上
     • 只能用having来完成
   • 建议：普通的条件判断用where，不要用having
   • 示例：部门人数大于3的部门
     • select post from employee group by post having count(*) > 3;
5. order by 排序
   • 默认是升序asc 从小到大：order by 某一个字段 asc;
   • 指定降序排列desc 从大到小：order by 某一个字段 desc;
     
   • 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列 ：
     • order by 第一个字段 asc,第二个字段 desc;
6. limit 限制查询数量
   • 取前n个：imit n == limit 0,n
     • 考试成绩的前三名
     • 入职时间最晚的前三个
   • 分页：limit m,n，从m+1开始取n个
     • 员工展示的网页
   • limit n offset m == limit m,n 从m+1开始取n个
7. 单表查询顺序
   • from 表
   • where 条件
   • group by 分组
   • having 过滤组
   • select 需要显示的列
   • order by 排序
   • limit 前n条

3.3 多表查询

1. 两张表连在一起：select * from emp,department;
2. 连表查询：把两张表连在一起查
   • 内连接：inner join
     • 两张表条件不匹配的项不会出现再结果中
     • select * from emp inner join department on emp.dep_id = department.id;
   • 外连接：
     • 左外连接：left join
       • 永远显示全量的左表中的数据
       • select * from emp left join department on emp.dep_id = department.id;
     • 右外连接：right join
       • 永远显示全量的右表中的数据
       • select * from emp right join department on emp.dep_id = department.id;
     • 全外连接：mysql中没有全外连接
       • 要实现全外连接，就使用左外连接union右外连接
       • select * from emp left join department on emp.dep_id = department.id union select * from department right join emp on emp.dep_id = department.id;
   • 连接的语法：
     • select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
     • 常用：内链接和左外链接
   • 示例：查询大于部门内平均年龄的员工名、年龄
     • select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d on emp.dep_id = d.dep_id where emp.age > d.avg_age;
3. 子查询：常用逻辑查询拼接
   • 示例一：找技术部门的所有人的姓名
     • 先找到部门表技术部门的部门id
       • select id from department where name = '技术';
     • 再找emp表中部门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);

四、索引

4.1 索引的基本知识

1. 索引的定义：就是建立起的一个在存储表阶段就有的一个存储结构，能在查询的时候加速
2. 索引的重要性：
   • 读写比例 ：10:1
   • 读（查询）的速度就至关重要了
3. 索引的原理：block 磁盘预读原理
   • 读硬盘的io操作的时间非常的长，比cpu执行指令的时间长很多
   • 尽量的减少io次数才是读写数据的主要要解决的问题
4. 数据库的存储方式
   • 新的数据结构 —— 树
   • 平衡树 balance tree - b树
   • 在b树的基础上进行了改良 - b+树
     • 分支节点和根节点都不再存储实际的数据了，让分支和根节点能存储更多的索引的信息，就降低了树的高度，所有的实际数据都存储在叶子节点中
     • 在叶子节点之间加入了双向的链式结构，方便在查询中的范围条件
   • mysql当中所有的b+树索引的高度都基本控制在3层
     • io操作的次数非常稳定
     • 有利于通过范围查询
   • 什么会影响索引的效率 —— 树的高度
     • 对哪一列创建索引，选择尽量短的列做索引
     • 对区分度高的列建索引，重复率超过了10%那么不适合创建索引
5. 聚集索引和辅助索引
   • 在innodb中，聚集索引和辅助索引并存的，在myisam中，只有辅助索引，没有聚集索引
   • 聚集索引 - 主键
     • 查询速度更快
     • 数据直接存储在树结构的叶子节点
   • 辅助索引 - 除了主键之外所有的索引都是辅助索引
     • 查询速度稍慢
     • 数据不直接存储在树中
6. 索引的种类
   • primary key 主键：聚集索引，约束的作用：非空 + 唯一，联合主键
   • unique 自带索引：辅助索引，约束的作用：唯一，联合唯一
   • index：辅助索引，没有约束作用，联合索引
7. 创建索引：create index 索引名字 on 表(字段)
8. 删除索引：drop index 索引名 on 表名字;
9. 索引是如何发挥作用的？
   • select * from 表 where id = xxxxx
     • 在id字段没有索引的时候，效率低
     • 在id字段有索引的之后，效率高
     • 查询的字段不是索引字段，效率也低
10. 联合索引
    • 创建联合索引：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进行单列的索引时，使用联合索引
11. 单列索引
    • 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符
    • 使用or来连接多个条件时，在满足上述条件的基础上，对or相关的所有列分别创建索引
12. 覆盖索引：如果我们使用索引作为条件查询，查询完毕之后，不需要回表查，这就是覆盖索引
13. 合并索引：对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这两个索引就成为了合并索引
14. 执行计划 : 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划
    • 情况1：假设30000000条数据，sql：20s
      • 使用explain + sql语句 --> 并不会真正的执行sql，而是会给你列出一个执行计划
    • 情况2：数据不足时，使用explain + sql语句 --> 并不会真正的执行sql，而是会给你列出一个执行计划
15. 建表、使用sql语句的时候注意：
    • char代替varchar
    • 连表代替子查询
    • 创建表的时候，固定长度的字段放在前面

4.2 索引不生效的原因

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. 不满足联合索引的最左前缀原则，索引不生效
   • 最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列

五、数据备份和恢复

• 数据备份：使用的不再是mysql.exe，而是mysqldump.exe

  #语法：
  mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
  
  #示例：
  #单库备份
  mysqldump -uroot -p123 db1 > db1.sql
  mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
  
  #多库备份
  mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
  
  #备份所有库
  mysqldump -uroot -p123 --all-databases > all.sql 

• 数据恢复：

  #方法一：
  [root@egon backup]# mysql -uroot -p123 < /backup/all.sql
  
  #方法二：
  mysql> use db1;
  mysql> set sql_log_bin=0;   #关闭二进制日志，只对当前session生效
  mysql> source /root/db1.sql    #主要使用这种方式

六、事务和锁

• 关键字：begin，for update，commit

  begin;     # 开启事务
  select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
  update emp set salary=10000 where id = 1;   # 完成更新
  commit;    # 提交事务，此时锁才结束