INSERT [INTO] tbl_name [(col_name,...)] {VAULES|VALUE} ({expr|DEFAULT},...),(...),...
mysql> USE test Database changed mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL, -> password VARCHAR(32) NOT NULL, -> age TINYINT UNSIGNED NOT NULL DEFAULT 10, -> sex BOOLEAN -> ); mysql> INSERT users VALUES(NULL,'Tom','asd123',24,1); mysql> SELECT * FROM users; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | Tom | asd123 | 24 | 1 | +----+----------+----------+-----+------+
mysql> INSERT users VALUES(DEFAULT,'John','asd123',24,1), -> (NULL,'Huang','daddd',25,1); mysql> SELECT * FROM users; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | | 3 | Huang | daddd | 25 | 1 | +----+----------+----------+-----+------+
mysql> INSERT users VALUES(NULL,'John','asd123',3*7-5,1); mysql> SELECT * FROM users; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | | 3 | Huang | daddd | 25 | 1 | | 4 | John | asd123 | 16 | 1 | +----+----------+----------+-----+------+
mysql> INSERT users VALUES(NULL,'John',md5('123'),DEFAULT,0); mysql> SELECT * FROM users; +----+----------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------+----------------------------------+-----+------+ | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | | 3 | Huang | daddd | 25 | 1 | | 4 | John | asd123 | 16 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 10 | 0 | +----+----------+----------------------------------+-----+------+
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
mysql> INSERT users SET username='Ben',password=md5('345'); mysql> SELECT * FROM users; +----+----------------------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------------------+----------------------------------+-----+------+ | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | | 3 | Huang | daddd | 25 | 1 | | 4 | John | asd123 | 16 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 10 | 0 | | 6 | Ben | d81f9c1be2e08964bf9f24b15f0e4900 | 10 | NULL | +----+----------------------+----------------------------------+-----+------+
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}]... [WHERE where_condition]
mysql> UPDATE users SET age = age+5; mysql> SELECT * FROM users; +----+----------------------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------------------+----------------------------------+-----+------+ | 1 | Tom | asd123 | 29 | 1 | | 2 | John | asd123 | 29 | 1 | | 3 | Huang | daddd | 30 | 1 | | 4 | John | asd123 | 21 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | | 6 | Ben | d81f9c1be2e08964bf9f24b15f0e4900 | 15 | NULL | +----+----------------------+----------------------------------+-----+------+
mysql> UPDATE users SET age = age+10 WHERE id=1; mysql> SELECT * FROM users WHERE id=1; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | Tom | asd123 | 39 | 1 | +----+----------+----------+-----+------+
mysql> UPDATE users SET age = age+10 WHERE id % 2 = 0; mysql> select * from users; +----+----------------------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------------------+----------------------------------+-----+------+ | 1 | Tom | asd123 | 39 | 1 | | 2 | John | asd123 | 39 | 1 | | 3 | Huang | daddd | 30 | 1 | | 4 | John | asd123 | 31 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | +----+----------------------+----------------------------------+-----+------+
mysql> UPDATE users SET sex = 0; mysql> select * from users; +----+----------------------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------------------+----------------------------------+-----+------+ | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | 39 | 0 | | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | 31 | 0 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | | 7 | Ben | d81f9c1be2e08964bf9f24b15f0e4900 | 15 | 0 | +----+----------------------+----------------------------------+-----+------+
DELETE FROM tbl_name [WHERE where_condition]
mysql> DELETE FROM users WHERE id = 7; mysql> select * from users; +----+----------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------+----------------------------------+-----+------+ | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | 39 | 0 | | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | 31 | 0 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | +----+----------+----------------------------------+-----+------+
SELECT select_expr [,select_expr...] [ FROM tbl_references [WHERE where_condition] [GROUP BY {col_name | position} [ASC | DESC],...] [HAVING where_condition] [ORDER BY {col_name | expo | position} [ASC | DESC],...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] ]
查询表达式(select_expr)
mysql> SELECT id,username FROM users; +----+----------+ | id | username | +----+----------+ | 1 | Tom | | 2 | John | | 3 | Huang | | 4 | John | | 5 | John | +----+----------+
mysql> SELECT id AS userid ,username AS uname FROM users; +--------+-------+ | userid | uname | +--------+-------+ | 1 | Tom | | 2 | John | | 3 | Huang | | 4 | John | | 5 | John | +--------+-------+
[GROUP BY {col_name | position} [ASC | DESC],...]
mysql> SELECT sex FROM users GROUP BY sex; +------+ | sex | +------+ | 0 | +------+
[HAVING where_condition]
mysql> SELECT sex,age FROM users GROUP BY sex having age > 20; +------+-----+ | sex | age | +------+-----+ | 0 | 39 | +------+-----+
mysql> SELECT sex FROM users GROUP BY sex HAVING count(id) >= 2; +------+ | sex | +------+ | 0 | +------+
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
mysql> SELECT * FROM users ORDER BY age ASC; +----+----------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------+----------------------------------+-----+------+ | 8 | Hui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 8 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | | 10 | Luyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 21 | 1 | | 9 | Dui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 27 | 1 | | 3 | Huang | daddd | 30 | 0 | | 12 | Murouan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 11 | Oduyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 13 | Spqoan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 4 | John | asd123 | 31 | 0 | | 2 | John | asd123 | 39 | 0 | | 1 | Tom | asd123 | 39 | 0 | +----+----------+----------------------------------+-----+------+
mysql> SELECT * FROM users ORDER BY age,id ASC; +----+----------+----------------------------------+-----+------+ | id | username | password | age | sex | +----+----------+----------------------------------+-----+------+ | 8 | Hui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 8 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | | 10 | Luyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 21 | 1 | | 9 | Dui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 27 | 1 | | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | 31 | 0 | | 11 | Oduyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 12 | Murouan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 13 | Spqoan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | 39 | 0 | +----+----------+----------------------------------+-----+------+
[LIMIT{[offset,]row_count|row_count OFFSET offset}]
SELECT * from users #返回所有的结果 SELECT * from users limit 2 #返回所有的结果集中前两条
mysql> SELECT * FROM users LIMIT 2; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | 39 | 0 | +----+----------+----------+-----+------+
SELECT * FROM users LIMIT 2,2; #要查询结果集中第3条和第4条记录。mysql中的记录是从0开始的,共几条。
mysql> SELECT * FROM users LIMIT 2,2; +----+----------+----------+-----+------+ | id | username | password | age | sex | +----+----------+----------+-----+------+ | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | 31 | 0 | +----+----------+----------+-----+------+
insert [into] 表名 [(列名,列名)]{values|value} ({(表达式|default},()) insert [into] 表名 set 列名={(表达式|default},。。 insert [into] 表名 [(列名,列名)] SELECT..
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition]
DELETE FROM tbl_name [WHERE where_condition}
SELECT select_expr [,select expr2...] #只查找某一个函数或表达式 [ FROM table_references #查询表名 [WHERE where_conditon] #查询条件 [GROUP BY {col_name|position} [ASC|DESC],...] #按某个字段进行分组,相同的只显示第一个 [HAVING where_conditon] #分组时,给出显示条件 [ORDER BY {col_name|expr|position} [ASC|DESC],...] #排序 [LIMIT {[offset,]row_count|row_count OFFSET offset}] #限制返回数量 ]
mysql> CREATE TABLE test( -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(20) NOT NULL -> );
mysql> INSERT test(username) SELECT username FROM users WHERE age >= 25; mysql> SELECT * FROM test; +----+----------+ | id | username | +----+----------+ | 1 | Tom | | 2 | John | | 3 | Huang | | 4 | John | | 5 | Dui | | 6 | Oduyuan | | 7 | Murouan | | 8 | Spqoan | +----+----------+