B7EB7999D6F25608EC63A319D0EE9876.jpg

拿去用,不謝!拖走!拖走!

操作數(shù)據(jù)庫(kù)中的表

首先是數(shù)據(jù)的插入對(duì)就是插入,INSERT

users表結(jié)構(gòu).jpeg

  INSERT [INTO] table_name [(colume_name,...)] {VALUES | VALUES} ({expr | DEFAULT},....),(...),...

for example:

INSERT INTO users (username,id) VALUES('lalala',3+1) ,(md5('lalala'),3+2)
INSERT INTO users VALUES (NULL,'張三','男',2,md5('123456'))

• colume_name 是列
  名稱(chēng)就是字段名稱(chēng) 如果省略掉 就是一次對(duì)所有的列(字段)賦值
• id是主鍵自增長(zhǎng) 可以 賦值成NULL 或者 DEFAULT

INSERT [INTO] table_name SET column_name = {expr | DEFAULT},...

for example:

INSERT INTO users SET username = '李四', pwd = '123456',sex = '女'

注意:與上一種方式的區(qū)別在于,此方法可以使用子查詢(xún)(subQuery),這種方法只能一次性插入一條記錄

INSERT [INTO] table_name [column_name,...] SELECT ...

for example:

INSERT INTO users (username,pwd,sex) SELECT username,password,sex FROM phps WHERE id = 2 

更新記錄(單表更新)

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET column_name = {exper1 | DEFAULT} [, column_name2 = {expr2 | DEFAULT}] ... [WHERE wherre_condition]

for example:

UPDATE users SET pwd = md5('12344') WHERE id % 2 = 0

刪除記錄(單表刪除)

DELETE FROM table_name  [WHERE wherre_condition]

for example:

DELETE FROM users  WHERE id % 2 = 0

注意:如果不加where條件的約束 會(huì)對(duì)整個(gè)表的數(shù)據(jù)進(jìn)行操作

查找記錄SELECT 很重要 要記住 比插入要重要 要記住 不要只記得插入哦??

SELECT select_expr [,select_expr ...] 
[
FROM table_references 
[WHERE where_condition]
[GROUP BY {column_name | position} [ASC | DESC],...]
[HAvING where_condition]
[ORDER BY {column_name | expr | position}  [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]

注意: 每一個(gè)表達(dá)式表示想要的一列,必須至少有一個(gè).多個(gè)列之間以英文逗號(hào)分隔,表示多有列.table_name.表示命名表的所有列.查詢(xún)表達(dá)式可以是有[AS] alias_name 為其賦予別名.別名可用于GROUP BY ,ORDER BY 或者 HAVING子句中.

for example:

SELECT username AS uname, id AS userId FROM users
SELECT users.username , users.id FROM users

WHERE 條件表達(dá)式

注意:對(duì)記錄進(jìn)行過(guò)濾,如果沒(méi)有指定WHERE子句,則顯示所有記錄.在WHERE表達(dá)式中,可以使用MySQL支持的函數(shù)或運(yùn)算符.

GROUP BY 查詢(xún)結(jié)果分組

 [GROUP BY {column_name | positon} [ASC | DESC],...]

for example:

SELECT id, username,sex FROM users GROUP BY sex

HAVING 分組條件

[HAVING where_condition]

for example:

SELECT id, username,sex FROM users GROUP BY sex HAVING id > 2

ORDER BY 對(duì)查詢(xún)結(jié)果進(jìn)行排序

[ORDER BY {column_name | expr | position} [ASC | DESC],...]

for example:

SELECT * FROM users ORDER BY pid, id DESC 
SELECT * FROM users ORDER BY id DESC 

LIMIT限制查詢(xún)結(jié)果返回的數(shù)量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

for example:

SELECT * FROM users LIMIT (0,20)