mysql

                                                    mysql

• 一、mysql

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、Mysql？

mysql

管理-服务和应用程序-服务

net stop  mysql10815

net start  mysql10815

mysql -h localhost -P 3306 -u root -p

show databases;

use 库名；

show tables;

show tables from 库名；

select database();

desc 表名；

 

 

select username as 用户名,password  as 密码 from user;

select salary as "out put" from employees;

 

去重 select distinct department_id from employees;

 

IFNULL用法
select IFNULL(commission_pct,0) AS 奖金率,commission_pct from employees;

concat用法
select concat(username,',',username,',',IFNULL(commission_pct,0)) as out_put from employees;

 

查询部门编号不是在90到110之间，或者工资高于15000的员工信息
select * from employees where not(department_id>=90 and department_id<=110) or salary>15000;

 

 

 

 

 

 

 

查询员工包含字符a的员工信息
select * from employees where username like '%a%';

查询员工第三个字符为e,第五个字符为a的员工名和工资
select username,salary from employees where username like '__n_e%';

查询员工名中第二个字符为_的员工名
select username from employees where username like '_\_%';
select username from employees where username like '_$_%' ESCAPE '$';指定转义字符

查找job_id(字符串)为 it_prot ad_vp ad_pres 的信息
select username job_id where job_id ='it_prot' or job_id ='ad_vp' or job_id ='ad_pres'
select username job_id where job_id in ('it_prot' ,'ad_vp' ,'ad_pres')

查询没有奖金的员工名和奖金率
select username,commission_pct from employees where commission_pct IS NULL;
select username,commission_pct from employees where commission_pct IS NOT NULL;

查询没有奖金，且工资小于18000的salary,last_name
select salary,last_name from employees where commission_pct is null and salary<18000;

查询employees表中，job_id不为‘IT'或者工资为12000的员工信息
selet * from employees where job_id <> 'IT' or salary=12000;

查看下部门departments表中涉及到的位置编号
select distinct location_id from department;

查看下一下sql语句是不是一样
select * from employees;
select * from employees where username like '%%' and commission_pct like '%%';
select * from employees where username like '%%' or commission_pct like '%%';
12结果不一样，判断字段有没有null
13结果一样

查询员工信息 排序
select * from employees ORDER BY salary DESC;

按姓名长度显示员工的姓名和工资【按函数排序】
select LENGTH(last_name)  字节长度,last_name,salary from employees ORDER BY LENGTH(last_name) DESC;

查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
select * from employees order by salary ASC,employees_id DESC;

查询员工的姓名和部门号和年薪，按年薪降序 按照姓名升序
select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees ORDER by 
年薪 DESC,last_name ASC;

查询工资不在8000到17000的员工的姓名和工资，按工资降序
select last_name,salary from employees where salary NOT between 8000 and 17000 order by salary desc;

查询邮箱中包含e的员工信息，并按着邮箱的字节数降序，再按着部门号升序
select *,LENGTH(email) from employees where email like '%e%' order by LENGTH(email) desc,department_id ASC;

concat拼接字符串
select concat(last_name,'_',first_name) 姓名 from employees;
upper,lower

将姓变大写，名变小写，然后拼接
select concat(upper(last_name),lower(first_name)) 姓名 from employees;
substr substring

截取从指定索引处后面所有字符
select substr('李莫愁爱上了陆展元') out_put;

截取从指定索引处指定字符串长度的字符
select substr('李莫愁爱上了陆展元',1,3) out_put;

姓名中首字符大写，其他字符小写然后用_拼接，显示出来
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2)))   out_put from employees;

instr返回子串第一次出现的索引，如果找不到返回0
select INSTR('杨不悔爱上了殷六侠'，'殷六侠') AS out_put;

trim()
select LENGTH(TRIM('  张翠山 ')) as out_put;
select TRIM('a' from 'aaaa张aa翠山aaa') as out_put;

lpad用指定的字符串实现左填充指定长度
select LPAD('殷素素',10,'*') as out_put;

rpad用指定字符实现又填充指定长度
select RPAD('殷素素',12,'ab') as out_put;

replace替换
select REPLACE('周芷若周芷若张无忌','周芷若'，'赵敏') as out_put;

返回当前系统日期+时间
select NOW();

返回当前系统日期，不包含时间
select CURDATE();

返回当前时间，不包含日期
select CURTIME();

 

 

 

 

 

 

 

 

 

 

 

 

distinct 去重
select SUM(DISTINCT salary),SUM(salary) from employees;
select COUNT(DISTINCT salary),COUNT(salary) from employess;

查询员工表中的最大入职时间和 最小入职时间的相差天数
select DATEDIFF(MAX(hiredate),MAX(hiredate)) DIFFRENCE from employees;

查询每个工种的最高工资
select MAX(salary),job_id from employees GROUP BY job_id;

查询每个位置上的部门个数
select COUNT(*),location_id from departments GROUP BY location_id;

查询邮箱中包含a字符的，每个部门的平均工资
select AVG(salary),department_id from employees where email like '%a%' GROUP BY department_id;

查询有奖金的每个领导手下员工的最高工资
select MAX(salary),manager_id from employees where commission_pct IS NOT NULL GROUP BY manager_id;

 

 

 

 

 

20210209号更新====================================

为表起别名
select e.last_name.e.job_id,j.job_title 
from emplpyees e, jobs j
 where e.`job_id`=j.`job_id`;

查询有奖金的员工名，部门名
select last_name,department_name,commision_pct 
from employees e,departments d 
where
e.`department_id` = d.`department_id` and e.`commission_pct` IS not NULL;

查询城市名中第二个字符为o的部门名和城市名
select department_name,city 
from departments d,locations l 
where d.`location_id` = l.`location_id` and like `_o%`;

查询每个城市你的部门个数
select count(*) 个数,city 
from departments d,locations l
 where d.`location_Id`=l.`location_id`
group by city;

查询有奖金的每个部门的部门名和部门的领导编号和改部门的最低工资
select department_name,d. `manager_id`,min(salary) 
from departments d,employees e 
where d.`department_id`=e.`department_id` and commision_pct is not null 
group
by department_name,d.`manager_id`;

查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_title,count(*) 
from employees e,jobs j 
where e.`job_id`=j.`job_id`
group by job_title 
order by count(*) desc;

查询员工名，部门名和所在的城市
select last_name,department_name,city 
from employees e,departments d,locations l
where e.`department_id`=d.`department_id` and d.`location_id` =l.`location_id` 
and city like 's%'//加筛选条件
group by department_name;//加筛选条件

查询员工的工资和工资级别
select salary,grade_level 
from employees e,job__grades g 
where salary between 
g,`lowest_sal` and g.`highest_sal`
and g.`grade_level`=`A`;//加筛选条件

查询 员工名和上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name 
from employees e,employees m, 
where e.`manager_id`=m.`employee_id`;

显示所有员工的姓名，部门号和部门名称
select last_name,d.department_id,department_name 
from employees e,
departments  d where e.`department_id` = d.`department_id`;

查询90号部门员工的job_id和90号部门的location_id
select job_id,location_id 
from employees e,departments d 
where e.`department`
=d.`department_id` and e.`department_id`=90;

选择所有奖金的员工的last_name,部门名，位置，城市
select last_name,department_name,l.location_id,city
from employees e,departments d,locations l 
where e.department_id=d.department_id
and d.location_id=l.location_id and e.commission_pct is not null;

 

查询数据表中某个字段（tel）出现相同内容大于1的情况
select id,name,tel from `member` where tel in (select tel from `member` group by tel having count(tel)>1);

选择city在Toronto工作的员工的last_name,job_id,department_id,department_name
select last_name,job_id,d.department_id,department_name 
from employees e,departments d,locations l 
where e.department_id =d.department_id and d.location_id`
=l.location_id and city='Toronto';

查询每个工种，每个部门的部门名，工种名和最低工资
select department_name,job_title,mix(salary) 最低工资
from employees e,departments d,jobs j
where e.`department_id`=d.`department_id`
and e.`job_id`=j.`job_id`
group by department_name,job_title;

查询每个国家下的部门个数大于2的国家编号
select country_id,count(*) 部门个数
from departments d,location l
where d.`location_id`=l.`location_id`
group by country_id
having count(*)>2;

 

 

 

 

 

 

 

今天解释一下数据库的隔离级别
1，八戒去高老庄领取每个月生活费，他媳妇给他打了500，此时银行卡显示500（此时数据还没有提交），八戒欣喜若狂，静静的等待着钱到账（这就是脏读）;
2, 八戒媳妇忽然觉得不应该给那么多，正所谓男人不能惯越惯越混蛋，不能给那么多钱，又及时撤销了，重新打了50块钱（这个时候更新了新数据），这下八戒再一刷新余额，发现，md变成了50，欲哭无泪（这就是不可重复读）
3，原本八戒已经说出去的大话请求兄弟们（沙僧和猴哥）吃饭，因为这些钱已经让八戒很心痛了，但是到了包间才发现，（谁知道又来了一个）唐僧已在此等候多时了（这就是幻读）

 

进行第一个隔离级别设置（这四个步骤里面的详细细节每行代表前后执行顺序，一定不能乱改,而且只能在cmd不同的链接mysql上进行测试，下面步骤无法在navicate测试，可在本地安装mysql,打开两个cmd测试）
查看隔离级别
打开一个cmd:cmd01
select @@tx_isolation;
set names gbk;
设置隔离级别
set session transaction isolation level read uncommitted;
use test;
set sutocommit=0;
update account set account_num+1 where account_id=1;//原来是10

再开一个cmd:cmd02
set session transaction isolation level read uncommitted;
set sutocommit=0;
select * from sccount;
此时(cmd01没有提交)查出来的 account_num是11====这就是脏读
cmd01提交 rollback之后  此时再在cmd02   select 查询就是不可重复读

进行第二个隔离级别设置
cmd01:set session transaction isolation level read committed;
set sutocommit=0;
update account set account_num+1 where account_id=1;//原来是10
cmd02:set session transaction isolation level read committed;和set sutocommit=0;
cmd02查询  此时account_num是10 没有变，避免了脏读，但是当cmd01提交之后，不可重复读和幻读避免不了。

进行第三个隔离级别设置
cmd01:set session transaction isolation level repeatable read;
set sutocommit=0;
update account set account_num+1 where account_id=1;//原来是10
cmd02：set session transaction isolation level repeatable read;和set sutocommit=0;
此时cmd02查询没有改变，没有出现脏读
然后cmd01提交 ，在cmd02查询依旧没有改变，不可重复读没有出现（幻读不可解决），执行在cmd02
执行提交commit;set sutocommit=0;select 下一次事务里面的查询的时候，草可以查询到最新的数据

测试幻读：
cmd01:set session transaction isolation level repeatable read;
set sutocommit=0;
update account set account_num+1 where 1=1 ;//select查询三条，原来共三行数据
cmd02 :set session transaction isolation level repeatable read;
set sutocommit=0;
insert into account('10')增加一条数据，并commit
此时再执行cmd01的更新，显示更新4条比刚刚查询的多一条数据被更新，这就是解释了没有解决幻读的问题

进行设置最高隔离级别的设置（四）
cmd01：set session transaction isolation level serializable;
set sutocommit=0;
update account set account_num+1 where 1=1 ;//select查询三条，原来共三行数据
cmd02：set session transaction isolation level serializable;
set sutocommit=0;
insert into account('10')增加一条数据（发现此时操作不能实现，因为他在等待前面的cmd01事务执行结束，他才会执行，否则提示超时失败）这样也就避免了幻读，但是性能十分低。因为如果选择他，意味着这一条数据被锁住，其他事务都会等他执行，才会依次执行。

20210214记录

查看文件ls -ltr

ps -ef | grep mysql

主从复制

 

下面的五项在mmap思维导图，由于内容太多，存在了百度云盘。

需要请联系作者。

                                   一，mysql的架构介绍

                                  二，索引优化分析

                                  三，查询截取分析

                                  四，Mysql锁机制

                                  五，主重复制