mysql 数据库 day_04

回顾

增删改

insert into tb1(a,b,c,d) values（1，2，3，4）；

查询数据 select

1*)where 字句过滤条件    =   等于    <>  不等    >   大于    <   小于    <=  小于等于    >=  大于等于    between 小值 and 大值 范围    in  指定固定的取值    like    模糊查询 通常只查字符串    is null     not between and    not in    is not null    and    or    \ : 指定转移运算符    \_  普通下划线字符2*)order by 字句    排序语句    select ...    from ...    where ...    order by a   --按 a 字段从小到大排序    order by a,b --按 a 字段排，a字段相当按b排asc ascend  升序（默认）desc    descend 降序    order by a asc  a 升序    order by a desc b 降序    order by a desc, b asc  a 降序 b 升序3*)distinct --去除重复数据    select distinct a    from...        --查询字段a并去除重复数据    select distinct a,b    from...        --a,b组合去除重复4*)字段别名    select name n,age a,gender g,    from...

mysql 中 where 子句不能使用字段别名

mysql 函数

*）字符串函数
char_length(‘a中’) –字符数
length(‘a中’) –字节数
concat() –字符串连接，
concat_ws() –用分隔符连接字符串
instr(‘abcdefgdef’,’def’) –返回第一个子串的位置，从1开始
ibsert(‘abcdefghijkl’,2, 11, ‘—’) – 用子串取代从2位置开始的11个字符

lower('AdFfLJf') -- 变为小写upper('AdFfLJf') -- 变为大写left('AdFfLJf',3) --- 返回最左边的三个字符right('AdFfLJf',3) -- 返回最右边的三个字符lpad('abc', 8, '*') -- 左侧填充，指定长度比源字符串少，相当于leftrpad('abc', 8, '*') -- 右侧填充，指定长度比源字符串少，相当于lefttrim('  a  bc   ') -- 去除两端空格substring('abcdefghijklmn', 3) -- 从3位置开始的所有字符substring('abcdefghijklmn', 3, 6) -- 从3位置开始的6个字符repeat('abc', 3) -- 重复三遍abcREPLACE('Hello MySql','My','Your') -- 子串替换REVERSE('Hello') -- 翻转字符串SPACE(10) -- 返回10个空格

*）数字函数

floor(3.94) -- 舍掉小数format(391.536, 2) --数字格式化为字符串，###,###.###，四舍五入，第二个参数为小数位数round(673.4974) -- 四舍五入round(673.4974, 2) -- 四舍五入到小数点后两位round(673.4974, -2) -- 四舍五入到百TRUNCATE(234,31, 1) -- 舍去至小数点后1位

*）日期函数

NOW()   --返回当前的日期和时间CURDATE()   --返回当前的日期CURTIME()   --返回当前的时间DATE(时间)    --提取日期或日期/时间表达式的日期部分TIME(时间)        --提取日期或日期/时间表达式的时间部分EXTRACT(字段 From 日期)     --返回日期/时间按的单独部分DATE_ADD(日期, INTERVAL 数量 字段)    --给日期添加指定的时间间隔                    --字段的合法值同上DATE_SUB(日期, INTERVAL 数量 字段)    --从日期减去指定的时间间隔DATEDIFF(日期1, 日期2)  --返回两个日期之间的天数DATE_FORMAT(日期, 格式)     --用不同的格式显示日期/时间        格式字符：  %Y-%m-%d %H:%i:%s                %d/%m/%Y                 %Y年%m月%d日LAST_DAY(日期)    -- 返回当月最后一天 IFNULL(数据1,数据2) - 数据1是null返回数据2；不是null返回数据1

coalesce(数据1,数据2,……) - 从左向右第一个不是null的数据

查询多行函数

*）多行数据交给函数处理，产生一个计算结果    count() 计数，数量    max()       求最大    min()       求最小    avg()       求平均    sum()       求合*)不能直接与普通字段一起查询

例子:

1，销售提成 commission_pct 不是null 的员工
use hr; –进入 hr 数据库
select employee_id,first_name,salary,commission_pct,job_id from employees
where commission_pct is not null;

2,没有上司的人 mannager_id
select employee_id,first_name,salary,manager_id
from employees
where manager_id is null;

3，没有部门的人 department_id
select employee_id,first_name,salary,department_id
from employees
where department_id is null;

4，部门 50 中，薪水大于等于 5000 的员工
select employee_id,first_name,salary,department_id
from employees
where department_id=50 and salary>=5000;

5, 姓名中有 en ,和在 80 部门中的员工
select employee_id,first_name,salary,department_id
from employees
where (first_name like ‘%en%’ or last_name like ‘%en%’)
and department_id=80;

6，部门 90 中所有员工，和工种后缀时 CLERK 的员工
select employee_id,first_name,salary,department_id,job_id
from employees
where department_id=90 or job_id like ‘%CLERK’;

7,薪水从小到大排列
select employee_id,first_name,salary
from employees
order by salary;

8,按部门降序排列，相同部门中，按薪水升序排类
select employee_id,first_name,salary,department_id
from employees
order by department_id desc,salary asc;

9，薪水大于等于 10000 的员工，按姓名排序
select employee_id,first_name,salary,department_id
from employees
where salary>=10000
order by first_name,last_name;

10，查询 员工 first_name 是 Bruce 的员工
select employee_id,first_name,salary,department_id
from employees
where first_name=’Bruce’;

11，查询 薪水大于 10000，且在 100 部门的员工
select employee_id,first_name,salary,department_id
from employees
where salary>10000 and department_id=100;

12，查询 薪水小于 3000 的员工，以及 100 部门所有的员工
select employee_id,first_name,salary,department_id
from employees
where salary<3000 or department_id=100;

13，查询员工编号为 100，150，200，250 的员工
select employee_id,first_name,salary,department_id
from employees
where employee_id in(100,150,200,250);

14,工资不在 3000-10000 范围内的员工
select employee_id,first_name,salary,department_id
from employees
where salary not between 3000 and 10000;

15，工作代码job_id 不是 IT_PROG,SH_CLERK,PU_CLERK,ST_CLERK
select employee_id,first_name,salary,department_id,job_id
from employees
where job_id not in(‘IT_PROG’,’SH_CLERK’,’PU_CLERK’,’ST_CLERK’)sa;

16,查询所有员工，按部门编号升序排列，相同部门，按first_name升序排列
select employee_id,first_name,salary,department_id
from employees
order by department_id,first_name;

17，查询 50 和 80 部门的员工，按工资降序排列，工资相同按工种代码排序
select employee_id,first_name,salary,department_id,job_id
from employees
where department_id in(50,80)
order by salary desc,job_id;

18，部门编号
select department_id
from employees;

select distinct department_id  from employees;     --删除重复部门select distinct department_id  from employeeswhere department_id is not null; --把null部门去掉

19，工种代码
select distinct job_id –去除重复job_id
from employees
order by job_id;

20，主管 id
select distinct manager_id –去除重复manager_id
from employees
where manager_id is not null;

22,部门中的主管id
select distinct department_id,manager_id
from employees
where department_id is not null and manager_id is not null
order by department_id,manager_id;
23，查询姓名和年薪
select
employee_id,
concat(first_name,last_name) name, –字段 别名
salary*12 anu_sal
from employees
where salary*12>100000; –这是对的
where anu_sal>10000; –这是错的，where后面不能跟别名

24，字符串函数测试
select char_length(‘a中’);
select length(‘a中’);

use stuselect char_length(name),length(name) from kecheng;select instr('abcdefgdef','def');select instr('abcdefgdef','xxx');--找不到得到0--first_name 第三四个字符是 'en'select employee_id, first_name, salaryfrom employeeswhere instr(first_name,'en')=3;

25，电话号码中的 44 替换成 88
select employee_id,first_name,salary,
phone_number,replace(phone_number,’44’,’88’)
from employees;

26, email 中去除第一个字符，与last_name 不相同的员工
select employee_id,first_name,last_name,salary,email,
substring(email,2)
from employees
where last_name<>substring(email,2);

27, first_name,last_name 长度相同
select employee_id,first_name,last_name
from employees
where char_length(first_name)=char_length(last_name);

28，first_name,last_name 拼接显示，空格居中
select concat(lpad(first_name,20,’ ‘),’ ‘,last_name) name
from employees;

29,数字函数测试
select floor(3.94); –向下舍去小数，不能取整到十位，百位
select floor(-3.94); –向下舍去小数
select format(214343435.234,2); –数字格式化为字符串
select round(673.4974); –四舍五入
select round(673.4974,2); –四舍五入到小数点2位
select round(673.4974,-2); –四舍五入到百
select truncate(234.91,1); –舍去至小数点后1位
select truncate(234.11,1); –舍去至小数点后1位

30，涨工资 11.37%，向上取整到 10 位
select employee_id,first_name,salary,
truncate(salary*1.1137+10,-1)
from employees;

select employee_id,first_name,salary,    ceil(salary*1.1137+10)from employees;

31,测试日期函数
select now(); 当前日期时间
select curdate(); 返回当前日期
select curtime(); 返回当前时间
select date();

use stu;insert into xueshengs(name,birth) values('hanjie',now());select*from xueshengs order by id desc limit 1;select date(now()); --取日期部分select time(now()); --取时间部分select extract(year from now());    --取年select extract(month from now());   --取月select extract(day from now()); --取日select date_add(now(),interval 10 year);    --加10年select date_add(now(),interval -24 month);  --减2年select datediff(now(),'1992-9-15'); --取中间天数格式字符：%Y-%m-%d %H:%i:%sselect date_format(now(),'%Y-%m-%d %H:%i:%s');select date_format(now(),'%Y-%m-%d %H:%i:%s');select date_format(now(),'%d-%m-%Y');select date_format(now(),'%Y年%m月%d号');select last_day(now()); --月份最后一天

32，入职15年以上的员工
select employee_id,first_name,salary,hire_date
from employees
where hire_date

作业：

4.2.1，收藏夹表（tb_collect）
create table tb_collect(
id bigint(20) primary key auto_increment comment ‘收藏夹编号，自增长’,
user_id bigint(20) not null comment ‘用户编号’,
item_id bigint(20) not null comment ‘商品编号’,
item_title varchar(100) comment ‘商品标题’,
item_price bigint(20) comment ‘商品单价’,
item_image varchar(200) comment ‘商品主图’,
item_param_data varchar(200) comment ‘商品参数’,
status int(4) default 1 comment ‘收藏夹状态1正常2删除’,
created datetime comment ‘创建时间’,
updated datetime comment ‘修改时间’,
foreign key(user_id) references tb_user(id),
foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;

4.2.5，购物车（tb_cart）
create table tb_cart(
id bigint(20) primary key auto_increment comment’购物车编号’,
user_id bigint(20) not null comment’用户编号’,
item_id bigint(20) not null comment’商品编号’,
num int(10) default 1 comment’商品数量’,
item_title varchar(100) comment’商品标题’,
item_image varchar(200) comment’商品主图’,
item_price bigint(20) comment’商品价格’,
created datetime comment’创建时间’,
updated datetime comment’修改时间’,
foreign key(user_id) references tb_user(id),
foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;

4.2.8，订单物流表（tb_order_shipping）
create table tb_order_shipping(
order_id varchar(50) primary key comment ‘order_id’,
receiver_name varchar(20) comment ‘收货人全名’,
receiver_phone varchar(20) comment ‘固定电话’,
receiver_mobile varchar(30) comment ‘移动电话’,
receiver_state varchar(10) comment ‘省份’,
receiver_city varchar(10) comment ‘城市’,
receiver_district varchar(20) comment ‘区/县’,
receiver_address varchar(200) comment ‘具体街道地址’,
receiver_zip varchar(6) comment ‘邮政编码’,
cereated datetime comment ‘创建时间’,
updated datetime comment ‘更新时间’,
foreign key(order_id) references tb_order(order_id)
)engine=innodb charset=utf8;

1,查询 first_name 以 es 结尾的人
select employee_id,first_name,salary
from employees
where first_name like ‘%es’;

2,first_name 和 last_name首字母相同的人
select employee_id,first_name,last_name,salary
from employees
where substring(first_name, 1,1)=
substring(last_name, 1,1);

3,做文员的员工人数(job_id 中含有CLERK）的
select count(1)
from employees
where job_id like ‘%CLERK%’;

4,销售人员 job_id:SA_xxxx 的最高薪水
select max(salary)
from employees
where job_id=SA_REP;

5,最早和最晚入职时间
select max(hire_date),min(hire_date)
from employees;

。