MySQL语句

刘超 7月前 ⋅ 5682 阅读   编辑

目录(归类规范参考5.7的说明文档sql-statements

  1、数据定义语句(也就是ddl语句)
    1.1 alter database
      a、修改编码
  2、数据操作语句(也就是dml语句)
  3、复制语句
    3.1 控制从属服务器语句
  4、复合语句
  5、数据库管理语句
    4.1 显示语句
      a、查看数据库连接数
      b、查看处于cached、connected、created、running状态连接数
      c、查看binlog文件大小
      d、查看server_id

  4、表

    a、更新

  5、注释

  6、列

    a、新增列

    b、重名列 

    c、修改列字符集  

  7、约束

  8、权限

  9、sql文件

  10、导入/导出

  11、查看操作日志

    1)、warning

    

一、数据定义语句

  1、alter database

  a、修改编码

将库编码改为utf8mb4 

alter database database_name character set = utf8mb4 collate = utf8mb4_unicode_ci;

三、复制语句

  1、控制从属服务器语句

change master to option [, option] ... [ channel_option ]

option:
   master_bind = 'interface_name'
  | master_host = 'host_name' # ip地址
  | master_user = 'user_name' # 用户名
  | master_password = 'password' # 密码
  | master_port = port_num # 端口
  | master_connect_retry = interval
  | master_retry_count = count
  | master_delay = interval
  | master_heartbeat_period = interval
  | master_log_file = 'master_log_name'
  | master_log_pos = master_log_pos
  | master_auto_position = {0|1}
  | relay_log_file = 'relay_log_name'
  | relay_log_pos = relay_log_pos
  | master_ssl = {0|1}
  | master_ssl_ca = 'ca_file_name'
  | master_ssl_capath = 'ca_directory_name'
  | master_ssl_cert = 'cert_file_name'
  | master_ssl_crl = 'crl_file_name'
  | master_ssl_crlpath = 'crl_directory_name'
  | master_ssl_key = 'key_file_name'
  | master_ssl_cipher = 'cipher_list'
  | master_ssl_verify_server_cert = {0|1}
  | master_tls_version = 'protocol_list'
  | ignore_server_ids = (server_id_list)

五、数据库管理语句

  1、显示语句

  1.1 查看数据库连接数

show full processlist;

show processlist

示例如下

    

  备注

    1) command列,值为sleep的表示是空闲连接

  2、查看处于cached、connected、created、running状态连接数

mysql -h1xxx -uxxx -pxxx -e "show status like 'threads%'"

  3、查看binlog文件大小

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      2979 |
| mysql-bin.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

  4、 查看server_id

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

四、表

通过engine=memory 指定存储引擎(innodb、myisam、memory)

1、创建表

// 创建新表
create table 表名;

// 根据一个表创建另一个表(通过这种方式创建的表不会携带约束)
create table 表名 select * from 表;

1、模糊查询表

show tables like '%order%'

  

2、查看表更新时间

select ifnull(update_time , create_time) from information_schema.`tables` where table_schema=database() and table_name='表名';

说明

  在mysql的表信息表(information_schema)中查询该表的update_time的变化即可,但是,使用这个方法之前要要注意下mysql版本,5.5.44之前的版本只有myisam引擎可以正常查询到该值,若是innodb引擎,update_time值是不会有更新,总是null值

示例如下

  select ifnull(update_time , create_time) from information_schema.`tables` where table_schema=database() and table_name='r_targeting_field_ratio';

  

3、重命名表

alter table 表1 rename to 表2;

4、修改表编码

将编码设置为utf8mb4

alter table table_name convert to character set utf8mb4 collate utf8mb4_unicode_ci;

5、查看当前库中有多少张表

select count(*) tables, table_schema from information_schema.tables where table_schema=database() group by table_schema; 

 

6、更新语句

// update if一起使用

update r_day_stat_pixel_dim_event set source=case when (source='0') then '1' else '0' end where source!='total';

// key存在update,不存在insert

insert into tb (id,name,age) values (?,?,?) on duplicate key update id=?,name =? ,age=?;

insert into lee(exp_id, created_by, location, animal, starttime, endtime, entct,  .....) select id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct, ....  from tmp t where uid=x
on duplicate key update entct=t.entct, inact=t.inact, ...

// case in

select  (case when publisher_id in ('pub3200534074304') then 1 else 0 end) as status from r_day_billing_report

// 使用另一个表的值更新列

update tableb inner join tablea on tableb.name = tablea.name set tableb.value = if(tablea.value > 0, tablea.value, tableb.value) where tablea.name = 'joe'

五、注释

  1)、创建表时, 添加comment来添加注释

create table test( id int not null default 0 comment '用户id' ) comment='表的注释';

  2)、如果是已经建好的表, 也可以用修改字段的命令,然后加上comment属性定义

字段注释

alter table test1 modify column field_name int comment '修改后的字段注释';

表注释

alter table test1 comment '修改后的表的注释';

  3)、查看注释

查看表注释

show create table test1;

select * from information_schema.tables where table_schema='my_db' and table_name='test1' ;

查看字段注释

show full columns from test1;

select * from columns where table_schema='my_db' and table_name='test1';

六、列

1、新增列

a、命令

  alter table tableName add [column] column_name column_definition [first|after existing_column];

b、示例

  在r_day_stat_pixel_site_eventbak表的site_id之后新增mid列

  alter table r_day_stat_pixel_site_eventbak add `mid` varchar(40) not null comment 'mid' after site_id;

2、修改自增属性

a、命令

  alter table 表名 modify column 列 列类型;

b、示例

  alter table t_app_renew_product_monthtemp modify column id int(11) primary key auto_increment;

2、修改字段类型

a、命令

  alter table 表 change 字段1 字段1 类型;

b、示例如下

  alter table r_day_stat_request_filed change ratio ratio decimal(25,5);

3、修改字段字符集,(字符集说明见这里

a、命令

  alter table 表名 change 列名 列名 列类型 character set utf8mb4 collate utf8mb4_unicode_ci;

b、示例

  将字段编码改为utf8mb4

  alter table tmp_in_ipaddress change province province varchar(1024) character set utf8mb4 collate utf8mb4_unicode_ci; 

4、重命名字段

a、命令

alter table 表名 change column 旧列名 新列名 列类型 ;

b、示例

alter table r_day_stat_delivery change column aid ad_id varchar(64) ;

5、删除列

a、命令

 alter table  表名  drop 列名;

b、示例

alter table r_day_stat_pixel_site_eventbak drop source;

七、索引

  1、添加索引

alter table r_hour_stat_ssp add unique index idx_unique_r_hour_stat_ssp2 (`day`,`hour`,`test`,`publisher_id`,`app_id`,`slot_id`,`traffic_source`,`bucket_id`,`country_code`,`format`) USING BTREE;

alter table r_hour_stat_ssp drop index idx_unique_r_hour_stat_ssp;

alter table r_hour_stat_ssp rename index idx_unique_r_hour_stat_ssp2 to idx_unique_r_hour_stat_ssp;

七、约束

  1、主键

删除掉主键

alter table 表 change 字段名 字段 字段类型;

示例

alter table app.t_app_expire_product_month change id id int(11);

八、权限

  1、查看授权

show grants;

 

# 查看某用户授权
show grants for root@'*';

  2、授予权限

grant all privileges on *.* to ' ambari '@'192.168.129.1' identified by 'ambari';

grant all privileges on *.* to ' ambari '@'192.168.129.1' identified by 'ambari' with grant option;

grant all privileges on *.* to 'ambari '@'%' identified by 'ambari';

  3、回收权限

revoke all privileges on davinci.* from 'root'@'%';

revoke all privileges on *.* from 'root'@'%';

九、sql文件

1、批量执行sql文件

source sql文件;

示例

source /root/zt_login.sql;

十、导入/导出

  1、导出

    a、使用重定向导出

mysql -h *** -u *** -p*** -d*** -e "select country,field,field_value,ratio from r_day_stat_request_filed where day=20190618" > ./20190618.csv

导出后,字段分隔符是tab,还需使用其他工具如sublime将tab替换逗号,  即可

或者使用tr将\t替换为逗号,如下

mysql -h *** -u *** -p*** -d*** -e "select country,field,field_value,ratio from r_day_stat_request_filed where day=20190703" | tr "\t" "," > ./20190703.csv

    b、into outfile导出

mysql> select * from r_day_stat_request_filed where day=20190618 into outfile './20190618.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

    在线上环境执行报如下错误(由于线上环境,没敢做测试,先记一下有这个方法)

    

    c、mysqldump导出

mysqldump -h *** -u adx -p*** -t -t .  ad_bi  r_day_stat_request_filed  --where='day=20190619' --fields-terminated-by=',' --fields-enclosed-by='\"'

    也报错

    

    d、

  2、导入

    a、load

      load data infile './r_week_stat_request_filed_position.csv' [replace] into table r_week_stat_request_filed_position fields terminated by ',' enclosed by '"'; 

      注意事项:

      1)、文件可以使用绝对路径如'c:/d.txt',否则请将文件放在数据库根目录中 
      2)、因为字段之间用了逗号隔开,所以必须fields terminated by',',否则导入失败 
      3)、因为winsows中行以“\r\n”隔开,所以必须lines terminated by'\r\n',如果不设置这个参数,也可导入成功,但是会多导入一个“\r”控制字符,可能在可视化 mysql工具中看不出字段异样,但是在mysql命令行中显示会明显混乱。 
      4)、如果表tt非空,且文件中的ind值在表中有重复,会提示错误,并导入失败。

    b、如果load没权限,可以是试试先生成insert文件,然后source

源:
  方法一
  mysql -n -h *** -u *** -p*** -d*** -e "select concat('\'',ifnull(country,''),'\',\'',ifnull(field,''),'\',\'',ifnull(replace(field_value,'\'','\'\''),''),'\',',ifnull(ratio,''),',',ifnull(end_day,'')) from r_week_stat_request_filed_position where week_index=(select max(week_index) from r_week_stat_request_filed_position) " | awk -f',' '{printf "insert into r_targeting_field_ratio(country,field,field_value,ratio,updateday) values (%s,%s,%s,%s,%s);\n",$1,$2,$3,$4,$5}' > ./r_week_stat_request_filed_position.sql

  方法二
  mysql -n -h *** -u *** -p*** -d*** -e "select concat_ws('','\'',country,'\',\'',field,'\',\'',replace(field_value,'\'','\'\''),'\',',ratio,',',end_day) from r_week_stat_request_filed_position where week_index=(select max(week_index) from r_week_stat_request_filed_position) " | awk -f',' '{printf "insert into r_targeting_field_ratio(country,field,field_value,ratio,updateday) values (%s,%s,%s,%s,%s);\n",$1,$2,$3,$4,$5}' > ./r_week_stat_request_filed_position.sql
目标
  登陆mysql,source ./r_week_stat_request_filed_position.sql

说明:
  如果有null,需要特别处理一下,把""替换成null
  update r_targeting_field_ratio set field_value=null where updateday=20190714 and field_value='';

十一、查看操作日志

  1、查看warnings日志

show warnings;

示例

十二、参考文章
  1、https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table/11709090


注意:本文归作者所有,未经作者允许,不得转载

全部评论: 0

    我有话说: