mysql---DML语句

来源：互联网发布：ubuntu arm 黑客工具编辑：程序博客网时间：2024/06/05 00:43

插入数据

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: 0mysql> 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)

0 0