mysql的基本操作

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;    # 提交事务，此时锁才结束
```
相关阅读:
一些有用的DNN方法
 将VB.Net反编译时StringType.StrCmp用C＃中的函数string.Compare替换
 接一个DNN安装时的未知错误，终于找到了原因
 这是用performancing for firefox编写的blog
这是用word2007发表的blog
一个DNN安装时的未知错误
 后端——框架——切面框架——aop——Pointcut表达式
 Metroid Prime Chs 20101004
第一财经周刊：书店的黄昏
 这年头连萝卜都靠不住了
原文地址：https://www.cnblogs.com/zengyi1995/p/10970063.html

mysql的基本操作

一、库操作

二、表操作

2.1 增删改查

2.2 表与表之间的关系

三、 数据操作

3.1 增删改查

3.2 单表查询

3.3 多表查询

四、索引

4.1 索引的基本知识

4.2 索引不生效的原因

五、数据备份和恢复

六、事务和锁

三、数据操作