21.数据库复习笔记

数据库相关基本概念：

图形化用户界面 Navicat

1.数据

2.数据库

3.数据库管理系统（Data base Management System，DBMS）

位于操作系统与用户之间的一层数据管理软件，数据库系统的核心。
DBMS功能包括：
数据定义，DLL
数据操纵，DML
数据库的建立和维护，
数据库运行管理。

4.数据库系统（Database System,DBS）

指引入数据库技术的计算机系统。

概念模型

最典型：实体-联系（Entity-Relationship）简称E-R图。
实体集之间联系：
一对一 1：1
一对多 1：N
多对多 N: N
图形含义：
实体：矩形表示，矩形框内写明实体名称。
属性：椭圆表示，用无向边连接实体。
联系：菱形表示，框内写明联系名称。

数据模型分为：

概念模型，逻辑模型，物理模型。

关系数据库

表（table）：
称为关系，由表名，构成表的各个列及若干数据构成。
关键字（Key）：
也称主键，唯一确定一个元组的属性或属性组。

二. MySql 编程语言

结构化查询语言SQL（Structured Query Language）

组成部分：

1.数据定义语言（DDL）

对于数据库及数据库中的各种对象进行创建，增删改等。
包含的主要SQL语句由：
CREATE：创建数据库或数据库对象
ALTER：对其修改
DROP：对其删除

2.数据操纵语言（DML）

特别是检索或修改数据。由：
SELECT：从表或视图中检索数据
INSERT：将数据插入到表中
UPDATE：修改表或视图中数据，既可以改一行数据，也可以改多行或全部。
DELETE：从表中删除数据，可有条件。

3.数据控制语言（DCL）

安全管理
GRANT：授予权限，语句许可或者对象许可授予其他用户和角色。
REVOKE：回收权限。

Mysql函数

聚合函数COUNT（）

实现根据一组数据求出一个值。

日期和时间函数。

CURDATE（）
CURRENT_DATE()
用于获取当前日期。
CURTIME（）
CURRENT_TIME()
用于获取当前时间
NOW（）获取当前日期和时间。

三.数据定义

创建数据库：

mysql>CREATE DATABASE （名称） default character set utf8； //；结束别忘记加。
Query OK, 1 row affected //表示数据库创建成功
1007 - Can't create database 'test'; database exists //重复

选择数据库：

USE （数据库名称）；

查看数据库：

SHOW database （数据库名称）；

删除数据库：

DROP database （数据库名称）；

数据类型中

CHAR [ ] 不可变，
VARCHAR [ ] 可变。

表的创建：

CREATE TABLE `（表名称）`（
`列名` 类型，
`列名` 类型，
`列名` 类型，
）；

CREATE TABLE `student`(
 `Id` int PRIMARY KEY, //普通大小整数；主键约束（primary key）默认为非空
 `Nmae` CHAR(20) not NULL COMMENT'学生姓名',//不可变字符数据类型；不为空；注释为学生姓名
 `Age` int not NULL, //普通大小整数；不为空
 `Sex` enum('男','女'),//枚举类型 单项 enum（'f','m'）
 `Phone` CHAR(20) not null,//字符数据；不空
 `Data` datetime not null,//日期型 ；不空
 `Pic` BINARY,    //
 `Intre` text,    //文章类型，查询不区大小写
 `File` VARCHAR(20) //长度不固定可变长的字符数据类型，用于保存以文本格式存储的信息。
 );

查看表

查看表的名称：

mysql> use wulianwang;
mysql>show tables；

查看表的基本结构：

mysql> desc student;

查看详细结构：

mysql> show create table student;

四.数据查询

SELECT语句

在需要时从数据库中快捷方便地检索，统计或输出数据。
执行过程：将数据从表中取出，放到一个临时表。

Select 子句//确定需要检索的数据投影到对应的列--列  from 子句 //首先确定数据检索的源--表
where 子句 //确定限定条件对应的行--行
order by 排序
having 子句
group by 分组
union 连接

无条件单表查询

1.查询表中所有列

Select *from emp；
Select ename，eno from emp；

2.查询指定列

Select eno，ename from emp；//可以更改的顺序,仅选取eno，ename两个字段的值

3.算术运算符

Select 子句中支持加减乘除和函数。

eg：查看员工年薪

Select ename，empno，sal*12+comm from emp；

eg：员工转正，月薪上调20%，请查询出所有员工转正的月薪；转正日期为入职后的6个月。

Select enmae'员工姓名'，sal'实习工资'，hiredata'入职日期'，hiredate+interval 6 month'转正日期'，sal*1.2 '转正工资' from emp;

eg.员工试用期6个月，转正后月薪上调20%，请查询所有员工工作第一年的年薪所得
（不考虑奖金部分，年薪的试用期6个月的月薪+转正后的6个月的月薪）

select ename,sal,DATA_ADD(hiredate,INTERVAL 6 MONTH)as newdate from emp; //as定义字段别名

4.null

任何值与null计算结果为null
通过ifnull 将 null 置为0

5.别名

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 使用concat()函数进行字符串连接
字符串常量被称为原义字符串

select concat(ename,' 的岗位是 ',job) '员工岗位' from emp;

7.消除重复行

select distinct job from emp；

条件单表查询

1.Where

关系运算符（< <= > >= <> != = ）
可以对数值型 字符型 日期型 直接进行运算

select empno,ename,deptno from emp where ename>'王';//from先确定表，where再确定限定条件对应的行。

特殊运算符（between and in like is null）
between 上限 and 下限 结果集操作包含上限和下限对应的行
like 模糊查询 %零个或者多个字符 _表示有且仅有一个字符

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 '/';

可以通过escape标示符实现对_ % 本身这两个字符的查找

select * from emp where ename like '%A/_B%' ESCAPE '/' ;
select * from emp where ename like '%A\_B%' ESCAPE '\\' ;

如果我就真的要查%或者_，怎么办呢？使用escape，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用
默认的模糊查询不区分大小写，如果区分大小写可以在前面加上binary关键词

select  *  from  emp  where  ename  binary  like  'A%';

逻辑运算符（ and or not）
优先级 not and or

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 limit
Limit 0,5 0代表起始行 5代表起始行开始连续的行的个数

limit (pagesize-1)*pizenum,pizenum

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 旧表

标准写法

CREATE TABLE deptno LIKE dept;            复制表结构
INSERT INTO deptno SELECT * FROM dept;    复制行数据

3日期函数

时间相加

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 WEEK + '1997-12-31'

日期格式调整

SELECT DATE_FORMAT(NOW(),'%Y年%M月%D日')
SELECT DAYOFMONTH(NOW()),DAYOFWEEK(CURDATE())

取出日期

SELECT DATE(NOW())
SELECT MONTH(NOW())

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

外连接 将满足条件的多表连接结果显示，全部空的空行NULL也显示
左外连接 左边那张表全部列为空的空行会显示
右外连接 右边那张表全部为空的空行会显示
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 right 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 
分组查询的6个关键词
SELECT    FROM    WHERE    GROUP BY    HAVING   ORDER BY

8.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

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)
SELECT ename,hiredate FROM emp 
WHERE (hiredate,sal) in (SELECT  hiredate,sal from emp WHERE deptno='01' ) and deptno!='01';

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

例题：

1．将所有2017-03-02后入职的技术员薪资涨1000，奖金涨1000
update ep set sal=sal+1000,comm=comm+1000 where hiredate> ‘20170302’;
2． 返回林思的工资等级select grade from salgrade where( select sal from emp where ENAME like ‘林思’) between LOSAL and HISAL;
3． 将所有在部门（02，03）的员工薪资涨500update ep set sal=sal+500 where deptno in (‘02’,‘03’);
4． 删除姓名为‘李兰’的员工
5． 删除入职日期不为‘2017-03-04’的员工
6． 删除奖金大于500并且薪资在5000到8000的员工
7． 删除部门编号为（02，03，04），并且薪资不高于5000的员工

update emp set sal=sal+1000,comm=comm+1000 where hiredate>'2017-03-02';
select grade from salgrade where( select sal from emp where ENAME like '林思') between LOSAL and HISAL;
update emp set sal=sal+500 where deptno in ('02','03');
delete  from emp where ename='李兰';
delete from emp where hiredate !='2017-03-03';
delete from emp where comm>500 and sal between 5000 and 8000;
delete from emp where deptno in( '02,03,04') and sal<5000;