DataMoney.net

Mysql-Sql-常用操作

andy发表: 

#>1.表操作
  

  ---- 创建表  ---- 
  CREATE table tablename(field1 varchar(50), field2 int(10))

  ---- 使用旧表创建新表  ---- 
  CREATE table newtable LIKE oldtable
  CREATE table 表名 as SELECT 列1, 列2... FROM 旧表 definition only

  ---- 删除表  ---- 
  DROP table 表名
  
  ---- 显示所有表  ---- 
  SHOW tables
  
  ---- 显示表详情  ---- 
  desc 表名
  SHOW CREATE table 表名
  
  ---- 清空表  ---- 
  TRUNCATE table 表名
  DELETE FROM 表名

  ---- 修改表名  ---- 
  ALTER table 原名 RENAME to 新名

  ---- 添加列  ---- 
  ALTER table 表名 ADD column 列名 类型
  
  ---- 删除列  ---- 
  ALTER table 表名 DROP column 列名
  
  ---- 修改列名  ---- 
  ALTER table 表名 CHANGE 原名 新名 类型 
  
  ---- 修改列类型  ---- 
  ALTER table 表名 MODIFY 列名 类型
  
  ---- 更新数据  ---- 
  UPDATE 表名 SET 字段=值 WHERE 条件
  
  ---- 删除数据  ---- 
  DELETE FROM 表名 WHERE 条件
  
  ---- 插入数据  ---- 
  INSERT INTO 表名(字段1, 字段2...) VALUES(值1, 值2...)
  ---- 批量插入 ----
  INSERT INTO `table_name` 
    (`column1`,`column2`,`column3`,`column4`)
    VALUES
    ('value1-1','value1-2','value1-3','value1-4'),
    ('value2-1','value2-2','value2-3','value2-4'),
    ('value3-1','value3-2','value3-3','value3-4');


  ---- 排序  ---- 
  SELECT * FROM 表名 ORDER BY 字段 [desc|asc]
  
  ---- 统计行数  ---- 
  SELECT COUNT(*) FROM 表名
  

#>2.数据库
  

  ---- 创建数据库  ---- 
  CREATE DATABASE 库名
  
  ---- 删除数据库  ---- 
  DROP DATABASE 库名
  
  ----  导出命令  ----
  mysqldump -uroot -p123456  --lock-all-tables --flush-logs yourdbname > yourdbname.sql
  lock-all-tablesp 加了锁表防止你在导的时候数据还在更新变化

  ---- 导入命令-先登陆进入mysql  ---- 
  source $path/yourdbname_2019.sql
  
 
#>3.取巧操作
  #>3.1.批量处理技巧-CASE WHEN 函数的使用
  需求:李家5个儿子ID1-5,每个名字加上名字,每个id有不同的名字是吧

  

  ----  更新5条相同的名字,这简单如下   ----
  UPDATE `table` SET `name`= '李家儿子' WHERE id>0 AND id<6

  ---- 更新5条,各有各的名字,一般写5条sql操作5次 ----
  UPDATE `table` SET `name`= '李一' WHERE id=1;
  UPDATE `table` SET `name`= '李二' WHERE id=2;
  UPDATE `table` SET `name`= '李三' WHERE id=3;
  UPDATE `table` SET `name`= '李四' WHERE id=4;
  UPDATE `table` SET `name`= '李五' WHERE id=5;

  ---- CASE 的写法 ----
  UPDATE `table` SET `name`= 
    CASE `id`
      WHEN id=1 THEN '李一' 
      WHEN id=2 THEN '李二' 
      WHEN id=3 THEN '李三' 
      WHEN id=4 THEN '李四' 
      WHEN id=5 THEN '李五'
    END
    WHERE `id` IN(1,2,3,4,5);
  
  ---- CASE查询 -----  
  SELECT 
    CASE `city`
      WHEN '长沙' THEN '湖南' 
      WHEN '衡阳' THEN '湖南'
      WHEN '海口' THEN '海南' 
      WHEN '三亚' THEN '海南'
      ELSE '其他' 
    END 
    AS province,
    SUM(nums) 
  FROM `pepole`
  GROUP BY `province`;

  

    Q:为什么WHERE条件不是 WHERE `id` >0 and `id`<6 ？
    A:这种写法是合法可行的,但是0-6之间会全部覆盖.如果[id=5,name=李五]这条你不想要修改,条件范围会还是修改id=5的数据.反之如果表中没有id=8的这条数据,你写了when id=8 也是可以的,建议用WHERE IN 改哪些写哪些！！    
    现在大部分框架都已集成有此功能,batchsave方法等,原理是一样的.


  #>3.2.高效的状态统计 COUNT(`status`=1 or NULL)
  统计 users 表中会员性别分类统计，会员账户状态分类统计
  sex字段表示性别[-1未知|0女|1男]; 
  status字段表示用户状态[0|新注册|1付款会员|-1被冻结];
  

  SELECT COUNT(`id`) total,
    COUNT(`sex`=-1 OR NULL) unknow_sex,
    COUNT(`sex`=1 OR NULL) male,
    COUNT(`sex`=0 OR NULL) female,
    COUNT(`status`>-1 OR NULL) active_users,
    COUNT( (`status`=0 AND `sex`=0 ) OR NULL) new_female_users,
    COUNT(`status`=1 OR NULL) pay_user
  FROM `users`;
  
         
  where in (array $list), $list 的长度不要超过命中总条数，否则会引起索引失败导致效率降低


杀死一个失控的事务 
kill trx_mysql_thread_id, select trx_mysql_thread_id from information_schema.innodb_trx.

mysql 密码重置命令行执行
mysql_secure_installation

5. 复制一张表结构
```
CREATE TABLE `your_table_name` LIKE `destination_table_name`;
INSERT INTO `your_table_name` SELECT * FROM `destination_table_name`;

6.内置函数
trim()  返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x) 返回字符串 x 的长度
replace(x,y,z)  将字符串 z 替代字符串 x 中的字符串 y
upper(x)  将字符串 x 的所有字母变成大写字母
lower(x)  将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y)  返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x)  返回 x 个空格
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
reverse(x)  将字符串 x 反转
© Since 2018 by DataMoney.net