数据库---mysql 之 常用命令行命令

1、展示当前所有的数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jzq_test           |
| mtx_1              |
| mysql              |
| performance_schema |
| study              |
| sys                |
+--------------------+
7 rows in set

2、选中某个数据库（相当于鼠标点击选中）：use sql_name;
【使用 数据库名.表名 可以省略 use 数据库名的操作】

mysql> use jzq_test;
Database changed

3、展示选中数据库下所有的表：show tables;

mysql> show tables;
+--------------------+
| Tables_in_jzq_test |
+--------------------+
| order_table        |
| user_info          |
| user_info_table    |
+--------------------+
3 rows in set

4、展示表结构：desc table_name;

mysql> desc user_info;
+-----------+-------------+------+-----+----------+----------------+
| Field     | Type        | Null | Key | Default  | Extra          |
+-----------+-------------+------+-----+----------+----------------+
| user_id   | int(11)     | NO   | PRI | NULL     | auto_increment |
| user_name | char(10)    | YES  |     | NULL     |                |
| password  | varchar(10) | YES  |     | 12345555 |                |
| user_nick | varchar(10) | YES  |     | NULL     |                |
| card_num  | bigint(20)  | YES  |     | NULL     |                |
+-----------+-------------+------+-----+----------+----------------+
5 rows in set

5、查看表数据信息：select * from table_name;

mysql> select * from user_info;
+---------+-----------+----------+-----------+---------------+
| user_id | user_name | password | user_nick | card_num      |
+---------+-----------+----------+-----------+---------------+
|       1 | zhangsan  | 12345555 | zhangsan  |  123456789111 |
|       2 | lisi      | 12345555 | ls        | 1234567891222 |
|       3 | wangwu    | 12345555 | ww        | 1234567891333 |
|       4 | zhaoliu   | 12345555 | zl        | 1234567891444 |
+---------+-----------+----------+-----------+---------------+
4 rows in set

6、创建数据库：create database sql_name;

mysql> create database test_sql;
Query OK, 1 row affected

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jzq_test           |
| mtx_1              |
| mysql              |
| performance_schema |
| study              |
| sys                |
| test_sql           |
+--------------------+
8 rows in set

7、创建表:

mysql> create table test_sql_table(
    -> t_id int zerofill unsigned not null primary key auto_increment,
    -> t_name varchar(20),
    -> t_num int(10));

//创建成功，展示如下：
mysql> desc test_sql_table;
+--------+---------------------------+------+-----+---------+----------------+
| Field  | Type                      | Null | Key | Default | Extra          |
+--------+---------------------------+------+-----+---------+----------------+
| t_id   | int(10) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| t_name | varchar(20)               | YES  |     | NULL    |                |
| t_num  | int(10)                   | YES  |     | NULL    |                |
+--------+---------------------------+------+-----+---------+----------------+
3 rows in set

8、删除表：drop table table_name;

//删表之前：
mysql> show tables;
+--------------------+
| Tables_in_jzq_test |
+--------------------+
| order_table        |
| test_sql_table     |
| user_info          |
| user_info_table    |
+--------------------+
4 rows in set

//执行删表操作：
mysql> drop table order_table;

//删表后结果显示；
mysql> show tables;
+--------------------+
| Tables_in_jzq_test |
+--------------------+
| test_sql_table     |
| user_info          |
| user_info_table    |
+--------------------+
3 rows in set

9、删数据库：drop database sql_name;

//删库之前：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jzq_test           |
| mtx_1              |
| mysql              |
| performance_schema |
| study              |
| sys                |
| test_sql           |
+--------------------+
8 rows in set

//执行删库命令：
mysql> drop database test_sql;

//删库后展示结果：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jzq_test           |
| mtx_1              |
| mysql              |
| performance_schema |
| study              |
| sys                |
+--------------------+
7 rows in set

10、修改表名：alter table old_table rename to new_table;

# 当前表名
mysql> show tables;
+---------------+
| Tables_in_jzq |
+---------------+
| old_table     |
+---------------+
1 row in set

# 执行修改表名的命令
mysql> alter table old_table rename  to new_table;
Query OK, 0 rows affected

# 展示修改后的表名
mysql> show tables;
+---------------+
| Tables_in_jzq |
+---------------+
| new_table     |
+---------------+
1 row in set