我爱编程

初涉MySQL

2018-03-23  本文已影响0人  _Sisyphus

启动/停止/重启MySQL服务

MySQL 语句的规范

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

操作数据库

数据类型

  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 :变长字符，

操作数据表

约束

约束：

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

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

修改数据表

    -- 修改列定义 位置放到第一个 （注意大类型改为小类型会精度丢失）
    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;

查询 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 子句

基本查询：

条件表达式 WHERE：

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

查询结果分组 GROUP BY：

排序 ORDER BY：

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

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

子查询
子查询（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` 

    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扩展的途径，其用法与内置函数相同
作用：将一些经常使用的方法提前定义好，方便调用

存储过程

存储引擎

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

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

上一篇 下一篇

猜你喜欢

热点阅读