day05_mySQL

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

SQL：Structure Query Language。（结构化查询语言）

Sql的分类

DDL(*)（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

DML(**)（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

DQL(*****)（Data Query Language）：数据查询语言，用来查询记录（数据）。

* 注意：sql语句以;结尾

DDL:操作数据库、表、列等

使用的关键字：CREATE、 ALTER、 DROP

创建

Create database mydb1;

Create database mydb2 character set gbk;

Create database mydb3 character set gbk COLLATE gbk_chinese_ci;

查询

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

Show databases;

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

Show  create  database mydb2;

修改

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

 alter database mydb2 character set utf8;

删除      

Drop database mydb3;

操作数据表

* 语法：

create table 表名(

     字段1 字段类型,

     字段2 字段类型,

     ...

     字段n 字段类型

);

* 常用数据类型：

            int：整型

            double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

            char：固定长度字符串类型；    char(10)     'aaa       '  占10位

            varchar：可变长度字符串类型； varchar(10)  'aaa'  占3为

             text：字符串类型；

             blob：字节类型；

             date：日期类型，格式为：yyyy-MM-dd；

             time：时间类型，格式为：hh:mm:ss

             timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值

             datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

DML操作(重要)

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

INSERT 、UPDATE、 DELETE

小知识：

在mysql中，字符串类型和日期类型都要用单引号括起来。

空值：null

INSERT

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

注意：列名与列值的类型、个数、顺序要一一对应。

可以把列名当做java中的形参，把列值当做实参。

参不要超出列定义的长度。

如果插入空值，请使用null

插入的日期和字符一样，都使用引号括起来

UPDATE

语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。 WHERE 列名=值

DELETE

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

数据的完整性

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

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

完整性的分类：

实体完整性:（行级约束）

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

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

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

• 主键约束（primary key）

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

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

CREATE TABLE student(
id int primary key,
name varchar(50)
);
//第二种添加方式：此种方式优势在于，可以创建联合主键
CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);	
CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid，stuid)
);

• 唯一约束(unique)：

特点：数据不能重复。

CREATE TABLE student(

Id int primary key,

Name varchar(50) unique

); 

 域完整性:（列级约束）

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域代表当前单元格

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

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

数据类型:（数值类型、日期类型、字符串类型）

• 非空约束：not null

CREATE TABLE student(

Id int pirmary key,

Name varchar(50) not null,

Sex varchar(10)

);

• 默认值约束 default

CREATE TABLE student(

Id int pirmary key,

Name varchar(50) not null,

Sex varchar(10) default ‘男’

); 

引用完整性:

• 外键约束：FOREIGN KEY

多表查询（重要）

多表查询有如下几种：

合并结果集；UNION 、  UNION ALL

1. 作用：合并结果集就是把两个select语句的查询结果合并到一起！
2. 合并结果集有两种方式：

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

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

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

连接查询

连接查询就是求出多个表的乘积，例如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;

              内连接  [INNER] JOIN  ON

               外连接  OUTER JOIN ON                  

                     1、左外连接 LEFT [OUTER] JOIN

                     2、 右外连接 RIGHT [OUTER] JOIN

                     3、全外连接（MySQL不支持）FULL JOIN

内连接

内连接不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

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

左连接 

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

右连接

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

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

             自然连接  NATURAL JOIN

                             大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去                     给出主外键等式，它会自动找到这一等式：

                                两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所                     以会被自然连接找到！

                    当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN dept;[q1] 

SELECT * FROM emp NATURAL LEFT JOIN dept;[q2] 

SELECT * FROM emp NATURAL RIGHT JOIN dept;[q3] 

      子查询

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

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

• 子查询出现的位置：

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

from后，作表；

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

any

all

• 子查询结果集的形式：

单行单列（用于条件）

单行多列（用于条件）

多行单列（用于条件）

多行多列（用于表）

MySQL数据库的备份与恢复

1　生成SQL脚本  导出数据

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。
现在可以在C盘下找到mydb1.sql文件了！

mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

2　执行SQL脚本 恢复数据

前提：必须先创建数据库名

执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！

执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！ 

SOURCE C:\mydb1.sql

　　注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

还可以通过下面的方式来执行脚本文件：

mysql -uroot -p123 mydb1<c:\mydb1.sql

mysql –u用户名 –p密码 数据库<要执行脚本文件路径

这种方式无需登录mysql！

注意：在CMD下 命令不能加;