MySQL数据库案例实战教程:数据类型、语法与高级查询详解

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库案例实战教程:数据类型、语法与高级查询详解

MySQL 案例实战教程

MySQL的数据类型

  • MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
  • 备注: char 和varchar 一定要指定长度,float 会自动提升为double,timestamp 是时间的混合类型,理论上可以存储 时间格式和时间戳。

Mysql语法

建表

约束

  • NOT NULL:规定某一列不能存储null值
  • UNIQUE:保证某列的每行都有唯一的值
  • PRIMARY KEY:NOT NULL和UNIQUE的组合
  • FOREIGN KEY(尽量少用,不好维护):保证一个表中的数据匹配另一个表中的值的参照完全性
  • CHECK:保证列中的值符合条件
  • DEFAULT:规定没有列赋值时的默认值

实例

--建表
 
create table 表名(
 
    字段名 类型 约束(主键,非空,唯一,默认值),
 
    字段名 类型 约束(主键,非空,唯一,默认值),
 
)编码,存储引擎

插入

INSERT INTO websites(name,url,alexa,sal,country) VALUES("腾讯",'https://www.qq.com',18,1000,'CN');

删除

DELETE FROM websites WHERE id = 2;

现在我们删除di为2的行

更新

UPDATE websites SET sal = 2000 WHERE id = 3;

更新id为3的行sal为2000

注释

-- 这是注释
 
/*
sql sentence
*/

查询

DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites`(
    id int(11) NOT NULL AUTO_INCREMENT,
    name char(20) NOT NULL DEFAULT'' COMMENT'站点名称',
    url varchar(255) NOT NULL DEFAULT '',
    alexa int(11) NOT NULL DEFAULT '0' COMMENT'Alexa排名',
    sal double COMMENT'广告收入',
    country char(10) NOT NULL DEFAULT'' COMMENT'国家',
    PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
 
 
INSERT INTO `websites` VALUES
    (1,'Google', 'https://www.google.com/', '1',2000 , 'USA '),
    (2,'淘宝','https://www.taobao.com/','13',2050,'CN'),
    (3,'菜鸟教程', 'http://www.runoob.com/', '4689' ,0.0001,'CN'),
    (4,'微博', 'http://weibo.com/','20',50,'CN'),
    (5,'Facebook','https://www.facebook.com/','3',500, 'USA');
CREATE TABLE IF NOT EXISTS `access_log`(
    `aid` int(11) NOT NULL AUTO_INCREMENT,
    `site_id` int(11) NOT NULL DEFAULT '0' COMMENT '网站id',
    `count` int(11) NOT NULL DEFAULT '0' COMMENT '访问次数',
    `date` date NOT NULL,
    PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `access_log` (`aid`,`site_id`,
`count`,`date`) VALUES
(1,1,45,'2016-05-10'),
(2,3,100,'2016-05-13'),
(3,1,230,'2016-05-14'),
(4,2,10,'2016-05-14'),
(5,5,205,'2016-05-14'),
(6,4,13,'2016-05-15'),
(7,3,220,'2016-05-15'),
(8,5,545,'2016-05-16'),
(9,3,201,'2016-05-17'),
(10,88,9999,'2016-09-09');

运行成功,查看websites表

查询语句

select * from websites
 
select id,name,url,alexa from websites

实际开发中尽量不要使用 *

原因:

在实际开发中尽量不要使用*通配符查询语句的原因有几个方面:

1. 性能问题:使用*通配符会导致数据库引擎进行全表扫描,这会带来性能上的损耗,特别是当数据量非常大的时候。相比之下,指定具体的列名可以让数据库引擎更有效地执行查询,提高查询效率。

2. 查询结果的不确定性:使用*通配符可能会返回多个不必要的列,包括一些不需要的敏感信息或者关联表中的数据,增加了数据传输的开销,并且也增加了处理结果集的复杂度。

3. 维护性和可读性:明确指定需要查询的列名可以使查询语句更易于维护和理解,尤其是在团队协作或者未来需要对查询进行修改和优化的情况下。

因此,尽量避免使用*通配符,而是明确指定需要查询的列名,可以提高查询的性能、确定性和可维护性。

分页查询

 
select id,name,url,alexa from websites limit 2
-- 从第一条开始查,查到第二条
 
select id,name,url,alexa from websites limit 2,3
-- 从第二条开始查,查三条

distinct去重

select distinct country from websites

查询结果去除表中重复重复国家的数据项

where语句

运算符> < >= <= <> != = is null is not null like in

在sql语句中 null值和任何东西比较 都为false,包括null

SELECT * FROM websites WHERE sal IS NOT NULL

现在我们把id为1的行sal置为NULl,再次查询

逻辑条件:and or

select * from websites where sal<=0 and sal>=2000;
select * from websites where sal between 2000 and 0;/*和上面效果一样*/
select * from websites where sal<5 or sal is null;

排序 order by

select * from websites order by sal asc,alexa desc;-- 先按sal升序,再按alexa降序

注意:分组时候的筛选用 having
常见的几个组函数:max()min()avg()count()sum()

在 SQL 中,ASC 和 DESC 用于指定排序顺序:

- ASC 表示升序(从小到大),是默认的排序顺序,如果不指定排序顺序,默认为 ASC。

- DESC 表示降序(从大到小),使用 DESC 关键词可以让查询结果按指定列以降序排列。

`order by sal asc` 表示按照 sal 列的升序排列,而 `alexa desc` 表示按照 alexa 列的降序排列。

like 通配符

select * from websites where name like '%O%'
select * from websites where name like '_O%'-- 匹配一个字符

in 匹配多个条件

select * from websites where country in ('USA','XXX','CN');
-- 等价于
select * from websites where country = 'USA' OR country = 'CN' OR country = 'XXX';

别名

select tt.name '网站名字' from websites tt -- tt是表的别名

group by 分组查询

select avg(sal) '平均值' ,country from websites group by country;
 
 
select avg(sal) '平均值' ,country from websites group by country HAVING 平均值 > 1200;-- 不能用where,只能用having;

子查询

把查询结果当做一个表来使用

子查询是指在 SQL 查询中嵌套使用的查询,将内部查询的结果作为外部查询的数据源之一。通过子查询,可以将查询结果当做一个表来使用,从而进行更复杂的数据操作和筛选。

例如,下面是一个简单的示例,演示了如何使用子查询:

SELECT name, age 
FROM students 
WHERE age > (SELECT AVG(age) FROM students);

在这个示例中,(SELECT AVG(age) FROM students) 就是一个子查询,它计算了学生年龄的平均值,并将该值作为外部查询条件进行筛选。子查询的结果就好像是一个临时的虚拟表,可以被外部查询引用和操作。

通过子查询,我们可以实现更灵活、更精细的数据查询和操作,从而满足复杂的业务需求。不过需要注意的是,过度复杂的子查询可能会影响查询性能和可读性,因此在使用子查询时需要权衡其优劣并谨慎设计。

连接查询

左外连接(Left Outer Join)、全连接(Full Outer Join)和右外连接(Right Outer Join)是 SQL 中用于合并表的不同类型的连接操作。

1. 左外连接(Left Outer Join):左外连接会返回左表中所有的行,并且和右表中满足连接条件的行进行连接,如果右表中没有匹配的行,则会用 NULL 值填充。左表是指在 JOIN 关键字之前的表,而右表是指在 JOIN 关键字之后的表。

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

2. 全连接(Full Outer Join):全连接会返回左表和右表中的所有行,并且对于没有匹配的行会用 NULL 值填充。

 

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
 

3. 右外连接(Right Outer Join):右外连接会返回右表中所有的行,并且和左表中满足连接条件的行进行连接,如果左表中没有匹配的行,则会用 NULL 值填充。

SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;

这些连接操作允许在 SQL 查询中根据特定的条件将两个表中的行进行关联,从而实现数据的联合查询和合并。左外连接、全连接和右外连接提供了灵活的方式来处理不同表之间的关系,使得我们可以根据具体的需求进行数据的组合和筛选。

查询每个网站的每天的访问量,显示出:名称访问量日期

/*过时写法*/
/*不加where的话会随意乱连*/
/*内连接*/
select name,a.count,a.date from websites w,access_log a where w.id=a.site_id
 
/*outer可以省*/
/*左外连接*/
select * from websites w left outer join access_log a
on w.id=a.site_id
union/*实现全连接*/
select * from websites w right join access_log a
on w.id=a.site_id

ifnull

select name,ifnull(count,0),ifnull(a.date,'无日期') from websites w left outer join access_log a
on w.id=a.site_id

这条 SQL 查询语句使用了左外连接(Left Outer Join),将表 websites(别名为 w)和 access_log(别名为 a)进行连接,并使用了 ifnull 函数来处理可能出现的 NULL 值。

这条查询的作用是获取网站名称、访问次数以及最近访问日期(如果有的话)。具体解释如下:

  • `select name, ifnull(count,0), ifnull(a.date,'无日期')`: 从左表 websites 中选择 name 列,并从右表 access_log 中选择 count 和 date 列,使用 ifnull 函数来处理可能的 NULL 值。
  • `from websites w left outer join access_log a on w.id=a.site_id`: 这部分指定了左外连接的逻辑,即将表 websites 和 access_log 根据 site_id 列进行连接,左表为 w,右表为 a。

左外连接将返回左表 websites 中的所有行,同时匹配右表 access_log 中的行。如果某个网站在 access_log 中没有对应的访问记录,那么 count 和 date 列就会包含 NULL 值。ifnull 函数用于将可能的 NULL 值替换为指定的默认值(0 或 '无日期')。

总的来说,这条查询用于获取网站的访问次数以及最近访问日期(如果有的话),并且对可能的 NULL 值进行了处理。

希望对你有帮助!加油!

若您认为本文内容有益,请不吝赐予赞同并订阅,以便持续接收有价值的信息。衷心感谢您的关注和支持!

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
9天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
10天前
|
SQL 数据库 Python
Django框架数据库ORM查询操作(6)
【7月更文挑战第6天】```markdown Django ORM常用数据库操作:1) 查询所有数据2) 根据ID查询 3) 精确查询 4) 分页排序
16 1
|
14天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
131 1
|
3天前
|
关系型数据库 MySQL 数据挖掘
MySQL 聚合函数案例解析:深入实践与应用
MySQL 聚合函数案例解析:深入实践与应用
|
3天前
|
SQL 关系型数据库 MySQL
MySQL 聚合函数深入讲解与实战演练
MySQL 聚合函数深入讲解与实战演练
|
12天前
|
SQL 存储 数据库
MSSQL数据库性能调优实战:索引、查询与并发控制的深度剖析
在数据库管理领域,Microsoft SQL Server(MSSQL)的性能调优是保障业务高效运行的核心任务
|
13天前
|
关系型数据库 MySQL Linux
Linux部署实战前言,MySQL在CentOS安装【单机软件】,MySQL的安装需要root权限,yum install mysql,systemctl enable mysqld开机自启的意思
Linux部署实战前言,MySQL在CentOS安装【单机软件】,MySQL的安装需要root权限,yum install mysql,systemctl enable mysqld开机自启的意思
|
14天前
|
SQL 关系型数据库 MySQL
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
|
14天前
|
小程序 数据库
【微信小程序-原生开发】实用教程15 - 列表的排序、搜索(含云数据库常用查询条件的使用方法,t-search 组件的使用)
【微信小程序-原生开发】实用教程15 - 列表的排序、搜索(含云数据库常用查询条件的使用方法,t-search 组件的使用)
12 0
|
13天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中