数据库的连接与断开

# 连接数据库
$ mysql -uroot -p123456

# 断开数据库
$ mysql> quit;
# 或者
$ mysql> exit;

数据库基本操作

# 创建数据库
$ mysql> create database test;

# 删除数据库（不要轻易使用）
$ mysql> drop database test;

# 显示数据库
$ mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

# 选择数据库
$ mysql> use test;

表操作

• 创建表：

create table user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30) COMMENT '用户名称',
    password VARCHAR(30) COMMENT '用户密码'
);

Query OK, 0 rows affected (0.02 sec)

• 删除表：

drop table user;

Query OK, 0 rows affected (0.01 sec)

• 查看表：

show tables;

+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

• 描述表：

describe user;

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| password | varchar(30) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

• 增加表数据：

$ mysql> INSERT INTO user VALUES (null, "张三", "123456");
$ mysql> INSERT INTO user VALUES (null, "李四", "234567");

• 查询表数据：

$ mysql> SELECT * FROM user;

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 张三     | 123456   |
|  2 | 李四     | 234567   |
+----+----------+----------+
2 rows in set (0.00 sec)

• 更新表数据：

$ mysql> UPDATE user SET password="999999" WHERE username="李四";

# 更新后可以查询李四的密码
$ mysql> SELECT password FROM user WHERE username="李四";

+----------+
| password |
+----------+
| 999999   |
+----------+
1 row in set (0.00 sec)

• 删除表数据：

$ mysql> DELETE FROM user WHERE username="张三";

# 删除后查询表数据
$ mysql> SELECT * FROM user;

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | 李四     | 999999   |
+----+----------+----------+
1 row in set (0.00 sec)