第一部分 数据库之SQL语句

注：1.mysql是一种关系型数据库

        2.大小写不敏感

        3.字符串用单引号，若字符串里有单引号，则可以用两个单引号表示一个单引号  ‘assad’’a‘表示 assad'a

一、数据库概述

数据：数据库中存储的基本对象

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，有组织可共享的大量数据集合

数据库管理系统（DataBase Management System，DBMS）：用于数据定义、操纵、数据库运行管理，建立维护

数据库系统：数据库+数据库管理系统+应用系统+数据管理员

二、数据库的配置与测试

官网下载 mysql 和Navicat for mysql

1.启动和终止mysql

（1）计算机--》管理--》服务--》打开关闭mysql

（2）dos窗口  net  start mysql          net stop mysql

2.进入mysql（默认账号root）

在bin目录里启动dos窗口

mysql -uroot -p密码

3.进入数据库

use 数据库名

三、Mysql数据类型

1、整型

MySQL数据类型含义（有符号）

tinyint(m)1个字节  范围(-128~127)

smallint(m)2个字节  范围(-32768~32767)

int(m)4个字节  范围(-2147483648~2147483647)

bigint(m)8个字节  范围(+-9.22*10的18次方)

2、浮点型(float和double)

float(m,d)单精度浮点型    8位精度(4字节)     m总个数，d小数位

double(m,d)双精度浮点型    16位精度(8字节)    m总个数，d小数位

设一个字段定义为float(5,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。

浮点数有精度损失，比如插入了123.14可能变成123.12

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 

4、字符串

char(n)固定长度，n最大为255.char(10)不足用空字符补齐（效率高）

varchar(n)可变长度，n最大255，varchar(10)最大为10,  一定要指定长度

text可变长度，最多65535个字符，很少用，

5、日期

data  只表示日期

time  只表示时间

注：1112 mysql识别为00：11：12而11：12mysql识别为11：12：00

YEAR 表示年

datatime 表日期+时间

四、SQL语句

解释性语言，写一句执行一句，不需要整体编译执行

desc 表名 观察表结构

表dual，用来进行表达式运算

起别名  select <列名>[As]别名（别名若有特殊字符加双引号）

分类

DDL(*)（Data DefinitionLanguage）：数据定义语言，create、ALTER、 DROP

DML(**)（Data ManipulationLanguage）：数据操作语言，insert delete update

DCL（Data Control Language）：数据控制语言，grant remove

DQL(*****)（Data Query Language）：数据查询语言，select

*注意：sql语句以;结尾

操作数据库(了解)

1.创建

Create database mydb1;

Create database mydb2 character set gbk;

2.查询

查看当前数据库服务器中的所有数据库

Show databases;

查看前面创建的mydb2数据库的定义信息

Show create database mydb2;

3..修改

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database mydb2 character set utf8;

4.删除

Drop database mydb3;

5.其他

查看当前使用的数据库

Select database();

6.切换数据库

Use mydb2;

操作表（重点）

DDL数据定义语言

对表的增删改

create

语法：

create table <表名>(

<列名> 数据类型 [列级完整性约束条件],

<列名> 数据类型 [列级完整性约束条件],

...

[表级完整性约束条件(可同时约束多列)]

);

数据完整性

作用：保证用户输入的数据保存到数据库中是正确的。

完整性的分类：

1.实体完整性:

2.域完整性:

3.引用完整性:

列级约束条件

1.实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型：主键约束（primary key）唯一约束(unique)自动增长列(auto_increment)

(1)主键约束（primary key）

注：每个表中要有一个主键,且只能有一个

特点：数据唯一，且不能为null,插入数据的组合中任何一个数据都不能是null

（2）唯一约束(unique)：特点：数据不能重复。可null

（3）自动增长列(auto_increment)，一个表只能有一个自增列（只能添加到主键约束或唯一约束上）

2.域完整性

域完整性的作用：域代表当前单元格，限制此单元格的数据正确，

域完整性约束：数据类型非空约束（not null）默认值约束(default) check约束（mysql不支持）check(sex='男'orsex='女')

（1）not null 不为空  null可为空

（2）default 默认值        给变量赋默认值

3.引用完整性（参照完整性）

外键约束

列级约束直接声明后添加：deptno int REFERENCES dept(deptno)

被约束的列必须是另一个表中的具有唯一性的一列

注：1.往具有外键约束的字段中添加数据，这个数据必须是另一张表中指定字段中出现的数据

2.被约束的字段和约束的字段不一定要名字相同

3.删除表的时候先删除添加外键约束的表，再删除参照的表

实际开发中很少使用外键约束，而更多使用的是业务逻辑进行判断

表级约束条件：

（1）constraint <约束名> unique(列1，列2) 表示组合唯一

（2）primary key(classid，stuid)  联合主键，两个值决定主键值，

（3）CONSTRAINT   外键约束名  foreign key (sid) references student(sid主键));

本表的sid依赖student表中的主键sid

Drop

Drop Table <表名>  有关联表先解除关联再删表

Alter（是针对表结构的更改）

注：alter针对表结构的更改，可以通过图形化工具直接操作，作为了解

1.添加列  

Alter Table <表名> ADD <新列名><数据类型>[完整性约束条件]  

(after 字段名(指定字段后添加)/first(第一列添加))

2.删除列

ALTER TABLE <表名> DROP <列名>

3.修改列数据类型

ALTER TABLE <表名> MODIFY <列名> <数据类型>[约束条件]

4.修改表名

ALTER TABLE <旧表名> RENAME TO <新表名>

5.修改字段名

ALTER TABLE <表名>    CHANGE <旧列名> <新列名> <新数据类型>

6.增补约束

（1）主键  ALTER TABLE <表名> ADD CONSTRAINT <约束名> （约束类_表名_列名）PRIMARY KEY[UNIQUE] <(列名)>

（2）外键 ALTER TABLE <表名> ADD CONSTRAINT <约束名> FOREIGN KEY <(列名)> REFERENCES <表名><(列名)>

（3）默认值 ALTER TABLE <表名> ALTER <列名> SET DEFAULT 默认值

（4）自增列  ALTER  TABLE <表名> MODIFY  COLUMN<列名>  <数据类型>  NOT NULL  <约束（主键或者唯一性约束）>     AUTO_INCREMENT

7.删除约束

1.主键 ALTER TABLE<表名> DROP PRIMARY KEY

2.唯一值 ALTER TABLE <表名> DROP INDEX <列名>

3. 外键 ALTER TABLE <表名> DROP FOREIGN KEY 约束名

4.删除自增长  ALTER TABLE <表名> MODIFY <列名> <数据类型>

DML数据操作语言

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。

INSERT 

1.插入不存在的数据  INSERT INTO <表名> [(列1，列2，列3...)] values （' 值1','值2 ','值3 '）,（' 值1','值2 ','值3 '）..

注：插全部数据的时候可以不写列名

2.将查询的数据查到数据表

INSERT INTO <表名> [(列1，列2....)]  SELECT 查询语句

UPDATE（针对值的更改）

UPDATE <表名> SET 列名1=值表达式[,列名2=值表达式].....[WHERE 条件表达式]

DELETE

DELETE FROM <表名> [WHERE 表达式]

例：删除id小于10的数据记录

DELETE 删除的数据 FROM student WHERE ID<10

TRUNCATE删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

Truncate Table<表名> 清空表数据

DQL数据查询语言

查询返回的结果集是一张虚拟表。

SELECT

实际应用时，查询是不可用select * 并且一定要写条件，因为公司数据很多，不写条件会引起死机

SELECT [ALL|DISTINCT] <列名>,[<列名>...]

FROM <表名或视图名> [表名或视图名]

[WHERE <条件表达式>]

[GROUP BY <列名> [HAVING] <条件表达式>]]

[ORDER BY <列名>[ASC|DESC]]    [LIMIT m,n];

*---->代表所有

DISTINCT：消除查询到的重复结果（若后面跟着多个列，去除的是重复组合）

LIMIT m,n    m表示开始位置，n表示长度（获取查询结果的一部分内容），注：第一行数据为0

执行顺序：FROM   WHERE   GROUP BY   HAVING  SELECT  DISTINCT   ORDER BY  LIMIT

条件查询WHERE 

=、<=>安全等可判断两者是否都为null，!=、<>不等、<、<=、>、>=；

AND 并且  OR或    NOT  非 

BETWEEN 条件1  AND 条件2 表示两者之间     / NOT BETWEEN 条件1  AND 条件2

<列名> in （）在括号里的值取，not in

IS NULL   表示为空   IS NOT NULL 不为空

通配符LIKE（Not Like不包含）

_ 下划线表示一个字符  例： LIKE '孙_'

%表示任意长度          例： LIKE '孙%'     LIKE '%M%'出现过M

[m,n]m到n个长度

聚合函数

sum()   avg()   min()   max() count()

聚合函数可以嵌套   max(avg(sal))  查询平均值最大的一组

注：聚合函数统计的是非NULL的数据，

如果一个列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(列名,0) FROM emp;

分组

GROUD BY (根据某个条件对数据进行分组)  虽说是分组但是分组后的数据是无法直接全部打印出来的，强行打印每组只会显示每组的一个数据。（可以使用GROUP_CONCAT(字段)函数来显示分组数据）

GROUD BY可以对两个属性进行分组，这时分的组是以两个属性的组合进行分的组，组和相同为一组。

分组可以理解成为，把你group by后面的字段作为一个整体，输出一条你想得到的一种聚合函数类型的数据，所以分组和聚合函数息息相关。

分组函数的是每一组多行输入，单行输出 这里就解释了  select ename max（sal） from emp  ，因为ename可能是多个，就产生了多行输出，产生了无法匹配的问题

注：出现在查询语句里的字段，如果没出现在聚合函数中，就一定要出现在groud by中不然会出现上述问题

SELECT s_id,count(*) AS total

from fruits

group by s_id with rollup    

注：with rollup表示的是所有count(*)的和，也就是所说的总计！

HAVING（对于聚合函数的统计结果进行筛选）

having与where的区别:

1.having是在分组后对数据进行过滤.

where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数)

where后面不可以使用分组函数。

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

排序

ORDER BY <列1>[ASC|DESC],[<列1>[ASC|DESC]]   先排列1，列1相同再排列2

DESC倒序，ASC正序(默认)

表与表之间的关系

一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；

给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

多表查询（重点）

多表查询有如下几种：

1.合并结果集；UNION、UNION ALL

2.连接查询

内连接[INNER] JOIN ON

外连接OUTER JOIN ON

左外连接LEFT [OUTER] JOIN

右外连接RIGHT [OUTER] JOIN

自然连接NATURAL JOIN

3.子查询

合并结果集

1.合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：

UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；

UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

要求：被合并的两个结果：列数、列类型必须相同。

2.连接查询（重点）

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

        连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

        那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

         你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

注：在多表查询中，在使用列时必须指定列所从属的表，例如emp.deptno表示emp表的deptno列。

（1）内连接

分为隐式和显示，显示内连接：将表间的逗号用Inner join表示，where用on表示（一个join跟一个on）

SELECT *

FROM emp e Inner JOIN dept d

ON e.deptno=d.deptno;

特点：查询结果必须满足条件，两个表中都有的数据才会显示出来

自连接：把同一张表当做多张表对待(特殊内连接)

求7369员工编号、姓名、经理编号和经理姓名

同一张表无法控制不同行的mgr和empno相同，如果只查一张表另mgr和empno相等比的是同一行。

所以如果比较条件是同一张表内不同行的数据使用自连接，将一张表拷贝一份

SELECTe1.empno , e1.ename,e2.mgr,e2.ename

FROM emp e1, emp e2

WHERE e1.mgr = e2.empno ANDe1.empno = 7369;

注意：内连接一定要给表起别名，让数据库知道你在调用表名时到底调用的是两张相同表的哪一张

（2）外连接（基础表中的数据必须都出现，另一张没有的用null填充）

左外连接：左边基础表     右外连接：右边基础表

       还用上面的例子其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

SELECT <列名>

FROM TABLE1 LEFT | RIGHT JOIN TABLE2

ON TABLE1.列=TABLE2.列

连接后的表是两个表笛卡尔积的结果，注意使用主外键关系去除不需要的记录

（3）自然连接

自动找到主外键关系（自己找到连接条件）

SELECT * FROM emp NATURAL JOIN dept;    //内连接

SELECT * FROM emp NATURAL LEFT JOIN dept;    //左链接

SELECT * FROM emp NATURAL RIGHT JOIN dept;    //右链接

（4）子查询（重点）

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

求每个组里最大工资的员工

        出现的问题：当薪水只要满足是分组里三组中任意一组工资最大值就可以选出来，也就是说第一组的某个人的薪资可能是第二组的最大值，这时也会被选了出来

解决办法：这是应该将子查询结果根据部门编号和最大工资建一张新表，然后进行连接查询

子查询常出现的位置：

where后，作为被查询的一条件的一部分；

from后，作表；

 单行单列（用于条件）

 单行多列（用于条件）

 多行单列（用于条件）

 多行多列（用于表，分组）

>all表示大于所有    >any表示大于任意一个，只要大于一个就行（子查询位于where后）

exists和in 的区别，

exists是先执行主查询然后看主查询的条件是否在子查询中存在

in是先执行子查询，然后看主查询的条件是否在子查询中

注：1.子查询结果集少，主查询结果集多，用in

      2.子查询结果集多，主查询结果集少，用exists

     3 .in一般表示的是某一字段的时在子查询中就进行主查询，而exist表示的是只要子查询查询到了数据就进行主查询

例题：

1.    查询工资最高的人名和薪水：子查询查出最高工资，主查询查出有最高薪水的人名和工资

（1）SELECT NAME,SAL    FROM EMP  WHERE SAL=(SELECT MAX(SAL) FROM EMP)

若不使用子查询：SELETE NAME,MAX(SAL)  这样是不对的，最大数据只有一个，所以这个查询只能查询一个对应的名字，而实际上最大薪水对应的不是一个人

（2）SELECT NAME,SAL    FROM EMP  WHERE SAL>(SELECT MAX(SAL) FROM EMP)

（3）SELECT NAME,SAL    FROM EMP  WHERE SAL in(SELECT MAX(SAL) FROM EMP)

表示sal是子查询查询出来众多值的一个

2.工资高于JONES的员工。

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')

3.查询工作和工资与MARTIN（马丁）完全相同的员工信息(重点！！！)

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')

3.有2个以上直接下属的员工信息(当子查询的结果查询的是分组后的结果返回的是多列不可用等号用in)

SELECT * FROM emp WHERE empno IN(

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

4.不用组函数，求薪水最大值（面试题）

思路：使用自连接，查询1表中工资中小于2表中工资的数组组成新表，这是从原表中查询工资不再新表里的数据就是最高工资

或者表1的某个工资值大于等于表2中所有的工资

5.查询字段为null的信息

SELECT * FROM EMP WHERE loc

将查询结果插入到表中

INSERT INTO 表名 (column_list1)

SELECT (column_list2) FROM table_name2 where (condition)

column_list1表示要插入哪些列

column_list2的字段数必须和column_list1相同