1.1什么是数据库 数据库:保存数据的仓库。它体现我们电脑中,就是一个文件系统。然后把数据都保存这些特殊的文件中,并且需要使用固定的语言(SQL语言)去操作文件中的数据。 技术定义: 数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设 备上的仓库。 1.2数据库介绍 我们开发应用程序的时候,程序中的所有数据,最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库。 我们学习数据库,并不是学习如何去开发一个数据库软件,我们学习的是如何使用数据 库以及数据库中的数据记录的操作。而数据库软件是由第三方公司研发。 1.3数据库的分类 关系型、非关系型的数据库常见的数据库软件: Oracle:它是Oracle公司的大型关系型数据库,它是收费的。 DB2:IBM公司的数据库,它是收费的。SqlServer:微软数据库。收费 Sybase:Sybase公司的。 工具PowerDesign 数据库建模工具。 MySql:早期瑞典一个公司发明,后期被sun公司收购,后期被Oracle。Java开发应用程序主要使用的数据库: MySQL(5.5)、Oracle、DB2。 1.4什么是关系型数据库 在开发软件的时候,软件中的数据之间必然会有一定的关系存在,需要把这些数据保存在数据库中,同时也要维护数据之间的关系,这时就可以直接使用上述的那些数据 库。而上述的所有数据库都属于关系型数据库。 描述数据之间的关系,并保存在数据库中,同时学习如果根据这些关系查询数据库中的数据, 关系型数据:设计数据库的时候,需要使用E-R图来描述。实体关系 E-R:实体关系图。 实体:可以理解成我们Java程序中的一个对象。在E-R图中使用 矩形(长方形) 表示。针对一个实体中的属性,我们称为这个实体的数据,在E-R图中使用 椭圆表示。 实体和实体之间的关系:在E-R图中使用菱形表示。 2.1、vmware中安装linux注意事项 2.1.1、记得关闭防火墙 2.1.2、创建统一的管理目录 2.1.3软件环境 2.1.4安装环境 2.2、centos6.9安装mysql 2.2.1、检查是否有自带的mysql 2.2.2、卸载自带的mysql 2.2.3、下载mysql安装包 2.2.4、上传安装包到linux服务器 2.2.5、安装 2.2.6、查看初始化密码 2.2.7、启动mysql并登录 2.2.8、修改密码 2.2.9、退出mysql客户端 2.2.10、用新密码进行登录 2.2.11、远程授权 2.2.12、验证远程授权是否成功 3.1、登录mysql 3.2、退出mysql 3.3、输入查询 查看当前mysql的版本号及当前时间 mysql中sql语句不区分大小写 可以进行简单的计算(如下所示) 多条语句比较短,可以写在一行 多个字段之间可以用逗号分隔,多行组成一条语句结束以分号结束 sql语句写了一半,又不想执行可以在语句末尾加上’\c’ 查看当前有哪些数据库 创建数据库 使用及切换数据库 3.5、创建表及使用 查看当前数据库有哪些表 创建一个表 校验创建表语句是否和执行的一致 查看表详情 准备数据 3.6、表中导入数据 在表中导入数据的方式有两种 第一种:将以上数据整理成SQL语句,insert into pet… 第二种:通过加载文件的方式将数据导入到表中 1、创建一个pet.txt的文件(注:每个字段中用tab键隔开,字段没有值得记录用\N 代替) 2、加载数据 3、校验是否加载进去 3.7、数据检索部分 3.7.1、检索全部数据 3.7.2、删除表中全部数据 3.7.3、更新表中特定记录的数据 更新表中名字为Bowser的生日 3.7.4、查询特定的行 查询名字为Bowser的记录 说明:字符串比较不区分大小写!如下所示: mysql> SELECT * FROM pet WHERE name = 'Bowser'; +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | name| owner | species | sex| birth| death| +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | Bowser | Diane | dog| m| 1979‐08‐31 | 1995‐07‐29 | +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ 1 row in set (0.00 sec) mysql> SELECT * FROM pet WHERE name = 'BowsEr'; +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | name| owner | species | sex| birth| death| +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | Bowser | Diane | dog| m| 1979‐08‐31 | 1995‐07‐29 | +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ 1 row in set (0.00 sec) mysql> SELECT * FROM pet WHERE name = 'BOWSER'; +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | name| owner | species | sex| birth| death| +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | Bowser | Diane | dog| m| 1979‐08‐31 | 1995‐07‐29 | +‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ 1 row in set (0.00 sec) 3.7.4.1、查找生日在1998年以后的特定查询 3.7.4.2、多条件查询(and | or) 优先执行括号中的逻辑 查询不重复的字段要使用关键词DISTINCT 可以使用组合条件查询特定的列 3.7.5、排序 根据某个字段进行排序(关键词:ORDER BY ) 升降序排列(desc:降序;asc:升序) 多列排序 根据species字段升序排列,根据birth字段降序排列 注: ORDER BY species 中无asc,desc,默认为升序排列 3.7.6、日期计算 查看宠物多少岁,就可以使用计算日期的函数TIMESTAMPDIFF() 3.7.7、null和not null值 对一些字段类型要进行检查,判断某些字段是否为NULL,或者 non-NULL 以下是如何解决MySQL的一些常见问题的示例。首先创建一个表,并且导入数据 1、检索表中的全部数据 2、求某一列的最大值或者 最小值 3.1、过滤出某个字段值最大的整条记录数据-涉及到子查询 3.2、也可以通过关联查询来进行检索 4、求出每一列的最大值,并且根据某一个字段进行分组–分组topn求法 5、1、数据库介绍篇
2、mysql在linux-安装篇
service iptables stop
chkconfig iptables off(关闭开机自启:所谓的永久关闭防火墙)
mkdir ‐p /exprot/software
mkdir ‐p /export/servers
VMware、crt、centos6.9
1、VMware软件安装
2、构建虚拟机
3、需要配置Linux(ip,mac地址,hostname,防火墙),就可以通过crt这个客户
端连接进行操作
4、在linux操作系统进行安装msyql‐5.6
说明:因为在linux操作系统中,安装软件的方式主要有3种:1、源码安装
(redis)2、rpm安装
3、yum在线安装(安装MySQL为例)‐‐‐linux联网()
[root@hadoop‐01 servers]# rpm ‐qa |grep mysql
mysql‐libs‐5.1.73‐8.el6_8.x86_64
[root@hadoop‐01 servers]# rpm ‐e ‐‐nodeps mysql‐libs‐5.1.73‐8.el6_8.x86_64
[root@hadoop‐01 servers]#
rz 上传文件到指定的目录(yum install lrzsz)
/export/software/mysql
rpm ‐ivh *.rpm
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.
[root@mysql ~]# cat /root/.mysql_secret
# The random password set for the root user at Wed Aug 8 22:19:00 2018 (local time): xQkcU3kbyuZby1_V
[root@mysql ~]#
#启动mysql
service mysql start
# 登 录 mysql mysql ‐uroot ‐p
(粘贴密码:xQkcU3kbyuZby1_V)
set PASSWORD=PASSWORD('123456');
mysql>quit
mysql ‐uroot ‐p 123456(新密码)
grant all privileges on *.* to 'root' @'%' identified by '123456'; flush privileges;
通过windows的mysql客户端工具连接,是否能连接上,能连接上就授权成功,没有连接上, 说明没有授权成功!
3、mysql-基础操作篇
mysql ‐uroot ‐p 123456
mysql>quit
SELECT VERSION(), CURRENT_DATE;
mysql> SELECT VERSION(), CURRENT_DATE;
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| VERSION() | CURRENT_DATE |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 5.6.25 | 2018‐08‐08 |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.32 sec)
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;
mysql> SELECT VERSION(), CURRENT_DATE;
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| VERSION() | CURRENT_DATE |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 5.6.25 | 2018‐08‐08 |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.00 sec)
mysql> select version(), current_date;
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| version() | current_date |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 5.6.25 | 2018‐08‐08 |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.00 sec)
mysql> SeLeCt vErSiOn(), current_DATE;
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| vErSiOn() | current_DATE |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 5.6.25 | 2018‐08‐08 |
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.00 sec)
mysql>SELECT SIN(PI()/4), (4+1)*5;
mysql> SELECT SIN(PI()/4), (4+1)*5;
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
| SIN(PI()/4) | (4+1)*5 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
| 0.7071067811865475 | 25 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
1 row in set (0.34 sec)
mysql> SELECT VERSION(); SELECT NOW();
+‐‐‐‐‐‐‐‐‐‐‐+
| VERSION() |
+‐‐‐‐‐‐‐‐‐‐‐+
| 5.6.25 |
+‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.00 sec)
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| NOW() |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 2018‐08‐08 23:11:11 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.00 sec)
mysql> SELECT
‐> USER()
‐> ,
‐> CURRENT_DATE;
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| USER() | CURRENT_DATE |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| jon@localhost | 2010‐08‐06 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
mysql> select
‐> user()
‐> \c mysql>
3.4、创建和使用数据库
mysql> show databases;
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| Database |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| information_schema |
| mysql |
| performance_schema |
| test |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
4 rows in set (0.07 sec)
mysql> CREATE DATABASE menagerie;
mysql> USE menagerie Database changed
mysql>show tables;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
‐> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql>show create table pet;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
| Table | Create Table
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
| pet | CREATE TABLE `pet` (
`name` varchar(20) DEFAULT NULL,
`owner` varchar(20) DEFAULT NULL,
`species` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birth` date DEFAULT NULL,
`death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
mysql> desc pet;
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Field | Type | Null | Key | Default | Extra |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
Fluffy Harold cat f 1993‐02‐04 Claws Gwen cat m 1994‐03‐17 Buffy Harold dog f 1989‐05‐13 Fang Benny dog m 1990‐08‐27
Bowser Diane dog m 1979‐08‐31 1995‐07‐29 Chirpy Gwen bird f 1998‐09‐11
Whistler Gwen bird 1997‐12‐09 Slim Benny snake m 1996‐04‐29
Fluffy Harold cat f 1993‐02‐04
Claws Gwen cat m 1994‐03‐17
Buffy Harold dog f 1989‐05‐13
Fang Benny dog m 1990‐08‐27
Bowser Diane dog m 1979‐08‐31 1995‐07‐29
Chirpy Gwen bird f 1998‐09‐11
Whistler Gwen bird \N 1997‐12‐09 \N
Slim Benny snake m 1996‐04‐29
mysql> load data local infile '/root/data/pet.txt' into table pet; Query OK, 8 rows affected, 6 warnings (0.06 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 6
mysql> select *from pet;
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Fluffy | Harold | cat | f | 1993‐02‐04 | NULL |
| Claws | Gwen | cat | m | 1994‐03‐17 | NULL |
| Buffy | Harold | dog | f | 1989‐05‐13 | NULL |
| Fang | Benny | dog | m | 1990‐08‐27 | NULL |
| Bowser | Diane | dog | m | 1979‐08‐31 | 1995‐07‐29 |
| Chirpy | Gwen | bird | f | 1998‐09‐11 | NULL |
| Whistler | Gwen | bird | NULL | 1997‐12‐09 | NULL |
| Slim | Benny | snake | m | 1996‐04‐29 | NULL |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
8 rows in set (0.01 sec)
mysql> select *from pet;
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Fluffy | Harold | cat | f | 1993‐02‐04 | NULL |
| Claws | Gwen | cat | m | 1994‐03‐17 | NULL |
| Buffy | Harold | dog | f | 1989‐05‐13 | NULL |
| Fang | Benny | dog | m | 1990‐08‐27 | NULL |
| Bowser | Diane | dog | m | 1979‐08‐31 | 1995‐07‐29 |
| Chirpy | Gwen | bird | f | 1998‐09‐11 | NULL |
| Whistler | Gwen | bird | NULL | 1997‐12‐09 | NULL |
| Slim | Benny | snake | m | 1996‐04‐29 | NULL |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
8 rows in set (0.01 sec)
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
mysql> UPDATE pet SET birth = '1989‐08‐31' WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Bowser | Diane | dog | m | 1989‐08‐31 | 1995‐07‐29 |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
mysql> SELECT * FROM pet WHERE birth >= '1998‐1‐1';
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Chirpy | Gwen | bird | f | 1998‐09‐11 | NULL |
| Puffball | Diane | hamster | f | 1999‐03‐30 | NULL |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Buffy | Harold | dog | f | 1989‐05‐13 | NULL |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Chirpy | Gwen | bird | f | 1998‐09‐11 | NULL |
| Whistler | Gwen | bird | NULL | 1997‐12‐09 | NULL |
| Slim | Benny | snake | m | 1996‐04‐29 | NULL |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
‐> OR (species = 'dog' AND sex = 'f');
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| name | owner | species | sex | birth | death |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Claws | Gwen | cat | m | 1994‐03‐17 | NULL |
| Buffy | Harold | dog | f | 1989‐05‐13 | NULL |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
mysql> SELECT name, birth FROM pet;
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | birth |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Fluffy | 1993‐02‐04 |
| Claws | 1994‐03‐17 |
| Buffy | 1989‐05‐13 |
| Fang | 1990‐08‐27 |
| Bowser | 1989‐08‐31 |
| Chirpy | 1998‐09‐11 |
| Whistler | 1997‐12‐09 |
| Slim | 1996‐04‐29 |
| Puffball | 1999‐03‐30 |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
mysql> SELECT DISTINCT owner FROM pet;
+‐‐‐‐‐‐‐‐+
| owner |
+‐‐‐‐‐‐‐‐+
| Benny |
| Diane |
| Gwen |
| Harold |
+‐‐‐‐‐‐‐‐+
mysql> SELECT name, species, birth FROM pet
‐> WHERE species = 'dog' OR species = 'cat';
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | species | birth |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Fluffy | cat | 1993‐02‐04 |
| Claws | cat | 1994‐03‐17 |
| Buffy | dog | 1989‐05‐13 |
| Fang | dog | 1990‐08‐27 |
| Bowser | dog | 1989‐08‐31 |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
mysql> SELECT name, birth FROM pet ORDER BY birth;
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | birth |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Buffy | 1989‐05‐13 |
| Bowser | 1989‐08‐31 |
| Fang | 1990‐08‐27 |
| Fluffy | 1993‐02‐04 |
| Claws | 1994‐03‐17 |
| Slim | 1996‐04‐29 |
| Whistler | 1997‐12‐09 |
| Chirpy | 1998‐09‐11 |
| Puffball | 1999‐03‐30 |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
mysql> SELECT name, birth FROM pet ORDER BY birth desc;//降序排列mysql> SELECT name, birth FROM pet ORDER BY birth asc ;//升序排列
mysql> SELECT name, species, birth FROM pet
‐> ORDER BY species, birth DESC;
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| name | species | birth |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
| Chirpy | bird | 1998‐09‐11 |
| Whistler | bird | 1997‐12‐09 |
| Claws | cat | 1994‐03‐17 |
| Fluffy | cat | 1993‐02‐04 |
| Fang | dog | 1990‐08‐27 |
| Bowser | dog | 1989‐08‐31 |
| Buffy | dog | 1989‐05‐13 |
| Puffball | hamster | 1999‐03‐30 |
| Slim | snake | 1996‐04‐29 |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+
#查询当前的日期
mysql> select curdate() from pet;
+‐‐‐‐‐‐‐‐‐‐‐‐+
| curdate() |
+‐‐‐‐‐‐‐‐‐‐‐‐+
| 2018‐08‐09 |
+‐‐‐‐‐‐‐‐‐‐‐‐+
#获取当年的年
mysql> select YEAR('2018‐02‐05') AS YEARS from pet;
+‐‐‐‐‐‐‐+
| YEARS |
+‐‐‐‐‐‐‐+
| 2018 |
+‐‐‐‐‐‐‐+
#获取当年的月
mysql> select month('2018‐02‐05') AS YEARS from pet;
+‐‐‐‐‐‐‐+
| YEARS |
+‐‐‐‐‐‐‐+
| 2 |
+‐‐‐‐‐‐‐+
#获取当年的日
mysql> select day('2018‐02‐05') AS YEARS from pet;
+‐‐‐‐‐‐‐+
| YEARS |
+‐‐‐‐‐‐‐+
| 5 |
+‐‐‐‐‐‐‐+
mysql> SELECT name, birth, CURDATE(),
‐> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
‐> FROM pet;
mysql> SELECT name, birth, death,
‐> TIMESTAMPDIFF(YEAR,birth,death) AS age
‐> FROM pet WHERE death IS NOT NULL ORDER BY age;
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| name | birth | death | age |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
| Bowser | 1989‐08‐31 | 1995‐07‐29 | 5 |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+
4、实例
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
select * from shop;
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| article | dealer | price |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
SELECT MAX(article) AS article FROM shop;
+‐‐‐‐‐‐‐‐‐+
| article |
+‐‐‐‐‐‐‐‐‐+
| 4 |
+‐‐‐‐‐‐‐‐‐+
//求某一列的最小值
select min(price) as article from shop;
+‐‐‐‐‐‐‐‐‐+
| article |
+‐‐‐‐‐‐‐‐‐+
| 1.25 |
+‐‐‐‐‐‐‐‐‐+
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| article | dealer | price |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| 0004 | D | 19.95 |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC LIMIT 1;
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| article | price |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price) FROM shop s2
WHERE s1.article = s2.article);
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| article | dealer | price |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+