数据库2

第一节 复习

一、数据库的一些内容
数据？
数据库？
数据库管理系统？oracle,mysql,…
数据库管理员，DBA？
数据库应用程序？
最终用户?

二、SQL 结构化查询语言
(1)DDL 数据定义语言 create ,alter ,drop
(2)DML数据操作语言 insert ,update,delete 针对于表中的数据进行增，删，改
(3)DQL 数据查询语言 select
(4)DCL 数据控制语言 grant ,revoke
(5)TCL 事务控制语言 rollback,commit

三、数据定义语言
创建表结构
create table 表名(
字段 1 数据类型(长度),
字段2 数据类型(长度)
);
修改表结构
a)新增列 :alter table 表名 add 字段名 数据类型(长度);
b)修改列的数据类型 :alter table 表名 modify 字段名 数据类型(长度);
c) 修改列的名称 : alter table 表名 change 原字段名 新字段名 数据类型（长度）
d)删除列 :alter table 表名 drop 字段名

删除表 ：
drop table 表名

四、DML数据操作语言
a)向表中添加数据
向表中全部列添加测试数据
insert into 表名values (值1，值2,…)

向表中的部分列添加测试数据
insert into 表名[(字段1，字段2,…)] values (值1,值2…)

b)修改表中的数据
update 表名 set 字段名1=值1,字段名2=值2 [where …]

c)删除表中的数据
delete from 表名 [where …]

如果需要删除表中的全部数据，建议使用truncate table 表名

五、MySQL支持的约束类型
(1)主键约束 primary key
(2)唯一约束 unique
(3)非空约束 not null
(4)默认约束 default
(5)自增列 auto_increment
(6)外键约束 foreign key

第二节 单表查询

一、准备数据

(1)将已有库中的表的数据进行备份
右击数据库–>转储成SQL文件，选择存储位置，执行完毕得到一个以.sql结尾的文件

(2)将备份的数据到数据库中执行
右击数据库–>运行SQL文件，选择文件存储的位置，点击开始,最后关闭,如果刷新之后，数所中没有表，则需要关闭连接，重新打开

二、学习查询所使用到的表

select * from dept;
select * from emp;

三、基本查询 【单表查询】

-- 基本查询   查询的结果为一张“虚拟”的二维表，因为在数据库根本不存在
 -- (1)查询表的所有数据【全部行和列】 select * from 表名;
  select * from dept;
  select * from emp;
 -- (2)查询表中的部分列 select 字段名1，字段名2 ,....  from 表名;
 -- 查询员工的编号，姓名，职位和入职时间
 select empno,ename,job,hiredate from emp;
 -- (3)查询部分行 【限定查询的条件】
  -- 查询薪水在3000及3000以上的员工的信息
  select * from emp where sal>=3000;  
  /**数据库中的关系运算符有  >,<,>=,<=,  = , <>, !=*/
  -- (4)  查询部分行和列
  -- 查询员工薪水在3000及3000以上的员工的编号，姓名，职位，薪水，
 select empno,ename,job,sal from emp where sal>=3000;
  -- (5)在查询的结果中使用别名  第一种使用as 关键字，第二种直接空格
 select empno as 员工编号,ename as 员工姓名,job as 员工职位,sal as 薪水
 from emp where sal>=3000;
 select empno 员工编号,ename 员工姓名,job 职位,sal 薪水 from emp where sal>=3000;
  -- (6)mysql中的大小写问题
  -- 查询姓名为smith的员工的所有信息
 select * from emp where BINARY ename='smith'; -- binary限制查询时值的大小写问题
 select * from emp where ename='SMITH';
  -- (7)去掉重复数据， 【简称，去重】查询一共有几种职位  
 select DISTINCT job from emp;
 select DISTINCT ename,job from emp; -- 重复数据指的是ename与job的组合完全相同的才叫重复数据
 -- (8)排序问题 使用order by asc(升) /desc （序）,默认为升序
 -- 查询部门编号为30的员工的编号，姓名，薪水，按照薪水降序排序
 select empno,ename,sal from emp where deptno=30 order by sal desc;
 -- 查询员工信息，按照姓名升序
 select * from emp order by ename asc;
 -- 查询所有员工的信息，要求按照入职时间由早到晚排序
select * from emp order by hiredate ;

  -- (9)查询员工的年薪
 select empno,ename,sal*12 as 年薪 from emp order by 年薪 desc;

四、模糊查询

(1)like关键字 ,像…样 ,只适用于字符型

(2)between …and 相当于 >= and <=

(3)in ,相当于 or的关系

 -- 模糊查询
-- (1)like ，通常与“通配符”一起使用  ,"%代表0个或多个字符" ，“_代表一个字符’
 -- 查询员工的姓名中含有A的员工的编号，姓名
 select empno,ename from emp where  ename like '%A%'; 
 
 -- 查询员工的姓名中第二个字母是A的员工的编号和姓名
 select empno,ename from emp where ename like '_A%';
 -- (2)范围限定的 between... and 介于两者之间
  -- 查询员工的薪水在1000到3000之间的员工的编号，姓名，薪水，按照薪水降序排序
  select empno,ename,sal from emp where sal>=1000 and sal<=3000 order by sal desc;
  -- 使用以下语句，等量替换
  select empno,ename,sal from emp where sal between 1000 and 3000 order by sal desc;
  -- 将1000与3000进行交换  条件 是 sal >= 3000 and sal<=1000
  select empno,ename,sal from emp where sal between 3000 and 1000 order by sal desc;
  -- (3)在指定的值中进行等值判断的 in 
  -- 查询员工的职位是销售，分析师，经理的员工的编号，姓名，职位，并按照职位降序
  select empno, ename ,job from emp where job ='SALESMAN' or job='ANALYST' or job='MANAGER'
  order by job desc;
  -- 等量替成in 
   select empno, ename ,job from emp where job in('SALESMAN','ANALYST','MANAGER')
   order by job desc;
   -- (4)空值的查询
  -- 查询没有经理的员工信息
  select * from emp where mgr is null;
  -- 查询除了经理之外的所有员工信息
  select * from emp where mgr is not  null;
  -- 查询职位不是销售的所有员工信息
  select * from emp where job <>'SALESMAN';
  select * from emp where job !='SALESMAN';

关于单引号的问题
除了数值型之外，都需要加单引号

 -- 查询1980-1-1到1985-12-31日之间入职的员工信息
  select * from emp where hiredate between '1980-1-1' and '1985-12-31' order by hiredate;

第三节 MySQL中的函数

一、单行函数

字符型函数
字符型函数相当于java中String类的方法

-- 字符型函数
 -- (1)字符串的拼接
  select concat('hello','world') as word ;
 -- (2)insert插入，在指定位置上使用新字母替换原有字符
 select insert('helloworld',6,5,'java'); -- 索引从1开始，从helloworld中的第6个位置开始之后的5个字符使用java替换
 
-- (3)获取字符串的长度 LENGTH()
 select length('helloworld');
 
  -- 查询员工表中姓名只有5个字母的员工的编号和姓名
 select empno,ename from emp where length(ename)=5;
 -- (4)大小写转换
  select upper('welcome to 中国');
  select lower('WELCOME TO 中国');

可以在查询时，为表起别名
e.* 指是 emp表的所有列

  -- 查询所有员工的信息加上姓名的长度
 select e.*,length(ename) from emp e;

select e.*,length(ename) from emp e;
-- (5) left和right  从左侧/右侧获取5个字符
select left('helloworld',5);
select right('helloworld',5);
-- (6)lpad和rpad 左/右进行填充，要求n 的长度不能小于字符串的总长度
select lpad('helloworld',14,'java');
select rpad('helloworld',14,'java');
-- (7)去除字符串前后的空格
 select ltrim('   hello   world   ');
 select rtrim('   hello   world   ');
 select trim('   hello   world   ');
-- (8)替换
select replace('helloworld','o','中'); -- 替换所有的o
-- (9)反转
select reverse('helloworld');
-- (10)比较字符串的大小
select strcmp('abc','banana');
select strcmp('banana','abc');
select strcmp('abc','abc');
-- (11)字符串的截取
 select substring('helloworld',6,5); -- 因为数据库索引从1开始

select 在mysql中作用有两个，一个作用是查询，select…from .
另外一个作用是打印输出 select length(‘hello’)输出hello的长度

select在oracle中的作用只有一个，那就是查询 语法，必须是 select …from
在oracle中使用函数时，也必须带上from，没有表怎么办，使用统一的“伪表dual”

-- orical中的语法是这样的
select length('helloworld') from dual;

数据值函数:相当于Java中的Math类

-- 关于数学运算的函数
 -- (1)绝对值
 select abs(10),abs(-10),abs(0);
 -- (2)向上取整，向下取整，四舍五入
select ceil(98.0001),ceil(-98.99999);
select floor(98.9999),floor(-98.0001);
select round(98.45),round(98.54);
-- (3)随机数
select rand();
-- 产生一个1000到9999的四位随机数
select floor(rand()*9000)+1000;
-- (4)m的n次方
select pow(2,3);
-- (5)圆周率
select PI();
select 3%2;  -- 余数,mysql支持，oracle不支持
select mod(3,2);
-- (6)TRUNCATE 截断小数点后的数据，留3位小数
select truncate(98.87654,3);

日期型函数

select * from emp;
 select concat(ename,'的薪水是',sal,'年薪为',sal*12) as remark  from emp ;
-- 日期型函数，相当于java中的Date类
 -- (1)获取当前系统时间
 -- yyyy-MM-dd
 select curdate();
  -- yyyy-MM-dd hh:mm:ss
 select now();
 select sysdate();
 -- hh:mm:ss
 select CURTIME();
-- dayofyear()一年中的第N天
select dayofyear(curdate());
 -- 这个月的第5天
select DAYOFMONTH(curdate());
 -- 这周的第三天
select DAYOFWEEK(curdate());
-- 一年的第多少个星 期
select WEEKOFYEAR(curdate());
-- 在当前的系统时间上加上10年
select DATE_ADD(curdate(),INTERVAL 10 year);
 -- 在当前的系统时间上加上10天
select DATE_ADD(curdate(),INTERVAL 10 day);
 -- 当前的系统时间的前10天
select DATE_ADD(curdate(),INTERVAL -10 day);
 -- 当前的系统时间的前10个月
select DATE_ADD(curdate(),INTERVAL -10 month);
  -- 当前的系统时间减去10个月，10个月之前
select DATE_SUB(CURDATE(),INTERVAL 10 month);
-- 两个时期之间的差
select DATEDIFF(CURDATE(),'1990-1-1');
-- 查询出36年前受雇的员工信息
select e.* from emp e where  DATEDIFF(CURDATE(),	hiredate)>365*36;

流程控制函数

-- 流程控制函数
 -- (1)分支结构 
 select ename, IF(length(ename)=5,'名字的长度为5','不为5') from emp;
 -- 查询员工的编号，姓名，薪水，及薪水级别并按照级别升序
 select empno,ename,sal ,if(sal>=3000,'high','low') as grade from emp order by grade;
-- 
  select empno,ename,sal+comm from emp;  --  查询的结果有问题sal+comm，如果comm为null，则结果为null
  
 --  如果comm的值为null则sal+0,否则sal+comm
  select empno,ename,sal+ifnull(comm,0) from emp; 
  -- nullif 如果两个参数值相同，则结果返回null,两个参数值不相同，返回的是第一个参数的值
  select nullif(10,20); 
   --  使用switch结构查询员工的编号，姓名，薪水，及薪水等级(high,low)，并按照薪水等级排序
  select empno,ename,sal,  
                case when sal>=3000 then 'high'
                     else 'low' 
   end
 as 薪水等级 from emp order by 薪水等级;
  --  使用switch结构查询员工的编号，姓名，薪水，及薪水等级(high,mid,low)，并按照薪水等级排序
  select empno,ename,sal, 
     case when sal>=3000 then 'high'
          when sal>=2000 then 'mid'
          else 'low'
      end
  as 薪水等级
  from emp order by sal desc;
  -- 其它函数
 select database(); -- 目前使用的数据库
 select version(); -- 目前使用的mysql的版本
 select user();  
 select password('123456'); -- 对密码进行加密

二、 多行函数

单行函数: 针对每一行都得到一个结果
多行函数 :针对于多行最终得一个结果
count ,sum,avg,min,max

-- 查询部门编号为30的员工的总人数   (常量)但是习惯上写*
 select deptno,count(*) from emp where deptno=30;
 select deptno,count(1000) from emp where deptno=30;
-- 查询一共有多少员工
 select count(*) from emp;
 -- 查询从事销售的人数
select job,count(*) from emp where job='SALESMAN';
-- sum 针对表中的某个列进行求和,只能是数值型
 -- 查询部门编号为30的总薪水
select deptno,sum(sal) from emp where deptno=30;
 --  查询所有的员工的总薪水
 select sum(sal) from emp;
 -- 查询经理的总薪水
 select job,sum(sal) from emp where job='MANAGER';
  -- avg平均值，只能用于数值型
 -- 查询员工的平均薪水
 select avg(sal) from emp;
 -- 查询部门编号为10的员工的平均薪水
 select deptno,floor(avg(sal)) from emp where deptno=10;
 -- 查询销售的平均薪水
select job,floor(avg(sal)) from emp where job='SALESMAN'; 
 
 -- 查询员工的平均入职天数
select floor(avg(DATEDIFF(CURDATE(),	hiredate))) from emp ;
 -- max和min，最大值和最小值
 -- 查询姓名最大的员工信息
  select max(ename) from emp; -- 针对于字符串
  select min(ename) from emp;
 -- 入职最晚的时间
  select max(hiredate) from emp;  -- 针对于日期型
  select min(hiredate) from emp; -- 入职最早的时间
  -- 查询员工的最高薪水和最低薪水
  select max(sal ),min(sal) from emp;
  -- 查询销售的最高薪水和最低薪水
  select max(sal ),min(sal) from emp where job='SALESMAN';
  -- 查询部门编号为30的最高薪水，最低薪水，平均薪水，及部门总人数 
  select max(sal),min(sal),avg(sal),count(*) from emp where deptno=30;

每日练习：

–【1】找出早于12年前受雇的员工
–【2】以首字母大写的方式显示所有员工的姓名.
–【3】显示不带有’R’的员工的姓名
–【4】显示正好为5个字符的员工的姓名

–【5】显示所有员工姓名的前三个字符
–【6】显示所有员工的姓名，用’a’替换所有的’A’
–【7】显示满10年的服务年限的员工的姓名和受雇日期
–【8】显示员工的详细资料，按姓名排序
–【9】显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面

–【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所月排序，
– 若月份相同则将最早年份的员工排在最前面
–【11】显示所有员工的姓名，工作，薪金，按工作的降序排列，若工作相同则按薪金排序

–【12】找出在（任何年份的）2月受聘的所有员工
–【13】对于每个员工，显示其加入公司的天数

–【14】显示姓名字段的任何位置包含‘A’的所有员工的姓名

SELECT * from emp;
-- 找出早于12年前受雇的员工
SELECT * FROM emp WHERE DATEDIFF(CURDATE(),HIREDATE)>365*12;
SELECT * FROM emp WHERE CURDATE()-hiredate>365*12
-- 【2】以首字母大写的方式显示所有员工的姓名.
SELECT concat(UPPER(left(ename,1)),LOWER(substring(ename,2,(length(ename)-1)))) 新名字
 FROM emp;
-- 【3】显示不带有'R'的员工的姓名
select ename from emp where ename not like '%R%'
-- 【4】显示正好为5个字符的员工的姓名
 select empno,ename from emp where length(ename)=5;
-- 【5】显示所有员工姓名的前三个字符
select substring(ename,1,3) FROM emp;
select left(ename,3) from emp;
-- 【6】显示所有员工的姓名，用'a'替换所有的'A'
SELECT replace(ename,'A','a') 新名字 FROM emp;
-- 【7】显示满10年的服务年限的员工的姓名和受雇日期
SELECT ename,hiredate FROM emp WHERE DATEDIFF(CURDATE(),HIREDATE)>365*10;
-- 【8】显示员工的详细资料，按姓名排序
SELECT * FROM emp ORDER BY ename;
-- 【9】显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
SELECT ename,hiredate FROM emp ORDER BY hiredate asc;
-- 【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所月排序，
-- 若月份相同则将最早年份的员工排在最前
SELECT ename,substring(HIREDATE,1,7),hiredate FROM emp 
ORDER BY substring(HIREDATE,6,7),substring(HIREDATE,3,4); 
select ename,year(hiredate) as empyear,month(hiredate) as empmonth from emp
order by empmonth,empyear ;
-- 【11】显示所有员工的姓名，工作，薪金，按工作的降序排列，若工作相同则按薪金排序
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal;
-- 【12】找出在（任何年份的）2月受聘的所有员工
SELECT ename from emp WHERE MONTH(HIREDATE)=2;
-- 【13】对于每个员工，显示其加入公司的天数
SELECT ename,TIMESTAMPDIFF(DAY,hiredate,CURDATE()) FROM emp;
SELECT ename,DATEDIFF(CURDATE(),hiredate) from emp
-- 【14】显示姓名字段的任何位置包含‘A’的所有员工的姓名
SELECT ename from emp WHERE ename LIKE '%A%';