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)