MySQL

什么是SQL
- 结构化查询语言（Structured Query Language）
SQL的作用
- 客户端使用SQL来操作服务器
数据库的分类
- DDL：Data Definition Language，数据定义语言，用来定义数据库对象，维护存储数据的结构（数据库，表）
  - 创建、删除、修改：库、表结构
- DML：Data Manipulation Language，数据操作语言，用来定义数据库记录（数据），对数据进行操作（数据表中的内容）
  - 增加、删除、修改：表记录
- DCL：Data Control Language，数据控制语言，用来定义访问权限和安全级别主要是负责权限管理（用户）
- DQL：Data Query Language，数据库查询语言，用来查询记录（数据）　
数据库的数据类型
- int：整型
- double：浮点型。例如double(5,2)表示最多5位，其中必须有2个小数，即最大值位999.99
- decimal：浮点型，在表钱方面使用该数据类型，因为不会出现精度缺失
- char：固定长度字符串类型
- varchar：可变长度字符串类型
- text：字符串类型
- blob：字节类型
- date：日期类型，格式yyyy-MM-dd
- time：时间类型，格式hh:mm:ss
- timestamp：时间戳类型

mac通过命令行进入数据库

xxx$ /usr/local/MySQL/bin/mysql -u root -p

查看有什么数据库
- ```
mysql> show databases;
```
创建数据库
- ```
mysql> create database [数据库名]
```
选择指定数据库
- ```
mysql> use [数据库名]
```
删除指定数据库
- ```
mysql> drop database [数据库名];
```

修改数据库编码

mysql> ALTER DATABASE [数据库名] CHARACTER SET [字符集编码];

修改数据库表编码

mysql> ALTER TABLE [数据库表名] CHARACTER SET [字符集编码];

修改数据库表列编码

-- 修改单个列
mysql> alter table [数据库表名] change [数据库表列名]  [数据库表列名]   [数据库表列属性] charset [字符集编码];
-- 修改全部列
mysql>  alter table [数据库表名] convert to character set utf8;

添加数据库表

--primary key是表示该键主键
--auto increment表该键自动增加值

mysql>CREATE TABLE IF NOT EXIST [数据库名] ([属性名] [属性类型] PRIMARY KEY AUTO_INCREMENT,[属性名] [属性类型],[属性名] [属性类型]);

删除数据库表
- ```
mysql> DROP TABLE test;
```
显示数据库表
- ```
mysql> SHOW TABLES;
```

修改数据库表之添加属性

mysql> ALTER TBALE [数据库表名] ADD([属性名] [属性类型],...,[属性名] [属性类型]);

修改数据库表之修改属性类型

ALTER TABLE [数据库表名] MODIFY 属性名 属性类型;

修改数据库表之删除属性

mysql> ALTER TABLE [数据库表名] DROP [属性名];

修改数据库表之修改数据库表名

mysql> ALTER TABLE [修改前的数据库表名] rename TO [修改后的数据库表名];

修改数据表中指定属性的数据

--where之前的属性是要修改的属性
--where之后的属性是用于指定符合条件的数据
mysql> update [数据库表名] set [属性名]=[属性值] where [属性值]=[属性值];

修改数据表中指定属性（范围）的数据

--where之前的属性是要修改的属性
--where之后的属性是用于指定符合条件的数据
--in之后是属性值的取值范围，是一个集合，符合其一即可
mysql> update [数据库表名] set [属性名]=[属性值] where [属性名] in ([属性值],[属性值]);

修改数据表中之判断数据是否为空，为空则赋予属性值

--属性名都为统一属性名
mysql> update [数据库表名] set [属性名]=[属性值] where [属性名] is null;

查看数据库表结构
- ```
　　mysql> DESC [数据库表名];
```
查看数据库表中所有记录
- ```
mysql> SELECT * FROM [数据库表名];
```

添加数据库表数据

--若属性名没有对应属性值插入，且允许为NULL，则自动插入NULL，否则报错
mysql> INSERT INTO [数据库表名]([属性名],[属性名]) VALUES ([属性值],[属性值]);

删除数据库
- ```
mysql> drop database [数据库名];
```
删除数据库表中全部数据
- ```
mysql> delete from [数据库表名];
```

删除数据库表中指定属性的数据

mysql> delete from [数据库表名] where [属性名] = [属性值];

DCL

一个项目创建一个用户，一个项目对应的数据库只有一个。
这个用户只能对这个数据库有权限，其他的数据库操作不了。

创建用户

mysql> create user [用户名]@[IP地址] identified by ['密码'];

给用户授权

--授予指定权限
mysql> grant select ,create,alter,delete,drop,update on [数据库名].* to [用户名]@[IP地址];

--授予全部权限
mysql> grant all on [数据库名].* to [用户名]@[IP地址];

撤销授权

--撤销指定权限
mysql> revoke select ,create,alter,delete,drop,update on [数据库名].* to [用户名]@[IP地址];

--撤销全部权限
mysql> revoke all on [数据库名].* to [用户名]@[IP地址];

查看权限

mysql> SHOW GRANTS FOR [用户名]@[IP地址];

删除用户

mysql> drop user [用户名]@[IP地址];

DQL

查询所有列
- ```
mysql> select * from [数据库表名];
```

查询指定列

mysql> select [属性名],...,[属性名] from [数据库表名];

查询指定列之去除重复记录

mysql> select distinct [属性名]  from [数据库表名];

查询之列运算

--若指定属性名无法进行运算时，则该数则为0。即无法转化的数都当作0
--任何属性值和NULL相加都为NULL
--as起别名的时候可以省略
mysql> select *,[属性名][运算类型][运算数] as [该列得出结果的列别名] from [数据库表名];

--判断属性是否为空，若为空则替换赋予查询新的属性值
mysql> select *,ifnull([属性名],[赋予查询新的属性值]) from [数据库表名];

--指定属性连接查询，属性名之间也可加字符串
mysql> select concat([属性名],[属性名]) from [数据库表名];

查询之条件查询

mysql> select [查找范围] from [数据库表名] where [条件语句];

查询之模糊查询

--%可以表示0-n个任意字符
mysql> select * from [数据库表名] where [属性名] like ['%'];

查询之排序

--默认是升序
mysql> select [查找范围] from [数据库表名] order by  [条件语句] ;
--升序
mysql> select [查找范围]  from [数据库表名] order by  [条件语句]  asc;
--降序
mysql> select [查找范围] from [数据库表名] order by  [条件语句]  desc;

查询之聚合函数

--计数
mysql> select count([属性名]) from [数据库表名];

--总和
mysql> select sum([属性名]) from [数据库表名];

--最大值
mysql> select max([属性名]) from [数据库表名];

--最小值
mysql> select min([属性名]) from [数据库表名];

--平均数
mysql> select avg([属性名]) from [数据库表名];

查询之分组查询

mysql> select [属性名] [别名],count(*) 别名 from [数据库表名] group by [属性名];

mysql> select  [属性名]  , count(*) from test where [条件语句] group by [属性名] having [条件语句];

查询指定范围的结果

mysql> select [查询范围] from [数据库表名] limit [开始位置],[查找条数] ;

对数据库进行备份

[数据库bin的位置] [用户]$ /usr/local/[数据库bin的位置]/ mysqldump -u[数据库用户名] -p[数据库密码] travelling_guideling>[存储位置]/[数据库备份文件名].sql;

对数据库进行恢复

--方法一
[数据库bin的位置] [用户]$ /usr/local/[数据库bin的位置]/ mysql -u[数据库用户名] -p[数据库密码] travelling_guideling<[存储位置]/[数据库备份文件名].sql


--方法二
--必须选择了数据库之后才能进行恢复 
mysql> source [数据库备份的位置]

约束之主键约束

--方法一（创建时）
--not null 非空约束，unique为唯一约束

mysql> CREATE TABLE [数据库表名]([属性名] [属性类型] PRIMARY KEY,[属性名] [属性类型] not null unique);

--方法二（创建时）
mysql> CREATE TABLE [数据库表名]([属性名] [属性类型],PRIMARY KEY([属性名]));

--方法三（创建后）
mysql> CREATE TABLE [数据库表名]([属性名] [属性类型]);
mysql> ALTER TABLE  [数据库表名] ADD PRIMARY KEY([属性名]);

概念模型
- 对象模型：可以双向关联，而且引用的是对象，而不是一个主键
  - 一对一
  - 一对多
  - 多对多：需要关联表
- 关系模型：（数据库中的表）：只能多方引用一方，而且引用的只是主键，而不是一整行记录

外键约束

--方法一（创建时）
CREATE TABLE [数据库表名] ([属性名] [属性值],[对应外键属性名] [属性值] )  CONSTRAINT [外键关系名] FOREIGN KEY([对应外键属性名]) REFERENCES [外键表]([外键表的主键])

--方法二（创建后）
ALTER TABLE [数据库表名] ADD CONSTRAINT [外键关系名] FOREIGN KEY([对应外键属性名]) REFERENCES [外键表]([外键表的主键])

合并结果集
- 什么是？
  - 要求被合并的表中，列的类型和列数相同
- UNION：去除重复行
- UNION ALL：不去除重复行

连接查询

分类

内连接

--通过查询条件，外键。去除笛卡尔积
--方法一
SELECT [查询范围--属性]
FROM [查询范围--表] INNER JOIN  [查询范围--表]
WHERE [查询条件]

--方法二
SELECT [查询范围--属性] 
FROM [查询范围--表] ,  [查询范围--表]
WHERE [查询条件]

--方法三
SELECT [查询范围--属性]
FROM [查询范围--表] NARURAL JOIN  [查询范围--表]

外连接

左外连接

--外连接有一次一主，左外即左表为主
--左表所有的记录无论满足不满足条件，都打印出来
--当不满足条件时，右表使用NULL来补位
--IFNULL([属性名],[代替字符串]);
SELECT [查询范围--属性]
FROM [查询范围--表] LEFT OUTER JOIN  [查询范围--表]
ON [查询条件]

右外连接

--
SELECT [查询范围--属性]
FROM [查询范围--表] RIGHT OUTER JOIN  [查询范围--表]
ON [查询条件]

全外连接（MySQL不支持）

--MySQL不支持
SELECT [查询范围--属性]
FROM [查询范围--表] FULL OUTER JOIN  [查询范围--表]
ON [查询条件]

--通过获取左外连接、右外连接、再合并结果集
--左右外连接的[查询范围--属性]必须一样的情况下


SELECT [查询范围--属性]
FROM [查询范围--表] LEFT OUTER JOIN  [查询范围--表]
ON [查询条件]
UNION
SELECT [查询范围--属性]
FROM [查询范围--表] RIGHT OUTER JOIN  [查询范围--表]
ON [查询条件]

自然连接（看内连接方法三）

子查询

出现的位置
- where后作条件存在
- from后作表存在（多行多列）

--单行单列
SELECT * FROM [数据表名] [数据表名别名]  WHERE 列1  [=、>、<、>=、<=、!=]  (SELECT [列名] FROM [数据表名] [数据表名别名]  WHERE 条件) 

--多行多列
SELECT * FROM [数据表名] [数据表名别名]   WHERE 列1  [IN、ALL、ANY]  (SELECT [列名] FROM [数据表名] [数据表名别名] WHERE 条件) 

--单行多列
SELECT * FROM [数据表名] [数据表名别名]   WHERE 1  [列1、列2] IN  (SELECT  [列名]  , [列名]   FROM [数据表名] [数据表名别名] WHERE 条件) ) 

--多行多列
SELECT * FROM [数据表名]  [数据表名别名]   ,(SELECT ...)  [数据表名别名]  WHERE 条件