常用命令

表操作

  1. 表异常处理

     #从/etc/my.ini查看MySQL日志文件的位置
     #从日志文件中查找出现异常的表(记下表名)
     mysql -u root -p 回车
     输入密码
     use 库名;
     #检查表存在的错误
     check table 表名;
     #修复存在错误的表
     repair table 表名;
     #检查表的修复情况
     check table 表名;
     #参考:http://www.cnblogs.com/yuwensong/archive/2013/05/09/3068336.html
    
  2. 判断一个表是否存在,存在则删除

     DROP TABLE IF EXISTS `table_name`;
    
  3. 创建一个表

     CREATE TABLE `user` (
       `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
       `nickname` varchar(255) NOT NULL COMMENT '用户昵称',
       `username` varchar(255) NOT NULL COMMENT '用户账号',
       `password` varchar(255) NOT NULL COMMENT '用户密码',
       `salt` varchar(255) NOT NULL COMMENT '密码加盐',
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    

字段、属性、列

  1. 修改字段类型

     ALTER TABLE 表名 MODIFY 字段名 VARCHAR (10);
    
  2. 修改字段名(要重新指定类型)

     ALTER TABLE 表名 CHANGE 旧字段名 新字段名 INT (10);
    
  3. 增加字段

     ALTER TABLE 表名 ADD COLUMN 字段名 INT (11) UNSIGNED NOT NULL DEFAULT '0' AFTER 前一字段名;
    
  4. 删除字段

     ALTER TABLE 表名 DROP COLUMN 字段名;
    

增删改查

  1.  # 单条数据
     INSERT INTO 表名 (
         键名,
         键名,
         键名,
         键名
     )
     VALUE
         (值1, 值2, 值3, 值4);
    
     # 多条数据
     INSERT INTO 表名 (
         键名,
         键名,
         键名,
         键名
     )
     VALUES
         (值1, 值2, 值3, 值4),
         (值1, 值2, 值3, 值4);
    
     # 数据导出
     INSERT INTO table1 (
         column_1,
         column_2,
         column_3,
         column_4
     ) SELECT
         column_w,
         column_x,
         column_y,
         column_z
     FROM
         table2
     WHERE
         column_x < 100
     ORDER BY
         column_w ASC
    
  2.  DELETE
     FROM
         表名
     WHERE
         条件;
    
  3.  UPDATE 表名
     SET 字段 = 值
     WHERE
         条件;
    
  4.  SELECT
         字段1,
         字段2
     FROM
         表名
     WHERE
         条件;
    
  5. 指定正则条件查

     SELECT * FROM 表名 WHERE 字段 REGEXP '^([0-9]+)$'
    
  6. 随机查询N条记录

     SELECT * FROM 表名 ORDER BY RAND() LIMIT N
    

索引管理

  1. 添加索引时若不定义索引名,MySQL会自动添加索引名,添加规则如下

     MySQL先尝试使用该表中字段名作为索引;
     MySQL该表中字段名相同名字的该表中索引已存在,则MySQL尝试使用该表中字段名_1作为索引;
     MySQL该表中字段名_1相同名字的该表中索引已存在,则MySQL尝试使用该表中字段名_2作为索引;
     MySQL该表中字段名_2相同名字的该表中索引已存在,则MySQL尝试使用该表中字段名_3作为索引;
     MySQL以此类推,直到找到该表中不存在的索引名字。
    
  2. 添加主键(PRIMARY KEY)索引

     ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
    
  3. 添加唯一(UNIQUE)索引

     # 出于工作效率考虑,MySQL不允许对数据类型为TEXT或BLOB的字段添加唯一索引
     ALTER TABLE `表名` ADD UNIQUE 索引名(字段名);
    
  4. 添加普通(INDEX)索引

     ALTER TABLE `表名` ADD INDEX 索引名(`字段名`);
    
  5. 添加全文(FULLTEXT)索引

     ALTER TABLE `表名` ADD FULLTEXT 索引名(字段名);
    
  6. 同时添加多列索引

     ALTER TABLE `表名` ADD UNIQUE 索引名1(`字段1`), ADD INDEX 索引名2(`字段2`);
    
  7. 删除索引

     SHOW INDEX FROM `表名`;
     ALTER TABLE `表名` DROP INDEX 索引名;
    
  8. 全文索引注意事项

     # 注意: 全文索引只能用于数据库引擎为MYISAM的数据表,全文索引只能对英文进行全文检索,目前无法
     # 对中文进行全文检索,如果需要对包含非英文文本数据进行全文检索,我们需要采用Sphinx/Coreseek 
     # 技术来处理中文,或者不通过数据库而是通过其它搜索工具如:elasticsearch来实现字符串相似匹配。
    
     # 使用全文索引不是通过'LIKE'关键字进行规制匹配,而是使用特有的语法才能使用全文索引进行查询,例
     # 如,我们想要在`表名`的'字段1'、'字段2'中,全文检索指定的'查询字符串',我们可以如下编写SQL语句:
    
     SELECT * FROM `表名` WHERE MATCH (字段1, 字段2) AGAINST ('查询字符串');
    
  9. 查看指定表所有索引

     SHOW INDEX FROM `表名`;
    
  10. 查看指定数据库所有索引

     # *可以换成查询索引指定属性,如:TABLE_NAME,COLUMN_NAME,INDEX_NAME等
     SELECT
         *
     FROM
         INFORMATION_SCHEMA.STATISTICS
     WHERE
         TABLE_SCHEMA = '数据库名'
    
  11. 索引名允许重复规则

     同一表内索引名不允许重复
    
  12. 索引值允许重复规则

     主键索引、唯一索引值不允许重复
    
  13. 外键、级联操作

     # 为table_2.slave_key添加外键fk_slave_key,并且当table_1.main_key改变时,table_2.slave_key同样改变
     ALTER TABLE table_2 ADD CONSTRAINT fk_slave_key FOREIGN KEY (slave_key) REFERENCES table_1(main_key) ON UPDATE CASCADE;
    
     # 为table_2.slave_key添加外键fk_slave_key,并且当table_1.main_key删除时,table_2.slave_key同样删除
     ALTER TABLE table_2 ADD CONSTRAINT fk_slave_key FOREIGN KEY (slave_key) REFERENCES table_1(main_key) ON DELETE CASCADE;
    
     # 为table_2.slave_key添加外键fk_slave_key,并且当table_1.main_key改变或删除时,table_2.slave_key同样改变或删除
     ALTER TABLE table_2 ADD CONSTRAINT fk_slave_key FOREIGN KEY (slave_key) REFERENCES table_1(main_key) ON UPDATE CASCADE ON DELETE CASCADE;
    
  14. 索引失效

     LIKE 中使用左%(最左原则)
     WHERE条件中使用OR
     索引列中大量重复数据值
     索引列中存在NULL和非NULL
    
  15. 自增主键节点设置为指定值

     ALTER TABLE `表名` AUTO_INCREMENT=指定值;
    

查询优化

  1. 合理索引很重要

     # 需要索引(增加查询速度)
         # 表的字段唯一约束
         # 查询中排序的字段
         # 直接条件查询的字段
         # 查询中与其它表关联的字段
         # 查询中统计或分组统计的字段
    
     # 不要索引(不能增加查询速度,还会导致增删改变慢)
         # 表记录太少
         # 字段内重复值过多或只有指定的几个值的字段(比如性别)
         # 经常增删改的表(因为同时要对索引增删改)
         # 经常和主字段一起放在查询条件的字段(因为主字段已经对数据进行了筛选,剩余数据较
         # 少再通过索引查询意义不大)
    
  2. 索引数据别含NULL

     # 列中包含有NULL值整列将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一
     # 列对于此复合索引就是无效的。所以我们在数据库设计时不要让索引字段的默认值为NULL。
    
  3. 复合索引要用好

     # 有一条SQL语句是这样的
     WHERE
         a = 1234
     AND b = 2345
     # 如果在a、b分别创建索引的话能提高效率,但不如复合索引效率更高,所以我们可以为a、b创建
     # 联合索引,联合索引特性:当创建联合索引(a,b,c)时,相当于创建了(a,b,c)、(a,b)、(a)
     # 三个索引(最佳左前缀特性)
    
  4. 能用EXISTS别用OR,索引尽量别失效

     SELECT
         *
     FROM
         a
     WHERE
         user_type = 1 OR user_type = 2
     # 如果user_type的值只存在0,1,2这三种情况,可以替换为
     SELECT
         *
     FROM
         a
     WHERE
         EXISTS(
             SELECT
                 user_type
             FROM
                 b
             WHERE
                 user_type = a.user_type
         )
    
  5. 条件顺序很重要(能够排除更多条数据的条件要先写)

     # 查询用户表中,25岁的男性用户
     # 较慢的方式
     WHERE
         user_sex = 'unknown'
     AND user_age = 25
     # 较快的方式
     WHERE
         user_age = 25
     AND user_sex = 'unknown'
     # 因为一个用户的年龄有多种情况比如0-200,而性别却只有'man','woman','unknown'
     # 年龄能从所有数据中筛除大量数据,然后从剩余较少数据中对性别进行筛选,所以更加快速
    
  6. 连表要比子句好

     # 能连表取得的数据,不要使用子查询
    

字符串连接

  1. 不要尝试将字符串和其它类型数据进行拼接,要将其它类型数据转换为字符串,再进行拼接
     # 以SELECT为例
     SELECT CONCAT('第', CONVERT(chapter_order,CHAR),'章 ', chapter_name) FROM chapter WHERE chapter_name NOT LIKE '第%'
    

字符串转正整数

  1. 转整数
     # float结果会四舍五入
     CAST([字段名] / 5 AS UNSIGNED)
    

获取指定范围内的随机整数

  1. 获取指定范围内的随机整数
     # 获取100-200之间的整数
         SELECT FLOOR(100 + RAND() *(200 - 100 + 1));
     # 原理
         SELECT RAND();会返回一个大于0小于1的小数
    

UNIX时间转YYYY-MM-DD HH:II:SS

  1. 举例:年月日时分秒转时间戳,年月日时分秒转年月日,时间戳转年月日时分秒
     SELECT
         DATE_FORMAT( FROM_UNIXTIME( test.time_stamp ), '%Y-%m-%d %H:%i:%s' ) AS `date_init`,        -- 无法转换时这样来使用
         UNIX_TIMESTAMP(test.date_time) AS time_stamp,                                               -- 年月日时分秒转时间戳
         DATE_FORMAT(test.date_time, '%Y-%m-%d') AS `date`,                                          -- 年月日时分秒转年月日
         FROM_UNIXTIME(test.time_stamp, '%Y-%m-%d %H:%i:%s') AS date_time                            -- 时间戳转年月日时分秒
     FROM
         test
     WHERE
         test.id > 0
    

语法顺序

  1. 查询语法顺序
     SELECT
     FROM
     WHERE
     LEFT/RIGHT/INNER JOIN ON
     GROUP BY
     ORDER BY
     LIMIT
    

统计语法

  1. 查询统计语法
     SUM()       -- 累加
     COUNT()     -- 计数
     AVG()       -- 平均
     +           -- 加法
     -           -- 减法
     *           -- 乘法
     /           -- 除法
     %           -- 余数
    

条件判断

  1. 判断语法
     >
     <
     =
     >=
     <=
     !=
     <>
     OR
     AND
     IS NULL
     IN()
     NOT IN()
     LIKE()
     NOT LIKE()
    

连表查询

  1. 左连接

     SELECT
         *
     FROM
         table1
     LEFT JOIN table2 ON table1.id = table2.id_1
     WHERE
         table1.id = 1
    
  2. 右连接

     SELECT
         *
     FROM
         table1
     RIGHT JOIN table2 ON table1.id = table2.id_1
     WHERE
         table1.id = 1
    
  3. 内连接

     # 方式1
     SELECT
         *
     FROM
         table1
     INNER JOIN table2 ON table1.id = table2.id_1
     WHERE
         table1.id = 1
    
     # 方式2
     SELECT
         *
     FROM
         table1,table2
     WHERE
         table1.id = table2.id_1
     AND
         table1.id = 1
    

子查询

  1. 查询表内pay_status=3的用户数
     SELECT
         COUNT (*) AS total
     FROM
     (
         SELECT
             user_id,
             COUNT (user_id)
         FROM
             user_pay
         WHERE
             pay_status = 3
         GROUP BY
             user_id
     ) AS user_data
    

备份与恢复

  1. 备份数据库

     mysqldump -u root -p --opt 数据库名>备份名;
     进入到库目录,拷贝文件
    
  2. 恢复数据库

     mysql -u root -p 数据库名<备份名;
     恢复时,数据库必须存在
    

清空表

  1. 初始化一个表为全新空表

     # 该操作十分危险,数据无法恢复!
     # 该操作十分危险,数据无法恢复!
     # 该操作十分危险,数据无法恢复!
     TRUNCATE TABLE 表名;
    
  2. 逐条清空表内数据

     DELETE FROM 表名;
    

数据库权限管理

  1. 数据库授权管理

     # 数据库授权格式
     GRANT 权限名 ON 数据库名.数据表名 TO '数据用户'@'访问IP' IDENTIFIED BY '用户密码' WITH GRANT OPTION;
    
     # 为任意数据库表 任意操作 分配root用户,仅限本地访问
     GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root12345' WITH GRANT OPTION;
     GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'root12345' WITH GRANT OPTION;
     GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY 'root12345' WITH GRANT OPTION;
    
     # 为任意数据库表 任意操作 分配MySQL用户,任意IP访问(安装调试阶段、本地开发使用,用后即删)
     GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' IDENTIFIED BY 'mysql12345' WITH GRANT OPTION;
    
     # 为任意数据库表 增删改查 分配单独用户,为不同的业务分配专属用户
     GRANT INSERT ON *.* TO 'insert1'@'127.0.0.1' IDENTIFIED BY 'insert12345' WITH GRANT OPTION;
     GRANT DELETE ON *.* TO 'delete1'@'127.0.0.1' IDENTIFIED BY 'delete12345' WITH GRANT OPTION;
     GRANT UPDATE ON *.* TO 'update1'@'127.0.0.1' IDENTIFIED BY 'update12345' WITH GRANT OPTION;
     GRANT SELECT ON *.* TO 'select1'@'127.0.0.1' IDENTIFIED BY 'select12345' WITH GRANT OPTION;
    
     # 刷新权限
     FLUSH PRIVILEGES;
    

MySQL事务

  1. 比喻说明

     事务就好比你要完成付钱、收货、找零,这三个步骤要同时正确或同时没有发生。
     事务保证了数据处理的可靠性。
    
  2. 事务支持

     MySQL事务在使用时,要求数据库引擎为InnoDB。
    
  3. 事务必须满足的条件

     原子性: 一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
     一致性: 在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则。
     隔离性: 数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
     持久性: 事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
    

MySQL触发器

  1. 比喻说明

     触发器类似于电视机的遥控器,当按下遥控器按钮时,必然会触发电视机的某些动作。
     触发器降低了开发的复杂性。
    
  2. MySQL触发器TRIGGER优点

    1. 提供了检查数据完整性的替代方法
    2. 对于审核表中数据的更改非常有用
    3. 可以捕获数据库层中业务逻辑中的错误
    4. 通过使用触发器在对表中的数据进行更改之前或之后自动调用触发器
  3. MySQL触发器TRIGGER缺点

    1. 批量化的数据操作会导致触发器工作效率低下
    2. 触发器不能修改只能删除后重建,且重建过程中不能保证数据的一致性
  4. 用法格式

     # 创建触发器
     CREATE TRIGGER 触发器名称 BEFORE/AFTER UPDATE/DELETE/INSERT ON 表名 FOR EACH ROW
     BEGIN
         SQL语句;
     END;
    
     # 删除触发器
     DROP TRIGGER IF EXISTS 表名.触发器名称
    
     # 触发器不支持修改,但是能删除后重建
    
     # 查询表中所有触发器
     SELECT * FROM 表名 WHERE xtype = 'TR'
     # 显示触发器的定义
     EXEC sp_helptext '触发器名'
     # 查看触发器的有关信息
     EXEC sp_help '触发器名'
     # 查看表中的触发器类型
     EXEC sp_helptrigger '表名'
    
  5. 触发器举例(带触发前操作数据参数)

     -- 假设有表
     -- user(id, user_name, insert_time)
     -- user_log(id, user_name, insert_time)
     -- 每当user表插入一条数据之后,触发向user_log表插入一条数据
    
     CREATE TRIGGER add_user_log AFTER INSERT ON `user` FOR EACH ROW
     BEGIN
     INSERT INTO user_log(
         `id`,
         user_name,
         insert_time
     ) VALUES (
         NEW.`id`,
         NEW.user_name,
         NEW.insert_time
     );
    
     END;
    
     -- OLD用在删除和修改,NEW用在添加和修改
    

存储过程参考链接

  1. 比喻说明

     存储过程就好比你自己写了一个插件,以后写代码的时候不用重新完成整套逻辑,而是通过调用插件来满足要求。
     存储过程降低了开发的复杂性。
    
  2. 什么是MySQL存储例程

     存储例程是存储在数据库服务器中的一组sql语句,通过在查询中调用一个指定的名称来执行这些sql语句命令.
    
  3. MySQL存储过程、存储例程、存储函数区别

     MySQL存储例程实际包含了存储过程和存储函数,它们被统称为存储例程。其中存储过程主要完成在获取记
     录或插入记录或更新记录或删除记录,即完成select insert delete update等的工作。而存储函数
     只完成查询的工作,可接受输入参数并返回一个结果。
    
  4. MySQL存储过程PROCEDURE优点

     假设现在有一个应用程序包含了基于web、基于桌面两种方式,现在要修改其中的一个查询sql语句,那么
     我们可能要同时修改他们中对应的查询sql语句,当我们的应用程序很庞大很复杂的时候问题就出现这,不
     易维护!另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏,而存储例程正好可
     以帮我们解决这些问题。
    
  5. 用法格式

     # 创建存储过程(IN为例)
     DELIMITER //
     CREATE PROCEDURE stat_book(IN time INT)
     BEGIN
     SELECT
         COUNT(*) AS book_count,
         SUM(book_size) AS size_count
     FROM
         book
     WHERE
         insert_time > time;
     END;
     //
     DELIMITER;
    
     # 调用存储过程
     CALL stat_book(0);
    
  6. 常用存储过程

     IN
     OUT
     INOUT
    

MySQL通用表表达式CTE

  1. 比喻说明

     通用表表达式就好比用一个词语描述一个复杂的概念,我们表达这个复杂概念时,只需要用这个词语来表示。
     通用表达式大大增加了SQL语句的可读性。
    
  2. MySQL通用表表达式CTE优点

    1. CTE使语句更易读
    2. CTE可以被多次引用
    3. CTE可以引用其他的CTE,派生表不能引用派生表
    4. CTE只会被创建一次,不管被引用了多少次,使性能提升
  3. 用法示例

     WITH 通用表表达式名 AS (
         查询语句
     )
     SELECT * FROM 通用表表达式名;
    
@耿志环 2012-∞ 冀ICP备17033181号, powered by Gitbook修订: 2019-02-25 13:37:34

results matching ""

    No results matching ""