先简单介绍下数据库
主要分 2 大类：
-
关系型数据库
MySQL、Oracle、SQL Server
-
非关系型数据库
Redis 内存数据库、MongoDB 文档数据库
SQL 语句支持三种注释：
# 注释
-- 注释
/* 注释 */
SQL 语句不区分大小写，但是库名、表名、字段名、值这些区不区分具体看 DBMS 及其配置。
SQL 语句要以分号结尾，如果只有一句，可写可不写。
SQL 语言分为 4 个部分： DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）
SQL 语句中可以用的一些小点：
\G 格式化输入
\s 查看服务器端信息
\c 结束命令输入操作
\q 退出当前的 sql 命令行模式
\h 查看帮助
以下语句都是基于 MySQL 数据库：
命令行中登录 MySQL ：
mysql -h localhost -u root -p
-h 服务器的地址， 在本地上可以省略
-u 登录的用户名， root 是权限最高的一个用户
-p (小写) 回车后输入密码
-P (大写) 端口号
MySQL 中的数据类型
-
字符串类型： 内容应该用 引号引起来。
-
定长串 char
每次都分配固定的长度用于存储数据。
1~255 个字符长度，不指定就是 char(1)
-
变长串 varchar
每次根据实际内容，分配用于存储数据的空间。只存储必要内容。
最大存储 255 个字节
-
MySQL 处理定长串 比 处理变长串，性能要好。
在进行存储和检索式，MySQL 会保留 varchar 末尾的空格，删除 char 末尾的空格。
在执行 update 时，行可能会比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作： MyISAM 会将行拆分成不同的片段存储， InnoDB 是分裂页来使行放进页内。
-
变长文本类型 Text
最大长度 为 64K 的变长文本。
-
-
数值类型
-
整型
tinyint ： 8 位； 1 字节
smallint ： 16 位； 2 字节
mediumint ： 24 位；3 字节
int ： 32 位； 4 字节
bigint ： 64位； 8 字节
-
浮点型
float : 单精度浮点型
double : 双精度浮点型
decimal : 高精度小数类型 。
以上三种都可以指定列宽，比如： decimal(5, 2) : 表示一共有 5 位数字， 小数部分占 2 位。
CPU 原生支持浮点运算，但是不支持 decimal 类型的计算，decimal 计算比浮点类型需要更高的代价。
-
-
日期
-
date ， 表示日期：YYYY-MM-DD
-
time ， 表示时间： HH：MM：SS
-
datetime： date 和 time 的结合。
占 8 个字节， 保存 1000 年到 9999 年的日期和时间。
与时区无关。
标准的 ANSI 定义的日期和时间表示法。
-
timestamp： 和 datetime 一样，只不过表示范围要小
和 UNIX 的时间戳差不多。
与时区有关，保存从 1970-1-1（格林威治时间）以来的秒数。 占 4 个字节。保存 1970 年到 2038 年的时间。
timestamp 比 datetime 空间效率要高
-
year： 4 位数字表示： 1901 ~~~ 2155 年。
-
-
二进制数据类型
可以存储 图像、视频、音频等等。
了解就好，开发的时候，不会用到这种数据类型的。。。一般都是记录他们的存储路径。
字段的约束：
- unsigned 无符号
- 默认无符号 int 为 int(11) , 有符号的为 int(10)。
- zerofill : 补齐长度，前面的用 0 填充， 比如： 0001
- not null
- default 设置默认值
- primary key
- auto_increment 自增属性，默认增长为 1
- unique 唯一索引。 增加查询速度，但是插入和更新的速度会减低。
# 在当前用户下，查看所有的库：
show databases;
# 创建库
create database 库名 default charset=utf8
# 或者先判断 这个库名 存不存在
create database if not exists 库名 default charset=utf8;
# 使用某个数据库：
use 库名;
# 查看数据库里，所有的数据表：
show tables;
# 删除数据库 （慎用了。。。。） 在 磁盘 中进行删除。。。
drop database 库名;
进入到某个具体的数据库里面之后：
# 创建表
# create table 表名（.....） engine=innodb default charset=utf8';'
create table user(
id int not null primary key auto_increment,
name varchar(25) not null,
age tinyint default 1, -- 默认值为 1
sex enum('男','女')
)engine=innodb default charset=utf8;
# 或者 先判断这个表名存不存在。。。
create table if not exists user(
id int not null primary key auto_increment,
name varchar(25) not null,
age tinyint default 1, -- 默认值为 1
sex enum('男','女')
)engine=innodb default charset=utf8;
# 查看表结构：
# desc 表名;
# 删除表：
# drop table 表名;
# ========= 修改表的结构 ==========
# 添加列
# alter table 表名 add 列名 约束条件;
alter table user add phone varchar(11) not null;
# 指定位置添加
alter table user add phone varchar(11) not null after age;
alter table user add phone varchar(11) not null first;
# 删除列
# alter table 表名 drop column 列名;
alter table user drop column phone;
# 修改列的信息：
# alter table 表名 change | modify 被修改的列信息
# modify 不能修改列名
alter table user modify age tinyint not null default 12;
# change 可以修改列名
alter table user change phone telphone varchar(11);
# 修改表名
# alter table 原表名 rename as 新表名;
增加
# 普通的插入。 插入全部数据的话，就可以省略中间那个括号， 也可以指定字段插入， 也可以添加一行。
insert into user (id, name, age, sex) values
(1, "如也", 18, "男"),
(2, "iris", 19, "女");
# 插入检索出来的数据
insert into mytable (col1, col2) values
select name, age from user;
# 将一个表的内容，插入到新表中
create table newtable as
select * from user;
删除
# 删除指定数据（行）
delete from user where id = 1;
# 删除所有行，清空表
truncate table user;
更新
update user set col1=val1, col2=val2 where id = 1;
查询
select 字段列表 | * from 表名
[ where 搜索条件 ]
[ group by 分组字段 [ having 分组条件 ] ]
[ order by 排序字段 排序规则 ]
[ limit 分页参数 ]
# DISTINCT ： 作用于所有列， 相同值只会出现一次
select distinct age, sex from user;
# LIMIT : 限制返回的行数。
# 第一个参数： 起始行，从 0 开始；
# 第二个参数： 返回的总行数。
select * from user limit 5; # 返回前 5 行
select * from user limit 0, 5; # 返回前 5 行
select * from user limit 2, 3; # 返回 3 ~~ 5 行
# ORDER BY 排序。 可以按照多个列进行排序
# ASC 升序， 默认的
# DESC 降序
select * from user order by age desc, id asc;
过滤操作： where
如果直接在数据库里查太多数据直接传给服务器的话，有时候会传输多余的数据，浪费网络带宽。
在 数据库 里先对 数据 做好处理就很有必要了。
可用于 where 子句的操作符：
-
操作符 说明 = < > != 或者 <> <= 或者 !> >= 或者 !< between 在 2 个值之间 is null 是 null 值 and 和 or 操作符， 用于连接多个过滤条件， 优先处理 and。
in 操作符，用于匹配一组值， 也可以接一个 select 子句，用于匹配子查询得到的结果。
not 操作符，用于否定一个条件。
使用 LIKE ， 进行通配符匹配。
-
% ： 匹配 >= 0 个任意字符。
-
_ ： 匹配 1 个任意字符。
-
[] : 匹配 【】 集合内的字符。
比如： [ab] 匹配字符 a， 或者字符 b。 可以用脱字符 ^ 表示否定。
通配符位于开头，匹配进行会非常慢。。。
# 不以 张 和 李 开头的任意文本
select * from user where name like '[^张李]%';
计算字段（给列名取别名）
在数据库服务器上，完成数据的转换和格式化，要比在客户端上完成要轻松的多。
并且转换 和 格式化后的数量如果更少的话，也可以减少网络的通信量。
# AS 取别名
select col1 * col2 as alias from user; # col1 * col2 的内容单独为 1 列， 列名为 alias
# CONCAT() 连接 2 个字段（列）， TRIM() 可以去除首尾空格。
select concat(col1, col2) as concat_col from user; # concat_col 的内容是 col1col2
select concat(col1, '(', col2, ')') as concat_col from user; # concat_col 的内容是 col1(col2)
分组： GROUP BY：
group by 列名1 —> 列名1 里相同的内容会被分到一组中。
分好组后， 还可以用 HAVING 对组的数据再进行过滤。
WHERE 过滤行， HAVING 过滤分组，行过滤优先于分组过滤。
除了汇总字段外， select 后的每一个字段，在 group by 后都要给出，不然不知道怎么显示啊。。。
# 统计 三年级及以上年级 的 每个班的人数，并且班级人数大于等于 15 人。
select classid, count(*) as num from mytable
where gradeid >= 3
group by classid having num >= 15;
函数
各个 DBMS 之间，函数是不相同的，这里只介绍 MySQL 中的函数。
聚合函数（汇总函数）：
- AVG( ) 返回某列的平均值， 会忽略 NULL 行。
- COUNT( ) 返回某列的行数
- MAX( ) 返回某列的最大值
- MIN( ) 返回某列的最小值
- SUM( ) 返回某列里面所有值的和
文本处理函数
- LEFT( ) 左边的字符
- RIGHT( ) 右边的字符
- LOWER( )
- UPPER( )
- LTRIM( ) 去掉左边的空格
- RTRIM( ) 去掉右边的空格
- LENGTH( )
- SOUNDEX( ) 将一个字符串 转换成，描述其语音表示的 字母数字模式。
日期和时间的处理函数：
不经常用，一个比较常见的就是 NOW( )， 返回当前的日期和时间。
数值处理的函数:
- SIN( )
- COS( )
- TAN( )
- ABS( ) 绝对值
- SQRT( ) 平方根
- MOD( ) 取余
- EXP( ) 指数
- PI( ) 圆周率
- RAND( ) 随机数
子查询
子查询里，只能返回一个字段的数据。 多的话是用在 in 操作符里的。
# 检索客户的订单数量
select cust_name,
(select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers order by cust_name;
连接
用于连接多个表，使用 JOIN 关键字，条件语句用 ON， 而不用 WHERE。
可以替换子查询，比子查询的性能要好一些。。。
- 内连接 （等值连接）
- 自连接 （同表的内连接）
- 自然连接 （同名列，通过等值测试，连接起来）
- 外连接 （保留没有关联的那些行）
- 左外连接 （保留 左表 没有关联的行）
- 右外连接 （保留 右表 没有关联的行）
- 全外连接 （都保留）
# 内连接，等值连接， 用 INNER JOIN 关键字。 用普通查询也可以做到。。。
select A.value, B.value from tablea as A inner join tableb as b
on A.key = b.key;
select A.value, B.value from tablea as A, tableb as B where A.key = B.key;
# 自连接，可以看成是 内连接的一种，只不过连接的表是自身。 用子查询也可以做到。。。
# 查找与 Jim 处在同一个部门的所有员工的姓名。
select e1.name from employee as e1 inner join employee as e2
on e1.department = e2.department and e2.name = "Jim"
select name from employee
where department = (select department from employee where name = "Jim");
# 自然连接： 自动连接所有的同名列。 内连接是提供连接的列。
select A.value, B.value from tablea as A natural join tableb as B;
# 外连接
# 检索所有顾客的订单信息，包括还没有订单的顾客。 左外连接
select customers.cust_id, customer.cust_name, orders.order_id
from customers left outer join orders
on customers.cust_id = orders.cust_id;
组合查询
使用 UNION 关键字。
组合 每个查询得到的结果， 每个查询出来必须包含相同的列、表达式、聚合函数， 这样才有可能组合到一起。
默认会去除相同的行，如果不希望去除，使用 UNION ALL 关键字。
只能包含一个 ORDER BY 子句，并且要在最后出现。
select col from mytable where col = 1
union
select col from mytable where col = 2;
视图
操作视图，就跟操作普通的表是一样的。
只不过，视图是虚拟的表，只使用了实际表的一部分数据。
本身不包含数据，不能对其进行索引操作。
可以通过给用户访问视图的权限，来保证数据的安全性。
create view myview as
select concat(col1, col2) as concat_col, col3*col4 as compute_col
from mytable where col5 = val;
权限管理
MySQL 的账户信息保存在 mysql 这个数据库中的 user 表里。
# 创建账户
# grant 授权的操作 on 授权的库.授权的表 to 账户@'登录地址' identified by '密码';
grant select,insert on learn.* to zhangsan@'%' identified by '123456';
# 修改账户名
rename user 原账户名 to 新账户名;
# 删除用户
drop user 账户名;
# 查看权限
show grants for 账户名;
# 修改密码， 需要用 Password 函数进行加密。
set password for 账户名 = Password('新密码');
用 GRANT 关键字授予权限， 用 REVOKE 关键字删除权限。
权限可以作用到的层次：
- 整个服务器。 使用 grant all ， 或者 revoke all。
- 整个数据库。 使用 ON database.*
- 特定的表。 使用 ON 库名.表名
- 特定的列。
- 特定的存储过程。
事务管理
一些基本的专业术语：
-
事务 transaction ： 指一组 SQL 语句。
-
回退 rollback ： 指撤销指定的 SQL 语句的过程。
-
提交 commit ： 指将未存储的 SQL 语句结果写入到数据库表中。
-
保留点 savepoint ：
指事务处理中设置的临时占位符（placeholder）， 可以 rollback 到该保留点上。
如果没有设置保留点，rollback 就会退回到 start transaction 语句处。
不能对 create 和 drop 语句 rollback； 不能对 select 语句进行 rollback ，这样也没有用。
MySQL 中的事务默认是隐士提交，也就是每一条 SQL 语句就是一个事务。
当出现 start transaction 语句时，会关闭隐士提交。
当 commit 或 rollback 语句执行后，事务就是自动关闭，恢复到原来的隐士提交。
设置 autocommit 为 0 可以取消自动提交，这个 autocommit 是对每个连接而言的，不是对服务器。
InnoDB 和 MyISAM 存储引擎的比较
DBMS 把对数据的存储和提取这些操作都封装到了 存储引擎 的模块里。 我们比较常见的就是 InnoDB 和 MyISAM 存储引擎。
-
InnoDB
是 MySQL 默认的存储类型。 是支持事务的，支持外键。
支持在线热备份，其他存储引擎都不支持的。
支持行级锁。
有 4 个标准的隔离级别。 默认级别是 可重复读（Repeatable read），在该级别下，通过多版本并发控制（MVCC） + Next-Key Locking 防止幻影读。
在索引中保存了数据，避免了直接对磁盘操作，对查询性能比较友好。
内部做了很多很多的优化。
如果需要大量的 insert 和 update 操作时， InnoDB 会更友好。
-
MyISAM
数据以紧密的格式存储。
支持压缩表和空间数据索引
只支持表级锁。 在读取时对表加共享锁，在写入时对表加排它锁。 在读的时候，也可以往里插数据，这个过程称为 并发插入（Concurrent insert）
数据崩溃后损坏的概率是很高的，修复的过程也会非常慢。。。
小型应用，有大量的 select 语句时，可以选择 MyISAM 存储引擎。
京公网安备 11010502036488号