拿去用,不谢!拖走!拖走!
操作数据库中的表
####首先是数据的插入对就是插入,INSERT
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 是列 名称就是字段名称 如果省略掉 就是一次对所有的列(字段)赋值
-
id是主键自增长 可以 赋值成NULL 或者 DEFAULT
INSERT [INTO] table_name SET column_name = {expr | DEFAULT},... for example:
INSERT INTO users SET username = '李四', pwd = '123456',sex = '女'
######注意:与上一种方式的区别在于,此方法可以使用子查询(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条件的约束 会对整个表的数据进行操作
###查找记录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}]
]
复制代码
注意: 每一个表达式表示想要的一列,必须至少有一个.多个列之间以英文逗号分隔,*表示多有列.table_name.*表示命名表的所有列.查询表达式可以是有[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 条件表达式
注意:对记录进行过滤,如果没有指定WHERE子句,则显示所有记录.在WHERE表达式中,可以使用MySQL支持的函数或运算符.
###GROUP BY 查询结果分组 [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 对查询结果进行排序 [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限制查询结果返回的数量 [LIMIT {[offset,] row_count | row_count OFFSET offset}]
for example:
SELECT * FROM users LIMIT (0,20)
复制代码