day02GroupBy

# sql函数



# 字符串操作函数
# 字符串拼接
select concat('aaa','bbb');
select concat ('qqq','qqqq','qqqq');

select concat('我叫',sname,'在',cname,'中考了',grade,'分')
  as string from score;
# as 起别名
select sname as name from score as sc;
select sname name from score;

# 字符串替换
select insert('这是一个字符串',1,3,'bb');

# 大小写转换
select upper('asdafdf');
select lower('ADSDKJLJ');


# 时间日期函数
# timestamp  datetime
# 获取当前日期
select curdate();
# 获取当前时间
select curtime();
#获取当前日期和时间
select now();
# 获取当前年份
select year(now());
# 计算
select year(now())-year('1997-2-8')as age;
# 获取月份
select month(now());

select week(now());
select hour(now());

select datediff(now(),'2017-3-9');
select adddate(now(),20);

# 数学函数
# 随机数
select rand();
# 向上取整
select ceil(5.1);
# 向下取整
select floor(5.1);

select timestamp ('2109-12-1');
select datetime ('2109-12-1');

update person
set  age=age+1
where name='张三';

聚合函数

# 聚合函数
# 统计所有部门的薪资总和
select sum(salary) as sum from employee;
select avg(salary) from employee;
select max(salary)from employee;
select min(salary)from employee;
select avg(workage)from employee;
select avg(age)from employee;
select avg(age)from employee where salary>10000;


# 聚合函数不和普通列名放在一起
select avg(age),count(*)from employee;
# count()统计不为null的值
select count(name)from employee;




# 求每个部门的薪资总和

select sum(salary) as sum ,depart from employee group by depart;

select  floor(avg(age) )as age,depart from employee group by depart;

select avg(salary)as salary, workage from employee group by workage;

select count(*) ,workage from employee group by workage;

select count(*) ,depart from employee where age>30 group by depart;

select count(*),depart from employee where salary>
                                    (select salary from employee where name='张三')
group by depart;


# 查询平均薪资超过7000的部门
select depart ,avg(salary) as avg from employee group by depart
having avg(salary)>7000;

select depart , count(*)from employee group by depart
having count(*)>2;

select depart ,avg(salary) as avg from employee group by depart
having count(*)>2;

select workage,avg(age) as avg from employee group by workage
having avg(salary)>5000;


# 薪资大于3000,人数大于2的部门
select depart from employee where salary>3000
group by depart
having count(*)>2;

# where控制列,having控制聚合函数


select avg(grade) ,sname from score
group by sname
having avg(grade)>70;


# 求数学成绩超过张三的学生的平均成绩

select avg(grade),sname from score
  where sname in (
                    select sname from score where cname='数学'
    and grade>
  (select grade from score where sname='张三'and cname='数学'))
group by  sname;

order by

用于对指定列队结果集进行排序
order by 语句默认升序对记录排序

模糊匹配like

%	替代一个或多个字符
_	替代单个字符
[charlist]	中括号中的任何一个字符
[^charlist]或者[!charlist]	不在括号中的任何单一字符

limit, offset

offset偏移量
limit 5,offset 5;
从第六行开始,显示10条记录

case when

将null 转为0查询

select case when level is null then 0
else level end from b_user;

union

不同表中相同字段,一起显示

union all全部输出不去重

多值操作

多值插入

insert into table values(),()

覆盖插入

replace into table values()

查询插入

insert into tablea select *from table tableb

忽略插入

insert ignore into table values()

insert主键重复则update

insert into table value(id,col1,col2)on duplicate key update col2=...;

update 用表b更新表a

update A,B set A.name=B.name where A.id=B.id;

delete 用表b删除表a

delete a from a,b where a.id=b.id and b.name='张三';

聚合函数

面向一组数据,对数据进行聚合运算后返回单一的值
select function(列) from table

函数	作用
avg()	平均值
count(distinct)	返回去重后的行数
count()	返回列的行数
max()	返回列的最大值
min()	返回列的最小值
sum()	返回列的总和
group_concat()	返回一组值的连续字符串,默认1024

调整

show global variables like '%concat%';

调整group_concat_max_len

聚合函数 行列转换

user	key	value
张三	age	18
张三	gender	male
张三	id	1
李四	age	18
李四	gender	male
李四	id	2

转换结果

user	age	gender	id
张三	18	male	1
李四	18	male	2

select user,
max(case when 'key'='age' then value end)age,
max(case when 'key'='gender' then value end )gender,
max(case when 'key'='id' then value end)id
from table
group by user;

预定义函数

预定义函数返回一对一结果
(聚合函数返回多对一)
预定义函数基本语法:

select function(列)from 表
select * from 表 where 列=function(value)

预定义函数

• 字符串处理

函数	作用
length()	返回列的字节数
char length()	返回列的字符数
trim()/rtrim()/ltrim()	去除两边空格/去除右边/左边空格
substring(str,pos,[len])	从pos位置截取str长度为len
locate(substr,str,[pos])	返回substr在str字符串中的位置
replace()
lower()/upper()	字符串转换为小写/大写

从第二个位置截取
mysql> select substring('qwert',2);
+----------------------+
| substring('qwert',2) |
+----------------------+
| wert                 |
+----------------------+
1 row in set (0.00 sec)

从倒数第二个位置截取
mysql> select substring('qwert',-2);
+-----------------------+
| substring('qwert',-2) |
+-----------------------+
| rt                    |
+-----------------------+
1 row in set (0.00 sec)
从第二个位置截取2个字符
mysql> select substring('qwert',2,2);
+------------------------+
| substring('qwert',2,2) |
+------------------------+
| we                     |
+------------------------+
1 row in set (0.00 sec)

locate

查找子串所在字符串位置
mysql> select locate('qwe','qwert');
+-----------------------+
| locate('qwe','qwert') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)
找不到则返回0
mysql> select locate('zwe','qwert');
+-----------------------+
| locate('zwe','qwert') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select locate('er','qwert');
+----------------------+
| locate('er','qwert') |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.00 sec)

规定从第几个开始匹配
mysql> select locate('qw','qwertqw');
+------------------------+
| locate('qw','qwertqw') |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> select locate('qw','qwertqw',3);
+--------------------------+
| locate('qw','qwertqw',3) |
+--------------------------+
|                        6 |
+--------------------------+
1 row in set (0.00 sec)

时间日期计算

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-12-04 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate() -interval 1 month;
+-----------------------------+
| curdate() -interval 1 month |
+-----------------------------+
| 2019-11-04                  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select now() +interval 1 week;
+------------------------+
| now() +interval 1 week |
+------------------------+
| 2019-12-11 19:25:43    |
+------------------------+
1 row in set (0.00 sec)

select rand();
随机数0-1
ceil()向上取整

触发器

是加在表上的一个特殊程序,当表上出现特定的事件(insert,update,delete)时触发该程序执行

触发器能进行数据订正,迁移表,实现特定的业务逻辑

存储过程

自定义函数

触发器对性能有损耗,应该慎用

字符集

字符集
ASCII
GBK
UTF-8 1-4字节
Latin1默认
查看字符集

show character set ;

查看字符序

show collation ;

字符集设置级别

服务器级别
配置文件设置
[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci

数据库级
create database db_name character set latin1 collate latin1_swedish_ci;
表级
列级

show global variables like '%char%'

character_set_client:客户端来源数据使用的字符集
character_set_connection连接层字符集
character_set_results:查询结果集

show  variables like '%char%'

set global character_set_server=utf8; -- 全局

修改表字符集

alter table tabname convert to character set uft8;