MySQL语句总结（二）

数据的增删改查

插入数据

查看person的建表语句：

person | CREATE TABLE `person` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

INSERT person VALUES(NULL,'top');
向person表中插入数据（主键pid是自增类型的所以再插入数据时：在不指定键的默认情况下应该传NULL（在传入数字时，可传入表达式）

+-----+------+
| pid | name |
+-----+------+
|   1 | top  |
+-----+------+

更新语句

UPDATE person set name = 'wang'
将person表中所有的name字段改为 wang

+-----+------+
| pid | name |
+-----+------+
|   1 | wang |
|   2 | wang |
|   3 | wang |
|   4 | wang |
|   5 | wang |
+-----+------+

UPDATE person set name = 'jack' WHERE pid % 2 =0;
将person表中所有pid为偶数的记录的name改为jack

+-----+------+
| pid | name |
+-----+------+
|   1 | wang |
|   2 | jack |
|   3 | wang |
|   4 | jack |
|   5 | wang |
+-----+------+

删除语句

DELETE FROM person WHERE pid = 5;
删除person表中 pid 为5的记录

+-----+------+
| pid | name |
+-----+------+
|   1 | wang |
|   2 | jack |
|   3 | wang |
|   4 | jack |
+-----+------+

SELECT语句

SELECT NOW();
查询当前时间

+---------------------+
| NOW()               |
+---------------------+
| 2017-09-07 21:59:41 |
+---------------------+

SELECT pid FROM person;
只查询pid这一列的数据

+-----+
| pid |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+

SELECT name , pid FROM person;
查询两列。查询时的字段与数据表的实际字段没关系，与查询的结果有关（先查name，后查pid ），查询时*表示数据表的所有列

+------+-----+
| name | pid |
+------+-----+
| wang |   1 |
| jack |   2 |
| wang |   3 |
| jack |   4 |
+------+-----+

SELECT name AS pname , pid AS id FROM person;
AS关键字：关键字将查询的列名映射为AS指定的字段，影响带查询结果（查询时是name，和 pid 查询结果是 pname和id）

+-------+----+
| pname | id |
+-------+----+
| wang  |  1 |
| jack  |  2 |
| wang  |  3 |
| jack  |  4 |
+-------+----+

GROUP BY语句

+-----+-------+---------+
| pid | name  | classid |
+-----+-------+---------+
|   5 | tom   |       2 |
|   6 | jack  |       2 |
|   7 | lilei |       3 |
|   8 | han   |       1 |
|   9 | jon   |       2 |
|  10 | snow  |       3 |
+-----+-------+---------+

SELECT classid FROM person GROUP BY classid;
数据表按照classid分类

+---------+
| classid |
+---------+
|       1 |
|       2 |
|       3 |
+---------+

LIMIT语句

SELECT *FROM person LIMIT 2,3;
从person表中取出从索引为2开始的3条记录

+-----+-------+---------+
| pid | name  | classid |
+-----+-------+---------+
|   7 | lilei |       3 |
|   8 | han   |       1 |
|   9 | jon   |       2 |
+-----+-------+---------+

CREATE TABLE classtwo( 
tid INT PRIMARY KEY AUTO_INCREMENT, 
tname VARCHAR(20) 
 );

创建新的数据表classtwo

INSERT classtwo(tname) SELECT name FROM person WHERE classid = 2;
检索出person表中classid为2的记录并将name插入classtwo的字段中

+-----+-------+
| tid | tname |
+-----+-------+
|   1 | tom   |
|   2 | jack  |
|   3 | jon   |
+-----+-------+