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;
# 表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)