CREATETABLE users(
idSMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
usernameVARCHAR(20) NOT NULL,
passwordVARCHAR(20) NOT NULL,
ageTINYINT UNSIGNED NOT NULL DEFAULT 10,
sexBOOLEAN
);
*在表中，若为某子段设置为AUTO_INCREMENT，在插入时即使将此子段设为NULL，实际插入中也会按照默认的顺序赋值。
如下：
INSERTusers VALUES (NULL, 'Tom', '123', 25, 1);
INSERTusers VALUES (NULL, 'John', '456', 25, 1);
结果为
+----+----------+----------+-----+------+
| id| username | password | age | sex |
+----+----------+----------+-----+------+
| 1| Tom | 123 | 25 | 1 |
| 2| John | 456 | 25 | 1 |
插入多条记录用如下语句
INSERT uses VALUES (NULL, 'TOM', '123', 25, 1), (DEFAULT, 'John',md5('123'), 2 * 3, 1);
此处NULL和DEFAULT都不影响实际ID值。
*md5('password')为函数，将存入md5值。
更新数据时，可以使用如下语句
UPDATEusers set age = age + 5 WHERE (id % 2) = 0;
*WHERE后为条件，可有可无。
删除单条数据时，可以使用如下语句
DELETEFORM users WHERE id = 5;
查询数据，可以使用如下语句
SELECT sex FROM users;
+------+
|sex |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
+------+
查询分组
SELECTsex FROM users GROUP BY sex;
+------+
|sex |
+------+
| 1 |
+------+
1row in set (0.00 sec)
条件查询分组
SELECTsex FROM users GROUP BY sex HAVING age > 35;
*末尾可考虑加[ASC|DESC]，默认ASC升序。DESC降序；
ERROR1054 (42S22): Unknown column 'age' in 'having clause'
因为，没有将age包含在SELECTFROM 之间。
正确方法应该是
SELECTsex , age FROM users GROUP BY sex HAVING age > 35;
+------+-----+
|sex | age |
+------+-----+
| 1 | 40 |
多条件查询排列
默认为SELECT* FROM users
需要将按id降序则
SELECT* FROM users ORDER BY id DESC;
+----+----------+----------+-----+------+
| id| username | password | age | sex |
+----+----------+----------+-----+------+
| 5| Tom | 789 | 25 | 1 |
| 4| Tom | 789 | 25 | 1 |
| 3| Tom | 456 | 25 | 1 |
| 1| Tom | 123 | 40 | 1 |
+----+----------+----------+-----+------+
4rows in set (0.00 sec)
若需要在id相同的时候，以age逆序排列则用如下语句
SELECT* FROM users ORDER BY id DESC, age DESC;
限制查询数量【默认第一条记录为0】
LIMIT3; 显示查询前三条
LIMIT0，3;显示查询前三条
LIMIT1，4;显示查询2到6条
mysql>CREATE TABLE test(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
QueryOK, 0 rows affected (0.75 sec)
mysql>INSERT test SELECT username FROM users WHERE age >=30;
ERROR1136 (21S01): Column count doesn't match value count at row 1
mysql>INSERT test(username) SELECT username FROM users WHERE age >=30;
QueryOK, 1 row affected (0.37 sec)
Records:1 Duplicates: 0 Warnings: 0
mysql>SELECT * FROM tesx
-> SELECT * FROM tesx;
ERROR1064 (42000): You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax touse near 'SELECT * FROM tesx' at line 2
mysql>SELECT * FROM test;
+----+----------+
| id| username |
+----+----------+
| 1| Tom |
+----+----------+
1row in set (0.00 sec)
mysql>Ctrl-C -- exit!
Aborted
[pcrane@localhost~]$
