INSERT [INTO] tbl_name [(col_name,……)] {VALUES|VALUE}({expr|DEFAULT},……),(),……
其中,列名称是可选的,若省略了列名称,表示要为 所有的字段依次赋值。
mysql> CREATE TABLE users( #创建数据表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 BOOLEAN
-> );
Query OK, 0 rows affected (0.10 sec)
将NULL或DEFAULT赋给id列,表示id字段采用原来定义好的自动编号。
mysql> INSERT INTO users VALUES(NULL,'tom','123',25,1);#将NULL赋给id列,表示id字段采用原来定义好的自动编号。
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',25,0);#将DEFAULT赋给id列,表示id字段采用原来定义好的自动编号。
Query OK, 1 row affected (0.15 sec)
mysql> SELECT * FROM users;#可以看到,id字段采用的是自动编号。
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',25);#若省略了列名称,必须要为所有的字段依次赋值。
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',3*7-1,0);#将表达式 3*7-1 的结果赋给age字段。
Query OK, 1 row affected (0.13 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
| 3 | jerry | 3423 | 20 | 0 |
+----+----------+----------+-----+------+
3 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM users; #可以看到,age字段定义了默认值10
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',DEFAULT,1);#将DEFAULT赋给age字段,表示age字段采用默认值10。
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
| 3 | jerry | 3423 | 20 | 0 |
| 4 | jerry | 3423 | 10 | 1 |
+----+----------+----------+-----+------+
4 rows in set (0.00 sec)
mysql> INSERT users VALUES(DEFAULT,'marry','mima',23,1),(NULL,'rose','345',8,0);# 一次插入多条记录。
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
| 3 | jerry | 3423 | 20 | 0 |
| 4 | jerry | 3423 | 10 | 1 |
| 5 | marry | mima | 23 | 1 |
| 6 | rose | 345 | 8 | 0 |
+----+----------+----------+-----+------+
6 rows in set (0.00 sec)
mysql> INSERT users VALUES(DEFAULT,'song',md5('mima'),23,0);#函数md5('mima') 表示 计算字符串'mima'的md5值。
Query OK, 1 row affected (0.14 sec)
mysql>SELECT * FROM users;# 其中 3f572fcb0f9af03848738946954b8c43 就是字符串'mima'的md5值。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
| 3 | jerry | 3423 | 20 | 0 |
| 4 | jerry | 3423 | 10 | 1 |
| 5 | marry | mima | 23 | 1 |
| 6 | rose | 345 | 8 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 23 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
插入记录(第2种方法)
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},……
该方法可以使用子查询(SubQuery),但是一次只能插入一条记录。
mysql> INSERT users SET username='Ben',password='2345';#id字段自动编号;age字段设置了默认值10;sex字段允许为空。
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
| 3 | jerry | 3423 | 20 | 0 |
| 4 | jerry | 3423 | 10 | 1 |
| 5 | marry | mima | 23 | 1 |
| 6 | rose | 345 | 8 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 23 | 0 |
| 8 | Ben | 2345 | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
插入记录(第3种方法)
INSERT [INTO] tbl_name [(col_name,……)] SELECT ……
该方法是 将SELECT语句的查询结果 插入到 指定的数据表中!当我们学习了SELECT语句后,再回来练习这种方法。。。
更新记录(单表更新)
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr|DEFAULT}[,col_name2={expr|DEFAULT}……]
[WHERE where_condition] 如果省略了WHERE条件,则表示更新所有记录!
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 25 | 1 |
| 2 | jerry | 3423 | 25 | 0 |
| 3 | jerry | 3423 | 20 | 0 |
| 4 | jerry | 3423 | 10 | 1 |
| 5 | marry | mima | 23 | 1 |
| 6 | rose | 345 | 8 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 23 | 0 |
| 8 | Ben | 2345 | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> UPDATE users SET age=age+5; #将age字段的值加5;省略了WHERE条件,表示更新所有记录。
Query OK, 8 rows affected (0.11 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 30 | 1 |
| 2 | jerry | 3423 | 30 | 0 |
| 3 | jerry | 3423 | 25 | 0 |
| 4 | jerry | 3423 | 15 | 1 |
| 5 | marry | mima | 28 | 1 |
| 6 | rose | 345 | 13 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 28 | 0 |
| 8 | Ben | 2345 | 15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> UPDATE users SET age=age+id,sex=1; #更新age字段和sex字段;
Query OK, 8 rows affected (0.06 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 1 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 1 |
| 5 | marry | mima | 33 | 1 |
| 6 | rose | 345 | 19 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> UPDATE users SET sex=0 WHERE id%2=0;#更新id为偶数的记录
Query OK, 4 rows affected (0.08 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 6 | rose | 345 | 19 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
删除记录(单表删除)
DELETE FROM tbl_name [WHERE where_condition] 如果省略了WHERE条件,将删除数据表中的全部记录。
mysql> DELETE FROM users WHERE id=6; # 删除id=6的记录。
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM users; #可以看到,id=6的记录已经被删除。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
如果此时,向数据表users中插入一条记录,其id会是多少呢?(是6,还是9呢?)
mysql> INSERT users VALUES(DEFAULT,'ping','123',26,NULL);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM users; #可以看到,新插入的记录的id为9(在之前 最大id的基础上 加1 )
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
针对数据表的操作,SELECT语句能占到80%以上。下面是SELECT语句的介绍:
查找记录,如下:
SELECT语句最简单的形式,如下:
mysql> SELECT VERSION(); #查询函数的返回值
+-----------+
| VERSION() |
+-----------+
| 5.5.37 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 3+8; #查询表达式的结果
+-----+
| 3+8 |
+-----+
| 11 |
+-----+
1 row in set (0.00 sec)
查询表达式,如下:
mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> SELECT id,username FROM users;#从数据表users中,查找id列和username列。
+----+----------+
| id | username |
+----+----------+
| 1 | tom |
| 2 | jerry |
| 3 | jerry |
| 4 | jerry |
| 5 | marry |
| 7 | song |
| 8 | Ben |
| 9 | ping |
+----+----------+
8 rows in set (0.00 sec)
mysql> SELECT username,id FROM users; # SELECT语句中字段(查询表达式)的顺序会影响结果中字段的顺序。
+----------+----+
| username | id |
+----------+----+
| tom | 1 |
| jerry | 2 |
| jerry | 3 |
| jerry | 4 |
| marry | 5 |
| song | 7 |
| Ben | 8 |
| ping | 9 |
+----------+----+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users;#查询数据表users中的所有列。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT users.* FROM users; #查询数据表users中的所有列。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
如果只是操作一个数据表,可以只写id(不用写成users.id的形式);
以后,我们会学到多表连接,如果两张表中存在相同的字段id,则只写id就会产生歧义,不知道到底指的是哪张表中的id。
mysql> SELECT users.id,users.username FROM users; #使用users.id的形式,在任何情况下,都不会有歧义。
+----+----------+
| id | username |
+----+----------+
| 1 | tom |
| 2 | jerry |
| 3 | jerry |
| 4 | jerry |
| 5 | marry |
| 7 | song |
| 8 | Ben |
| 9 | ping |
+----+----------+
8 rows in set (0.04 sec)
有时候,某些字段的名字太长,不容易记忆和书写,可以为该字段起一个别名,别名会出现在结果中(别名会影响到结果集)。
mysql> SELECT id AS userID,username AS uname FROM users;#为id字段起一个别名userID,为username字段起一个别名uname。
+--------+-------+
| userID | uname |
+--------+-------+
| 1 | tom |
| 2 | jerry |
| 3 | jerry |
| 4 | jerry |
| 5 | marry |
| 7 | song |
| 8 | Ben |
| 9 | ping |
+--------+-------+
8 rows in set (0.03 sec)
起别名时,AS关键词虽然可以省略,但是,推荐最好使用AS关键词。
mysql> SELECT id username FROM users;#为id字段起一个别名username(此处,省略了AS关键词)
+----------+
| username |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
+----------+
8 rows in set (0.00 sec)
注意:上面这个例子中的username指的是id字段的别名username,不是数据表users中的字段名username!!
mysql> SELECT id AS username FROM users; #起别名时,推荐写上AS关键字。(此处的username指的是id字段的别名。)
+----------+
| username |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
+----------+
8 rows in set (0.00 sec)
WHERE条件表达式
对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。
在WHERE表达式中,可以使用MySQL支持的函数或运算符。
mysql> SELECT * FROM users;#如果没有指定WHERE子句,则显示所有记录。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users WHERE id>=8; #显示id>=8的记录
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
GROUP BY查询结果分组
[GROUP BY {col_name|position}[ASC|DESC],……]
col_name:按照列名分组; position:按照位置分组(位置从1开始);
ASC:ascending order 升序; DESC:descending order 降序; 默认是ASC升序。。。
如果存在多个分组条件,则多个分组条件之间用逗号分隔。
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT sex FROM users;
+------+
| sex |
+------+
| 1 |
| 0 |
| 1 |
| 0 |
| 1 |
| 1 |
| 0 |
| NULL |
+------+
8 rows in set (0.00 sec)
mysql> SELECT sex FROM users GROUP BY sex; #按照列名sex进行分组(对所有的记录做分组)
+------+
| sex |
+------+
| NULL |
| 0 |
| 1 |
+------+
3 rows in set (0.05 sec)
mysql> SELECT sex FROM users GROUP BY 1;#按照位置分组(对所有的记录做分组)(1指的是SELECT关键词后边,出现的第1个字段名)
+------+
| sex |
+------+
| NULL |
| 0 |
| 1 |
+------+
3 rows in set (0.00 sec)
上述2种分组方法,我们推荐按照列名来分组!!下面,举一个 有多个分组条件的例子:
mysql> SELECT id,sex FROM users GROUP BY sex,id;#分组条件第一优先级sex,第二优先级id;(对所有的记录做分组)
+----+------+
| id | sex |
+----+------+
| 9 | NULL |
| 2 | 0 |
| 4 | 0 |
| 8 | 0 |
| 1 | 1 |
| 3 | 1 |
| 5 | 1 |
| 7 | 1 |
+----+------+
8 rows in set (0.00 sec)
having语句设置分组条件
[HAVING where_condition]
我们可以省略having语句,对所有记录做分组;也可以通过having语句设置分组条件,对部分记录做分组。
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT sex FROM users GROUP BY sex HAVING age >23;
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'
这个例子报错了,因为使用HAVING语句设置分组条件需要满足下面2个条件中的任意一个:
1》HAVING 后的字段名(分组条件)出现在SELECT关键词的后面。
用Having条件分组时候,其条件字段必须出现在select语句中,Having是对select之后的结果进行条件判断。
2》HAVING 后的字段名(分组条件)和聚合函数配合使用。
聚合函数就是把数据聚合起来的函数,如果想对符合某些条件的所有数据进行操作(例如加在一起),那么聚合函数就能派上用途了。
MySQL中比较常用的聚合函数有:
求和函数SUM()
计数函数COUNT()
平均值函数AVG()
最大/最小值函数MAX()/MIN()
mysql> SELECT sex,age FROM users GROUP BY sex HAVING age >23;#HAVING后的字段名age出现在SELECT关键词的后面。
+------+-----+
| sex | age |
+------+-----+
| NULL | 26 |
| 0 | 32 |
| 1 | 31 |
+------+-----+
3 rows in set (0.03 sec)
mysql> SELECT sex FROM users GROUP BY sex HAVING count(id)>=2; #HAVING后的字段名id和聚合函数配合使用。
+------+
| sex |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.04 sec)
上面这条指令,指按照sex分组,分成了三组:sex=0,sex=1和sex=NULL,条件id数大于等于2的留下显示,
显然sex=0个数有3>=2故留下;而sex=NULL个数为1,不满足条件,故不显示;sex=1的个数有4>=2故留下。。。
下面对比介绍一下,mysql中的where和having子句。。。
having子句可以让我们筛选成组后的各种数据,where子句在聚合前先筛选记录,也就是说作用在group by和having子句前。
而having子句在聚合后对组记录进行筛选。
mysql> INSERT users VALUES(DEFAULT,'haha','1234',23,1);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT users VALUES(DEFAULT,'hahhha','1234',23,0);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
| 10 | haha | 1234 | 23 | 1 |
| 11 | hahhha | 1234 | 23 | 0 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)
下面的例子,首先按照sex分组,分成了3组;然后用聚合函数SUM,对每组中的id字段和age字段分别求和。
mysql> SELECT sex,SUM(id),SUM(age) FROM users GROUP BY sex;
+------+---------+----------+
| sex | SUM(id) | SUM(age) |
+------+---------+----------+
| NULL | 9 | 26 |
| 0 | 25 | 97 |
| 1 | 26 | 150 |
+------+---------+----------+
3 rows in set (0.04 sec)
mysql> SELECT sex,SUM(id),SUM(age) FROM users GROUP BY sex HAVING SUM(age)>50;#having子句会筛选成组后的数据。
+------+---------+----------+
| sex | SUM(id) | SUM(age) |
+------+---------+----------+
| 0 | 25 | 97 |
| 1 | 26 | 150 |
+------+---------+----------+
2 rows in set (0.04 sec)
mysql判断某个字段的长度:
mysql> SELECT * FROM users WHERE char_length(trim(username))=4;#查找username字段长度为4的记录
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 9 | ping | 123 | 26 | NULL |
| 10 | haha | 1234 | 23 | 1 |
+----+----------+----------------------------------+-----+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM users WHERE char_length(trim(username))<5 and char_length(username)>2;#查找username字段长度为3或4的记录
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
| 10 | haha | 1234 | 23 | 1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)
mysql中的where和having子句都可以实现过滤记录的功能,但他们的用法还是有一些区别的,看一例子:
mysql> SELECT username,count(*) AS ct FROM users GROUP BY username;
+----------+----+
| username | ct |
+----------+----+
| Ben | 1 |
| haha | 1 |
| hahhha | 1 |
| jerry | 3 |
| marry | 1 |
| ping | 1 |
| song | 1 |
| tom | 1 |
+----------+----+
8 rows in set (0.00 sec)
mysql> SELECT username,count(*) AS ct FROM users GROUP BY username HAVING ct>1;#使用HAVING子句进行过滤。
+----------+----+
| username | ct |
+----------+----+
| jerry | 3 |
+----------+----+
1 row in set (0.00 sec)
先用group by对username进行分组,再用having来过滤大于1的,这样查找出来的就是username字段重复的记录了。。。
mysql> SELECT max(age) FROM users;
+----------+
| max(age) |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users WHERE age=(SELECT max(age) FROM users); #使用WHERE子句进行过滤。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
+----+----------+----------------------------------+-----+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users WHERE age=35;#使用WHERE子句进行过滤。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
+----+----------+----------------------------------+-----+------+
1 row in set (0.00 sec)
having和where的区别:
作用的对象不同;WHERE 子句作用于表和视图,WHERE子句不能包含聚集函数;HAVING子句作用于组,HAVING可以使用聚合函数。
order by语句对查询结果排序
[ ORDER BY {col_name|expr|position}[ASC|DESC]] 默认是ASC升序
ASC:ascending order 升序; DESC:descending order 降序; 默认是ASC升序。。。
mysql> DELETE FROM users WHERE id=10;
Query OK, 1 row affected (0.07 sec)
mysql> DELETE FROM users WHERE id=11;
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM users; #按照 记录的插入顺序 显示。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY id DESC; #按照id降序排列
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 9 | ping | 123 | 26 | NULL |
| 8 | Ben | 2345 | 23 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 5 | marry | mima | 33 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 1 | tom | 123 | 31 | 1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY age; #按照age排序(默认按照升序排列)
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 4 | jerry | 3423 | 19 | 0 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
| 3 | jerry | 3423 | 28 | 1 |
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> INSERT INTO users VALUES(NULL,'haha','1234',23,1);#插入记录
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO users VALUES(NULL,'hahhha','1234',23,0);#插入记录
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM users ORDER BY age; #按照age排序(默认升序排列)
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 4 | jerry | 3423 | 19 | 0 |
| 13 | hahhha | 1234 | 23 | 0 |
| 12 | haha | 1234 | 23 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
| 3 | jerry | 3423 | 28 | 1 |
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY age,id DESC; #首先按照age字段(默认)升序排列;若age字段的值相同,再按照id降序排列。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 4 | jerry | 3423 | 19 | 0 |
| 13 | hahhha | 1234 | 23 | 0 |
| 12 | haha | 1234 | 23 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
| 3 | jerry | 3423 | 28 | 1 |
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)
限制 查询结果返回的数量
[LIMIT {[offset,] row_count|row_count OFFSET offset}]
即:
第1种方式:LIMIT [offset,] row_count offset是可选的,若省略,offset默认取0
第2种方式:LIMIT row_count OFFSET offset
其中,offset的取值从0开始!
mysql> SELECT * FROM users; #没有使用LIMIT子句;返回全部的查询结果。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 8 | Ben | 2345 | 23 | 0 |
| 9 | ping | 123 | 26 | NULL |
| 12 | haha | 1234 | 23 | 1 |
| 13 | hahhha | 1234 | 23 | 0 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM users LIMIT 0,2; #指定offset为0 (offset的取值从0开始,若省略offset,则offset取默认值0)
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users LIMIT 2; #省略了offset;offset取默认值0;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users LIMIT 2 OFFSET 0; #从offset=0开始,连续的返回2条记录(采用第2种方式)
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users LIMIT 2,2; #从offset=2开始,连续的返回2条记录。
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 3 | jerry | 3423 | 28 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
注意:offset的取值从0开始;offset的取值与id字段的取值没有任何的联系!!
mysql> SELECT * FROM users ORDER BY id DESC; #按照id降序排列
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 13 | hahhha | 1234 | 23 | 0 |
| 12 | haha | 1234 | 23 | 1 |
| 9 | ping | 123 | 26 | NULL |
| 8 | Ben | 2345 | 23 | 0 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
| 5 | marry | mima | 33 | 1 |
| 4 | jerry | 3423 | 19 | 0 |
| 3 | jerry | 3423 | 28 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 1 | tom | 123 | 31 | 1 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM users ORDER BY id DESC LIMIT 2,2;#先按照id降序排列;然后,从offset=2开始,连续的返回2条记录;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 9 | ping | 123 | 26 | NULL |
| 8 | Ben | 2345 | 23 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
前面,我们学习过 插入记录有3种方法;现在,我们学习了SELECT子句,可以练习第3种插入记录的方法,如下:
mysql> CREATE TABLE test( #创建数据表test
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> SELECT * FROM users WHERE age>30; #查找数据表users中age>30的记录。
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | tom | 123 | 31 | 1 |
| 2 | jerry | 3423 | 32 | 0 |
| 5 | marry | mima | 33 | 1 |
| 7 | song | 3f572fcb0f9af03848738946954b8c43 | 35 | 1 |
+----+----------+----------------------------------+-----+------+
4 rows in set (0.00 sec)
mysql> INSERT test SELECT username FROM users WHERE age>30;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
上述命令报错了,原因是:SELECT子句的返回结果只有1列;而test数据表包含2列;二者列数不匹配!
mysql> SELECT username FROM users WHERE age>30; #SELECT子句的返回结果只有1列。
+----------+
| username |
+----------+
| tom |
| jerry |
| marry |
| song |
+----------+
4 rows in set (0.00 sec)
正确的做法是:
mysql> INSERT test(username) SELECT username FROM users WHERE age>30;#将SELECT子句的查询结果 写入 指定的数据表test
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test; #可以看到,SELECT子句的查询结果,已经写入到了test数据表。
+----+----------+
| id | username |
+----+----------+
| 1 | tom |
| 2 | jerry |
| 3 | marry |
| 4 | song |
+----+----------+
4 rows in set (0.00 sec)
小结:
本文主要介绍了针对记录的 增删改查 4个操作。
插入记录的3种方法,如下:
更新记录的方法,如下:
删除记录的方法,如下:
查找记录,如下:
(完)