7天快速掌握MySQL-DAY2

有关SQL 2019-11-26

354

1. 导入示例数据库

---------------------------------------------
---连接到MySQL服务器并创建数据库
----------------------------------------------
mysql> CREATE DATABASE IF NOT EXISTS yiibaidb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
---------------------------------------------
---导入数据
----------------------------------------------
mysql> use yiibaidb;
Database changed
mysql> source D:/yiibaidb/yiibaidb.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......
---------------------------------------------
---测试导入结果
----------------------------------------------
mysql> select city,phone,country from `offices`;
+---------------+------------------+-----------+
| city          | phone            | country   |
+---------------+------------------+-----------+
| San Francisco | +1 650 219 4782  | USA       |
| Boston        | +1 215 837 0825  | USA       |
| NYC           | +1 212 555 3000  | USA       |
| Paris         | +33 14 723 4404  | France    |
| Beijing       | +86 33 224 5000  | China     |
| Sydney        | +61 2 9264 2451  | Australia |
| London        | +44 20 7877 2041 | UK        |
+---------------+------------------+-----------+
7 rows in set (0.00 sec)

至此，示例数据库导入完毕。

2. 查询语句 select from

最基本的语句，意思是从那张表去查询什么数据列，可以是原表的列，也可以是聚合后的列，可以包含重复列，也可以去重，也可以只查看前几列。

-------------------------------------------
--最基本语句
------------------------------------------
mysql> select * from offices;
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA    | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA    | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY    | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy D'abbans | NULL         | NULL  | France    | 75017      | EMEA      |
| 5          | Beijing       | +86 33 224 5000  | 4-1 Haidian Area         | NULL         | BJ    | China     | 110000     | NA        |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL  | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL  | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
7 rows in set (0.00 sec)
------------------------------------------------------------
--查看yiibaidb数据库中的table
------------------------------------------------------------
mysql> show tables;
+--------------------+
| Tables_in_yiibaidb |
+--------------------+
| customers          |
| employees          |
| items              |
| offices            |
| orderdetails       |
| orders             |
| payments           |
| productlines       |
| products           |
| tokens             |
+--------------------+
10 rows in set (0.00 sec)
----------------------------------------------------------
---账单表payments应该包含重复数据，首先查看行数
----------------------------------------------------------
mysql> select count(1) from payments;
+----------+
| count(1) |
+----------+
|      273 |
+----------+
1 row in set (0.00 sec)
----------------------------------------------------------
--查看payments数据表的结构
----------------------------------------------------------
mysql> describe payments;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| customerNumber | int(11)       | NO   | PRI | NULL    |       |
| checkNumber    | varchar(50)   | NO   | PRI | NULL    |       |
| paymentDate    | date          | NO   |     | NULL    |       |
| amount         | decimal(10,2) | NO   |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
----------------------------------------------------------
--查询非重复数据customerNumber记录条目数
----------------------------------------------------------
mysql> select count(distinct customerNumber) dis_customerNumber_count from payments;
+--------------------------+
| dis_customerNumber_count |
+--------------------------+
|                       98 |
+--------------------------+
1 row in set (0.00 sec)
----------------------------------------------------------
--查询payments数据表的前10行
----------------------------------------------------------
mysql> select * from payments limit 10;
+----------------+-------------+-------------+----------+
| customerNumber | checkNumber | paymentDate | amount   |
+----------------+-------------+-------------+----------+
|            103 | HQ336336    | 2014-10-19  |  6066.78 |
|            103 | JM555205    | 2013-06-05  | 14571.44 |
|            103 | OM314933    | 2014-12-18  |  1676.14 |
|            112 | BO864823    | 2014-12-17  | 14191.12 |
|            112 | HQ55022     | 2013-06-06  | 32641.98 |
|            112 | ND748579    | 2014-08-20  | 33347.88 |
|            114 | GG31455     | 2013-05-20  | 45864.03 |
|            114 | MA765515    | 2014-12-15  | 82261.22 |
|            114 | NP603840    | 2013-05-31  |  7565.08 |
|            114 | NR27552     | 2014-03-10  | 44894.74 |
+----------------+-------------+-------------+----------+
10 rows in set (0.00 sec)

3. 限制条件&分组： where/group by/having

用来限制查询条件

----------------------------------------------------------
--查询payments消费额大于80000的订单记录
----------------------------------------------------------
mysql> select * from payments where amount > 80000;
+----------------+-------------+-------------+-----------+
| customerNumber | checkNumber | paymentDate | amount    |
+----------------+-------------+-------------+-----------+
|            114 | MA765515    | 2014-12-15  |  82261.22 |
|            124 | AE215433    | 2015-03-05  | 101244.59 |
|            124 | BG255406    | 2014-08-28  |  85410.87 |
|            124 | ET64396     | 2015-04-16  |  83598.04 |
|            124 | KI131716    | 2013-08-15  | 111654.40 |
|            141 | ID10962     | 2014-12-31  | 116208.40 |
|            141 | JE105477    | 2015-03-18  | 120166.58 |
|            148 | KM172879    | 2013-12-26  | 105743.00 |
|            167 | GN228846    | 2013-12-03  |  85024.46 |
|            239 | NQ865547    | 2014-03-15  |  80375.24 |
|            321 | DJ15149     | 2013-11-03  |  85559.12 |
+----------------+-------------+-------------+-----------+
11 rows in set (0.00 sec)
----------------------------------------------------------
--查询消费额大于80000的订单记录中的非重复的customerNumber
----------------------------------------------------------
mysql> select customerNumber  from payments where amount > 80000
    -> group by customerNumber;
+----------------+
| customerNumber |
+----------------+
|            114 |
|            124 |
|            141 |
|            148 |
|            167 |
|            239 |
|            321 |
+----------------+
7 rows in set (0.00 sec)
----------------------------------------------------------------------------
--查询payments消费总额大于200000的订单记录，注意，这里是消费总额，是
--聚合后的量，需要使用having
----------------------------------------------------------------------------
mysql> select customerNumber, sum(amount) as sum_amount
    -> from payments
    -> group by customerNumber
    -> having sum_amount > 200000;
+----------------+------------+
| customerNumber | sum_amount |
+----------------+------------+
|            124 |  584188.24 |
|            141 |  715738.98 |
+----------------+------------+
2 rows in set (0.00 sec)

4. 排序函数 order by

用来排序，默认升序，desc降序，asc显示声明升序。

----------------------------------------------------------
--查询消费额最小的10个订单，从小到大
----------------------------------------------------------
mysql> select * from payments
    -> order by amount
    -> limit 10;
+----------------+-------------+-------------+---------+
| customerNumber | checkNumber | paymentDate | amount  |
+----------------+-------------+-------------+---------+
|            398 | JPMR4544    | 2015-05-18  |  615.45 |
|            381 | MS154481    | 2013-08-22  | 1128.20 |
|            121 | FD317790    | 2013-10-28  | 1491.38 |
|            381 | CC475233    | 2013-04-19  | 1627.56 |
|            103 | OM314933    | 2014-12-18  | 1676.14 |
|            456 | MO743231    | 2014-04-30  | 1679.92 |
|            350 | OB648482    | 2015-01-29  | 1834.56 |
|            172 | AD832091    | 2014-09-09  | 1960.80 |
|            161 | BR352384    | 2014-11-14  | 2434.25 |
|            148 | DD635282    | 2014-08-11  | 2611.84 |
+----------------+-------------+-------------+---------+
10 rows in set (0.00 sec)
----------------------------------------------------------
--查询消费额最大的10个订单，从大到小
----------------------------------------------------------
mysql> select * from payments
    -> order by amount desc
    -> limit 10;
+----------------+-------------+-------------+-----------+
| customerNumber | checkNumber | paymentDate | amount    |
+----------------+-------------+-------------+-----------+
|            141 | JE105477    | 2015-03-18  | 120166.58 |
|            141 | ID10962     | 2014-12-31  | 116208.40 |
|            124 | KI131716    | 2013-08-15  | 111654.40 |
|            148 | KM172879    | 2013-12-26  | 105743.00 |
|            124 | AE215433    | 2015-03-05  | 101244.59 |
|            321 | DJ15149     | 2013-11-03  |  85559.12 |
|            124 | BG255406    | 2014-08-28  |  85410.87 |
|            167 | GN228846    | 2013-12-03  |  85024.46 |
|            124 | ET64396     | 2015-04-16  |  83598.04 |
|            114 | MA765515    | 2014-12-15  |  82261.22 |
+----------------+-------------+-------------+-----------+
10 rows in set (0.00 sec)

5. 函数

5.1 时间函数

--获取当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-02-27 21:18:21 |
+---------------------+
1 row in set (0.00 sec)
--时间格式转换
mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301                                     |
+----------------------------------------------------+
1 row in set (0.00 sec)


mysql> select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
+---------------------------------------+
| str_to_date('08/09/2008', '%m/%d/%Y') |
+---------------------------------------+
| 2008-08-09                            |
+---------------------------------------+
1 row in set (0.00 sec)


mysql> select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
+--------------------------------------+
| str_to_date('08/09/08' , '%m/%d/%y') |
+--------------------------------------+
| 2008-08-09                           |
+--------------------------------------+
1 row in set (0.00 sec)


mysql> select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
+---------------------------------------+
| str_to_date('08.09.2008', '%m.%d.%Y') |
+---------------------------------------+
| 2008-08-09                            |
+---------------------------------------+
1 row in set (0.00 sec)


mysql> select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
+-------------------------------------+
| str_to_date('08:09:30', '%h:%i:%s') |
+-------------------------------------+
| 08:09:30                            |
+-------------------------------------+
1 row in set (0.00 sec)


mysql> select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
+---------------------------------------------------------+
| str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s') |
+---------------------------------------------------------+
| 2008-08-09 08:09:30                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)
--UNIX时间戳转换为时间
mysql> select from_unixtime(1550937600);
+---------------------------+
| from_unixtime(1550937600) |
+---------------------------+
| 2019-02-24 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)

5.2 数值函数

--举例：产生 0～100 内的任意随机整数
mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
|               23 |               10 |
+------------------+------------------+
1 row in set (0.00 sec)

5.3 字符串函数

--举例：CONCAT_WS(separator,str1,str2,...)：将多个字符串参数以给定的分隔符separator首尾相连后返回
mysql> select concat_ws(';','First name','Second name','Last name');
+-------------------------------------------------------+
| concat_ws(';','First name','Second name','Last name') |
+-------------------------------------------------------+
| First name;Second name;Last name                      |
+-------------------------------------------------------+
1 row in set (0.00 sec)

6. 作业

6.1 项目一：查找重复的电子邮箱（难度：简单）

查找重复的电子邮箱（难度：简单）
创建 email表，并插入如下三行数据
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
|  2  | c@d.com |
| 3  | a@b.com |
+----+---------+


编写一个 SQL 查询，查找 email 表中所有重复的电子邮箱。
根据以上输入，你的查询应返回以下结果：
+---------+
| Email   |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。
附录：
-- 创建表
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');

请尝试写出sql查询出重复的电子邮箱。

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

创建如下 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查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:
+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+
附录：
-- 创建表
CREATE TABLE World (
name VARCHAR(50) NOT NULL PRIMARY KEY,
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);