mysql---DML语句

插入数据

mysql> insert into em(age, name, address, hiredate, sal) values (12, 'zhangsa', 'china', '2016-01-02', 'hjhjflx');
Query OK, 1 row affected (0.00 sec)

直接插入

mysql> insert into em values (13, 'lisi', 'beijing', '2017-09-08', 'ttadfad');
Query OK, 1 row affected (0.01 sec)

部分插入

mysql> insert into em(age, hiredate) values (14, '2015-09-04');
Query OK, 1 row affected (0.00 sec)

查看表中记录数据

mysql> select * from em;
+------+---------+---------+------------+---------+
| age  | name    | address | hiredate   | sal     |
+------+---------+---------+------------+---------+
|   12 | zhangsa | china   | 2016-01-02 | hjhjflx |
|   13 | lisi    | beijing | 2017-09-08 | ttadfad |
|   14 | NULL    | NULL    | 2015-09-04 | NULL    |
+------+---------+---------+------------+---------+
3 rows in set (0.00 sec)

一次插入多条数据

mysql> insert into em(age, address) values(11, 'china'), (12, 'shanghai'),(13, 'beijing');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

将name=“zhangsa“的sal值更改为eandroid

mysql> update em set sal='eandroid' where name='zhangsa';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from em;
+------+---------+----------+------------+----------+
| age  | name    | address  | hiredate   | sal      |
+------+---------+----------+------------+----------+
|   12 | zhangsa | china    | 2016-01-02 | eandroid |
|   13 | lisi    | beijing  | 2017-09-08 | ttadfad  |
|   14 | NULL    | NULL     | 2015-09-04 | NULL     |
|   11 | NULL    | china    | NULL       | NULL     |
|   12 | NULL    | shanghai | NULL       | NULL     |
|   13 | NULL    | beijing  | NULL       | NULL     |
+------+---------+----------+------------+----------+
6 rows in set (0.00 sec)

删除记录

mysql> delete from em where age=11;
Query OK, 1 row affected (0.01 sec)

mysql> select * from em;
+------+---------+----------+------------+----------+
| age  | name    | address  | hiredate   | sal      |
+------+---------+----------+------------+----------+
|   12 | zhangsa | china    | 2016-01-02 | eandroid |
|   13 | lisi    | beijing  | 2017-09-08 | ttadfad  |
|   14 | NULL    | NULL     | 2015-09-04 | NULL     |
|   12 | NULL    | shanghai | NULL       | NULL     |
|   13 | NULL    | beijing  | NULL       | NULL     |
+------+---------+----------+------------+----------+
5 rows in set (0.00 sec)

查看部分数据

mysql> select * from em where age=12;
+------+---------+----------+------------+----------+
| age  | name    | address  | hiredate   | sal      |
+------+---------+----------+------------+----------+
|   12 | zhangsa | china    | 2016-01-02 | eandroid |
|   12 | NULL    | shanghai | NULL       | NULL     |
+------+---------+----------+------------+----------+
2 rows in set (0.00 sec)

mysql> select age, name from em;
+------+---------+
| age  | name    |
+------+---------+
|   12 | zhangsa |
|   13 | lisi    |
|   14 | NULL    |
|   12 | NULL    |
|   13 | NULL    |
+------+---------+
5 rows in set (0.00 sec)

查询不重复的数据(distinct)

mysql> select distinct age from em;
+------+
| age  |
+------+
|   12 |
|   13 |
|   14 |
+------+
3 rows in set (0.01 sec)

排序查询

mysql> select * from em order by age;
+------+---------+----------+------------+----------+
| age  | name    | address  | hiredate   | sal      |
+------+---------+----------+------------+----------+
|   12 | zhangsa | china    | 2016-01-02 | eandroid |
|   12 | NULL    | shanghai | NULL       | NULL     |
|   13 | lisi    | beijing  | 2017-09-08 | ttadfad  |
|   13 | NULL    | beijing  | NULL       | NULL     |
|   14 | NULL    | NULL     | 2015-09-04 | NULL     |
+------+---------+----------+------------+----------+
5 rows in set (0.00 sec)

限制查询

mysql> select * from em order by age;
+------+---------+----------+------------+----------+
| age  | name    | address  | hiredate   | sal      |
+------+---------+----------+------------+----------+
|   12 | zhangsa | china    | 2016-01-02 | eandroid |
|   12 | NULL    | shanghai | NULL       | NULL     |
|   13 | lisi    | beijing  | 2017-09-08 | ttadfad  |
|   13 | NULL    | beijing  | NULL       | NULL     |
|   14 | NULL    | NULL     | 2015-09-04 | NULL     |
+------+---------+----------+------------+----------+
5 rows in set (0.00 sec)

mysql> select * from em order by age limit 1, 3;
+------+------+----------+------------+---------+
| age  | name | address  | hiredate   | sal     |
+------+------+----------+------------+---------+
|   12 | NULL | shanghai | NULL       | NULL    |
|   13 | lisi | beijing  | 2017-09-08 | ttadfad |
|   13 | NULL | beijing  | NULL       | NULL    |
+------+------+----------+------------+---------+
3 rows in set (0.00 sec)

limit和order by在一起进行分页查询
获取总记录数

mysql> select count(1) from em;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select * from em;
+------+---------+----------+------------+----------+
| age  | name    | address  | hiredate   | sal      |
+------+---------+----------+------------+----------+
|   12 | zhangsa | china    | 2016-01-02 | eandroid |
|   13 | lisi    | beijing  | 2017-09-08 | ttadfad  |
|   14 | NULL    | NULL     | 2015-09-04 | NULL     |
|   12 | NULL    | shanghai | NULL       | NULL     |
|   13 | NULL    | beijing  | NULL       | NULL     |
+------+---------+----------+------------+----------+
5 rows in set (0.00 sec)