MySql,筆記（記錄操作）

標籤：過濾   where   下標   highlight   返回   group   ble   fse   字元   

記錄操作：INSERT/UPDATE/DELETE/SELECT
1.插入記錄(共有三種)
第一種： 運算式

1.INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE}({expr|DEFAULT},...),(...),...mysql> 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 BOOLEAN-> );Query OK, 0 rows affected (0.16 sec)mysql> INSERT INTO users VALUES(NULL,‘Tom‘,‘123‘,25,1);Query OK, 1 row affected (0.06 sec)mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 |+----+----------+----------+-----+------+1 row in set (0.00 sec)mysql> INSERT INTO users VALUES(NULL,‘JULY‘,‘123‘,25,1);Query OK, 1 row affected (0.06 sec)mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 || 2 | JULY | 123 | 25 | 1 |+----+----------+----------+-----+------+2 rows in set (0.00 sec)mysql> INSERT INTO users VALUES(DEFAULT,‘Tom‘,‘123‘,25,1);Query OK, 1 row affected (0.05 sec)mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 || 2 | JULY | 123 | 25 | 1 || 3 | Tom | 123 | 25 | 1 |+----+----------+----------+-----+------+3 rows in set (0.00 sec)mysql> INSERT INTO users VALUES(NULL,‘JULY‘,‘123‘,2*9*6-9,1);Query OK, 1 row affected (0.08 sec)mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 || 2 | JULY | 123 | 25 | 1 || 3 | Tom | 123 | 25 | 1 || 4 | JULY | 123 | 99 | 1 |+----+----------+----------+-----+------+4 rows in set (0.00 sec)mysql> INSERT INTO users VALUES(DEFAULT,‘Tom‘,‘123‘,DEFAULT,1);Query OK, 1 row affected (0.06 sec)mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 || 2 | JULY | 123 | 25 | 1 || 3 | Tom | 123 | 25 | 1 || 4 | JULY | 123 | 99 | 1 || 5 | Tom | 123 | 10 | 1 |+----+----------+----------+-----+------+5 rows in set (0.00 sec)mysql> INSERT INTO users VALUES(NULL,‘JULY‘,‘123‘,2*9*6-9,1),(DEFAULT,‘King‘,‘123‘,DEFAULT,0);Query OK, 2 rows affected (0.05 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 || 2 | JULY | 123 | 25 | 1 || 3 | Tom | 123 | 25 | 1 || 4 | JULY | 123 | 99 | 1 || 5 | Tom | 123 | 10 | 1 || 6 | JULY | 123 | 99 | 1 || 7 | King | 123 | 10 | 0 |+----+----------+----------+-----+------+7 rows in set (0.00 sec)md5是php的一個函數，用來計算字串的hash值mysql> INSERT INTO users VALUES(DEFAULT,‘Tom‘,md5(‘123‘),DEFAULT,1);Query OK, 1 row affected (0.06 sec)mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 25 | 1 || 2 | JULY | 123 | 25 | 1 || 3 | Tom | 123 | 25 | 1 || 4 | JULY | 123 | 99 | 1 || 5 | Tom | 123 | 10 | 1 || 6 | JULY | 123 | 99 | 1 || 7 | King | 123 | 10 | 0 || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 10 | 1 |+----+----------+----------------------------------+-----+------+8 rows in set (0.00 sec)

第二種：（如果看不懂這，先往下看，看完再回來看就懂了）

說明：與第一種方式的區別在於，此方法可以使用子查詢（SubQuery）。並且一次只能插入一條資料。INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...mysql> CREATE TABLE test(-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> username VARCHAR(20)-> );Query OK, 0 rows affected (0.18 sec)mysql> SELECT * FROM test;Empty 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 1mysql> INSERT test(username) SELECT username FROM users WHERE age>=30;Query OK, 2 rows affected (0.06 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+| 1 | JULY || 2 | JULY |+----+----------+2 rows in set (0.00 sec)

第三種：
說明：此方法可以將查詢結果插入到指定資料表。
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

2.更新記錄(有兩種方式)
第一種：單表更新

UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]mysql> UPDATE users SET age=age+5;Query OK, 9 rows affected (0.09 sec)Rows matched: 9 Changed: 9 Warnings: 0mysql> UPDATE users SET age=age+10,sex=0 WHERE id%2=0;Query OK, 4 rows affected (0.06 sec)Rows matched: 4 Changed: 4 Warnings: 0

第二種：多表更新

3.刪除記錄（有兩種方式）
第一種：單表刪除

DELETE FROM tbl_name [WHERE where_condition]DELETE FROM users WHERE id =6;

第二種：多表刪除

4.尋找記錄

SELECT select_expr[,select_expr...][FROM table_reference[WHERE where_condition][GROUP BY {col_name|position}{ASC|DESC}][HAVING where_condition][ORDER BY {col_name|expr|position}[ASC|DESC}]][LIMIT {[offset,]row_count|row_count OFFSET offset}]]SELECT 3+5;+-----+| 3+5 |+-----+| 8 |+-----+

 

查詢運算式
每一個運算式表示想要的一列，必須有至少一個。
多個列之間以英文逗號分隔。
星號（*）表示所有列。tbl_name.* 可以表示命名表的所有列。
查詢運算式可以使用[AS] alias_name 為其賦予別名。
別名可用於GROUP BY ,ORDER BY 或 HAVING 子句。
查詢所有

mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 29 | 0 || 2 | JULY | 123 | 38 | 0 || 3 | Tom | 123 | 27 | 0 || 4 | JULY | 123 | 110 | 0 || 5 | Tom | 123 | 10 | 0 || 7 | King | 123 | 8 | 0 || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 || 9 | Lucy | 123 | 6 | 0 || 10 | qqqqq | 123 | 10 | NULL |+----+----------+----------------------------------+-----+------+

按查詢次序排序

mysql> SELECT username,id from users;+----------+----+| username | id |+----------+----+| Tom | 1 || JULY | 2 || Tom | 3 || JULY | 4 || Tom | 5 || King | 7 || Tom | 8 || Lucy | 9 || qqqqq | 10 |+----------+----+

查詢指定表的欄位

mysql> SELECT users.id,users.username FROM users;+----+----------+| id | username |+----+----------+| 1 | Tom || 2 | JULY || 3 | Tom || 4 | JULY || 5 | Tom || 7 | King || 8 | Tom || 9 | Lucy || 10 | qqqqq |+----+----------+

‘AS‘的使用

mysql> SELECT id username FROM users;不標準+----------+| username |+----------+| 1 || 2 || 3 || 4 || 5 || 7 || 8 || 9 || 10 |+----------+9 rows in set (0.00 sec)mysql> SELECT id AS username FROM users;標準+----------+| username |+----------+| 1 || 2 || 3 || 4 || 5 || 7 || 8 || 9 || 10 |+----------+

4.1.條件運算式

對記錄進行過濾，如果沒有指定WHERE子句，則顯示所有記錄。
在WHERE運算式中，可以使用MYSQL支援的函數或運算子。

4.2.查詢結果分組
[GROUP BY {col_name|position} [ASC|DESC],...]
根據sex欄位分組

mysql> SELECT sex FROM users GROUP BY sex;+------+| sex |+------+| NULL || 0 |+------+2 rows in set (0.00 sec)

根據查詢的第一個欄位分組

mysql> SELECT sex FROM users GROUP BY 1;+------+| sex |+------+| NULL || 0 |+------+2 rows in set (0.00 sec)

根據查詢的第二個欄位分組

mysql> SELECT sex,username FROM users GROUP BY 2;+------+----------+| sex | username |+------+----------+| 0 | JULY || 0 | King || 0 | Lucy || NULL | qqqqq || 0 | Tom |+------+----------+5 rows in set (0.00 sec)

4.3分組條件

[HAVING where_condition]hava後用到的欄位要麼是彙總函式，要麼是出現在前面的欄位否則報錯。mysql> SELECT sex FROM users GROUP BY 1 HAVING age >5;ERROR 1054 (42S22): Unknown column ‘age‘ in ‘having clause‘mysql> SELECT sex,age FROM users GROUP BY 1,2 HAVING age >5;+------+-----+| sex | age |+------+-----+| NULL | 10 || 0 | 6 || 0 | 8 || 0 | 10 || 0 | 17 || 0 | 27 || 0 | 29 || 0 | 38 || 0 | 110 |+------+-----+9 rows in set (0.00 sec)mysql> SELECT sex,age FROM users GROUP BY 1 HAVING age >5;+------+-----+| sex | age |+------+-----+| NULL | 10 || 0 | 29 |+------+-----+2 rows in set (0.00 sec)mysql> SELECT sex,age FROM users GROUP BY 1 HAVING count(id) >2;+------+-----+| sex | age |+------+-----+| 0 | 29 |#四捨五入後的平均值+------+-----+

4.4對查詢結果進行排序
[ORDER BY {col_name|expr|position}[ASC|DESC],...]
預設按插入順序

mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 29 | 0 || 2 | JULY | 123 | 38 | 0 || 3 | Tom | 123 | 27 | 0 || 4 | JULY | 123 | 110 | 0 || 5 | Tom | 123 | 10 | 0 || 7 | King | 123 | 8 | 0 || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 || 9 | Lucy | 123 | 6 | 0 || 10 | qqqqq | 123 | 10 | NULL |+----+----------+----------------------------------+-----+------+9 rows in set (0.00 sec)

ORDER BY 預設是升序

mysql> SELECT * FROM users ORDER BY age;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 9 | Lucy | 123 | 6 | 0 || 7 | King | 123 | 8 | 0 || 5 | Tom | 123 | 10 | 0 || 10 | qqqqq | 123 | 10 | NULL || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 || 3 | Tom | 123 | 27 | 0 || 1 | Tom | 123 | 29 | 0 || 2 | JULY | 123 | 38 | 0 || 4 | JULY | 123 | 110 | 0 |+----+----------+----------------------------------+-----+------+9 rows in set (0.00 sec)

降序

mysql> SELECT * FROM users ORDER BY age DESC;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 4 | JULY | 123 | 110 | 0 || 2 | JULY | 123 | 38 | 0 || 1 | Tom | 123 | 29 | 0 || 3 | Tom | 123 | 27 | 0 || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 || 5 | Tom | 123 | 10 | 0 || 10 | qqqqq | 123 | 10 | NULL || 7 | King | 123 | 8 | 0 || 9 | Lucy | 123 | 6 | 0 |+----+----------+----------------------------------+-----+------+9 rows in set (0.00 sec)

4.5限制查詢結果返回的數量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 29 | 0 || 2 | JULY | 123 | 38 | 0 || 3 | Tom | 123 | 27 | 0 || 4 | JULY | 123 | 110 | 0 || 5 | Tom | 123 | 10 | 0 || 7 | King | 123 | 8 | 0 || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 || 9 | Lucy | 123 | 6 | 0 || 10 | qqqqq | 123 | 10 | NULL |+----+----------+----------------------------------+-----+------+9 rows in set (0.00 sec)

查詢兩條

mysql> SELECT * FROM users LIMIT 2;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 29 | 0 || 2 | JULY | 123 | 38 | 0 |+----+----------+----------+-----+------+2 rows in set (0.00 sec)

查詢從下標3開始，查兩條。（MYSQL下標從0開始）

mysql> SELECT * FROM users LIMIT 3,2;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 4 | JULY | 123 | 110 | 0 || 5 | Tom | 123 | 10 | 0 |+----+----------+----------+-----+------+2 rows in set (0.00 sec)

查詢從下標2開始，查兩條。（MYSQL下標從0開始）

mysql> SELECT * FROM users LIMIT 2,2;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 3 | Tom | 123 | 27 | 0 || 4 | JULY | 123 | 110 | 0 |+----+----------+----------+-----+------+2 rows in set (0.00 sec)

查詢降序後從下標2開始，查兩條。

mysql> SELECT * FROM users ORDER BY age DESC;+----+----------+----------------------------------+-----+------+| id | username | password | age | sex |+----+----------+----------------------------------+-----+------+| 4 | JULY | 123 | 110 | 0 || 2 | JULY | 123 | 38 | 0 || 1 | Tom | 123 | 29 | 0 || 3 | Tom | 123 | 27 | 0 || 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 || 5 | Tom | 123 | 10 | 0 || 10 | qqqqq | 123 | 10 | NULL || 7 | King | 123 | 8 | 0 || 9 | Lucy | 123 | 6 | 0 |+----+----------+----------------------------------+-----+------+mysql> SELECT * FROM users ORDER BY age DESC LIMIT 2,2;+----+----------+----------+-----+------+| id | username | password | age | sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 29 | 0 || 3 | Tom | 123 | 27 | 0 |+----+----------+----------+-----+------+

　　

 

MySql,筆記（記錄操作）