初涉MySQL

启动/停止/重启MySQL服务

  • 启动MySQL服务：

      mysql.server start

  • 停止MySQL服务:

      mysql.server stop

  • 重启MySQL服务:

       mysql.server restart

  • 查看版本号：

      mysql -V    /  mysql --version

  • 登录退出：

      mysql -u root -p            //登录
  exit/quit                   //退出

MySQL 语句的规范

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

操作数据库

  • 创建数据库

      CREATE DATABASE db_test;            //创建名称为tb_test的数据库
  CREATE DATABASE  IF NOT EXISTS db_test;   //如果该数据库已存在，则忽略警告信息
  CREATE DATABASE  IF NOT EXISTS db_test CHARACTER SET GBK;  //创建时候指定字符编码

  • 查看警告信息

      SHOW WARNINGS ;

  • 查看当前服务器数据库列表

      SHOW DATABASES;

  • 查看数据库创建方式

      SHOW CREATE DATABASE db_test;

  • 修改数据库 -编码字符集

      ALTER DATABASE db_test CHARACTER SET=utf8;

  • 从删库到跑路

      DROP DATABASE db_test;

  • 使用数据库

      USE db_test;            //db_test为数据库名称

  • 查看当前打开 的数据库名称

      SELECT DATABASE();

数据类型

  1. 整型

     TINYINT：    1字节  
 SMALLINT：   2字节    
 MEDIUMINT：  3字节
 INT：        4字节  
 BIGINT：     8字节

    以上取值范围从小到大。

  2. 浮点型

     FLOAT 【M,D】  ：  单精度，M是数字总位数，D是小数点后面位数，精确到大约7位小数位  
 DOUBLE【M,D】  ：  双精度

  3. 日期时间型

     YEAR：       年份，默认4位也可存两位，可以允许70~69（1970~2069之间）1
 TIME:      -8385959~8385959之间的一个时间类型  3
 DATE：     存储日期范围，支持范围：1000年的1月1号到9999年的12月31号之间的日期 3
 DATETIME： 日期时间类型，同DATE范围,多了时分秒   8
 TIMESTAMP：时间戳类型，1970年1月1号0点起到2037年的一个值 4

  4. 字符型

    CHAR（M）：定长字符，M个字节，0 <=M <=255
    VARCHAR :变长字符，

操作数据表

  • 创建表

      CREATE TABLE tb_test(   

      user_name varchar(20),
      age TINYINT UNSIGNED,           --  UNSIGNED表示无符号位
      salary FLOAT(8,2) UNSIGNED      -- （8，2）表示最多八位，小数点后2位 穷逼 T_T    
  );

  • 查看表列表

      SHOW TABLES;                            //查看当前数据库表
  SHOW TABLES FROM mysql;             //查看指定数据库表

  • 查看表结构

      DESC girl;
  SHOW COLUMNS FROM girl;

  • 增加记录

      INSERT INTO girl VALUES(13,10,'E');      //省略掉列名的话 所有字段都要赋值
  INSERT girl (id,age) VALUES(808,99);     //一部分赋值

  • 查找记录

      SELECT * FROM girl;     //查询girl表下所有字段

  • 空值和非空

      NULL：字段值可以为空，不指定话默认  
  NOT NULL：字段值禁止为空
  
  CREATE TABLE tb2(

      user_name VARCHAR(20) NOT NULL, -- 不允许为空
      age TINYINT UNSIGNED NULL           

  );
  
  当 使用 INSERT INTO tb2 VALUES(NULL,16); 插入时候会报错：
  
      Column 'user_name' cannot be null

  • 自动编号 AUTO_INCREMENT

    • 自动编号，必须和主键组合使用
    • 默认起始值为1，每次增量为1（保证记录唯一性，不会重复）

  • 主键 PRIMARY KEY（非空+唯一）

    • 主键保证记录的唯一性
    • 主键自动为 NOT NULL
    • 不一定和 AUTO_INCREMENT 一起使用
    • 每张数据表只能存在一个主键

  • 唯一约束（UNIQUE KEY）

    • 唯一约束
    • 保证记录唯一性
    • 字段可以为空值（NULL）
    • 每张数据表可以存在多个唯一约束

  • 默认值约束
    默认值，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

         create table tb_test (
         id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
         user_name VARCHAR(20) NOT null unique key,
         sex enum('1','2','3') DEFAULT '3'
     ); 
     
     //插入时不指定sex的值 ，会默认Wie‘3’
     insert tb5(user_name) values("Tom");

约束

约束：

  1. 约束保证数据的完整性和一致性
  2. 约束分为表级约束和列级约束
  3. 约束类型包括：
    NOT NULL    （非空约束）  
    PRIMARY KEY （主键约束）  
    UNIQUE KEY  （唯一约束）  
    DEFAULT     （默认值约束）  
    FOREIGN KEY （外键约束）

  • 外键约束

    作用：保证数据一致性，完整性；实现一对一或多对多关系。
    要求：
    1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
    2. 数据表的存储引擎只能为InnoDB。
    3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
    4. 外键列和参照列必须创建索引。

    • 主键在创建的同时会自动创建索引，所以如果参照列为主键的话，则会自动创建索引（一般参照列就是作为主键存在）；而如果参照列不是主键而又不存在索引的话，MySQL不会自动创建索引；

    • 外键列不存在索引的话，MySQL会自动创建索引。

        -- 父表(子表所参照的表叫父表)
  create table provinces(
      id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,        -- 参照列（主键在创建的同时会自动创建索引，所以参照列其实已经有了索引）
      p_name VARCHAR(20) NOT NULL
  
  );
  
  -- 子表(有外键的表称为子表)
  create Table users(
      id smallint UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      username VARCHAR(10) NOT NULL,
      pid SMALLINT UNSIGNED,                  -- 外键列（外键列上没有创建索引，Mysql则会自动创建索引）
      FOREIGN KEY (pid) REFERENCES provinces (id)
  
  );
  
  -- 查看某张表的索引
  SHOW INDEX FROM provinces;

  • 外键约束的参照操作

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

在实际开放中，很少使用物理外键约束，很多都去使用逻辑的外键约束，因为物理的外键约束只有InnoDB这种引擎才得以支持；逻辑外键就是指：在定义两张表的结构的时候，我们是按照存在着某种结构的方式去定义，但是不去使用 FOREIGN KEY 这个关键字

  • 表级约束和列级约束 （按参照数目划分）
    • 表级约束：对一个数据列定义的约束
    • 表级约束：对多个数据列定义的约束
    • 列级约束即可以在列定义时声明，也可以在列定以后声明，表级约束只能在列定义后说明

修改数据表

  • 添加删除列：

      -- 添加一列 默认值添加在最后面
  ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 10;
  
  -- 添加一列  添加到 username 字段后面 默认值为'龟孙子'
  ALTER TABLE users ADD COLUMN sex VARCHAR(20) NOT NULL DEFAULT '龟孙子' AFTER username;
  
  -- 添加一列  到第一列 
  ALTER TABLE users ADD COLUMN true_name VARCHAR(20) NOT NULL DEFAULT '超人' FIRST;
  
  
  -- 添加多列 不能指定位置关系，只能在原来数据表列的下方
  
  
  -- 删除一列
  ALTER TABLE users DROP COLUMN true_name;
  
  -- 删除多列
  ALTER TABLE users DROP COLUMN sex,DROP COLUMN age

  • 添加、删除约束

      -- 添加主键约束
  ALTER TABLE user2 ADD COLUMN id SMALLINT UNSIGNED;          -- 添加id字段
  ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id);       -- 将id字段设置为主键
  
  -- 添加唯一约束  
  ALTER TABLE user2 ADD UNIQUE(username)
  
  -- 添加外键约束  
  ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
  
  -- 添加或删除默认约束
  -- 设置age字段默认值约束=15 
  ALTER TABLE user2 ALTER COLUMN age SET DEFAULT 15
  -- 删除默认值
  ALTER TABLE user2 ALTER COLUMN age DROP DEFAULT 
  
  
  --  删除主键约束
  ALTER TABLE user2 DROP PRIMARY KEY ;
  
  -- 删除唯一约束
  show INDEXES from user2;                                    -- 先查看指定约束
  ALTER TABLE user2 DROP INDEX username;      -- 再根据约束名称删除指定约束
  
  -- 删除外键约束  
  ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;

  • 修改列定义和更名数据表

    -- 修改列定义 位置放到第一个 （注意大类型改为小类型会精度丢失）
    ALTER TABLE user2 MODIFY COLUMN id SMALLINT UNSIGNED NOT NULL FIRST;

    -- 修改列名称 id改为user_id，类型改为INT
    ALTER TABLE user2 CHANGE COLUMN id user_id INT UNSIGNED NOT NULL ;  
    
    -- 修改数据表的名称 为 user3
    ALTER TABLE user2 RENAME user3;
    -- 修改数据表的名称 为 user2
    RENAME TABLE user3 TO  user2;

  • 插入记录 INSERT

      INSERT person VALUES(NULL,'tom','123',16,1);      -- id 为主键可用 null 或 default 替代
  INSERT person VALUES(NULL,'tom',MD5('123'),17,0)  -- MD5加密存储
  INSERT person VALUES(NULL,'tom',MD5('123'),10*3-9,0);  -- 表达式
  INSERT person VALUES(NULL,'jerry','54321',17,1),(DEFAULT,'MARI','1993',18,0);                                -- 插入多个
  
  
  第二种方式：可以使用子查询
  INSERT person SET user_name= 'Ben' , pass_word ='123456' ;

  • 单表更新记录 UPDATE

      -- 更新 person 表 age 字段的值  省略where条件将操作全部列记录
  UPDATE person SET age = age + 5;
  -- 更新多个字段  
  UPDATE person SET age = age + id,sex = 0;
  -- 设置id是偶数的  年龄+10
  UPDATE person SET age = age + 10 WHERE id % 2 = 0;

  • 单表删除记录 DELETE

      DELETE FROM person WHERE id = 6;     -- 从person表删除id=6的那行

查询 SELECT

-- 查找记录  
SELECT select_expr[,select_expr ...]
[
    FROM table_reference
   [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，ORDER BY 或 HAVING 子句

基本查询：

  • 查询 person 表下所有字段

      SELECT * FROM person;

  • 查询 person 表下部分字段

      SELECT user_name,pass_word FROM person;

  • 查询时指定结果集字段别名

      SELECT user_name AS '用户名',pass_word AS '密码' FROM person;

条件表达式 WHERE：

对记录进行过滤，如果没有指定 WHERE 子句，则显示所有记录。在 WHERE 表达式中，可以使用 MySQL 支持的函数或运算符。

查询结果分组 GROUP BY：

  • 查询男女的人数

    select sex,COUNT(*) as '人数' from person GROUP BY sex;

  • 查询总人数大于3的性别

      SELECT sex,COUNT(*) as '人数' FROM person GROUP BY sex HAVING COUNT(*)>3 ;

排序 ORDER BY：

语法 : order by 字段 asc/desc
ASC : 顺序，正序。数值：递增，字母：自然顺序（a-z）
DESC: 倒序，反序。数值：递减，字母：自然反序 (z-a)

  • 默认情况下，按照插入记录顺序排序

    select * from student;

  • 按照id排序

      select * from student order by id ASC;   //按照id顺序排序
  select * from student order by id DESC;  //按照id倒序排序

  • 多个排序条件：按照英语成绩正序顺序，如果英语成绩相同，按照语文成绩倒序

      select * from student order by english ASC,chinese DESC;

限制查询结果返回的数量 Limit（起始行,查询几行）(startRow,pageSize)

  • 起始行从0开始

  • 分页：当前页 每页显示多少条

  • 分页查询当前页的数据的sql:

      SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;

  • 例子

      查询第1,2条记录（第1页的数据）：SELECT * FROM student LIMIT 0,2;
  
  查询第3,4条记录（第2页的数据）：SELECT * FROM student LIMIT 2,2;
  
  查询第5,6条记录（第3页的数据）：SELECT * FROM student LIMIT 4,2;
  
  查询第7,8条记录 (没有记录不显示)：SELECT * FROM student LIMIT 6,2;

子查询
子查询（subQuery）是指出现在其他 SQL 语句内的 SELECT 子句。
例如：
SELECT * FROM t1 WHERE col1= （SELECT col2 FROM t2）;
其中，SELECT * FROM t1 称为 Outer Query/OuterStatement （外层查询、外层声明）
SELECT col2 FROM t2，称为 subQuery（子查询）

子查询指嵌套在查询内部，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件，
如 DISTINCY 、GROUP BY、ORDER BY、LIMIT，函数等
子查询的外层查询可以是 SELECT 、INSERT、UPDATE、SET、或DO。
子查询可以返回标量、一行、一列或子查询

  1. 使用比较运算符的子查询

    =、>、<、>=、<=、<>、!=、<=> 这些运算符可以引发子查询
    如果要和子查询结果做运算，而子查询结果为多条时，使用ANY、SUM、ALL三个关键字：
    ANY、SUM:符合其中一个
    ALL：符合全部

     -- 查询均价 
 SELECT AVG(`goods_price`) FROM `tdb_goods` ;
 
 -- 查询均价     (四舍五入，保留两位小数)
 SELECT ROUND(AVG(`goods_price`), 2) AS '均价' FROM `tdb_goods` ;
 
 -- 查询大于均价的商品
 SELECT `goods_id`,`goods_name`,`goods_price` FROM `tdb_goods`  WHERE `goods_price` > (SELECT ROUND(AVG(`goods_price`), 2) FROM `tdb_goods`);
 
 
 -- 查询超极本的价格
 SELECT `goods_price` FROM `tdb_goods`  WHERE `goods_cate`='超级本';
 
 -- 查询哪些商品的价格大于超极本 
 SELECT `goods_name`,`goods_price` FROM `tdb_goods`  WHERE `goods_price` >ANY (
 
     SELECT `goods_price` FROM `tdb_goods`  WHERE `goods_cate`='超级本'
 
 );

  2. 使用 [NOT] IN 的子查询

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

  3. 使用 [NOT] EXISTS 的子查询

    如果子查询返回任何行，EXISTS 将返回 TRUE；否则返回 FALSE。

将查询结果写入数据表：INSERT...SELECT

    INSERT [INTO] tbl_name [(col_name ,...)]  
    SELECT ...  
    
    --  1. 创建分类空表
    CREATE TABLE IF NOT EXISTS tdb_goods_cates(
    
        cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        cate_name VARCHAR(40) NOT NULL
    
    );
    
    
    -- 2. 将查询到的分类 goods_cate 数据 插入到 tdb_goods_cates 表中的 cate_name 字段
    INSERT INTO `tdb_goods_cates` (cate_name)  SELECT `goods_cate` FROM `tdb_goods`GROUP BY `goods_cate`;  
    
    -- 查询商品表
    SELECT * FROM `tdb_goods_cates`

  • 多表更新：参照另外一张表来更新当前表的记录

      UPDATE table_references                -- 表参照关系  
  SELECT col_name1={expr1|DEFAULT}  
  [col_name2={expr2|DEFAULT} ]...   
  [WHERE where_condition]

  • 表的参照关系 ：表通过 INNER JOIN 或 LEFT JOIN 去连接另外一张表

     table_references            -- 表1
 {[INNER|CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}  -- 连接类型
 table_references            -- 表2
 ON conditional_expr         -- 连接条件

    连接类型：

    • INNER JOIN：内连接 【用的较多】
      显示左表和右表中交集(公共)的部分（仅显示符合连接条件的记录）
    • LEFT [OUTER] JOIN：左外连接
      显示左表的全部记录及右表符合连接条件的记录。
    • RIGHT [OUTER] JOIN：右外连接
      显示右表的全部记录及左表符合连接条件的记录。
    • 自身连接
      电商分类，虚拟一张相同的表，使用内连接
    3. 使用内连接更新：
    UPDATE tdb_goods INNER JOIN `tdb_goods_cates` ON `goods_cate`=`cate_name` SET `goods_cate`=`cate_id`;       -- ON... 连接条件   
    
连接条件：  
使用 ON 关键字来设定连接条件，也可以使用 WHERE 来代替。  
通常使用 ON 关键字来设定连接条件  
使用 WHERE 关键字进行结果集记录的过滤

  • 表连接：
    外键的逆向操作，外键把数据分开来存储，通过连接又将多张表联系在一起。
    外连接：

    子查询和连接这里有点模糊 ，后面有时间再屡一下...

运算符和函数

内置函数库

  1. 字符函数

    • CONCAT() : 字符连接

        SELECT CONCAT('A', 'B');          -- 结果:AB
  SELECT CONCAT('1','-','2');       -- 结果:1-2
  SELECT CONCAT(first_name,last_name) AS 'full name' FROM tb_name; --拼接姓名

    • CONCAT_WS(): 使用指定的分隔符进行字符连接

        SELECT CONCAT_WS('|', 'A','B','C')  -- 结果 A|B|C   
  SELECT CONCAT_WS('-',first_name,last_name) AS 'full name' FROM tb_name -- 结果：sun-wukong

    • FORMAT(): 数字格式化

        SELECT FORMAT(120.24,0)         -- 120
  SELECT FORMAT(120.25,1)         -- 120.3
  SELECT FORMAT(120.24,1)         -- 120.2
  SELECT FORMAT(120.24,3)         -- 120.240

    • LOWER(): 转换成小写

        SELECT LOWER('MySQL')           -- mysql

    • UPPER(): 转换成大写

        SELECT UPPER('MySQL')           -- MYSQL

    • LEFT(): 获取左侧字符

        SELECT LEFT('MySQL',2)          -- My
  SELECT UPPER(LEFT('MySQL',2))   -- 函数嵌套

    • RIGHT(): 获取右侧字符

        SELECT RIGHT('MySQL',3)         -- SQL
  SELECT LOWER(RIGHT('MySQL',3)   -- sql

    • LENGTH(): 获取字符长度

        SELECT LENGTH("MySQL")      -- 5
  SELECT LENGTH("My SQL")     -- 6

    • LTRIM(): 删除左边空格

    • RTRIM(): 删除右边空格

    • TRIM (): 删除左边&&右边的空格

    • REPLACE(): 字符替换

        SELECT REPLACE('???My??SQL??','?','')     -- MySQL
  SELECT REPLACE('???My??SQL??','?','-')    -- ---My--SQL--
  SELECT REPLACE('???My??SQL??','?','!!')    -- !!!!!!My!!!!SQL!!!!
  SELECT REPLACE('???My??SQL??','?','!*')    -- !*!*!*My!*!*SQL!*!*

    • SUBSTRING(): 截取

        SELECT SUBSTRING('MySQL',1,2)    -- My   从1开始，不是从0开始
  SELECT SUBSTRING('MySQL',3)  -- SQL         SELECT SUBSTRING('MySQL',-1)     -- L
  SELECT SUBSTRING('MySQL',-3)     -- SQL

    • LIKE: 模式匹配

        SELECT 'MYSQL' LIKE 'M%'    -- 1 %指任意字符、_代表任意一个字符

  2. 数值运算符和函数

    • CEIL(): 向上取整 / 进一取整

        SELECT CEIL(3.01)       -- 4
  SELECT CEIL(3.99)       -- 4

    • FLOOR(): 向下取整 / 舍一取整

        SELECT FLOOR(3.01)      -- 3
  SELECT FLOOR(3.99)      -- 3

    • DIV: 整数除法（类似java中的除法）

        SELECT 3/4                -- 0.7500
  SELECT 3 DIV 4              -- 0
  SELECT 4 DIV 3              -- 1

    • MOD（%）： 取余数 （模）

        SELECT 2 MOD 5;     -- 2
        SELECT 5 MOD 2;    -- 1
        SELECT 5.3 % 3;     -- 2.3
        
* POWER（）：    幂运算 m的n次方   

        SELECT POWER(3, 3)      -- 3的3次方  3*3*3 
        
* ROUND():      四舍五入  

        SELECT ROUND(3.652,1)       -- 3.7
        SELECT ROUND(3.652,2)       -- 3.65
        SELECT ROUND(3.655,2)       -- 3.66
        SELECT ROUND(3.655,0)       -- 4

  1. 比较运算符和函数

    • [NOT] BETWEEN ... AND ... [不]在范围内

        SELECT 35 BETWEEN 1 AND 22  -- 0
  SELECT 35 BETWEEN 1 AND 36  -- 1
  SELECT 35 NOT BETWEEN 1 AND 34  -- 1

    • [NOT] IN() [不]在列出值范围之内

        SELECT 10 IN(5,10,15,20)        -- 1
  SELECT 30 IN(5,10,15,20)        -- 0

    • IS [NOT] NULL [不]为空

       SELECT NULL IS NULL     -- 1
 SELECT '' IS NULL          -- 0
 SELECT 0 IS NULL           -- 0

  2. 时间日期函数

    • NOW(): 当前日期和时间

        SELECT NOW()            --  2018-03-22 17:08:26

    • CURDATE(): 当前时间

        SELECT CURDATE()        --  2018-03-22

    • CURTIME(): 当前时间

        SELECT CURTIME()         --     17:08:58

    • DATE_ADD(): 日期变化

        SELECT DATE_ADD('2018-03-22',INTERVAL 365 DAY)   -- 2019-03-22
  SELECT DATE_ADD('2018-03-22',INTERVAL -365 DAY)  -- 2017-03-22
  SELECT DATE_ADD('2018-03-22',INTERVAL 1 YEAR)    -- 2019-03-22
  SELECT DATE_ADD('2018-03-22',INTERVAL 3 WEEK)    -- 2018-04-12

    • DATEDIFF(): 日期差值

        SELECT DATEDIFF('2018-03-22','2019-03-22')  -- -365
  SELECT DATEDIFF('2018-03-22','2017-03-22    -- 365

    • DATE_FORMAT(): 日期格式化

        SELECT DATE_FORMAT('2018-03-22', '%m/%d/%Y')    -- 03/22/2018

  3. 信息函数

        --   返回当前连接的id(线程id)
    SELECT CONNECTION_ID()
    
    --    当前打开的数据库
    SELECT DATABASE()
    
    -- 最后插入记录的ID号
    SELECT LAST_INSERT_ID()
    
    -- 当前用户  root@localhost
    SELECT USER()
    
    -- 当前数据库版本信息 5.7.21
    SELECT VERSION()

  4. 聚合函数
    特点：只有一个返回值

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

  5. 加密函数

    • MD5():信息摘要算法
    • PASSWORD（）：密码算法

自定义函数

用户自定义函数（UDF）,是一种对MySQL扩展的途径，其用法与内置函数相同
作用：将一些经常使用的方法提前定义好，方便调用

  • 创建自定义函数

      CREATE FUNCTION function_name    
  RETURNS                                 -- 返回值类型
  {STRING | INTEGER | REAL | DECIMAL}  
  routine_body                            -- 函数体

  • 关于函数体

    1). 由合法的 SQL 语句构成，
    2). 也可以是简单的 SELECT 或 INSERT 语句
    3). 如果为复合结构则使用 BEGIN...END 语句
    4). 复合结构可以包含声明，循环，控制结构

  • 创建不带参数的自定义函数

      SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时:%i分:%s秒')   -- 2018年03月22日 19时:19分:16秒
  
  //自定义函数
  CREATE FUNCTION f1() RETURNS VARCHAR(30)
  RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时:%i分:%s秒');

  //调用  
  SELECT f1();    -- 2018年03月22日 19时:19分:16秒  

  //删除方法
  DROP FUNCTION f1();

  • 创建带有参数的自定义函数

      //新建带参函数
  CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
  RETURNS FLOAT(10,2)             
  RETURN (num1 + num2)/2;         -- 返回两个参数和除以2

  //调用
  SELECT f2(1,5);

存储过程

  • 存储过程是 SQL 语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，省略了 MySQL引擎对SQL语句语法分析和编译，客户端调用则直接调用编译的结果，所以执行速度较快，同时增强了 SQL 语句的功能和灵活性，减少了网络流量。

  • 参数

    • IN：表示改参数的值必须在调用存储过程中指定
    • OUT：表示该参数的值可以被存储过程改变，并且可以返回
    • INOUT：表示该参数的调用时指定，并且可以被改变了返回（都可以）

  • 过程体

    • 过程体由合法的SQL语句构成
    • 过程体可以是任意SQl语句
    • 过程体如果我复合结构，则使用 BEGIN...END语句
    • 复合结构可以包含声明、循环、控制结构。

  • 创建不带参数的存储过程

      CREATE PROCEDURE test_pro()
  SELECT VERSION();
  
  //调用
  CALL test_pro();

  • 创建带有IN类型参数的存储过程

      DELIMITER $             -- 定界符
  CREATE PROCEDURE delPersonById(p_id INT UNSIGNED)
  BEGIN
  DELETE FROM person WHERE id = p_id;         -- 过程体
  END $ 
  
  -- 调用存储过程
  CALL delPersonById(5);

  • 创建带有OUT类型参数的存储过程

      -- 从 person 表中根据id删除一条数据，并将 剩余总数设置到输出参数 num 字段
  DELIMITER $ 
  CREATE PROCEDURE delUserReturnNums(IN p_id INT,OUT userNums INT)
  BEGIN
  
  DELETE FROM person WHERE id = p_id;
  SELECT COUNT(id) FROM person INTO userNums;     -- 将 SELECT 表达式的结果放入到 userNums 变量
  
  END $
  
  -- 调用存储过程
  CALL `delUserReturnNums`(4,@nums);
  
  -- 查看 nums 的值
  SELECT @nums 
  
  -- 声明一个用户变量：跟MySQL客户端绑定，这种方式设定的变量只对当前用户所使用的客户端生效
  SET @i = 8;

  • 创建带有多个OUT类型参数的存储过程

      -- 根据年龄删除用户：返回两个参数 1.删除的用户数量 2. 剩余的用户数量
  DELIMITER $
  CREATE PROCEDURE delUsersByAgeReturnInfos(IN p_age INT,OUT delUsers INT,OUT userCount INT)
  BEGIN
  DELETE FROM person WHERE age = p_age;   -- 根据年龄删除用户 
  SELECT ROW_COUNT() INTO delUsers;                           -- 返回上一次操作影响的行数
  SELECT COUNT(id) FROM person INTO userCount;
  END $
  
  
  
  -- 调用存储过程
  CALL delUsersByAgeReturnInfos(14,@A,@B);
  
  -- 查看返回结果变量
  SELECT @A;
  SELECT @B;

  • 存储过程与自定义函数的区别

    • 存储过程实现相对复杂；而函数针对性较强
    • 存储过程可以返回多个值；函数只能有一个返回值
    • 存储过程一般是独立的来执行；而函数可以作为其他SQL语句的组成部分来实现。

存储引擎

MySQL 可以将数据以不同的技术存储在文件（内存）中，这种技术成为存储引擎。
每一种存储引擎使用了不同的存储机制、索引技巧、锁定水平、最终提供广泛且不同的功能。
关系型数据库中数据的存储是以表的形式来实现的，所以说存储引擎也可以称为表类型。
一种技术：一种存储数据

  • MySQL 支持的存储引擎

    • MyISAM：
      存储限制可达256TB,支持索引，表级锁定，数据压缩；适用于事务的处理不多的情况。
    • InnoDB：
      存储限制可达64TB,支持事务和索引，锁颗粒为行锁；适用于事务处理比较多，需要有外键支持的情况 。
      其他等等...

  • 并发控制

    • 当多个连接对记录进行修改时保证数据的一致性和完整性
    • 在处理并发读或并发写时，系统会使用一套锁机制来解决这个问题

  • 锁：

    • 共享锁（读锁）：
      在某一资源上 读锁是共享的（互不阻塞），在同一时间段内，多个用户可以同时读取同一个资源，读取过程中数据不会发生任何变化。

    • 排他锁（写锁）：
      在任何时候，只能有一个用户来写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

    • 锁颗粒

      • 表锁：是一种开销最小的锁策略
      • 行锁：是一种开销最大的锁策略（支持最大并发操作处理）
        • 怎么理解呢，就是每条记录都要加，所以开销最大

  • 事务

    • 用于保证数据库的完整性
    • 特征：
      • 原子性
      • 一致性
      • 隔离性
      • 持久性

  • 外键

    • 是保证数据一致性的策略

  • 索引

    • 是对数据表中一列或者多列的值进行排序的一种结构。

使用索引可以快速的访问数据表中的特定信息，索引是进行记录快速定位的一种方法，好比是书的目录，如果我们要快速找到这本书的某一部分内容，那么我们应该首先在目录当中查找相应的章节，然后看一下他在第几页，再快速定位到相应的页码查找想看的内容。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,271评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,275评论 2赞 380
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,151评论 0赞 336
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,550评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,553评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,559评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,924评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,580评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,826评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,578评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,661评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,363评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,940评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,926评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,156评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,872评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,391评论 2赞 342

推荐阅读更多精彩内容

  • MySQL
    第1章 初涉MySQL 1.1 MySQL文件 （1）MySQL目录结构 （2）MySQL配置向导文件（安装后配置...
    凛0_0阅读 777评论 1赞 0
  • mySQL笔记
    1、MySQL启动和关闭（安装及配置请参照百度经验，这里不再记录。MySQL默认端口号：3306；默认数据类型格式...
    强壮de西兰花阅读 632评论 0赞 1
  • 七绝 赠友人
    闲修连月宏图起，直取黄龙梦日边。 莫教玉笛飞月夜，书山学海一游仙。 (新韵)
    晴鹤1阅读 191评论 0赞 6
  • 2018-01-01过一个快乐的元旦假日
    期待了很久，2018年终于来到了。新的一年，我是从加班开始的。其实我对加班这件事情并没有太多的怨言，尤其是在我算过...
    夏烟阅读 125评论 0赞 1
  • 【读书清单】非暴力沟通（三）
    1.区分观察与评论 你是否也曾将自己的意见与观察混为一谈的说出来，是否感受到不同语言表达式带给别人的不同感受，区分...
    fish__dora阅读 163评论 0赞 2