MySQL使用记录

1、数据库常用操作

• 查看表结构

    desc tabl_name;
  

• 结尾加上\G，使结果易于阅读

    select * from tblRule where rulename='cjtest'\G;
  

• 给表添加列，并赋予初值

    ALTER TABLE cmxt_log.tl_user_lastmailinfo ADD COLUMN phishing_attack int default 0;
  

• 删除表的某个列

    ALTER TABLE cmxt_log.tl_user_lastmailinfo DROP COLUMN phishing_attack;
  

• 数据库的备份

    --------------------------备份数据库
    /home/corexxx/mysql/bin/mysqldump -ucorxxxx -p15281xxxxx -h127.0.0.1 -P3308 --default-character-set=gbk -B cmxt > cmxt.sql
    --------------------------备份数据库表cm_user_info
    /home/corexxx/mysql/bin/mysqldump -ucorxxx -p13310xxxx -h127.0.0.1 -P3308 --default-character-set=gbk cmxt cm_user_info > cmxt_cm_user_info.sql
  

• 查看创建表td_organization的创表语句

    show create table td_organization;
  

• 授予core用户从192.168.20X.XXX远程机上访问数据库的权限

    GRANT ALL PRIVILEGES ON *.* TO 'core'@'192.168.20X.XXX' IDENTIFIED BY '32478XXXX' WITH GRANT OPTION;
  

• 显示有访问权限的所有数据库用户

    SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
  

• 在host126确保已授予host124访问数据库的相应权限（core用户）

    show grants for core@XXX.XXX.XXX.124;
  

• 更改某个字段（icm_role）的默认值

    alter table cm_user_XX alter column icm_role set default 1;
  

• 更改某个字段的字符集

    alter table corp_stat modify column corp_name varchar(64) character set utf8mb4;
  

• 启动MySQL

    /usr/bin目录下  service mysql start
  

• 查看数据库的配置和目录

    show variables like "%char;
    show variables;
  

• 查看数据库启动日志

    /var/lib/mysql/Centos6x64.err
    /var/lib/mysql/Centos6x64.pid
  

• 本地连接MySQL

    mysql -u user -p123456 -h 127.0.0.1 -P 3306 test_db
  

• 创建数据库并指定字符集

    create database icm_test default character set utf8mb4 collate utf8mb4_general_ci;
  

• 建表

---

 create table domain_list
 (
 id         bigint       auto_increment
 primary key,
 createTime datetime     null,
 domain     varchar(100) null,
 origin     varchar(30) null,
 constraint domain_index
 unique (domain)
 )
 engine = MyISAM;

    # 表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)