MySQL之实用篇

MySQL之牛刀小试 MySQL之进化篇

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

操作数据库中的表

####首先是数据的插入对就是插入,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)
复制代码

转载于:https://juejin.im/post/5a31ea4f51882503eb4b4aab