1. 查询技巧
1.1 逻辑操作符
主要是对 and or in not的结合使用
select * from tb where field <2 and field2 >3;
1.2去重
使用关键字distinct,放在所有查询字段的前面,表示后面的查询字段组合不能重复
SELECT DISTINCT stuname,sex,class FROM stu_info;
1.3 设置结果查询行数
使用limit语句,limit m,n
limit m前m行;
limit m,n从第m+1行开始,查询n行;
select * from tb_name where xxx limit m,n
1.4查询结果排序
使用ORDER BY field_name ASC/DESC # 升序/降序
1.5以上四条总结
SELECT * FROM tb_name WHERE xxx ORDER BY field_name ASC/DESC LIMIT m,n;
1.6 列别名及计算
- 有时为了便于区别不同表中相同字段或者减少书写量,可以给字段取一个别名:
field_name as alias或者field_name alias; - 有些数据需要计算,可以直接对查询出的字段进行+ - * / 运算。
2.MySQL 函数
MySQL支持利用函数来处理数据,函数在数据上执行,它给数据的转换和处理提供了方便。MySQL支持的函数有:
- 文本处理函数
- 日期和时间处理函数
- 数值处理函数
- 系统函数
2.1 文本处理函数
-
concat(field1,field2)返回字符串拼接后的结果 -
length()返回字符串的长度 -
lower()小写 -
upper()大写 -
trim()去掉左右两边的空格 -
triml()去掉左边的空格 -
trimr()去掉右边的空格 -
substring(field,m,n)从m位置开始取n个
2.2 时间处理函数
curdate()当前日期
curtime()当前时间
now()当前日期及时间
date(日期值) 返回日期部分
time(日期值)返回时间部分
year(日期值)返回年
month(日期值)返回月
day(日期值)返回日
DATEDIFF('2021-06-29','2008-12-30')返回两个日期之间的天数
DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%S') 指定格式显示
DATE_ADD(date,INTERVAL expr type)向日期添加指定的时间间隔
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔
date是合法的日期表达式
INTERVAL 是关键字
expr 时间间隔
type 参考下图


2.3数值处理函数
round(x,y) x四舍五入的y为小数的值
truncate(x,y) x截断为y位小数的值
abs(x)x的绝对值
rand() 0~1内的随机值
2.4系统函数
version()返回数据库版本号
database()当前连接的数据库
user()当前登录的用户
2.5 聚合函数
聚合函数运算在列上,计算和返回单个值的函数,有:
sum()求和
count()个数
max()最大值
min()最小值
avg()平均值
3.分组查询及子查询
3.1 分组查询
分组查询使用group by语句,根据一个或多个列队结果集进行分组。在分组的裂伤可以使用聚合函数。
SELECT field_name,function(field_name) FROM tb_name WHERE field_name operator value GROUP BY field_name HAVING xxx ORDER BY field_name;
注意上述代码顺序:WHERE xxx GROUP BY yyy ORDER BY zzz
image.png
- 分组采用
GROUP BY,过滤是HAVING; WHERE field_nama operator value GROUP BY xxx HAVING gather_function(field) ORDER BY field_name;-
HAVING一般与聚合函数连用。
3.2 子查询
嵌套在其他查询中的查询,即:一个查询语句(select - from tb_name - where)块可以嵌套在另外一个查询块的where子句中。
SELECT m,a.n FROM (SELECT sex AS n,address m FROM sys_user) AS a;SELECT * FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE DATE(order_date)='2021-05-16');# 查询2021年5月16日的下订单的用户信息
4.关系表
- 假设有一个包含产品目录的数据库表,其中每种类别的产品占一行。对于每种物品要存储的信息包括产品描述和价格,以及生成该产品的供应商信息。
- 现在,假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如:供应商名、地址、联系方式等)呢?将这些数据与产品信息分开存储还是存储在同一张表里呢?通常的原则是分开存储,理由:
- 节约存储空间,因为同一供应商生产的每个产品的供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空;
- 便于数据更正,如果供应商信息改变(如:供应商搬家或电话号码变动,只需要改动一次即可)
- 保证数据唯一,如果有重复数据(即每种产品都存储供应商信息),很难保证每次输入该数据的方式都相同,不一致的数据在报表中很难利用。
- 在这个例子中,可建立两个表,一个存储供应商信息(vendors表),另一个存储产品信息(products表)。关系数据可以有效地存储和方便地处理。因此,关系数据库的可伸缩性远比非关系数据库要好。
5.表连接
5.1 内连接
只有当连接的两个或多个表中都存在满足条件的记录时,才返回行。JOIN 或者 INNER JOIN

5.2 左连接
返回左表(table1)中的所有记录,当右表中没有匹配记录时,左连接任然返回一行,只是该行左表字段有值,右表字段以NULL填充。
SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2;

5.3 右连接
返回右表(table1)中的所有记录,当左表中没有匹配记录时,右连接任然返回一行,只是该行右表字段有值,左表字段以NULL填充。
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column1 = table2.column2

5.5 全连接
Mysql不支持,可以使用UNION将左连接和右连接组合起来。
5.6 交叉连接
也称为笛卡尔连接,将左表的每一行与右表的每一行合并
SELECT * FROM table1 CROSS JOIN table2
5.6 自连接
用于将一个表和自身连接,就像存在两个表一样。通常用于将表的某个字段与该表的同一字段的其他值进行比较。
SELECT * FROM table1 AS a, table1 AS b WHERE a.common_column < b.common_column;
6.视图
视图是基于SQL语句的结果集的可视化的表。包含行和列,就像一个真实的表。
- 创建视图
CREATE VIEW view_name AS SELECT * FROM tb_name WHERE condition;create view zhang as select * from students INNER JOIN courses ON students.sid = courses.ssid;create view wang as select name, age from students INNER JOIN courses ON students.sid = courses.ssid;
- 删除视图
DROP VIEW view_name;
7.存储过程
一组为了完成特定功能的SQL语句集,用户可以通过调用过程来实现相应的操作。好处是简化了复杂的操作。
- 创建存储过程
DELIMITER $$
CREATE PROCEDURE proc_zhang()
BEGIN
CREATE VIEW view_zhang AS SELECT name, age FROM students;
SELECT * FROM view_zhang;
END $$
DELIMITER ;
- 调用存储过程
CALL proc_zhang();
- 删除存储过程
DROP PROCEDURE procdure_name;
- 查看所有存储过程
SHOW PROCEDURES STATUS
8. 索引
索引增加提升查询效率,降低增删改速度。
- 普通索引:最基本的索引,没有任何限制,创建命令:
create index pro_index on product(name);
- 唯一索引:索引列的值必须唯一,允许空值
create unique index pro_index on product(amount);
- 主键索引
SHOW INDEX FROM product;#查看product表的索引
- 删除索引
DROP INDEX prod_index ON product;
- 索引规则:
- 表的主键、外键必须有索引;
- 数据量大的表应有索引;
- 经常与其他表连接的表,应在连接字段上建立索引;
- 经常出现在where语句中的自动,应该建立索引。
8.数据导入及导出
8.1 导出
- 导出数据库
mysqldump -uuser_name -ppasswprd database_name > /path/database.sql
- 导出数据表
mysqldump -u root -p crashcourse customers vendors >/home/charles/customersback.sql
8.2 导入
- 检查是否需要新建表库或表
- 建立库及表
CREATE DATABASE db_name - 使用库
USE db_name; - 导入
source your_sql_file.sql;
