mysql数据库增删改查基础语句_Mysql数据库基础增删改查常用语句命令

Mysql增删改查常用语句命令

一、增删改查语句总览

创建： create(创建数据库)

增：insert(插入表数据)

删：drop、delete(删除表、删除表数据)

改：update、alter(更改表数据、插入新字段)

查：select、show、describe/desc(查询表数据、查看所有表、查看表结构)

二、创建、删除数据库

创建mydatabase数据库

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)

删除mydatabase数据库

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)

三、对数据库表的操作

在mydatabase数据库中创建表stu

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)

查看表stu

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)

向表stu插入新字段

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)

四、插入数据库表数据

想stu表中插入表数据

方式一

mysql> use mydatabase;

Database changed

mysql> insert into stu values('1','zhangsan','1314');

Query OK, 1 row affected (0.06 sec)

方式二

mysql> insert into stu(id,name,class)values('2','lisi','520');

Query OK, 1 row affected (0.00 sec)

查看表数据

mysql> select * from stu;

+------+----------+-------+

| id | name | class |

+------+----------+-------+

| 1 | zhangsan | 1314 |

| 2 | lisi | 520 |

+------+----------+-------+

2 rows in set (0.00 sec)

五、删除表数据

直接全部删除

mysql> select * from stu;

Empty set (0.00 sec)

根据条件删除指定行(删除是一整行数据)

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)

六、更改数据

示例一

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)

示例二

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)

示例三

这种方式不加条件，则更改整个表数据对应的字段内容

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)

• « 上一篇：leetcode-环形链表2-18
• » 下一篇：mysql数据库更新数据库语句_详解MySQL数据库之更新语句