SQL 必知必会（2）—— SQL的增删改查及函数应用

本次实验地址：https://developer.aliyun.com/adc/scenario/ad08dc06f71349dd9375a8779061e605?spm=a2c6h.26876520.0.i2.7cfe371cosEugk

1.连接ECS服务器

打开系统自带的终端工具。

Windows：打开命令窗口。
MAC：打开命令行终端Terminal

Windows用户请检查系统中是否安装有ssh工具。检查方法：

a. 在终端中输入命令ssh -V，查看ssh版本号。

b. 终端中返回以下结果说明已安装。

c. 否则请下载安装OpenSSH

在终端中输入连接命令ssh [username]@[ipaddress]。您需要将username和ipaddress替换成ECS服务器的登录名和公网地址，ECS服务器的登录名和公网地址可在第一小节中的云产品资源下拉列表中查看。例如:

ssh root@139.xxx.xxx.230

输入 yes

同意继续后将会提示输入登录密码。密码为已创建的ECS云服务器的登录密码。
说明：输入密码的过程中没有回显，请确保键入内容正确。

登录成功后会显示如下信息。

2.安装并配置MySQL

依次执行如下命令，下载并安装MySQL:

wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server --nogpgcheck

返回如下结果，表示MySQL安装成功。

执行如下命令，启动MySQL 数据库。

systemctl start mysqld.service

执行如下命令，查看MySQL运行状态。

systemctl status mysqld.service

执行如下命令，查看MySQL初始密码。

grep "password" /var/log/mysqld.log

执行如下命令，登录数据库。

mysql -uroot -p

执行如下命令，退出MySql。

exit

3.创建数据库和创建数据表

使用create命令创建数据库，语法如下:

create DATABASE 数据库名;

执行如下命令，创建数据库Test:

create DATABASE Test;

执行如下命令，连接Test数据库:

use Test;

创建MySQL数据表:

创建MySQL数据表时需要以下相关信息：

• 表名。
• 表字段名。
• 定义每个表字段。

创建数据表的SQL通用语法：

CREATE TABLE table_name(column_name column_type);

执行如下命令，在Test数据库中创建数据表web:

CREATE TABLE `web` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `rank` int(11) NOT NULL DEFAULT '0' COMMENT '排名',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

执行如下命令，查看数据库Test下的所有数据表:

show tables;

4.SQL的AND和OR运算符

AND和OR运算符用于基于一个以上的条件对记录进行过滤。

使用AND运算符

如果第一个条件和第二个条件都成立，则AND运算符显示一条记录。

执行如下SQL语句，从web表中选取id为1且rank大于2的数据：

SELECT * FROM web WHERE id= '1' AND rank > 2;

使用OR运算符

如果第一个条件和第二个条件中只要有一个成立，则OR运算符显示一条记录。

执行如下SQL语句，从web表中选取rank大于300的或name为阿里云的数据：

SELECT * FROM web WHERE rank > 300 OR name = '阿里云';

5.SQL的ORDER BY关键字

ORDER BY关键字用于对结果集按照一个列或者多个列进行排序。

ORDER BY关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用DESC关键字。ORDER BY语法如下。

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

使用ORDER BY。

执行如下SQL语句，从web表中选取所有数据，并按照rank列排序。

SELECT * FROM web ORDER BY rank;

使用ORDER BY DESC

执行如下SQL语句，从web表中选取所有数据，并按照rank列降序排序。

SELECT * FROM web ORDER BY rank DESC;

使用ORDER BY多列

执行如下SQL语句，查询web表中选取所有数据，并按照rank列降序排序。

SELECT * FROM web ORDER BY name,rank;

6.SQL函数说明

使用AVG()函数

AVG()函数返回数值列的平均值。AVG()语法如下。

SELECT AVG(column_name) FROM table_name;

执行如下SQL语句，在Test数据库中创建数据表access_log。

CREATE TABLE `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 AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

执行如下SQL语句，向数据表access_log插入数据。

INSERT INTO `access_log` 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');

执行如下SQL语句，从access_log数据表的count列获取平均值。

SELECT AVG(count) AS CountAverage FROM access_log;

使用COUNT()函数

COUNT()函数返回匹配指定条件的行数。COUNT()函数有三种语法，如下所示。

COUNT(column_name)语法 ：COUNT(column_name)函数返回指定列的值的数目（NULL不计入）。

SELECT COUNT(column_name) FROM table_name;

COUNT() 语法 COUNT() ：函数返回表中的记录数。

SELECT COUNT(*) FROM table_name;

COUNT(DISTINCT column_name)语法 ：COUNT(DISTINCT column_name)函数返回指定列的不同值的数目 。

SELECT COUNT(DISTINCT column_name) FROM table_name;

执行如下SQL语句，计算access_log数据表中site_id=3的总访问量。

SELECT COUNT(count) AS nums FROM access_log WHERE site_id=3;

执行如下SQL语句，计算access_log数据表中总记录数。

SELECT COUNT(*) AS nums FROM access_log;

执行如下SQL语句，计算access_log数据表中不同site_id的记录数。

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

使用MAX()函数

MAX()函数返回指定列的最大值。MAX()语法 如下。

SELECT MAX(column_name) FROM table_name;

执行如下SQL语句，获取web数据表中rank列的最大值。

SELECT MAX(rank) AS max_rank FROM web;

使用MIN()函数

MIN()函数返回指定列的最小值。MIN()语法 如下。

SELECT MIN(column_name) FROM table_name;

执行如下SQL语句，获取web数据表中rank列的最小值。

SELECT MIN(rank) AS min_rank FROM web;

使用SUM()函数

SUM()函数返回数值列的总数。SUM()语法 如下。

SELECT SUM(column_name) FROM table_name。

执行如下SQL语句，获取access_log数据表的count字段的总数。

SELECT SUM(count) AS nums FROM access_log;

使用GROUP BY语句

GROUP BY语句用于结合聚合函数，根据一个或多个列对结果集进行分组。GROUP BY语法如下。

SELECT column_name, aggregate_function(column_name)

FROM table_name
WHERE column_name operator value 

GROUP BY column_name;

执行如下SQL语句，获取access_log数据表各个site_id的访问量。

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

使用EXISTS运算符

EXISTS运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回False。EXISTS语法 如下。

SELECT column_name(s) 
FROM table_name
WHERE EXISTS 
(SELECT column_name FROM table_name WHERE condition);

执行如下SQL语句，查找查找count字段大于200的网站是否存在。

SELECT web.name, web.url 
FROM web
WHERE EXISTS (SELECT count FROM access_log WHERE web.id = access_log.site_id AND count > 200);