mysql学习4—插入、查询、删除

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~]$