MySQL基础总结

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