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;

    # 表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)
posted @ 2019-12-29 16:19  军子~  阅读(205)  评论(0编辑  收藏  举报