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,womanmysql> 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)写在后面：多表操作将在后续文章中陆续贴出
1741
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
