学习MySql第三天

MySql03

复习

1. DDL控制表结构,不支持事务
2. DML控制表数据,支持事务
   • DQL专门做查询 select
3. TCL管理事务
4. DCL管理数据库权限
5. 数据类型
   • int(4)
   • double(7,2)
   • char(固定长度) (4) 'a'
   • varchar(长度)
   • text
   • 时间类型 date time datetime timestamp

没有条件简单的查询语句

select * from emp;
select 字段名 from emp;

1. 查询表中所有的数据 案例：查询emp,dept,titem表中所有的数据 select * from emp; select * from dept; select * from titem;

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

  案例：查询没有上级领导的员工的编号，姓名，工资 select empno,ename,sal from emp where mgr is null;

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

  select ename,job,sal,comm
  from emp
  where comm is nul;
  

• 列值不为null： is not null

  案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金

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

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

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

别名

select 字段名 as 别名 from 表名;
select 字段名 别名,字段名 别名 from 表名;
select ename '员工姓名' from emp;
select e.ename '员工姓名' from emp e;

select e.ename,e.job,e.sal
from emp e;---表的别名

去重 distinct

- 查询emp中所有的职位

    select job
    from emp;

- 查询emp中所有的部门领导编号
- 查询emp中有多少个部门领导

    select distinct mgr from emp;

WHERE

• where子句用于规定选择的标准
• 如果需要有条件的从表中选取数据,可将where子句添加到select语句的后面

• where子句是决定一条查询语句最后显示多少条目数的关键

  查询上级领导编号为7698的员工所有信息 select empno,ename,job,mgr from emp where mgr=7698; (条目数===对象数量)

• 在where中我们会使用一些运算符,大致与java相同

  1. and or

     where mgr=7698 and (comm>1000 or deptno=3)

  2. like

  3. < , > , <= , >= , = , != <>
  4. is null , is not null, in , not
  5. between XX and XXX

  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;
  
  where deptno <> 10;
  

  6.案例： 查询单价等于128的商品

  select *
  from t_item
  where price=89\G;
  

  7.案例：查询单价不等于8443的商品

  select *
  from t_item
  where price != 8443;
  
  where prcie <> 8443;
  

AND 和 OR 运算符

• 在where子句中两个或者多个条件结合起来
• 结合的结果与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; 
  

SQL LIKE 操作符

• like用于在where子句中搜索列中的指定的 模式

• sql 模式 --->通配符

  • %代替一个或者多个字符

    a% %a a%a

  • _代替一个字符

    A_ B AB A

  • 小演示

    %@%.% abc@163.com %@%.cn %@163.%

    where ename like '%a_'

  1.案例：查询标题包含记事本的商品

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

  2.案例：查询有赠品的DELL产品 sell_point

  select *
  from t_item
  where title like '%DELL%' and sell_point like '%赠%';
  

  3.案例：查询单价低于100的笔记本 price

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

  4.案例：查询价格介于50到200之间的得力商品

  select *
  from t_item
  where title like '%得力%' and price>=50 and price <=200;
  

  5.案例：查询有图片的得力商品 image

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

  6.案例：查询分类为238,917的产品

  select *
  from t_item
  where category_id=238 or category_id=917;
  
  where category_id in (238,917);
  

  7.案例：查询标题中不含得力的商品

  select *
  from t_item
  where title not like '%得力%';
  

  8.案例：查询分类不是238,917的商品

  select *
  from t_item
  where category_id not in (238,917);
  

  9.案例：查询价格介于50到200之外的商品

  select *
  from t_item
  where price not between 50 and 200;
  

SQL ORDER BY 子句

• 用于根据指定列对结果集进行排序
• 默认升序 小--->大
• asc为升序,desc降序

• order by必须写在where语句之后

  1.案例：查询所有带燃字的商品,按单价升序排列

  2.案例：查询所有DELL商品,按单价降序排列

  3.案例：查询所有DELL商品,按分类升序单价降序排列

上午把3个排序案例再做一遍,然后好好理解order by 之后有多个字段,最后的效果

limit 子句--分页

• limit begin,size
• begin 本页数据的起始行,从0开始

• size 本页显示多少行

  -- 查询所有商品,并按单价正序排列,显示其中第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;
  

很多函数功能与java中相关api类似,但是,除了几种特殊的需求以外,我们开发时,尽量不要在数据库中进行函数计算

CONCAT()函数

concat(str1,str2,...)

• 如果任何一个str是null,返回值就为null

  select concat('今天周5了','下周讲完数据库')
  from dual;
  
  select 'helloword' from dual;
  
  select concat(ename,mgr) from emp;
  

  -- 查询商品,并将标题和单价,加上元,拼到一起进行展现

  select concat(title,price,'元')
  from t_item;
  

数值计算

• 运算符 + - * / % mod()
• 弱数据类型 字符串与数字进行计算 '10'-5

• 取余 % 7%2 等同于 mod(7,2)

  -- 查询商品,并在结果中显示商品的总价值

  select price '单价',num '库存',price*num 总价值
  from t_item
  order by 总价值;
  

DATE_FORMAT() 函数

• now() 返回当前的日期和时间
• curdate() 返回当前的日期
• curtime() 返回当前的时间
• date(时间日期表达式)提取这个表达式日期的部分
• time(时间日期表达式)提取这个表达式时间的部分

• extract(时间日期表达式)提取这个表达式单独的一部分(年月日时分秒)

      select now() from dual;
      select date(now());
      select time(now());
      select extract(year from now());
      select extract(month from now());
      select extract(day from now());
  

date_format()

• 以不同的格式显示时间/日期数据

• 语法 dateformat(日期表达式,'format'); dateformat(now(),'%c-%d')

• format

  • %c 月 1-12
  • %d 月的天(00-31)
  • %H 小时(00-23)
  • %h 小时(01-12)
  • %i 分钟
  • %m 月 01-12
  • %S 秒

  • %Y 年

    select date_format(now(),'%c--%m');

    • emp中hiredate, XXXX年XX月XX日 select date_format(hiredate,"%Y年%m月%d日") from emp;
    • 显示当前时间 格式为 23:12:16 select date_format(now(),"%h:%i:%s");
    • 显示当前日期和时间,格式为XXXX-XX-XX xx:xx:xx
    • 显示当前日期为 日/月/年

  -- 查询商品,并显示商品上传日期(x年x月x日) select dateformat(createdtime,"%Y年%m月%d日") from t_item;

strtodate 把字符串转换成日期格式

• 把字符串格式的时间,转换成时间格式
• 第一个参数为字符串,第二个参数为格式和第一个参数一样的fromat字符

• 如果格式不同,结果为null

  select strtodate('08/09/2008','%m/%d/%Y'); select strtodate('08/09/08','%m/%d/%y'); select strtodate('08.09.2008','%m.%d.%y'); select strtodate('08.08.2008 08:00:00','%m.%d.%Y %h:%i:%S');

IFNULL() 的函数

• 空值处理函数 comm 0 null

• ifnull(expr1,expr2) 如果expr1不是null,ifnull返回的就是expr1,如果是null,返回expr2

  -- 查询没有奖金的员工，并把没有奖金的数值变成0

  select ifnull(comm,0)
  from emp;
  

聚合函数

• 对多行数据进行合并统计
• 使用聚合函数,要注意,聚合函数一般只有一行结果,如果其他要查询的列,有多行结果,那么只会显示,其他结果被舍弃
• 原因,数据库不支持行合并

• 不要把聚合函数和普通列放到同一个dql语句中,除非普通列只有一条数据

  • sum():返回列的总数(总额)

    select sum(sal) from emp;

  • avg(): 返回数值的平均值,但是null不包含在计算中

    select sum(sal),avg(comm) from emp; select comm from emp;

  • count(): 返回指定列的总数目,null不计数

    select count(comm) from emp;

  • max():这一列中的最大值,null不计算

  • min():这一列中的最小值,null不计算

  -- 查询得力商品的库存合计

  select sum(num)
  from t_item
  where title like "%得力%";
  

  -- 查询得力商品的平均单价

  select avg(price)
  from t_item
  where title like '%得力%';
  

  -- 查询得力商品的条目数

  select count(1)
  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()--字符数

  select char_length(ename) from emp;

• instr('abcdefg','bcd') 返回第二个字符串在第一个中占的位置,从1开始,找不到返回0

  select instr('abcdefg','bcd');

• locate('abc','---abc---abc---abc') 返回第一个字符串在第二个中占的位置,从1开始,找不到返回0

  select locate('abc','---abc---abc---abc');

• insert("abcdefghajdfkafjsdak",2,5,'---') 用子串取代第一个字符串的位置,从2开始,取代5个长度

  select insert("abcdefghajdfkafjsdak",2,5,'---');

• lower()转化成小写

• upper()转换成大写

  select lower(ename) from emp;

• left("abcdef",3)返回左边3个字符

• right("abcdef",3)返回右边3个字符

  select right("abcdef",3);

• trim(" a b c ")去除的是两边空格

• substring("fdafadfadsfsad",4)从4开始截取

• substring("fdafadfadsfsad",4,6);从4开始截取,截取6个字符

  select substring("fdafadfadsfsad",4,6);
  

• repeat("abc",3) 重复3遍

• replace("hello my sql","my","your")子串替换
• reverse()-反转字符串

数学相关函数

• floor()向下取整
• round(32.25)四舍五入
• round(32.2523432,2)四舍五入,小数点后两位

• round(4332.25,-2)四舍五入

  select round(4332.25,-2);
  

• truncate(234.234,1) 保留小数点后1位,不四舍五入

  select truncate(234.294,1);

练习

1. 笔记内容都打一遍
2. 必须完成前30题

3. 后30题作为提升

   1.案例：查询没有上级领导的员工的编号，姓名，工资 2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金 3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金 4.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号 5.案例：查询emp表中名字以‘S’开头的所有员工的姓名 6.案例：查询emp表中名字的最后一个字符是'S'的员工的姓名 7.案例：查询倒数的第2个字符是‘E’的员工的姓名 8.案例：查询emp表中员工的倒数第3个字符是 9.案例：查询emp表中员工的名字中包含‘A’的员工的姓名 10.案例：查询emp表中名字不是以'K'开头的员工的所有信息 11.案例：查询emp表中名字中不包含‘A’的所有员工的信息 12.案例：做文员的员工人数（jobid 中 含有 CLERK 的） 13.案例：销售人员 job: SAXXXXX 的最高薪水 14.案例：最早和最晚入职时间 15.案例：查询没中类别的商品数量 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,