数据库基础汇总

• 数据库基础汇总
  • 一、sql简介
  • 二、数据类型
  • 三、约束
  • 四、sql语句
    • 1、创建
    • 2、插入/添加
    • 3、更新/删除
    • 4、查询（重点）
    • 多表查询
    • 5、存储过程
    • 6、事务
    • 7、视图
    • 8、日志：set global general_log = 1默认为假（不保留日志）

一、sql简介

sql(Structured Query Language)：结构化查询语句，常用于关系型数据库，语句不区分大小写

• 综合统一

SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求。

• 高度非过程化

非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”。

• 面向集合的操作方式

SQL语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。例如查询所有平均成绩在80分以上的学生姓名，用户必须说明完成该请求的具体处理过程，即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。

• 以同一种语法结构提供两种使用方式

SQL语言既是自含式语言，又是嵌入式语言。

二、数据类型

完整数据类型：https://www.runoob.com/mysql/mysql-data-types.html
常用数据类型：

• 整数：int(-2^31~231/2^32(无符号))
• 浮点数：float、double、decimal(M,D):M表示共存位数，D表示小数位数
• 字符串：char(0_{255bytes)、varchar(0}65535bytes),char(n)/varchar(n)表示字符的个数，一个中文/英文/数字均占用一个字符
• 日期：datetime(1000-01-01 00:00:00~9999-12-31 23:59:59):YYYY-MM-DD HH:MM:SS
             date(1000-01-01~9999-12-31):YYYY-MM-DD

三、约束

用于限制加入表的数据的类型,不同列之间用,隔开

• NOT NULL:非空，列不接受NULL值Id_O int NOT NULL注意：空串""并不为NULL
• UNIQUE:唯一，列不接受重复值UNIQUE (Id_P)，也是索引类型
• PRIMARY KEY:主键，列唯一标识数据库中每条信息PRIMARY KEY (Id_P)，也是索引类型
• FOREIGN KEY：外键，一个表的外键指向另一个表的主键FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
• Check：约束，列只允许加入特定的值CHECK (Id_P>0)
• DEFAULT:默认，无值插入时将自动插入默认值OrderDate date DEFAULT GETDATE()

四、sql语句

1、创建

• 创建数据库：CREATE DATEBASE [if not exists] datebase_name
• 创建表：

DROP TABLE IF EXISTS userinfo;
--用于避免数据库中存在相同表名而造成的覆盖 
CREATE TABLE userinfo(    
    id_P int unsigned NOT NULL AUTO_INCREMENT,
    --AUTO_INCREMENT只允许被用于一列中且必须被索引（如被加入主键）
    name varchar(20) NOT NULL,
    height decimal(4,1) NOT NULL,
    birth_date datetime,
    --为了后面数据处理函数的说明，类型设置为datetime
    sexuality varchar(6),
    id_nationality int unsigned,
    PRIMARY KEY (id_p),
    foreign key(id_nationality) references userinfo(id_n) 
);
--后面用法均以此表为例

• 数据库基本操作

show databases;
--显示全部数据库
select database();
--查看当前使用的数据库
use database_name;
--使用数据库

• 表基本操作

show tables;
--显示数据库中所有表
desc table_name;
--查看表结构

2、插入/添加

• 插入完整的行

  • 不指定列名：

  insert into userinfo values(
      default,'jack',168.0
  );
  

  • 指定列名(推荐)：能够避免表结构变化导致的代码不可用

  insert into userinfo (id_p,name,height)values(
      /*当满足以下情形之一者可省略列名：
        (1)该列允许NULL值
        (2)该列有默认值*/
      default,'jack',168.0
  );
  

• 插入多行

insert into userinfo (id_p,name,height) values(
    default,'jack',168.0
),
--单个数据行用()包裹 多个数据行间用，隔开
(
    default,'rose',165.5
);

• 将查询结果作为数据插入

insert into userinfo (
    id_p,
    name,
    height) 
    select id_p,name,height from userinfo_A
    --在原values的位置直接用查询语句替换    
);

3、更新/删除

• 对数据进行操作

  • 更新

    update userinfo set name='michael'，height = 163.0 where id_p = 3;
    --多个列名间用，隔开
    

  • 删除

    --尽量避免删除时不带where子句
    delete from userinfo where id_p = 4;
    --删除单行数据
    delete from userinfo where id_p = 3 or id_p = 4;
    --删除多行数据，存在多种实现方法，具体参考where子句的用法
    delete from userinfo; 
    /*delete无法删除表，只能删除表内所有行
    如确需删除表内所有数据，使用以下语句效率更高（实质是通过删除原表再创建新表实现）*/
    truncate table userinfo;
    

• 对表进行操作

  • 更新

  alter table userinfo add age int not null;
  --为userinfo表添加age列，此时age默认值为0
  alter table userinfo add foreign key(id_nationality)
  references user_nationality(id_n); 
  
  alter table userinfo drop column age;
  --删除age列
  

  • 删除

  drop table userinfo;
  --删除整张表
  

4、查询（重点）

• select基本用法

select id_p，name from userinfo;
--多个列之间用，隔开
select userinfo.id_p from userinfo;
--使用限定的表名
select * from userinfo;
--检索所有列
select id_p as 身份证,name as 姓名 from userinfo;
--重命名id_p,name为身份证和姓名，其中as可省略
select distinct height,name from userinfo;
--去重，distinct作用于其后的所有列，即除非同一行中所有列均相同，否则所有行都会被检索出来
select distinct id_p,name from userinfo limit 5;
--显示前五行
select distinct id_p,name from userinfo limit 5,5;
--检索出来的行从0开始，因此结果为显示6~10行

• 排序检索数据

select name from userinfo order by name;
--按数字从小到大或字母顺序输出检索结果（默认升序），order by后一般接需要显示的列
select name,height from userinfo order by name,height;
--仅当多个行的name值相同时按height排序
select id_p,name,height from userinfo order by height desc;
--按身高降序进行排列
select id_p,name,height from userinfo order by height desc,name;
--按身高降序进行排列，当身高相同时按姓名升序进行排列

• 过滤数据

--当where子句与order by子句并列时，where子句应放于前，否则会报错
select name,height from userinfo where id_p = 5;
--显示id_p列值为5的包含name，height列的行值
select name,height from userinfo where height <= 170;
/*where子句条件操作符：
=,<>,!=,<,<=,>=,>,between(两值之间，包含端点)*/
select name,height from userinfo where height between 160 and 170;
--查找身高在160~170之间包括160和170的行值
select name,height from userinfo where height is null;
--NULL值不代表空串或空格，因此只能通过指定来进行查询（空值不属于任何值）
select name,height from userinfo where height <= 170 and id_p >= 5;
/*where子句逻辑操作符：
and:当且仅当and左右两侧条件均满足时为真
or:两侧条件任意满足其一为真
not:当且仅当不满足条件为真*/
select name,height from userinfo where height not in (170,175) and (id_p <= 3 or id_p >= 5;
/*当任意一侧包含多个逻辑运算符时最好使用()进行分组，避免因逻辑运算次序造成结果错误
in:相当于or，只包含端点值，注意与between进行区分

• 模糊查询

/*使用模糊查询时，必须使用like谓词
不要过度使用通配符，使用时应当注意其位于搜索词的位置
应当尽量避免将通配符放于搜索词首，此时搜索效率最低*/
select name,height from userinfo where name like 'jack';
--此时结果为空，因为like后跟的搜索词在列值中出现时不会被找到，这也是与正则表达式REGEXP的主要区别
select name,height from userinfo where name like 'jack%';
--检索任何姓名以jack为开头的非空数据，%能匹配除NULL以外的任意位数据
select name,height from userinfo where name like 'jack___';
--检索姓名以jac为开头且包含任意非空后三位的数据，下划线_只能匹配除NULL以外的单个字符

• MySQL正则表达式：仅支持多数正则表达式实现的一个很小的子集

/*使用正则表达式进行查询时，必须包含REGEXP谓词
无论REGEXP作用列的数据类型为何，其后必须用''*/
select name,height from userinfo where name REGEXP 'jack';
--此时结果显示姓名为jack的数据行
select name,height from userinfo where name REGEXP 'jack.';
--检索任何姓名以jack为开头的非空数据，.能匹配除NULL以外的任意位数据
select name,height from userinfo where name REGEXP 'jack|mary';
--检索任意包含jack or mary的数据，相当于like '%jack%' or '%mary%'
select * from userinfo where age REGEXP '1[0-9]';
--检索年龄为10~19的数据,若为'1[789]'则表示检索年龄为17或18或19的数据
select * from userinfo where height REGEXP '\\.'
--检索包含特殊字符.的数据，检索特殊字符前需用'\\'进行转义
select * from userinfo where age REGEXP '[[:digit:]]{2}'
--检索数字位为2的数据

• 结合数据处理函数

/*文本处理函数：
Left（）：返回串左边的值
Right（）：返回串右边的值
Length（）：返回串的长度
Locate（）：找出串的一个子串
Upper（）：将串转换为大写
Lower（）：将串转换为小写
Trim（）：去除串两边的空格
LTrim（）：去除串左边的空格
RTrim（）：去除串右边的空格
SubString（）：返回子串的字符
Soundex（）：根据发音而不是字符返回串的SOUNDEX值
*/
select name,upper(name) as name_upper from userinfo order by name;
--将大写后的姓名列重命名后输出
select name,height from userinfo where date(birth_date) = '2000-01-02';
--将出生日期类型转换为date类型
/*数值处理函数：ads()绝对值 rand()随机数等，使用较少，了解即可*/

• 汇总数据：通过聚合函数

聚集函数（忽略列值为NULL的行）：对象为列
AVG()：返回某列的平均值
COUNT()：返回某列的行数，特殊：COUNT(*)将对表中所有行计数，不忽略NULL值
MAX()：返回某列的最大值
MIN()：返回某列的最小值
SUM()：返回某列之和

select avg(height) as avg_height from userinfo;
--获得身高的平均值
select count(name)  as num_name from userinfo;
--获得姓名数
select max(height)  as max_height from userinfo;
--获得身高的最大值
select min(age)  as min_age from userinfo;
--获得年龄的最小值
select sum(age)  as sum_age from userinfo;
--计算年龄总和
select avg(height) as avg_height,
       count(name)  as num_name,
       max(height)  as max_height,
       min(age)  as min_age,
       sum(age)  as sum_age
       from userinfo;
--组合使用聚集函数

• 数据分组
  GROUP BY:
  • 可以包含任意数量的列，进而对分组提供更细致的控制
  • 对象必须是列
  • SELECT语句中的每个列都要在GROUP BY中给出
  • 将NULL值单独分为一组
  • 出现在WHERE子句后，ORDER BY子句之前

select sexuality,count(*) as total from userinfo group by sexuality
--检索不同性别的数量
/*HAVING:过滤分组
HAVING支持所有WHERE操作符
与WHERE的区别：
WHERE过滤指定的是行而不是分组
WHERE在分组前进行过滤，GROUP BY在分组后进行过滤*/
select sexuality,count(*) from userinfo
group by sexuality
having count(*) >= 4
order by sexuality;
--分组并排序,只检索数量大于等于4的性别

多表查询

相关概念：

关系表：不同表通过某些常用的值（关系）相关联
外键：某个表中的一列，其本身也是另外一张表的主键
联结：一种机制，用于关联表，仅存在于查询的执行中
可伸缩性：能够适用不断增加的工作量而不失败，称为可伸缩性好
笛卡尔积：由没有联结关系的表返回的结果称为笛卡尔积(叉联结)，行数为两表行数相乘

注意：应当保证所有联结都包含WHERE子句，否则将返回比想要数据多得多的数据*

--新建关系表国籍
create table user_nationality(
    id_n int NOT NULL,
    location varchar(30),
    primary key(id_n)       
)

• 联结查询

  • 基本用法：

  select name,height,nationality from user_nationality,userinfo 
  where userinfo.id_nationality = user_nationality.id_n
  order by nationality;
  --输出各用户的姓名身高和国籍
  

  • 内联结：等值联结，基于两个表之间的相等测试

  select name,height,nationality 
  from user_nationality inner join userinfo 
  on userinfo.id_nationality = user_nationality.id_n
  order by nationality;
  --注意，使用INNER JOIN指定时，子句用特定的ON子句而不是WHERE
  select name,height,nationality 
  from user_nationality un inner join userinfo u
  on un.id_n = u.id_nationality
  order by nationality;
  --通过重命名1)缩短表名2)多次使用相同表
  

  • 自联结：两张表相同

  select name,height 
  from userinfo as u1, userinfo as u2
  where u1.id_p = u2.id_p and name = 'jack';
  

  • 联结多个表：通过一张表分别与不同表存在外键实现，性能随表的数量而下降

  select name,height,nationality,x 
  from user_nationality,userinfo,user_x 
  where userinfo.id_nationality = user_nationality.id_n
  and userinfo.id_nationality = user_x.id_x
  order by nationality;
  

  • 外部联结：分为LEFT/RIGHT (OUTER) JOIN
    用于指定包含JOIN左侧/右侧表的所有行

  select name,height,nationality 
  from user_nationality un right outer join userinfo u
  on un.id_n= u.id_nationality
  order by nationality;
  --指定右联结
  

• 子查询：嵌套查询
  功能：

  • 利用子查询进行过滤，常与IN结合使用
  • 作为计算字段

select name from userinfo where height > (select avg(height) from userinfo)
--作为计算字段，检索身高高于平均的人名
select name,height from userinfo 
where id_nationality  
in (select id_n from user_nationality where nationality = 'AMERICA')
--作为过滤手段

• 索引

  MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
  拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。与此同时，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

  • 创建索引

  CREATE INDEX indexName ON table_name (column_name)
  --直接创建
  ALTER table tableName ADD INDEX indexName(columnName)
  --更改表结构
  CREATE TABLE mytable(
        ID INT NOT NULL,
        username VARCHAR(16) NOT NULL,
        INDEX [indexName] (username(length))
  );
  --创建时声明  
  

  • 删除索引

  DROP INDEX [indexName] ON mytable;
  --直接删除
  ALTER TABLE testalter_tbl ADD INDEX (c);
  --更改表结构
  

  • 显示索引

  SHOW INDEX FROM table_name; \G
  

5、存储过程

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程在思想上很简单，就是数据库 SQL 语言层面的代码封装与重用

• 创建存储过程

delimiter //
--声明//作为； 目的：避免存储过程体内的；不被识别
create procudure example_1()
/*声明变量
out a1 type
out a2 type
out a3 type
*/
begin
    declare i int;
    set i = 0;
    while i < 500 do
        insert into list_name values(null,i);
        set i=i+1;
    end while
    select * from list_name;
end //
delimiter ;
--恢复；作为结束分隔符

• 删除存储过程

drop procudure procudure_name;
drop procudure if exists procudure_name;
--避免错误的更好的方法

• 使用存储过程

call procudure_name(@variable1,@variable2,@variable3)

6、事务

用于维护数据库的完整性即保证成批的Mysql操作要么完全执行，要么完全不执行。值得注意的是，目前MyISAM引擎不支持事务处理，而InnoDB支持。

基本概念：
事务（transaction）：指一组sql语句
回退（rollback）：指撤销指定sql语句的过程，只能撤销insert/update/delete语句
提交（commit）：将未存储的sql语句结果写入数据表
保留点（savepoint）：事务过程中的占位符，可回退到保留点设置的位置

特点（ACID）：
原子性（ATOMICITY）：事务中的每个操作看做是基本的逻辑单元，对于每个逻辑单元来说，其结果只有T/F
一致性（Consistency）：事务的运行不改变数据的一致性
隔离性（Isolation）：事务的中间状态对其他事务不可见
持久性（Durability）：事务的提交是永久的

• 回退（rollback）

select * from list_name;
start transaction;
delete from list_name;
select * from list_name;
rollback;
select * from list_name;
--rollback只能在事务内部使用，使用后回退到开始事务后,事务自动关闭

• 提交（commit）

start transaction;
delete from list_name where id = 1;
delete from list_name where id = 2;
commit;
--只有在两条delete语句都执行成功后才将结果提交至数据库表，提交后事务自动关闭

• 保留点（savepoint）

savepoint savepoint_name;
--创建保留点（在事务内部）
rollback to savepoint_name;
--回退至指定保留点
/*保留点越多越好，这样使用起来更灵活
保留点将在执行提交/回退后自动释放，也可通过release savepoint主动释放*/

• 更改默认的提交行为：set autocommit = 0,默认为真（自动提交）

7、视图

将查询结果封装成视图，进而简化及重用sql语句

使用视图：

create view v_name as select a from list_name where id = 1;
--创建视图，为便于区分视图与表命名最好以v开头
drop view v_name;
--删除视图
create or replace view v_name;
--避免重复
select b from v_name;
--查看视图中的列
/*尽管视图本身是一张或多张表的部分内容，但其一般用于数据检索而不是更新
且更新对于视图定义有诸多限制，包括不得使用分组、联结、子查询、聚集函数等，
因此非必要条件下，不推荐更新视图*/

8、日志：set global general_log = 1默认为假（不保留日志）