(8条消息) MySQL5.7 常用命令

参考下文整理

Mysql常用命令行大全

1、连接mysql

格式: mysql -h主机地址 -u用户名 -p用户密码

1、连接到本机上的MYSQL。(假设用户名、密码均为root)
  首先打开DOS窗口,切换至目录mysql\bin,再键入命令mysql -u root -p,回车后输密码root再回车即可;或者直接键入命令mysql -uroot -proot回车即可;
注意:用户名前空格可有可无,但是密码前必须没有空格,否则再次输入密码。
  如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是: mysql>

2、连接到远程主机上的MYSQL。
  假设远程主机的IP为:110.110.110.110,用户名为root,密码为root。则键入以下命令:
mysql -h110.110.110.110 -uroot -proot;(注:p与root之间可以不用加空格)。

3、退出MYSQL命令:
  exit (回车)。

2、修改密码

格式:alter user 用户名@主机地址 identified by '新密码’

1、将root密码改为newroot。
alter user root@localhost identified by 'newroot’
主机地址为空时:alter user root identified by 'newroot’
2、查看用户主机地址方法

mysql -uroot -prootuse mysql;select user,host from user;

host列即为主机地址。

3、增加新用户

注意:和上面不同,下面的因为是MYSQL环境中的命令,所以后面都带一个分号作为命令结束符

格式:create user '用户名’@’主机地址’ identified by '密码’;

1、增加tom1用户 密码为 tom1,可在任何主机登录:

create user 'tom1'@'localhost' identified by 'tom1';

2、引号可省略,即:

create user tom1@localhost identified by 'tom1';

3、主机地址存在 % 时不可省略引号,即:

create user tom1@'192.168.1.%' identified by 'tom1';

4.1 创建数据库

注意:创建数据库之前要先连接Mysql服务器

格式:create database 数据库名

1、建立一个名为testdb的数据库

   mysql> create database testdb;

4.2 显示数据库

格式:show databases; (注意:最后有个s)

mysql> show databases;

注意:为了不再显示的时候乱码,要修改数据库默认编码。以下以utf-8编码页面为例进行说明:

1、修改MYSQL的配置文件:my.ini里面修改default-character-set=utf-8
2、代码运行时修改:
①Java代码:jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8;

4.3 删除数据库

格式:drop database 数据库名;

1、删除名为 testdb的数据库
mysql> drop database testdb;

2、删除一个已经确定存在的数据库

mysql> drop database testdb;Query OK, 0 rows affected (0.00 sec)

3、删除一个不确定存在的数据库

mysql> drop database testdb;ERROR 1008 (HY000): Can't drop database 'testdb'; database doesn't exist//发生错误,不能删除'testdb'数据库,该数据库不存在。
mysql> drop database if exists testdb;Query OK, 0 rows affected, 1 warning (0.00 sec)//产生一个警告说明此数据库不存在查看警告:mysql> show warnings;Empty set (0.00 sec)
mysql> create database testdb;Query OK, 1 row affected (0.00 sec)mysql> drop database if exists testdb;//if exists 判断数据库是否存在,不存在也不产生错误Query OK, 0 rows affected (0.00 sec)

4.4 连接数据库

格式: use 数据库名

1、如果testdb数据库存在,连接数据库:

mysql> use testdb;Database changed

use 语句可以通告MySQL把db_name数据库作为默认(当前)数据库使用,用于后续语句。该数据库保持为默认数据库,直到语段的结尾,或者直到发布一个不同的USE语句:

mysql> USE db1;mysql> SELECT COUNT(*) FROM mytable;   即:selects from db1.mytable;mysql> USE db2;mysql> SELECT COUNT(*) FROM mytable;   即:selects from db2.mytable;

使用USE语句为一个特定的当前的数据库做标记,不会阻碍您访问其它数据库中的表。下面的例子可以从db1数据库访问作者表,并从db2数据库访问编辑表:

mysql> USE db1;mysql> SELECT author_name,editor_name FROM author,db2.editor    ->        WHERE author.editor_id = db2.editor.editor_id;

2、连接其他数据库
直接使用命令:use 其他数据库名字就可以了。

4.5 当前选择的数据库

命令:select database();

MySQL中select命令类似于其他编程语言里的print或者write,你可以用它来显示一个字符串、数字、数学表达式的结果等等。部分select命令如下:

select version(); // 显示mysql版本select now(); // 显示当前时间select current_date; // 显示年月日select ((4 * 4) / 10 ) + 25; // 计算

5.1 创建数据表

格式:create table 表名 ( 字段名1 类型1 , ··· 字段名n 类型n);

1、建立一个名为class的表:

字段名 数字类型 数据宽度 是否为空 是否主键 自增 默认值
id int 4 primary key auto_increment
name char 20
sex int 4 0
degree double 16
mysql> create table class(    -> id int(4) not null primary key auto_increment comment '主键',    -> name varchar(20) not null comment '姓名',    -> sex int(4) not null default '0' comment '性别',    -> degree double(16,2) default null comment '分数');Query OK, 0 rows affected (0.07 sec)--查看表详情:mysql> show full fields from class;或mysql> show full columns from class;+--------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+| Field  | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |+--------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+| id     | int(4)       | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references | 主键    || name   | varchar(20)  | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | 姓名    || sex    | int(4)       | NULL            | NO   |     | 0       |                | select,insert,update,references | 性别    || degree | double(16,2) | NULL            | YES  |     | NULL    |                | select,insert,update,references | 分数    |+--------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+4 rows in set (0.01 sec)

5.3 删除数据表

格式:drop table 表名

1、删除表名为 mytable 的表

mysql> drop table mytable;Query OK, 0 rows affected (0.02 sec)

DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消,所以使用本语句要小心!

注意:对于一个带分区的表,DROP TABLE会永久性地取消表定义,取消各分区,并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义(.par)文件。

2、删除可能不存在的表
对于不存在的表,使用IF EXISTS用于防止错误发生。当使用IF EXISTS时,对于每个不存在的表,会生成一个NOTE。

mysql> drop table if exists mytable;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show warnings;+-------+------+---------------------------------+| Level | Code | Message                         |+-------+------+---------------------------------+| Note  | 1051 | Unknown table 'charles.mytable' |+-------+------+---------------------------------+1 row in set (0.00 sec)

5.4 表插入数据

格式:insert into 表名 ( 字段名1,···, 字段名n ) values ( 值1, ···, 值n );

1、向表class中插入一条记录

mysql> insert into class(name,sex,degree) values('charles','1','80.5');Query OK, 1 row affected (0.01 sec)mysql> select * from class;+----+---------+-----+--------+| id | name    | sex | degree |+----+---------+-----+--------+|  1 | charles |   1 |  80.50 |+----+---------+-----+--------+1 row in set (0.00 sec)

2、插入多条记录

mysql> insert into class(name,sex,degree) values('charles','1','80.5'),('tom','1','80.5');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from class;+----+---------+-----+--------+| id | name    | sex | degree |+----+---------+-----+--------+|  9 | charles |   1 |  80.50 || 10 | tom     |   1 |  80.50 |+----+---------+-----+--------+2 rows in set (0.00 sec)

5.5 查询表数据

1、查询所有行
格式: select 字段1, ···, 字段n from 表名 where 表达式
查看表 class 中所有数据:

mysql> select * from class;+----+---------+-----+--------+| id | name    | sex | degree |+----+---------+-----+--------+|  1 | charles |   1 |  80.50 ||  2 | charles |   1 |  80.50 ||  3 | charles |   1 |  80.50 ||  4 | charles |   1 |  80.50 ||  5 | charles |   1 |  80.50 ||  6 | charles |   1 |  80.50 |+----+---------+-----+--------+6 rows in set (0.00 sec)

2、查询前几行
例如:查看表 class 中前2行数据:

mysql> select * from class limit 2;+----+---------+-----+--------+| id | name    | sex | degree |+----+---------+-----+--------+|  1 | charles |   1 |  80.50 ||  2 | charles |   1 |  80.50 |+----+---------+-----+--------+2 rows in set (0.00 sec)select

一般配合where使用,以查询更精确更复杂的数据。

5.6 删除表中数据

格式:delete from 表名 where 表达式

1、删除表 class 中id为1 的记录

mysql> delete from class where id='9';Query OK, 1 row affected (0.01 sec)mysql> select * from class;+----+------+-----+--------+| id | name | sex | degree |+----+------+-----+--------+| 10 | tom  |   1 |  80.50 |+----+------+-----+--------+1 row in set (0.00 sec)

5.7 修改表中数据

格式:
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

  • UPDATE语法可以用新值更新原有表行中的各列;
  • SET子句指示要修改哪些列和要给予哪些值;
  • WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有的行;
  • 如果指定了ORDER BY子句,则按照被指定的顺序对行进行更新;
  • LIMIT子句用于给定一个限值,限制可以被更新的行的数目。

1、单表update一条记录

mysql> update class set degree = '90.9' where id = 10;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from class;+----+------+-----+--------+| id | name | sex | degree |+----+------+-----+--------+| 10 | tom  |   1 |  90.90 |+----+------+-----+--------+1 row in set (0.00 sec)

2、单表update多条记录

mysql> update class set degree = '0'    -> where id < 9    -> order by id desc    -> limit 3;Query OK, 3 rows affected (0.00 sec)Rows matched: 3  Changed: 3  Warnings: 0mysql> select * from class;+----+------+-----+--------+| id | name | sex | degree |+----+------+-----+--------+|  1 | tom1 |   1 |  90.00 ||  2 | tom2 |   1 |  90.00 ||  3 | tom3 |   1 |   0.00 ||  4 | tom4 |   1 |   0.00 ||  5 | tom5 |   1 |   0.00 || 10 | tom  |   1 |  90.90 |+----+------+-----+--------+6 rows in set (0.00 sec)

5.8 增加字段

更多详细内容查看MySQL5.7官方参考手册

MySQL 5.7 Reference Manual

1、增加单列
格式:ALTER TABLE tbl_name ADD col_name1 column_definition [FIRST | AFTER col_name];
[FIRST | AFTER col_name] 指定位置关系,FIRST表示在第一列,AFTER col_name表示在 col_name 列之后;

在表class中添加了一个字段exam_type,类型为INT(4),默认值为 NULL,在sex列后

mysql> ALTER TABLE class    -> ADD exam_type    -> INT(4) DEFAULT NULL    -> COMMENT '考试类别'    -> AFTER sex;Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc class;+-----------+--------------+------+-----+---------+----------------+| Field     | Type         | Null | Key | Default | Extra          |+-----------+--------------+------+-----+---------+----------------+| id        | int(4)       | NO   | PRI | NULL    | auto_increment || name      | varchar(20)  | NO   |     | NULL    |                || sex       | int(4)       | NO   |     | 0       |                || exam_type | int(4)       | YES  |     | NULL    |                || degree    | double(16,2) | YES  |     | NULL    |                |+-----------+--------------+------+-----+---------+----------------+5 rows in set (0.00 sec)

2、增加多列

mysql> ALTER TABLE class ADD(    -> col_1 VARCHAR(10) DEFAULT NULL,    -> col_2 VARCHAR(10) DEFAULT NULL    -> );Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc class;+-----------+--------------+------+-----+---------+----------------+| Field     | Type         | Null | Key | Default | Extra          |+-----------+--------------+------+-----+---------+----------------+| id        | int(4)       | NO   | PRI | NULL    | auto_increment || name      | varchar(20)  | NO   |     | NULL    |                || sex       | int(4)       | NO   |     | 0       |                || exam_type | int(4)       | YES  |     | NULL    |                || degree    | double(16,2) | YES  |     | NULL    |                || col_1     | varchar(10)  | YES  |     | NULL    |                || col_2     | varchar(10)  | YES  |     | NULL    |                |+-----------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

有多种写法,比如:

ALTER TABLE class ADD COLUMN(col_1 VARCHAR(10) DEFAULT NULL,col_2 VARCHAR(10) DEFAULT NULL );ALTER TABLE class ADD col_1 VARCHAR(10) DEFAULT NULL,ADD col_2 VARCHAR(10) DEFAULT NULL;

注意: 增加多列时不可指定位置关系。

2、删除列
格式:ALTER TABLE tbl_name DROP [COLUMN] col_name1 [, DROP col_name2 ...];
- [COLUMN] 关键字可有可无;
- 删除多列时需使用DROP关键字,不可直接用 , 分隔;

5.9 修改字段

格式:

ALTER TABLE tbl_nameCHANGE [COLUMN] old_col_name new_col_name column_definition[FIRST|AFTER col_name]

1、修改表class 列name 为 name_new

mysql> alter table class    -> change name name_new varchar(50) not null comment '姓名'; // 注意一定要指定类型Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show full fields from class;+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+----------+| Field     | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment  |+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+----------+| id        | int(4)       | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |          || name_new  | varchar(50)  | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references | 姓名     || sex       | varchar(10)  | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |          || degree    | double(16,2) | NULL            | YES  |     | NULL    |       | select,insert,update,references | 分数     || exam_type | int(4)       | NULL            | YES  |     | NULL    |       | select,insert,update,references | 考试类别 || remark    | varchar(200) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references | 备注     |+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+----------+6 rows in set (0.01 sec)

2、修改表class 列sex 类型为 varchar

mysql> alter table class    -> modify sex varchar(10);Query OK, 7 rows affected (0.11 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> show full fields from class;+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+| Field     | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+| id        | int(4)       | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         || name_new  | varchar(50)  | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references | 姓名    || sex       | varchar(10)  | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         || degree    | double(16,2) | NULL            | YES  |     | NULL    |       | select,insert,update,references | 分数    || exam_type | int(4)       | NULL            | YES  |     | NULL    |       | select,insert,update,references | 考试类别  |+-----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+5 rows in set (0.00 sec)

6.1 添加约束

1、添加主键约束
格式:ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…);

  • [CONSTRAINT [symbol]] constraint 关键字,symbol 表示约束别名,可有可无,mysql会自动创建;
  • [index_type] 索引类型 包含 {BTREE | HASH},存储引擎为InnoDB时只能使用BTREE,默认值为BTREE;
    给表 class 中 id 列 添加主键约束
mysql> ALTER TABLE class    -> add CONSTRAINT pk_id PRIMARY KEY    -> USING BTREE (id);Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0// 查看约束:mysql> select constraint_name,table_schema,table_name,column_name from information_schema.key_column_usage    -> where table_name = 'class';+-----------------+--------------+------------+-------------+| constraint_name | table_schema | table_name | column_name |+-----------------+--------------+------------+-------------+| PRIMARY         | charles      | class      | id          |+-----------------+--------------+------------+-------------+1 row in set (0.00 sec)注意: constraint_name 并不是 pk_id,说明命令中symbol并没有生效,我也不知道为什么,,或者:mysql> show keys from class;或者:mysql> show index from class;

若出现:ERROR 1068 (42000): Multiple primary key defined ,说明主键定义重复,id 列本来就存在主键,所以需要先删除主键。删除方法参考:
【数据库】MySQL中删除主键】
转自:https://blog.csdn.net/LanTingShuXu/article/details/70215063

2、添加唯一约束
格式:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);

1)给表class 列 name 添加 唯一约束

mysql> ALTER TABLE class    -> ADD UNIQUE KEY uk_name USING BTREE (name);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select constraint_name,table_schema,table_name,column_name from information_schema.key_column_usage    -> where table_name = 'class';+-----------------+--------------+------------+-------------+| constraint_name | table_schema | table_name | column_name |+-----------------+--------------+------------+-------------+| PRIMARY         | charles      | class      | id          || uk_name         | charles      | class      | name        |+-----------------+--------------+------------+-------------+2 rows in set (0.00 sec)这个约束的constraint_name 生效了,并且命令中使用 constraint uk_name 效果相同,同时使用时index_name 优先级高。

2)尝试插入重复 name tom

mysql> select * from class;+----+------+-----+-----------+--------+| id | name | sex | exam_type | degree |+----+------+-----+-----------+--------+|  1 | tom1 |   1 |      NULL |  90.00 ||  2 | tom2 |   1 |      NULL |  90.00 ||  3 | tom3 |   1 |      NULL |   0.00 ||  4 | tom4 |   1 |      NULL |   0.00 ||  5 | tom5 |   1 |      NULL |   0.00 ||  6 | tom6 |   0 |      NULL |   NULL || 10 | tom  |   1 |      NULL |  90.90 |+----+------+-----+-----------+--------+7 rows in set (0.00 sec)mysql> insert into class(id, name) values(7,'tom');ERROR 1062 (23000): Duplicate entry 'tom' for key 'uk_name' // 重复录入 for 约束 'uk_name',不能插入

3)删除唯一约束

mysql> alter table class drop key uk_name;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show keys from class;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| class |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)

6.2 添加索引

1、加索引
格式:

  普通索引ALTER TABLE tbl_nameADD {INDEX|KEY} [index_name](key_part,...) [index_option] ...  全文索引ALTER TABLE tbl_nameADD FULLTEXT [INDEX|KEY] [index_name](key_part,...) [index_option] ...  空间索引ALTER TABLE tbl_nameADD SPATIAL [INDEX|KEY] [index_name](key_part,...) [index_option] ...key_part:    col_name [(length)] [ASC | DESC]index_type:    USING {BTREE | HASH}index_option:    KEY_BLOCK_SIZE [=] value  | index_type  | WITH PARSER parser_name  | COMMENT 'string'

1)给表class 列name 添加普通索引

mysql> alter table class    -> add index name_index (name);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from class;+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| class |          0 | PRIMARY    |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               || class |          1 | name_index |            1 | name        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)

2)给表class 列remark 添加普通索引

添加列remark:mysql> alter table class    -> add remark varchar(200) default null comment '备注';添加全文索引:mysql> alter table class    -> add fulltext remark_full (remark);Query OK, 0 rows affected, 1 warning (0.34 sec)Records: 0  Duplicates: 0  Warnings: 1mysql> show index from class;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| class |          0 | PRIMARY     |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               || class |          1 | name_index  |            1 | name        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               || class |          1 | remark_full |            1 | remark      | NULL      |           7 |     NULL | NULL   | YES  | FULLTEXT   |         |               |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)

3)删除索引

mysql> alter table class    -> drop index name_index,    -> drop index remark_full;Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from class;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| class |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)
(0)

相关推荐