MySQL数据库二

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 参考下图

image.png
image.png

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.关系表

  1. 假设有一个包含产品目录的数据库表,其中每种类别的产品占一行。对于每种物品要存储的信息包括产品描述和价格,以及生成该产品的供应商信息。
  2. 现在,假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如:供应商名、地址、联系方式等)呢?将这些数据与产品信息分开存储还是存储在同一张表里呢?通常的原则是分开存储,理由:
    • 节约存储空间,因为同一供应商生产的每个产品的供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空;
    • 便于数据更正,如果供应商信息改变(如:供应商搬家或电话号码变动,只需要改动一次即可)
    • 保证数据唯一,如果有重复数据(即每种产品都存储供应商信息),很难保证每次输入该数据的方式都相同,不一致的数据在报表中很难利用。
  3. 在这个例子中,可建立两个表,一个存储供应商信息(vendors表),另一个存储产品信息(products表)。关系数据可以有效地存储和方便地处理。因此,关系数据库的可伸缩性远比非关系数据库要好。

5.表连接

5.1 内连接

只有当连接的两个或多个表中都存在满足条件的记录时,才返回行。JOIN 或者 INNER JOIN

内连接.png

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;

  • 索引规则:
    1. 表的主键、外键必须有索引;
    2. 数据量大的表应有索引;
    3. 经常与其他表连接的表,应在连接字段上建立索引;
    4. 经常出现在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;

9.参考资料

MySQL文档
MySQL官网
MySQL 教程|菜鸟教程

最后编辑于 :
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成,浏览时请结合常识与多方信息审慎甄别。
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

相关阅读更多精彩内容

  • 1 编写目的 本手册是为指导开发人员按照文档中的规范进行MYSQL数据库设计及SQL编码。 2 数据库对象定义规范...
    Graddy阅读 2,451评论 0赞 74
  • 一、django ORM简介 O(objects):类和对象。R(Relation):关系,关系数据库中的表格。M...
    汪菲宇阅读 2,189评论 0赞 1
  • 一、数据库系统 数据库DataBase【DB】,指的是长期保存到计算机上的数据,按照一定顺序组织,可以被各种用户或...
    fly5阅读 484评论 0赞 0
  • Mysql数据库基础 添加用户 土办法 tip:在 MySQL5.7 中 user 表的 password 已换成...
    好奇的小菜狗阅读 557评论 0赞 2
  • 什么是数据库? 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库, 每个数据库都有一个或多个不...
    Jason_c8d4阅读 554评论 1赞 0

友情链接更多精彩内容