面试篇：mysql基础要点巩固

感觉数据库基础不是很扎实，所以来复习复习。

一、基础增删改查部分

1.1 创建数据库

#创建名为['study']数据库：
create database study
#删除数据库 这个真实开发估计用不到吧？什么你想删库跑路??
drop database stydy;

1.2 创建数据表

comment：表字段注释
auto_increment：自动增长

#创建数据表 auto_increment：自动增长,一般用于主键自动增长。
CREATE TABLE person (
	 id INT auto_increment primary key,
	`name` VARCHAR ( 25 ) NOT NULL comment '学生姓名',
	score FLOAT NOT NULL comment '姓名',
	stu_num VARCHAR ( 10 ) NOT NULL comment '学号',
	sex VARCHAR ( 10 ) NOT NULL comment '性别',
);
# 查看表结构
desc person
# 删除数据表
drop table person;
# 新增列
alter table person add sex VARCHAR(10);
# 删除列
alter table person drop sex ;
# 修改列为int类型
alter table person modify column stu_num int

1.2.1 mysql六大约束：

• NOT NULL ：非空，用于保证该字段的值不能为空。
• DEFAULT：默认值，用于保证该字段有默认值。
• PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
• UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。
• CHECK：检查约束（MySql不支持），检查字段的值是否为指定的值。
• FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

主键（primary key）与唯一(unique)的区别：

约束名称	保证唯一性	是否允许为空	一个表中可以有多少个
主键	√	×	最多一个可以没有
唯一	√	√	可以有多个

1.2.2 表级约束

表级约束再创建表字段的最下面 关键词：constraint

CREATE TABLE person (
    id INT auto_increment ,
	`name` VARCHAR ( 25 ) NOT NULL comment '学生姓名',
	score FLOAT NOT NULL comment '姓名',
	stu_num VARCHAR ( 10 ) NOT NULL comment '学号',
	sex VARCHAR ( 10 ) NOT NULL comment '性别',
    #设置id为主键 pk为约束名（随意）
	CONSTRAINT pk PRIMARY KEY ( id ),
	#设置stu_num(学号)不可重复 uq为约束名（随意）
	CONSTRAINT uq UNIQUE ( stu_num ),
	#设置sex列只能输入男女,ck为约束名
    CONSTRAINT ck CHECK ( sex = '男' OR sex = '女' ) 
);

表级约束添加约束：

# alter table 表名称 add 约束类型（约束字段），eg:
alter table 表名称 add primary key(id);

1.2.2 列级约束

列级约束直接跟在字段后面定义就好了。

CREATE TABLE person (
	id INT auto_increment PRIMARY KEY,
	`name` VARCHAR ( 25 ) NOT NULL,
	score FLOAT NOT NULL,
	stu_num VARCHAR ( 10 ) NOT NULL UNIQUE,
    sex VARCHAR ( 10 ) NOT NULL CHECK ( sex = '男' 
     OR sex = '女' ) 
);

列级约束添加约束：（可以顺便修改下该字段的类型）

alter table 表名称 modify column 列名 列类型 约束类型

1.3 插入数据（insert）

#插入[person]表数据
insert into personvalues(0,"吴亦凡",'70');
insert into person(id,name,score) values(0,"谢霆锋",'70');
insert into person(id,name,score) values(0,"陈冠希",'45');
insert into person(id,name,score) values(0,"吴彦祖",'86');
insert into person(id,name,score) values(0,"孙悟空",'75');
insert into person(id,name,score) values(0,"猪八戒",'58');

1.4 删除操作（delete）

#删除[person]表所有数据
delete from person
#条件删除
delete from person where score<60;

1.5 修改操作（update）

#更新[person]表数据
update person set name='齐天大圣' where name ='孙悟空';

1.6 查询操作（select）

#普通查询
select *from person
#条件查询
select *from person where score>60;
# 获取总条数
select count(*) 总人数 from person where score>60;
# 获取成绩最高 
select * 
from person  ORDER BY score desc  limit 0,1;
# 获取成绩最高 （第二种方案）
select *from person where score=(select max(score) from person);
#case查询的使用
select *,
	case 
	 when score<60 then "学渣"
	 when score>60 then "混子" when score>80 
then '学霸' end 评价 from person;

1.6.1 关键词位置先后：

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

一条最基本的sql查询语句的执行顺序
from > on> join > where > group by > select > order by
(该图片来自博客：blog)

升序：
order by 升序字段 desc
降序：
order by 降序字段 asc

二、常用数据类型及属性

2.1数据类型

2.1.1 日期和时间数据类型

MySQL数据类型	含义
date	3字节，日期，格式：2014-09-18
time	3字节，时间，格式：08:42:30
datetime	8字节，日期时间，格式：2014-09-18 08:42:30
timestamp	4字节，自动存储记录修改的时间
year	1字节，年份

2.1.2 数值数据类型

MySQL数据类型	含义（有符号）
tinyint	1字节，范围（-128~127）
smallint	2字节，范围（-32768~32767）
mediumint	3字节，范围（-8388608~8388607）
int	4字节，范围（-2147483648~2147483647）
bigint	8字节，范围（±9.22*10的18次方）

2.1.3 浮点型

注意浮点类型的精度问题：点击打开精度介绍：blog
注意：默认的float类型都只能存6个数字（包括小数点前后的位数），整数超过6位就被科学计数表示，用的时候一定要考虑精度问题！

MySQL数据类型	含义（有符号）
float(m, d)	4字节，单精度浮点型，m总个数，d小数位
double(m, d)	8字节，双精度浮点型，m总个数，d小数位
decimal(m, d)	decimal是存储为字符串的浮点数

2.1.4 字符串数据类型

MySQL数据类型	含义（有符号）
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

2.2 数据类型属性

auto_increment：自动增长
default： 可以给字段一个默认值
not null: 字段不应该为null

三、 索引（index）

索引的作用可以大大优化查询速度，开发中还是挺常用的。

#给person表的字段[score]创建了名字为test的索引
create index test on person (score)

也可以在创建表的时候添加索引：

create table employees
(
	id varchar(9) not null,
	firstname varchar(15) not null,
	lastname varchar(25) not null,
	email varchar(45) not null,
	# 添加索引
    index lastname(lastname),
	primary key(id)
);

索引内容还是蛮多的，本文只是记录一下学习点，故介绍不会太多，只是方便以后深入学习。

四、 视图（views）

视图相当于一个虚拟表，是一个逻辑表，本身不包含数据，作为一个select语句保存在数据字典中。
视图跟数据库表的区别：
视图并不在数据库中以存储数据集的形式存在，可以理解成一个容器，每次引用的时候会动态生成里面的数据。
视图的优点：

• 1 用户不用关系对应的表结构关联条件和删选条件，因为他已经是一个过滤好的符合条件的结果集。
• 安全：使用视图的用户只能访问被允许查询的结果集
• 数据独立，视图创建好后，可以屏蔽掉表结构变化对程序后端的影响，修改表之后只需要更新视图即可，后端程序也不用修改了。

# 创建名为[allperson]的视图
create view allperson as select * from person;
# 删除名为[allperson]的视图
drop view allperson
#查找名为[allperson]的视图
show create view allperson
#修改名为[allperson]的视图查询条件
create or replace view allperson as select score,name from person

有下列内容之一，视图不能做DML（增删改查）操作：
　　①select子句中包含distinct
　　②select子句中包含组函数
　　③select语句中包含group by子句
　　④select语句中包含order by子句
　　⑤select语句中包含union 、union all等集合运算符
　　⑥where子句中包含相关子查询
　　⑦from子句中包含多个表
　　⑧如果视图中有计算列，则不能更新
　　⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作
　　
上述八点内容摘抄博主地址：blog
　
视图用法示例：
我们可以直接查找视图的数据，例如

#allperson为上一步创建的视图名
select * from allperson

五、连接查询

现有两个表：
女生表

男生表：

其中，女生表 的boyfriend_id为对应男生表的id。

5.1 内连接（inner join）

内链接即相当于两表做一次笛卡尔乘积，即就是把左边的表的每一行都和右边表的所有行去拼接。

select g.*,b.* from girls g inner join boys b;

可以看到内连接的结果是将左表的每一行数据都链接了右表的每一行数据。

当然这样的数据并不是我们要的，我们要进行筛选。后面加个添加 如果 查询的两个id值相同 才显示。如下：

可以看到查询出来的数据为两表满足关系的所有数据
所以，内连接就相当于查询出两表的交集。如下图：若按照上面的sql语句，下图交集部分为id。

5.2 左连接（left join）

左连接即是两表做笛卡尔乘积之后，无论左边的表满不满足条件，也要保证左表的所有数据都能显示出来。右表记录不足的地方均为NULL

如下sql使用左连接查询两个表，不管有没有男朋友，也确保她会被查询出来。

SELECT g.*,b.* from girls g left JOIN boys b  on g.boyfriend_id=b.id 
ORDER BY b.id desc;

左连接理解图：

场景一：查询出所有女生的名字以及对应男朋友的名字，若不存在则显示 单身。
这个时候我们就可以使用连接查询了。

SELECT g.NAME,CASE
		WHEN b.boyName is not null    THENb.boyName
		ELSE "单身" 
		END 男朋友 
FROM
	girls g
	LEFT JOIN boys b ON g.boyfriend_id = b.id 
ORDER BY
	b.id DESC;

查询结果：

5.3 右连接（right join）

右连接跟左连接一个道理。如果能理解左连接，那么也就清楚右连接。

通过下面的sql语句，通过右连接保证应该出现所有男生。

SELECT b.*,g.* from girls g right JOIN boys b  on g.boyfriend_id=b.id 
ORDER BY b.id desc;

右连接示意图：（右边的表数据一定完整）

5.4 交叉连接(cross join)

select *from girls cross join boys

也称笛卡儿积,笛卡儿积。关于笛卡儿积，如下图。（图片来自百度）

5.5 全链接(full join)

mysql不支持全连接，但是可以使用union或者union all 查询。其查询结果为左连接的所有行+右连接的所有行。

六、触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。可以满足我们某个时候的需求，但是并不常用。

触发器类型：

• insert型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；
• update型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
• delete型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

关于触发器的使用：这里推一个浅显易懂的博客地址，感兴趣可以看看：blog