SQL基础（二）

2 SQL基础（二）

2.1与2.2见SQL基础（一）

2.3    DML (用来操作表格的语句)

MySQL中（关键字），不区分大小写（数据区分大小写）。一般情况下，关键字全部大写。为了方便，学习时使用小写。数据库表中每一行记录是唯一的（解决方法是定义主键）。 1  insert insert into 表名(列名1, 列名2…列名n)  values (值1,值2…值n); 注意： 值与列顺序相同，且值必须与对应的列的类型相同。 ex：insert into student(sno,  name,  age, gender, tel, score,  birthday) values(1，’jack’，25，‘男’，‘110’, 80.5，’1990-1-1’); 如果没有全部列出所有列，其他没有对应的值的列，以null插入。 ex:  insert into student(name, age,gender) values(‘jack’, 25, ‘男’);其他列对应的值为null. 如果表中所有列都要插入数据，可不用在表名后列出所有的列。以后插入数据，必须将列在表后显示出来。 ex: insert into student values(值1， 值2…值n);   2.  Delete Delete  from 表格名 where  condition；                    数据删除，可恢复，表结构不删除 Truncate  表格名;                                                      数据删除，不可恢复， 表结构不删除，属于DDL语言 Drop table 表格名；                                                  数据和表结构都删除，并且不可恢复   3. Update update  表名  set  列=值, 列=值…;                             将某列的值修改成指定值 eg: 将stu表中age列的值修改成100，gender列值修改成女 update stu set age=100, gender=’女’; update 表名 set 列=值,列=值…  where  condition(条件); eg: 将sal大于3000的工资增加500 update stu set sal=sal+500 where sal>3000; eg: 将年龄大于23的学生的工资增加300 update stu set sal=sal+300 where age > 23; eg: 将学号为1,11,21,31,41学生，年龄增长一岁，工资增长25% update stu set age=age+1, sal=sal*1.25 where sno in (1, 11, 21, 31, 41);   4. 基本运算符  1.比较运算符 =、!=/<>、>、>=、<、<= eg: 删除姓名为jack学生信息 delete from student where name=’jack’; eg: 删除性别不为女的所有学生信息 delete from student where gender != ‘女’; eg: 删除年龄大于25的所有学生信息 delete from student where age > 25;     2. and  or and 连接两个条件，表示两个条件必须同时满足，结果才满足。 or 连接两个条件，表示其中任意一个条件成立，结果就成立。 eg: 删除性别为男且年龄在25岁以上的所有学生信息 delete from student where gender=’男’ and  age > 25; eg: 删除分数在80到90之间所有学生的信息 delete from student where score >= 80 and score <= 90; eg: 删除性别为男或者分数小于60分所有学生的信息 delete from student where gender=’男’ or score < 60; eg: 删除学号为1, 2, 3的学生信息 delete from student where  sno = 1 or sno = 2 or sno = 3;     3. between  min_val  and  max_val表示一段范围 eg: 删除分数在80到90之间所有学生的信息 delete from student score between 80 and 90; delete from student where score >= 80 and score <= 90;     4. in 表示离散的值 eg: 删除学号为1， 2， 3的学生信息 delete from student where  sno = 1 or sno = 2 or sno = 3; delete from student where sno in (1, 2, 3);     5. null eg: 删除电话为null的学生信息 delete from student where tel = null; delete from student where tel is null;     6. not  表示取反 eg: 删除分数不在80到90之间所有学生的信息 delete from student where score not between 80 and 90; eg: 删除学号不为1， 2， 3的所有学生信息 delete from student where sno not in (1, 2, 3); eg: 删除电话不为null的所有学生信息 delete from student where tel is not null;   练习DML语句：   DEPT DEPTNO（部门编号）主键 DNAME（部门名称） LOC（部门地址） 01 财务部 2楼102 02 技术部 2楼103 03 销售部 2楼104 SALGRADE GRADE（工资等级1-6）enum LOSAL（起始值） HISAL（终止值） 1 2000 3000 2 3001 4000 3 4001 6000 4 6001 9000 5 9001 15000 6 15001 20000 EMP EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 00001 王明 技术员 00005 2017-03-03 5000 800 02 00002 李兰 会计 00010 2017-03-03 3000 500 01 00003 张皓 会计 00010 2017-03-03 3000 500 01 00004 秦三 会计 00010 2017-03-04 6000 900 01 00005 秦思 技术员 00012 2017-03-04 7000 900 01 00006 林思 销售总监 00009 2017-03-04 10000 900 03 1. 将所有2017-03-02后入职的技术员薪资涨1000，奖金涨1000 update emp set sal=sal+1000,comm=comm+1000 where hiredate>'2017-03-02'; 2. 返回林思的工资等级 select grade from salgrade where( select sal from emp where ENAME like '林思') between LOSAL and HISAL; 3. 将所有在部门（02，03）的员工薪资涨500 update emp set sal=sal+500 where deptno in ('02','03'); 4. 删除姓名为‘李兰’的员工 delete  from emp where ename='李兰'; 5. 删除入职日期不为‘2017-03-03’的员工 delete from emp where hiredate !='2017-03-03'; 6. 删除奖金大于500并且薪资在5000到8000的员工 delete from emp where comm>500 and sal between 5000 and 8000; 7. 删除部门编号为（02，03，04），并且薪资不高于5000的员工 delete from emp where deptno in( '02,03,04') and sal<5000;

2.4  DQL (数据查询语句)

2.4.1  DQL (数据查询语句)- 单表查询

查询 将数据从表取出，放到一个临时的表中。查询有三种方式（选择，投影，连接） Select 子句  from 子句 where 子句                     order by 排序                     having  子句                     group by 分组                     union  连接 1. From 首先确定数据检索的源---表 2. Where 确定限定条件对应的行---行 3. Select 确定需要检索的数据投影到对应的列---列   无条件单表查询 1. 查询表中所有列 Select * from emp;   2. 查询表中指定列 Select  eno,ename from emp;可以更改列的顺序   3. 算术运算符 Select 子句中支持加减乘除和函数 eg.查看员工年薪 Select ename,empno,sal*12+comm from emp; eg.员工转正后，月薪上调20%， 请查询出所有员工转正的月薪；转正日期为入职后的6个月 select ENAME '员工姓名' , SAL '实习工资' , hiredate '入职日期', hiredate+interval 6 month '转正日期', sal*1.2 '转正工资' from emp; eg.员工试用期6个月，转正后月薪上调20%，请查询所有员工工作第一年的年薪所得  （不考虑奖金部分，年薪的试用期6个月的月薪+转正后的6个月的月薪） select ENAME '员工姓名' , SAL '实习工资' , hiredate '入职日期', hiredate+interval 6 month '转正日期', sal*6+sal*1.2*6 '年薪' from emp; select ename,sal,DATE_ADD(hiredate,INTERVAL  6  MONTH) as newdate from emp;   4. null 任何值与null计算结果都为null 通过ifnull 将 null 置为0 select ename,sal*12+ifnull(comm,0) from emp;   5. 别名 Select 列 ‘列名’  from emp; Select 列 as ‘列名’  from emp; Eg.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入，需要考虑奖金部分,要求显示列标题为员工姓名，工资收入， 奖金收入，总收入 select ENAME '员工姓名' , sal*6+sal*1.2*6 '工资收入', ifnull(comm, 0) '奖金收入' , sal*6+sal*1.2*6+ifnull(comm, 0) '年薪' from emp;   6. 连接字符串 Oracle 使用||进行字符串连接    Sql server 使用+进行字符串连接    Mysql 使用cancat()函数进行字符串连接    字符串常量被称为原义字符串 select concat(ename,' 的岗位是 ',job) '员工岗位' from emp;   7. 消除重复行    select distinct job from emp;   条件单表查询 1. Where       关系运算符（<  <=  >  >=  <>  !=   = ）       可以对数值型 字符型 日期型 直接进行运算       eg. select empno,ename,deptno from emp where ename>'王';         特殊运算符（between and  in  like  is null）       between 上限 and 下限   结果集操作包含上限和下限对应的行       like 模糊查询               %零个或者多个字符                      _表示有且仅有一个字符       可以通过escape标示符实现对_ % 本身这两个字符的查找       不区分大小写       select empno,ename,deptno from emp where empno between '00002' and '00003';       select *  from emp where comm in(500,800);       select empno,ename,deptno from emp where empno in('00002','00003' );       select *  from emp where ename like '王%';       select *  from emp where hiredate like '_____03%';       select *  from emp where ename like '%/_%' escape '/';          逻辑运算符（  and  or not）       优先级 not and or   分页 Mysql   limit Limit 0,5  :          0代表起始行               5代表起始行开始连续的行的个数 limit (pagesize-1)*pizenum, pizenum   2. Order by 默认为升序 asc 降序序显式声明为desc select * from emp order by deptno desc; select ename,job from emp order by deptno asc; 多列排序 select * from emp order by deptno asc,sal desc; select ename,job from emp order by 2 asc;   mysql中用命令行复制表结构 1.只复制表结构到新表   CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2; 或   CREATE TABLE 新表 LIKE 旧表 ; 注意上面两种方式，前一种方式是不会复制时的主键类型和自增方式是不会复制过去的，而后一种方式是把旧表的所有字段类型都复制到新表。   2.复制表结构及数据到新表   CREATE TABLE 新表 SELECT * FROM 旧表   3.复制旧表的数据到新表(假设两个表结构一样)    INSERT INTO 新表 SELECT * FROM 旧表   4.复制旧表的数据到新表(假设两个表结构不一样)  INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表   练习单表查询：   emp表中所有20号部门员工工资增加10%。 查找01部门，薪水低于12000的员工编号，姓名，职位 查找没有上级的员工信息 查找奖金高于工资的员工信息 查找两个字的员工信息 查找10号部门或职位为经理的员工信息 查找2007年入职的员工信息 查询30号部门员工信息，按照薪水降序排列 查找所有员工信息，按照职位降序排列，职位相同，按照薪水降序排列 查找姓张的员工信息 查找员工编号不为1005,1009,1013的员工信息 查找入职在2003年1月1号到2010年1月1号入职的员工信息 单行函数 1. 字符函数 大小写处理 LOWER(str) UPPER(str) SELECT LOWER(ename) from emp; SELECT UPPER(ENAME)  from emp;   字符处理函数 CONCAT(str1,str2,...) SUBSTR(str,pos,len) LENGTH(str) SELECT CONCAT(ename,' 的薪水是 ',sal) 薪水 FROM emp; SELECT SUBSTR(ename,1,1) 姓氏 FROM emp SELECT SUBSTR(ename,2) 名字 FROM emp SELECT SUBSTR(ename,-1,1) 名字 FROM emp SELECT ename,LENGTH(ename) 名字存储字节长度 FROM emp SELECT ename,CHAR_LENGTH(ename) 名字字符长度 FROM emp   字符处理函数 INSTR(str,substr)     填补LPAD(str,len,padstr)       RPAD(str,len,padstr)          TRIM([remstr FROM] str) SELECT * from dept SELECT loc,INSTR(LOC,'楼') FROM dept             SELECT loc,LPAD(loc,10,'#') FROM dept;          ===>lpad函数从左边对字符串使用指定的字符进行填充。 SELECT loc,RPAD(loc,2,'#') FROM dept; SELECT loc,REPLACE(loc,'楼','栋') FROM dept   SELECT TRIM('    你好     '),TRIM('s' from 'sssdsssddss') FROM DUAL; e.g查询姓名包含大写或小写字母a的员工姓名 select ename,CHAR_LENGTH(ename) from emp where instr(upper(ename),'A')>0;   2. 数值函数 SELECT FLOOR(RAND()*100) FROM DUAL SELECT sal,CONCAT('$',ROUND(sal,2))from emp   mysql> SELECT ROUND(13.57),ROUND(13.57,1),ROUND(16.57,-1),TRuncate(83.57,-2); +--------------+----------------+-----------------+--------------------+ | ROUND(13.57) | ROUND(13.57,1) | ROUND(16.57,-1) | TRuncate(83.57,-2) | +--------------+----------------+-----------------+--------------------+ | 14           | 13.6           | 20              | 0                  | +--------------+----------------+-----------------+--------------------+ 1 row in set   3. 日期函数 SELECT NOW(), CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP()  FROM DUAL 时间相加 SELECT DATE_ADD(CURDATE(), INTERVAL 31 DAY) SELECT ADDDATE(CURDATE(), INTERVAL 31 DAY) SELECT DATE('2003-12-31 01:02:03') SELECT DATEDIFF('1997-11-30','1997-12-31') SELECT SUBDATE(CURDATE(), INTERVAL 3 DAY) SELECT INTERVAL 1 DAY + '1997-12-31' 日期格式调整 SELECT DATE_FORMAT(CURDATE(),'%Y年%M月%D日') SELECT DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) 取出日期 SELECT DATE(NOW())

2.4.2  DQL (数据查询语句)- 多表查询

1. 笛卡尔积 一个表的每一行和第二张表的每一行发生连接 Select * from emp,dept Select * from emp,dept,salgrade    //多余两个表 Select * from emp e,dept d            //别名 2. 等值连接 Select * from emp e,dept d where e.deptno=d.deptno 3. 非等值连接 Select * from emp e,dept d where e.deptno between ‘01’ and ‘09’ 4. 内连接和外连接 内连接 将满足条件的多表连接结果显示，不满足条件的不显示 Select ename,job,e.deptno,d.DEPTNO  from emp e INNER JOIN dept d where e.deptno=d.deptno 外连接 将满足条件的多表连接结果显示，不满足条件的也显示 左外连接 右外连接   mysql不支持全外连接 Select ename,job,e.deptno,d.DEPTNO  from emp e LEFT OUTER JOIN dept d on e.deptno=d.deptno Select ename,job,e.deptno,d.DEPTNO  from emp e LEFT OUTER JOIN dept d USING(DEPTNO) where  e.deptno>'01' 5. 自连接 SELECT * FROM emp d,emp f WHERE d.EMPNO=f.MGR 6. 分组函数 MIN(expr) MAX( ) SUM( )  AVG([DISTINCT] expr)  COUNT(expr) SELECT COUNT(DISTINCT(job)) from emp; 7. 分组查询group by SELECT * FROM emp GROUP BY deptno; SELECT DEPTNO,count(JOB) FROM emp GROUP BY DEPTNO; 分组函数不能放在where子句中 可以在having子句中 SELECT DEPTNO,MAX(sal) FROM emp GROUP BY DEPTNO HAVING MAX(sal)>2000 8. 分组查询的6个关键词 SELECT    FROM    WHERE    GROUP BY    HAVING   ORDER BY 6个关键词的执行顺序 FROM    WHERE    GROUP BY   HAVING   SELECT   ORDER BY   SELECT e.deptno,count(empno) from  emp e,dept d WHERE e.deptno=d.deptno GROUP BY e.deptno   SELECT deptno,count(*) from emp GROUP BY deptno HAVING count(*)>2

2.4.3  DQL (数据查询语句)- 子查询

1.子查询用小括号括起来 SELECT ename FROM emp  WHERE (SELECT SAL FROM emp WHERE ENAME='jack')<sal   2.WHERE子句中的子查询,子查询可以包括分组函数 子查询可以在 WHERE  HAVING  FROM  update的set 分组函数可以在 select  HAVING  GROUP BY SELECT ename FROM emp  WHERE  (SELECT SAL FROM emp WHERE ENAME='jack')<sal SELECT ename,empno,sal FROM emp  WHERE sal=(SELECT MIN(SAL) FROM emp ) 关系运算和单行函数不能包含返回多行结果的子查询语句 SELECT ename,empno from emp where sal=(SELECT MIN(sal) FROM emp GROUP BY deptno)   3.多行字查询运算符 IN的使用 查询每个部门工资最低的员工姓名，员工编号，员工薪资 SELECT ename,empno,sal from emp where sal in (SELECT MIN(sal) FROM emp GROUP BY deptno) ANY 的使用 只要满足结果中任意一行即可 查询大于所有部门最低工资的员工姓名，员工编号，员工薪资 SELECT MIN(sal) FROM emp GROUP BY deptno SELECT ename,empno,sal from emp where sal >ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ALL 的使用 满足结果中所有行 SELECT MIN(sal) FROM emp GROUP BY deptno SELECT ename,empno,sal from emp where sal >ALL (SELECT MIN(sal) FROM emp GROUP BY deptno)   4.相关子查询 SELECT * from emp,dept WHERE emp.deptno=dept.deptno SELECT dname,(SELECT count(empno)from emp WHERE emp.deptno=dept.deptno GROUP BY emp.deptno ) FROM dept

2.5  DCL (数据控制语句)

DCL: 数据控制语言，主要创建用户，管理用户权限 Create   Grant    revoke   drop Create user  用户名@ip地址  IDENTIFIED BY  密码 刚刚创建的用户没有任何权限 Grant  权限  on 数据库名.表名  to用户名@ip地址 Revoke  权限  on 数据库名.表名  from 用户名@ip地址 权限：all代表所有权限   *代表所有数据库或说有表 Drop user用户名@ip地址;   显示权限 show grants for admin@localhost;   CREATE USER 'admin'@'localhost' IDENTIFIED BY '123'; SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('123');   解决MySQL乱码问题 查看编码的命令： >show  variables  like ‘char%’ character_set_client    表示客户端发来数据的编码 character_set_connection  将客户端编码转换成服务器端编码 character_set_results   将服务器端查询的结构转换成客户端编码 如何不会有乱码？ 1.  客户端，client, connection,results,服务器端编码一致。（客户端编码和服务器端编码一致，设置其他三项编码保持一致）。 >set names 编码;(gbk, utf8) set names gbk相当于以下三句 >set  character_set_client=gbk; > set  character_set_connection=gbk; >set  character_set_results=gbk; 2. character_set_client:与client保持一致； character_set_connection:与数据库保持一致; character_set_results:与客户端保持一致（客户端编码与服务器端编码不一致时） >set  character_set_client=客户端的编码; > set  character_set_connection=服务器端编码; >set  character_set_results=客户端编码;

2.5  TPL(事务处理语言)

-- 修改默认引擎为INNODB，以支持事务管理 ALTER TABLE dept ENGINE=INNODB; select * FROM dept;   -- 去掉自动提交 SET autocommit=0;   -- 开始事务 BEGIN; DELETE FROM dept where deptno='45'; DELETE FROM dept where deptno='08'; DELETE FROM dept where deptno='06'; -- 事务提交，任何一句不能执行，则整个三句都不能执行 COMMIT;   BEGIN DELETE FROM dept where deptno='45'; DELETE FROM dept where deptno='08'; -- 事务提交，全部语句都能执行，则整个事务都能执行 COMMIT;   BEGIN -- 以下2句都能执行，但整个事务回退 DELETE FROM dept where deptno='66'; DELETE FROM dept where deptno='67'; ROLLBACK; SELECT * FROM dept;   -- 开始事务 BEGIN; DELETE FROM dept where deptno='01'; SAVEPOINT p1; DELETE FROM dept where deptno='02'; SELECT * FROM dept; -- 撤销p1后的sql语句 ROLLBACK to p1; SELECT * FROM dept; DELETE FROM dept where deptno='30'; -- 事务提交，任何一句不能执行，则整个三句都不能执行 COMMIT;