mysql 基础 第一篇

sql语言

事务、存储引擎、索引、sql优化、锁

日志、主从复制、读写分离、分库分表

 

初级工程师：mysql概述、sql、函数、约束、多表查询、事务

中级工程师：存储等

 

面试题目背诵：

什么是事务，以及事务的四大特性？

事务的隔离级别有哪些，mysql默认是哪个？

内连接与左外连接的区别是什么？

常用的存储引擎？lnnoDB与MylSAM的却别？

Mysql默认lnnoDB引擎的索引是什么数据结构？

如何查看Mysql的执行计划？

索引失效的情况有哪些？

什么是回表查询？

什么是MVCC？

Mysql主从复制原理是什么？

主从复制后的读写分离如何实现？

数据库的分库分表如何实现？

 

sql的学习

DDL

>>查询：show databases;

>>查询当前数据库：select database(); 只有在use 数据名 之后 才可以查询当前数据库

>>创建：create database 数据名;

>>删除：drop database 数据库名;

>>使用：use 数据名;

>>查询当前数据库所有表：show tables;

>>查询表结构：desc 表名;

>>查询指定表的建表语句：show creat table 表名;

>>ddl-表操作-创建 (注意：在创建之前切换到自定义的数据库 use 已创建的数据库名 (目的 是不要在系统中执行)

     create table 表名（

     id int comment '编号',

     name varchar(50) comment '姓名'

     ）comment '数据表的名字'；

>>查询当前数据库所有表 show tables;

>>查询表结构 desc 表名；

>>查询制定表的建表语句 show create table  表名字

>>表操作--数据类型

     数值类型 tinyint、int、float doubie(4,1)其中4代表整体长度；1代表小数的个数只有1个

     字符串类型 char()  定长字符串 不可变的字段、varchar()  变长字符串 可变的字段

     日期时间类型 date 1000-01-01、time 838:59:59 datetime 1000-01-01 838:59:59

>>添加字段   alter table 表名 add 字段名 数据类型 comment '  ',

>>修改数据类型   alter table 表名 modlfy 字段名 新数据类型；

>>修改字段名和字段类型   alter table 表名 change 旧字段名 新字段名 数据类型 comment '  ';

>>删除字段 alter table 表名 drop 字段名 ;

>>修改表名 alter table 表名 rename to 新表名;

>>删除表 drop table if exists 表名 ;

>>穿出指定表，并重新创建该表 truncate table 表名 ;

 

图形化界面

DataGrip 是一款数据库管理客户端工具，方便连接到数据库服务器，执行sql、创建表、创建索引以及导出数据

DML

》添加数据

>> 给指定字段添加数据 insert into 表名（字段1，字段2...)  values (值1，值2...) ；
>> 给全部字段添加数据  insert into 表名 values (值1，值2...) ；

>> 批量添加数据 insert into 表名（字段1，字段2...)  values (值1，值2...)，(值1，值2...)；

》修改数据

#修改id为1的数据 将name修改为itheima
update emploree set name= 'ithema'where id=1;

#修改id为1的数据 将name修改为小赵 将gender改成女

update emploree set name='小赵',gender='女' where id=1;
#修改所有员工入职日期为 2008-01-01
update emploree set entrydate = '20080101';

》删除数据

#删除gender为女的员工
delete from emploree where gender = '女';
#删除所有员工
delete from emploree;

 

DQL 查询数据表中的数据 关键词select

》基本查询

 

-- 基本查询
-- 1 查询指定字段 name,workno,age 返回
select name,workno,age from emploree;
-- 2 查询所有字段返回
select id, workno, name, gender, age, idcard, entrydate from emploree;
-- 3 查询所有员工的工位号，并将原来的workno修改为工位号
select workno as '工位号' from emploree;
-- 4 查询所有员工的id号（不要重复）
select distinct id from emploree;

 

》基本查询

>>语法   select 字段列表 from 表名 where 条件列表 ;

>>条件   比较运算符、逻辑运算符

#条件查询
-- 查询年龄等于88的员工
select * from emploree where age = 88;
-- 查询年龄小于30员工
select * from emploree where age <30;
-- 查询没有workno的员工信息
select * from emploree where idcard is null;
-- 查询有workno的员工信息
select * from emploree where idcard is not null;
-- 查询年龄在20岁和30岁之间的员工信息
select * from emploree where age <=30 && age >=20;
# 另一种写法  select * from emploree where between 15 and 20;
-- 查询年龄在20岁 性别是女的员工信息
select * from emploree where gender='女' and age=20 ;
-- 查询年龄在20岁 或等于30岁
select * from emploree where age=30 or age=20;
# 另一种写法  select * from emploree where in (15,20);
-- 查询姓名为两个字的员工信息
select * from emploree where name like '__';
-- 查询姓名最后一位是y的员工信息
select * from emploree where name like '%y';
# 另一种写法  select * from emploree where name like '___y';

>> 聚合函数

#统计员工数量
select count(*) from emploree;
#统计员工的平均年龄
select avg(age) from emploree;
#统计最大年龄
select max(age) from emploree;
#统计最小年龄
select min(age) from emploree;
#统计名字为张四的年龄之和
select sum(age) from emploree where name='张四';

 

>>分组查询

#分组查询
-- 根据性别分组,统计男性员工和女性员工
select gender, count(*) from emploree group by gender;
-- 根据性别分组 统计男性员工 和 女性员工的平均年龄
select gender,avg(age) from emploree  group by gender;
-- 查询年龄大于45的员工，并根据性别分组，获取员工数量 其中 员工数量小于2的性别
select gender,count(*) from emploree where age>45 group by gender having count(*)<2;

where 先执行 having 后执行

 

>> 排序查询

#排序查询
-- 根据年龄对员工进行升序排序
select * from emploree order by age asc;
-- 根据年龄对员工进行降序排序
select * from emploree order by age desc;
-- 根据年龄进行降序排序 年龄相同 再按 入职时间进行降序排序
select * from emploree order by age desc,entrydate desc ;

>>分页查询

（页码-1)乘 页展示记录数

#查询第二页员工数量 每页展示10条记录
select * from emploree limit 10,10;

》执行顺序

>>编写顺序 select >  from   >  where     >  group by  >  having  >  order by  > limit

>>执行顺序 from   > where  > group by >  having      >  select   >  order by  > limit

 

DCL 管理数据库用户 控制数据库的访问 权限

-- 创建用户 it，只能够在当前主机localhost访问，密码 123456
create user 'it'@'localhost' identified by '123456';
-- 创建用户yan ，可以在任意主机访问该数据库，密码 '123456'；
create user 'yan'@'%' identified by '123456';
-- 修改用户 yan 的访问密码为1234；
alter user 'yan'@'%' identified with mysql_native_password by '1234';
-- 删除it'@'localhost的用户
drop user 'it'@'localhost' ;

#查询权限
show grants for 'it'@'localhost';
#授予权限
grant all on user.*to 'it'@'localhost';
#撤销权限
revoke all on user.* from 'it'@'localhost';

函数 指一段可以直接被另一段程序调用的程序或代码

》联合查询 — union （直接合并）,union all （合并并去重）

多次查询的结果合并到一起

注意：1联合的字段要保持一致，

 

》嵌套查询

>>标量子查询  子查询返回的结果是单个值（数字、字符串、日期等）

#标量子查询
-- 查询'销售部'的所有员工信息
-- 拆解需求：a 查询'销售部' 部门id b 查询在'方东白'入职之后的员工信息
select id from dept where name = '销售部';
-- 根据部门id查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部') ;
-- 查询方东白的入职信息
select entrydate from emp where name = '东方白';
-- 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '东方白');

 

》列子查询 子查询返回的结果是一列（可以是多行）

常用的操作符：in 和、 not in 、any 任一、some 、all

-- 查询销售部和市场部的所有员工信息
-- 查询销售部和市场部的id
select id from dept where name = '销售部'or nmae= '市场部';
-- 查询在对应id里面查询员工信息
select * from emploree  where dept_id = (select id from dept where name = '销售部'or nmae= '市场部');
select * from emploree  where dept_id in (2,4);

-- 查询比财务部的所有人工工资都高的员工信息
-- 1.查询财务部
select id from dept where name = '财务部';
-- 2.查询财务部的工资
select salary from emploree where dept_id = (select id from dept where name = '财务部') ;
-- 3.从员工里面筛选所有比财务部的全部人工工资都高的员工信息
select * from emploree where salary > all (select salary from emploree where dept_id = (select id from dept where name = '财务部'));

-- 查询比财务部的最低工资高的所有的员工信息
select id from dept where name = '财务部';
select salary from emploree where dept_id = (select id from dept where name = '财务部') ;
select * from emploree where salary > any (select salary from emploree where dept_id = (select id from dept where name = '财务部'));

》行子查询 结果是一行可以是多列

常用的操作符：=、<>、in、not in

-- 查询 直属领导为张无忌的员工工资
select manger,manageid manageid from emploree where manger = "张无忌";
select * from emploree where (manger,manageid)= (select manageid from emploree where manger = "张无忌");

》表子查询 子查询返回的结果是多行多列

   常用操作: in

-- 查询与 王三和张四 的职位 和薪资相同的员工信息
select salary,job where name='王三'or name='张四';
select * from emploree where (salary,job) in (select salary,job where name='王三'or name='张四');

>> 多表连接

-- 查询入职日期 是2022-01-01 之后的员工信息，及其部门信息
select * from emploree where enterdate > '2022-01-01';
select * from (select * from emploree where enterdate > '2022-01-01') a left join b on a.id=b.dept_id;

>> 讲解left join

例表a
aid      adate
1         a1
2         a2
3         a3

表b
bid      bdate
1         b1
2         b2
4         b4

两个表a,b相连接,要取出id相同的字段
select * from a inner join b on a.aid = b.bid（这是仅取出匹配的数据）
此时的取出的是:
1        a1       b1
2        a2       b2

那么left join 指:
select * from a left join b on a.aid = b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1        a1       b1
2        a2       b2
3        a3       空字符
同样的也有right join
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
 此时的取出的是:
1        a1       b1
2        a2       b2
4        空字符  b4

LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。