1.mysql--常用sql（2）

20、条件查询

(1)条件表达式：< ， > ，=， !=，<>

(2)逻辑运算符:&& ，||， ! ， and ，or， not

(3)模糊查询：like between and,in,is null,is not null

21、通配符

% 任意多个字符
_ 任意单个字符

//查询员工表中，姓名中第三个字符为e，第五个字符为a的员工信息和工资
select lastname,salary from employees where last_name like '__e_a%';

//查询员工表中，姓名中带有“明”字的信息
select * from employees where last_name like '%明%';

//查询员工表中，姓名中带有“明”字的信息
select * from employees where last_name like '%明%';
1
2
3
4
5
6
7
8

22、转义字符

转义字符：\ -可以把_由通配符转换为其本身代表的下划线，escape也可以表示转义，
escape '${}^{\prime }表示把$表示为转义的意思。
例如：查询某个字段中带有下划线，查询时需要查询下换线为条件

select * from employees where late_name='ssk\_sss';
select * from employees where last_name='ssk$_sss' escape '$'; #两句效果等同，escape '$'表示把$用作转义字符，字符自主选择。
1
2

23、数据库种执行脚本

 source  /home/xxx.sql
1

24、查看数据库版本

mysql> select version();
或
[root@host1 ~]# mysql --version
1
2
3

25、查询常量值和表达式

mysql> select 100；
mysql> select '100';
1
2

26、查询表达式

mysql> select 100*98； #结果显示9800，sql语句支持表达式查询，包括+，-，\*，%
1

27、查询函数

mysql> select VERSION();  #查询函数version()的返回值
1

28、sql起别名

mysql> select last_name as 性，first _name as 名 from table1；
mysql> select last_name 姓 from table1； #可省略as
有特殊符号时，需要加" "或者' '
mysql> seclet last_name as "out name" from table1; #空格和out属于特殊字符，不加" ",sql无法辨认
1
2
3
4

29、去重查询

mysql> select distinct last_name from table1;
mysql> select distinct iden_str,capturetime,devicecode from trs_kp_elefence; #多个字段为条件去重
mysql> select count(*) from (select distinct iden_str,capturetime,devicecode from trs_kp_elefence;); #计算去重后的总数量，select distinct count(*) 是错误的，该命令无法执行。
1
2
3

30、字段拼接查询

concat()函数

mysql> select concat(last_name,first_name) as name from table1; #如果拼接字段存在NULL，会造成拼接值为NULL
1

IFNULL()可判断字段是否为NULL，例如IFNULL(devicecode,0)即判断devicecoad字段是否为NULL，如果为NULL则置为0.

mysql> select concat(last_name,first_name，IFNULL(device_code,0)) as name from table1;
1

31、in模糊查询

mysql> select * from employes where  job_id in('IT_PORT','AD_VP','AD_PRES');
#查询表中的员工工种是T_PORT，AD_VP，AD_PRES的员工信息
1
2

32、<=> 安全等于

判断是否等于,IS NULL 仅可以判断NULL值,<=>既可以判断NULL，也可以判断具体值。

33、排序

**group by：**asc代表升序，desc代表降序，不写代表升序，order by 支持单个字段，多个字段，表达式，函数，别名，order by放在语句最后，limit除外

mysql> select * from employees order by salary desc; #按照salary的大小降序排列

mysql> select * from employees order by salary;#升序排列，默认升序，desc表降序，也可以加asc表升序

mysql> select * ,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees order by 年薪 desc；
#计算员工年薪（年薪等于月薪*12*（1+奖金比例，奖金为null时置为0）），并降序排序。order by即可以选择别名年薪，也可以选择表达式，salary*12*(1+IFNULL(commission_pct,0))

mysql> select length(last_name) 字节长度,last_name,salary from employees order by length(last_name); #按字节长度排序

mysql> select * from employees order by salary asc,employees_id desc; #按照员工工资和员工编号降序，规则是先按照员工工资排序升序排序，工资相同时按照员工编号降序排序
1
2
3
4
5
6
7
8
9
10

34、函数及常见函数

1.单行函数
concat，length，ifnull等
2.分组函数（统计函数或聚合函数）

一.字符函数

#（1）length():计算字节长度字符
mysql> select length('john'); #输出为4，显示john占用的字节数
mysql> select last_name,length(last_name) from employees; #查询员工表中last_name列的字段长度

#（2）concat():拼接函数
mysql> select concat(last_name,first_name) as name from table1; #如果拼接字段存在NULL，会造成拼接值为NULL

#（3）upper()将字符转换为大写,lower()将字符转化为小写
mysql> select upper('john');#显示JOHN
mysql> select lower('joHn');#显示john
mysql> select concat(upper(last_name),lower(first_name)) as 姓名 from employees; #将姓都转化为大写，将名字都转化为小写并拼接成一个字段，显示为名字。#函数支持嵌套查询

#（4）substr,sunstring() 截取字符串
mysql> select substr('last_name',1,5) from employees;#截取员工表中last_name字段的第1个-第7个字符
mysql> select substr('last_name',3) from employees;#截取员工表中last_name字段第3个字符之后的字符串
mysql> select concat(upper(substr(last_name,1,1),'_',lower(sunstr(last_name,2)) as name from employees; #将员工表中last_name字段中，第一位大写，后面显示小写并用_进行拼接显示
        
#（5）instr() 显示后面字符串在前面字符中第一次出现的索引位置
      
#（6）trim() 去掉字段的前后的空格,也可以去掉指定字符
 mysql> select length(trim(last_name)) from employees;#显示员工表中last_name去除前后空格以后的字符长度
 mysql> select length(trim('a',last_name) from employees;#显示员工表中last_name去除前后字符a以后的字符长度，仅去除指定字段的前后，中间的为有效字符
                
#（7）lpad() 左填充 rpad() 又填充
mysql> select lpad(last_name,10,*) from employees;#显示员工表中last_name字段，如果不足10个字符则左侧用*号填充，长度超过了10会从左侧进行截断
mysql> select rpad(last_name,10,*) from employees;#显示员工表中last_name字段，如果不足10个字符则右侧用*号填充，长度超过了10会从右侧进行截断
   
#（8）replace() 替换
mysql> select repalce（last_name,'a','e'）as name from employees;#将last_name字段中的字符a全部替换为e
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

二.数学函数

#round():四舍五入
mysql> select round(-1.55);#-2
mysql> select round(1.567,2);#1.57 四舍五入，小数点后保留两位

#ceil():向上取整，返回大于等于改参数的最小整数
mysql> select cell(1.23) #数据2

#floor()：向下取整
mysql> select floor(9.99) #输出9

#truncate() :截断
mysql> select truncate(1.697,1); #输出1.6，小数点后保留一位

#mod取余
mysql> select mod(10,3);#输出为1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

三.日期函数

#now() 当前时间（日期+时间）
mysql> select now();#获取当前系统时间，例如2020-07-18 21:32:30

#curdate() 返回当前系统日期
#curtime() 返回当前系统时间

#year(),month(), month,date(),hour(),minute(),second()
mysql> select year(now());#返回2020
mysql> select month(now());#返回7月，select monthname(now()) 可返回英文版7月

#str_to_date() 把日期格式按照指定的格式转化为字符
str_to_date('2019-03-09','%Y-%m-%d');#2019-03-09
mysql> select * from employees where hiredate = str_to_date('4-3,2019','%c-%d %Y');#查询hire_date为2019-04-03的员工信息，主要针对日期格式输入不规范，实际hire_date的时间格式为2019-04-03，但是web输入可能时字符串形式的4-3，2019，通过转换成时间格式，也可以对应查到2019-04-03

#date_format():将日期转换成字符
date_format('2019/07/01','%Y年%m月%d日')；#2019年7月1日
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

时间格式

%Y  //四位的年份 
%y //两位的年份
%m //月份（01，02，03，....）
%c //月份（1，2，3，4，....）
%d //日 （01，02，03，....）
%H //小时（24小时制）
%h //小时（12小时制）
%i //分钟（01，02，03，....）
%s //秒 （01，02，....）
1
2
3
4
5
6
7
8
9

四.其它函数

mysql> select version()
mysql> select database()#查看当前数据库
mysql> select user() #查看当前用户
1
2
3

五.流程控制函数

#（1）if():if else效果
mysql> select if(10>5,'true','false');#显示true
mysql> select if(10<5,'true','false');#显示false

#（2）case(): 
#1.switch case模式,适用于等值判断
/*case
    when ... then ...
    when ... then ...
    else ...
end*/
select salary 原始工资，department_id
case department_id
   when 30 then salary*1.1
   when 40 then salary*1.2
   when 50 then salary*1.3
   else salary*1
   end as 加权工资
   from employees；#当department_id=30时，工资*1.1倍，为40时，工资*1.2倍，为50时，工资*1.3倍，否者维持原技术不变，最终得到加权工资，显示原始工资，depart_id,加权工资三列。
#2.类似多重if模式，适用于区间判断
/*case
  when 条件 then 显示的结果
  when 条件 then 显示的结果
  ....
  else*/
select salary，
case
 when salary>15000 then 'A'
 when salary>12000 then 'B'
 when salary>10000 then 'C'
 else 'C'
 end as 薪资等级
 from employees；#显示工资salary>15000的员工工资级别为A,15000>=salary>12000的为B，小于12000>=salary>10000的为C，10000>=salary的为D
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

六.聚合函数（分组函数，统计函数）

sum，ave适合处理处理数值型，忽略null值
max，min，count适合处理任何类型，忽略null值

#sum():求和函数

#avg():平均值

#max():最大值

#min():最小值

#count():计算非空值个数
mysql> select count(salary) from employees;
mysql> select count(*) from employees;#count(*)中的*可以改成常量值或字段，效果相同，都是实现对行的计数，只是在统计时加了一行，内容全部置为常量，计算常量的数量
效率:
myisam存储引擎下，counnt(*)的效率高
innodb存储引擎下，count（*）和count（1）等效率相同

分组函数与distinct配合使用
mysql> select count（distinct salary）from employees;

配合分组函数使用的一般使用 group by，即分组查询
mysql> select max(salary),job_id from empolyees group by job_id;#查询每个工种的最高工资
#41号部门的平均工资
mysql> select  dept_id,avg(salary) from s_emp group by dept_id having dept_41;
#having相当于where条件语句，其存在弥补了where关键字不能与聚合函数联合使用的不足。having和where可以同时使用。having一般针对分组后的数据进行查询，where针对原始表进行查询
#计算平均工资比41号部门高的部门
mysql> select dept_id,avg(salary) from s_emp group by dept_id having avg(salary) > (select avg(salary) from s_emp group by dept_id having dept_id=41);
#查询那个部门的员工数大于2
mysql> select count(*),department_id from employees group by department_id having count(*)>2;
#按多个字段分组
mysql> select AVG(salary),department_id,job_id from employees group by department_id,job_id;#查询每个部门每个工种的平均工资，department_id和job_id相同的被划分为一个分组

mysql> select count(*) 个数,city from departments d,locations l where d.location_id=d.location_id group by city;
#查询每个城市的部分数

mysql> select department_num,d.manager_id,min(salary) from departments d,employees e where d.department_id=e.department_id and commission_pct is not null group by department_name,
d.manger_id; #查询有奖金的每个部门的部门的部门名和部门领导编号和部门最低工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

35、查看当前数据库使用的字符集

mysql> show variables like '%char%'; 显示当前使用的字符集
1

36、连接查询

连接查询又叫多表连接,根据有效的连接条件实现连接
连接按功能分：
内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接，全外连接
交叉连接：

等值连接：多表连接的结果为多表的交集部分
mysql> select name1,name2 from table1,tale2 where table1.name_id=table2.name_id;
#tale1和table2都有name_id字段，可以作为连接条件，时间连接查询，最终返回表1name1列，表2的name2列，并根据name_id 相等一一对应

mysql> select last_name,e.job_id,job_title from employees as e,jobs as j where e.job_id=j.job_id;
#当两张表都有job_id字段时，字段前需要加表名进行指定，另外，针对表名过长，可以给表起别名,起了别名以后，查询的字段就不能使用原来的表名去限定了。

mysql> select last_name ,department_name,commission_pct from employees e,department d where e.department_id=d.department_id and commissin_pct is not null;
#连接查询可以加筛选分组分组条件
mysql> seletc job_title,count(*) from employees e,jobs j where e.jobid=j.jobid group by job_title order by count(*) desc;
#查询每个工种的工种名和员工个数

非等值连接
1
2
3
4
5
6
7
8
9
10
11
12
13

37、查看当前锁状态

mysql> show status like 'table_lock%';
1