MySQL笔记

1）mac采用brew安装mysql
  1.1）获取brew安装的列表
      brew list
  1.2）具体安装位置
      brew list mysql@5.7
2）mac 启动和关闭mysql数据库
  # mysql.server start
  # mysql.server stop
  # mysql.server restart
3）终端连接数据库
  # sudo su
  # 输入电脑密码
  # mysql -uroot -p -P3306 -h127.0.0.1
  # 输入mysql密码
  mysql> //todo something...
4）修改默认提示符
  4.1）连接客户端时通过参数指定
      shell>mysql -uroot -proot --prompt 提示符
      ex：mysql -uroot -proot --prompt \h    
      prompt参数后面跟的提示符包括：
      {\D：完整的日期;\d：当前数据库;\h：服务器名称;\u：当前用户}
  4.1）登录了mysql终端修改
      mysql>prompt
5）常用命令
  5.1）显示当前服务器版本 SELECT VERSION();
  5.2）显示当前日期       SELECT NOW();
  5.3）显示当前用户      SELECT USER();
  5.3）显示当前数据库      SELECT DATABASE();
6）书写规范
  关键字和函数名称全部大写；
  数据库名称、表名称、字段名称全部小写；
  SQL语句必须以分号结尾；
7）创建数据库语法结构: 大括号内是必选项，中括号是可选选
  CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name;
8） 查看当前服务器下有多少个数据库
    SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
9）修改数据库
  ALTER {DATABASE | SCHEMA}  db_name [DEFAULT] CHARACTER SET [=] character_name;
10）删除数据库
  DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
11）数据类型
  整型：TINYINT(1),SMALLINT(2),MEDIUMINT(3),INT(4),BIGINT(8); 无符号位：UNSIGNED
  浮点型：FLOAT[(M,D)],DOUBLE[(M,D)]   M是数字总位数，D是小数点后面的位数
  日期类型：YEAR,TIME,DATE,DATETIME,TIMESTAMP
  字符型：CHAR(M),VARCHAR(M),TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,ENUM('value1',...,'valueN'),SET('value1',...,'valueN')
12）打开数据库
  USE database_name;
13）创建表语法结构
    CREATE TABLE [IF NOT EXISTS] table_name(
      column_name data_type,
      ...
    );
    -- example
    CREATE TABLE `item_stock` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `stock` int(11) NOT NULL DEFAULT '0',
      `item_id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

14）查看数据表列表
  SHOW TABLES [FROM db_name][LIKE 'pattern' | WHERE expr]
15）查看数据表结构
  15.1）SHOW COLUMNS FROM table_name；
  15.2）DESC table_name;
16）INSERT插入命令语法
  1) INSERT [INTO] table_name[(col_name,...)] VALUES(val,...);
  2) INSERT [INTO] table_name[(col_name,...)] {VALUES|VALUE}({expr | DEFAULT},...),(...),...
  3）INSERT [INTO] table_name SET col_name ={expr | DEFAULT},... 插入子查询查询的结果
  4）INSERT [INTO] table_name[(col_name,...)] SELECT ...
  
  如果插入的时候是自增序列则使用DEFAULT 或者 NULL，则自增序列不受影响。
17）SELECT 命令语法
  SELECT expr,...FROM table_name 
18）空值和非空
  NULL,字段值可为空
  NOT NULL,字段值不能为空
19）自动编号
  AUTO_INCREMENT
  自动编号，且与主键组合使用；
  默认情况下，起始值为1，自增值为1；
20）主键
  PRIMARY KEY
  主键约束：
    每张数据表都有唯一一个主键，
    主键保证记录的唯一性，
    主键自动为NOT NULL;
21)唯一约束
  UNIQUE KEY
  唯一约束保证记录的唯一性，
  唯一约束的字段可以为NULL，
  每张数据表可以存在多个唯一约束；
22）默认约束 DEFAULT 
  当插入记录时，如果没有明确为字段赋值，则自动赋予默认值；
23）约束
  23.1）约束保证数据的完整性和一致性；
  23.2）约束分为表级约束和列级约束
  23.3）约束分类：
    NOT NULL(非空约束)
    PRIMARY KEY(主键约束)
    UNIQUE KEY (唯一约束)
    DEFAULT (默认约束)
    FOREIGN KEY (外键约束)
24）外键约束 FOREIGN KEY
  保证数据的完整性和一致性；
  实现一对一或者一对多的关系；
  要求：
    1）父表和子表必须使用相同的数据引擎，而且禁止使用临时表；
    2）数据表的存储引擎必须只能为InnoDB；
    3）外键列和参照列必须使用相似的数据类型。其中数字的长度或是否有符号位必须相同，而字符的长度可以不同；
    4）外键列和参照列必须创建索引，如果外键不存在索引，Mysql则自动创建索引；
25）外键约束的参照操作
  1）CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
  2）SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须指定子表列没有指定NOT NULL
  3）RESTRICT:拒绝对父表的更新或删除操作
  4）NO ACTION: 标准SQL的关键字，在mysql中与RESTRICT相同
  具体语法：
     FOREIGN KEY (sub_table_id) REFERENCES father_table (id) ON DELETE CASCADE
26）编辑数据库默认的存储引擎
  mysql配置文件：
    default-storage-engine=INNODB
27）表级约束和列级约束
  1）对一个数据列建立的约束，称为列级约束
  2）对多个数据列建立的约束，称为表级约束
  3）列级约束既可以在列定义时声明，也可在列定义后声明
  4）表级约束只能在列定义后声明
28）修改数据表-添加/删除列
  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）删除列
    TABLE tbl_name DROP [COLUMN] col_name;
29）修改数据表-添加约束
  1）添加主键约束
    ALTER TABLE tbl_name ADD [CONSTRAINT [symbol 自定义约束名称]] PRIMARY KEY [index_type](index_col_name,...);
  2）添加唯一约束
    ALTER TABLE tbl_name ADD [symbol 自定义约束名称]] UNIQUE [INDEX | KEY] [index_name] [index_type](index_col_name,...);
  3）添加外键约束
    ALTER TABLE tbl_name ADD [symbol 自定义约束名称]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition;
  4）添加/删除默认约束
    ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  5）删除主键约束
     ALTER TABLE tbl_name DROP PRIMARY KEY
  6）删除唯一约束
     ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
  7）删除外键约束
     ALTER TABLE tbl_name DROP FOREIGN KEY ky_symbol
  8）修改定义列
     ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  9）修改列名称
     ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
  10）数据表更名
    1） TABLE tbl_name RENAME [TO|AS]  new_tbl_name
    2） RENAME TABLE tbl_name  TO new_tbl_name [,tbl2_name TO new_tbl2_name]...
30）单表更新记录UPDATE
  UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr|DEFAULT} [,col_name2 = {expr|DEFAULT}]...
  [WHERE where_condition] 
  table_reference表的关系：
    table_reference 
      {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} 
    table_reference
      ON conditional_expr
    连接类型
    INNER JOIN 内连接
    LEFT [OUTER] JOIN 左外连接
    RIGHT [OUTER] JOIN 右外连接
    在mysql中，JOIN,CROSS JOIN,INNER JOIN 都是等价的。
    多表更新数据：
      UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
      
30）单表删除记录 DELETE
  DELETE FROM table_name [WHERE where_condition]
31）查询记录 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}]
  ]
31）查询表达式 select_expr
  每一个表达式表示的是一列，必须有至少一个；
  多列之间用英文逗号分隔；
  星号(*)表示所有列，tbl_name.*可以表示命名表的所有列；
  查询表达式可以使用[AS] alias_name 为其赋予别名；
  别名可以用于GROUP BY、ORDER BY或HAVING子句中；
31）查询条件表达式 WHERE
  对记录进行过滤，没有指定WHERE则查询全部记录；
  在WHERE表达式中，可以使用mysql支持的函数或表达式；
32）查询结果分组 GROUP BY
  [GROUP BY {col_name | position} [ASC | DESC],...]
33）分组条件 HAVING 针对符合条件的分组
  [HAVING where_condition] where_condition是集合函数表达式
34）对查询结果进行排序 ORDER BY
  [ORDER BY {col_name | expr | position} [ASC | DESC],...]
  ASC 升序，DESC 降序，position 位置
35）限制查询结果返回的数量 LIMIT
  [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  offset：偏移量
  row_count：需要返回的行数
36）子查询
  1）子查询(SubQuery)是指出现在其他SQL语句内的SELECT子句
  SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
  其中 SELECT * FROM t1称为Outer Quary / Outer Statement
  SELECT col2 FROM t2 称为SubQuery。
  2）子查询指嵌套在查询内部，且必须始终出现在小圆括号内；
    子查询可以包含多个关键字或条件(DISTINCT,GROUP BY.ORDER BY, LIMIT)
    子查询的外围查询可是：SELECT,INSERT,UPDATE,SET或DO。
  3）子查询可以返回标量，一行，一列，子查询；
  4）比较符号的子查询
    =、>、<、<=、>=、<>、!=、<=>
    语法结构
      operand comparisn_operator subquery
    查询大于平均价格的商品
    SELECT * FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
    4.1）当子查询出现多行记录，用ANY、SOME、ALL修饰的比较运算符
    operand comparison_operator ANY (称为SubQuery)
    operand comparison_operator SOME (称为SubQuery)
    operand comparison_operator ALL (称为SubQuery)
    ANY和SOME是等价的，满足一个即可；ALL是全部满足。
              ANY        SOME      ALL
    >、>=    最小值     最小值   最大值
    <、<+    最大值     最大值   最小值
    =        任意值     最大值
    <>、!=                       最大值
  -- 查询商品价格
  SELECT * FROM  tdb_goods WHERE goods_price >= ALL
    (SELECT goods_price FROM tdb_goods WHERE  goods_cate = '超级本');
  5）[NOT] IN 子查询
    operand comparison_operator [NOT] IN (SubQuery)
    =ANY运算符和IN等效
    !=ALL或<>ALL运算符和NOT IN 等效
  6）创建表的时候插入数据 CREATE...SELECT
    创建数据库表的同时将查询到的结果集写入到数据表
    CREATE TABLE [IF NOT EXISTS] tbl_name 
    [(create_definition,...)]
    select_statement
    -- example
    CREATE TABLE tdb_goods_brand(
	    band_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	    band_name VARCHAR(40) NOT NULL
    )SELECT brand_name FROM tdb_goods GROUP BY  brand_name;

    -- 同时修改多列列明的同时修改字段类型
    ALERT TABLE tdb_goods 
      CHANGE goog_cate cate_id SMALLINT UNSIGNED NOT NULL,
      CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
37）连接的语法结构
    table_reference表的关系：
    table_reference 
      {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} 
    table_reference
      ON conditional_expr
    连接类型
    INNER JOIN 内连接
    LEFT [OUTER] JOIN 左外连接
    RIGHT [OUTER] JOIN 右外连接
    在mysql中，JOIN,CROSS JOIN,INNER JOIN 都是等价的。
    
    2)tbl_name [[AS] alias] | table_query [AS] alias
      数据表可以使用tbl_name [[AS] alias]赋予别名；
      table_query可以作为子查询使用在FROM子句中，这样的子查询必须赋予别名
38）连接条件
  使用ON关键字来设定连接条件，也可以使用WHERE来代替。
  通常使用ON关键字来设定连接条件，
  使用WHERE来进行结果集的过滤。
39）连接
  1）内连接：显示左表和右表符合连接条件的记录，取A表和B表的交集
    SELECT goods_id,goods_name,cate_name 
    FROM tdb_goods INNER JOIN tdb_goods_cate
    ON tdb_goods.cate_id = tdb_goods_cate.cate_id
  2）左外连接：显示左表的全部记录和右表符合连接记录的集合
    SELECT goods_id,goods_name,cate_name 
    FROM tdb_goods LEFT JOIN tdb_goods_cate
    ON tdb_goods.cate_id = tdb_goods_cate.cate_id
  3）右外连接：显示右表的全部记录和左表符合连接记录的集合
  4）多表连接
40）无限分类的数据表设计
  CREATE TABLE tb_type(
	  type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	  type_name VARCHAR(20) NOT NULL,
	  parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
  );
  自身连接：
    -- 查询子类的父类
    SELECT s.type_id,s.type_name,p.type_name
    FROM tb_type AS s
    LEFT JOIN tb_type AS P
    ON s.type_id = p.type_id;
  -- 查询父类下面的子类
    SELECT p.type_id,p.type_name,s.type_name
    FROM tb_type AS p
    LEFT JOIN tb_type AS s
    ON s.type_id = p.type_id;
41）多表删除
  DELETE table_name[.*][,table_name[.*]]...
  FROM table_reference
  [WHERE where_condition]
  -- example
  DELETE t1 FROM tb_type AS t1
  LEFT JOIN 
    (SELECT goods_id,goods_name 
	   FROM tb_type 
	   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;
42）函数-字符函数
  CONCAT()  字符连接
  CONCAT_WS() 使用指定的分隔符连接字符串
  FORMAT() 数字格式化
  LOWER() 转换为小写字母
  UPPER() 转换为大写字母
  LEFT()  获取左侧字符
  RIGHT() 获取右侧字符
  LENGTH() 获取字符串长度
  LTRIM()  删除前导空格
  RTRIM() 删除后续空格
  TRIM()  删除前导和后续空格
  SUBSTRING() 字符串截取
  [NOT]LIKE  模糊匹配
  REPLACE() 字符串替换
43）函数-数值运算符函数
  CEIL() 进一取整
  DIV()  整数除法
  FLOOR() 舍一取整
  MOD() 取余数(取模)
  POWER() 幂运算
  ROUND() 四舍五入
  TRUNCATE() 数字截取
44）函数-比较运算符
  [NOT]BETWEEN...AND... [不]在范围内
  [NOT] IN()[不]在列出范围内
  IS [NOT] NULL[不]为空
45）函数-日期函数
  NOW()   获取当前日期和时间
  CURDATE() 当前日期
  CURTIME() 当前时间
  DATE_ADD() 日期变化
  DATEDIFF() 日期差值
  DATE_FORMAT() 日期格式化
46）函数-信息函数
  CONNECTION_ID() 连接ID
  DATABASE() 当前数据库
  LAST_INSERT_ID() 最后插入记录的ID
  USER() 当前用户
  VERSION()  版本信息
47）函数-集合函数-只有一个返回值
  AVG() 平均值
  COUNT() 计数
  MAX() 最大值
  MIN() 最小值
  SUM() 求和
48）函数-加密函数
  MD5()   信息摘要算法
  PASSWORD()密码算法 修改当前账号或其他账号登录mysql的登录密码
50）函数-自定义函数
  CREATE FUNCTION function_name
  RETURNS
  {STRING|INTEGER|REAL|DECIMAL}
  routine_body
  
  routine_body函数体：
    由合法的SQL语句组成；
    可以是简单的SELECT或INSERT语句；
    如果函数体为复合结构，则使用BEGIN...END语句；
    复合结构可以包含声明、循环、控制结构；
  1)创建不带参数的自定义函数：
    CREATE FUNCTION f1() RETURNS VARCHAR(30)
    RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
    调用函数：SELECT f1();
  2)创建带参数的自定义函数：
    CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) 
    RETURNS FLOAT(10,2) UNSIGNED
    RETURN (num1 + num2) / 2;
    调用函数：SELECT f2(10,5);
  3)创建集合函数的自定义函数
    -- 替换分隔符
    DELIMITER $$
    -- 创建聚合自定义函数
    CREATE FUNCTION addUser(userName VARCHAR(20))
    RETURNS INT UNSIGNED
    BEGIN
	    INSERT INTO test(username) VALUES(userName);
	    RETURN LAST_INSERT_ID();
    END
    $$
    DELIMITER ;
    -- 调用函数
    SELECT addUser('TOM');
    
51）-存储过程-创建
  CREATE 
  [DEFINER = {user | CURRENT_USER}]
  PROCEDURE sp_name ([proc_paramter[,...]])
  [characteristic ...] routine_body
  
  proc_paramter:
  [IN | OUT] param_name type
  参数：
    IN表示在调用存储过程的时候必须指定
    OUT表示该参数的值可以被存储过程改变，并且可以返回
    INOUT表示该参数调用时指定，并且可以被修改和返回
  特性：
    COMMENT: 注释
    CONTAINS SQL: 包含sql语句，但是不包含读或写的语句
    NO SQL: 不包含sql语句
    READS SQL DATA: 包含读数据的语句
    MODIFIES SQL DATA: 包含写数据的语句
    SQL SECURITY{DEFINER | INVOKER} 指明谁有权限执行
  过程体：
    过程体由合法是sql语句组成；
    过程体是任意的sql语句；
    过程体如果是复合结构，则采用BEGIN...END语句；
    复合结构可以包含声明、循环、控制结构;
  调用存储过程：
    CALL sp_name([paramter[,...]])
    CALL sp_name[{}]
  删除存储过程
    DROP PROCEDURE  [IF EXISTS] param_name
  不带参数的存储过程：
    CREATE PROCEDURE sp1() SELECT VERSION();
    CALL sp1;
  带IN参数的存储过程:
    DELIMITER $$
    CREATE PROCEDURE sp2(IN p_id INT UNSIGNED)
    BEGIN
	    DELETE FROM users WHERE users.id = p_id; 
    END
    DELIMITER ;
    CALL sp2(11);
  带IN,OUT参数的存储过程
    DELIMITER $$
    CREATE PROCEDURE sp3(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
    BEGIN
	    DELETE FROM users WHERE users.id = p_id;
	    SELECT COUNT(id)FROM users.id INTO userNums;
    END
    DELIMITER ;
    
    CALL sp3(11,@nums);
    SELECT @nums;
    
  注意：declare定义的变量只在begin...end内有效；
        类似 SET @ID = 7;是用户变量，
        用户变量是和mysql的客户端绑定的，只对当前用户使用的客户端生效；
        
  带有多个OUT参数的存储过程
    DELIMITER $$
    CREATE PROCEDURE sp3 (IN p_age SMALLINT UNSIGNED,OUT delNum INT UNSIGNED,OUT userCount INT UNSIGNED)
    BEGIN
	    DELETE FROM users WHERE users.age = p_age;
	    SELECT ROW_COUNT() INTO delNum;
	    SELECT COUNT(id) FROM users INTO userCount;
    END
    DELIMITER ;
52）-MySQL管理工具
  PHPMyAdmin
  Navicat
  MySQL Workbench