mysql的基本操作

一、库操作

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

二、表操作

2.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;
删
- 删除表：drop table 表名;
  - drop table student;
改
- 添加字段：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;
查：
- 查看当前文件夹中有多少张表：show tables;
- 查看表的结构
  - 能够看到和这张表相关的所有信息：show create table 表名;
  - 只能查看表的字段的基础信息：desc 表名; / describe 表名;

2.2 表与表之间的关系

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

三、数据操作

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

3.2 单表查询

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 表名;
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.*';
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
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,第二个字段 desc;
limit 限制查询数量
- 取前n个：imit n == limit 0,n
  - 考试成绩的前三名
  - 入职时间最晚的前三个
- 分页：limit m,n，从m+1开始取n个
  - 员工展示的网页
- limit n offset m == limit m,n 从m+1开始取n个
单表查询顺序
- from 表
- where 条件
- group by 分组
- having 过滤组
- select 需要显示的列
- order by 排序
- limit 前n条

3.3 多表查询

两张表连在一起：select * from emp,department;
连表查询：把两张表连在一起查
- 内连接：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;
子查询：常用逻辑查询拼接
- 示例一：找技术部门的所有人的姓名
  - 先找到部门表技术部门的部门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 索引的基本知识

索引的定义：就是建立起的一个在存储表阶段就有的一个存储结构，能在查询的时候加速
索引的重要性：
- 读写比例：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的条件让两个索引同时生效了，那么这两个索引就成为了合并索引
执行计划 : 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划
- 情况1：假设30000000条数据，sql：20s
  - 使用explain + sql语句 --> 并不会真正的执行sql，而是会给你列出一个执行计划
- 情况2：数据不足时，使用explain + sql语句 --> 并不会真正的执行sql，而是会给你列出一个执行计划
建表、使用sql语句的时候注意：
- char代替varchar
- 连表代替子查询
- 创建表的时候，固定长度的字段放在前面

4.2 索引不生效的原因

要查询的数据的范围大，索引不生效
- 比较运算符：> < >= <= !=
- between and
  - select * from 表 limit 1000000，5
  - select * from 表 where id between 1000000 and 1000005;
- like，结果的范围大，索引不生效
  - 如果 abc% 索引生效，%abc索引就不生效
如果索引列内容的区分度不高，索引不生效
索引列在条件中参与计算，索引不生效
- select * from s1 where id*10 = 1000000;
对两列内容进行条件查询
- 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';
不满足联合索引的最左前缀原则，索引不生效
- 最左前缀原则：在联合索引中，条件必须含有在创建索引的时候的第一个索引列

五、数据备份和恢复

数据备份：使用的不再是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;    # 提交事务，此时锁才结束

posted @ 2019-06-03 21:08 林染阅读(291) 评论(0) 编辑收藏举报

刷新页面返回顶部

林染

如切如磋，如琢如磨

mysql的基本操作

一、库操作

二、表操作

2.1 增删改查

2.2 表与表之间的关系

三、数据操作

3.1 增删改查

3.2 单表查询

3.3 多表查询

四、索引

4.1 索引的基本知识

4.2 索引不生效的原因

五、数据备份和恢复

六、事务和锁

公告

林染

如切如磋，如琢如磨

mysql的基本操作

一、库操作

二、表操作

2.1 增删改查

2.2 表与表之间的关系

三、 数据操作

3.1 增删改查

3.2 单表查询

3.3 多表查询

四、索引

4.1 索引的基本知识

4.2 索引不生效的原因

五、数据备份和恢复

六、事务和锁

公告

三、数据操作