java面试之Mysql基础一

java面试之Mysql基础一

1.概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

• mysql是开源的，成本低
• mysql支持大型数据库，可以处理上千万记录的大型数据库
• mysql支持多操作系统，支持多语言连接

DB：数据库 Database，按照数据结构来组织、存储和管理数据的仓库。
DBMS：数据库管理系统 Database Management System，数据库是通过DBMS创建和操作的容器。
基于共享文件系统的DBMS ACCESS
基于客户机-服务器的DBMS MYSQL、ORACLE
SQL：结构化查询语言，Structure Query Language，专门用来与数据库通信的语言。几乎所有DBMS都支持SQL。
DBA：数据库管理员，DataBase Administrators。

DDL ： 数据定义语言 Data Define Language。Create、Drop、Alter、Truncate
DML： 数据操纵语言 Data Manipulation Language。 insert 、update 、delete
DQL ： 数据查询语言 Data Query Language。 SELECT
DCL ： 数据控制语言 Data Control Language。Grant 、Revoke、COMMIT 、SAVEPOINT、ROLLBACK SET TRANSACTION。

不区分大小写，建议关键字大写，表名、列名小写。

超键、候选键、主键、外键

• 超键：在关系模式中，能唯一知标识元组的属性集称为超键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

SQL 约束

• NOT NULL: 约束字段的内容一定不能为NULL。
• UNIQUE: 约束字段唯一性，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。
• CHECK: 用于控制字段的值范围。

mysql默认数据库

默认数据库分类：

informance_schema

• 保存了MySQl服务所有数据库的信息。
• 具体MySQL服务有多少个数据库，各个数据库有哪些表，各个表中的字段是什么数据类型，各个表中有哪些索引，各个数据库要什么权限才能访问。

---

mysql

• 保存MySQL的权限、参数、对象和状态信息。
• 如哪些user可以访问这个数据、DB参数、插件、主从

权限表分别如下：

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

---

performance_schema

• 主要用于收集数据库服务器性能参数
• 提供进程等待的详细信息，包括锁、互斥变量、文件信息；
• 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
• 对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

test

• 测试库，默认没有数据

2.数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

char 和 varchar

char(n) ：固定长度类型，比如char(10)，当你输入"abc"三个字符时，它们占的空间还是 10 个字节，其他 7 个是空字节。
优点：效率高；缺点：占用空间；适用场景：存储密码的md5 值，固定长度。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
所以，从空间上考虑 varchar 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

Blob和text

• Blob用于存储二进制数据，而Text用于存储大字符串。
• Blob值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中字节的数值。
• text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

DATETIME和TIMESTAMP

• DATETIME 的日期范围是 1001——9999 年；TIMESTAMP 的时间范围是 1970——2038 年
• DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
• DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
• DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

3.基本命令

函数说明
运算符

#右键管理员身份进入cmd进行操作，不带;
#启动服务
net start mysql
#关闭服务
net stop mysql
#登录
mysql -h localhost -P 3306 -u root -p
#退出
exit
#查看mysql版本
mysql -V
mysql --version

#显示所有的数据库
SHOW DATABASES;
#查询在哪个库
SELECT DATABASE();
#查询数据库版本
SELECT VERSION();
#当前用户名
SELECT USER();
#服务器状态
SHOW STATUS;
#服务器配置变量
SHOW VARIABLES;
#查看数据库的存储引擎	
show variables like '%storage_engine%';
#查看数据库的字符编码
show variables like '%character_set%';
#创建一个数据库名为RUNOOB
CREATE DATABASE RUNOOB;
#如果数据库不存在则创建，存在则不创建,并设定编码集为utf8
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
#删除名为 RUNOOB 的数据库
drop database RUNOOB;
#选取了数据库 RUNOOB
use RUNOOB;

#查询常量
SELECT 100 数字;
SELECT 'lymn';
#查询表达式
SELECT 100%98 AS 结果;
SELECT 1 ^ 0;#1
SELECT 2/3;#0.667
SELECT 10 DIV 4;#2
SELECT 100+90;#190
SELECT '100'+90;#190
SELECT 'tom'+90;#90
SELECT NULL+90;#NULL
SELECT NULL = NULL;#NULL
SELECT NULL<=>NULL;#1
SELECT CONCAT('a','b','c') 字符串; #abc
SELECT CONCAT('a','b',NULL) 字符串;#NULL

#创建数据表 runoob_tbl
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=INNODB DEFAULT CHARSET=utf8;

#查看库下的表
SHOW TABLES;
SHOW TABLES FROM RUNOOB;
#查看runoob_tbl表结构
DESC runoob_tbl;
#删除数据表runoob_tbl
DROP TABLE runoob_tbl;

/*
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，
如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
*/
#向 runoob_tbl 表插入数据
INSERT INTO runoob_tbl(runoob_title, runoob_author, submission_date) VALUES("学习 MySQL", "菜鸟教程", NOW());
#插入多条数据(主键自增（PRINARY KEY AUTO_INCREMENT）第一列在增加数据的时候，可以写为0或者null)
INSERT INTO runoob_tbl VALUES(NULL,"学习 MySQL1", "菜鸟教程", NOW()),(NULL,"学习 MySQL2", "菜鸟教程", NOW()),(NULL,"学习 MySQL3", "菜鸟教程", NOW());

#返回数据表 runoob_tbl 的所有记录
SELECT * FROM runoob_tbl;
#读取 runoob_tbl 表中 runoob_title字段值为学习 MySQL的所有记录：
SELECT * FROM runoob_tbl WHERE runoob_title='学习 MySQL';#小写也能查询出结果
#WHERE子句的字符串比较是不区分大小写的。 用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的
SELECT * FROM runoob_tbl WHERE BINARY runoob_title='学习 mySQL';#查不出结果
#执行顺序select –>where –> group by–> having–>order by

#更新数据表中 runoob_id 为 3 的 runoob_title 字段值
UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
#更新 runoob_id 为 3 的runoob_title 字段值的 "C++" 替换为 "Python"(替换某个字段中的某个字符)
UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') WHERE runoob_id = 3;

/*  delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除;
	delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚;
	执行的速度上，drop>truncate>delete.
*/
#删除表中的所有数据
DELETE FROM runoob_tbl;
#删除 runoob_tbl 表中 runoob_id 为3 的记录
DELETE FROM runoob_tbl WHERE runoob_id=3;

#创建临时表
CREATE TEMPORARY TABLE SalesSummary (
    product_name VARCHAR(50) NOT NULL,
    total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
     avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
    total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO SalesSummary(product_name, total_sales, avg_unit_price, total_units_sold) VALUES('cucumber', 100.25, 90, 2);
SELECT * FROM SalesSummary;
#删除临时表
DROP TABLE SalesSummary;

#只复制表结构到新表
CREATE TABLE clone_tb2 SELECT * FROM runoob_tbl WHERE 1=2;
#完整复制表的方法
CREATE TABLE clone_tb4 SELECT * FROM runoob_tbl; 
#完整复制表的方法
CREATE TABLE clone_tb5 LIKE runoob_tbl;
INSERT INTO clone_tb5 SELECT * FROM runoob_tbl;

#runoob_tbl 表中获取 runoob_title 字段中包含My的所有记录
SELECT * FROM runoob_tbl  WHERE runoob_title LIKE '%My%';
# runoob_tbl 表数据按runoob_title字段的升序排列。
SELECT * FROM runoob_tbl ORDER BY runoob_title;
#runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
#读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
#读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

#查找数据表中 runoob_test_tbl 列是否为 NULL
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NOT NULL;
#删除以上创建表的i字段;数据表中只剩余一个字段则无法使用DROP来删除字段。
ALTER TABLE testalter_tbl DROP i;
#在表 testalter_tbl 中添加 i 字段，并定义数据类型
ALTER TABLE testalter_tbl ADD i INT;
#添加h字段到第一列
ALTER TABLE testalter_tbl ADD h INT FIRST;
#在c字段之后添加m字段
ALTER TABLE testalter_tbl ADD m INT AFTER c;
#字段 c 的类型从 CHAR(1) 改为 CHAR(10)
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
#指定字段 j 为 NOT NULL 且默认值为100
ALTER TABLE testalter_tbl  MODIFY j BIGINT NOT NULL DEFAULT 100;
#CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型
ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl CHANGE j j INT;
# ALTER 来修改m 字段的默认值
ALTER TABLE testalter_tbl ALTER m SET DEFAULT 1000;
#查看testalter_tbl表的所有字段描述
SHOW COLUMNS FROM testalter_tbl;
#删除m字段的默认值
ALTER TABLE testalter_tbl ALTER m DROP DEFAULT;
#将表 testalter_tbl 的类型修改为 MYISAM 
ALTER TABLE testalter_tbl ENGINE = MYISAM;
#查看testalter_tbl数据表类型
SHOW TABLE STATUS LIKE 'testalter_tbl';
#将数据表 testalter_tbl 重命名为 alter_tbl
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
#设置序列的开始值
ALTER TABLE testalter_tbl AUTO_INCREMENT = 100;

#在jobs表中添加j字段的索引
CREATE INDEX index_1 ON jobs(max_salary); 
ALTER TABLE jobs ADD INDEX (job_title);
ALTER TABLE jobs ADD UNIQUE (min_salary);
#在jobs表中删除job_id字段的索引
ALTER TABLE jobs DROP INDEX job_id;
DROP INDEX index_1 ON jobs; 
#添加主键索引
ALTER TABLE jobs ADD PRIMARY KEY (job_id);
#删除主键索引
ALTER TABLE jobs DROP PRIMARY KEY;
#显示索引信息
SHOW INDEX FROM jobs;

/*事务主要用于处理操作量大，复杂度高的数据。事务用来管理 insert,update,delete 语句
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
在 MySQL 命令行的默认设置下，事务都是自动提交的。显式地开启一个事务务须使用命令 
BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0。
SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO 把事务回滚到标记点；
savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
SET TRANSACTION 用来设置事务的隔离级别。
*/
#开始事务
BEGIN;
START TRANSACTION ;
#禁止自动提交
SET AUTOCOMMIT=0;
#开启自动提交
SET AUTOCOMMIT=1;
#回滚
ROLLBACK; 
#提交事务
COMMIT;
#声明一个 SAVEPOINT
SAVEPOINT savepoint_name;
# 回滚到savepoint
ROLLBACK TO savepoint_name;
#删除 SAVEPOIN
RELEASE SAVEPOINT savepoint_name; 

/*
首先使用下面的命令 show variables like '%secure%'; 查看数据库的存储路径。
如果查出的 secure_file_priv 是 null 的时候就证明在 my.ini 文件里面没有配置写出路径。
这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加
secure_file_priv=D:/java/mysql-5.7.27-win32/backup 
重启服务后再进行导出的地址下面写上刚才配置的这个地址
*/
SHOW VARIABLES LIKE 'secure_file_priv';
#将数据表 runoob_tbl 数据导出到runoob.txt 文件中
SELECT * FROM runoob_test_tbl INTO OUTFILE 'D:/java/mysql-5.7.27-win32/backup/runoob.sql';
#导出数据库RUNOOB下的runoob_test_tbl表
mysqldump -u root -p RUNOOB runoob_test_tbl > D:/java/mysql-5.7.27-win32/BACKUP/dump.txt
#导出整个数据库RUNOOB
mysqldump -u root -p RUNOOB > D:/java/mysql-5.7.27-win32/BACKUP/dump.txt
#备份所有数据库
mysqldump -u root -p --all-DATABASES > D:/java/mysql-5.7.27-win32/BACKUP/database_dump.txt
#将导出的数据直接导入到远程的服务器 确保两台服务器是相通
mysqldump -u root -p database_name \  | mysql -h other-host.com database_name
#指定主机的数据库拷贝到本地
mysqldump -h other-host.com -P PORT -u root -p database_name > D:/java/mysql-5.7.27-win32/BACKUP/dump.txt

#将备份的数据库导入到MySQL服务器中 确认数据库已经创建
mysql -u root -p test <  D:/java/mysql-5.7.27-win32/BACKUP/dump.txt
#将备份的数据库导入到指定的test2库中
USE test2;
source D:/java/mysql-5.7.27-win32/BACKUP/dump.txt;
#将备份的数据表runoob的数据导入到此表中
USE test2;
LOAD DATA LOCAL INFILE 'D:/java/mysql-5.7.27-win32/backup/runoob.sql' INTO TABLE runoob_test_tbl;

数据库的三范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式：任何非主属性不依赖于其它非主属性。

4.Sql学习

SQL简介

SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。
是结构化查询语言（Structured Query Language），可以访问和处理数据库，SQL 对大小写不敏感。
RDBMS指关系型数据库管理系统（Relational Database Management System） ，是 SQL的基础，也是所有现代数据库系统的基础。

sql语法

select * from emp where not sal > 1500; //查询EMP表中 sal 小于等于 1500 的值。
Select * from emp where sal between 1500 and 3000; //between and(在 之间的值)

like
% 表示多个字值，_ 下划线表示一个字符；
M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。
and&or
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
order by
ORDER BY 关键字用于对结果集进行排序，默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。
order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序，B 升序排列
order by A ,B desc 这个时候 A 升序，B 降序排列
即 desc 或者 asc 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序。
insert
insert into select 和select into from 的区别
insert into user_bak select * from user where userid=5 //插入一行,要求表scorebak 必须存在。Table ‘test.user_bak’ doesn’t exist
SELECT * INTO user_bak FROM USER WHERE userid=5; //也是插入一行,要求表scorebak 不存在。
由于mysql不支持select into from，故因此用CREATE TABLE user_bak(SELECT * FROM USER WHERE userid=5);代替。
update/delete
执行没有 WHERE 子句的 update/delete 要慎重，再慎重。
在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在update 语句后携带 where 条件，否则就会报错。
set sql_safe_updates=1; 表示开启该参数。
UPDATE user_bak SET STATUS=1; DELETE FROM user_bak; //You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
总结
通过SHOW VARIABLES LIKE ‘sql_safe%’;//查看此参数开闭情况。
如果设置了sql_safe_updates=1，那么update语句必须满足如下条件之一才能执行成功
1)使用where子句,并且where子句中列必须为prefix索引列(主键、组合索引第一列)
2)使用limit
3)同时使用where子句和limit(此时where子句中列可以不是索引列)
delete语句必须满足如下条件之一才能执行成功
1)使用where子句,并且where子句中列必须为prefix索引列
2)同时使用where子句和limit(此时where子句中列可以不是索引列)

高级语法

SELECT TOP 50 PERCENT * FROM Websites;//从 websites 表中选取前面百分之 50 的记录：
select top 5 * from table;
并非所有的数据库系统都支持 SELECT TOP 语句
别名
在下面的情况下，使用别名很有用：
在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起
NULL 函数
无法比较 NULL 和 0；它们是不等价的。
始终使用 IS NULL 来查找 NULL 值
微软的 ISNULL() 函数用于规定如何处理 NULL 值。
NVL()、IFNULL() 和 COALESCE() 函数也可以达到相同的结果
约束
1.NOT NULL 约束
NOT NULL 约束 在默认的情况下，表的列接受 NULL 值。NOT NULL 约束强制列不接受 NULL 值。
ALTER TABLE Persons MODIFY Age int NOT NULL;//添加 NOT NULL 约束
ALTER TABLE Persons MODIFY Age int NULL;//删除 NOT NULL 约束

2. UNIQUE 约束
   UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
   ALTER TABLE Persons ADD UNIQUE (P_Id);//在 “P_Id” 列创建 UNIQUE 约束
   ALTER TABLE Persons DROP INDEX uc_PersonID;//撤销 UNIQUE 约束 Mysql
   ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;//撤销 UNIQUE 约束 Oracle
   ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);//定义多个列的 UNIQUE 约束
   3.PRIMARY KEY 约束
   PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。
   ALTER TABLE Persons ADD PRIMARY KEY (P_Id) ;//在 “P_Id” 列上创建 PRIMARY KEY 约束
   ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName);//定义多个列的 PRIMARY KEY 约束
   ALTER TABLE Persons DROP PRIMARY KEY;//撤销 PRIMARY KEY 约束 Mysql
   ALTER TABLE Persons DROP PRIMARY KEY ALTER TABLE Persons DROP CONSTRAINT pk_PersonID;//撤销 PRIMARY KEY 约束 Oracle
3. FOREIGN KEY 约束
   FOREIGN KEY 约束用于预防破坏表之间连接的行为。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
   ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);//在 “P_Id” 列创建 FOREIGN KEY 约束
   ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);//定义多个列的 FOREIGN KEY 约束
   ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;//撤销 FOREIGN KEY 约束 Mysql
   ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders;//撤销 FOREIGN KEY 约束 Oracle
4. CHECK 约束
   CHECK 约束用于限制列中的值的范围。
   ALTER TABLE Persons ADD CHECK (P_Id>0);//在 “P_Id” 列创建 CHECK 约束
   ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=‘Sandnes’);//定义多个列的 CHECK 约束
   ALTER TABLE Persons DROP CHECK chk_Person;//撤销 CHECK 约束 Mysql
   ALTER TABLE Persons DROP CONSTRAINT chk_Person;/撤销 CHECK 约束 Oracle
5. DEFAULT 约束
   DEFAULT 约束用于向列中插入默认值。 ALTER TABLE Persons ALTER City SET DEFAULT ‘SANDNES’;//在 “City” 列创建 DEFAULT 约束 Mysql ALTER TABLE
   Persons ALTER City DROP DEFAULT;//撤销 DEFAULT 约束 Mysql ALTER TABLE
   Persons MODIFY City DEFAULT ‘SANDNES’;//在 “City” 列创建 DEFAULT 约束 Oracle
   ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;//撤销 DEFAULT 约束 Oracle
   **union**
   UNION 操作符合并两个或多个 SELECT 语句的结果。UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
   UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

JOIN
INNER JOIN 关键字在表中存在至少一个匹配时返回行。INNER JOIN 与 JOIN 是相同的。
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。在某些数据库中，FULL OUTER JOIN 称为FULL JOIN。 可以使用union关键字来实现所谓的全连接。
natural join 自然连接默认是匹配两个表列名相同的，然后不重复显示。
cross join 笛卡尔积，就是第一个表的行数乘以第二个表的行数。

可以参照MySQL的Sql join

通配符
MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
SELECT * FROM USER WHERE username RLIKE ‘^ [bat]’;
SELECT * FROM USER WHERE username REGEXP ‘^ [bat]’;
SELECT * FROM USER WHERE username REGEXP ‘^ [^bat]’;

函数

MID() 函数
MID() 函数用于从文本字段中提取字符。Oracle 中没有 MID 函数，有 substr 函数有类似功能
SELECT MID(name,1,4) AS ShortTitle FROM Websites;//从 “Websites” 表的 “name” 列中提取前 4 个字符
SELECT substr(name,1,4) AS ShortTitle FROM Websites;//从 “Websites” 表的 “name” 列中提取前 4 个字符
LEN() 函数
LEN() 函数返回文本字段中值的长度。
SELECT name, LENGTH(url) as LengthOfURL FROM Websites;//从 “Websites” 表中选取 “name” 和 “url” 列中值的长度 Mysql
FORMAT() 函数
FORMAT() 函数用于对字段的显示进行格式化。
SELECT name, url, DATE_FORMAT(Now(),’%Y-%m-%d’) AS date FROM Websites;//从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期
HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites ON access_log.site_id=Websites.id) GROUP BY Websites.name HAVING SUM(access_log.count) > 200;//总访问量大于 200 的网站
ROUND() 函数
ROUND() 函数用于把数值字段舍入为指定的小数位数。

5.视图

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式。

视图由一个预定义的查询select语句组成，不存储数据，存储的是sql，保存在数据库的数据字典中。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]
create view v1 as select * from student where sid<10 with check option;; #创建视图
select * from v1; #查询视图
drop view v1; #删除视图

特点

• 视图是由基本表(实表)产生的表(虚表)，列可以来自不同的表。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新(添加，删除和修改)直接影响基本表。
• 视图不能创建索引，名字唯一，不占实际空间。
• 有下列内容之一，视图不能做DML操作：

①select子句中包含distinct

②select子句中包含组函数

③select语句中包含group by子句

④select语句中包含order by子句

⑤select语句中包含union 、union all等集合运算符

⑥where子句中包含相关子查询

⑦from子句中包含多个表

⑧如果视图中有计算列，则不能更新

⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

优点

• 查询简单化。视图能简化用户操作
• 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
• 数据独立性。一旦视图结构确定，可以屏蔽表结构变化对用户的影响

缺点

• 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建。

• 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表结构时，都必须更改视图。

6.存储过程

存储过程是一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。保存在数据库的数据字典中。

### 创建存储过程
DELIMITER // #将语句的结束符号从分号;临时改为//
CREATE PROCEDURE productpricing (
  OUT pl DECIMAL(8,2),
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN
  SELECT Min(prod_price) INTO pl FROM products;
  SELECT Max(prod_price) INTO ph FROM products;
  SELECT Avg(prod_price) INTO pa FROM products;
END //
DELIMITER ;

### 调用存储过程
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
### 查看3个变量
SELECT @pricelow,@pricehigh,@priceaverage;

#删除存储过程
drop procedure productpricing;
drop procedure if exists productpricing;

参数

3种参数类型：

IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

in输入参数

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;
mysql> set @p_in=1;
mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+
+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+
#以上可以看出，p_in在存储过程中被修改，但并不影响@p_in的值，因为前者为局部变量、后者为全局变量。

out输出参数

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
#调用了out_param存储过程，输出参数，改变了p_out变量的值

inout输入参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

优点

• 存储过程是一个预编译的代码块，执行效率比较高
• 存储过程在服务器端运行，减少客户端的压力
• 允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
• 可以一定程度上确保数据安全

缺点

• 调试麻烦

• 可移植性不灵活

• 重新编译问题

7.触发器

触发器是与表事件相关的特殊存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。比如当对一个表进行操作（insert，delete， update）时就会激活它执行。

触发器创建四要素

监视地点（table）
监视事件（insert/update/delete）
触发时间（after/before）
触发事件（insert/update/delete）

MySQL 数据库中有六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

#触发器的名称为t1，触发时间为after，监视动作为insert，监视ord表,update为触发事件
delimiter $ #设置MySQL执行结束标志，默认为;
create trigger t1 
after
insert
on ord
for each row
begin
 update goods set num=num-2 where gid = 1;
end$
delimiter ;

触发器中引用行变量

• 在触发目标上执行insert操作后会有一个新行，如果在触发事件中需要用到这个新行的变量，可以用new关键字表示
• 在触发目标上执行delete操作后会有一个旧行，如果在触发事件中需要用到这个旧行的变量，可以用old关键字表示
• 在触发目标上执行update操作后原纪录是旧行，新记录是新行，可以使用new和old关键字来分别操作

当下订单时减少相应的货品的库存量，创建触发器：

create trigger t2
after
insert
on ord
for each row #触发器分为行触发器和语句触发器。每一行受影响，触发事件都执行，叫做行触发器
begin
 update goods set num=num-new.much where gid=new.gid;

当删除订单时增加相应的修改货品的库存量，创建触发器：

create trigger t3
after
delete
on ord
for each row
begin
 update goods set num=num+old.much where gid=old.gid;
end$

当更新订单的购买数修改相应的修改货品的库存量，创建触发器：

create trigger t4
before 
update
on ord
for each row
begin
 update goods set num=num+old.much-new.much where gid = new.gid;
end$

使用场景：

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。

注意不要滥用，否则会造成数据库及应用程序的维护困难。

8.游标

游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 作用是用于对查询数据库所返回的记录进行遍历，以便进行相应操作。

delimiter $$
create procedure p2()
begin
#定义的三个变量用于将fetch取值出来的值放到对应的变量中
declare row_cat_id int;
declare row_cat_name varchar(90);
declare row_parent_id int;
declare getcategory cursor for select cat_id,cat_name,parent_id from category;#定义游标
open getcategory;#打开游标
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;#取值
select row_cat_id,row_cat_name,row_parent_id;#对赋值后的变量进行查询显示
close getcategory;#关闭游标
end$$
delimiter ;

使用方式

loop循环

#在windows系统中写存储过程时，如果需要使用declare声明变量，需要添加这个关键字，否则会报错。  
delimiter //  
drop procedure if exists StatisticStore;  
CREATE PROCEDURE StatisticStore()  
BEGIN  
    #创建接收游标数据的变量  
    declare c int;  
    declare n varchar(20);  
    #创建总数变量  
    declare total int default 0;  
    #创建结束标志变量  
    declare done int default false;  
    #创建游标  
    declare cur cursor for select name,count from store where name = 'iphone';  
    #指定游标循环结束时的返回值  
    declare continue HANDLER for not found set done = true;  
    #设置初始值  
    set total = 0;  
    #打开游标  
    open cur;  
    #开始循环游标里的数据  
    read_loop:loop  
    #根据游标当前指向的一条数据  
    fetch cur into n,c;  
    #判断游标的循环是否结束  
    if done then  
        leave read_loop;    #跳出游标循环  
    end if;  
    #获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  
    set total = total + c;  
    #结束游标循环  
    end loop;  
    #关闭游标  
    close cur;  
  
    #输出结果  
    select total;  
END//
delimiter ;  
#调用存储过程  
call StatisticStore();

while循环

delimiter $$
CREATE PROCEDURE p4()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  #当没数据的时候要执行的语句
    set total = 0;  
    open cur;  
    fetch cur into n,c;  
    while(not done) do  
        set total = total + c;  
        fetch cur into n,c;  
    end while;  
      
    close cur;  
    select total;  
END$$
delimiter ;

delimiter $
create procedure p41()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods where 0;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  while have = 1 do 
  select row_name,row_num;
    fetch getgoods into row_gid,row_name,row_num;
  end while;
  close getgoods;
end$
delimiter ;

repeat执行

#最后一个查了两次,发生NOT FOUND事件就会出发set ergodic=0,由于continue还需要继续执行后面的sql，导致查询两次
#声明处理hanlder不再是continue，而是exit即可达到目的 declare exit handler for NOT FOUND set ergodic=0;
#exit触发后，后面的语句不再执行，而continue还需要继续执行。
delimiter $$
create procedure p5()
begin
declare row_cat_id int;
declare row_cat_name varchar(90);
declare row_parent_id int;
declare ergodic int default 1;#声明一个变量表明还有数据可遍历
declare getcategory cursor for select cat_id,cat_name,parent_id from category;
declare continue handler for NOT FOUND set ergodic=0;#一旦发生NOT FOUND事件就会出发set ergodic=0这个语句
open getcategory;
repeat
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
select row_cat_id,row_cat_name,row_parent_id;
until ergodic=0 end repeat;
close getcategory;
end$$
delimiter ;

delimiter $
create procedure p7()
begin
declare row_cat_id int;
declare row_cat_name varchar(90);
declare row_parent_id int;
declare ergodic int default 1;
declare getcategory cursor for select cat_id,cat_name,parent_id from category;
declare continue handler for NOT FOUND set ergodic:=0;
open getcategory;
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
repeat
select row_cat_id,row_cat_name,row_parent_id;
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
until ergodic=0 end repeat;
close getcategory;
end$
delimiter ;

9.其他问题

drop、delete与truncate

	delete	truncate	drop
类型	DML	DDL	DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，逐行删除	删除速度快	删除速度最快

count(1)、count(*) 与 count(列名)

• count(*)包括了所有的列，相当于行数，在统计结果时不会忽略列值为NULL
• count(1)包括了忽略所有列，用1代表代码行，在统计结果时不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果时，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时不统计。
• 列名为主键，count(列名)会比count(1)快；列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)最优

UNION与UNION ALL

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
• UNION的效率高于 UNION ALL

主键使用自增ID还是UUID，为什么？

如果是单机选择自增ID；如果是分布式系统优先考虑UUID，但还是最好一套分布式唯一ID生产方案。

• 自增ID：数据存储空间小，查询效率高。但是如果数据量过大会超出自增长值的范围，多库合并，也有可能有问题。
• uuid：适合大量数据的插入和更新操作，但是它无序，插入数据效率慢，占用空间大。

数据库是否支持emoji表情存储

更换字符集utf8–>utf8mb4

百万级别数据如何删除

• 可以先删除索引
• 然后批量删除其中无用数据
• 删除完成后重新创建索引

监控数据库工具有很多，例如zabbix，lepus（天兔）

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录？

1、如果A表TID是自增长,并且是连续的,B表的ID为索引 select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。 select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

段值为NULL时不统计。

• 列名为主键，count(列名)会比count(1)快；列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)最优

UNION与UNION ALL

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
• UNION的效率高于 UNION ALL

主键使用自增ID还是UUID，为什么？

如果是单机选择自增ID；如果是分布式系统优先考虑UUID，但还是最好一套分布式唯一ID生产方案。

• 自增ID：数据存储空间小，查询效率高。但是如果数据量过大会超出自增长值的范围，多库合并，也有可能有问题。
• uuid：适合大量数据的插入和更新操作，但是它无序，插入数据效率慢，占用空间大。

数据库是否支持emoji表情存储

更换字符集utf8–>utf8mb4

百万级别数据如何删除

• 可以先删除索引
• 然后批量删除其中无用数据
• 删除完成后重新创建索引

监控数据库工具有很多，例如zabbix，lepus（天兔）

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录？

1、如果A表TID是自增长,并且是连续的,B表的ID为索引 select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。 select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;