数据库基础语句：增删改查

创建删除数据库

创建数据库

mysql> create database mydatabase;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

删除数据库

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

关于数据表的操作

创建数据表

mysql> use mydatabase;
Database changed
mysql> create table stu(id int(10),name varchar(10),class varchar(10));
Query OK, 0 rows affected, 1 warning (0.02 sec)

查看数据表

mysql> use mydatabase;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| stu                  |
+----------------------+
1 row in set (0.00 sec)
或者
mysql> describe table stu;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | stu   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

注意：describe可以简写成desc
mysql> desc table stu;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | stu   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

向数据表中插入新字段

mysql> alter table stu add stunum varchar(10);
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| class  | varchar(10) | YES  |     | NULL    |       |
| stunum | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

查看表结构

第一中方式查看表结构有些混乱，推荐使用第二种方式
mysql> show create table stu;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

第二种方式查看表结构
mysql> describe stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| class | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

或者
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| class | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据

向表中插入数据

方法1：

mysql> use mydatabase;
Database changed
mysql> insert into stu values('1','zhangsan','1314');
Query OK, 1 row affected (0.06 sec)

方法2：

mysql> insert into stu(id,name,class)values('2','lisi','520');
Query OK, 1 row affected (0.00 sec)

推荐方法2

查看表数据

mysql> select * from stu;
+------+----------+-------+
| id   | name     | class |
+------+----------+-------+
|    1 | zhangsan | 1314  |
|    2 | lisi     | 520   |
+------+----------+-------+
2 rows in set (0.00 sec)

删除数据

全部删除

mysql> delete from stu;

根据条件删除指定行

mysql> delete from stu where id=1;
Query OK, 1 row affected (0.00 sec)

删除整张表

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

更改数据

示例1

mysql> select * from stu;
+------+----------+-------+
| id   | name     | class |
+------+----------+-------+
|    1 | lisi     | 1314  |
|    2 | zhangsan | 5321  |
+------+----------+-------+
2 rows in set (0.00 sec)
mysql> update stu set name='wangwu' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu;
+------+--------+-------+
| id   | name   | class |
+------+--------+-------+
|    1 | lisi   | 1314  |
|    2 | wangwu | 5321  |
+------+--------+-------+
2 rows in set (0.00 sec)

示例2

mysql> update stu set class=1234 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu;
+------+--------+-------+
| id   | name   | class |
+------+--------+-------+
|    1 | lisi   | 1314  |
|    2 | wangwu | 1234  |
+------+--------+-------+
2 rows in set (0.00 sec)

示例3

这种方式不加条件，则更改整个表数据对应的字段内容
mysql> update stu set class=1234;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from stu;
+------+--------+-------+
| id   | name   | class |
+------+--------+-------+
|    1 | lisi   | 1234  |
|    2 | wangwu | 1234  |
+------+--------+-------+
2 rows in set (0.00 sec)

查询数据

根据条件查询数据

mysql> select * from stu where id=2;
+------+--------+-------+
| id   | name   | class |
+------+--------+-------+
|    2 | wangwu | 1234  |
+------+--------+-------+
1 row in set (0.00 sec)

查询表中所有数据

mysql> select * from stu;
+------+--------+-------+
| id   | name   | class |
+------+--------+-------+
|    1 | lisi   | 1234  |
|    2 | wangwu | 1234  |
+------+--------+-------+
2 rows in set (0.00 sec)