Database
11 Sep 2017

mysql常用语句(随手记)

  1. select * from table
  2. update table set coumn=1 where id=1
  3. delete from table where id=1
  4. insert into table(id,nam) values(1,'aa'),(2,'bb')
  5. SELECT sex,sum(age),GROUP_CONCAT(age),GROUP_CONCAT(name) FROM test group by sex
  6. 复制表结构以及数据
    1. Insert into Table2(field1,field2,...) select value1,value2,... from Table1 #Table2必须存在（mysql不支持）
    2. SELECT vale1, value2 into Table2 from Table1 #Table2不存在
    3. CREATE TABLE 新表 SELECT * FROM 旧表
    4. 只复制表结构 CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2 CREATE TABLE 新表 LIKE 旧表
  7. 创建索引
    1. ALTER TABLE 表名 ADD INDEX 索引名 (哪些列)
    2. ALTER TABLE 表名 ADD UNIQUE (哪些列)
    3. ALTER TABLE 表名 ADD PRIMARY KEY (哪些列)
    4. CREATE INDEX index_name ON table_name (column_list)
    5. CREATE UNIQUE INDEX index_name ON table_name (column_list)
  8. 删除索引
    1. DROP INDEX index_name ON talbe_name
    2. ALTER TABLE table_name DROP INDEX index_name
    3. ALTER TABLE table_name DROP PRIMARY KEY
  9. 查看索引 show index from tblname; show keys from tblname;
  10. 日期格式化及比较
    1. select str_to_date('20181111','%Y%m%d %H:%i:%s')
    2. select from_unixtime(javatime/1000,'%Y%m%d %H:%i:%s');
    3. select unix_timestamp('2013-01-01 10:10:10')*1000;
    4. select * from product where date(add_time) between '2013-01-01' and '2013-01-31'
    5. select * from product where Year(add_time) = 2013 and Month(add_time) = 1
    6. SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; #date_col的值是在最后30天以内：
    7. SELECT exam.id FROM exam exam WHERE DATE_FORMAT(exam.date,"%Y-%m-%d %H:%i:%s") in ('2017-02-22')
  11. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
  12. select arc.creNo, COUNT(arc.creNo) AS coun from t_staff_archive arc GROUP BY arc.creNo HAVING coun>1; #重复数据
  13. SELECT COUNT( arc.id) FROM archive arc WHERE NOT EXISTS ( SELECT DISTINCT qua.id FROM qualification qua WHERE arc.id=qua.id );
  14. SELECT COUNT( arc.id) FROM archive arc WHERE arc.id not in ( SELECT DISTINCT qua.id FROM qualification qua WHERE arc.id=qua.id );
  15. SELECT COUNT(*) FROM t_staff_training_plan plan WHERE plan.qualificationType NOT in (SELECT dic.code FROM dictionary dic WHERE dic.groupType ='CYZGLB_D' );
  16. select (SELECT COUNT(* ) FROM qualification1 qual)-( SELECT COUNT(*) FROM t_staff_qualification2 qual2 );
  17. select LENGTH(No),LEFT(oldNo,6),RIGHT(oldNo,6) FROM archive;
  18. UPDATE archive SET No= CONCAT(LEFT(oldNo,6),RIGHT(oldNo,6));
  19. update 表名 set 字段名=replace(字段名,"Z01","Z02")
  20. select concat('11','22','33'); #112233 select concat('11','22',null); #NULL
    concat_ws(',','11','22','33') #11,22,33 concat_ws(',','11','22',NULL); #11,22 select id,group_concat(distinct name order by name desc separator ';') from aa group by id; select repeat('ab',2); #abab
  21. 分组统计 
    EXPLAIN  
SELECT  count(*) AS '总数',  
    count(  
        CASE oup.status  
        WHEN '1' THEN  
            oup.id  
     END  
) AS '未绑定' FROM passport oup
   # 方案二：
SELECT  count(*) AS '总数' FROM lab_org_uc_passport oup   
UNION ALL  
SELECT  count(*) AS '未绑定' FROM lab_org_uc_passport oup WHERE oup.status = '1'
  22. 查询数据带序号 
        SELECT (@i:=@i+1) as '序号',FROM_UNIXTIME( val.createTime/1000,'%Y-%m-%d') 
AS '操作日期', FROM eval_record eval,archive arc,(select @i:=0) as it  
WHERE eval.staffId=arc.archiveId order by eval.operator;
  23. 当起始不是模糊查询时，其实是可以用索引的 
    explain extended select * from users where email like '303578599%'
explain extended select * from users where email like '303%578599%'
  24. mysql 临时表
    1. 创建 
      -- SET SESSION tmp_table_size=64*1024*1024*1024 
SET SESSION max_heap_table_size=2*1024*1024*1024;
DROP TABLE IF EXISTS `tmp_table`;
create temporary table tmp_table  ENGINE=memory (
select distinct a.archiveId,a.staffName
  left join apply qa on q.applyId = qa.applyId 
  left join exam e on q.applyId = e.applyId
  where 1=1 );

      create temporary  TABLE `temtable` (
  `jws` varchar(100) character set utf8 collate utf8_bin NOT NULL,
  `tzlb` varchar(100) character set utf8 collate utf8_bin NOT NULL,
  `uptime` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1″

      mysql创建临时表，本连接断开自动删除表并释放所有空间

  25. 直接将查询结果导入临时表 
      create temporary table tmp_table  TYPE = HEAP SELECT * FROM table_name
  insert into temtable (select a,b,c,d from tablea)
  #在内存中创建临时表：
  create  temporary table tmp_table (
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
  ) TYPE = HEAP  ENGINE=MEMORY DEFAULT CHARSET=gbk
  26. 查看临时表 show create table
  27. 重命名临时表 alter table orig_name rename new_name
  28. 复制临时表得到一个新的临时表 create temporary table new_table select * from old_table
  29. 手动删除 DROP TEMPORARY TABLE IF EXISTS temp_tb

  30. 执行释放内存 DELETE FROM heap_table或DROP TABLE heap_table

  31. 【使用临时表的场景】
    1. ORDER BY子句和GROUP BY子句不同， 例如：ORDERY BY price GROUP BY name；
    2. 在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
    3. ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
    4. SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可
  32. 【直接使用磁盘临时表的场景】
    1. 表包含TEXT或者BLOB列；
    2. GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
    3. 使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列
  33. 【临时表相关配置】 tmp_table_size：指定系统创建的内存临时表最大大小； max_heap_table_size: 指定用户创建的内存表的最大大小
  34. 【如何判断使用了临时表？】 使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表
  35. 自定义排序顺序 ORDER BY FIELD(排序字段,3,4,0,2,1)
  36. 修改字段默认值 alter table t_vehicle alter column tonnage drop default; alter table t_vehicle alter column tonnage set default 0;
  37. CREATE table t_vehicle222 SELECT veh.* FROM t_vehicle veh,test aa where aa.vehNo=veh.vehNo;
  38. 修改字段属性 alter table vehicle modify column dateEnd varchar(20);
  39. 查找veh开头的表 select * from information_schema.tables where TABLE_NAME like 'veh';
  40. remark最大长度 SELECT MAX(LENGTH(remark)) FROM vehicle ;
  41. vehicle所有字段属性 DESC vehicle
  42. select if(type='FREIGHT',"普通","危险") as '类型' FROM vehicle
  43. INSERT INTO dic (code,label) VALUES('531','济南'),('532','青岛');
  44. if,case语句使用总结 
    select if(sva=1,"男","女") as ssva from taname;
select CASE 1 WHEN 1 THEN 'one'  WHEN 2 THEN 'two' 
    ELSE 'more' END as ssva from taname;
SELECT IFNULL(1,0);        # 1
SELECT IFNULL(NULL,10);    # 10
   create procedure dbname.Grade(stu_no varchar(20),cour_no varchar(10));
  BEGIN 
  declare stu_grade float;  
  select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no;  
  if stu_grade>=90 then 
    select stu_grade,'A';  
  elseif stu_grade<90 and stu_grade>=80 then 
    select stu_grade,'B';
  else 
    select stu_grade,'E';  
  end if;  
  END;
  45. 两个结果集合并 
    select a.id, a.name FROM a
union all 
SELECT b.id, b.name FROM b
  46. 区间分组 
    ELT(N,str1,str2,str3,...)
<!-- 如果N= 1，返回str1，如果N= 2返回str2，等等。。ELT()是FIELD()反运算。 -->
INTERVAL(N,N1,N2,N3,...)
<!-- 如果 N < N1，返回0，如果N < N2 返回1，等等。。 -->
  47. 正则表达式 
    SELECT * FROM `user` WHERE `name` LIKE regexp'3|4';
  48. 导出 
    <!-- 导出一个库 -->
mysqldump --user=db_user --password=pwd --complete-insert=TRUE --default-character-set=utf8 --skip-triggers dbname > "./dbname.sql"
<!-- 导出一个库，并压缩 -->
mysqldump --user=db_user --password=pwd --complete-insert=TRUE --default-character-set=utf8 --skip-triggers dataBase  | gzip > "./dataBase.sql.$(date +%Y%m%d%H%M%S).tar.gz"
<!-- 导出一个表 -->
mysqldump -u db_user -p pwd dbname tablename > tablename.sql 
<!-- 导出一个数据库结构 -->
mysqldump -u db_user -p pwd -d --add-drop-table dbname> d:\dbname.sql
  49. 导入 
    mysql --user=root --password=_pwd
use dataBase
source ./dataBase.sql

    shell文件 import.sh

    mysql --user=root --password=_pwd;
use $1;
source "./$2";
exit;

    使用 ./import.sh dataBase dataBase.sql

    mysql -u用户名 -p密码 数据库名 < 数据库名.sql
  50. 类似 || 的操作 
    SELECT  if(name is null||name ="",id,name) AS aa FROM `user`;
  51. 字节长度： LENGTH('鲁AA0295·')
  52. 字符串长度CHARACTER_LENGTH('鲁AA0295·')
  53. 字符串是否包含某字符locate('·','鲁AA0295·')
  54. 逗号分隔的字符串是否包含 find_in_set('3','3,13')
  55. 按cType分组并且cStatus=4 ，按cNo排序后每组取前2条排序 
    SELECT cert.cType,cert.cNo,cert.cStatus FROM cert_entity  cert 
WHERE (SELECT COUNT(*) FROM cert_entity cert1 WHERE cert.cType=cert1.cType 
AND cert1.cStatus=4 AND
CAST(cert.cNo AS signed)>CAST(cert1.cNo AS signed) )<2 AND   cert.cStatus=4 
ORDER BY CAST(cert.cNo AS signed);
  56. 加6年减1天 DATE_SUB(DATE_ADD('2016-05-03',Interval 6 YEAR),Interval 1 DAY )
Tags: