MySQL基本语法使用(增删改查)

增：insert

删：delete

改：update

查：select

 

1.创建一个数据库

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

2.显示当前数据库内容

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

3.切换当前数据库对象

mysql> use test
Database changed

4.显示数据库中的表

mysql> show tables;
Empty set (0.00 sec)

5.插入一个表

mysql> create table pet( name varchar(20), owner varchar(20), species varchar(20), sex char(1), birth date, death date);
Query OK, 0 rows affected (0.02 sec)

显示test中的表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.01 sec)

6.显示表属性

mysql> desc pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

7.查看pet表中信息(此时为空)

mysql> select *from pet;
Empty set (0.01 sec)

8.插入一段信息

mysql> insert into pet values('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.00 sec)

再查看表中信息：

mysql> select *from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

再插入一段信息：

mysql> insert into pet values('旺旺','周星驰','狗','公','1990-01-01',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select *from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| Puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺旺     | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
+----------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

9.修改数据

mysql> update pet set name="旺旺财" where owner="周星驰";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| Puffball  | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺旺财    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
+-----------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

10.删除数据

mysql> delete from pet where name='旺旺财';
Query OK, 1 row affected (0.01 sec)

mysql> select *from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)