MySQL基础语句


Mysql增删改查常用语句命令

一、增删改查语句总览

创建: create(创建数据库) 增:insert(插入表数据) 删:drop、delete(删除表、删除表数据) 改:update、alter(更改表数据、插入新字段) 查:select、show、describe/desc(查询表数据、查看所有表、查看表结构)

二、创建、删除数据库

  1. 创建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)
  1. 删除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)

三、对数据库表的操作

  1. 在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)
  1. 查看表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)
  1. 向表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)
  1. 查看表结构
第一中方式查看表结构有些混乱,推荐使用第二种方式 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. 想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)
  1. 查看表数据
mysql> select * from stu; +------+----------+-------+ | id | name | class | +------+----------+-------+ | 1 | zhangsan | 1314 | | 2 | lisi | 520 | +------+----------+-------+ 2 rows in set (0.00 sec)

五、删除表数据

  1. 直接全部删除
mysql> select * from stu; Empty set (0.00 sec)
  1. 根据条件删除指定行(删除是一整行数据)
mysql> delete from stu where id=1; Query OK, 1 row affected (0.00 sec)
  1. 连表一起删除
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)

七、查询数据

  1. 根据条件查询数据
mysql> select * from stu where id=2; +------+--------+-------+ | id | name | class | +------+--------+-------+ | 2 | wangwu | 1234 | +------+--------+-------+ 1 row in set (0.00 sec)
  1. 查询表中所有数据
mysql> select * from stu; +------+--------+-------+ | id | name | class | +------+--------+-------+ | 1 | lisi | 1234 | | 2 | wangwu | 1234 | +------+--------+-------+ 2 rows in set (0.00 sec)
符攀飞 符攀飞
大约 1 个月前
50

扫描下方二维码手机阅读文章

0 0