Mysql基础入门-SQL_DML语句
DML语句：
DML操作是指对数据库中的表进行操作，主要包括记录的插入（insert），更新（update），删除（delete），查询（select）。
记录插入
创建表完成后就需要给器插入记录和数据了，插入记录基本语法如下：
INSERT tablename (fielde1,fielden2....)VALUES(value1,value2....);
下面我给我制作的表名为class_1填入以下下信息
name分别为liao，liaoxz，marryage为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: 3Duplicates: 0Warnings: 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...;
例 如将表中的liao 的age更改为20：
mysql> update test.class_1 set age=20 where name="liao";Query OK, 0 rows affected (0.00 sec)
Rows matched: 1Changed: 0Warnings: 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 ;
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 ;
常见用法
例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后的字段进行条件比较，比较符号包括 >, 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 ];
DESC是按照字段进行排序，如果第一个字段值一样赋值会按照第二个字段进行比较排序，ASC是按照升序排列，不加sac默认也是升序排列
例子 1 将表class_1按照age进行排序，
mysql> select name,age,sex from class_1 orderby 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 ...
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)
写在后面：多表操作将在后续文章中陆续贴出
页:
[1]