一、表操作

1、创建数据库

命令:

create database xxx;

2、创建表

例子:

CREATE TABLE t_ci_sessions (
  id varchar(128) NOT NULL,
  ip_address varchar(45) NOT NULL,
  timestamp int(10) unsigned NOT NULL DEFAULT '0',
  data blob NOT NULL,
  KEY ci_sessions_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、查看表结构

命令1:

desc xxx;

例子1:

mysql> desc t_ci_sessions;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | varchar(128)     | NO   |     | NULL    |       |
| ip_address | varchar(45)      | NO   |     | NULL    |       |
| timestamp  | int(10) unsigned | NO   | MUL | 0       |       |
| data       | blob             | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

命令2:

show create table xxx;

例子2:

mysql> show create table t_ci_sessions;
+---------------+----------------------------+
| Table         | Create Table                                                                     
+---------------+----------------------------+
| t_ci_sessions | CREATE TABLE `t_ci_sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` int(10) unsigned NOT NULL DEFAULT '0',
  `data` blob NOT NULL,
  KEY `ci_sessions_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+----------------------------+

4、修改表名

命令:

alter table t_book rename to bbb;

5、添加字段

命令:

alter table 表名 add column 列名 属性;

例子:

alter table t_ci_sessions add column field varchar(30) not null default 'test' comment '';

6、删除字段

命令:

alter table 表名 drop column 列名;

7、修改字段属性

例子:

alter table t_book modify name varchar(22) default '' not null comment '名称';

8、添加索引

命令:

# 添加普通索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
# 添加唯一索引
ALTER TABLE table_name ADD UNIQUE (column_list)
# 添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

9、删除索引

命令:

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

10、查看索引

命令:

show index from tblname;

例子:

mysql> show index from t_ci_sessions\G;
*************************** 1. row ***************************
        Table: t_ci_sessions
   Non_unique: 1
     Key_name: ci_sessions_timestamp
 Seq_in_index: 1
  Column_name: timestamp
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

二、常用SQL语句

1、date_format日期转换

例子:

mysql> select date_format(insertTime,'%Y%m%d') as mydate from t_xxx;
+----------+
| mydate   |
+----------+
| 20190515 |
| 20190521 |
+----------+

2、联表查询

例子:

mysql> desc t_table1;
+---------------+------------------+------+-----+-------------------+-----------------------------+
| Field         | Type             | Null | Key | Default           | Extra                       |
+---------------+------------------+------+-----+-------------------+-----------------------------+
| taskId        | char(32)         | NO   | PRI | NULL              |                             |
| taskName      | varchar(200)     | NO   | MUL |                   |                             |
| pluginId      | text             | YES  |     | NULL              |                             |
| insertTime    | varchar(19)      | NO   | MUL |                   |                             |
+---------------+------------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)

mysql> desc t_table2;
+----------------+------------------+------+-----+-------------------+----------------------------+
| Field          | Type             | Null | Key | Default           | Extra                      |
+----------------+------------------+------+-----+-------------------+----------------------------+
| taskInstanceId | int(10) unsigned | NO   | PRI | NULL              | auto_increment             |
| taskId         | char(32)         | NO   | MUL | NULL              |                           |
| currentCnt     | int(10) unsigned | NO   |     | 0                 |                            |
| totalCnt       | int(10) unsigned | NO   |     | 0                 |                            |
| insertTime     | varchar(19)      | NO   | MUL |                   |                            |
+----------------+------------------+------+-----+-------------------+----------------------------+

mysql> select a.taskId,b.taskName,a.taskInstanceId from t_table2 as a join t_table1 as b on a.taskId=b.taskId limit 1\G;
+----------------------------------+----------+----------------+
| taskId                           | taskName | taskInstanceId |
+----------------------------------+----------+----------------+
| 03aecb2c216bc0f645c3195abcb629d8 | test1    |         100262 |
+----------------------------------+----------+----------------+

三、用户管理与授权管理

1、创建用户

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明:

  • username:用户名
  • host:指定该用户在哪个主机上可以登录,如果是本机,可以用localhost,如果想让该用户可以从任意远程主机登录,可以使用通配符%
  • password:用户的登录密码

例子:

CREATE USER 'wgc1'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'wgc2'@'192.168.1.111' IDENTIFIED BY '123456';
CREATE USER 'wgc3'@'%' IDENTIFIED BY '123456';

2、查看用户信息

命令:

# 切换到 mysql 系统db
use mysql;
SELECT user, host FROM user;

例子:

mysql> select user,host from user;
+------------+--------------------+
| user       | host               |
+------------+--------------------+
| scan       | %                  |
| scan_inner | %                  |
| root       | 127.0.0.1          |
| root       | ::1                |
|            | localhost          |
| root       | localhost          |
+------------+--------------------+

3、授权

命令:

GRANT all privileges ON databasename.tablename TO 'username'@'host';
# 上面的授权命令无法给用户拥有授权的权限,如果想要让用户有授权的权限,可以使用如下命令:
GRANT all privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
# 刷新权限。使用这个命令使得权限生效
flush privileges;

说明:

  • privileges:用户的操作权限,如SELECT、INSERT、UPDATE等,如果要授予所有权限则使用ALL。详细的权限信息可以查看mysql官网文档:Privileges Provided by MySQL
  • databasename:数据库名,如果要授予所有数据库的权限,可以使用*表示
  • tablename:表名,如果要授予所有表的权限,可以使用*表示

例子:

GRANT SELECT,INSERT on test_db.t_user TO 'wgc'@'%';
GRANT ALL on *.* TO 'wgc'@'%';

4、查看用户授权信息

命令:

SHOW GRANTS FOR 'username'@'host'

例子:

mysql> show grants for 'scan_inner'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for scan_inner@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'scan_inner'@'%' IDENTIFIED BY PASSWORD '*31AB55F03251A0052B5C4472436C' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'scan_inner'@'%' WITH GRANT OPTION                                |
+-----------------------------------------------------------------------------------------------------------+

5、设置与更改用户密码

命令:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
# 如果是当前登录用户:
SET PASSWORD = PASSWORD('newpassword');

说明:

  • username:用户名
  • host:登录的主机
  • newpassword:新密码

例子:

SET PASSWORD FOR 'wgc'@'%' = PASSWORD('123456');

6、撤销用户权限

命令:

REVOKE privelege ON databasename.tablename FROM 'username'@'host';

说明:同上2授权部分

可以通过查询用户授权信息后再撤销

7、删除用户

命令:

DROP USER 'username'@'host'

说明:同上

四、空间统计命令

1、查看各个数据库的数据大小

命令:

use information_schema;
select table_schema,concat(round(sum(data_length)/1024/1024,2),'MB') as data from tables group by table_schema;

例子:

mysql> select table_schema,concat(round(sum(data_length)/1024/1024,2),'MB') as data from tables group by table_schema;
+--------------------+----------+
| table_schema       | data     |
+--------------------+----------+
| information_schema | 0.00MB   |
| mysql              | 0.82MB   |
| performance_schema | 0.00MB   |
| vul_scan           | 114.59MB |
+--------------------+----------+

2、查看指定数据库下每个表的数据大小

命令:

use information_schema;
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='vul_scan';

例子:

use information_schema; 
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='vul_scan';
+-----------------------------------+--------------------------+------------+----------+
| TABLE_NAME                        | DATA_LENGTH+INDEX_LENGTH | TABLE_ROWS | data     |
+-----------------------------------+--------------------------+------------+----------+
| t_ci_sessions                     |                    32768 |          7 | 0.03MB   |
| t_xxx1_0                          |                   163840 |          3 | 0.16MB   |
| t_xxx2_0                          |                151273472 |    1079020 | 144.27MB |
+-----------------------------------+--------------------------+------------+----------+

3、查看指定数据库的某个表的数据大小

命令:

use information_schema;
select TABLE_NAME,concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='vul_scan' and table_name='t_xxx';

例子:

mysql> select TABLE_NAME,concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='vul_scan' and table_name='t_xxx';
+--------------------------+----------+
| TABLE_NAME               | data     |
+--------------------------+----------+
| t_xxx                    | 113.66MB |
+--------------------------+----------+

五、重复数据处理

1、根据单个字段查找表中重复记录

命令:

select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

2、根据单个字段查找表中重复记录,并删除保留1个

命令:

delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)    

3、根据多个字段查找表中重复记录

命令:

select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、根据多个字段查找表中重复记录,并删除保留1个

命令:

delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

六、备份与恢复

1、备份

(1)备份命令格式

mysqldump -h主机名  -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql

(2)数据库压缩备份

mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz

(3)备份数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

(4)同时备份多个数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

(5)备份所有数据库

mysqldump –all-databases > allbackupfile.sql

2、恢复

(1)还原数据库的命令

命令:

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

(3)还原压缩的数据库

命令:

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

(3)数据库复制

例子:

# 假设已存在数据库名为old_db,想要复制一份到新的数据库,命名为new_db,命令如下:
mysqldump old_db -u root -ppassword --add-drop-table | mysql new_db -u root -ppassword

(4)source导入数据库

例子:

# 用use进入到某个数据库
mysql>source d:\test.sql,后面的参数为脚本文件

(5)恢复binlog文件

例子:

   mysqlbinlog bin-log.000002 |mysql -hx.x.x.x -utest2 -p123  -P3310  
打赏

发表评论

电子邮件地址不会被公开。