mysql增刪改查語句以及常用方法（mysql增刪改查語句在哪里操作）

創建數據庫

MariaDB [(none)]> create database mydatabase1;

查看數據庫

MariaDB [(none)]> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| mydatabase1 |

| mysql |

| performance_schema |

+——————–+

4 rows in set (0.001 sec)

刪除數據庫

MariaDB [(none)]> drop database mydatabase1;

Query OK, 0 rows affected (0.002 sec)

進入到數據庫mydatabase1

MariaDB [(none)]> use mydatabase1;

Database changed

MariaDB [mydatabase1]>

創建表格

MariaDB [mydatabase1]> create table students (id INT primary key, name VARCHAR(20),city CHAR(20));

Query OK, 0 rows affected (0.013 sec)

MariaDB [mydatabase1]>

顯示表

MariaDB [mydatabase1]> show tables;

+———————–+

| Tables_in_mydatabase1 |

+———————–+

| students |

+———————–+

1 row in set (0.001 sec)

刪除表

MariaDB [mydatabase1]> drop table students;

Query OK, 0 rows affected (0.004 sec)

MariaDB [mydatabase1]>

插入表記錄

MariaDB [mydatabase1]> insert into students(id,name,city) values (1,’xiaoli’,suzhou);

Query OK, 1 row affected (0.002 sec)

添加一個字段

MariaDB [mydatabase1]> alter table students add tel char(11);

Query OK, 0 rows affected (0.005 sec)

Records: 0 Duplicates: 0 Warnings: 0

查詢表格

MariaDB [mydatabase1]> select * from students;

+—-+——–+——–+——+

| id | name | city | tel |

+—-+——–+——–+——+

| 1 | xiaoli | suzhou | NULL |

+—-+——–+——–+——+

1 row in set (0.000 sec)

MariaDB [mydatabase1]>

更新數據

ariaDB [mydatabase1]> update students set city=’beijing’;

Query OK, 1 row affected (0.002 sec)

Rows matched: 1 Changed: 1 Warnings: 0

刪除數據

MariaDB [mydatabase1]> delete from students where id=1;

Query OK, 1 row affected (0.003 sec)

刪除了表數據之后，我們再給添加上。

MariaDB [mysql]> insert into students (id,name,city,tel) values (1,’xiaoli’,’suzhou’,’12345678901′);

Query OK, 1 row affected (0.002 sec)

查詢某個字段

MariaDB [mydatabase1]> select name from students where id=1;

+——–+

| name |

+——–+

| xiaoli |

+——–+

1 row in set (0.000 sec)

MariaDB [mydatabase1]>

模糊查詢

MariaDB [mydatabase1]> select * from students where name like ‘x%’; #查詢首字母是x的行

+—-+——–+——–+————-+

| id | name | city | tel |

+—-+——–+——–+————-+

| 1 | xiaoli | suzhou | 12345678901 |

+—-+——–+——–+————-+

1 row in set (0.001 sec)