mysql中什么是dml语句_Mysql基础入门-SQL_DML语句

DML语句：

DML操作是指对数据库中的表进行操作，主要包括记录的插入(insert)，更新(update)，删除(delete)，查询(select)。

记录插入

创建表完成后就需要给器插入记录和数据了，插入记录基本语法如下：

INSERT tablename (fielde1,fielden2....)VALUES(value1,value2....);

下面我给我制作的表名为class_1填入以下下信息

name分别为liao，liaoxz，marry  age为18,18,28 sex 为man,man,woman

mysql> insert class_1 (name,age,sex)values('liao','18','man');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert class_1 (name,age,sex)values('liaoxz','18','man');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert class_1 (name,age,sex)values('marry','28','woman');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select name,age,sex from class_1 ;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| liao | 18 | m |

+--------+------+------+

3 rows in set (0.00 sec)

注：可以不用指定字段名但是values后面的顺序必须要和字段排序一样，加入新的数据记录时如果只指定其中几个字段，其他默认为null值

例如：

mysql> insert class_1 (name,age)values('tom','19');

Query OK, 1 row affected (0.00 sec)

mysql> select name,age,sex from class_1 ;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| liao | 18 | m |

| tom | 19 | NULL |

+--------+------+------+

4 rows in set (0.00 sec)

mysql> insert class_1 values('jarry','18','woman');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select name,age,sex from class_1 ;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| liao | 18 | m |

| tom | 19 | NULL |

| jarry | 18 | w |

+--------+------+------+

5 rows in set (0.00 sec)

同时加入多条记录

mysql> insert class_1 values('wang','19','man'),('li','16','woman'),('liu','17','woman');

Query OK, 3 rows affected, 3 warnings (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 3

mysql> select name,age,sex from class_1 ;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| liao | 18 | m |

| tom | 19 | NULL |

| jarry | 18 | w |

| wang | 19 | m |

| li | 16 | w |

| liu | 17 | w |

+--------+------+------+

8 rows in set (0.00 sec)

记录更新

表中的值可以通过update来更新语法如下：

UPDATE tablename SET filed1=value1,filed2=value2...[WHERE CONDITION];

例 如将表中的liao 的age更改为20：

mysql> update test.class_1 set age=20 where name="liao";Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

mysql> select name,age from class_1;

+--------+------+

| name | age |

+--------+------+

| marry | 28 |

| liaoxz | 18 |

| liao | 20 |

| tom | 19 |

| jarry | 18 |

| wang | 19 |

| li | 16 |

| liu | 17 |

+--------+------+

8 rows in set (0.00 sec)

记录删除

当数据不再需要时，可以使用delete将其删除，例如将class_1中所有name为liao的所有数据删除；

语法如下：

DELETE FROM tablename [WHERE CONDITION];

mysql> delete from class_1 where name='liao';

Query OK, 1 row affected (0.00 sec)

mysql> select name,age,sex from class_1;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| tom | 19 | NULL |

| jarry | 18 | w |

| wang | 19 | m |

| li | 16 | w |

| liu | 17 | w |

+--------+------+------+

7 rows in set (0.00 sec)

记录查询

当想要查看数据库中其中一条数据信息时可以使用select命令查找；基本语法如下；

SELECT*FROM tablename [WHERE CONDITION];

常见用法

例1

查看表中class_1中所有数据；

mysql> select * from test.class_1;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| tom | 19 | NULL |

| jarry | 18 | w |

| wang | 19 | m |

| li | 16 | w |

| liu | 17 | w |

+--------+------+------+

7 rows in set (0.00 sec)

例2

查看class_1中所有age为19的用户记录

mysql> select * from class_1 where age=19;

+------+------+------+

| name | age | sex |

+------+------+------+

| tom | 19 | NULL |

| wang | 19 | m |

+------+------+------+

2 rows in set (0.00 sec)

根据查询不同要求可以将查询分为以下几类：

1)去重查询

使用distinct关键字来实现

例如查看表class_2中age去重后显示出来

mysql> select distinct age from class_2;

+------+

| age |

+------+

| 18 |

| 19 |

| 16 |

+------+

3 rows in set (0.00 sec)

从上面可以看出class_2表中学生age都在16,18,19之间

2)条件查询

条件查询同where后的字段进行条件比较，比较符号包括 >,=,!= 还可以使用多个条件 使用or ，and等逻辑运算符进行多条件查询。

例子1 查看class_1表中age大于等于18的数据，

mysql> select name,age,sex from class_1 where age>=18;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| marry | 28 | w |

| liaoxz | 18 | m |

| tom | 19 | NULL |

| jarry | 18 | w |

| wang | 19 | m |

+--------+------+------+

5 rows in set (0.00 sec)

例子2 查看class_1表中sex为man，且年龄大于等于18的数据

mysql> select name,age,sex from class_1 where sex='m' and age>=18;

+--------+------+------+

| name | age | sex |

+--------+------+------+

| liaoxz | 18 | m |

| wang | 19 | m |

+--------+------+------+

2 rows in set (0.00 sec)

3)输出排序查询

在查询过程中常会将数据进行排序后进行查看，在这里排序查看使用关键字 ORDER BY来实现，语法如下：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY filed1 [DESC|ASC]];

DESC是按照字段进行排序，如果第一个字段值一样赋值会按照第二个字段进行比较排序，ASC是按照升序排列，不加sac默认也是升序排列

例子 1 将表class_1按照age进行排序，

mysql> select name,age,sex from class_1 order by age asc;

+--------+------+-------+

| name | age | sex |

+--------+------+-------+

| li | 16 | woman |

| liu | 17 | woman |

| liaoxz | 18 | man |

| jarry | 18 | woman |

| tom | 19 | NULL |

| wang | 19 | man |

| marry | 28 | woman |

+--------+------+-------+

7 rows in set (0.00 sec)

4)限定输出查询

当一张表中数据过多时，我们只想查看其中一部分可以使用LIMIT关键字实现，LIMIT 语法如下：

SELECT ... [LIMIT offset_start,row_count]

offset_start表示记录起始偏移量，row_count表示行数；

注意：默认其实偏移量为0，例；显示class_1表中按照age排序后的前三条记录

mysql> select * from class_1 order by age;

+--------+------+-------+

| name | age | sex |

+--------+------+-------+

| li | 16 | woman |

| liu | 17 | woman |

| liaoxz | 18 | man |

| jarry | 18 | woman |

| tom | 19 | NULL |

| wang | 19 | man |

| marry | 28 | woman |

+--------+------+-------+

7 rows in set (0.00 sec)

mysql> select * from class_1 order by age limit 3;

+--------+------+-------+

| name | age | sex |

+--------+------+-------+

| li | 16 | woman |

| liu | 17 | woman |

| liaoxz | 18 | man |

+--------+------+-------+

3 rows in set (0.00 sec)

例2 显示从第3条开始后的三条记录

mysql> select * from class_1 order by age limit 2,3;

+--------+------+-------+

| name | age | sex |

+--------+------+-------+

| liaoxz | 18 | man |

| jarry | 18 | woman |

| tom | 19 | NULL |

+--------+------+-------+

3 rows in set (0.00 sec)

写在后面：多表操作将在后续文章中陆续贴出