20170708――20170807
注：cmd需要以管理员身份运行
1，启动与关闭
net start mysql ，
net stop mysql
2，登录与退出
mysql -u(username) -p(password) -P(端口号） -h(服务器地址），
exit; quit; \q;
默认端口号：3306，本机服务器地址：127.0.0.1
3，修改MySQL提示符
①连接客户端时 ：mysql -u(username) -p(password) --prompt 提示符
②连接客户端后 ：mysql>prompt 提示符
4，常用命令
SELECT VERSION(); 显示当前服务器版本
SELECT NOW(); 显示当前日期时间
SELECT USER(); 显示当前用户
SELECT DATABASE(); 显示当前打开的数据库
5，语句规范
① 关键字与函数名称全部大写
② 数据库名称，表名称，字段名称全部小写
③ SQL语句必须以分号结尾
6，创建数据库
CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
7，查看当前服务器下的数据库列表
SHOW DATABASES;
8，修改数据库
ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;
9，删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
10，数据类型
整形 ：
浮点型
日期
字符型
定长：用空格补齐
L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时，MySQL要把数据内容和数据长度都保存起来。
11，创建数据表：先打开数据库 USE db_name
CREATE TABLE [IF NOT EXISTS] tb_name(column_name data_type,...)
column_name:列名称，data_type:数据类型
12，查看数据表
SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHERE expr]
13，查看数据表结构
SHOW COLUMNS FROM tb_name;
14，记录插入(为行内容赋值)
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...);
(col_name,...)若省略则需要写入所有要插入的字段
15，记录的查找(查看赋值结果如何)
SELECT expr,...FROM tb_name;
16，自动编号
AUTO_INCREMENT
必须与主键组合使用，起始值为1，每次增量为1；类型可以为整数或者浮点数，浮点数的小数部分必须为0
17，主键约束 PRIMARY KEY
① 一张数据表中只能有一个主键
② 主键保证记录的唯一性
③ 主键自动为NOT NULL
自动编号不需要赋值，主键约束可以赋值，但是不允许重复
18，唯一约束 UNIQUE KEY
① 唯一约束可以保证记录的唯一性
② 唯一约束的字段可以为空值
③ 每张数据表可以存在多个唯一的约束
唯一约束不允许有多个空值
19，默认约束 DEFAULT
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
eg：
CREATE TABLE tb1(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
20，根据约束所针对的字段的数目可以分为 表级约束（>=2字段）和列级约束（1字段）
21，根据功能将约束分为
NOT NULL (非空约束)
PRIMARY KEY (主键约束)
UNIQUE KEY (唯一约束）
DEFAULT (默认约束）
FOREIGN KEY (外键约束)
22，外键约束 FOREIGN KEY（chl_col_name）REFERENCES fath_tb_name(fath_col_name)
23，表级约束与列级约束
（1）对一个数据列建立的约束，称为列级约束
（2）对多个数据列建立的约束，称为表级约束
（3）列级约束既可以在列定义时声明，也可以在列定以后声明
（4）表级约束只能在列定义后声明
24，修改数据表（改）
添加单列 ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
添加多列 ALTER TABLE tb_name ADD [COLUMN] (col_name column_definition,...);
删除列 ALTER TABLE tb_name DROP [COLUMN] col_name;
添加主键约束 ALTER TABLE tb1_name add [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
添加唯一约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUWE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)
添加外键约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_col_name,...) reference_definition
添加/删除默认约束 ALTER TABLE tbl_name ALTER [COLUMN] col_name{SET DEFAULT literal | DROP DEFAULT }
删除主键约束 ALTER TABLE tbl_name DROP PRIMARY KEY
删除唯一约束 ALTER TABLE tbl_name DROP {INDEX | KEY } index_name
删除外键约束 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
修改列定义 ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改列名称 ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
数据表更名
① ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
② RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
插入记录（增）
① INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
② INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},... 与第一种方式的区别在于，法②可以使用子查询（SubQuery），且一次只能插入一条记录
③ INSERT [INTO] tbl_name [(col_name,...)] SELECT ... 从一个数据表导入
更新记录（单表更新）
UPDATE [LOW_PRIORITY][IGNORE] tbale_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]
删除记录（单表删除）（删）
DELETE FROM tbl_name [WHERE where_condition]
25, SELECT:
SELECT select_expr[,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name|position} [ASC|DESC],...]
[HAVING where_condition]
[ORDER BY {col_name|expr|position}[ASC|DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
select_expr(查询表达式)
每一个表达式表示想要的一列，必须有至少一个
多个列之间以逗号分隔
星号*表示所有列，tbl_name.*表示命名表的所有列
查询表达式可以使用[AS] alias_name为其赋予别名
别名可用于GROUP BY ， ORDRE BY 或 HAVING 字句
26，查询结果的操作（查）
where的条件查询
GROUP BY的查询结果分组
[GROUP BY {col_name | position} [ASC | DESC],...]
ASC表示升序，DESC表示降序
HAVING 的分组条件 [HAVING where_condition]
eg:
SELECT id，age FROM tb_name GROUP BY id HAVING expr;
expr中出现的字段必须为聚合函数或者在前面的SELECT语句中出现的字段，该例中只能为id，age或者其他聚合函数。
聚合函数为max（）min（）avg（）sum（）count（）等，只有一个返回值。
ORDER BY 对查询结果进行排序
[ORDER BU {col_name | expr | position} [ASC | DESC],...]
eg: SELECT * FROM tb_name ORDER BY id DESC; //按id的降序排列
LIMIT 限制查询结果返回的数量
[LIMIT {[offset,]row_count | row_count OFFSET offset}]
eg: SELECT * FROM tb_name LIMIT 1; //只显示第一条
SELECT * FROM tb_name LIMIT 3,2; //显示第四条和第五条，排序时从0开始的
27， 修改客户端显示的内容编码(不影响数据表的内容）
SET NAMES gbk/utf8...
28 ， 子查询 指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以包含多个关键字或条件，如 DISTINCT，GROUP BY，ORDER BY，LIMIT 函数等。子查询的外层查询可以是：SELECT，INSERT，UPDATE，SET或DO。子查询可以返回标量，一行，一列，或者一个子查询
分类：
① 使用比较运算符的子查询 = ,>=, < ,<= ,<> ,!= ,<=>
语法结构 operand comparison_operator subquery
eg:
SELECT ROUND(AVG(goods_price),2) FROM tbd_goods; //对价格字段求平均值，四舍五入保留两位，不写2，默认保留整数。
SELECT goods_id,goods_price,goods_name FROM tbd_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbd_goods) //用子查询查找数据表中价格大于价格平均值的项
用ANY，SOME，ALL修饰的比较运算符
operand comparsion_operator ANY (subquery)
operand comparsion_operator SOME (subquery)
operand comparsion_operator ALL (subquery)
② 使用[NOT] IN
语法结构 operand comparison_operator [NOT] IN (subquery)
ANY 运算符与IN等效，!=ALL或<>ALL 与NOT IN等效
③ 使用[NOT] EXISTS 的子查询，如果子查询返回任何行，EXISTS将返回TRUE，否则为FALSE。（不常用）
29 ，INSERT ... SELECT 将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
30 , 多表更新
UPDATE table_references SET col_name1 = {expr | DEFAULT} [,col_name2 = {expr2 | DEFAULT} ... [WHERE where_condition]
table_references : {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
31 , CREATE ... SELECT 创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [create_definition,...]select_statement
32 , 连接
①{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
②table_reference
③ON conditional_expr
JOIN
INNER JOIN：内连接和交叉连接等价，都是笛卡尔积运算
CROSS JOIN：交叉连接
LEFT [OUTER] JION 左连接：是根据最左表的记录，在被连接右表中找出符合条件的记录与之匹配，找不到与左表匹配的，用null填充。
RIGHT [OUTER] JOIN 右连接：是根据最右表的记录，在被连接左表中找出符合条件的记录与之匹配，找不到与右表匹配的，用null填充。
连接类型：
INNER JOIN，内连接。在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接
连接条件：使用On关键字来设定连接条件，也可以用WHERE来代替。通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤
内连接：显示左表及右表符合连接条件的记录
左外连接：显示左表的全部及右表中符合条件的记录
右外连接：显示右表的全部及左表中符合条件的记录
多表连接
SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1
INNER JOIN tbl_name2 AS t2 ON join_condition
INNER JOIN tbl_name3 AS t3 ON join_condition\G;
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
-> INNER JOIN tdb_goods_cate AS c ON g.cate_id=c.cate_id
-> INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;
连接三个表联合显示查询结果，注意不同的INNER JOIN中间没有逗号！！没有逗号！！没有逗号！！
显示的结果和商品表tdb_goods的记录一样，不过之前是通过单表的查询来显示，这次是通过三张表的连接来显示。
多表的连接实际上是外键的逆向约束。外键把数据分开存储，多表连接又把数据联系在一起。
多表删除
DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition]
子查询与连接总结：
子查询:出现在mysql语句的select子句，而子查询必须要出现在小括号()内,它的外层可以是select语句，insert语句，update语句，delete语句，而且在子查询中可以包含多个关键词:group by,order by,limit以及相关函数。。。引发子查询的情况有比较运算符，(Not)in运算符，exist(Not exists)引发的子查询
连接:连接分为外连接和内连接，内连接主要显示的是左表和右表当中都符合连接条件的记录。外链接有分为左外连接和右外连接，左外连接显示的是左表中的全部记录和右表符合条件的记录，右外连接和左外连接相反
33，字符函数
CONCAT(); 连接多个字段
eg（面试题）：SELECT CONCAT(first_name,'_',last_name) AS full_name FROM test;
CONCAT_WS(); 用某个分隔符连接多个字段
eg：SELECT CONCAT_WS('_',first_name,last_name,...);
FORMAT() 数字格式化（返回类型为字符型）
eg: SELECT FORMAT(12530.75,2) //12,530.75
SELECT FORMAT(12530.75,1) //12,530.8
SELECT FORMAT(12530.75,0) //12,531
UPPER() 转换成大写字母
LOWER() 转换成小写字母
eg：SELECT UPPER('MySQL') //MYSQL
SELECT LOWER(;MySQL') //mysql
LEFT() 获取左侧字符串
RIGHT() 获取右侧字符串
eg:SELECT LEFT('mysql',2) //my
SELECT RIGHT('mysql',3) //sql
SELECT UPPER(LEFT('mysql',2)) //MY 函数嵌套
SELECT CONCAT(UPPER(LEFT('mysql',2)),LOWER(RIGHT('mysql',3))) //MYsql 函数嵌套
LENGTH() 获取字符串长度
eg: SELECT LENGTH('MY SQL') //6
LTRIM() 删除字符串的前导空格
RTRIM() 删除字符串的后续空格
TRIM() 删除字符串的前导和后续空格
eg: SELECT LENGTH(LTRIM(' mysql ')) //9
SELECT LENGTH(RTRIM(' mysql ')) //7
SELECT LENGTH(TRIM(' MYSQL ')) //5
SELECT TRIM(LEADING '?' FROM '???MYSQL??') //MYSQL?? 删除前导指定字符
SELECT TRIM(TRAILING '?' FROM '???MYSQL??')//???MYSQL 删除后续指定字符
SELECT TRIM(BOTH '?' FROM '???MYSQL??') //MYSQL 删除前导和后续的指定字符
REPLACE 替换指定字符
eg: SELECT REPLACE('??MY??SQL???','?','') //MYSQL
SUBSTRING 从某字符串的某个位置开始截取指定长度的字符
eg: SELECT SUBSTRING('MYSQL',1,2); //MY 从1开始计数
SELECT SUBSTRING('MYSQL',2); //YSQL
SELECT SUBSTRING('MYSQL',-2); //QL从右往左数
SELECT SUBSTRING('MYSQL',-1,-2); //输出空
[NOT]LIKE 模式匹配
eg: SELECT 'MYSQL' LIKE 'M%' //1 即true，MySQL中的%为通配符，类似Windows下的*，%代表任意字符，_代表任意一个字符
SELECT 'MYSQL' LIKE 'm%' //1 不区分大小写
34，数值运算函数
CEIL() 向上取整
FLOOR() 向下取整
DIV 整除运算
MOD 取模
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
35 ，比较运算符与函数
[NOT] BETWEEN AND(); //（不）在某个范围内
eg：SELECT 15 BETWEEN 1 AND 22; //1(true),包括两边 [1,22]
SELECT 15 BETWEEN 15 AND 15; //1(true)
[NOT] IN(); //(不)在列出值范围内
eg：SELECT 15 IN(10,15,20); //1(true)
SELECT 15 IN(10,12,20); //0(true)
IS [NOT] NULL; //(不)为空
eg：SELECT NULL IS NULL; //1(true)
SELECT '' IS NULL; //0(false)
36 , 日期时间函数
NOW(); //当前日期和时间
CURDATE(); //当前日期
CURTIME(); //当前时间
DATE_ADD(); //日期的变化
eg：SELECT DATE_ADD('2017-7-25',INTERVAL 365 DAY); //2018-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL -365 DAY); //2016-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL 48 HOUR); //2017-07-27 00:00:00
SELECT DATE_ADD('2017-7-25',INTERVAL 48*60 MINUTE); //2017-07-27 00:00:00
DATE_DIFF() //日期差值
eg：SELECT DATEDIFF('2019-6-10','2015-6-8'); //1463
DATE_FORMAT() //日期格式化
eg：SELECT DATE_FORMAT('2017-7-25','%m/%d/%Y'); //07/25/2017
37 , 信息函数
CONNECTION_ID() //连接ID
eg：SELECT CONNECTION_ID(); //8（服务器不同输出不同）
DATABASE(); //当前数据库
eg：SELECT DATABASE();
LAST_INSERT_ID() //最后插入记录的ID号。 表中必须存在一个自动编号的字段，设置为主键，名字不一定叫ID。而且如果最后一条记录一次插入了多个数据，显示的是插入的多个数据的第一个数据的ID号
USER(); //当前用户
38 ， 聚合函数
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
39 ，加密函数
MD5()：信息摘要算法，为以后的Web页面做准备，尽量使用MD5()
eg: SELECT MD5('admin');
PASSWORD()：密码算法，通过PASSWORD()修改(重要用于MYSQL数据库)当前用户和其他用户的密码，修改客户端自己的密码
eg: SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。
40 , 自定义函数(udf user-define-function)：
两个必要条件：a，参数，b，返回值
创建自定义函数：
CREATE FUNCTION function_name RETURNS{STRING|INTEGER|REAL|DECIMAL} routine_body
eg：
CREATE FUNCTION F1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点：%i分：%s秒');
SELECT F1(); //2017年07月25日 16点：33分：01秒 输出当前时间并格式化
创建带参数的自定义函数：
eg：
CREATE FUNCTION f2（num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED）
RETURNS FLOAT(10，2) UNSIGNED
RETURN(num1+num2)
SELECT f2(10,15) //12.5
删除函数:
DROP [IF EXISTS]FUNCTION fun_name;
修改分隔符： DELIMITER 分隔符
41，存储过程：存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理
优点：
a）增强SQL语句的功能和灵活性
b）实现较快的执行速度
c）减少网络流量
创建存储过程 CREATE [DEFINER = {user| CURRENT_USER}] PROCEDURE sp_name([proc_parameter[,...]]) [characteristic ...]routine_body
proc_parameter: [IN|OUT|INOUT] param_name type
IN:表示该参数的值必须在调用存储过程时指定
OUT：表示该参数的值可以被存储过程改变，并且可以返回
INOUT：表示该参数的调用时指定，并且可以被改变和返回
eg: CREATE PROCEDURE sp() SELECT VERSION();
调用存储过程：
CALL sp_name([parameter[,...]])
CALL sp_name[()]
eg:CALL sp();
修改存储过程：只可以修改参数类型和注释，过程体不可以修改,只能删除再重建。
ALTER ORICEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}
删除存储过程： DROP PRICEDURE[IF EXISTS] sp_name;
eg： CREATE PROCEDURE removeUsersAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
CALL removeUsersAndReturnUserNums(10,@nums); //变量
SELECT @nums; //输出剩余条目数
SET @nums = 7； //定义用户变量，只对当前客户端有效
SELECT ROW_COUNT(); //系统函数；得到插入，删除，更新 影响的记录总数
eg：INSERT test（username）VALUES('A'),('B'),('C');
SELECT ROW_COUNT(); //3
UPDATE test SET username = CONCAT(username,'--CHINA') WHERE id<=2;
SELECT ROW_COUNT(); //2
42，存储引擎：可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制，索引技巧，锁定水平，最终提供广泛且不同的功能
查看数据表的存储引擎： SHOW CREATE TABLE tb_name;
mysql支持的存储引擎：
MySAM（常用）：存储限制可达256TB，支持索引，表级锁定，数据压缩
InnoDB（常用）： 限制为64TB，支持事务和索引，锁颗粒为行锁
Memory
CSV
Archive
相关知识点：
①并发处理：
并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性。对于并发读或者并发写，系统采用“锁”系统解决
a）锁：
共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。
b）锁颗粒：
表锁：是一种开销最小的锁策略。
行锁：是一种开销最大的锁策略。
②事务处理：
事务：用于保证数据库的完整性。
事务的特性：
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
③外键：是保证数据一致性的策略
④索引：是对数据表中一列或多列的值进行排序的一种结构。是快速定位的一种方法
普通索引
唯一索引
全文索引
btree索引
hash索引
...
修改存储引擎的方法
①通过修改MySQL配置文件实现 ———— default-storage-engine = engine
②通过创建数据表命令实现 ———— CREATE TABLE table_name（ ... )ENGINE = engine;
③通过修改数据表命令实现 ———— ALTER TABLE table_name ENGINE [=] engine_name;
43，数据库管理工具：PHPMyAdmin，Navicat，MySQL Workbench
注：cmd需要以管理员身份运行
1，启动与关闭
net start mysql ，
net stop mysql
2，登录与退出
mysql -u(username) -p(password) -P(端口号） -h(服务器地址），
exit; quit; \q;
默认端口号：3306，本机服务器地址：127.0.0.1
3，修改MySQL提示符
①连接客户端时 ：mysql -u(username) -p(password) --prompt 提示符
②连接客户端后 ：mysql>prompt 提示符
4，常用命令
SELECT VERSION(); 显示当前服务器版本
SELECT NOW(); 显示当前日期时间
SELECT USER(); 显示当前用户
SELECT DATABASE(); 显示当前打开的数据库
5，语句规范
① 关键字与函数名称全部大写
② 数据库名称，表名称，字段名称全部小写
③ SQL语句必须以分号结尾
6，创建数据库
CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
7，查看当前服务器下的数据库列表
SHOW DATABASES;
8，修改数据库
ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;
9，删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
10，数据类型
整形 ：
浮点型
日期
字符型
定长：用空格补齐
L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时，MySQL要把数据内容和数据长度都保存起来。
11，创建数据表：先打开数据库 USE db_name
CREATE TABLE [IF NOT EXISTS] tb_name(column_name data_type,...)
column_name:列名称，data_type:数据类型
12，查看数据表
SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHERE expr]
13，查看数据表结构
SHOW COLUMNS FROM tb_name;
14，记录插入(为行内容赋值)
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...);
(col_name,...)若省略则需要写入所有要插入的字段
15，记录的查找(查看赋值结果如何)
SELECT expr,...FROM tb_name;
16，自动编号
AUTO_INCREMENT
必须与主键组合使用，起始值为1，每次增量为1；类型可以为整数或者浮点数，浮点数的小数部分必须为0
17，主键约束 PRIMARY KEY
① 一张数据表中只能有一个主键
② 主键保证记录的唯一性
③ 主键自动为NOT NULL
自动编号不需要赋值，主键约束可以赋值，但是不允许重复
18，唯一约束 UNIQUE KEY
① 唯一约束可以保证记录的唯一性
② 唯一约束的字段可以为空值
③ 每张数据表可以存在多个唯一的约束
唯一约束不允许有多个空值
19，默认约束 DEFAULT
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
eg：
CREATE TABLE tb1(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
20，根据约束所针对的字段的数目可以分为 表级约束（>=2字段）和列级约束（1字段）
21，根据功能将约束分为
NOT NULL (非空约束)
PRIMARY KEY (主键约束)
UNIQUE KEY (唯一约束）
DEFAULT (默认约束）
FOREIGN KEY (外键约束)
22，外键约束 FOREIGN KEY（chl_col_name）REFERENCES fath_tb_name(fath_col_name)
23，表级约束与列级约束
（1）对一个数据列建立的约束，称为列级约束
（2）对多个数据列建立的约束，称为表级约束
（3）列级约束既可以在列定义时声明，也可以在列定以后声明
（4）表级约束只能在列定义后声明
24，修改数据表（改）
添加单列 ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
添加多列 ALTER TABLE tb_name ADD [COLUMN] (col_name column_definition,...);
删除列 ALTER TABLE tb_name DROP [COLUMN] col_name;
添加主键约束 ALTER TABLE tb1_name add [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
添加唯一约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUWE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)
添加外键约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_col_name,...) reference_definition
添加/删除默认约束 ALTER TABLE tbl_name ALTER [COLUMN] col_name{SET DEFAULT literal | DROP DEFAULT }
删除主键约束 ALTER TABLE tbl_name DROP PRIMARY KEY
删除唯一约束 ALTER TABLE tbl_name DROP {INDEX | KEY } index_name
删除外键约束 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
修改列定义 ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改列名称 ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
数据表更名
① ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
② RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
插入记录（增）
① INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
② INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},... 与第一种方式的区别在于，法②可以使用子查询（SubQuery），且一次只能插入一条记录
③ INSERT [INTO] tbl_name [(col_name,...)] SELECT ... 从一个数据表导入
更新记录（单表更新）
UPDATE [LOW_PRIORITY][IGNORE] tbale_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]
删除记录（单表删除）（删）
DELETE FROM tbl_name [WHERE where_condition]
25, SELECT:
SELECT select_expr[,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name|position} [ASC|DESC],...]
[HAVING where_condition]
[ORDER BY {col_name|expr|position}[ASC|DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
select_expr(查询表达式)
每一个表达式表示想要的一列，必须有至少一个
多个列之间以逗号分隔
星号*表示所有列，tbl_name.*表示命名表的所有列
查询表达式可以使用[AS] alias_name为其赋予别名
别名可用于GROUP BY ， ORDRE BY 或 HAVING 字句
26，查询结果的操作（查）
where的条件查询
GROUP BY的查询结果分组
[GROUP BY {col_name | position} [ASC | DESC],...]
ASC表示升序，DESC表示降序
HAVING 的分组条件 [HAVING where_condition]
eg:
SELECT id，age FROM tb_name GROUP BY id HAVING expr;
expr中出现的字段必须为聚合函数或者在前面的SELECT语句中出现的字段，该例中只能为id，age或者其他聚合函数。
聚合函数为max（）min（）avg（）sum（）count（）等，只有一个返回值。
ORDER BY 对查询结果进行排序
[ORDER BU {col_name | expr | position} [ASC | DESC],...]
eg: SELECT * FROM tb_name ORDER BY id DESC; //按id的降序排列
LIMIT 限制查询结果返回的数量
[LIMIT {[offset,]row_count | row_count OFFSET offset}]
eg: SELECT * FROM tb_name LIMIT 1; //只显示第一条
SELECT * FROM tb_name LIMIT 3,2; //显示第四条和第五条，排序时从0开始的
27， 修改客户端显示的内容编码(不影响数据表的内容）
SET NAMES gbk/utf8...
28 ， 子查询 指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以包含多个关键字或条件，如 DISTINCT，GROUP BY，ORDER BY，LIMIT 函数等。子查询的外层查询可以是：SELECT，INSERT，UPDATE，SET或DO。子查询可以返回标量，一行，一列，或者一个子查询
分类：
① 使用比较运算符的子查询 = ,>=, < ,<= ,<> ,!= ,<=>
语法结构 operand comparison_operator subquery
eg:
SELECT ROUND(AVG(goods_price),2) FROM tbd_goods; //对价格字段求平均值，四舍五入保留两位，不写2，默认保留整数。
SELECT goods_id,goods_price,goods_name FROM tbd_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbd_goods) //用子查询查找数据表中价格大于价格平均值的项
用ANY，SOME，ALL修饰的比较运算符
operand comparsion_operator ANY (subquery)
operand comparsion_operator SOME (subquery)
operand comparsion_operator ALL (subquery)
② 使用[NOT] IN
语法结构 operand comparison_operator [NOT] IN (subquery)
ANY 运算符与IN等效，!=ALL或<>ALL 与NOT IN等效
③ 使用[NOT] EXISTS 的子查询，如果子查询返回任何行，EXISTS将返回TRUE，否则为FALSE。（不常用）
29 ，INSERT ... SELECT 将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
30 , 多表更新
UPDATE table_references SET col_name1 = {expr | DEFAULT} [,col_name2 = {expr2 | DEFAULT} ... [WHERE where_condition]
table_references : {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
31 , CREATE ... SELECT 创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [create_definition,...]select_statement
32 , 连接
①{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
②table_reference
③ON conditional_expr
JOIN
INNER JOIN：内连接和交叉连接等价，都是笛卡尔积运算
CROSS JOIN：交叉连接
LEFT [OUTER] JION 左连接：是根据最左表的记录，在被连接右表中找出符合条件的记录与之匹配，找不到与左表匹配的，用null填充。
RIGHT [OUTER] JOIN 右连接：是根据最右表的记录，在被连接左表中找出符合条件的记录与之匹配，找不到与右表匹配的，用null填充。
连接类型：
INNER JOIN，内连接。在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接
连接条件：使用On关键字来设定连接条件，也可以用WHERE来代替。通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤
内连接：显示左表及右表符合连接条件的记录
左外连接：显示左表的全部及右表中符合条件的记录
右外连接：显示右表的全部及左表中符合条件的记录
多表连接
SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1
INNER JOIN tbl_name2 AS t2 ON join_condition
INNER JOIN tbl_name3 AS t3 ON join_condition\G;
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
-> INNER JOIN tdb_goods_cate AS c ON g.cate_id=c.cate_id
-> INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;
连接三个表联合显示查询结果，注意不同的INNER JOIN中间没有逗号！！没有逗号！！没有逗号！！
显示的结果和商品表tdb_goods的记录一样，不过之前是通过单表的查询来显示，这次是通过三张表的连接来显示。
多表的连接实际上是外键的逆向约束。外键把数据分开存储，多表连接又把数据联系在一起。
多表删除
DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition]
子查询与连接总结：
子查询:出现在mysql语句的select子句，而子查询必须要出现在小括号()内,它的外层可以是select语句，insert语句，update语句，delete语句，而且在子查询中可以包含多个关键词:group by,order by,limit以及相关函数。。。引发子查询的情况有比较运算符，(Not)in运算符，exist(Not exists)引发的子查询
连接:连接分为外连接和内连接，内连接主要显示的是左表和右表当中都符合连接条件的记录。外链接有分为左外连接和右外连接，左外连接显示的是左表中的全部记录和右表符合条件的记录，右外连接和左外连接相反
33，字符函数
CONCAT(); 连接多个字段
eg（面试题）：SELECT CONCAT(first_name,'_',last_name) AS full_name FROM test;
CONCAT_WS(); 用某个分隔符连接多个字段
eg：SELECT CONCAT_WS('_',first_name,last_name,...);
FORMAT() 数字格式化（返回类型为字符型）
eg: SELECT FORMAT(12530.75,2) //12,530.75
SELECT FORMAT(12530.75,1) //12,530.8
SELECT FORMAT(12530.75,0) //12,531
UPPER() 转换成大写字母
LOWER() 转换成小写字母
eg：SELECT UPPER('MySQL') //MYSQL
SELECT LOWER(;MySQL') //mysql
LEFT() 获取左侧字符串
RIGHT() 获取右侧字符串
eg:SELECT LEFT('mysql',2) //my
SELECT RIGHT('mysql',3) //sql
SELECT UPPER(LEFT('mysql',2)) //MY 函数嵌套
SELECT CONCAT(UPPER(LEFT('mysql',2)),LOWER(RIGHT('mysql',3))) //MYsql 函数嵌套
LENGTH() 获取字符串长度
eg: SELECT LENGTH('MY SQL') //6
LTRIM() 删除字符串的前导空格
RTRIM() 删除字符串的后续空格
TRIM() 删除字符串的前导和后续空格
eg: SELECT LENGTH(LTRIM(' mysql ')) //9
SELECT LENGTH(RTRIM(' mysql ')) //7
SELECT LENGTH(TRIM(' MYSQL ')) //5
SELECT TRIM(LEADING '?' FROM '???MYSQL??') //MYSQL?? 删除前导指定字符
SELECT TRIM(TRAILING '?' FROM '???MYSQL??')//???MYSQL 删除后续指定字符
SELECT TRIM(BOTH '?' FROM '???MYSQL??') //MYSQL 删除前导和后续的指定字符
REPLACE 替换指定字符
eg: SELECT REPLACE('??MY??SQL???','?','') //MYSQL
SUBSTRING 从某字符串的某个位置开始截取指定长度的字符
eg: SELECT SUBSTRING('MYSQL',1,2); //MY 从1开始计数
SELECT SUBSTRING('MYSQL',2); //YSQL
SELECT SUBSTRING('MYSQL',-2); //QL从右往左数
SELECT SUBSTRING('MYSQL',-1,-2); //输出空
[NOT]LIKE 模式匹配
eg: SELECT 'MYSQL' LIKE 'M%' //1 即true，MySQL中的%为通配符，类似Windows下的*，%代表任意字符，_代表任意一个字符
SELECT 'MYSQL' LIKE 'm%' //1 不区分大小写
34，数值运算函数
CEIL() 向上取整
FLOOR() 向下取整
DIV 整除运算
MOD 取模
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
35 ，比较运算符与函数
[NOT] BETWEEN AND(); //（不）在某个范围内
eg：SELECT 15 BETWEEN 1 AND 22; //1(true),包括两边 [1,22]
SELECT 15 BETWEEN 15 AND 15; //1(true)
[NOT] IN(); //(不)在列出值范围内
eg：SELECT 15 IN(10,15,20); //1(true)
SELECT 15 IN(10,12,20); //0(true)
IS [NOT] NULL; //(不)为空
eg：SELECT NULL IS NULL; //1(true)
SELECT '' IS NULL; //0(false)
36 , 日期时间函数
NOW(); //当前日期和时间
CURDATE(); //当前日期
CURTIME(); //当前时间
DATE_ADD(); //日期的变化
eg：SELECT DATE_ADD('2017-7-25',INTERVAL 365 DAY); //2018-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL -365 DAY); //2016-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL 48 HOUR); //2017-07-27 00:00:00
SELECT DATE_ADD('2017-7-25',INTERVAL 48*60 MINUTE); //2017-07-27 00:00:00
DATE_DIFF() //日期差值
eg：SELECT DATEDIFF('2019-6-10','2015-6-8'); //1463
DATE_FORMAT() //日期格式化
eg：SELECT DATE_FORMAT('2017-7-25','%m/%d/%Y'); //07/25/2017
37 , 信息函数
CONNECTION_ID() //连接ID
eg：SELECT CONNECTION_ID(); //8（服务器不同输出不同）
DATABASE(); //当前数据库
eg：SELECT DATABASE();
LAST_INSERT_ID() //最后插入记录的ID号。 表中必须存在一个自动编号的字段，设置为主键，名字不一定叫ID。而且如果最后一条记录一次插入了多个数据，显示的是插入的多个数据的第一个数据的ID号
USER(); //当前用户
38 ， 聚合函数
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
39 ，加密函数
MD5()：信息摘要算法，为以后的Web页面做准备，尽量使用MD5()
eg: SELECT MD5('admin');
PASSWORD()：密码算法，通过PASSWORD()修改(重要用于MYSQL数据库)当前用户和其他用户的密码，修改客户端自己的密码
eg: SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。
40 , 自定义函数(udf user-define-function)：
两个必要条件：a，参数，b，返回值
创建自定义函数：
CREATE FUNCTION function_name RETURNS{STRING|INTEGER|REAL|DECIMAL} routine_body
eg：
CREATE FUNCTION F1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点：%i分：%s秒');
SELECT F1(); //2017年07月25日 16点：33分：01秒 输出当前时间并格式化
创建带参数的自定义函数：
eg：
CREATE FUNCTION f2（num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED）
RETURNS FLOAT(10，2) UNSIGNED
RETURN(num1+num2)
SELECT f2(10,15) //12.5
删除函数:
DROP [IF EXISTS]FUNCTION fun_name;
修改分隔符： DELIMITER 分隔符
41，存储过程：存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理
优点：
a）增强SQL语句的功能和灵活性
b）实现较快的执行速度
c）减少网络流量
创建存储过程 CREATE [DEFINER = {user| CURRENT_USER}] PROCEDURE sp_name([proc_parameter[,...]]) [characteristic ...]routine_body
proc_parameter: [IN|OUT|INOUT] param_name type
IN:表示该参数的值必须在调用存储过程时指定
OUT：表示该参数的值可以被存储过程改变，并且可以返回
INOUT：表示该参数的调用时指定，并且可以被改变和返回
eg: CREATE PROCEDURE sp() SELECT VERSION();
调用存储过程：
CALL sp_name([parameter[,...]])
CALL sp_name[()]
eg:CALL sp();
修改存储过程：只可以修改参数类型和注释，过程体不可以修改,只能删除再重建。
ALTER ORICEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}
删除存储过程： DROP PRICEDURE[IF EXISTS] sp_name;
eg： CREATE PROCEDURE removeUsersAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
CALL removeUsersAndReturnUserNums(10,@nums); //变量
SELECT @nums; //输出剩余条目数
SET @nums = 7； //定义用户变量，只对当前客户端有效
SELECT ROW_COUNT(); //系统函数；得到插入，删除，更新 影响的记录总数
eg：INSERT test（username）VALUES('A'),('B'),('C');
SELECT ROW_COUNT(); //3
UPDATE test SET username = CONCAT(username,'--CHINA') WHERE id<=2;
SELECT ROW_COUNT(); //2
42，存储引擎：可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制，索引技巧，锁定水平，最终提供广泛且不同的功能
查看数据表的存储引擎： SHOW CREATE TABLE tb_name;
mysql支持的存储引擎：
MySAM（常用）：存储限制可达256TB，支持索引，表级锁定，数据压缩
InnoDB（常用）： 限制为64TB，支持事务和索引，锁颗粒为行锁
Memory
CSV
Archive
相关知识点：
①并发处理：
并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性。对于并发读或者并发写，系统采用“锁”系统解决
a）锁：
共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。
b）锁颗粒：
表锁：是一种开销最小的锁策略。
行锁：是一种开销最大的锁策略。
②事务处理：
事务：用于保证数据库的完整性。
事务的特性：
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
③外键：是保证数据一致性的策略
④索引：是对数据表中一列或多列的值进行排序的一种结构。是快速定位的一种方法
普通索引
唯一索引
全文索引
btree索引
hash索引
...
修改存储引擎的方法
①通过修改MySQL配置文件实现 ———— default-storage-engine = engine
②通过创建数据表命令实现 ———— CREATE TABLE table_name（ ... )ENGINE = engine;
③通过修改数据表命令实现 ———— ALTER TABLE table_name ENGINE [=] engine_name;
43，数据库管理工具：PHPMyAdmin，Navicat，MySQL Workbench
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
