Mysql学习记录小结

一、基本操作

1.启动mysql

net start mysql

2.停止mysql

net stop mysql

3.登陆mysql

mysql -uroot -proot -P3306 -h127.0.0.1 --prompt \u@\h \d
root@localhost user> 

prompt 操作符参数 \D 完整日期 \d 当前数据库 \h 服务器名称 \u 当前用户

4.退出mysql

exit; 
quit; 
\q;

5.常用命令

1.显示当前服务器版本
SELECT VERSION();

2.显示当前日期时间
SELECT NOW();

3.显示当前用户
SELECT USER();

4.客户端展示的编码格式
SET NAMES GBK;

5.返回影响的数据数量
SELECT ROW_COUNT();

6.设置结束符 默认为; 防止与MySql语句冲突
DELIMITER 

6.数据库操作

1.创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

2.查看当前服务器下的数据表列表
SHOW {DATABASE | SCHEMA} [LIKE 'pattern' | WHERE expr]

3.修改数据库编码
ALTER {DATABASE | SCHEMA} [db_name] [DEFULT] CHARACTER SET [=] charset_name

4.删除数据库
DROP {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

5.打开数据库
USE {DATABASE}

7.标准规范

• 1.关键字与函数名称全部大写
• 2.数据库名称，表名称，字段名称全部小写
• 3.sql语句必须以分号结尾

二、数据类型与操作数据表

1.数据类型

1.整型

整型.png

2.浮点型

浮点型.png

3.时间型

时间型.png

4.字符型

字符型.png

2.修饰属性字段

1.空值与非空 NULL, NOT NULL

2.自动编号(且必须与主键组合使用) AUTO_INCREMENT

3.主键约束 PRIMARY KEY

4.唯一约束 UNIQUE KEY

5.默认值 DEFULE 

6.外键 FOREIGN KEY

3.数据表操作

1.创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
 column_name data_type,
 ...
 )
 
2.查看数据表
SHOW TABLE [FROM db_name] [LIKE 'pattern' | WHERE expr]

3.查看数据表创建结构
SHOW CREATE TABLE db_name

4.查看数据表结构
SHOW COLUMNS FROM tbl_name

5.数据表更名
 (1). ALTER TABLE tbl_name RENAME [TO | AS] new_col_name
 (2). RENAME TABLE tbl_name TO new_tbl_name[,tbl_name2 TO new_tbl_name2]

创建一张数据表例子如下:

CREATE TABLE [IF NOT EXISTS] users(
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(20) NOT NULL UNIQUE KEY,
 sex ENUM(1,2,3) DEFAULT 3,
 pid SMALLINT,
 FOREIGN KEY (pid) REFERENCES provinces (id)
 )

三：约束以及修改数据表

1.约束

NOT NULL (非空约束)

PRIMARY KEY (主键约束)

UNIQUE KEY (默认约束)

DEFULT (默认约束)

FOREIGN KEY (外键约束)

2.外键约束的参照操作

• 1.CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行
• 2.SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定为NOT NULL
• 3.RESTRICT: 拒绝对父表的删除或更新操作
• 4.NO ACTION: 标准SQL的关键字，在MySQL中与RESTRICT相同

3.表列属性操作

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 tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...)

5.添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name, ...)

6.添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definiton

7.添加／删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFULT}

8.删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY

9.删除唯一约束
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

10.删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

11.修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

12.修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

四：操作数据表中的记录

1.INSERT 插入记录

1. 标准的插入语句
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},..),(...),...

2. 此方法可以使用子查询(SubQuery)
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...

3.此方法可以将查询的结果插入到指定的数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

2.UPDATE 更新记录

UPDATE [LOW_PRIRITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]

3.DELETE 删除记录

DELECE FROM tbl_name [WHERE where_condition]

4.SELECT 查询记录

SELECT select_expr [, select_expr ...]
[
   FROM table_references
   [WHERE where_condition]
   [GROUP BY {col_name | position} [ASE | DESE], ...]
   [HAVING where_condition]
   [ORDER BY {col_name | expr | position} [ASE | DESE], ...]
   [LIMIT {[offset,] row_cont | row_cont OFFSET offset}]
]

5.WHERE 过滤条件

6.GROUP BY 对查询结果进行分组

[GROUP BY {col_name | position} [ASC | DESC], ...]

7.HAVING 分组条件

[HAVING where_condition]

8.ORDER BY 对查询结果进行排序

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

9.LIMIT 限制查询结果返回的数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

10.UNION 合并两个或多个 SELECT 语句的结果集

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

11.DISTINCT 方法用于返回唯一不同的值

SELECT DISTINCT select_expr [, select_expr ...] FROM table_references;

五：子查询与连接

1.子查询

(1).子查询定义

子查询（Subquery）是指出现在其他SQL语句内的SELECT子句。

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.

(2).使用比较运算符的子查询

=, >, <, >=, <=, <>, !=, <=>

1.语法结构 operand comparison_operstor subquery

2.使用ANY, SOME, ALL修饰的比较运算符

operand comparison_operstor ANY (subquery)
operand comparison_operstor SOME (subquery)
operand comparison_operstor ALL (subquery)

(3).使用[NOT] IN 的子查询

语法结构 operand comparison_operstor [NOT] IN (subquery)

=ANY运算符与IN等效。
!=ALL或<>ALL运算符与NOT IN等效.

(4).使用[NOT] EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE;否则为FALSE.

(5).子查询的应用

1.将查询的结果写入数据表

INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...

2.多表更新

UPDATE table_references
SET col_name1 = {expr1 | DEFAULT}
[, col_name2 = {expr1 | DEFAULT)] ...
[WHERE where_conditon]

3.多表更新之一步到位

CREATE...SELECT
创建数据表同时将查询的结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition, ...)]
select_statement

2.连接

(1).基本定义

table_reference {([INNER | CROSS] JOIN)|({LEFT | RIGHT} [OUTER] JOIN)}
table_reference ON conditional_expr

(2).数据表参照 AS

table_reference tbl_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用 tbl_name AS alias_name 或 tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名.

(3).连接类型

• INNER JOIN, 内连接(JOIN, CROSS JOIN, INNER JOIN等价)

• LEFT [OUTER] JOIN, 左外连接

• RIGHT [OUTER] JOIN, 右外连接

• ON关键字设定连接条件 WHERE关键字进行结果记录的过滤

• 内连接仅显示符合连接条件的记录

• 左外连接显示左表的全部记录及右表符合连接条件的记录

• 右外连接显示右表的全部记录及左表符合连接条件的记录

(4).多表连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tbd_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS a ON g.brand_id = b.brand_id\G

注：将 tbd_goods表中cate_id与tdb_goods_cates表中cate_id相等条件数据进行内连接 并输出tdb_goods_cates中的cate_name字段，
   将 tbd_goods表中brand_id与tdb_goods_brands表中brand_id相等条件数据进行内连接 并输出tdb_goods_brands中的brand_name字段，其中两个内连接是等价的,不存在依存关系

(5).无限制分类表设计（自身连接）

CREATE TABLE tab_goods_types(
  type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  type_name VARCHAR(20) NOT NULL,
  parent_id SMALLINT UNSIGNED NOT NULL DEFAULE 0
);

SELECT s.type_id,s.type_name,p.type_name FROM tbd_goods_types AS s LEFT JOIN tbd_goods_types AS p ON s.parent_id = p.type_id\G 

(6).多表删除

DELETE tbl_name[.*] [,tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

例子：删除一张表中goods_id大的重复的数据
1.找出一张表中重复的记录
SELECT goods_id,goods_name FROM tbd_goods GROUP BY goods_name Having count(goods_name) >= 2;

DELETE t1 FROM tbd_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tbd_goods GROUP BY goods_name Having count(goods_name) >= 2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id

六：运算符和函数

(1).字符函数

CONCAT()                字符连接
COUCAT_WS()             使用指定的分隔符进行字符连接
FORMAT()                数字格式化
LOWER()                 转换成小写字母
UPPER()                 转换成大写字母
LEFT()                  获取左侧字符
RIGHT()                 获取右侧字符
LENGTH()                获取字符串长度
LTRIM()                 删除前导空格
RTRIM()                 删除后续空格
TRIM()                  删除前导和后续空格
SUBSTRING()             字符串截取
[NOT] LIKE              模式匹配
REPLACE()               字符串替换

(2).数值运算符与函数

CEIL()                  进一取整DIV                     整数除法
FLOOR()                 舍一取整
MOD                     取余数（取模）
POWER()                 幂运算
ROUND()                 四舍五入
TRUNCATE()              数字截取

(3).比较运算符与函数

[NOT] BETWEEN...AND... [不]在范围之内
[NOT] IN()             [不]在列出的值范围内
IS [NOT] NULL          [不]为空

(4).日期时间函数

NOW()                  当前日期和时间
CURDATE()              当前日期
CURTIME()              当前时间
DATE_ADD()             日期变化 
DATEDIFF()             日期差值
DATE_FORMAT()          日期格式化

(5).信息函数

CONNECTION_ID()        连接ID
DATEBASE()             当前数据库 
LAST_INSERT_ID()       最后插入记录的ID号
USER()                 当前用户
VERSION()              版本信息

(6).聚合函数

AVG()                  平均值
COUNT()                计数
MAX()                  最大值
MIN()                  最小值
SUM()                  求和

(7).加密函数

MD5()                  信息摘要算法
PASSWORD()             密码算法

七：自定义函数UDF

(1).创建自定义函数

基本定义:
CREATE FUNCTION function_name 
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body

不带参数的函数：
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点:%i分:%s秒');

创建带有参数的自定义函数:
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED,) RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;

创建具有复合机构函数体的函数 
DELIMITER //  设置结束符 默认为; 防止与MySql语句冲突
CREATE FUNCTION adduser(username VARCHAR(20)) 
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//

(2).删除函数

DROP FUNCTION [IF EXISTS] function_name

八：MySQL存储过程

(1).创建存储过程

CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type
注:IN 表示该参数的值必须在调用过程时指定
OUT 表示该参数的值可以被存储过程改变，并且可以返回
INOUT 表示该参数的调用时指定，并且可以被改变和返回

1.创建不带参数的存储过程
CREATE PROCeDURE sql() SELECTION VERSION();
调用: CALL sql;

1.创建带有IN类型参数的存储过程
DELIMITER //  设置结束符 默认为; 防止与MySql语句冲突
CREATE PROCeDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELECTE FROM users WHERE id = p_id;
END
//
调用: CALL removeUserById(3);

2.创建带有IN和OUT类型参数的存储过程
CREATE PROCeDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELECTE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
//
调用: CALL removeUserAndReturnUserNums(27,@nums);
使用返回值: SELECT @nums;
SET @i = 7; @nums, @i为用户变量，全局变量

3.创建带有多个OUT类型参数的存储过程
CREATE PROCeDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT delectUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELECTE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO delectUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
//
调用: CALL removeUserByAgeAndReturnInfos(27,@a,@b);

(2).修改存储过程

ALTER PROCEDURE sp_name [characteristic ...]
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
| SQL SECURITY {DEFINER | INVOKER}

CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含写数据的语句
SQL SECURITY {DEFINER | INVOKER}:指明谁有权限来执行

(3).删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

(4).调用存储过程

CALL sp_name([parameter[,...]])
CALL sp_name[()]

九：MySQL存储引擎

(1).主要存储引擎类型

特点     MyISAM InnoDB Memory Archive
存储限制  256TB  64TB   有     无
事务安全  -      支持    -      -
支持索引  支持    支持   支持    支持
锁颗粒    表锁   行锁    表锁    行锁
数据压缩  支持    -      -      支持
支持外键  -      支持    -      -

(2).性能详解

1.并发处理

并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性
锁：     共享锁（读锁）   排他锁（写锁）
锁颗粒： 表锁，开销最小的锁策略； 行锁，开销最大的锁策略；

2.事务处理

事务处理：保证数据库的完整性
事务的特性：
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

3.外键和索引

外键：是保证数据一致性的策略
索引：是对数据表中一列或多列的值进行排序的一种结构

4.修改存储引擎

通过创建数据表命令实现
CREATE TABLE table_name(
...
...
) ENGING = engine_name;

通过修改数据表命令实现
ALTER TABLE table_name ENGINE [=] engine_name;