这篇博客详细介绍了MySQL数据库的相关概念和操作，包括SQL语言分类（DQL、DML、DDL、DCL），数据库查询语言DQL的函数和连接查询，DML的插入、更新和删除，DDL的表和视图管理，以及事务控制、存储过程和函数的使用。还涵盖了流程控制结构和数据类型的介绍。
数据库的相关概念
DB：database 数据库
DBMS：数据库管理系统 mysql/oracle
启动windows的mysql服务
cmd
net start mysql
net stop mysql
连接mysql
#-h 主机地址 -P端口号
mysql -h localhost -P 3306 -u root -p
mysql常用语法：
#数据库列表
show databases;
#进入使用test数据库
use test;
#数据库中的表
show tables;
#查看mysql数据库中的表
show tables from mysql;
#查询所在的库
select database();
#创建表
create table student (
id int,
name varchar(20));
#查看表结构
desc student;
查看数据库版本
select version();
设置字符集
set names gbk；
sql语言分类
数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>
数据操纵语言DML
数据操纵语言DML主要有三种形式：
- 插入：INSERT
- 更新：UPDATE
- 删除：DELETE
数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇
DDL操作是隐性提交的！不能rollback
数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
-
GRANT：授权。
-
ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚—ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK; -
COMMIT [WORK]：提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
select 用法
1、常用查询语句
2、常量查询
select 100;
3、表达式查询
select 100*98
4、函数查询
select version();
mysql中"+"的作用：仅仅是个运算符
null与任何的字段拼接，结果均为null
可以使用函数 ifnull (字段名，替换字段值)
select contac(first_name,',',last_name,','ifnull(salary,0)) as 'out put' from employee
条件查询
DQL数据库查询语言
常用函数
单行函数
字符函数：length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
数学函数：round、rand、ceil、floor、truncate、mod
日期函数：now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_formate
其他函数：version、database、user
分组函数
if：if(2>5,a,b)
case:
CASE WHEN SCORE = 'A' THEN '优'
WHEN SCORE = 'B' THEN '良'
WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
分组查询 group by
分组之后的条件查询：having
连接查询
内连接 ：inner join
外连接 ：
左外连接 ：left join on
右外连接：right join on
全连接： full join on
交叉连接
连接总结：
1、左外连接，左边的表为全部，右边的表可为空，右外相反
2、内连接：只查询，两个表的交集部分
3、全外连接：查询两个表的并集，包括两个表中为null的行
子查询
where、having
单行子查询：where a > ( select ) 单行单列
多行子查询: where a in (select ) 单列多行
in：等于列表中的任意一个
any：和子查询中的任意一个值比较
all：和子查询中的所有的值比较
分页查询
limit(offset,size)
limit(size)
limit((page-1)*size,size)
联合查询
union 去重
union all 不去重
DML数据操纵语言
数据的操作管理
insert
update
delete
delete和truncate的区别:
delete from 表名 #可以通过事务回滚，数据不删除
truncate table 表名 #彻底删除数据，事务回滚不起作用
DDL数据定义语言
库和表的管理
库的操作
库的创建
CREATE DATABASE IF NOT EXISTS books;
库字符集的修改
ALTER DATABASE books CHARACTER SET utf8;
库的删除
DROP DATABASE IF EXISTS books;
表的操作
表的创建
CREATE TABLE IF NOT EXISTS 表名 (
列名 列的类型【长度 约束】,
列名 列的类型【长度 约束】,
列名 列的类型【长度 约束】,
...
列名 列的类型【长度 约束】
)
表的修改
修改列明
ALTER TABLE 表名 CHANGE COLUMN 列名 新列名 列名类型
修改列的类型
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型
添加新列
ALTER TABLE 表名 ADD COLUMN 列名 类型
删除列
ALTER TABLE 表名 DROP COLUMN 列名
修改表名
ALTER TABLE 表名 RENAME TO 新表名
表的删除
DROP TABLE IF EXISTS 表名;
表的复制
1、仅仅复制表的结构
CREATE TABLE 复制表名 LIKE 被复制表名
2、复制表的结构和数据
CREATE TABLE 复制表名 SELECT * FROM 被复制表名
3、只复制部分数据
CREATE TABLE 复制表名 SELECT 部分字段 FROM 被复制表名 WHERE 字段 = 值
4、只复制某些字段，不要数据
CREATE TABLE 复制表名 SELECT 部分字段 FROM 被复制表名 WHERE 0；
0代表false 1代表true
数据类型介绍
数值型：
整型：tinyint（2^8）、smallint(2^16)、mediumint(2^32)、int(2^64)、bigint(2^128)
有符号则包含正负值
无符号则不包含复制，正值扩大一倍
例如：
CREATE TABLE IF NOT EXISTS books (
#zerofill 关键词 如果插入数值长度不够，自动填充0；如果有此关键字，则代表此数值无符号，不能插入负数
booknum INT(7) ZEROFILL,
bookpage INT(7) UNSIGNED
)
………………………………………………………………………………………………………………………………………………………………………………………………………………
小数：
定点数：dec(M,D)、decimal(M,D) 精确
浮点数：float(M,D)、double(M,D)
注：M是整数位+小数位总位数，D小数点后的位数
***********************************************************************************
字符型：
较短的文本：char、varchar、binary（较短的二进制数据）、varbinary、enum（枚举）、set（集合）
例如： CREATE TABLE IF NOT EXISTS table_t1(
#枚举类型插入的值只能是枚举中的一个值，不区分大小写
t1 ENUM('a','b','c'),
#set类型，插入的值可以是a、b、c中的多个值
t2 SET('a','b','c')
)
较长的文本：text、blob（较长的二进制数据）
***********************************************************************************
日期型：date、datetime、timestamp（常用，占用资源较少，受时区影响）、time、year
查看时区： SHOW VARIABLES LIKE 'time_zone'
设置时区： SET time_zone = '+9:00'、 SET time_zone = 'system'
常见约束
六大约束：
not null:非空约束
default：默认值约束
primary key：主键 （唯一性，非空）
unique：唯一约束（可以为空）
check：检查约束 （mysql不支持，年龄、性别）
foreign key：外键约束
约束分类：
列级约束：除了外键约束，均可
表级约束：除了非空、默认，其他都支持
添加列级约束
CREATE TABLE major(
id INT PRIMARY KEY,
majorname VARCHAR(20)
)
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuname VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认
majorid INT REFERENCES major(id)#外键约束 不支持
)
添加表级约束
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20) ,
gender CHAR(1) ,
seat INT ,
age INT ,
majorid INT,
# CONSTRAINT pk 可以省略
CONSTRAINT pk PRIMARY KEY (id),#主键
CONSTRAINT uq UNIQUE (seat),#唯一
CONSTRAINT ck CHECK(gender = '男' OR gender='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
)
# 【CONSTRAINT 名称】 可以省略
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20) ,
gender CHAR(1) ,
seat INT ,
age INT ,
majorid INT,
PRIMARY KEY(id),#主键
UNIQUE(seat),#唯一
CHECK(gender = '男' OR gender='女'),#检查
FOREIGN KEY(majorid) REFERENCES major(id)#外键
)
通用的写法（列级约束+表级约束）
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuname VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认
majorid INT,#外键约束
FOREIGN KEY(majorid) REFERENCES major(id)#外键
)
注：
外键：
1、要求在从表中设置外键关系
2、从表的外键类型与主表的关联列的类型要求一致或兼容
3、主表的关联列必须时一个key（主键或唯一）
4、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
修改表时添加约束
#修改时添加列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
#修改时添加表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY (id);#主键
ALTER TABLE stuinfo ADD UNIQUE(seat)#唯一
ALTER TABLE stuinfo ADD FOREIGN KEY (majorid) REFERENCES major(id)#外键
修改表示删除约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL #删除非空
ALTER TABLE stuinfo MODIFY COLUMN age INT #删除默认
ALTER TABLE stuinfo DROP PRIMARY KEY #删除主键
ALTER TABLE stuinfo DROP INDEX seat #删除唯一
ALTER TABLE stuinfo DROP FOREIGN KEY majorid #删除外键
标识列
自增长列：可以不用手动的插入值，系统提供默认的序列值
创建表时设置标识列
#auto_increment
CREATE TABLE a(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
注：
1、标识列必须与主键或者唯一约束搭配
2、一个表最多一个标识列
3、标识列的类型只能是数值型
4、标识列可以通过设置数据库字段属性，设置步长：
SHOW VARIABLES LIKE '%auto_increment%'
SET auto_increment_increment = 1
5、可以通过手动插入值，设置起始值
修改表时设置标识列
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT
修改表时删除标识列
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
TCL事务语言
事务：一个或一组sql语句组成一个执行单元，要么全执行，要么全不执行
存储引擎中： innodb支持事务，myisam、memory不支持事务
事务的ACID属性：
1、原子性：最小单元，要么都成功，要么都失败
2、一致性：a+b = 1000 执行事务之后 a+b 还是1000
3、隔离性：事务之间互不干扰
4、持久性：数据持久化
事务的创建
隐式事务：事务没有明显的开启和结束的标记
显式事务：事务具有明显的开启和结束的标记
（前提：必须先设置自动提交功能为禁用）
SHOW VARIABLES LIKE 'autocommit'
SET autocommit = 0
步骤：
1、开启事务
SET autocommit = 0
start transaction；（可选，禁用自动提交即开启事务）
2、编写事务中的sql
3、结束事务
commit
rollback
隔离级别
脏读：事务A、B，B更新某个字段，但未提交，A读到更新的字段
不可重复读：事务A、B，A读到字段值，B更新了字段，提交，A事务再次读取，字段值变更
幻读：事务A、B，A读取到3行数据，对三行数据进行修改，修改之前B插入几行数据，A修改数据时，会将B插入的新数据一并修改
oracle支持两种隔离级别：read commited（默认） 、serializable
mysql支持四种隔离级别：read uncommited、read commited、repeatable read（默认）、serializable
read uncommited：三种情况都有可能出现
read commited：避免脏读
repeatable read（默认）：避免脏读和不可重复读
serializable：避免脏读、不可重复读、幻读
注：serializable开启最高隔离级别之后，开启某个事务，会对表加锁，其他事务只能等待该事务执行完毕，才能对表数据进行操作
查看隔离级别
SELECT @@tx_isolation
设置隔离级别
#设置当前mysql连接的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
#设置全局数据库系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REA COMMITTED
回滚点 savepoint
SET autocommit = 0;
DELETE FROM stuinfo WHERE id = 5
SAVEPOINT a;#设置回滚点
DELETE FROM stuinfo WHERE id = 6
ROLLBACK TO a;#回滚到a的位置
视图
含义：虚拟的表
mysql 5.1新出的特性
视图的创建
CREATE VIEW 视图名 AS SELECT 查询语句
视图的修改
方式一：
CREATE OR REPLACE VIEW 视图名 AS SELECT 查询语句
方式二：
ALTER VIEW 视图名 AS SELECT 查询语句
视图的删除
DROP VIEW 视图名,视图名,视图名;
视图的查看
SHOW CREATE VIEW 视图名 #查看视图的创建过程
DESC 视图名 #查看视图数据结构
视图的更新
插入
insert into 视图名（）values ()
修改
update 视图名 set 字段 = 值 where 条件
删除
delete from 视图名 where 条件
对于视图数据的增删改，会影响到原始表的数据
一般情况下视图时不允许增删改数据的
变量
系统变量：
全局变量：全局
会话变量：会话连接范围内
自定义变量：
用户变量：
局部变量：
系统变量
说明：由系统提供属于服务器层面
1、查看所有的系统变量
show global|【session】 variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%值%'
3、查看指定的某个系统变量的值
select @@global|【session】.系统变量名；
4、系统变量复制
方式一：set global|【session】系统变量名 =值；
方式二：set @@global|【session】.系统变量名 = 值;
自定义变量
用户变量
说明：变量是用户自定义的,作用域当前会话
1、声明并初始化
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
2、赋值
方式一：
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
方式二：
select 字段 into @变量名 from 表;
局部变量：作用域，仅仅再定义它的begin end中有效
1、声明
declare 变量名 类型；
declare 变量名 类型 default 值；
2、赋值：
方式一：
set 局部变量名=值
set 局部变量名:=值
select @局部变量名:=值
方式二：
select 字段 into @局部变量名 from 表;
3、使用
select 局部变量名；
案例：
SET @m = 1;
SET @n = 2;
SET @sum = @m+@n;
SELECT @sum # 值为3
#报错，自定义局部变量只能使用在begin，end中的第一句话
DECLARE m INT DEFAULT 1;
DECLARE m INT DEFAULT 1;
DECLARE s INT ;
SET s=m+n;
SELECT @s;
存储过程和函数
理解：类似于java中的方法
存储过程：
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
好处：
1、提高代码的重用性
2、简化操作
3、减少SQL的编译次数，并且减少了和数据库服务器的连接次数
创建存储过程
创建语法：
DELIMITER 结束标记
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
存储过程体（一组合法的SQL语句）
END 结束标记
注意：
1、参数列表包含三部分：参数模式、参数名、参数类型
举例：IN stuname VARCHAR(20)
参数模式：
IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入，也可以作为输出
2、如果存储过程体仅仅只有一句话，begin、end可以省略
3、存储过程体中的每条SQL语句的结尾要求必须加分号，存储过程的结尾可以使用DELIMITER重新设置
语法：DELIMITER 结束标记
例子：
DELIMITER $
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
SQL1;
SQL2;
SQL3;
END $
调用存储过程
call 存储过程名(实参列表);
例子：
创建
DELIMITER $ #定义存储过程结束标记
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
SQL1;
SQL2;
SQL3;
END $
调用
call 存储过程名 $ #调用存储过程一定要加上自定义的结束标记
例子：
in
out
inout
删除存储过程
语法：drop procedure 存储过程名；#只能一个个删除
查看存储过程结构
语法：show create procedure 存储过程名
函数
1、创建函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意：
1、参数列表：参数名 参数类型
2、函数体：肯定会有return语句
3、函数体中只有一条语句，可以省略begin、end
4、使用delimiter语句设置结束标记
2、调用函数
select 函数名(参数列表)
3、查看函数
show create function 函数名
4、删除函数
drop function 函数名
举例：
总结存储过程和函数的区别：
存储过程：可以有0个返回值，也可以有多个返回值，适合做批量插入，批量更新
函数：有且仅有一个返回值，适合做处理数据后返回一个结果
流程控制结构
分支结构
1、if函数
功能：实现简单的双分支
语法：
IF(表达式1，表达式2，表达式3)
如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值
2、case结构
情况一：类似于java中的switch，一般用于实现等值判断
语法：
case 变量|表达式|字段
when 要判断的值 then 返回的值或语句1
when 要判断的值 then 返回的值或语句2
...
else 返回的值或语句n
end
情况二：类似于java中的多重IF语句，实现区间判断
语法：
case
when 要判断的条件1 then 返回的值或语句1
when 要判断的条件2 then 返回的值或语句2
...
else 返回的值或语句n
end
case结构的特点：
1、可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
2、可以作为独立语句使用 ，只能放在begin、end中
3、if结构
功能：实现多重分支
语法：
IF 条件1 then 语句1；
elseif 条件2 then 语句2；
...
else 语句n；
end if；
应用场合：begin、end中
循环结构
分类：
while、loop、repeat
循环控制：
iterate类似于continue，继续，结束本次循环进行下一次
leave类似于break，跳出，结束当前所在的循环
1、while
语法：
【标签:】while 循环条件 do
循环体；
end while【标签】；
2、loop
语法：
【标签:】loop循环条件 do
循环体；
end loop【标签】;
3、repeat
语法：
【标签:】repeat
循环体;
untile 结束循环的条件
end repeat【标签】;
如果有循环控制语句，则循环必须加标签名
举例：
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
