mysql学习笔记(2)

导入sql文件

用Navicat for Mysql导入.sql文件 - 路修远而求索 - 博客园
如果右键刷新失败，则用F5刷新，就会显示成功。

常用语句

SQL是一种用于操作数据库的语言。SQL是用于所有数据库的基本语言。不同数据库之间存在较小的语法更改，但基本的SQL语法基本保持不变。SQL是Structured Q uery Language 的简短缩写。根据ANSI（美国国家标准协会），SQL是操作关系数据库管理系统的标准语言。
SQL用于访问，更新和操作数据库中的数据。它的设计允许管理RDBMS中的数据，例如MYSQL。SQL语言还用于控制数据访问以及数据库模式的创建和修改。

MySQL是一个RDBMS tostore，使用SQL检索，修改和管理数据库。需要学习SQL语言才能有效地使用它。它可以通过下载和安装轻松获得。SQL是一种查询语言。MySQL是数据库软件。

查询语句 SELECT FROM

SELECT 语句用于从表中选取数据。
结果被存储在一个结果表中（称为结果集）。
SQL SELECT 语法

SELECT 列名称 FROM 表名称

以及：

SELECT * FROM 表名称

注释：SQL 语句对大小写不敏感。SELECT 等效于 select。
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据。
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

去重语句
sql查询语句去除重复列（行） - danuo2011的博客 - CSDN博客
1.存在两条完全相同的纪录
这是最简单的一种情况，用关键字distinct就可以去掉

example： 
select distinct * from table(表名) where (条件)

2.存在部分字段相同的纪录（有主键id即唯一键）
如果是这种情况的话用distinct是过滤不了的，这就要用到主键id的唯一性特点及group by分组

example:
select * from table where id in (select max(id) from table group by [去除重复的字段名列表,....])

3.没有唯一键ID
这种情况我觉得最复杂，目前我只会一种方法，有那位知道其他方法的可以留言，交流一下：

example:
select identity(int1,1) as id,* into newtable(临时表) from table
select * from newtable where id in (select max(id) from newtable group by [去除重复的字段名列表,....])
drop table newtable

前N个语句
各个数据库中，查询前n条记录的方法 - 闲看漫随 - 博客园

1.select top X *  from table_name    

 --查询前X条记录，可以改成需要的数字，比如前10条。

　2.select top X *  from table_name order by colum_name desc  

--按colum_name属性降序排序查询前X条记录，“order by” 后紧跟要排序的属性列名，其中desc表示降序，asc表示升序（默认也是升序方式）。

　3.select top n * from (select top m * from table_name order by column_name ) a order by column_name desc   

 --查询第N到M条记录。常用的分页也是这种方式。

CASE…END判断语句
CASE WHEN THEN END多条件判断 - Writing_the_future的博客 - CSDN博客
CASE WHEN THEN END结构有两种书写方式
结构：
第一种格式简单的CASE 函数

CASE 
            WHEN 条件1 THEN 结果1
            WHEN 条件2 THEN 结果2
            WHEN 条件3 THEN 结果3
            .........
            WHEN 条件N THEN 结果N
        END
    方式：

第二种格式为CASE搜索函数

CASE SEX 
                WHEN '1' THEN '男'
                WHEN '0'  THEN '女'
                ELSE '其他' END

筛选语句 WHERE
MySQL WHERE 子句 | 菜鸟教程
从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。
语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。 你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。 WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

运算符/通配符/操作符

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

分组语句 GROUP BY
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

   SELECT column_name, function(column_name)
        FROM table_name
        WHERE column_name operator value
        GROUP BY column_name;

聚集函数：

[GROUP BY {col_name | position}] [ASC | DESC],…]

HAVING子句
HAVING用法详解 - 肥宅兜 - 博客园

HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE
搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但
HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。
USE AdventureWorks;

GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail 
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3 ;
GO

排序语句 ORDER BY

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。 你可以设定多个字段来排序。 你可以使用 ASC 或 DESC
关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。 你可以添加 WHERE…LIKE 子句来设置条件。
ASC：升序（默认），DESC：降序，

函数
MySQL 函数 | 菜鸟教程
MySQL函数大全及用法示例 - 雨林少爷 - 博客园
函数较多不一一列出，可参考上述链接：
时间函数：
dayofweek(date)
返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)
mysql> select dayofweek(‘1998-02-03’);
　　-> 3
weekday(date)
返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
mysql> select weekday(‘1997-10-04 22:23:00’);
　　-> 5
mysql> select weekday(‘1997-11-05’);
　　-> 2
数值函数
abs(n)
返回n的绝对值
mysql> select abs(2);
　　-> 2
mysql> select abs(-32);
　　-> 32
sign(n)
返回参数的符号(为-1、0或1)
mysql> select sign(-32);
　　-> -1
mysql> select sign(0);
　　-> 0
mysql> select sign(234);
　　-> 1
字符串函数
ascii(str)
返回字符串str的第一个字符的ascii值(str是空串时返回0)
mysql> select ascii(‘2’);
　　-> 50
mysql> select ascii(2);
　　-> 50
mysql> select ascii(‘dete’);
　　-> 100
ord(str)
如果字符串str句首是单字节返回与ascii()函数返回的相同值。
如果是一个多字节字符,以格式返回((first byte ascii code)*256+(second byte ascii code))[*256+third byte asciicode…]
mysql> select ord(‘2’);
　　-> 50
sql注释
单行注释
SQL语句中的单行注释使用 –

create database database_x    --创建数据库database_x

多行注释
SQL语句中的多行注释采用 /…/

create database database_x
/*
创建一个数据库
名字叫做database_x
*/

两个项目练习：

项目一：查找重复的电子邮箱（难度：简单）
创建 email表，并插入如下三行数据

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
说明：所有电子邮箱都是小写字母。

参考代码：
– 创建表

   CREATE TABLE email (
    ID INT NOT NULL PRIMARY KEY,
    Email VARCHAR(255)
    )

– 插入数据

INSERT INTO email VALUES('1','a@b.com');
INSERT INTO email VALUES('2','c@d.com');
INSERT INTO email VALUES('3','a@b.com');

Mysql实现"查找重复的电子邮箱"的两种方法 - 数据库其他综合 - 红黑联盟
–查询数据
1：先按邮箱Email分组，生成临时表A，表A中有Email和对应出现的次数。然后判断次数是否大于1

SELECT Email
FROM (SELECT Email, COUNT(*) AS Sum
      FROM Email
     GROUP BY Email) AS A
WHERE A.Sum>1

2：利用HAVING

WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

SELECT Email FROM Email
GROUP BY Email
HAVING COUNT(*)>1

项目二：查找大国（难度：简单）

创建如下 World 表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一个国家的面积超过300万平方公里，或者(人口超过2500万并且gdp超过2000万)，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
参考代码：

• 创建表

    CREATE TABLE World (
        name VARCHAR(50) NOT NULL,
        continent VARCHAR(50) NOT NULL,
        area INT NOT NULL,
        population INT NOT NULL,
        gdp INT NOT NULL
        );

– 插入数据

INSERT INTO World
  VALUES('Afghanistan','Asia',652230,25500100,20343000);
INSERT INTO World 
  VALUES('Albania','Europe',28748,2831741,12960000);
INSERT INTO World 
  VALUES('Algeria','Africa',2381741,37100000,188681000);
INSERT INTO World
  VALUES('Andorra','Europe',468,78115,3712000);
INSERT INTO World
  VALUES('Angola','Africa',1246700,20609294,100990000);

–查询数据
查询目标：name，population，area
查询范围：World表
查询条件：面积超过300万平方公里 或 人口超过2500万

select
    name,
    population,
    area
from
    World
where
    area > 3000000 or population > 25000000