路径 | 目录 | 子目录 | 重点 | 课程名 | 备注 |
---|---|---|---|---|---|
java工程师 | 入门必学 | 网络与多线程 | socket编程、多线程 | Java Socket应用-通信是这样炼成的 | |
深入浅出java多线程 | |||||
数据操作 | MySQL数据库操作、Oracle护具库操作、应用JDBC操作数据库 | 与MySQL的零距离接触 | |||
Oracle数据库开发必备利器之SQL基础 | |||||
JDBC之对面的女孩看过来 |
20200710-202000715:《imooc-与MySQL的零距离接触》 |
第01章 初涉MySQL
01-01 MySQL概述
一、MySQL基础
1、MySQL由瑞典MySQL AB公司开发,目前属于Oracle公司。
2、MySQL是一个开源的关系型数据库管理系统。
3、MySQL分为社区版和企业版。
01-02 MySQL的安装与配置
一、MySQL安装方式
1、MSI安装(Windowss Installer)
2、ZIP安装
二、安装类型
Typical: 典型安装
Custom:自定义安装
Complete:完全安装
三、MySQL目录结构
1、bin目录,存储可执行文件。
2、data目录,存储数据文件。
3、docs, 文档
4、include目录,存储包含的头文件
5、lib目录,存储库文件
6、share, 错误消息和字符集文件。
四、MySQL的配置选项
1、修改编码方式
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
01-03 启动与停止MySQL服务
一、启动/关闭MySQL服务
1、计算机管理-服务和应用程序-服务-MySQL服务,右键,启动/重新启动。
2、启动MySQL服务
net start mysql
3、关闭MySQL服务
net stop mysql
01-04 MySQL登录与退出
一、使用MySQL
1、MySQL登录/退出
2、修改MySQL提示符
3、MySQL常用命令
4、MySQL语句规范
5、数据库操作
二、MySQL登录
参数 | 描述 |
---|---|
-D, --database=name | 打开指定数据库 |
–delimiter=name | 指定分隔符 |
-h, --host=name | 服务器名称 |
-p, --oassword[=name] | 密码 |
-P,–port=# | 端口号 |
–prompt=name | 设置提示符 |
-u, --user=name | 用户名 |
-v,–version | 输出版本信息并且退出 |
三、
mysql -V
mysql -uroot -p -P3306 -h127.0.0.1
四、MySQL退出
exit
quit
\q
01-05 修改MySQL提示符
一、windows下清屏:cls
二、修改MySQL提示符
1、连接客户端时通过参数指定
mysql -uroot -proot --prompt 提示符
2、连接上客户端后,通过prompt命令修改。
prompt 提示符
三、MySQL提示符
参数 | 描述 |
---|---|
\D | 完整的日期 |
\d | 当前数据库 |
\h | 服务器名称 |
\u | 当前用户 |
01-06 MySQL常用命令
一、常用命令
1、显示当前服务器版本
SELECT VERSION();
2、显示当前日期时间
SELECT NOW();
3、显示当前用户
SELECT USER();
二、Mysql语句的规范
1、关键字与函数名称全部大写
2、数据库名称、表名称、字段名称全部小写
3、SQL语句必须以分号结尾。
01-07 操作数据库
一、创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
二、 查看当前服务器下的数据表列表
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
三、显示数据库创建时所使用的指令
SHOW CREATE DATABASE t1
四、修改数据库
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT CHARACTER SET [=] charset_name
五、删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
第02章 数据类型与操作数据表
02-02 MySQL数据类型之整型
一、数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它决定了数据的存储格式,代表了不同的信息类型。
1、整型
2、浮点型
3、日期时间型
4、字符型
二、整型
数据类型 | 存储范围 | 字节 |
---|---|---|
TINYINT | 1、有符号值:-128到127(-2的7次方 到 2的7次方-1) 2、无符号值:0到255(0到2的8次方-1) | 1 |
SMALLINT | 1、有符号值:-32768到32767(-2的15次方 到 2的15次方-1) 2、无符号值:0到65535(0到2的16次方-1) | 2 |
MEDIUMINT | 1、有符号值:-8388608到8388607(-2的23次方 到 2的23次方-1) 2、无符号值:0到16777215(0到2的24次方-1) | 3 |
INT | 1、有符号值:-2147483648到2147473647(-2的31次方 到 2的31次方-1) 2、无符号值:0到4294967295(0到2的32次方-1) | 4 |
BIGINT | 1、有符号值:-9223372036854775808到9223373036854775807(-2的63次方 到 2的63次方-1) 2、无符号值:0到255(0到2的64次方-1) | 8 |
二、浮点型
三、日期时间型
列类型 | 存储需求 |
---|---|
YEAR | 1 |
TIME | 3 |
DATE | 3 |
DATETIME | 8 |
TIMESTAMP | 4 |
四、字符型
02-06 MySQL创建数据表
一、概述
数据表(或称表)是数据库最重要的组成部分之一,是其他对象的基础。
二、USE
1、打开数据库
2、SHOW DATABASES // 查看数据库是否存在
3、USE 数据库名称
4、SELECT DATABASE // 当前打开的数据库
三、创建数据表
CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type,
...
)
eg
CREATE TABLE tb1(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
)
02-07 MySQL查看数据表
一、查看数据表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
二、查看数据表结构
SHOW COLUMNS FROM tbl_name
三、INSERT
1、插入记录
INSERT [INFO] tbl_name [(col_name, ...)] VALUES(val, ...)
eg
INSERT tb1 VALUES('Tom', 25, 7863.25); // 正确
INSERT tb1 VALUES('Tom', 25) // 错误
INSERT tb1(username, salary) VALUES('John', 4500.49);
四、SELECT 记录查找
SELECT expr, ... FROM tbl_name
eg
SELECT * FROM tb1;
// * 是字段的过滤
五、空值与非空
NULL,字段值可以为空
NOT NULL, 字段值禁止为空
CREATE TABLE tb2(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);
SHOW COLUMNS FROM tb2
六、AUTO_INCREMENT
1、自动编号,且必须与主键组合使用。
2、默认情况下,起始值为1,每次的增量为1
CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL
);
七、PRIMARY KEY
1、主键约束
2、每张数据表只能存在一个主键
3、主键保证记录的唯一性
4、主键自动为NOT NULL
5、AUTO_INCREMENT必须和PRIMARY KEY一起使用,但是PRIMARY KEY不一定要和AUTO_INCREMENT一起使用。
八、UNIQUE KEY
1、唯一约束
2、唯一约束可以保证记录的唯一性
3、唯一约束的字段可以为空值(NULL)
4、每张数据表可以存在多个唯一约束
CREATE TABLE tb5(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
age tinyint UNSIGNED
);
九、DEFAULT 默认值
1、当插入记录时,如果没有明确为字段赋值,则自动赋予默认值。
CREATE TABLE tb6(
id SMALLINT UNGIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1', '2', '3') DEFAULT '3'
);
十、知识点
1、数据类型:字符型、整型、浮点型、日期时间型
2、数据表操作:插入记录、查找记录。
3、记录操作
(1)创建数据表
(2)约束的使用
① PRIMARY KEY
主键约束
②UNIQUE KEY
唯一约束
③DEFAULT
默认约束
④NOT NULL
非空约束
第03章 约束以及修改数据表
03-02 MySQL外键约束的要求解析
一、约束
1、约束保证数据的完整性和一致性。
2、约束分为表级约束和列级约束。
3、约束类型包括:
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
二、FOREIGN KEYp
1、保持数据一致性,完整性。
2、实现一对一或一对多关系。
二、外键约束的要求
1、父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
(1)子表:具有外键列的表
(2)父表:子表所参照的表。
2、数据表的存储引擎只能为InnoDB.
3、外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
4、外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。
三、编辑数据表的默认存储引擎
1、MySQL配置文件
default-storage-engine=INNODB
修改后需要重新启动。
四、
// 父表
CREATE TABLE provinces(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL,
);
// 子表
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);
五、显示索引
SHOW INDEXS FROM provinces;
03-03 MySQL外键约束的参照操作
一、外键约束的参照操作
1、CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2、SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL
3、RESTRICT:拒绝对父表的删除或更新操作。
4、NOT ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同。
二、表级约束与列级约束
1、对一个数据列建立的约束,称为列级约束。
2、对多个数据列建立的约束,称为表级约束。
3、列级约束既可以再列定义时声明,也可以再列定义后声明。
4、表级约束只能在列定义后声明。
5、NOT NULL(非空约束)、DEFAULT(默认约束)不存在表级约束,只能为列级约束 。
PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、FOREIGN KEY(外键约束),既可以存在表级约束,也可以存在列级约束。
三、修改数据表
1、添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
2、添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...);
3、删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name;
4、删除多列
ALTER TABLE tabl_name DROP [COLUMN] col_name, DROP [COLUMN] col_name;
5、添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...)
6、添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...)
7、添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition
8、添加/删除默认约束
ALTER TABEL tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
9、删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
10、删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
11、删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
12、修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | ALTER col_name]
(1)由大类型改成小类型,可能会造成数据丢失。
13、修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|ALTER col_name]
14、数据表更名
(1)方法1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
(2)方法2
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
四、本节知识点
1、约束
(1)按功能划为:NOT NULL(非空约束), PRIMARY KEY(主键约束), UNIQUE KEY(唯一约束), DEFAULT(默认约束), FOREIGN KEY(外键约束)
(2)按数据列的数目划为:表级约束,列级约束
2、修改数据表
(1)针对字段的操作:添加/删除字段,修改列定义,修改列名称等
(2)针对约束的操作:添加/删除各种约束
(3)针对数据表的操作:数据表更名(两种方式)
第04章 操作数据表中的记录
04-02 MySQL插入记录INSERT
一、插入记录 INSERT
(1)方法1
INSERT [INTO] tbl_name [col_name, ...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
(2)方法2
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...
与方法1 的区别在于,此方法可以使用子查询(SubQuery)
(3)方法3
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
此方法可以将查询结果插入到指定数据表。
二、更新记录(单表更新) UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHRE where_condition]
三、删除记录(单表删除) DELETE
DELETE FROM tbl_name [WHERE where_condition]
四、查找记录 SELECT
SELECT select_expr [,select_expr ...]
[
FROM table_references
[WHERE where_condition]
[WGROUP BY {col_name | position} [ASC | DESC], ...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row count OFFSET offeset}]
]
五、查询表达式 select_expr
1、每一个表达式表示想要的一列,必须有至少一个。
2、多个列之间以英文逗号隔开。
3、星号(*)表示所有列。tbl_name.*可以表示命名表的所有列。
4、查询表达式可以使用[AS] alias_name为其赋予别名。
5、别名可用于GROUP BY, ORDER BY 或HAVING子句。
(1)select 字段出现的顺序,将影响结果集出现的顺序。
(2)字段的别名,将影响结果集中字段别名。
六、条件表达式 WHRE
1、对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。
2、在WHERE表达式中,可以使用MySQL支持的函数或运算符。
七、查询结果分组 GROUP BY
[GROUP BY {col_name | position} [ASC | DESC], ...]
八、分组条件 HAVING
[HAVING where_condition]
九、对查询结果进行排序 ORDER BY
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
(1)ASC:升序
DESC:降序
(2)eg
SELECT * FROM users ORDER BY age, id DESC;
十、限制查询结果返回的数量 LIMIT
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
(1) eg
SELECT * FROM users LIMIT 2;
SELECT * FROM users limit 2,2;
// id号与结果集中的排列顺序没有任何关系
04-12 小结
一、本节知识点
1、记录操作
(1)写操作
INSERT
UPDATE
DELETE
(2)读取操作
SELECT
第05章 子查询与连接
05-01 数据准备
一、子查询、连接
二、SET NAMES gbk;
只是影响客户端的数据,并不影响真实数据表的数据。
三、子查询
子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。
(1)
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中
① SELECT * FROM t1
, 称为 Outer Query/Outer Statement
②SELECT col2 FROM t2
, 称为SubQuery
四、子查询指嵌套在查询内部,且必须始终出现在圆括号内。
五、子查询可以包含多个关键字或条件,如DISTINCT、GROUP BY、ORDER BY,LIMIT,函数等。
六、子查询的外层查询可以是:SELECT, INSERT, UPDATE, SET 或DO。
七、子查询可以返回标量、一行、一列或子查询。
05-03 由比较运算符引发的子查询(第1种子查询)
一、使用比较运算符的子查询
=
、>
、<
、>=
、<=
、<>
、!=
、<=>
语法结构
operand comparison_operator subquery
(1)
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT AVG((goods_price),2) FROM tdb_goods);
二、用ANY、SOME 或ALL修饰的比较运算符
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
1、
运算符\关键字 | ANY | SOME | ALL |
---|---|---|---|
<、>= | 最小值 | 最小值 | 最大值 |
<、<= | 最大值 | 最大值 | 最小值 |
= | 任意值 | 任意值 | |
<>、!= | 任意值 |
05-03 由[NOT] IN/EXISTS引发的子查询(第2种子查询、第3种子查询)
一、语法结构
operand comparison_operator [NOT] IN (subquery)
=ANY 运算符与IN等效。
!=ALL或<>ALL运符与NOT IN等效。
二、使用[NOT] EXISTS的子查询
1、如果子查询返回任何行,EXISTS将返回TRUE;否则为FALSE。
三、将查询结果写入数据表 INSERT…SELECT
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
1、INSERT tdb_goods_cates(cate_name) SELECT good_cate FROM tdb_goods GROUP BY goods_cate;
05-06 多表更新
一、多表更新
1、方法1
UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition]
2、方法2:多表更新之一步到位 CREATE…SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement
(1)
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
二、语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr
三、连接类型(3种)
1、INNER JOIN, 内连接
在MySQL中,JOIN, CROSS JOIN 和INNER JOIN是等价的。
2、LEFT [OUTER] JOIN,左外连接
3、RIGHT [OUTER] JOIN,右外连接
05-08 连接的语法结构
一、连接
1、MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
二、语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr
三、数据表参照
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
1、数据表可以使用tbl_name AS alias_name,或tbl_name alias_name赋予别名。
2、table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名。
四、连接条件
1、使用ON关键字来设定连接条件,也可以使用WHERE来代替。
2、通常使用功能ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤。
五、内连接
1、
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
六、左外连接
SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
七、右外连接
SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
05-11 多表连接
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id
05-12 关于连接的几点说明
一、外连接
A LEFT JOIN B join_condition
1、数据表B的结果集依赖数据表A。
2、数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。
3、左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)。
4、如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外的B行。
5、如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE 子句中尝试以下操作:col_name IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连执着条件的记录后停止搜索更多的行。
05-13 无限级分类表设计
一、无限级分类表
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
二、自身连接
同一个数据表对其自身进行连接。
SELECT p.type_id,p.type_name,s.type_name FROM tdbs_goods_types p LEFT JOIN IN tdb_goods_types s ON s.parent_id = p.type_id;
三、多表删除
DELETE tbl_name[.*] [,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]
第06章 运算符和函数
06-01 回顾和概述
一、内置函数库
1、字符函数
2、数值运算符与函数
3、比较运算符与函数
4、日期时间函数
5、信息函数
6、聚合函数
7、加密函数
06-02 字符函数
一、字符函数
函数名称 | 描述 | eg |
---|---|---|
CONCAT() | 字符连接 | |
CONCAT_WS() | 使用指定的分隔符进行字符连接 | SELECT CONCAT_WS('-','love','imooc'); |
FORMAT() | 数字格式化 | |
LOWER() | 转换成小写字母 | |
UPPER() | 转换成大写字母 | |
LEFT() | 获取左侧字符 | SELECT LEFT('MySQL', 2); |
RIGHT() | 获取右侧字符 | |
LENGTH() | 获取字符串长度 | SELECT LENGTH('MySQL'); |
LTRIM() | 删除前导空格 | SELECT LTRIM(' MySQL '); |
RTRIM() | 删除后续空格 | |
TRIM() | 删除前导和后续空格 | SELECT TRIM(LIADING '?' FROM '??MySQL???'); |
SUBSTRING() | 字符串截取 | |
[NOT] LIKE | 模式匹配 | 1、SELECT 'MySQL' LIKE 'M%'; 返回1。2、SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1'; |
REPLACE() | 字符串替换 | SELECT REPLACE('??My??SQL??', '??','!'); |
二、MySQL中字符串从1取,javascript中从0取。
三、%
(百分号):代表任意个字符,
_
(下划线): 代表任意一个字符。
06-03 MySQL数值运算符和函数
名称 | 描述 | eg |
---|---|---|
CEIL() | 进一取整 | SELECT CEIL(3.01); ,返回4 |
DIV | 整数除法 | SELECT 3 DIV 4; , 返回0 |
FLOOR() | 舍一取整 | |
WOD | 取余数(取模) | SELECT 5.3 MOD 3; , 等同于SELECT 5.3 % 3; |
POWER() | 幂运算 | |
ROUND() | 四舍五入 | |
TRUNCATE() | 数字截取 | 1、SELECT TRUNCATE(125.16, 1); ,返回125.1。2、SELECT TRUNCATE(125.16,0); 返回125 |
[NOT] BETWEEN…AND… | [不]在范围之内 | |
[NOT] IN() | [不]在列出值范围内 | |
IS [NOT] NULL | [不]为空 |
06-05 MySQL 日期时间函数
名称 | 描述 | eg |
---|---|---|
NOW() | 当前日期和时间 | |
CURDATE() | 当前日期 | |
CURTIME() | 当前时间 | |
DATE_ADD() | 日期变化 | SELECT DATE_ADD('2014-3-12', INTERVAL 3 WEEK); |
DATEDIFF() | 日期差值 | |
DATE_FORMAT() | 日期格式化 | SELECT DATE_FORMAT('2014-3-12', '%m/%d/%y') |
06-06 MySQL 信息函数
名称 | 描述 | eg |
---|---|---|
CONNECTION_ID() | 连接ID | SELECT CONNECTION_ID(); |
DATEBASE() | 当前数据库 | SELECT DATEBASE(); |
LAST_INSERT_ID() | 最后插入记录的ID号 | |
USER() | 当前用户 | |
VERSION() | 版本信息 |
06-07 聚合函数
名称 | 描述 | eg |
---|---|---|
AVG() | 平均值 | |
COUNT() | 计数 | |
MAX() | 最大值 | |
MIN() | 最小值 | |
SUM() | 求和 |
06-08 加密函数
名称 | 描述 | |
---|---|---|
MD5() | 信息摘要算法 | |
PASSWORD() | 密码算法 |
第07章 自定义函数
07-02 MySQL 自定义函数简介
一、自定义函数
用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径,其用法与内置函数相同。
二、自定义函数的两个必要条件
1、参数:可以有零个或多个
2、返回值:只能有一个返回值
三、函数可以返回任意类型的值,同样可以接收这些类型的参数。
四、创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
五、关于函数体
1、函数体由合法的SQL语句构成。
2、函数体可以是简单的SELECT或INSERT语句。
3、函数体如果未复合结构则使用BEGIN…END语句。
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
4、复合结构可以包含声明,循环,控制结构。
六、删除函数
DROP FUNCTION [IF EXISTS] function_name;
删除函数名为function_name的函数
第08章 MySQL存储过程
08-02 MySQL存储过程简介
一、MySQL命令的执行过程
二、存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。
三、存储过程的优点
1、增强SQL语句的功能和灵活性。
2、实现较快的执行速度。
3、减少网络流量
四、创建存储过程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCENURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
1、proc_parameter: (参数)
[IN | OUT | INOUT] param_name type
(1)IN,输入类型,表示该参数的值必须在调用存储过程时指定。
(2)OUT,输出类型,表示该参数的值可以被存储过程改变,并且可以返回。
(3)INOUT,输入&&输出,表示该参数的调用时指定,并且可以被改变和返回。
五、修改存储过程
ALTER PROCEDURE sp_name [characteristic ...]
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY { DEFINER | INVOKER }
1、COMMENT:注释
2、CONTAINS SQL :包含SQL语句,但不包含读或写数据的语句。
3、NO SQL:不包含SQL语句。
4、READS SQL DATA:包含读数据的语句。
5、MODIFIES SQL DATA:包含写数据的语句。
6、SQL SECURITY {DEFINER | INVOKER } 指明谁有权限来执行。
7、不能修改存储过程体,只能先删除存储过程,再重新创建。
六、删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
七、存储过程过程体
1、过程体由合法的SQL语句构成。
2、过程体可以是任意SQL语句。
3、过程体如果为复合结构则使用BEGIN…END语句。
4、复合结构可以包含声明,循环,控制结构。
八、调用存储过程
1、CALL sp_name([parameter[,...]])
2、CALL sp_name[()]
九、
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p.id;
END
//
DELIMITER ;
CALL removeUsersById(22);
十、局部变量、用户变量
1、BETWEEN…AND之间的变量是局部变量
2、通过SELECT 语句,或SELECT…INTO…以及SET @符号所声明的变量称为用户变量,是跟用户的客户端绑定的。
十一、ROW_COUNT()
来得到插入、删除以及更新的,被影响到的记录总数。
十二、存储过程与自定义函数的区别
1、存储过程实现的功能要复杂一些,而函数的针对性更强。
2、存储过程可以返回多个值;函数只能有一个返回值。
3、存储过程一般独立地来执行;而函数可以作为其他SQL语句的组成部分来实现。
十三、注意事项
1、创建存储过程或者自定义函数时需要通过delimiter语句修改定界符。
2、如果函数体或过程体有多个语句,需要包含在BEGIN…END语句块中。
3、存储过程通过call来调用。
第09章 MySQL存储引擎
09-02 MySQL 存储引擎简介
一、查看数据表的创建命令
SHOW CREATE table tbl_name
二、存储引擎
1、MySQL可以将数据不同的技术存储在文件(内存)中,这种技术就成为存储引擎。
2、每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
三、MySQL支持的存储引擎
1、MyISAM
2、InnoDB
3、Memory
4、CSV
5、Archive
四、相关知识点
1、并发控制
当多个连接对记录进行修改时保证数据的一致性和完整性。
(1)锁
①共享锁(读锁)
在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。
②排他锁(写锁)
在任何时候只能有一个用户写入资源的,当进行写锁时会阻塞其他的读锁或者写锁操作。
(2)锁颗粒
①表锁,是一种开销最小的锁策略。
②行锁,是一种开销最大的锁策略。
2、事务
事务用于保证数据库的完整性
(1)eg
用户A给用户B转账200元。
实现步骤:
①从当前账户减掉200元(账户余额大于等于200元)
②在对方账户增加200元。
(2)事务的特性
①原子性(Atomicity)
②一致性(Consistency)
③隔离性(Isolation)
④持久性(Durability)
3、外键
是保证数据一致性的策略。
4、索引
是对数据表中一列或多列的值进行排序的一种结构。
09-06 MySQL 各个存储引擎特点
一、各种存储引擎的特点
特点 | MyISAM | InnoDB | Memory | Archive |
---|---|---|---|---|
存储限制 | 256TB | 64TB | 有 | 无 |
事务安全 | - | 支持 | - | - |
支持索引 | 支持 | 支持 | 支持 | |
锁颗粒 | 表锁 | 行锁 | 表锁 | 行锁 |
数据压缩 | 支持 | - | - | 支持 |
支持外键 | - | 支持 | - | - |
二、索引:普通索引、唯一索引、全文索引、btree索引、hash索引…
三、CSV存储引擎不支持索引。
四、BlackHole:黑洞引擎,写入的数据都会消失,一般用于做数据复制的中继。
五、MyISAM:适用于食物的处理不多的情况。
InnoDB:适用于事务处理比较多,需要有外键支持的情况。
六、修改存储引擎的方法
1、通过修改MySQL配置文件实现
default-storage-engine = engine
2、通过创建数据表命令实现
CREATE TABLE table_name(
...
) ENGINE = engine;
3、通过修改数据表命令实现
ALTER TABLE table_name ENGINE [=] engine_name;
七、存储引擎
1、MyISAM:存储限制可达256TB,支持索引、表级锁定、数据压缩。
2、InnoDB:存储限制为64TB,支持事务和索引,锁颗粒为行锁。
第10章 MySQL图形化管理工具
10-01 课程介绍
一、管理工具
1、PHPMyAdmin
2、Navicat
3、MySQL Workbench