mysql应用笔记

mysql应用.md

DDL(Data Definition Language)数据定义语言:
对数据库中的某些对象(例如，database,table)进行管理，如create、 alter、 truncate、rename…… 不能回滚
DML(Data Manipulation Language)数据操纵语言:
对数据库中的数据进行一些简单操作，sql语句，select、insert、update、delete…… 手动控制事务，可以回滚alter
DCL（Data Control Language）：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

一、连接

mysql -u[count] -p [password]:u和账户名之间可以没有空格，省略password回车后提示输入密码
-h [ip]:远程连接
exit:退出
mysql -h127.0.0.1 -P3306 -uroot -Nse’select concat(“kill “, id, “;”) from information_schema.processlist where user = “tbdw”;’

二、密码修改

mysqladmin -u[count] -password [password]:没有添加
mysqladmin -u[count] -p[old] -password [new]:有，修改
mysql>set PASSWORD=PASSWORD(‘password’):登录mysql后修改

三、用户权限grant,revoke

grant select,insert,update,delete,drop,alter on 数据库名.表名 to 用户名@登录主机
eg:grant select on database.* to ‘zjk’@’%’ //这里一般不要对某台主机授权，如:’zjk’@’10.100.101.101’
eg:grant select,insert,update,delete on . to test1@”%” Identified by “abc”;abc为密码
show grants for 用户名 //如果对某台主机授权的，查看有没有授权成功需要show grants for ‘zjk’@’10.100.101.101’
revoke和grant语法相同，只是把to 换成from
eg:revoke all on . from ‘zjk’@’%’; revoke delete on . from ‘zjk’@’10.100.101.101’;
select host,user from mysql.user where user=’monitor’;查看机器上某个用户赋权的机器

四、创建删除数据库

show databases;
create database 库名：一般创建数据库紧接着就是分配权限然后创建密码
drop database 库名
drop database if exists drop_database;先判断数据是否存在
use 数据库名
mysql> select database();查看选择使用的数据库

五、数据库函数

1.显示MYSQL的版本
mysql> select version();   ==show variables like 'version';
+-----------------------+ 
| version()             | 
+-----------------------+ 
| 6.0.4-alpha-community | 
+-----------------------+ 
1 row in set (0.02 sec) 

2. 显示当前时间
mysql> select now();    select unix_timestamp();时间戳
+---------------------+ 
| now()               | 
+---------------------+ 
| 2009-09-15 22:35:32 | 
+---------------------+ 
1 row in set (0.04 sec) 

3. 显示年月日
SELECT DAYOFMONTH(CURRENT_DATE); 
+--------------------------+ 
| DAYOFMONTH(CURRENT_DATE) | 
+--------------------------+ 
|                       15 | 
+--------------------------+ 
1 row in set (0.01 sec) 

SELECT MONTH(CURRENT_DATE); 
+---------------------+ 
| MONTH(CURRENT_DATE) | 
+---------------------+ 
|                   9 | 
+---------------------+ 
1 row in set (0.00 sec) 

SELECT YEAR(CURRENT_DATE); 
+--------------------+ 
| YEAR(CURRENT_DATE) | 
+--------------------+ 
|               2009 | 
+--------------------+ 
1 row in set (0.00 sec) 

4. 显示字符串
mysql> SELECT "welecome to my blog!"; 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
1 row in set (0.00 sec) 

5. 当计算器用
select ((4 * 4) / 10 ) + 25; 
+----------------------+ 
| ((4 * 4) / 10 ) + 25 | 
+----------------------+ 
|                26.60 | 
+----------------------+ 
1 row in set (0.00 sec) 

6. 串接字符串
select CONCAT(f_name, " ", l_name) 
AS Name 
from employee_data 
where title = 'Marketing Executive'; 
+---------------+ 
| Name          | 
+---------------+ 
| Monica Sehgal | 
| Hal Simlai    | 
| Joseph Irvine | 
+---------------+ 
3 rows in set (0.00 sec) 
注意：这里用到CONCAT()函数，用来把字符串串接起来。另外，我们还用到以前学到的AS给结果列'CONCAT(f_name, " ", l_name)'起了个假名。

7.分隔字符串
select substring_index(str,'.',1) form xx; 若str=ww.google.com 则返回www,截取.前的字符串
select substring_index(str,'.',-1) form xx; 若str=ww.google.com 则返回com,截取.后的字符串

8.组合字符串group_concat
SELECT db_name,group_concat(distinct app_name) as app_name  FROM `meta_appname` group by db_name;
db_name通过group聚类了，把多个app_name通过group_concat连接起来，多个以逗号连接

9.DATE_SUB(date,INTERVAL expr type)  时间相减
delete FROM xxx where date(gmt_create) < DATE_SUB(CURDATE(), INTERVAL 180 DAY) //删除从当前时间开始180天之前的数据，当前-180天

10.CURDATE() 当前时间
select FROM_UNIXTIME(1156219870); 时间戳转时间

11.replace(str,stra,strb):将str中所有出现的stra字符串替换为strb字符创

12.select row_count();查询上一条sql变更的行数，主要针对insert update delete

13.select last_insert_id();查询最后插入一行的id

14.select FOUND_ROWS(); 前一条sql查询的行数

五、数据表操作

5.1创建:create table 表名(字段) values(字段值)
命令：create table <表名> ( <字段名1> <类型1> ,..<字段名n> <类型n>);
    例如，建立一个名为MyClass的表，(注意并不是创建下面这张数据表，这张表只是说明字段的修饰)
    字段名 数字类型    数据宽度    是否为空    是否主键    自动增加    默认值
    id  int 4   否   primary key auto_increment   
    name    char    20  否            
    sex int 4   否           0
    degree  double  16  是        
create table table_aaa as select * from table_bbb    复制表bbb为表aaa    
数据类型（data_type）规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型：
数据类型    描述
integer(size)
int(size)
smallint(size)
tinyint(size)
仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)
numeric(size,d)
容纳带有小数的数字。
"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。
char(size)  
容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。
在括号中规定字符串的长度。
varchar(size)   
容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。
在括号中规定字符串的最大长度。
date(yyyymmdd)  容纳日期。
eg1:创建四列
CREATE TABLE Person 
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)
LastName    FirstName   adress  age          
必须要制定数据宽度，
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
id  name    sex degree 创建表相当于是只创建了个表头，里面没有数据的，此时用select * from 表名；也查询不到内容的
可以用show tables；查看都有哪些表
show create table table_name  查看建表结构
desc table_name  查看表中字段详情

5.2删除数据表
命令：drop table <表名>
例如：删除表名为 MyClass 的表
   mysql> drop table MyClass;
DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！
注意：对于一个带分区的表，DROP TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义（.par）文件。
对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。
RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。

5.3表插入数据
命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]
例如：往表 MyClass中插入二条记录, 这二条记录表示：编号(id)为1的名(name)为Tom的成绩(score)为96.45, 编号为2 的名为Joan 的成绩为82.99， 编号为3 的名为Wang 的成绩为96.5。
   mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);省略了字段
等于mysql> insert into MyClass （id,name,score）values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
注意：insert into每次只能向表中插入一条记录，就是每次插入的是一行，其他未指定的字段为NULL，
    等再插入指定的另一个字段时其实是新开辟一行，其他为NULL。
    如果想改每个单元格中的数据要用update，下面讲解
INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;当有主键或者唯一键冲突时候执行UPDATE c=c+1 else执行insert

5.4查询表中的数据
1)、查询所有行
    命令： select <字段1，字段2，...> from < 表名 > where < 表达式 >
    例如：查看表 MyClass 中所有数据
       mysql> select * from MyClass;
2）、查询前几行数据
    例如：查看表 MyClass 中前2行数据并排序
    1.select * from MyClass order by id limit 0,2;
    select一般配合where使用，以查询更精确更复杂的数据。
    2.select * from table limit m,n
        其中m是指记录开始的index，从0开始，表示第一条记录
        n是指从第m+1条开始，取n条。
        select * from tablename limit 2,4
        即取出第3条至第6条，4条记录
    3.如何优化limit
        当一个查询语句偏移量offset很大的时候，如select * from table limit 10000,10 , 最好不要直接使用limit，而是先获取到offset的id后，再直接使用limit size来获取数据。效果会好很多。
        如：
            select * From customers Where customer_id >=(
                select customer_id From customers Order By customer_id limit 10000,1
            ) limit 10;
3)、查询不重复
    |name|id|
    |----|--|
    |zjk1|10|
    |zjk1|11|   
    select distinct name from table;//单个字段  显示|zjk1|                   |zjk1|10|
    select distinct name ,id from table;//多个字段，显示id和name都不重复的  显示|zjk1|11|
    select name ,id from table group by name;//这样才是只显示不重复的name  显示|zjk1|10|
4)、查询不重复的数据出现的次数
    select count(distinct buyer_member_id) from bing_mysupplier;
    注:count(col1):统计出的行数不包括col1=null的行，所以不准确，不推荐使用，用count(*)代替，count(*)是统计的
       count(col1,col2):这个也很危险，如果col1或col2中其中一个全为null，则值为0
5)、查询某个字段出现的频率及其出现次数最多的10个
    select count(*) as cnt,city from table1 group by group order by cnt DESC LIMIT 10;
    select count(*) as cnt,LEFT(city,3) as pref from table1 group by pref order by cnt DESC LIMIT 10;  //前三个字符
5.1)、group与having连用
    select group_concat(distinct cluster_id) from table1 where xx group by instance_id having count(distinct custer_id)>1;
    having和where区别，where的只能是表中有的字段，having可以是group by聚合出来的
6)、排序 order by
    select * from table1 order by id   //按照id升序排序
    select * from table1 order by name,id  //按照name中的字母顺序进行升序排序并根据id的升序对相同的name进行升序排序
    select * from table1 order by id DESC  //降序排序
    select * from table1 order bu name DESC,id ASC   //按照那么降序排序，相同name的行按照id进行升序排序
7)、汇总 group by,table如下:  必须放在order by之前
        |des|num|oth|
        |---|---|---|
        | a | 1 |aaa|
        | a |110|bac|
        | a | 2 |aaa|
        | b | 2 |aae|
        | c | 6 |sse|
    select des,sum(num) as sum1 from table1 group by des  //sum是个聚合函数还有：max,min,avg,first,last,count等
        |des|sum1|
        |---|---|
        | a |113|
        | b | 2 |
        | c | 6 |
    就是按照des汇总一下，列出des不同的，然后再把num改为sum相同a下之和
    和order by连用
    select des,sum(num) as sum1 from table1 group by des order by sum DESC  //然后按照sum将序排序
    多列分组:select des,oth,sum(num) as sum1 from table1 group by des,oth //将des和oth两列全部相同的合并，输出如下
        |des|oth|sum|
        |---|---|---|
        | a |aaa| 3 |
        | a |bac|110|
        | b |aae| 2 |
        | c |sse| 6 |
    注意:oth一定要放在sum函数的前面，也就是说sum要放在最后面，不然会报错
    下面是查询表中所有不重复的des和每个des重复个数，其中count(1)代表第一列的意思，其实就是count(*)
    select des ,count(1)as cnt from table1 group by des order by des order by des desc
8)、查询collect_date为最大时的
SELECT distinct db_name,app_name , app_owner,dba_owner ,bu_name,collect_date FROM `meta_appname` where collect_date = (SELECT DISTINCT collect_date from meta_appname order by collect_date DESC limit 0, 1);
9)、查询字段长度
SELECT LENGTH("阿里巴巴")； 返回为12  utf-8字符集时候
SELECT CHARACTER_LENGTH("阿里巴巴")； 返回为4
10)、强制指定走的索引
select * from xxx force index (idx_xxx) where gmt_create>xxx;//如果一条sql可能会走到多个索引，这样强制制定使用走哪个索引force
11)、动态指定索引
    动态指定索引hit：https://askdba.alibaba-inc.com/libary/control/getArticle.do?articleId=41361
    动态指定索引指的是指定走哪一个索引而不是创建索引
    理论上主备都要做，因为要预防发生主备切换，动态指定索引重启会失效
12)、alisql优化
    select * from update xxx set x=1 where y=2; 返回更新的项


5.5删除表中数据   
1.命令：delete from 表名 where 表达式
例如：删除表 MyClass中编号为1 的记录 
    mysql> delete from MyClass where id=1;
不带where子句的delete删除表中所有数据，但是自增主键id不清零，再插入数据时候从上次id位置继续往下增加
2.命令:truncate table 表名
  快速删除表中所有数据，保留表结构，自增主键id清零
与delete比较:比不带where子句的delete快速，但是删除数据后无法回滚，非常危险，不建议使用

5.6修改表中数据
语法：update 表名 set 字段=新值,… where 条件
   mysql> update MyClass set name='Mary' where id=1;
例子1：单表的MySQL UPDATE语句：
   UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
    mysql> update table1 set age=10 where address='wuxi';
例子2：多表的UPDATE语句：
    UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

5.61增加/修改表中数据replace into   （非常危险，慎用，详情看我的askdba）
语法:replace into 表名(字段) values(xx);
如果想要replace的字段不存在就insert，存在就update

5.7增加字段
命令：alter table 表名 add字段 类型 其他;
例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0
   mysql> alter table MyClass add passtest int(4) default '0'
增加id主键
   alter table xxx add `id` bigint(20) NOT NULL AUTO_INCREMENT,primary key (id);

5.8修改原字段名称及类型：
    mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
修改字段长度
    alter table aaa  modify column title varchar(130);

5.9删除字段：
MySQL ALTER TABLE table_name DROP field_name;

5.10修改字段位置
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST ｜ AFTER 字段名2;
alter table adha_ha_builder modify db_type varchar(32) after biz_type;

5.10修改表名
命令：rename table 原表名 to 新表名;
例如：在表MyClass名字更改为YouClass
   mysql> rename table MyClass to YouClass;
当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。
如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。
RENAME TABLE 在 MySQL 3.23.23 中被加入。

5.11查询表信息
show table status like 'table_name'  :显示表的所有信息，引擎/版本/行数/大小……
show create table 表名:显示创建表的信息。字段名/字段类型/key

6.临时表sql使用
delete from meta_appname where collect_date =  (select collect_date from (select distinct collect_date FROM meta_appname ORDER BY collect_date  LIMIT 0,1) a)
如果不用临时表会报错：You can't specify target table 'meta_appname' for update in FROM clause

六、索引

0.查看索引
    SHOW INDEX FROM table_name
1.加索引
   mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
例子： mysql> alter table employee add index emp_name (name);

2.加主关键字的索引
  mysql> alter table 表名 add primary key (字段名);
例子： mysql> alter table employee add primary key(id);

3.加唯一限制条件的索引(唯一索引)
   mysql> alter table 表名 add unique 索引名 (字段名);
例子： mysql> alter table employee add unique emp_name2(cardnumber);

4.前缀索引
    索引限定的长度为255，但是如果需要在比较长的字段上使用索引怎么办呢，就需要前缀索引
    由于字段长度越长，索引占用空间越大，查询越慢，所以最好使用前缀索引，就是只是用字段的一部分作为索引
    mysql> alter table 表名 add key (列名(x));  //x代表使用字段的前几个字符作为索引
    注意:mysql无法使用前缀索引做order by 和group by,也无法使用个前缀索引做覆盖扫描
5.删除某个索引
   mysql> alter table 表名 drop index 索引名;
例子： mysql>alter table employee drop index emp_name;
6.修改索引
   mysql没有修改索引的语句，需要先删除索引再新建
6.varchar字段不加引号走不到索引
  eg:select * from aaa where offer_id in (12345)   若offer_id是varchar的这样就要全表扫描，走不到索引了，但是int字段加引号照样可以走到索引

七、备份数据库

1.导出整个数据库
导出文件默认是存在mysql\bin目录下
    mysqldump -u 用户名 -p 数据库名 > 导出的文件名    //可同时指定多个库名用空格隔开  -p密码
    mysqldump -u user_name -p123456 database_name > outfile_name.sql
    --databases:后面跟多个数据库用空格隔开
    -all-databases:复制所有数据库

2.导出一个表
    mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名   
    mysqldump -u user_name -p123456 database_name table_name > outfile_name.sql

3.导出一个数据库结构
    mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
    -d 没有数据，意思是只导出库的结构
     –add-drop-table 在每个create语句之前增加一个drop table，如果数据库存在无需手动删除，自动覆盖

4.带语言参数导出
    mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql

例如，将aaa库备份到文件back_aaa中：
　　[root@test1 root]# cd　/home/data/mysql
　　[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

5.导出数据
    SELECT * INTO OUTFILE 'data.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test.table
    SELECT * FROM test.table INTO OUTFILE 'data.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' 
    FIELDS TERMINATED BY ',' 字段间分割符
    OPTIONALLY ENCLOSED BY '"' 将字段包围 对数值型无效
    LINES TERMINATED BY '\n' 换行符

6.导入数据
    LOAD DATA INFILE '/tmp/fi.txt' INTO TABLE test.fii FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

八、还原数据库source

source 文件名；需要登录上mysql后操作，要先用use 数据库名来选择数据库
    若以前没有数据库要create database 库：来新建数据库
mysql -uroot -p 库名 < 文件名  无需登录mysql，在库存在的前提下使用

九、特殊名词

• 1.explain
  sql前加上explain查看执行sql用到了哪些key等详细信息
• 2.profile
  
SELECT @@profiling; 来查看是否已经启用profile，如果profilng值为0，可以通过SET profiling = 1; 来启用。
执行语句select count(*) from roi_summary;
show profiles 查看如下执行的语句
show profile for query xx(id) 查看某个查询的执行过程，性能，耗时等等
show profile cpu for query 1; 查看cpu的消耗情况
show profile memory for query 1; 查看内存消耗
show profile block io,cpu for query 1; 查看io及cpu的消耗
FROM INFORMATION_SCHEMA.PROFILING 系统表

• 2.distinct
  只返回不相同的，可以用来查询某个字段非重复的值：select distinct id form xx;
  如果想查询单独的id，还想看看另外的一个字段就需要特殊
  select test,count(distinct id) as id from xx group by id test id; id是非重复的，test只是输出id行的test

• 3.inner join (等值连接) 只返回两个表中联结字段相等的行

  • left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
  • right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
    语法：
    

    SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号    连接三个表
    
    select * from xxx a inner join xxxx b on a.ip=b.ip;
    == select * from xxx a, xxxx b where a.ip=b.ip; 

十、optimize

• 查看碎片率
  
  • select * from information_schema.tables where table_schema='库名' and table_name='表名'\G; 里面的data_free字段就是碎片率
  • 或: show table status from 库名 like '表名'; 里面的data_free字段就是碎片率
• 操作
  
  • optimize table 表名; 会锁表，只能做备库 udc提任务