MySQL常用操作

上个教程跟大家介绍了如何安装MySQL数据库，现在跟大家分享一些常用操作。

1.显示当前用户可以查看的数据库列表

mysql> show databases;

2.创建数据库

mysql> create database 库名;

3.显示库中的数据表

先进入某个数据库，再查表

mysql> use mysql;
mysql> show tables;

4. 创建表

mysql> create table 表名 (字段设定列表)；

4.1 普通表

CREATE TABLE mytable
(
  id INT(11),
  name VARCHAR(25) not null,
  deptId INT(11),
  salary FLOAT
);

4.2 带主键并且自增

CREATE TABLE mytable2
(
 		id int not null AUTO_INCREMENT primary key,
    name char(20)
);

4.3 复合主键

CREATE TABLE mytable(
    id int not null,
    name char(20),
    primary key (id,name)
);

4.4 默认值

CREATE TABLE mytable(
    id int not null primary key,
    name char(20) default '1'
);

4.5 创建表指定索引

CREATE TABLE mytable(
    id int not null primary key,
    name char(20) default '1',
    index index_name (name)
);

4.6 创建表指定唯一索引

CREATE TABLE mytable(
    id int not null primary key,
    name char(20) default '1',
    unique index index_name (name)
);

5. 查看索引

mysql>  SHOW INDEXES FROM mytable;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mytable |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| mytable |          0 | index_name |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

6. 显示数据表的结构

mysql> desc mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(20) | YES  | UNI | 1       |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> show create table mytable;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
  `id` int NOT NULL,
  `name` char(20) COLLATE utf8mb4_general_ci DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

7. 修改表

7.1 增加字段

mysql> ALTER TABLE mytable ADD num INT;

7.2 增加字段-指定位置

mysql> ALTER TABLE mytable ADD sex INT after name;

7.3 修改字段默认值

mysql> ALTER TABLE mytable ALTER sex SET DEFAULT 1;

7.4 修改字段类型及名称

mysql> ALTER TABLE mytable MODIFY sex2 varchar(10);

或者

mysql> ALTER TABLE mytable CHANGE sex sex2 INT;

7.5 删除表的某个字段

mysql> ALTER TABLE mytable  DROP sex2;

8. 插入表

mysql> insert into mytable(id, name, num)
       values (1, 'hello', 1);

9. 更新表

更新某个字段值

mysql> update mytable set name='testerzhang' where id=1;

替换数据库某个字段的值

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string')

例子：

mysql> UPDATE mytable SET name=REPLACE(name, 'testerzhang', 'hello') where id=1;

10.查询表

普通查询

mysql> select * from mytable where id=1;
+----+-------+------+
| id | name  | num  |
+----+-------+------+
|  1 | hello |    1 |
+----+-------+------+
1 row in set (0.00 sec)

人性化显示

mysql> select * from mytable where id=1\G;
*************************** 1. row ***************************
  id: 1
name: hello
 num: 1
1 row in set (0.00 sec)

like模糊查询

mysql> select * from mytable where name like '%el%';
*************************** 1. row ***************************
  id: 1
name: hello
 num: 1
1 row in set (0.00 sec)

排序

降序
mysql> select * from mytable order by num desc;

升序
mysql> select * from mytable order by num asc;

过滤重复数据

mysql> SELECT DISTINCT num from mytable;

group by

语法:

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例子:

mysql> select num,count(num) from mytable group by num;
+------+------------+
| num  | count(num) |
+------+------------+
|    1 |          1 |
|    2 |          2 |
+------+------------+
2 rows in set (0.01 sec)

11. 删除表数据

mysql> delete from mytable where id=2;

12.truncate清空表数据

mysql> truncate table mytable;

13. 删除表

mysql> drop table mytable

14. 数据select导出

需要有权限才可以导出，因此您必须拥有FILE权限，才能使用此语法。

mysql> SELECT * FROM mytable  INTO OUTFILE '/tmp/mytable.txt';
ERROR 1227 (42000): Access denied; you need (at least one of) the FILE privilege(s) for this operation

用数据库用户root登录，给当前导出的用户添加 FILE 权限。

GRANT FILE ON *.* TO 'test'@'%';

再尝试导出

mysql> SELECT * FROM mytable  INTO OUTFILE '/tmp/mytable.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

查看 secure_file_priv 的值，默认为NULL，表示限制不能导入导出。

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)

解决方法: 打开my.cnf 或 my.ini，加入以下语句后重启mysql。

[mysqld]
secure_file_priv=''

查看secure_file_priv修改后的值

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |   |
+------------------+-------+
1 row in set (0.01 sec)

修改后再次执行，成功导出。

mysql> SELECT * FROM mytable  INTO OUTFILE '/tmp/mytable.txt';
Query OK, 3 rows affected (0.00 sec)

说明：原则上相关权限，只给特定的备份用户开放。

15.mysqldump导出

15.1 MySQL 5.7.31以后的版本报错

执行mysqldump报错

$ mysqldump -h127.0.0.1 -P3306 -utest -ptesterzhang test > test_db_dump.txt
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

方法一:增加--no-tablespaces参数

$ mysqldump --no-tablespaces -h127.0.0.1 -P3306 -utest -ptesterzhang test > test_db_dump.txt

方法二：用数据库用户root登录，给当前导出的用户添加 PROCESS 权限。

GRANT PROCESS ON *.* TO 'test'@'%';

这里需要留意两点：

A.之前我创建test用户的权限是%,任意主机。如果你改成localhost，会报错。

mysql> GRANT PROCESS ON test.* TO 'test'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

B.这里的PROCESS必须是*.*，否则会报如下的错误：

mysql> GRANT PROCESS ON test.* TO 'test'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

说明：原则上相关权限，只给特定的备份用户开放。

15.2 mysqldump的一些命令：

导出某个数据库表结构（不导出数据）

mysqldump -h主机名 -P端口号 -u用户名 -p密码 -d 数据库名 表名 > ./xxxx.sql

导出某个数据库表结构（导出数据）

mysqldump -h主机名 -P端口号 -u用户名 -p密码 数据库名 表名 > ./xxxx.sql

导出某个数据库结构（导出数据）

mysqldump -h主机名 -P端口号 -u用户名 -p密码 数据库名 > ./xxxx.sql

导出某个数据库结构（导出数据）和存储过程

mysqldump -h主机名 -P端口号 -u用户名 -p密码 -R 数据库名 > ./xxxx.sql

本文没有授权给任何组织、企业和个人转载，未经作者允许禁止转载！

欢迎关注我的公众号testerzhang，原创技术文章第一时间推送。

公众号二维码

MySQL常用操作

相关文章：