数据库系列（一）:数据库基础01

ithao2 2019年05月22日

阿里云活动（仅限新用户）：https://promotion.aliyun.com/ntms/yunparter/invite.html?userCode=1frag12g

1、数据库介绍篇

1.1什么是数据库

数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言）去操作文件中的数据。

技术定义：数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设备上的仓库。

1.2数据库介绍

我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库。

我们学习数据库，并不是学习如何去开发一个数据库软件，我们学习的是如何使用数据库以及数据库中的数据记录的操作。而数据库软件是由第三方公司研发。

1.3数据库的分类

关系型、非关系型的数据库

常见的数据库软件：

Oracle：它是Oracle公司的大型关系型数据库，它是收费的。

DB2：IBM公司的数据库，它是收费的。

SqlServer：微软数据库。收费

Sybase：Sybase公司的。工具PowerDesign 数据库建模工具。

MySql：早期瑞典一个公司发明，后期被sun公司收购，后期被Oracle。

Java开发应用程序主要使用的数据库：MySQL（5.5）、Oracle、DB2。

1.4什么是关系型数据库

在开发软件的时候，软件中的数据之间必然会有一定的关系存在，需要把这些数据保存在数据库中，同时也要维护数据之间的关系，这时就可以直接使用上述的那些数据库。而上述的所有数据库都属于关系型数据库。

描述数据之间的关系，并保存在数据库中，同时学习如果根据这些关系查询数据库中的数据，

关系型数据：设计数据库的时候，需要使用E-R图来描述。实体关系

E-R：实体关系图。

实体：可以理解成我们Java程序中的一个对象。在E-R图中使用矩形(长方形) 表示。

针对一个实体中的属性，我们称为这个实体的数据，在E-R图中使用椭圆表示。

实体和实体之间的关系：在E-R图中使用菱形表示。

2、mysql在linux-安装篇

2.1、vmware中安装linux注意事项

2.1.1、记得关闭防火墙

 service iptables stop chkconfig iptables off（关闭开机自启：所谓的永久关闭防火墙）

2.1.2、创建统一的管理目录

 mkdir -p /exprot/software  mkdir -p /export/servers

2.1.3软件环境

 VMware、crt、centos6.9

2.1.4安装环境

 1、VMware软件安装  2、构建虚拟机  3、需要配置Linux（ip,mac地址，hostname，防火墙），就可以通过crt这个客户端连接进行操作  4、在linux操作系统进行安装msyql-5.6 说明：因为在linux操作系统中，安装软件的方式主要有3种：1、源码安装（redis）2、rpm安装  3、yum在线安装（安装MySQL为例）---linux联网（）

2.2、centos6.9安装mysql

2.2.1、检查是否有自带的mysql

 [root@hadoop-01 servers]# rpm -qa |grep mysql mysql-libs-5.1.73-8.el6_8.x86_64

2.2.2、卸载自带的mysql

 [root@hadoop-01 servers]# rpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_64 [root@hadoop-01 servers]#

2.2.3、下载mysql安装包

2.2.4、上传安装包到linux服务器

 rz 上传文件到指定的目录（yum install lrzsz） /export/software/mysql

2.2.5、安装

 rpm -ivh *.rpm

2.2.6、查看初始化密码

 A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'.154·

 [root@mysql ~]# cat /root/.mysql_secret # The random password set for the root user at Wed Aug 8 22:19:00 2018 (local time): xQkcU3kbyuZby1_V  [root@mysql ~]#

2.2.7、启动mysql并登录

 #启动mysql service mysql start

 #登录mysql mysql -uroot -p (粘贴密码：xQkcU3kbyuZby1_V)

2.2.8、修改密码

 set PASSWORD=PASSWORD('123456');

2.2.9、退出mysql客户端

 mysql>quit

2.2.10、用新密码进行登录

 mysql -uroot -p 123456（新密码）

2.2.11、远程授权(赋予任何主机访问你数据库的权限)

 grant all privileges on *.* to 'root' @'%' identified by '123456'; flush privileges;

2.2.12、验证远程授权是否成功

 通过windows的mysql客户端工具连接，是否能连接上，能连接上就授权成功，没有连接上，说明没有授权成功！

3、mysql-基础操作篇

3.1、登录mysql

 mysql -uroot -p 123456

3.2、退出mysql

 mysql>quit

3.3、输入查询

查看当前mysql的版本号及当前时间

 SELECT VERSION(), CURRENT_DATE;

 mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 5.6.25 | 2018-08-08 | +-----------+--------------+ 1 row in set (0.32 sec)

mysql中sql语句不区分大小写

 mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;

 mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 5.6.25 | 2018-08-08 | +-----------+--------------+ 1 row in set (0.00 sec)  mysql> select version(), current_date; +-----------+--------------+ | version() | current_date | +-----------+--------------+ | 5.6.25 | 2018-08-08 | +-----------+--------------+ 1 row in set (0.00 sec)  mysql> SeLeCt vErSiOn(), current_DATE; +-----------+--------------+ | vErSiOn() | current_DATE | +-----------+--------------+ | 5.6.25 | 2018-08-08 | +-----------+--------------+ 1 row in set (0.00 sec)  mysql>

可以进行简单的计算（如下所示）

 mysql>SELECT SIN(PI()/4), (4+1)*5;  mysql> SELECT SIN(PI()/4), (4+1)*5; +--------------------+---------+ | SIN(PI()/4) | (4+1)*5 | +--------------------+---------+ | 0.7071067811865475 | 25 | +--------------------+---------+ 1 row in set (0.34 sec)

多条语句比较短，可以写在一行

 mysql>SELECT VERSION(); SELECT NOW();  mysql> SELECT VERSION(); SELECT NOW(); +-----------+ | VERSION() | +-----------+ | 5.6.25 | +-----------+ 1 row in set (0.00 sec)  +---------------------+ | NOW() | +---------------------+ | 2018-08-08 23:11:11 | +---------------------+ 1 row in set (0.00 sec)

多个字段之间可以用逗号分隔，多行组成一条语句结束以分号结束

 mysql> SELECT -> USER() -> , -> CURRENT_DATE; +---------------+--------------+ | USER() | CURRENT_DATE | +---------------+--------------+ | jon@localhost | 2010-08-06 | +---------------+--------------+

sql语句写了一半，又不想执行可以在语句末尾加上'\c'
```
 mysql> select -> user() -> \c mysql> 
```

3.4、创建和使用数据库

查看当前有哪些数据库

 mysql>show databases;  mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.07 sec)

创建数据库
```
 mysql> CREATE DATABASE menagerie;
```
使用及切换数据库
```
 mysql> USE menagerie Database changed
```

3.5、创建表及使用

查看当前数据库有哪些表
```
 mysql>show tables;
```

创建一个表

 mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

校验创建表语句是否和执行的一致

 mysql>show create table pet;  +-------+-------------------------------- | Table | Create Table +-------+-------------------------------- | pet | CREATE TABLE `pet` ( `name` varchar(20) DEFAULT NULL, `owner` varchar(20) DEFAULT NULL, `species` varchar(20) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `birth` date DEFAULT NULL, `death` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------

查看表详情

 mysql> desc pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+

准备数据

 Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29

3.6、表中导入数据

在表中导入数据的方式有两种

第一种：将以上数据整理成SQL语句，insert into pet....

第二种：通过加载文件的方式将数据导入到表中

1、创建一个pet.txt的文件（注：每个字段中用tab键隔开，字段没有值得记录用\N代替）

 Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1979-08-31 Chirpy Gwen bird f 1998-09-11 Whistl Gwen bird \N 1997-12-09 Slim Benny snake m 1996-04-29

2、加载数据

 mysql> load data local infile '/root/data/pet.txt' into table pet; Query OK, 8 rows affected, 6 warnings (0.06 sec) Records: 8 Deleted: 0 Skipped: 0 Warnings: 6

3、校验是否加载进去

 mysql> select *from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.01 sec)

3.7、数据检索部分

3.7.1、检索全部数据

 mysql> select *from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.01 sec)

3.7.2、删除表中全部数据

 mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

3.7.3、更新表中特定记录的数据

更新表中名字为Bowser的生日

 mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

3.7.4、查询特定的行

查询名字为Bowser的记录

 mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+

说明：字符串比较不区分大小写！如下所示：

 mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)  mysql> SELECT * FROM pet WHERE name = 'BowsEr'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)  mysql> SELECT * FROM pet WHERE name = 'BOWSER'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)

3.7.4.1、查找生日在1998年以后的特定查询

 mysql> SELECT * FROM pet WHERE birth >= '1998-1-1'; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+

3.7.4.2、多条件查询（and | or）

 mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+

 mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+-------+---------+------+------------+-------+

优先执行括号中的逻辑

 mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+

3.7.5、检索特定的列

 mysql> SELECT name, birth FROM pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+

查询不重复的字段要使用关键词DISTINCT

 mysql> SELECT DISTINCT owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+

可以使用组合条件查询特定的列

 mysql> SELECT name, species, birth FROM pet -> WHERE species = 'dog' OR species = 'cat'; +--------+---------+------------+ | name | species | birth | +--------+---------+------------+ | Fluffy | cat | 1993-02-04 | | Claws | cat | 1994-03-17 | | Buffy | dog | 1989-05-13 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | +--------+---------+------------+

3.7.6、排序

根据某个字段进行排序（关键词：ORDER BY ）

 mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Bowser | 1989-08-31 | | Fang | 1990-08-27 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+

升降序排列（desc：降序；asc：升序）

 mysql> SELECT name, birth FROM pet ORDER BY birth desc;//降序排列 mysql> SELECT name, birth FROM pet ORDER BY birth asc ;//升序排列

多列排序

根据species字段升序排列，根据birth字段降序排列

注： ORDER BY species 中无asc，desc，默认为升序排列

 mysql> SELECT name, species, birth FROM pet -> ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy | dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +----------+---------+------------+

3.7.7、日期计算

查看宠物多少岁，就可以使用计算日期的函数TIMESTAMPDIFF()

 #查询当前的日期 mysql> select curdate() from pet; +------------+ | curdate() | +------------+ | 2018-08-09 | +------------+  #获取当年的年 mysql> select YEAR('2018-02-05') AS YEARS from pet; +-------+ | YEARS | +-------+ | 2018 | +-------+  #获取当年的月 mysql> select month('2018-02-05') AS YEARS from pet; +-------+ | YEARS | +-------+ | 2 | +-------+  #获取当年的日 mysql> select day('2018-02-05') AS YEARS from pet; +-------+ | YEARS | +-------+ | 5 | +-------+

 mysql> SELECT name, birth, CURDATE(), -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age -> FROM pet;

3.7.8、null和not null值

对一些字段类型要进行检查，判断某些字段是否为NULL，或者 non-NULL

 mysql> SELECT name, birth, death, -> TIMESTAMPDIFF(YEAR,birth,death) AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | 1989-08-31 | 1995-07-29 | 5 | +--------+------------+------------+------+

4、实例

以下是如何解决MySQL的一些常见问题的示例。

4.1、首先创建一个表，并且导入数据

 CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer));   INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45), (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

4.2、检索表中的全部数据

 select * from shop;  +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+

4.3、求某一列的最大值或者最小值

 SELECT MAX(article) AS article FROM shop;  +---------+ | article | +---------+ | 4 | +---------+  //求某一列的最小值 select min(price) as article from shop; +---------+ | article | +---------+ | 1.25 | +---------+

4.4、过滤出某个字段值最大的整条记录数据-涉及到子查询

 SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);  +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+

4.5、也可以通过关联查询来进行检索

 SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;  SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;

4.6、求出每一列的最大值，并且根据某一个字段进行分组--分组topn求法

 SELECT article, MAX(price) AS price FROM shop GROUP BY article;  +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+

4.6的另一种写法

 SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);  +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+

5、SQL中的聚合函数

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：

1.count 统计个数（行数）

2.sum函数：求和

3.avg函数：求平均值

4.max、min 求最大值和最小值

5.1、count函数

语法：select count(*)|count(列名) from表名

注意： count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。

 mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec)  mysql> select count(sex) from pet; +------------+ | count(sex) | +------------+ | 7 | +------------+ 1 row in set (0.00 sec)  mysql> select count(owner) from pet; +--------------+ | count(owner) | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec)  mysql> select count(death) from pet; +--------------+ | count(death) | +--------------+ | 1 | +--------------+

5.2、sum函数

语法：select sum(列名) from 表名;

注意事项：

1、如果使用sum 多列进行求和的时候，如果某一列中有null，这一列所在的行中的其他数据不会被加到总和。

2、可以使用mysql 数据库提供的函数 ifnull(列名,值)

3、在数据库中定义double类型数据，是一个近似值，需要确定准确的位数，这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)

numeric double float

 mysql> select sum(price) from shop; +------------+ | sum(price) | +------------+ | 42.77 | +------------+

5.3、avg函数

语法：select avg(列名) from 表名;

 mysql> select avg(price) from shop; +------------+ | avg(price) | +------------+ | 6.110000 | +------------+

5.4、max函数

语法：select max(列名) from 表名;

 mysql> select max(price) from shop; +------------+ | max(price) | +------------+ | 19.95 | +------------+

5.5、min函数

语法：select min(列名) from 表名;

 mysql> select min(price) from shop; +------------+ | min(price) | +------------+ | 1.25 | +------------+

6、SQL分类

6.1、DDL （数据定义语言）

数据定义语言 - Data Definition Language

用来定义数据库的对象，如数据表、视图、索引等

 创建数据库：create database test; 创建视图：create view test; 创建索引：create index test; 创建表：create table test1;

6.2、DML （数据操纵语言）

数据处理语言 - Data Manipulation Language

在数据库表中更新，增加和删除记录

如 update， insert， delete

 update tableName set age='18' where name='lisi'  insert into tableName value('1','2','3');  drop table tableName //删除表操作

6.3、DCL （数据控制语言）

数据控制语言 – Data Control Language

指用于设置用户权限和控制事务语句

如grant，revoke，if…else，while，begintransaction

6.4、DQL （数据查询语言）

数据查询语言 – Data Query Language

select

6.5、小结

 1、创建数据库：create database itcast; 2、使用数据库：use itcast; 3、查看当前数据库中的所有表：show tables ; 4、查看所有的数据库：show databases; 5、删除数据库：drop database itcast; 6、删除数据库中的表：drop table t1;

7、数据库的备份与恢复

7.1、备份命令

在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。

语法：mysqldump -u 用户名 -p 数据库名> 磁盘SQL文件路径

由于mysqldump命令不是sql命令，需要在dos窗口下使用。

注意：在备份数据的时候，数据库不会被删除。可以手动删除数据库。同时在恢复数据的时候，不会

自动的给我们创建数据库，仅仅只会恢复数据库中的表和表中的数据。

 mysqldump -uroot -p123456 menagerie >/root/data/menagerie.sql  //备份的文件 -rw-r--r--. 1 root root 3118 Oct 20 04:04 menagerie.sql

7.2、恢复命令

恢复数据库，需要手动的先创建数据库：

create database heima2;

语法：mysql -u 用户名-p 导入库名< 硬盘SQL文件绝对路径

需求：

1、创建heima8数据库。

2、重新开启一个新的dos窗口。

3、将mydb2备份的数据表和表数据恢复到mydb6中。

 //恢复命令 mysql -uroot -p123456 itcast</root/data/menagerie.sql

8、多表查询

8.1、笛卡尔积介绍

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

准备数据：

 create table A( A_ID int primary key auto_increment, A_NAME varchar(20) not null ); insert into A values(1,'apple'); insert into A values(2,'orange'); insert into A values(3,'banana');  create table B( A_ID int primary key auto_increment, B_PRICE double ); insert into B values(1,2.30); insert into B values(2,3.50); insert into B values(4,null);

展示效果：

 mysql> select * from A,B; +------+--------+------+---------+ | A_ID | A_NAME | A_ID | B_PRICE | +------+--------+------+---------+ | 1 | apple | 1 | 2.3 | | 2 | orange | 1 | 2.3 | | 3 | banana | 1 | 2.3 | | 1 | apple | 2 | 3.5 | | 2 | orange | 2 | 3.5 | | 3 | banana | 2 | 3.5 | | 1 | apple | 4 | NULL | | 2 | orange | 4 | NULL | | 3 | banana | 4 | NULL | +------+--------+------+---------+

作用：笛卡尔积的数据，对程序是没有意义的，我们需要对笛卡尔积中的数据再次进行过滤。

对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。

 mysql> select * from A,B where B.A_ID=1; +------+--------+------+---------+ | A_ID | A_NAME | A_ID | B_PRICE | +------+--------+------+---------+ | 1 | apple | 1 | 2.3 | | 2 | orange | 1 | 2.3 | | 3 | banana | 1 | 2.3 | +------+--------+------+---------+ 3 rows in set (0.00 sec)  mysql> select * from A,B where B.A_ID=1 and A.A_ID=1; +------+--------+------+---------+ | A_ID | A_NAME | A_ID | B_PRICE | +------+--------+------+---------+ | 1 | apple | 1 | 2.3 | +------+--------+------+---------+

8.2、内连接（表的交集）

内连接：语法一： select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;

语法二：

select * from 表名1 inner join 表名2 on 条件

 mysql> select * from A inner join B on A.A_ID=B.A_ID; +------+--------+------+---------+ | A_ID | A_NAME | A_ID | B_PRICE | +------+--------+------+---------+ | 1 | apple | 1 | 2.3 | | 2 | orange | 2 | 3.5 | +------+--------+------+---------+

8.3、左外连接

外链接：左外连接、右外连接、全连接、自连接。

左外连接：用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。

即：虽然右表没有香蕉对应的价格，也要把他查询出来。

语法：select * from 表1 left outer join 表2 on 条件;

 mysql> select * from A left join B on A.A_ID=B.A_ID; +------+--------+------+---------+ | A_ID | A_NAME | A_ID | B_PRICE | +------+--------+------+---------+ | 1 | apple | 1 | 2.3 | | 2 | orange | 2 | 3.5 | | 3 | banana | NULL | NULL | +------+--------+------+---------+

8.4、右外连接

用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示。

即：不管左方能够找到右方价格对应的水果，都要把左方的价格显示出来。

语法：select * from 表1 right outer join 表2 on 条件;

 mysql> select * from A right join B on A.A_ID=B.A_ID; +------+--------+------+---------+ | A_ID | A_NAME | A_ID | B_PRICE | +------+--------+------+---------+ | 1 | apple | 1 | 2.3 | | 2 | orange | 2 | 3.5 | | NULL | NULL | 4 | NULL | +------+--------+------+---------+

8.5、全外连接

全外连接：左外连接和右外连接的结果合并，但会去掉重复的记录。 select * from 表1 full outer join 表2 on 条件 select * from a full outer join b on a.A_ID = b.A_ID; 但是mysql数据库不支持此语法。

8.6、关联子查询

关联子查询：把一个sql的查询结果作为另外一个查询的参数存在。

8.6.1、in和exist关键词的用法

关联子查询其他的关键字使用：

回忆：age=23 or age=24 等价于 age in (23,24)

in 表示条件应该是在多个列值中。

in：使用在where后面，经常表示是一个列表中的数据，只要被查询的数据在这个列表中存在即可。

 mysql> select * from A where A_ID in(1,2,3); +------+--------+ | A_ID | A_NAME | +------+--------+ | 1 | apple | | 2 | orange | | 3 | banana | +------+--------+ 3 rows in set (0.00 sec)  mysql> select * from A where A_ID =1 or A_ID =2 or A_ID =3; +------+--------+ | A_ID | A_NAME | +------+--------+ | 1 | apple | | 2 | orange | | 3 | banana | +------+--------+ //not in mysql> select * from A where A_ID not in (1,2,3,4); Empty set (0.00 sec)  mysql> select * from A where A_ID not in (3,4); +------+--------+ | A_ID | A_NAME | +------+--------+ | 1 | apple | | 2 | orange | +------+--------+ 2 rows in set (0.00 sec)

exists：

exists：将外查询表的每一行，代入子查询作为检验，如果exists子句返回TRUE，就将外查询的这行记录放进结果集中。

使用exists完成：

 mysql> select * from A where exists(select A_ID from B); +------+--------+ | A_ID | A_NAME | +------+--------+ | 1 | apple | | 2 | orange | | 3 | banana | +------+--------+  mysql> select * from A where not exists(select A_ID from B); Empty set (0.00 sec)

in not in 与 exists not exists 的区别参考：https://www.jianshu.com/p/9c863ad1032f

8.6.2、union 和union all使用法

UNION 语句：用于将不同表中查询的数据合并展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中查询的数据合并展示出来；（包括重复数据）

 mysql> select * from A union select * from B; +------+--------+ | A_ID | A_NAME | +------+--------+ | 1 | apple | | 2 | orange | | 3 | banana | | 1 | 2.3 | | 2 | 3.5 | | 4 | NULL | +------+--------+ 6 rows in set (0.00 sec)  mysql> select * from A union all select * from B; +------+--------+ | A_ID | A_NAME | +------+--------+ | 1 | apple | | 2 | orange | | 3 | banana | | 1 | 2.3 | | 2 | 3.5 | | 4 | NULL | +------+--------+

8.6.3、case when 语句

case when 语句语法结构：

 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END

准备数据

 //创建表 create table employee( empid int , deptid int , sex varchar(20) , salary double );  //加载数据 1 10 female 5500.0 2 10 male 4500.0 3 20 female 1900.0 4 20 male 4800.0 5 40 female 6500.0 6 40 female 14500.0 7 40 male 44500.0 8 50 male 6500.0 9 50 male 7500.0  load data local infile '/root/data/emp.txt' into table employee ;

 select *, case when salary < 5000 then "低等收入" when salary>= 5000 and salary < 10000 then "中等收入" when salary > 10000 then "高等收入" end as level, case sex when "female" then 1 when "male" then 0 end as flag from employee;

HTML教程 HTML5教程 HTML参考手册 HTML字符集 HTML标签 HTMLDOM教程 CSS教程 CSS3教程 Bootstrap3教程 Bootstrap4教程 FontAwesome图标 Foundation5教程 CSS参考手册 JavaScript教程 JavaScript对象 HTMLDOM对象 jQuery教程 JSON教程 AngularJS教程 Angular2教程 Vue.js教程 React教程 jQueryUI教程 jQueryEasyUI教程 AJAX教程 Highcharts教程 GoogleMapsAPI教程 ES6教程 TypeScript教程 ES6标准入门 PHP教程设计模式正则表达式-教程 WebServices教程 Python2教程 Python3教程 Django教程 NumPy教程 ASP.NET教程 C#教程 Ruby教程 Go语言教程 Java教程 Servlet教程 JSP教程 Maven教程 Node.js教程 C语言教程 C++教程 Perl教程 Lua教程 Scala教程 ASP教程 VBScript教程 Linux教程 Docker教程 SQL教程 MySQL教程 SQLite教程 MongoDB教程 Redis教程 Memcached教程 ADO教程 Android教程 Swift教程 jQueryMobile教程 ionic教程 Kotlin教程 XML教程 DTD教程 XMLDOM教程 XSLT教程 XPath教程 XQuery教程 XLink教程 XMLSchema教程 XSL-FO教程 SVG教程 SOAP教程 RDF教程 WSDL教程 RSS教程 AppML教程 Eclipse教程 Git教程 SVN教程 Firebug教程 HTTP教程网站建设指南浏览器信息网站主机教程 TCP/IP教程 W3C教程网站品质教程