MySQL基础（三）

MySQL基础笔记系列

比较运算符 > < >= <= = !=和<>

1. 查询工资在2000以内的员工姓名和工资

select ename,sal from emp where sal<2000;

2. 查询工作job是manager的员工姓名、工资和工作

select ename,sal,job from emp where job='manager';

3. 查询员工工资小于等于1600的员工姓名，职位和工资

select ename,job,sal from emp where sal<=1600;

4. 查询不是10号部门的员工姓名，部门编号（两种写法）

select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;

5. 查询单价为23的商品信息（商品表t_item）

select * from t_item where price=23;
select * from t_item where price=23 \G;

6. 查询单价不是8443的商品标题和单价(商品表)

select title,price from t_item where price!=8443;

去重 distinct

• 把关键字distinct写在需要去重的字段前面

1. 查询员工表有哪些职位job

select distinct job from emp;

2. 查询员工表中的部门编号有哪些？

select distinct deptno from emp;

and 和 or

• 当需要多个条件同时满足时使用 and
• 当多个条件满足一个就行时使用 or

1. 查询10号部门中工资低于2000的员工信息

select * from emp where deptno=10 and sal<2000;

2. 查询30号部门或工资大于3000的员工信息

select * from emp where deptno=30 or sal>3000;

模糊查询 like

• %代表0或多个未知字符
• _代表单个未知字符

1. 查询名字中包含a的员工姓名

select ename from emp where ename like '%a%';

2. 查询第二个字符是l的员工姓名和工资

select ename,sal from emp where ename like '_l%';

3. 查询t_item表中 标题中包含记事本的商品标题

select title from t_item where title like '%记事本%';

4. 查询职位中包含an并且工资高于1500的员工姓名、工资、职位

select ename,sal,job from emp where job like '%an%' and sal>1500;

5. 查询有赠品的dell商品详情(卖点sell_point中包含“赠”，并且title包含dell)

select * from t_item where sell_point like '%赠%' and title like '%dell%' \G;

6. 查询单价低于100的笔记本信息（title包含笔记本）

select * from t_item where price<100 and title like '%笔记本%' \G;

7. 查询有图片的得力商品信息（image字段不为null）

select * from t_item where image is not null and title like '%得力%' \G;

8. 查询名字中不包含a的员工姓名和工资

select ename,sal from emp where ename not like '%a%';

between x and y 包含x和y

1. 查询工资在2000到3000之间的员工姓名和工资(要求包含2000和3000)

select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;

2. 查询单价在50到100之间的商品标题和单价

select title,price from t_item where price between 50 and 100;

3. 查询工资在1000到2000之外的员工姓名和工资

select ename,sal from emp where sal not between 1000 and 2000;

in

1. 查询员工工资为800,1300,1500的员工信息

select * from emp where sal=800 or sal=1300 or sal=1500;
select * from emp where sal in(800,1300,1500);

2. 查询商品价格为56,58,89的商品标题和单价

select title,price from t_item where price in(56,58,89);

3. 查询工资不等于3000,5000,1500的员工姓名和工资

select ename,sal from emp where sal not in(3000,5000,1500);

排序 order by 字段名

• order by 字段名 asc（升序）/desc（降序）;
• 默认升序排序

1. 查询每个员工的姓名和工资 按照工资降序排序

select ename,sal from emp order by sal desc;

2. 查询10号部门每个员工的姓名和工资 按照工资升序排序

select ename,sal from emp where deptno=10 order by sal;

3. 查询有奖金的员工姓名、工资、奖金 按照奖金降序排序

select ename,sal,comm from emp where comm>0 order by comm desc;

4. 查询单价低于100的商品标题和单价并按照单价升序排序

select title,price from t_item where price<100 order by price;

• 多字段排序 order by 字段1,字段2;
  select ename,sal,deptno from emp order by deptno,sal desc;

5. 查询价格在200以内商品名称，单价，分类id 按照分类id降序排序，如果分类id相同则按照单价升序排序

select title,price,category_id from t_item where price<200 order by category_id desc,price;

分页查询

• limit 跳过条数,请求条数

1. 查询每个员工的姓名和工资，按照工资升序排序，请求第三页的两条数据

select ename,sal from emp order by sal limit 4,2;

2. 请求员工表中工资最高的前三名员工的信息

select * from emp order by sal desc limit 0,3;

数值计算 + - * / % 7%2 = mod(7,2)

1. 查询员工姓名，工资和年终奖(年终奖=5*月工资)

select ename,sal,5*sal 年终奖 from emp;

2. 查询商品标题，商品单价，库存和总价值（单价*库存）

select title,price,num,price*num 总价值 from t_item;

3. 查询每个员工的姓名和 加薪5块钱之后的工资

select ename,sal+5 from emp;

日期相关

1. SQL语言中的HelloWorld

select 'HelloWorld';

2. 获取当前系统时间 now()

select now();
create table t_date(name varchar(10),birthday datetime);
insert into t_date values('刘德华',now());

3. 获取当前的年月日，当前的时分秒 current当前

select curdate(),curtime();

4. 从年月日时分秒中提取年月日 和 提取时分秒

select date(now()), time(now());

5. 从完整的年月日时分秒中提取时间分量

select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());

6. 日期格式化

• 格式：date_format（时间,格式）
• %Y 四位年 %y 2位年
• %m 2位月 %c 1位月
• %d 日
• %H 24小时 %h 12小时
• %i 分钟
• %s 秒

# 把now() 转成2019年5月20号 15点15分15秒
select date_format(now(),'%Y年%c月%d号 %H点%i分%s秒');

7. 把非标准时间格式转成标准时间

# 把19.04.2019 15点36分20秒 转成标准时间
 select str_to_date("19.04.2019 15点36分20秒","%d.%m.%Y %H点%i分%s秒");

ifnull

• ifnull(x,y)，对查询的列进行判定，如果x列的值为null则取y作为值返回

修改奖金为null的值为0，不为null则不变
update emp set comm = ifnull(comm,0);

聚合函数

• 对查询的多条数据进行统计：平均值、最大值、最小值、求和、计数

1. 平均值avg
2. 最大值max(字段名)
3. 最小值min(字段名)
4. 求和sum(字段名)
5. 统计数量count(字段名)

字符串相关

1. 字符串拼接 concat(s1,s2)

# admm
select concat("ab","mm");

2. 获取字符串长度 char_length(str);

select ename,char_length(ename) from emp;

3. 获取字符串出现的位置 instr(str,substr)

select instr("abcdefg","d");

4. 转大写和转小写 upper() lower()

select upper('nba'),lower('ABC');

5. 截取字符串

# 从左边截取
select left("abcdefg",2);

# 从右边截取
select right("abcdefg",2);

# 自由截取substring(str,位置,长度)
select substring("abcdefg",2,3);

6. 去空白trim()

select trim("  a b   ");

7. 重复repeat()

select repeat('ab',2);

8. 替换replace('abcdefg abc','b','m');

select replace('abcdefg abc','b','m');

9. 反转reverse()

select reverse("abc");