初识MySQL(二)

MySQL相关函数

字符串相关函数

数学函数

日期相关函数

1.DATE_ADD() 中的 interval 后面可以是 year minute second day 等
2.DATE_SUB() 中的 interval 后面可以是year minute second hour day等
3.DATEDIFF(date1,date2)得到的是天数,而且是date1-date2的天数,可以取负
4.(还有DATE())这四个日期函数的日期类型可以是date,daterime或者timestampTIMEDDIFF('10:11:11','06:10:10') FROM DUAL;

--返回的是1970-1-1到现在的秒数
unix_timestamp() from dual;

-- %Y-%m-%d 格式是规定好的，表示年月日
-- %Y-%m-%d %H:%i:s格式是规定好的，表示年月日
form_unixtime() 可以把一个unix_timestamp 秒数，转成指定格式的日期

在实际开发中，我们也经常使用int来保存一个unix时间戳
然后用from_unixtime()进行转换

加密函数和系统函数

SELECT USER() FROM DUAL;–可以查看登录到MySQL的有哪些用户，以及登录的IP
SELECT DATABASE() FROM DUAL;–查询当前使用数据库名称
MD5(str) 为字符串算出一个MD5 32的字符串，常用于用户密码加密
PASSWORD(str)–加密函数

流程控制函数

IF(expr1,expr2,expr3) 如果expr1为True,返回expr2 否则返回expr3
IFNULL(如果expr1不为NULL,则返回expr1 否则返回expr2)
SELECT CASE WHEN expr1 THEN expr2
WHEN expr3 THEN expr4
ELSE expr5
END;
如果expr1为TRUE，则返回exor2,如果expr3为true,返回expr4,否则expr5

查询加强

在MySQL中，日期类型可以直接比较，需注意格式

select * from emp
 where '1992-01-01' < hiredata;

查询第三个字符为O的

select * from emp
  where name like '--O%';

分页查询

基本语法
select … limit start,rows
表示从start + 1 行开始取出rows行,start 从0开始计算

总结：
如果select语句同时包含group by,having,limit,order by
那么他们的顺序是 group by,having,order by,limit

多表查询

在进行多表查询的时候，查询的条件不能少于 表的个数-1，否则会出现笛卡尔集

自连接

指同一张表当作两张表来使用

查询员工和他上级的名字

SELECT worker.ename AS '职员名', boss.ename AS '上级名'
       FROM emp worker,emp boos       --自连接 别名
       WHERE worker.mgr = boss.empno;

子查询

子查询是指 嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询是指 返回一行数据的子查询语句

多行子查询是指 返回多行数据的子查询 使用关键字 in

查询和号部门的工作相同的雇员的
名字，岗位，工资，部门号，但是不包含部门自己的雇员

1.查询号部门有哪些工作
2.把上面查询的结果当作子查询

子查询当作临时表使用

把子查询当作一张临时表

all操作符

显示工资比部门30的所有员工的工资高的员工的姓名，工资部门号

any操作符

显示工资比部门30的其中一个员工的工资高的员工的姓名，工资部门号
同上

多列子查询

是指 子查询返回多个列数据

子查询联系
例1.查询本部门中工资大于本部门平均工资的所有员工的资料
1.
select deptno , AVG(sal) AS avg_sal
       from emp
            group by deptno
2.
select * 
     from emp,(
     select deptno , AVG(sal) AS avg_sal
       from emp
            group by deptno
     ) temp
     where emp.deptno = temp.deptnp AND emp.sal > temp.avg_sal;

例2.查找每个部门工资最高的人的详细信息
1.
select MAX(sal) 
    from emp
         group by deptno
2.
select * from emp,(
        select deptno ,MAX(sal) AS max_sal 
            from emp
              group by deptno 
        ) temp
        where emp.deptno = temp.deptno AND emp.sal = temp.max_sal;

例3.查询每个部门的信息和人员数量
1.各部门的信息 (部门名，编号，地址)来自 dept表
2.各个部门的人员数量 -> 构建一个临时表
select count(*),deptno
      FROM emp
      GROUP BY deptno;

select dname,dept.deptno,loc,temp.person_num AS '人数'
       FROM dept,(
            select count(*) AS person_num,deptno
            FROM emp
            GROUP BY deptno;
       ) temp
       WHERE dept.deptno = temp.deptno;
还有一种写法 表.*表示将该表所有列都显示出来 简化sql语句
在多表查询中，当多个表的列不重复时，才可以直接写出列名
select temp.*,dname,loc
       FROM dept,(
            select count(*) AS person_num,deptno
            FROM emp
            GROUP BY deptno;
       ) temp
       WHERE dept.deptno = temp.deptno;

表复制

自我复制数据(蠕虫复制)
为了对某个sql语句进行效率测试，我们需要白亮数据时，可以使用此法为表创建海量数据

表复制
CREATE TABLE my_tab01(
      id INT,
      `name` VARCHAR(32),
      sal DOUBLE,
      job VARCHAR(32),
      deptno INT);

自我复制
1.把emp表复制到my_tab01
INSERT INTO my_table01
         (id,`name`,sal,job,deptno)
         SELECT empno,ename,sal,job,deptno FROM emp;
2.自我复制
INSERT INTO my_tab01
         SELECT * FROM my_tab01;

如何删除掉一张表重复记录
1.先创建一张表 my_tab02,
2.让my_tab02有重复记录

CREATE TABLE my_tab02 LIKE emp;  -- 这个语句 把temp表的结构(列)，复制到my_tab02
3.去重
思路
(1)创建一张临时表 my_temp,该表结构与my_tab02一样
(2)把my_temp的记录通过distinct关键字处理后复制到my_temp
(3)清除掉my_tab02的记录
(4)把my_temp表的记录复制到my_tab02
(5)drop 掉临时表

(1)CREATE TABLE my_temp LIKE my_tab02;
(2)INSERT INTO my_temp
          SELECT distinct * FROM my_tab02;
(3)DELETE FROM my_tab02;
(4)INSERT INTO my_tab02
          SELECT * from my_temp;
(5)DROP TABLE my_temp;

合并查询

合并多个select语句的结果，可以使用集合操作符号

1.union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
2.union 
会去重

MySQL表外连接

左外连接（如果左侧的表完全显示就是左外连接，左边的表与右边的表没有匹配记录，左边的表也会完全显示出来）
基本语法select..from table_name1 left join table_name2 on 条件

右外连接（如果右侧的表完全显示就是右外连接）

//使用左外连接显示所人的成绩，如果没有成绩，也要显示其信息为NULL
CREATE TABLE stu(
    name varchar(20),
    id int
    );
INSERT INTO stu
       VALUES('jack',1),('tom',2),('nono',3),('kiki',4);
CREATE TABLE exam(
     id INT,
     gread INT
);
INSERT INTO exam
       VALUES(1,56),(2,76),(11,8)
SELECT `name`,stu.id,gread
       FROM stu LEFT JOIN exam
       ON stu.id = exam.id;
例1.列出部门名称和这些部门的员工信息(名字和工作),
    同时列出那些没有员工的部门名称

SELECT deptno,ename,job
       from emp RIGHT JOIN dept
       ON emp.deptno = dept.deptno;
       

小结：在实际开发中，我们绝大多数情况下使用的是 前面学过的连接