先简单介绍下数据库

主要分 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 存储引擎。

存储过程、游标、触发器： 之后有时间再来学习整理下。

参考连接： https://cyc2018.github.io/CS-Notes/#/notes/SQL