mysql数据库增删改查基础语句_Mysql数据库基础增删改查常
⽤语句命令
增删改查常⽤语句命令
⼀、增删改查语句总览
创建： create(创建库)
增：(插⼊表数据)
删：drop、delete(删除表、删除表数据)
改：、alter(更改表数据、插⼊新字段)
查：、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;
+-------+-------------+------+-----+---------+-------+
mysql中delete语句| 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)
⽰例⼆

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。