MySQL 数据库

一、数据库简介

1.1 简介

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。简单理解数据的仓库。
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
数据库是通过数据库管理系统创建和操作的。

1.2 常见数据库管理系统

Oracle：Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
DB2：DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司的需要，并可灵活地服务于中小型电子商务解决方案。
Microsoft SQL Server：SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。
SQLLite:应用在手机端的数据库。

1.3 客户端工具

2.7.1 Navicat的安装

2.7.2 Navicat的使用

使用自动完成代码，默认选中第1个使用 TAB键

https://blog.csdn.net/lss0217/article/details/102625628

二、SQL语言

2.1 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建数据库、表等等，查询内容，更新内容，并删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。
MySQL注释：（1）#开头（2）--空格开头（3）/* 多行注释 */

2.2 SQL语句分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）。
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。

2.3 DDL

2.3.1 操作数据库

2.3.1.1 系统默认数据库

information_schema：information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。别动！
mysql:mysql：这个是mysql的核心数据库,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息，不可以删除。
performance_schema：性能优化的数据库
test:这个是安装时候创建的一个测试数据库，和它的名字一样，是一个完全的空数据库，没有任何表，可以删除。

2.3.1.2 显示所有数据库

show databases; # 显示当前mysql中的数据库

2.3.1.3 创建数据库

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2.3.1.4 查看创建的test数据库的定义信息

ShOW CREATE DATABASE test;

2.3.1.5 修改字符集修改为utf8;

ALTER DATABASE mydb2 character SET utf8;

2.3.1.6 删除数据库

DROP DATABASE IF EXISTS test;

2.3.1.7 当前使用的数据库

Select database(); #没有选择数据 null

2.3.1.8 切换数据库

USE test;

2.3.1.9查看数据库编码的具体信息

Show variables like ‘character%’;

2.3.2 操作表

2.3.2.1 创建表。

CREATE TABLE IF NOT EXISTS student
(
   id INT NOT NULL,
   name VARCHAR(50),
   age INT,
   address VARCHAR(100)     # 最后一个字段无序写逗号，否则报错
) CHARSET=utf8;
或
CREATE TABLE  IF NOT EXISTS `student`
(
   `id` INT NOT NULL,
   `name` VARCHAR(50),
   `age` INT , 
   `address` VARCHAR(100)   # 最后一个字段无序写逗号，否则报错
) CHARSET=utf8; # 不能写成 utf-8

注意：数据库名、表名、字段名可以使用反勾号` 括住，也可以不括。如果SQL关键字一般要括住。

2.3.2.2 删除表。

DROP TABLE student;

2.3.2.2 当前数据库中的所有表

SHOW TABLES;

2.3.2.3 查看表的字段信息

DESC student;

2.3.2.4 添加列。

ALTER TABLE student ADD image blob;

2.3.2.5 修改列

ALTER TABLE student MODIFY address varchar(60);

2.3.2.6 删除列

ALTER TABLE student DROP image;

2.3.2.7 修改表名

RENAME TABLE student TO user;

2.3.2.8 查看表的创建细节

SHOW CREATE TABLE user;

2.3.2.9 修改表的字符集

ALTER TABLE user CHARACTER SET gbk;

2.3.2.10 修改列名

ALTER TABLE user CHANGE name username varchar(100);

2.4 DML

2.4.1 INSERT

语法： INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);

列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
参数不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用单引号括起来。

2.4.1.1 一次添加一条数据

INSERT INTO student(id,name,age,address) values (1,'zhangsan',20,'北京海淀');
INSERT INTO student(id,name,age,address) values (2,'lisi',22,'上海浦东');
INSERT INTO student(id,name,age,address) values (3,'wangwu',23,'北京昌平');

2.4.1.2 一次添加多条数据

INSERT INTO student(id,name,age,address) values (4,'曹操',27,'北京海淀'),
                                            (5,'周瑜',28,'北京朝阳'),
                                            (6,'赵云',30,'北京大兴');

2.4.2 UPDATE

语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 ... WHERE 列名=值

2.4.2.1 一次修改一条数据

UPDATE student SET address='河北保定',age=33 WHERE id=1;

2.4.2.2 一次修改多条数据

UPDATE student SET age=age+5;

2.4.3 DELETE

语法： DELETE FROM 表名【WHERE 列名=值】

2.4.2.1 一次删除一条数据

DELETE FROM student WHERE name=‘zhangsan’;

2.4.2.2 一次删除所有数据

DELETE FROM student;

2.4.2.3 一次删除所有数据

TRUNCATE TABLE studnet;

DELETE 删除表中的数据，表结构还在;删除后的数据使用日志可以找回。
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
TRUNCATE 删除的数据不能找回。执行速度比DELETE快。

2.5 DQL

语法： SELECT 列名 FROM 表名【WHERE --> GROUP BY-->HAVING--> ORDER BY-->LIMIT】

  SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后的行条件*/
  ORDER BY sorting_columns /*对结果排序*/
  LIMIT offset_start, row_count /*结果限定*/

示例操作：

1>创建学生表并添加数据

# 创建表stu
CREATE TABLE stu (
    sid CHAR(6),
    sname       VARCHAR(50),
    age     INT,
    gender  VARCHAR(50)
);
#添加数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

2>创建雇员表并添加数据

#创建雇员表
CREATE TABLE emp(
    empno       INT,
    ename       VARCHAR(50),
    job     VARCHAR(50),
    mgr     INT,
    hiredate    DATE,
    sal     DECIMAL(7,2),
    comm        decimal(7,2),
    deptno      INT
);
#添加数据
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

3>创建部门表并添加数据

#创建部门表
CREATE TABLE dept(
    deptno      INT,
    dname       varchar(14),
    loc     varchar(13)
)CHARSET=utf8;
#添加数据
INSERT INTO dept values(10, '财务部', 'beijing');
INSERT INTO dept values(20, 'java开发部', 'tianjin');
INSERT INTO dept values(30, '测试部', 'shanghai');
INSERT INTO dept values(40, '销售部', 'shenzheng');

2.5.1 简单查询

2.5.1.1 查询所有列 *表示所有列

SELECT * FROM stu;

2.5.1.2 查询指定列

SELECT sid, sname, age FROM stu;

2.5.2 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

(1)查询性别为女，并且年龄小于50的记录

SELECT * FROM stu WHERE gender='female' AND age<50;

(2)查询学号为S_1001，或者姓名为liSi的记录

SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

(3)查询学号为S _ 1001 ，S _ 1002，S _ 1003的记录

SELECT * FROM stu 
WHERE sid IN ('S_1001','S_1002','S_1003');
等同于
SELECT * FROM stu 
WHERE sid='S_1001' or sid='S_1002' or sid='S_1003';

(4)查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM tab_student 
WHERE sid NOT IN('S1001','S1002','S_1003');

(5)查询年龄为null的记录

SELECT * FROM stu WHERE age IS NULL;

(6)查询年龄在20到40之间的学生记录

SELECT * 
FROM stu 
WHERE age BETWEEN 20 AND 40;

(7)　查询性别非男的学生记录

SELECT * 
FROM stu
WHERE NOT gender='male';

(8) 查询姓名不为null的学生记录

SELECT * 
FROM stu
WHERE sname IS NOT NULL;

2.5.3 模糊查询

关键字LIKE。

通配符:

_ 任意一个字符

%：任意0~n个字符

(1)查询姓名由5个字符构成，并且第5个字符为“i”的学生记录

SELECT * 
FROM stu
WHERE sname LIKE '____i';

(2)查询姓名以“z”开头的学生记录

SELECT * 
FROM stu
WHERE sname LIKE 'z%';

(3)查询姓名中包含“a”字符的学生记录

SELECT * 
FROM stu
WHERE sname LIKE '%a%';

2.5.4 字段控制查询

2.5.4.1 去除重复记录

去除重复记录（两行或两行以上记录中列的数据都相同）

SELECT DISTINCT sal FROM emp;
SELECT DISTINCT sal,comm FROM emp;

2.5.4.2 两列之和

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

注：任何东西与NULL相加结果还是NULL，使用函数IFNULL把NULL转换成数值0的

SELECT *,sal+IFNULL(comm,0) FROMemp;

2.5.4.2 给列名添加别名

在上面查询中出现列名为sal+comm，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

注：给列起别名时，也可以省略AS关键字

2.5.5 排序

使用 ORDER BY

2.5.5.1 升序（ASC 默认）

SELECT *
FROM stu
ORDER BY age ASC;

2.5.5.2 降序（DESC）

SELECT *
FROM stu
ORDER BY age DESC;

2.5.6 聚合函数

COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

2.5.6.1 COUNT

查询emp表中记录数：

SELECT COUNT(*) AS ‘cnt’ FROM emp;

查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp
WHERE sal > 2500;

2.5.6.2　SUM和AVG

查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0))FROM emp;

统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

2.5.6.3　MAX和MIN

查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

2.5.7 分组查询

使用GROUP BY子句

注：凡是和聚合函数同时出现的列名，则一定要写在group by 之后

2.5.7.1 分组查询

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*)
FROM emp
WHERE sal>1500
GROUP BY deptno;

2.5.7.2 HAVING子句

查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

注：having与where的区别:

1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
where是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；having是对分组后数据的约束。

2.5.8 LIMIT限制

LIMIT用来限定查询结果的起始行，以及总行数。

查询10行记录，起始行从3开始

SELECT* FROM emp LIMIT 3, 10;

总结单表查询

查询语句书写顺序：select 列 from 表【where- group by- having- order by-limit】
查询语句执行顺序：from 表 where -group by -having - select - order by-limit

2.5.9 多表查询

多个表之间是有关系的，那么关系靠谁来维护?

多表约束：外键约束。

2.5.9.1 多表的关系

一对多关系

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一方的主键.
多对多关系

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.
一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.

两种建表原则：

唯一外键对应：在多的一方创建一个外键指向一的一方的主键，将外键设置为unique和非空.

主键对应：让一对一的双方的主键进行建立关系. 唯一非空

2.5.9.2 多表查询

（1）合并结果集：把两个select语句的查询结果合并到一起！

合并结果集有两种方式：

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

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

注意：被合并的两个结果：列数必须相同，列类型可以不同。

2.5.9.3 连接查询

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

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

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

emp表

CREATE TABLE emp(
    empno   int,
    ename   varchar(50),
    job     varchar(50),
    mgr     int,
    hiredate    date,
    sal     decimal(7,2),
    comm    decimal(7,2),
    deptno  int
);
#添加数据SQL语句省略

dept表

CREATE TABLE dept(
    deptno      int,
    dname       varchar(14),
    loc     varchar(13)
);
#添加数据SQL语句省略

执行如下SQL语句

select * from emp,dept;

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

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

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname 
FROM emp,dept 
WHERE emp.deptno=dept.deptno;

（1）内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！

SQL标准的内连接为：

SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno;
注意：on后面 主外键关系

内连接的特点：查询结果必须满足条件。

（2）外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

a.左外连接：以左表为主表，右表是从表

SELECT * FROM emp e 
LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，左表中满足条件和不满足条件都显示出来，右边不满足条件的显示NULL。

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

b.右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e 
RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

连接查询总结：

连接不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

2.5.9.4 子查询（嵌套查询）

子查询出现的位置：

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

b. from后，作临时表；

当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any 跟结果集里面的多行单列部分进行比较，满足就返回true

b. all 跟结果里面的多行单列所有进行比较,所有都满足了才返回true

子查询结果集的常见形式：

a. 单行单列（用于条件）

b. 多行单列（用于条件）

c. 多行多列（用于表）

示例：

1. 工资高于JONES的员工。

分析：

查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES';

第二步：查询高于JONES工资的员工

SELECT * FROM emp WHERE sal > (第一步结果);

结果：

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

2.6 DCL（待办的）

三、数据完整性

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

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

实体完整性: 行
域完整性: 列
引用完整性: 学生表(学号 ,姓名) 成绩表( 学号,科目,成绩) 科目表（科目编号，科目名称）

3.1 实体完整性约束

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

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

约束类型：

主键约束（primary key）

唯一约束(unique)

自动增长列(auto_increment)

3.1.1 主键约束（primary key）

注：每个表中要有一个主键。

特点：数据唯一，且不能为null

第一种添加方式：

CREATE TABLE student(
    id int primary key,
    name varchar(50)
);

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student(
    classid int,
    stuid int,
    name varchar(50),
    primary key(classid，stuid)
);

3.1.2 唯一约束(unique)

特点：数据不能重复。可以为null

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) unique
);

3.1.3 自动增长列(auto_increment)

CREATE TABLE student(
    Id int primary key auto_increment,
    Name varchar(50)
) auto_increment=100;
INSERT INTO student(name) values(‘tom’);

3.2 域完整性约束

域完整性约束的作用：限制此单元格的数据正确，不对其它单元格起作用，域代表当前单元格

域完整性约束：数据类型、非空约束（not null）、默认值约束(default)

check约束（mysql不支持）check(sex='男'or sex='女')

3.2.1 数据类型

3.2.1.1 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	查看帮助文档	查看帮助文档	单精度浮点数值
DOUBLE	8 字节	查看帮助文档	查看帮助文档	双精度浮点数值
DOUBLE(M,D)	8个字节，M表示长度，D表示小数位数	同上，受M和D的约束 DUBLE(5,2) -999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值

3.2.1.2 日期类型:

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3.2.1.3 字符串类型:

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字符	定长字符串 char(10) 10个字符
VARCHAR	0-65535 字节	变长字符串 varchar(10) 10个字符
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB（binary large object）	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

3.2.2 非空约束

not null

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null,
    Sex varchar(10)
);
INSERT INTO student values(1,’tom’,null);

3.2.3 默认值约束

default

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null,
    Sex varchar(10) default '男'
);
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);

3.3 引用完整性约束（参照完整性约束）

3.3.1 外键约束：FOREIGN KEY

第一种添加外键方式。推荐

#学生表(主表)
CREATE TABLE student(
    sid int primary key,
    name varchar(50) not null,
    sex varchar(10) default '男'
);

#成绩表（从表）
create table score(
        id int,
        score int,
        sid int , 
        CONSTRAINT fk_score_sid foreign key(sid) references student(sid)
);
-- 外键列的数据类型一定要与主键的类型一致

第二种添加外键方式。不推荐

ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(sid);

四、数据库事务

4.1 事务概述

一组要么同时执行成功，要么同时失败的SQL语句。是数据库操作的一个不能分割执行单元。

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

事务开始于

连接到数据库上，并执行一条DML语句insert、update或delete
前一个事务结束后，又输入了另一条DML语句

事务结束于

执行commit或rollback语句。
执行一条DDL语句，例如create table语句，在这种情况下，会自动执行commit语句。
执行一条DDL语句，例如grant语句，在这种情况下，会自动执行commit。
断开与数据库的连接
执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语句。

4.2 事务的四大特点

（ACID）

Atomicity(原子性)

表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

Consistency(一致性)

表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

Isolation(隔离性)

事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

Durability(持久性)

持久性事务完成之后，它对于系统的影响是永久性的。

#开启事务
START TRANSACTION;     
#事务内数据操作语句
UPDATE account SET money=money-1000 WHERE id=1;
UPDATE account SET money=money+1000 WHERE id=2;
#提交事务
COMMIT;
#回滚
ROLLBACK;

第五节视图

5.1 什么是视图？

视图，虚拟表，从一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

5.2 视图的优点和缺点

优点

简单化，数据所见即所得
安全性，用户只能查询或修改他们所能见到得到的数据
逻辑独立性，可以屏蔽真实表结构变化带来的影响

缺点

性能相对较差，简单的查询也会变得稍显复杂
修改不方便，特变是复杂的聚合视图基本无法修改

5.3 创建、使用视图

语法：

语法：
create view 视图名
as
查询源表语句;

创建：
CREATE VIEW 视图名 AS SELECT 列名1，列名2,.... FROM 原表名;

使用：
select * from 视图名 where 条件;

注意：

如果视图包含以下结构中的任何一种，则该视图不可更新：

聚合函数

DISTINCT

GROUP BY

HAVING

UNION、 UNION ALL

FROM子句中的不可更新的多个表

WHERE子句中的子查询，引用FROM子句中的表

5.4 视图的修改

方式一：
/*
create or replace view 视图名
as
查询语句;

*/
SELECT * FROM myv3

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

方式二：
/*
语法：
alter view 视图名
as
查询语句;

*/
ALTER VIEW myv3
AS
SELECT * FROM employees;

5.5 删除视图

/*

语法：drop view 视图名,视图名,...;
*/

DROP VIEW emp_v1,emp_v2,myv3;

DROP VIEW IF EXISTS v_students_info; --删除视图

SHOW CREATE VIEW v_students_info;--已不存在

六、数据库管理和常用函数

6.1 导出导入数据库

6.1.1 使用命令方式

导出数据库表

mysqldump -u root -p 数据库名 > school.sql

导入数据库表

mysql -u root -p
mysql>use 数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/dbname.sql

6.1.2 使用SQLyog工具完成导出导入(演示操作)

1 先备份,右击数据库选择备份---》备份数据库，转储到sql
2 导入，右击选择 "执行SQL脚本"

6.2 创建用户和授权

要使用命令行登录。

创建用户

CREATE USER `zhangsan` IDENTIFIED BY '123';
CREATE USER `zhangsan`@`localhost` IDENTIFIED BY '123';

授权

GRANT ALL ON school.* TO `zhangsan`;

撤销权限

REVOKE ALL ON school.* FROM `zhangsan`;

删除用户

DROP USER `zhangsan`;

6.3时间处理

函数名	作用	举例（结果与当前时间有关）
CURDATE()	获取当前日期	**SELECT CURDATE();返回：2016-08-08
CURTIME()	获取当前时间	SELECT CURTIME(); 返回：19:19:26
NOW()	获取当前日期和时间	SELECT NOW(); 返回：2016-08-08 19:19:26
WEEK(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW()); 返回：26
YEAR(date)	返回日期date的年份	SELECT YEAR(NOW()); 返回：2016
HOUR(time)	返回时间time的小时值	SELECT HOUR(NOW()); 返回：9
MINUTE(time)	返回时间time的分钟值	SELECT MINUTE(NOW()); 返回：43
DATEDIFF(date1,date2)	返回日期参数date1和date2之间相隔的天数	SELECT DATEDIFF(NOW(), '2008-8-8'); 返回：2881
ADDDATE(date,n)	计算日期参数date加上n天后的日期	SELECT ADDDATE(NOW(),5); 返回：2016-09-02 09:37:07

6.4字符串处理

函数名	作用	举例
**CONCAT(str1, str1...strn)	字符串连接	SELECT CONCAT('My','S','QL');返回：MySQL
INSERT(str, pos,len, newstr)	字符串替换	SELECT INSERT( '这是SQL Server数据库', 3,10,'MySQL'); 返回：这是MySQL数据库
LOWER(str)	将字符串转为小写	SELECT LOWER('MySQL'); 返回：mysql
UPPER(str)	将字符串转为大写	SELECT UPPER('MySQL');返回：MYSQL
SUBSTRING (str,num,len)	字符串截取	SELECT SUBSTRING( 'JavaMySQLOracle',5,5);返回：MySQL

七、综合练习

某网上商城数据库表结构如下：

# 创建用户表
create table user(
     userId int primary key auto_increment,
     username varchar(20) not null,
     password varchar(18) not null,
     address varchar(100),
     phone varchar(11)
);

#一对多的实现
#创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100) not null       #分类名称
);

# 商品表
CREATE TABLE `products` (
  `pid` varchar(32) PRIMARY KEY,
  `name` VARCHAR(40) ,
  `price` DOUBLE(7,2),
   category_id varchar(32),
   constraint foreign key(category_id) references category(cid)
);

#多对多的实现
#订单表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double(12,2), #总计
  `userId` int,
   constraint foreign key(userId) references user(userId) #外键
);

# 订单项表
create table orderitem(
  oid varchar(32),  #订单id
  pid varchar(32),  #商品id
  num int ,         #购买商品数量
  primary key(oid,pid), #主键
  foreign key(oid) references orders(oid),
  foreign key(pid) references products(pid)
);

////////////////////////////////////////////////////////////////////////////////
#初始化数据

#用户表添加数据
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');

#给商品表初始化数据
insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');
insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');
insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');
insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');
insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');
insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);


#给分类表初始化数据
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');

#添加订单
insert into orders values('o6100',18000.50,1);
insert into orders values('o6101',7200.35,1);
insert into orders values('o6102',600.00,2);
insert into orders values('o6103',1300.26,4);

#订单详情表
insert into orderitem values('o6100','p001',1),('o6100','p002',1),('o6101','p003',1)

7.1 综合练习1-【多表查询】

1>查询所有用户的订单

SELECT o.oid,o.totalprice, u.userId,u.username,u.phone 
FROM orders o INNER JOIN USER u ON o.userId=u.userId;

2>查询用户id为 1 的所有订单详情

SELECT o.oid,o.totalprice, u.userId,u.username,u.phone ,oi.pid
FROM  orders o INNER JOIN USER u ON o.userId=u.userId
INNER JOIN orderitem oi ON o.oid=oi.oid
where u.userid=1;

7.2 综合练习2-【子查询】

1>查看用户为张三的订单

SELECT * FROM orders WHERE userId=(SELECT userid FROM USER WHERE username='张三');

2>查询出订单的价格大于800的所有用户信息。

SELECT * FROM USER WHERE userId IN (SELECT DISTINCT userId FROM orders WHERE totalprice>800)

7.3 综合练习3-【分页查询】

1>查询所有订单信息，每页显示5条数据

#查询第一页
SELECT * FROM orders LIMIT 0,5

作业题

数据库结构
创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据
学生信息表 Student
字段名   字段类型      字段约束    含义
Sno     varchar(3)  Not null    学员编号
Sname   varchar(4)  Not null    学员姓名 
Ssex    varchar(2)  Not null    性别
Sbirthday   Datetime            生日
Classnum    varchar(5)          班级号

CREATE TABLE student
(
    SNO varchar(3) NOT NULL,
    SNAME varchar(4) NOT NULL,
    SSEX varchar(2) NOT NULL,
    SBIRTHDAY DATETIME,
    CLASS varchar(5)
);
课程信息表 course
字段名 字段类型    字段约束     含义
Cno     varchar(5)   Not null  课程编号
Cname   varchar(10)  Not null  课程名称
Tno     varchar(10)  Not null  授课老师编号

CREATE TABLE course
(
    CNO VARCHAR(5) NOT NULL,
    CNAME VARCHAR(10) NOT NULL,
    TNO VARCHAR(10) NOT NULL
)charset=utf8;
成绩表score
字段名 字段类型    字段约束     含义
Sno Varchar(3)  Not null    学员编号
Cno Varchar(5)  Not null    课程编号
Degree  Double(3,1) Not null  分数

CREATE TABLE score
(
    SNO  varchar(3) NOT NULL,
    CNO  varchar(5) NOT NULL,
    DEGREE Double(3,1) NOT NULL
);
讲师表teacher
字段名     字段类型    字段约束     含义
Tno     varchar(3)  Not null      讲师编号
Tname   varchar(4)  Not null  讲师姓名
Tsex    varchar(2)  Not null  讲师性别
Tbirthday Datetime Not null 出生日期
Prof    varchar(6)  等级
Depart  varchar(10) 所属院系

CREATE TABLE teacher
(
    TNO   varchar(3) NOT NULL,
    TNAME varchar(4) NOT NULL, 
    TSEX  varchar(2) NOT NULL,
    TBIRTHDAY DATETIME NOT NULL, 
    PROF    varchar(6),
    DEPART  varchar(10) NOT NULL
);

向表中存储数据
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,1977-09-01,95033);
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,1975-10-02,95031);
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,1976-01-23,95033);
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,1976-02-20,95033);
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,1975-02-10,95031);
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,1974-06-03,95031);


INSERT INTO course(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
INSERT INTO course(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO course(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO course(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);


INSERT INTO score(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO score(SNO,CNO,DEGREE)VALUES (108,'6-166',81);


INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');


1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。