MySQL常用命令

服务器连接数据库
登录数据库：MYSQL -h host-name -u user -p password

-h:当连接mysql服务器不在同台主机时，填写主机名或IP，连接本机可以省略-h

-u：登录mysql的用户名

-p：登录mysql密码

连接服务器中本地数据库并进入stus库

[root@io ~]# mysql -uroot -proot123 stus;
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5344
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

常用命令
进入数据库：use stus;

mysql> use stus;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

显示所有数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| from_info          |
| students           |
| stus               |
| mysql              |
| sys                |
| test_info          |
+--------------------+
7 rows in set (0.01 sec)

mysql> 

显示默认数据库中所有表：show tables;

mysql> show tables;
+------------------------------+
| Tables_in_flyos              |
+------------------------------+
| asssets                      |
| name_log_his                 |
| assets_info                  |
| student_info                 |
| update                       |
| update_his                   |
+------------------------------+
76 rows in set (0.00 sec)

mysql>

退出mysql服务器：;q或quit;

mysql> quit
Bye
[root@flyos ~]# 

查看表结构 ：desc 表名;

mysql> desc stus;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| id_no       | varchar(20)   | YES  |     |         |                |     |
| name        | varchar(50)   | NO   |     | NULL    |                |
|create_time  | bigint(20)    | YES  |     | NULL    |                |
| update_time | bigint(20)    | YES  |     | NULL    |                |
| update_ping | varcha(4)     | YES  |     | 0       |                |
+-------------+---------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

添加字段：alter table 表名 add 增加字段名 增加字段类型（varchar(50)）not null;

mysql> alter table stus add sex varchar(50)not null;

修改字段类型：alter table 表名 modify column 字段 char(50)(字段类型）;

mysql> alter table stus modify column name char(50);

修改字段名：alter table 表名 change column 修改前 修改后 varchar(30) null default null;

mysql> alter table stus change column name name_id varchar(30) null default null;

删除字段：alter table 表名 drop 字段;

mysql> alter table stus drop name;

修改表名：alter table 原表名rename as新表名;

mysql> alter table stusc  as stuse;

删除表：drop table 表名;

mysql> drop table stus;

插入一条完整数据：insert into 表名 values (‘xx’,’xxx’,’as’,’ad’);

mysql> insert into stus values (‘12’,’zhangsan’,’as’,’ad’);

插入部分数据：insert into 表名(字段1,字段2,字段3) values(‘xx’,’xxx’,’as’,’ad’);

mysql> insert into stus(id,name) values(‘2’,’zhangliu’);

插入多条数据：insert into 表名(字段1,字段2,字段3) values(‘xx’,’xxx’,’as’,’ad’),(‘xx’,’xxx’,’as’,’ad’);

mysql> insert into stus(id,name) values(‘2’,’zhangliu’),(‘3’,’zhasi’);

用insert…set插入数据：insert into 表名 set 字段1=’xx’,字段2=’xxx’;

mysql> insert into stus set id=’15’,name=’lisi’;

查看数据：select * from 表名;

mysql> select * from stus;
+------+----------+
| id   | name     | 
+------+----------+
| 1623 | lisi     |
+------+----------+
4 rows in set (0.01 sec)

修改字段数据：update 表名set 字段修改后=’xx’ where 字段修改前=’xxx’;

mysql> update stus set  name=’yangming’ where name=’lisi’;
+------+----------+
| id   | name     | 
+------+----------+
| 1623 | yangming |
+------+----------+
4 rows in set (0.01 sec)

修改全部数据(字段内容都一样)：update 表名 set 字段=’xx’;

mysql> update stus set name=’yangming’;
+------+----------+
| id   | name     | 
+------+----------+
| 1623 | yangming |
+------+----------+
4 rows in set (0.01 sec)

删除表中部分数据：delete from 表名where 字段名=’xx’;

mysql> delete from stus where name=’yangming’;
+------+----------+
| id   | name     | 
+------+----------+
4 rows in set (0.01 sec)

删除全部数据：delete from 表名;

mysql> delete from stus;

读完本文有收获吗？请转发分享更多人

软件测试分享圈