(8条消息) MySQL5.7 常用命令
参考下文整理
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 changeduse 语句可以通告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官方参考手册
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,...);[INDEX|KEY]说明是INDEX还是KEY，关于INDEX和KEY的区别参考：数据库操作之——key与index的区别
（转自：https://www.cnblogs.com/654321cc/p/7762943.html）[index_name]索引名称，好像 和[CONSTRAINT [symbol]]没有区别；[index_type]索引类型， 包含{BTREE | HASH}，- 这部分的叙述参考：mysql索引创建修改删除
(转自：https://blog.csdn.net/yue7603835/article/details/54925374)
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'- 格式参考对应版本官方手册；
- [] 表示非必需，{} 表示多选一；
key_part和index_type和index_option中各项的含义参考：MySQL进阶语法
(转自：https://blog.csdn.net/rongxiaojun1989/article/details/77731662)- 索引简单比较参考：【mysql】主键、普通索引、唯一索引和全文索引的比较
(转自：https://www.cnblogs.com/xuzhengzong/p/7680464.html)
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)