MySQL语句（二）（表的增删改查）

MySQL表的操作

建表
CREATE TABLE [IF NOT EXISTS] tbl_name（
column_name data_type，
…
）； （最后一行不加逗号）

增：
即插入

分为三种：
（1）第一种插入方式
INSERT [INTO] tbl_name [ ( col_name,col_name,…)] { VALUES | VALUE }（{exper | DEFAULT }，…）；

当插入语句省略列名称（即col_name）时，VALUES后对应的值的个数要与表的列数一致，即一一对应。

不省略列名，可对某一个或多个列进行数据插入。

例如： CREATE TABLE users（
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
username VARCHAR(20) NOT NULL，
password VARCHAR(32) NOT NULL，
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOLLEAN
）；

以上为创建数据表users ，并设置列id username password age sex ，并分别对其类型和条件进行描述（UNSIGNED表示无符号即非负，PRIMARY KEY 表示主键约束， AUTO_INCREMENT 表示自动编号，NOT NULL 表示该字段不能为空，以上使用到的条件详细解释将在本文后半段进行）。

以下操作均针对刚建立的表users

插入示例： ①省略字段名（列名）
INSERT users VALUES（NULL,‘TOM’,‘123’,25,1）；

表中id设置为PRIMARY KEY（主键）并AUTO_INCREMENT（自动编号），编号从1开始自增，所以id处可以赋值为NULL（空）或DEFAULT（默认值），即它的自动编号数值。

②插入的值中可以使用表达式
INSERT users VALUES（DEFAULT,‘TOM’,‘123’,3*7-5,1）；

其中3*7-5是表达式。

③利用默认值
INSERT users VALUES（DEFAULT,‘TOM’,‘123’,DEFAULT,1）；

第一个使用的DEFAULT在①中已经解释过，第二个DEFAULT则是在字段定义时就应经设置了默认值10，所以可以使用。

④同时插入多个
INSERT users VALUES（DEFAULT,‘TOM’,‘123’,DEFAULT,1），（NULL,‘ROSE’,mds(‘123’),DEFAULT,0）；

这一操作为同时插入两组值，两组中间用逗号隔开。其中mds为PHP中计算字符串哈希值的一个函数，即赋值中可以使用表达式和函数。

（2）第二种插入方式 （与第一种区别在于可以使用子查询）（但每次只能插入一条数据，所以实际操作中使用较少）
INSERT [INTO] tbl_name SET col_name = { expr | DEFAULT},…

例：INSERT users SET username = ‘Ben’，password = ‘456’；
（此处age字段有默认值10，sex字段可以为空，所以二者均可省略）

（3）第三种插入方式 （用于将查询到的结果插入到指定表中）
INSERT [INTO] tbl_name [ ( col_name，…) ] SELECT …

例：INSERT test(username) SELECT username FROM users WHERE age >= 30；
“将 users 表中age >=30 的username 插入到test表中”。
“SELECT后选择的列名一定要加在INSERT后，因为需要使用，否则会报错”。

删：
删除分为单表删除和多表删除，此时我们只考虑单表的删除操作。

DELETE FROM tbl_name [WHERE where_condition]；

例：DELETE FROM users WHERE id = 6 ；
“id编号为1~8，操作删除了id = 6的值，删除后我们进行新的数据插入时，新数据的编号id = 9”。

改：
与删除一样，改（更新）数据分为单表和多表，此处只考虑单表操作。

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = { expr | DEFAULT} [，col_name2 = { expr | DEFAULT }…] [WHERE where_conditon]；

例：①UPDATE users SET age = age + 5 ；
“对age一整列进行更新，省略WHERE条件”。

②UPDATE users SET age = age - id ， sex = 0 ；
“age字段减去其对应的id的值，sex 全都变为0” （此操作无实际意义，仅做练习使用）

③UPDATE users SET age = age +10 WHERE id % 2 = 0；
“id为偶数的用户年龄加10”。

查：
SELECT col_name [，col_name，…] FROM tbl_name
（后可以加限定条件）
[ WHERE expr ] + [ IN (元素1，元素2，元素3)] “从元素1~3总挑选”
[ WHERE expr ] + [ BETWEEN xx AND xx] “在某一范围内选择”
[ WHERE expr ] + [ IS NULL | NOT NULL] “是否为空”

SELECT查询语句的查询顺序会影响查询结果显示的顺序：如表中第一列为id，第二列为name，但当SELECT语句为 SELECT name，id …时，结果会显示第一列为name，第二列为id。

查询表达式中可以使用[ AS ] alias_name 为字段赋予别名，别名可以用于GROUP BY、ORDER BY、HAVING语句。

例：SELECT id AS userId，username AS uname FROM users；
“查询结果显示两列，分别为userId和uname”。

tip1:
当在不同数据表中存在相同名字字段时，使用table_name.*进行区分
例：SELECT users.id ， users.username FROM users；
可以明确字段所在的表。

tip2：
WHERE条件限定中，可以使用MySQL支持的函数和运算符。

下面介绍刚刚在查询中提及的①GROUP BY ② ORDER BY ③HAVING

ASC指按照升序排列，DESC指按降序排列。

①用于将查询结果分组
[ GUOUP BY { col_name | position } [ ASC | DESC]，…]

例：SELECT sex FROM users GROUP BY sex；
“按照sex值的不同进行分组”

SELECT sex FROM users GROUP BY 1；
“按照sex的第一个值进行分组”（实际中尽量不使用这一分组方式）

②用于对查询结果排序
[ ORDER BY { col_name | expr | positon } [ ASC | DESC ]，…]

例：SELECT * FROM users ； （*代表所有） 按照原有顺序输出

SELECT * FROM users ORDER BY id DESC；
“按照id序号的降序排列”

tip1：
ORDER BY 后面可以加多个列名，即按照多个列进行排序，具体是先按照第一个列的数值进行排列，如果能够得出完整结果，则输出，否则一次按照后续列的值排序。

例：SELECT * FROM users ORDER BY age , id DESC；

③用于对结果部分分组

例：SELECT sex , age FROM users GROUP BY 1 HAVING age > 35 ；
“对字段sex 按照其第一个值分组，但要求只取age > 35的数据”。
“由于分组时使用到了age字段，所以要在SELECT后添加，否则报错”。

上述在SELECT后添加字段可用另一种写法代替：
SELECT sex FROM users GROUP BY 1 HAVING count（id） > 2 ；

补充：
LIMIT的使用，限制查询结果的返回数量

[ LIMIT { [ offset] row_count | row_count OFFSET offset } ]
“offset是指偏移量，在PHP的分页技术中会使用到，此处不进行详细解释”。

例：SELECT * FROM users LIMIT 2；
“结果只返回前两条”

SELECT * FROM users LIMIT 2,2；
“记录的编号是从0开始，第一个2表示返回2条，第二个2表示从编号为2的数据开始返回，即返回了id = 3 和 id = 4的数据”