<cite id="jdb0i"><object id="jdb0i"></object></cite>

<b id="jdb0i"></b>

<u id="jdb0i"></u>

<u id="jdb0i"><sub id="jdb0i"><mark id="jdb0i"></mark></sub></u>

<u id="jdb0i"><bdo id="jdb0i"></bdo></u>
<u id="jdb0i"></u><u id="jdb0i"><small id="jdb0i"><mark id="jdb0i"></mark></small></u>

数据库系列(一):数据库基础01

阿里云活动(仅限新用户):https://promotion.aliyun.com/ntms/yunparter/invite.html?userCode=1frag12g

1、数据库介绍篇

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、mysql在linux-安装篇

2.1、vmware中安装linux注意事项

2.1.1、记得关闭防火墙

service iptables stop
chkconfig iptables off(关闭开机自启:所谓的永久关闭防火墙)

2.1.2、创建统一的管理目录

mkdir -p /exprot/software
?
mkdir -p /export/servers

2.1.3软件环境

VMware、crt、centos6.9

?

2.1.4安装环境

1、VMware软件安装
?
    2、构建虚拟机
?
    3、需要配置Linux(ip,mac地址,hostname,防火墙),就可以通过crt这个客户端连接进行操作
?
    4、在linux操作系统进行安装msyql-5.6
    
    说明:因为在linux操作系统中,安装软件的方式主要有3种:1、源码安装(redis)2、rpm安装      
?
3、yum在线安装(安装MySQL为例)---linux联网()
?

2.2、centos6.9安装mysql

?

2.2.1、检查是否有自带的mysql

[root@hadoop-01 servers]# rpm -qa |grep mysql 
mysql-libs-5.1.73-8.el6_8.x86_64

2.2.2、卸载自带的mysql

[root@hadoop-01 servers]# rpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_64
[root@hadoop-01 servers]# 

2.2.3、下载mysql安装包

2.2.4、上传安装包到linux服务器

rz 上传文件到指定的目录(yum install lrzsz)
/export/software/mysql

2.2.5、安装

rpm -ivh *.rpm

2.2.6、查看初始化密码

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.154·
[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 ~]# 

2.2.7、启动mysql并登录

#启动mysql
service mysql start

?

#登录mysql
mysql -uroot -p
(粘贴密码:xQkcU3kbyuZby1_V)

2.2.8、修改密码

set PASSWORD=PASSWORD('123456');

2.2.9、退出mysql客户端

mysql>quit

2.2.10、用新密码进行登录

mysql -uroot -p
123456(新密码)

2.2.11、远程授权(赋予任何主机访问你数据库的权限)

grant all privileges on *.* to 'root' @'%' identified by '123456'; 
flush privileges;

2.2.12、验证远程授权是否成功

通过windows的mysql客户端工具连接,是否能连接上,能连接上就授权成功,没有连接上,说明没有授权成功!

3、mysql-基础操作篇

3.1、登录mysql

mysql -uroot -p
123456

3.2、退出mysql

mysql>quit

3.3、输入查询

  • 查看当前mysql的版本号及当前时间

    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中sql语句不区分大小写

    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> 
  • 可以进行简单的计算(如下所示)

    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();
    ?
    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 |
    +---------------+--------------+
  • sql语句写了一半,又不想执行可以在语句末尾加上'\c'

    mysql> select 
     ?  -> user()
     ?  -> \c
    mysql> 

3.4、创建和使用数据库

  • 查看当前有哪些数据库

    mysql>show databases;
    ?
    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

3.5、创建表及使用

  • 查看当前数据库有哪些表

    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

3.6、表中导入数据

在表中导入数据的方式有两种

  • 第一种:将以上数据整理成SQL语句,insert into pet....

  • 第二种:通过加载文件的方式将数据导入到表中

    1、创建一个pet.txt的文件(注:每个字段中用tab键隔开,字段没有值得记录用\N代替)

    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 ? ? ?
    Chirpy  Gwen ?  bird ?  f ? ? ? 1998-09-11
    Whistl  Gwen ?  bird ?  \N ? ?  1997-12-09 ? ? ?
    Slim ?  Benny ? snake ? m ? ? ? 1996-04-29

    2、加载数据

    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

    3、校验是否加载进去

    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)

3.7、数据检索部分

3.7.1、检索全部数据

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)

3.7.2、删除表中全部数据

mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

3.7.3、更新表中特定记录的数据

  • 更新表中名字为Bowser的生日

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

3.7.4、查询特定的行

  • 查询名字为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 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年以后的特定查询

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 |
+----------+-------+---------+------+------------+-------+

3.7.4.2、多条件查询(and | or)

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 |
    +-------+--------+---------+------+------------+-------+

3.7.5、检索特定的列

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 |
+----------+------------+
  • 查询不重复的字段要使用关键词DISTINCT

    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 |
    +--------+---------+------------+

3.7.6、排序

  • 根据某个字段进行排序(关键词:ORDER BY )

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 |
+----------+------------+
  • 升降序排列(desc:降序;asc:升序)

    mysql> SELECT name, birth FROM pet ORDER BY birth desc;//降序排列
    mysql> SELECT name, birth FROM pet ORDER BY birth asc ;//升序排列
  • 多列排序

    根据species字段升序排列,根据birth字段降序排列

    注: ORDER BY species 中无asc,desc,默认为升序排列

    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 |
    +----------+---------+------------+

3.7.7、日期计算

查看宠物多少岁,就可以使用计算日期的函数TIMESTAMPDIFF()

#查询当前的日期
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;

3.7.8、null和not null值

对一些字段类型要进行检查,判断某些字段是否为NULL,或者 non-NULL

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、实例

以下是如何解决MySQL的一些常见问题的示例。

4.1、首先创建一个表,并且导入数据

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);

4.2、检索表中的全部数据

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 |
+---------+--------+-------+

4.3、求某一列的最大值或者 最小值

SELECT MAX(article) AS article FROM shop;
?
+---------+
| article |
+---------+
| ? ? ? 4 |
+---------+
?
//求某一列的最小值
 select min(price) as article from shop; 
 
 +---------+
| article |
+---------+
| ?  1.25 |
+---------+

4.4、过滤出某个字段值最大的整条记录数据-涉及到子查询

SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
?
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| ?  0004 | D ? ?  | 19.95 |
+---------+--------+-------+

4.5、也可以通过关联查询来进行检索

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;

4.6、求出每一列的最大值,并且根据某一个字段进行分组--分组topn求法

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 |
+---------+-------+

4.6的另一种写法

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 |
+---------+--------+-------+

5、SQL中的聚合函数

SQL语言中定义了部分的函数,可以帮助我们完成对查询结果的计算操作:

1.count 统计个数(行数)

2.sum函数:求和

3.avg函数:求平均值

4.max、min 求最大值和最小值

5.1、count函数

语法:select count(*)|count(列名) from表名

注意: count在根据指定的列统计的时候,如果这一列中有null 不会被统计在其中。

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 ?  | 1989-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.00 sec)
?
mysql> select count(sex) from pet; 
+------------+
| count(sex) |
+------------+
| ? ? ? ?  7 |
+------------+
1 row in set (0.00 sec)
?
mysql> select count(owner) from pet; ? 
+--------------+
| count(owner) |
+--------------+
| ? ? ? ? ?  8 |
+--------------+
1 row in set (0.00 sec)
?
mysql> select count(death) from pet; ? ? 
+--------------+
| count(death) |
+--------------+
| ? ? ? ? ?  1 |
+--------------+
?

5.2、sum函数

语法:select sum(列名) from 表名;

注意事项:

1、如果使用sum 多列进行求和的时候,如果某一列中有null,这一列所在的行中的其他数据不会被加到总和。

2、可以使用mysql 数据库提供的函数 ifnull(列名,值)

3、在数据库中定义double类型数据,是一个近似值,需要确定准确的位数,这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)

numeric double float

mysql> select sum(price) from shop; ?
+------------+
| sum(price) |
+------------+
| ? ?  42.77 |
+------------+

5.3、avg函数

语法:select avg(列名) from 表名;

mysql> select avg(price) from shop; ? 
+------------+
| avg(price) |
+------------+
| ? 6.110000 |
+------------+

5.4、max函数

语法:select max(列名) from 表名;

mysql> select max(price) from shop; ? 
+------------+
| max(price) |
+------------+
| ? ?  19.95 |
+------------+

5.5、min函数

语法:select min(列名) from 表名;

mysql> select min(price) from shop; ? 
+------------+
| min(price) |
+------------+
| ? ? ? 1.25 |
+------------+

6、SQL分类

6.1、DDL (数据定义语言)

数据定义语言 - Data Definition Language

用来定义数据库的对象,如数据表、视图、索引等

创建数据库:create database test;
创建视图:create  view  test;
创建索引:create index test;
创建表:create table test1;

?

6.2、DML? (数据操纵语言)

数据处理语言 - Data Manipulation Language

在数据库表中更新,增加和删除记录

如 update, insert, delete

update tableName set age='18' where name='lisi'
?
insert into tableName value('1','2','3');
?
drop table tableName //删除表操作

6.3、DCL (数据控制语言)

数据控制语言 – Data Control Language

指用于设置用户权限和控制事务语句

如grant,revoke,if…else,while,begintransaction

?

6.4、DQL (数据查询语言)

数据查询语言 – Data Query Language

select

6.5、小结

1、创建数据库:create database  itcast;
2、使用数据库:use itcast;
3、查看当前数据库中的所有表:show tables ;
4、查看所有的数据库:show databases;
5、删除数据库:drop database itcast;
6、删除数据库中的表:drop table t1;
?

7、数据库的备份与恢复

7.1、备份命令

在mysql的安装目录的bin目录下有mysqldump命令,可以完成对数据库的备份。

语法:mysqldump -u 用户名 -p 数据库名> 磁盘SQL文件路径

由于mysqldump命令不是sql命令,需要在dos窗口下使用。

注意:在备份数据的时候,数据库不会被删除??梢允侄境菘?。同时在恢复数据的时候,不会

自动的给我们创建数据库,仅仅只会恢复数据库中的表和表中的数据。

mysqldump -uroot -p123456 menagerie >/root/data/menagerie.sql
?
//备份的文件
-rw-r--r--. 1 root root 3118 Oct 20 04:04 menagerie.sql

7.2、恢复命令

恢复数据库,需要手动的先创建数据库:

create database heima2;

语法:mysql -u 用户名-p 导入库名< 硬盘SQL文件绝对路径

需求:

1、创建heima8数据库。

2、重新开启一个新的dos窗口。

3、将mydb2备份的数据表和表数据 恢复到mydb6中。

//恢复命令
mysql -uroot -p123456 itcast</root/data/menagerie.sql
?

?

8、多表查询

8.1、笛卡尔积介绍

笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尓积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

准备数据:

create table A(
  A_ID int primary key auto_increment,
  A_NAME varchar(20) not null
);
insert into A values(1,'apple');
insert into A values(2,'orange');
insert into A values(3,'banana');
?
create table B( 
 ? A_ID int primary key auto_increment,
 ? B_PRICE double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);
?

展示效果:

mysql> select * from A,B;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| ?  1 | apple  | ?  1 | ? ? 2.3 |
| ?  2 | orange | ?  1 | ? ? 2.3 |
| ?  3 | banana | ?  1 | ? ? 2.3 |
| ?  1 | apple  | ?  2 | ? ? 3.5 |
| ?  2 | orange | ?  2 | ? ? 3.5 |
| ?  3 | banana | ?  2 | ? ? 3.5 |
| ?  1 | apple  | ?  4 | ?  NULL |
| ?  2 | orange | ?  4 | ?  NULL |
| ?  3 | banana | ?  4 | ?  NULL |
+------+--------+------+---------+

?

作用:笛卡尔积的数据,对程序是没有意义的,我们需要对笛卡尔积中的数据再次进行过滤。

对于多表查询操作,需要过滤出满足条件的数据,需要把多个表进行连接,连接之后需要加上过滤的条件。

mysql> select * from A,B where B.A_ID=1;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| ?  1 | apple  | ?  1 | ? ? 2.3 |
| ?  2 | orange | ?  1 | ? ? 2.3 |
| ?  3 | banana | ?  1 | ? ? 2.3 |
+------+--------+------+---------+
3 rows in set (0.00 sec)
?
mysql> select * from A,B where B.A_ID=1 and A.A_ID=1;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| ?  1 | apple  | ?  1 | ? ? 2.3 |
+------+--------+------+---------+

8.2、内连接(表的交集)

内连接: 语法一: select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;

语法二:

select * from 表名1 inner join 表名2 on 条件

mysql> select * from A inner join B on A.A_ID=B.A_ID;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| ?  1 | apple  | ?  1 | ? ? 2.3 |
| ?  2 | orange | ?  2 | ? ? 3.5 |
+------+--------+------+---------+

?

8.3、左外连接

外链接:左外连接、右外连接、全连接、自连接。

左外连接:用左边表去右边表中查询对应记录,不管是否找到,都将显示左边表中全部记录。

即:虽然右表没有香蕉对应的价格,也要把他查询出来。

语法:select * from 表1 left outer join 表2 on 条件;

?

mysql> select * from A left join B on A.A_ID=B.A_ID;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| ?  1 | apple  | ?  1 | ? ? 2.3 |
| ?  2 | orange | ?  2 | ? ? 3.5 |
| ?  3 | banana | NULL | ?  NULL |
+------+--------+------+---------+

8.4、右外连接

用右边表去左边表查询对应记录,不管是否找到,右边表全部记录都将显示。

即:不管左方能够找到右方价格对应的水果,都要把左方的价格显示出来。

语法:select * from 表1 right outer join 表2 on 条件;

?

mysql> select * from A right join B on A.A_ID=B.A_ID; ? ? 
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| ?  1 | apple  | ?  1 | ? ? 2.3 |
| ?  2 | orange | ?  2 | ? ? 3.5 |
| NULL | NULL ? | ?  4 | ?  NULL |
+------+--------+------+---------+

8.5、全外连接

全外连接:左外连接和右外连接的结果合并,但会去掉重复的记录。 select * from 表1 full outer join 表2 on 条件 select * from a full outer join b on a.A_ID = b.A_ID; 但是mysql数据库不支持此语法。

8.6、关联子查询

关联子查询:把一个sql的查询结果作为另外一个查询的参数存在。

8.6.1、in和exist关键词的用法

关联子查询其他的关键字使用:

回忆:age=23 or age=24? 等价于 age in (23,24)

in 表示条件应该是在多个列值中。

in:使用在where后面,经常表示是一个列表中的数据,只要被查询的数据在这个列表中存在即可。

?

mysql> select * from A where A_ID in(1,2,3);
+------+--------+
| A_ID | A_NAME |
+------+--------+
| ?  1 | apple  |
| ?  2 | orange |
| ?  3 | banana |
+------+--------+
3 rows in set (0.00 sec)
?
mysql> select * from A where A_ID =1 or A_ID =2 or A_ID =3;
+------+--------+
| A_ID | A_NAME |
+------+--------+
| ?  1 | apple  |
| ?  2 | orange |
| ?  3 | banana |
+------+--------+
//not in
mysql> select * from A where A_ID not in (1,2,3,4);
Empty set (0.00 sec)
?
mysql> select * from A where A_ID not in (3,4); ? ?
+------+--------+
| A_ID | A_NAME |
+------+--------+
| ?  1 | apple  |
| ?  2 | orange |
+------+--------+
2 rows in set (0.00 sec)

exists:

exists:将外查询表的每一行,代入子查询作为检验,如果exists子句返回TRUE,就将外查询的这行记录放进结果集中。

使用exists完成:

?

mysql> select * from A where exists(select A_ID from B);
+------+--------+
| A_ID | A_NAME |
+------+--------+
| ?  1 | apple  |
| ?  2 | orange |
| ?  3 | banana |
+------+--------+
?
mysql> select * from A where not exists(select A_ID from B);
Empty set (0.00 sec)

in not in 与 exists not exists 的区别参考:https://www.jianshu.com/p/9c863ad1032f

8.6.2、union 和union all使用法

?

UNION 语句:用于将不同表中查询的数据合并展示出来;(不包括重复数据)

UNION ALL 语句:用于将不同表中查询的数据合并展示出来;(包括重复数据)

mysql> select * from A  union ? select * from B; ? 
+------+--------+
| A_ID | A_NAME |
+------+--------+
| ?  1 | apple  |
| ?  2 | orange |
| ?  3 | banana |
| ?  1 | 2.3 ?  |
| ?  2 | 3.5 ?  |
| ?  4 | NULL ? |
+------+--------+
6 rows in set (0.00 sec)
?
mysql> select * from A  union all  select * from B;
+------+--------+
| A_ID | A_NAME |
+------+--------+
| ?  1 | apple  |
| ?  2 | orange |
| ?  3 | banana |
| ?  1 | 2.3 ?  |
| ?  2 | 3.5 ?  |
| ?  4 | NULL ? |
+------+--------+

?

8.6.3、case when 语句

case when 语句语法结构:

CASE sex ?
WHEN '1' THEN '男' ?
WHEN '2' THEN '女' ?
ELSE '其他' END 

准备数据

//创建表
create table employee(
empid ? ? ? ? ? ? ? ? ? int  , ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 
deptid ? ? ? ? ? ? ? ?  int ? , ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
sex ? ? ? ? ? ? ? ? ? ? varchar(20)  , ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
salary ? ? ? ? ? ? ? ?  double 
);
?
//加载数据
1   10  female  5500.0
2   10  male    4500.0
3   20  female  1900.0
4   20  male    4800.0
5   40  female  6500.0
6   40  female  14500.0
7   40  male    44500.0
8   50  male    6500.0
9   50  male    7500.0
?
load data local infile '/root/data/emp.txt' into table employee ;
?
select *,
case 
when salary < 5000 then "低等收入" 
when salary>= 5000 and salary < 10000 then "中等收入"
when salary > 10000 then "高等收入" ?
end  as level,
case sex
when "female" then 1 
when "male" then 0
end as flag 
from employee;
展开阅读全文

没有更多推荐了,返回首页

66期公式一肖算法 元江| 牡丹江市| 新源县| 香河县| 隆尧县| 乐陵市| 兴城市| 双城市| 原阳县| 固始县| 沙雅县| 徐闻县| 花莲市| 怀安县| 稷山县| 师宗县| 隆回县| 手游| 马关县| 浪卡子县| 麻栗坡县| 达孜县| 贵南县| 张家川| 陈巴尔虎旗| 长宁区| http://mdppfk.com 烟台市| 阜阳市| 崇明县| 敦化市| 太康县| 通辽市| 邻水| 延寿县| 太湖县| 仲巴县| 霍邱县| 北宁市| 台北县| 东海县| 铜山县| 瑞金市| 博罗县| 景东| 宜州市| 正蓝旗| 正镶白旗| 鹰潭市| 茌平县| 隆尧县| 德兴市| 五寨县| 光泽县| 宜城市| 韩城市| http://qou0256.tw 嘉峪关市| 章丘市| 会理县| 平泉县| 平和县| 三穗县| 石河子市| 新邵县| 无锡市| 金华市| 建宁县| 榆社县| 新乐市| 基隆市| 合作市| 时尚| 营山县| 二连浩特市| 遂宁市| 泌阳县| 南丰县| 乡宁县| 吴旗县| 泰州市| 琼海市| 叶城县| 古丈县| 海兴县| 郧西县| 车致| 涟源市| 武强县| 大渡口区| 惠水县| http://www.wgmlxv.cn 龙门县| 舟曲县| 东乌珠穆沁旗| 遵义市| 东兴市| 长汀县| 淮滨县| 新建县| 凌海市| 临潭县| 子长县| 信丰县| 遂宁市| 星子县| 伊吾县| 杭锦后旗| 漳浦县| 固阳县| 京山县| 江口县| 康乐县| 桂平市| 射洪县| 阿克苏市| 甘洛县| 邮箱| 峨眉山市| 揭阳市| 乌审旗| http://www.ilyngoi.tw 石首市| 浪卡子县| 达拉特旗| 银川市| 和龙市| 新巴尔虎右旗| 河源市| 东城区| 庆安县| 延庆县| 徐水县| 苏州市| 长海县| 临湘市| 甘孜县| 武鸣县| 保康县| 沙河市| 昌平区| 衡水市| 湘潭市| 蓬安县| 鹤庆县| 武平县| 榕江县| 潼关县| 安泽县| 安远县| 九寨沟县| http://www.apd43.co 潼关县| 辽中县| 晋城| 镇原县| 施秉县| 西昌市| 交城县| 永新县| 社旗县| 贵德县| 南皮县| 佛教| 越西县| 黎城县| 南华县| 巴南区| 安多县| 新昌县| 凌源市| 湘乡市| 泗阳县| 玉环县| 丽水市| 长海县| 遵义市| 星座| 柳林县| 都兰县| http://dwqwte.co 宜阳县| 甘泉县| 平陆县| 宁陕县| 苗栗县| 平顶山市| 桃江县| 日照市| 榆社县| 阳谷县| 福泉市| 英吉沙县| 河东区| 韶关市| 淮滨县| 肥城市| 明水县|