All sorts of function and query

MySql03

回顾

1.主键
唯一 不能为null
primary key

2. 自增
   auto_increment

3. 非空 not null

4. 注释 comment

5. `用来处理字段名和表名 ' 表示字符串

6. 事务
   关掉自动提交： set autocommit=0;
   begin commit rollback
   7.SQL分类
   DDL 数据 定义 语言
   create drop alter
   不支持事务
   DML 数据 操作 语言
   insert update delete select
   支持事务
   DQL Query 数据 查询 语言
   select
   TCL Transaction Control 事务控制语言
   begin commit rollback
   DCL 数据 控制 语言
   控制权限的分配
   8.数据类型
   1.整数 int bigint(10) zerofill
   2.浮点数 double decimal（m,d） m总长度 d小数点后面的位数
   3.字符串 char固定长度最大255 varchar 可变长度最大65535 建议255以内超过则使用text text 可变长度 最大65535
   4.日期 date time datetime null 9999-12-31 timestamp 2038-1-19 最大值 不给值默认当前时间
   5.其它类型

没有条件简单的查询语句

1. 查询表中所有的数据

   -案例：查询emp,dept,t_item表中所有的数据

   select * from emp;
   select * from dept;
   select * from t_item;

2. 查询某些列中所有的列值
   -案例：查询emp表中所有员工的姓名，上级领导的编号，职位，工资
   select ename,mgr,job,sal from emp;
   -案例：查询emp表中所有员工的编号，姓名，所属部门的编号，工资和奖金
   select empno,ename,deptno,sal,comm from emp;
   -案例：查询dept表中所有部门的名称和地址
   select dname,loc from dept;

• 列值为null ： is null
  1.案例：查询没有上级领导的员工的编号，姓名，工资

   select empno,ename,sal from emp where mgr is null;
  

  2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金

   select ename,job,sal,comm from emp
   where comm is null
  

• 列值不为null： is not null
  1.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金

   select empno,ename,job,comm from emp 
   where comm is not null;
  

  2.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号

   select ename,sal,mgr from emp
   where mgr is not null;
  

别名

select ename as '姓名',sal as '工资',mgr as '领导编号' from emp;
select ename '姓名',sal '工资',mgr '领导编号' from emp;
select ename 姓名,sal 工资,mgr 领导编号 from emp;

去重

-查询emp表中所有员工的职位

    select job from emp;
-distinct
    
    select distinct job from emp;

WHERE

= < > >= <= !=(<>)

1.案例：查询工资高于2000的所有员工的编号，姓名，职位，工资
    select empno,ename,job,sal from emp
    where sal>2000;
2.案例：查询工资小于等于1600的所有员工的编号，姓名，工资
    select empno,ename,sal from emp
    where sal<=1600;
3.案例：查询部门编号是20的所有员工的姓名，职位以及所属部门的编号
    select ename,job,deptno from emp
    where deptno=20;
4.案例：查询职位是MANAGER的所有员工的姓名，职位
    select ename,job from emp
    where job='manager';
5.案例：查询不是10号部门的所有员工的编号，姓名，以及所属部门的编号（2种方法）
    select empno,ename,deptno from emp
    where deptno!=10;
    select empno,ename,deptno from emp
    where deptno<>10;
6.案例： 查询单价等于23的商品
    select * from t_item where price=23;
7.案例：查询单价不等于8443的商品
    select title from t_item where price!=8443;
在查询语句最后添加\G 让每条数据纵向排列

AND 和 OR 运算符

and和java中的&&一样效果
or和java中的||一样效果

1.案例：查询不是10号部门的并且工资小于3000的所有员工的编号，姓名，以及所属部门的编号

    select empno,ename,deptno from emp
    where deptno!=10 and sal<3000;
2.案例：查询部门编号是30或者上级领导为7698的所有员工的姓名，职位以及所属部门的编号

    select ename,job,deptno from emp
    where deptno=30 or mgr=7698;

练习：

1. 创建表
    create table person (id int,name varchar(10),sex varchar(5),age int,sal int, des varchar(20));
insert into person values
(1, '貂蝉','女',68,5000,'曾经漂亮'),
(2,'凤姐','女',28,28000,'好吃懒惰'),
(3,'小乔','女',18,3000,'美女一枚'),
(4,'吕布','男',25,6000,'猛男一位'),
(5,'苍老师','男',22,500000,'也是猛男一位'),
(6,'大桥','女',19,1000,'美女一枚');

2.需求 是女的 并且 工资高或长的漂亮
    select * from person 
    where sex='女' and sal>20000 or des='美女一枚';

in

-如果查询某个字段的值为多个的时候 可以使用 in关键字

    select * from person 
    where age in (68,18,19);
    
    select * from person 
    where age not in (68,18,19);

between and

-在某两个数值之间 包含 and两边的数

    select * from person 
    where age between 22 and 28; 

SQL LIKE 操作符

- _ : 代表单个未知字符
    
    共有三个字第二个是老 其它未知：_老_   
- % : 代表多个未知字符
    
    a开头  a%
    b结尾  %b
    包含c  %c%
    第二个字母是d  _d%
    倒数第三个字母是e  %e__
    163邮箱   %@163.com
    任意com的邮箱  %@%.com

    select * from user 
    where name like 'a%';
1.案例：查询标题包含记事本的商品
    select title from t_item 
    where title like '%笔记本%';
2.案例：查询有赠品的DELL产品
    select sell_point from t_item
    where  sell_point like '%赠%'
    and title like '%dell%';
3.案例：查询单价低于100的记事本
    select title from t_item
    where price<100 and title like '%记事本%';
4.案例：查询价格介于50到200之间的得力商品    
    select title from t_item 
    where price between 50 and 200 and title like '%得力%';
5.案例：查询有图片的得力商品 
    select title from t_item
    where image is not null 
    and title like '%得力%';
6.案例：查询分类为238,917的产品

    select title from t_item
    where category_id in (238,917);
7.案例：查询标题中不含得力的商品
    
    select title from t_item
    where title not like '%得力%';
8.案例：查询分类不是238,917的商品   
    
    select title from t_item
    where category_id not in (238,917);
9.案例：查询价格介于50到200之外的商品
    select title from t_item
    where price not between 50 and 200;

SQL ORDER BY 子句

order by 排序  by后面添加排序规则的字段
asc ：升序
desc：降序
order by 如果有where 要写在 where 后面
select price from t_item order by price asc;    
省略asc/desc时,默认按升序排列
-多字段排序  会先按照第一个字段排序，如果第一个字段有数值相同的，则再通过第二个字段进行排序
-需求：按照分类id升序和 价格降序  
select category_id,price from t_item 
order by category_id, price desc;
1.案例：查询所有带燃字的商品,按单价升序排列 
    
    select title,price from t_item 
    where title like '%燃%'
    order by price;
2.案例：查询所有DELL商品,按单价降序排列 
    select title,price from t_item 
    where title like '%dell%'
    order by price desc;
 
3.案例：查询所有DELL商品,按分类升序单价降序排列
    select title,category_id,price
    from t_item 
    where title like '%dell%'
    order by category_id,price desc;

limit 子句

limit begin,count 分页语句
begin 从0开始 
需求：每页显示5条 查询第三页数据
limit 10,5

    select price from t_item order by price 
limit 10,5;
-- 查询所有商品,并按单价正序排列,显示其中第1页(每页5条)

    select price from t_item order by price 
    limit 0,5;
-- 查询所有商品,并按单价正序排列,显示其中第2页(每页5条)

    select price from t_item order by price 
    limit 5,5;

CONCAT()函数

mysql 的 helloWord： select 'helloword';

-concat(s1,s2,s3) 拼接字符串函数
-- 查询商品,并将标题和单价拼到一起进行展现
    select 
    concat(title,' 价格：',price,'元') newTitle
    from t_item
    where price<100;

数值计算

+ - * / 
7%2等效mod(7,2)
-- 查询商品,并在结果中显示商品的总价值

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

日期相关函数

now() 获取当前日期+时间
curdate() 获取当前日期
curtime() 获取当前时间
    
    select now(),curdate(),curtime();
date(now()) 从某个时间中获取日期
time(now()) 从某个时间中获取时间

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

extract() 从某个时间中获取 年、月、日、时、分、秒
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());

DATE_FORMAT() 函数

-格式： date_format(时间,格式)

• format

  • %Y 4位年 2018

  • %y 2位年 18

  • %m 月 05

  • %c 月 5

  • %d 日

  • %H 24小时

  • %h 12小时

  • %i 分

  • %s 秒
    now() 2018年03月18号 15点27分22秒
    select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
    -- 案例：查询商品,并显示商品上传日期(x年x月x日)

    select date_format(created_time,'%Y年%m月%d日') from t_item;

str_to_date

-把日期字符串转成date 
-格式：str_to_date(时间字符串,转换格式);
练习： 1985年12月11
select str_to_date('1985年12月11','%Y年%m月%d');
练习2：11号10月2008年
select str_to_date('11号10月2008年','%d号%m月%Y年');
案例：14.08.2008 08:00:00 转成date

select str_to_date('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');

IFNULL() 的函数

格式 age = ifnull(A,B) 如果A值为null则age=B 如果不为空 age=A

把person表中年龄为null修改为0
update person set age=ifnull(age,0);

案例：
把emp表中没有奖金的 奖金修改为0

update emp set comm=ifnull(comm,0);

聚合函数

• 对多行数据进行合并统计

  • sum(): 求和
  • avg(): 平均值
  • count(): 数量
  • max(): 最大值
  • min(): 最小值

  -- 查询dell商品的库存合计
  select sum(num) from t_item
  where title like '%dell%';
  -- 查询dell商品的平均单价
  select avg(price) from t_item
  where title like '%dell%';
  -- 查询dell商品的条目数
  select count(*) from t_item
  where title like '%dell%';
  -- 查询dell商品的最高单价
  select max(price) from t_item
  where title like '%dell%';
  -- 查询dell商品的最小库存
  select min(num) from t_item
  where title like '%dell%';

字符串的函数

• char_length(str) 获取字符串的长度
  获取所有员工名称的长度
  select ename,char_length(ename) from emp;

• instr(str,substr) 得到subStr在str里面的位置 从1开始

    select instr('abcde','d');
  

• locate(substr,str) 得到subStr在str里面的位置 从1开始

    select locate('d','abcde');
  

• insert(str,start,length,newStr) 插入字符串

  select insert('abcdefg',3,2,'m');

• lower(str) 转成小写
  select lower('Nba');

• upper(str) 转成大写
  select upper('nba');

    select upper(ename) from emp;
  

• left(str,count) 从左边截取2个字符
  select left('abcdefg',2);

• right(str,count)从右边截取2个字符
  select right('abcdefg',2);

• trim(str) 去掉字符串两头的空格
  select trim(' abc d ');

• substring(str,index)截取字符串

    select substring('abcdefg',3);
  

• substring(str,index,length);截取字符串

    select substring('abcdefg',3,2);
  

• repeat(str,count) 重复

    select repeat('ab',2);
  

• replace(str,old,new) 替换

    select replace('This is my sql','my','your');
  

• reverse() 反转
  select reverse('abcd');

数学相关函数

• floor(num) 向下取整
  select floor(3.84); 值为3
• round(num) 四舍五入
  select round(23.8);
• round(num,m)
  select round(23.869,2);
• truncate(num,m) m代表小数点后位数 不四舍五入
  select truncate(23.869,2);
• rand() 随机数 获取小于1的随机数

-获取 5-10 的随机数 
    获取0-5的随机整数+5
select floor(rand()*6)+5;

-获取8-12的随机数 
    获取0-4的随机数+8
select floor(rand()*5)+8;

课程回顾：
1.别名
2.去重 distinct
3.where
4.and or
5.in （值1，值2，值3）
6.between x and y 包含xy
7.not 不
8.like _代表当个未知字符 %代表多个未知字符
9.order by 排序 升序 asc 降序 desc
10.limit begin，size 分页
11.concat(s1,s2,s3) s1s2s3
12.数值计算 + - * / % mod(7,2)
13.日期 now() curdate() curtime() date(now())
time(now()) extract(year from now()) month,day,hour,minute,second
14.date_format(now(),'') Y y m c d H h i s

15.str_to_date('','')

16.ifnull(x,y)
17.聚合函数 sum() avg() count() max() min()
18.字符串 char_length() instr(s1,s2) locate(s1,s2) insert('abcdefg',3,2,'m') abmefg
lower() upper() left() right() trim() substring('abcdefg',2,3) repeat('a',2) replace('abde','d','m'),reverse('')
19.数学 floor() round() trancate rand()

练习

1.案例：查询没有上级领导的员工的编号，姓名，工资
2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
4.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
5.案例：查询emp表中名字以‘S’开头的所有员工的姓名
6.案例：查询emp表中名字的最后一个字符是'S'的员工的姓名
7.案例：查询倒数的第2个字符是‘E’的员工的姓名
8.案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
9.案例：查询emp表中员工的名字中包含‘A’的员工的姓名   
10.案例：查询emp表中名字不是以'K'开头的员工的所有信息
11.案例：查询emp表中名字中不包含‘A’的所有员工的信息
12.案例：做文员的员工人数（job_id 中 含有 CLERK 的）
13.案例：销售人员 job: SALESMAN 的最高薪水
14.案例：最早和最晚入职时间
15.案例：查询类别 163的商品总库存量
16.案例：查询 类别 163 的商品
17.案例：查询商品价格不大于100的商品名称列表
18.案例：查询品牌是联想,且价格在40000以上的商品名称和价格
19.案例：查询品牌是三木,或价格在50以下的商品名称和价格
20.案例：查询品牌是三木、广博、齐心的商品名称和价格
21.案例：查询品牌不是联想、戴尔的商品名称和价格
22.案例：查找品牌是联想且价格大于10000的电脑名称
23.案例：查询联想或戴尔的电脑名称列表
24.案例：查询联想、戴尔、三木的商品名称列表
25.案例：查询不是戴尔的电脑名称列表
26.案例：查询所有是记事本的商品品牌、名称和价格
27.案例：查询品牌是末尾字符是'力'的商品的品牌、名称和价格
28.案例：名称中有联想字样的商品名称
29.案例：查询卖点含有'赠'产品名称
30.案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
31.案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
32.案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。   
33.案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
34.案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
35.案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号 
36.案例：查询不是30号部门的员工的所有信息
37.案例：查询奖金不为空的员工的所有信息
38.案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
39.案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
40.案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
41.案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
42.案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
43.案例：统计emp表中员工的总数量
44.案例：统计emp表中获得奖金的员工的数量
45.案例：求出emp表中所有的工资累加之和
46.案例：求出emp表中所有的奖金累加之和
47.案例：求出emp表中员工的平均工资
48.案例：求出emp表中员工的平均奖金
49.案例：求出emp表中员工的最高工资
50.案例：求出emp表中员工编号的最大值
51.案例：查询emp表中员工的最低工资。
52.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资
57.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金
58.案例：查询员工的编号是7369，7521，    
59.案例：查询emp表中，职位是ANALYST，
60.案例：查询emp表中职位不是ANALYST,

上午内容回顾：
1.别名 select后面字段的后面 加as 或不加 然后再加别名
2.去重 distinct
3.比较运算符 = > < >= <= != <>
4.and or
5.between and
6.is not in (2,3)
7.like _ %