MySQL基础

原创

虾王之王 2024-04-27 12:32:22 ©著作权

©著作权归作者所有：来自51CTO博客作者虾王之王的原创作品，请联系作者获取转载授权，否则将追究法律责任

数据库简介

什么是数据库

数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言）去操作文件中的数据。技术定义：数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设备上的仓库。

我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库。我们学习数据库，并不是学习如何去开发一个数据库软件，我们学习的是如何使用数据库以及数据库中的数据记录的操作。而数据库软件是由第三方公司研发。

数据库的分类

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

常见的数据库软件： Oracle：它是Oracle公司的大型关系型数据库，它是收费的。 DB2：IBM公司的数据库，它是收费的。 SqlServer：微软数据库。收费 Sybase：Sybase公司的。工具PowerDesign 数据库建模工具。 MySql：早期瑞典一个公司发明，后期被sun公司收购，后期被Oracle。 Java开发应用程序主要使用的数据库： MySQL（5.5）、Oracle、DB2。

什么是关系型数据库

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

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

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

实体：可以理解成我们Java程序中的一个对象。在E-R图中使用矩形(长方形) 表示。针对一个实体中的属性，我们称为这个实体的数据，在E-R图中使用椭圆表示。实体和实体之间的关系：在E-R图中使用菱形表示。

mysql在ubuntu的安装

# 安装最新版本
sudo apt install -y mysql-server
# 安装指定版本
sudo apt install -y mysql-server-8.0

mysql的登录

#登录mysql
mysql ‐uroot ‐p

在命令行输入密码

修改密码

use mysql
update user set PASSWORD=PASSWORD('123456');

退出mysql客户端

mysql>quit

远程授权

默认root用户是不允许在远程登录的。

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

数据库查询

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

创建表及使用

查看当前数据库有哪些表

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 | |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐

表中导入数据

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

第一种：将以上数据整理成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 1995‐07‐29
Chirpy Gwen bird f 1998‐09‐11
Whistler Gwen bird \N 1997‐12‐09 \N
Slim Benny snake m 1996‐04‐29

加载数据

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

数据检索

检索全部数据

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)

删除表中全部数据

mysql> DELETE FROM pet;

更新表中特定记录的数据

更新表中名字为Bowser的生日

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

查询特定的行

查询名字为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)

查找生日在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 |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

多条件查询（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 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

检索特定的列

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 |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+

排序

根据某个字段进行排序（关键词：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 |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+

日期计算

查看宠物多少岁，就可以使用计算日期的函数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;

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 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+

SQL中的聚合函数

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

1.count 统计个数（行数） 2.sum函数：求和 3.avg函数：求平均值 4.max、min 求最大值和最小值

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 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

sum函数

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

注意事项：

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

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

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

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

avg函数

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

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

max函数

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

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

min函数

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

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

SQL分类

DDL （数据定义语言）

数据定义语言 - Data Definition Language

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

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

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 //删除表操作

DCL （数据控制语言）

数据控制语言 – Data Control Language

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

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

DQL （数据查询问题）

数据查询语言 – Data Query Language

select

小结

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

数据库的备份与恢复

备份命令

在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

恢复命令

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

create database heima2;

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

需求：

1、创建heima8数据库。

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

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

//恢复命令
mysql ‐uroot ‐p123456 itcast</root/data/menagerie.sql
//恢复校验

多表查询

笛卡尔积

笛卡尔乘积是指在数学中，两个集合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 |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+

内连接

内连接：

语法一：

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 |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+

左外连接

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

左外连接：用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。即：虽然右表没有香蕉对应的价格，也要把他查询出来。

语法：

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

右外连接

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

即：不管左方能够找到右方价格对应的水果，都要把左方的价格显示出来。语法：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 |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+

全外连接

全外连接：左外连接和右外连接的结果合并，单会去掉重复的记录。

select * from 表1 full outer join 表2 on 条件 select * from a full outer join b on a.A_ID = b.A_ID; 但是mysql数据库不支持此语法。

关联子查询

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

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完成：

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)

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 |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+

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;

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型 (FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。