round(45.666,2)作用:四舍五入,保留2位小数
ceil(45.6) 作用:向上取整
floor(45.6) 作用:向下取整
rand()返回一个0到1范围内的随机数
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
if( 100 is not null , ‘a=100’,‘a=99’ )
当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull
select
case sex
when '1' then '男'
when '2' then '女'
else ' 未知' end
as sex_desc
from table_sex
# 样例1
select
case when grade > 60 and grade <70 then '及格'
case when grade >= 70 and grade <90 then '良好'
case when grade >= 90 then '优秀'
else '不及格' end
as grade_desc
from table_grade
# 样例2
select
case when name like '叶%' then '姓叶的人'
case when name like '王%' then '姓王的人'
else '既不是姓叶也不是姓王的人' end
as name_desc
from table_name
select
case 100
when 50 then 'tom'
when 100 then 'mary'
else 'tim'
end
case
when month(s_birth)<month(`current_date`()) or (month(s_birth)=month(`current_date`()) and day(s_birth)<day(`current_date`()))
then (year(`current_date`())-year(s_birth)-1)
else (year(`current_date`())-year(s_birth))
end age
current_date():获取当前格式化日期
year/quarter/month/day/hour/minute/second: 年/季度/月/日/时/分/秒
weekofyear(): 日期转周(当前的日期是一年中的第几周)
dayofyear(): 日期转天(当前的日期是一年中的第几天)
上传下载:需安装插件=》yum install -y lrzsz
上传: rz
下载 :sz xxx
查看数据库
SHOW {DATABASES | SCHEMAS}
[LIKE ‘pattern’ | WHERE expr]
show databases;
切换数据库
use 数据库名;
查看数据库下面的表
SHOW [FULL] TABLES
[{FROM | IN} db_name]
[LIKE ‘pattern’ | WHERE expr]
show tables;
show tables from sys;
创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] …
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
create database 数据库名;
创建表
数值类型:
整数:
int 整型
long 长整型
小数:
float 单精度
double 双精度
decimal 小数 =》 跟钱挂钩的
字符串:
char 字节 长度 0-255 bigdataxxxxxxx 255
varchar 字符串 长度范围
日期:
date 日期: YYYY-MM-DD
time 时间 : HH:mm:SS
datetime 年月日时分秒 : YYYY-MM-DD HH:mm:SS
timestamp 年月日时分秒【时间戳】:YYYY-MM-DD HH:mm:SS
create table user_table(
id int(3),
name varchar(10),
age int(3)
);
show tables;
表中插入数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] …)]
{VALUES | VALUE} (value_list) [, (value_list)] …
[ON DUPLICATE KEY UPDATE assignment_list]
insert into user_table (id,name,age)values (1,‘zhangsan’,10);
insert into user_info (name,age) values(‘zs01’,18),(‘ls01’,20);
查看数据
- 表示所有字段
select * from user_table;
select name,age from user_table;
查看表中描述
desc 表名;
创建表
create 建表规范:
1.表名字 一定是英文 不要写中文 可以写汉语拼音
2.建表风格
主键 使用表中第一个字段使用自增主键 本身没有任何 业务意义
3.字段的注释
非业务字段:
1.表创建表用户及时间和更新表用户及时间
2.主键
create table user_info(
id int(3) not null auto_increment,
name varchar(10) comment '用户名称',
age int(3),
create_user varchar(10),
create_time timestamp not null default current_timestamp,
update_user varchar(10),
update_time timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
更新数据
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
where 过滤
update user_info set age=‘20’; =》 对整张表
update user_info set age=‘20’ where name=‘zs’ ; 对某条数据
删除一条数据
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] …)]
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
delete from user_info where id=1;
Incorrect string value: ‘\xE5\xAD\x90\xE8\x88\xAA…’ for column 'name中文报错 mysql 5.7版本 默认建表字符集 :latin1 解决:修改字符集 、建表直接指定字符集 utf8
CREATE TABLE user_info (
id int(3) not null auto_increment,
name varchar(10) default null,
age int(3) default null,
create_user varchar(10) default null,
create_time timestamp not null default current_timestamp,
update_user varchar(10) default null,
update_time timestamp not null default current_timestamp on update current_timestamp,
primary key (id)
) engine=InnoDB default charset=utf8;
删除表
drop table user_info;
where
where 过滤条件
1. >
2.<
2. =
3. and or
4. in not in
select * from user_info where name in (‘zs’,‘ls’,‘ww’);
select * from user_info where name not in (‘zs’,‘ls’,‘ww’);
select * from emp where
(deptno=10 and job=‘MANAGER’) or (deptno=20 and job=‘SALESMAN’)
order by 排序语法
1.默认是升序 asc
2. desc 降序
select * from user_info order by age ,name desc;
按年纪的升序排名,年纪相同时按名字的降序排名
like语法 模糊查询
1.% 任意字符数量
2.占位符 _一个字符
select * from user_info where name like ‘%z%’;
select * from user_info where name like “_s%”;
合并表 列数相同 竖着拼 列数要一致
1.union 去重
2.union all 不去重
select * from a
union / union all
select * from b;
处理null
1.过滤null
is null
is not null
2.处理 null的函数:
1.coalesce()
2.ifnull
select id ,name ,coalesce(age,0) as age_alias ,create_user ,create_time ,update_user ,update_time
from user_info
select
ifnull((sal+comm),sal) as earning, hiredate
from emp
order by earning desc ,hiredate asc;
聚合函数: 指标 sum max min avg count
select
sum(age) as age_sum,
max(age) as age_max
,min(age) as age_min
,avg(age) as age_avg
,count(age) as cnt
from user_info
select
(sal+comm_alias) as earning,
hiredate
from
(
select
sal,
ifnull(comm,0) as comm_alias,
hiredate
from emp
) as a
order by earning desc ,hiredate asc;
分组语法
group by column…
select
name,
avg(age) as age_avg
max(age) as age_max,
min(age) as age_min,
count(age) as cnt
from user_info
group by name
1.select 字段 和 group by 字段 要对应 【非聚合函数字段】
维度:分了几组
分组聚合 + having
select
name,
max(age) as age_max,
avg(age) as age_avg,
count(age) as cnt
from user_info
group by name
having age_avg > 18
1.where 写在 from 后面
2.having 写在 group by 后面
子查询 查询里面 嵌套查询
select
name, id,phone,age
from
(
select
name,
avg(age) as age_avg
from user_info
group by name
) as res
where age_avg >18;
join 多表联查 内连接、左连接、右连接、全连接 横着拼 行数要保持一致
内连接 inner join 会删除与其他连接表中没有匹配的信息
select *
from a1 inner join b1
on a1.id = b1.id
左连接 left join 以左表为主 数据是全的 即使左边部分数据为null也会显示 右表来匹配 匹配上了就显示 匹配不上就是null
select *
from a1 left join b1
on a1.id =b1.id;
右连接 right join 以右表为主 数据是全的 即使右边部分数据为null也会显示 左表来匹配 匹配上了就显示 匹配不上就是null
select *
from a1 right join b1
on a1.id =b1.id;
全连接 full join mysql 不支持全连接 左表右表数据是全的,而且没有重复数据
select
*
from a1 left join b1
on a1.id =b1.id
union
select
*
from a1 right join b1
on a1.id =b1.id
查询数据条数 显示限制 limit 避免发生 滚屏效果
select * from user_info limit 3;
alter
修改数据库的字符集
alter database bigdata character set='utf8';
给表添加一列
alter table a add column job varchar(10) after address;
alter table a add column empno int(10) first;
修改列名或列的字段类型
alter table a change column empno empno varchar(10);
alter table a change column empno empno_alias varchar(10);
alter table a change column empno_alias empno int(10);
删除一列
alter table a drop empno;
清空表内容
truncate table a;
只拷贝表结构
create table a1 like a;
将查询的结果临时生成一个表
create table a2 as select ename,job from emp;
emp a 表结构是一样:emp有数据,a没有数据 把emp表数据导入a数据
insert into a(name,job) select ename,job from emp;
mysql习题
1.查询出部门编号为30的所有员工的编号和姓名
mysql> select empno,
-> ename
-> from emp
-> where deptno=30;
+-------+--------+
| empno | ename |
+-------+--------+
| 7499 | ALLEN |
| 7521 | WARD |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7844 | TURNER |
| 7900 | JAMES |
+-------+--------+
2.找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料
mysql> select *
-> from emp
-> where (deptno=10 and job='MANAGER') or (deptno=20 and job='SALESMAN');
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
3.查询所有员工详细信息,用 工资 降序排序,如果工资相同使用入职日期升序排序
mysql> select empno,
-> ename,
-> deptno,
-> mgr,
-> hiredate,
-> job,
-> coalesce((sal+comm),sal) as earning
-> from emp
-> order by earning desc,hiredate asc;
+-------+--------+--------+------+---------------------+-----------+---------+
| empno | ename | deptno | mgr | hiredate | job | earning |
+-------+--------+--------+------+---------------------+-----------+---------+
| 7839 | KING | 10 | NULL | 1981-11-17 00:00:00 | PRESIDENT | 5000.00 |
| 7902 | FORD | 20 | 7566 | 1981-12-03 00:00:00 | ANALYST | 3000.00 |
| 7788 | SCOTT | 20 | 7566 | 1982-12-09 00:00:00 | ANALYST | 3000.00 |
| 7566 | JONES | 20 | 7839 | 1981-04-02 00:00:00 | MANAGER | 2975.00 |
| 7698 | BLAKE | 30 | 7839 | 1981-05-01 00:00:00 | MANAGER | 2850.00 |
| 7654 | MARTIN | 30 | 7698 | 1981-09-28 00:00:00 | SALESMAN | 2650.00 |
| 7782 | CLARK | 10 | 7839 | 1981-06-09 00:00:00 | MANAGER | 2450.00 |
| 7499 | ALLEN | 30 | 7698 | 1981-02-20 00:00:00 | SALESMAN | 1900.00 |
| 7521 | WARD | 30 | 7698 | 1981-02-22 00:00:00 | SALESMAN | 1750.00 |
| 7844 | TURNER | 30 | 7698 | 1981-09-08 00:00:00 | SALESMAN | 1500.00 |
| 7934 | MILLER | 10 | 7782 | 1982-01-23 00:00:00 | CLERK | 1300.00 |
| 7876 | ADAMS | 20 | 7788 | 1983-01-12 00:00:00 | CLERK | 1100.00 |
| 7900 | JAMES | 30 | 7698 | 1981-12-03 00:00:00 | CLERK | 950.00 |
| 7369 | SMITH | 20 | 7902 | 1980-12-17 00:00:00 | CLERK | 800.00 |
+-------+--------+--------+------+---------------------+-----------+---------+
4.列出薪金大于1500的 各种 工作及从事此工作的 员工人数
mysql> select job,
-> count(job) as job_num
-> from emp
-> where sal>1500
-> group by job;
+-----------+---------+
| job | job_num |
+-----------+---------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
| SALESMAN | 1 |
+-----------+---------+
5.列出在销售部工作的员工的姓名,假定不知道销售部的部门编号
mysql> select ename
-> from emp
-> where deptno=(
-> select deptno
-> from dept
-> where dname='SALES'
-> );
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6.查询姓名以S开头的\以S结尾\包含S字符\第二个字母为L
select ename
from emp
where ename like 'S%'
where ename like '%S'
where ename like '%S%'
where ename like '_L%'
7.查询 每种 工作的 最高工资 、 最低工资 、 人数
mysql> select job,
-> max(earning) as earning_max,
-> min(earning) as earning_min,
-> count(job) as job_num
-> from (
-> select job,
-> coalesce((sal+comm),sal) as earning
-> from emp
-> ) as a
-> group by job;
+-----------+-------------+-------------+---------+
| job | earning_max | earning_min | job_num |
+-----------+-------------+-------------+---------+
| ANALYST | 3000.00 | 3000.00 | 2 |
| CLERK | 1300.00 | 800.00 | 4 |
| MANAGER | 2975.00 | 2450.00 | 3 |
| PRESIDENT | 5000.00 | 5000.00 | 1 |
| SALESMAN | 2650.00 | 1500.00 | 4 |
+-----------+-------------+-------------+---------+
8.列出薪金高于公司 平均薪金 的所有员工号,员工姓名,所在部门名称,上级领导,工资,工资等级
mysql> select
-> a.empno,
-> a.ename,
-> dname,
-> c.ename as mgr_name,
-> earning,
-> grade
-> from(
-> select empno,
-> ename,
-> deptno,
-> mgr,
-> coalesce((sal+comm),sal) as earning
-> from emp
-> where sal>(
-> select avg(sal) as sal_avg
-> from emp
-> )) as a left join (
-> select deptno,
-> dname
-> from dept
-> ) as b
-> on a.deptno=b.deptno
-> left join (
-> select ename,
-> empno
-> from emp
-> ) as c
-> on a.mgr=c.empno
-> left join salgrade
-> on earning between losal and hisal;
+-------+-------+------------+----------+---------+-------+
| empno | ename | dname | mgr_name | earning | grade |
+-------+-------+------------+----------+---------+-------+
| 7788 | SCOTT | RESEARCH | JONES | 3000.00 | 4 |
| 7902 | FORD | RESEARCH | JONES | 3000.00 | 4 |
| 7782 | CLARK | ACCOUNTING | KING | 2450.00 | 4 |
| 7566 | JONES | RESEARCH | KING | 2975.00 | 4 |
| 7698 | BLAKE | SALES | KING | 2850.00 | 4 |
| 7839 | KING | ACCOUNTING | NULL | 5000.00 | 5 |
+-------+-------+------------+----------+---------+-------+
9.列出薪金高于在 各自 部门工作的员工的 平均薪金 的员工姓名和薪金、部门名称
mysql> select
-> b.ename,
-> b.sal,
-> b.dname
-> from(
-> select
-> ename,
-> sal,
-> dname,
-> a.deptno
-> from(
-> select ename,
-> sal,
-> deptno
-> from emp
-> ) as a
-> left join dept
-> on a.deptno=dept.deptno
-> ) as b left join(
-> select deptno,
-> avg(sal) as sal_avg
-> from emp
-> group by deptno
-> ) as c
-> on b.deptno=c.deptno
-> where sal>sal_avg;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
| ALLEN | 1600.00 | SALES |
| BLAKE | 2850.00 | SALES |
+-------+---------+------------+