学习笔记 mysql_Mysql学习笔记

1. 使用数据库

使用数据库

USE database_name

展示所有数据库

SHOW databases;

展示所有表

SHOW tables;

2. 检索数据

检索列

''单列''

SELECT tbname FROM dbname;

''多列''

SELECT tbname1,tbname2 FROM dbname;

''所有列''

SELECT * FROM dbname

'''DISTINCT关键词 检索不相同的列'''

SELECT DINSTINCT tbname FROM dbname;

''LIMIT关键词''

SELECT tbname FROM dbname LIMIT size;

排序

''ORDER BY子句''

SELECT tbname FROM dbname ORDER BY tbname;

''降序''

SELECT tbname FROM dbname ORDER BY tbname DESC;

过滤数据

''WHERE子句''

SELECT tbname FROM dbname WHERE (CONDITION);

"BETWEEN"

SELECT tbname FROM dbname WHERE BETWEEN A AND B;

"判断NULL"

SELECT tbname FROM dbname WHERE IS NULL;

"IN操作符"

SELECT tbname FROM dbname WHERE IN (CONDITION);

"NOT操作符"

SELECT tbname FROM dbname WHERE NOT (CONDITION);

"通配符过滤"

" %任意字符 _单个字符"

SELECT tbname FROM daname WHERE tbname LIKE (CONDITION);

WHERE子句判断

正则表达式过滤

pass

计算字段

pass

3. 数据处理函数

文本处理函数

SELECT tbname, Upper(tbname) AS upcase FROM dbname;

常用文本处理函数

时间/日期处理函数

"判断2001年2月1日的日期"

SELECT tbname FROM dbname WHERE Date(tbdatename) = '2001-02-01';

常用时间日期处理函数

数值处理函数

数值处理函数

聚集函数(支持函数内多个列使用，可以组合使用聚集函数)

聚集函数

"AVG()"

"AVG()函数只用于一个列，且列名必须用函数表名"

SELECT AVG(tbname) AS avg_tbname FROM dbname (WHERE CONDITIDON)

"CONUNT()"

SELECT COUNT(tbname) AS cnt_tbname FROM dbname (WHERE CONDITIDON)

"MAX()"

SELECT MAX(tbname) AS max_tbname FROM dbname (WHERE CONDITIDON)

##### 5. 分组数据

"GROUP BY子句"

SELECT tbname1, tbname2 FROM dbname

GROUP BY tbname1;

"HAVING 子句''

SELECT tbname1, tbname2 FROM dbname

GROUP BY tbname1

HAVING (CONDITION);


##### 6. 子查询

没看懂

##### 7. 联结

- 等值联结(内部联结)

"1"

SELECT tb1name, tb2name, tb2name

FROM db1name, db2name

WHERE db1name.id = db2name.id;

"2"

SELECT tb1name, tb2name, tb2name

FROM db1name INNER JOIN db2name

ON db1name.id = db2name.id;

- 高级联结

没看懂

##### 8. 组合查询

- UNION

SELECT

UNION

SELECT;


##### 9. 全文本搜索

pass

##### 10. 插入数据

- 插入一行

INSERT INTO tbname(field1,field2

)VALUES(value1,value2);

- 插入检索出来的数据

INSERT INTO tbname1(field1,field2)

SELECT field1,field2 FROM tbname2;

##### 11. 更新和删除数据

- 更新

UPDATE tbname

SET field1 = '',

field2 = "

WHERE (CONDITION);

- 删除

DELECT tbname

WHERE (CONDITION);

##### 12. 创建和操作表

ALTER TABLE tbname (CONDITION);