LeetCode Database习题 && MySQL基础

LeetCode有一些数据库的题目，部分免费的题做一下；MySQL有一些最基础的知识，捡重要的mark一下。

LeetCode Database习题

下列习题都通过了Navicat Premium环境的测试。

Swap Salary

描述：

基本意思就是交换男女性别那栏，原本是‘男’就设为‘女’，原本是‘女’就设为‘男’，要求不使用临时表；

思路：

这里可以使用IF(expr1, expr2, expr3)，类似三目运算，expr1为条件。

解答如下：

1	UPDATE `salary` set `sex` = if(`sex` = 'm', 'f', 'm');

Exchange Seats

描述：

基本意思就是每两行中的奇数行和偶数行互换id，如果整个表中行数是奇数，那么最后一行不变；

解答如下：

CREATE TABLE count01 (
    counts1 int(11)
);
INSERT INTO count01
 ( counts1 )
SELECT COUNT(*) FROM seat;
SELECT
    CASE
        WHEN id % 2 = 1 AND id != counts1  THEN id + 1
        WHEN id % 2 = 1 AND id = counts1 THEN id
        ELSE id - 1
    END id, student
FROM seat, count01
ORDER BY id;

CASE END是sql用来做条件语句的，这段代码先构造了一个表，然后把seat条目数存在表中，然后在后面的查询语句中，引用两个表seat和count01的栏位名来调用具体的值，进行逻辑处理，最后ORDER BY一下；

但是题目要求的是用查询语句，所以稍微改一下原本的代码：

SELECT
    CASE
        WHEN id % 2 = 1 AND id != (SELECT COUNT(*) FROM seat) THEN id + 1
        WHEN id % 2 = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
        ELSE id - 1
    END id, student
FROM seat, count01
ORDER BY id;

Not Boring Movies

描述：

基本意思就是在表中，查询奇数id且描述非“无聊”的电影，然后按照评分由高到低排列；

思路：

可以使用where语句，代码如下：

1	SELECT * FROM cinema WHERE id % 2 = 1 AND description <> 'boring' ORDER BY rating DESC;

Classes More Than 5 Students

描述：

大致意思就是给你一个课程表，列出有五个及五个以上学生上课的课程；

思路：

GROUP BY配合一些聚合函数使用，使用通用方法：

1
2
3

SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1";

同时where不能和聚合函数一起使用，所以采用having，having进一步约束组数据，通用方法：

SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"
HAVING (函數條件);

所以代码如下：

1 2	SELECT `courses`.`class` FROM courses GROUP BY `courses`.`class` HAVING COUNT(*) >= 5;

但是题目中有个提示：

The students should not be counted duplicate in each course.

所以having的条件应该做一番更改：

1 2	SELECT `courses`.`class` FROM courses GROUP BY `courses`.`class` HAVING COUNT(DISTINCT student) >= 5;

Big Countries

描述：

大致意思是编写sql输出大国名称、人口以及面积，其中大国指的是面积超过300万活着人口超过2500万的国家；

思路：

使用where，代码如下：

1 2	SELECT name, population, area FROM World WHERE population > 25000000 OR area > 3000000;

Rising Temperature

描述：

大致意思就是找出比昨天温度高的日期的id；

思路：

使用表格别名，采用TO_DAYS函数编写条件语句，具体代码如下：

SELECT wtr01.Id
FROM Weather AS wtr01, Weather AS wtr02
WHERE TO_DAYS(wtr01.RecordDate) - TO_DAYS(wtr02.RecordDate) = 1
AND wtr01.Temperature > wtr02.Temperature;

Delete Duplicate Emails

描述：

大致意思是说编写SQL删除表中的相同条目的重复条目，仅保留相同条目中最小id的那一个条目；

思路：

如果给一个表创建两个别名，比如Person per01和Person per02，调用Id和Email进行比较，每次都是等号左边的先遍历完，通过这个性质可以删除重复的数据，代码如下：

1
2
3

DELETE per01
FROM Person per01, Person per02
WHERE per01.Email = per02.Email AND per01.Id > per02.Id;

Customers Who Never Order

描述：

大致意思是有两个表，分表表示订单和客户，订单中有标识消费过的客户的Id，找出订单中没有标识的客户（也就是没有买任何东西的客户）；

思路：

查询出Id不存在于订单中的客户，这里可以使in语法，in配合where使用，可以遍历多个值，代码如下：

SELECT c.`Name` AS Customers
FROM Customers c
WHERE c.Id NOT IN
(SELECT o.CustomerId FROM Orders o)

Duplicate Emails

描述：

大致意思是查找Person01表中所有重复的emails；

思路：

前面某题Delete Duplicate Emails类似，代码如下：

1
2
3

SELECT DISTINCT per01.Email
FROM Person01 per01, Person01 per02
WHERE per01.Email = per02.Email AND per01.Id > per02.Id;

注：重复的数据在查询的结果中只出现一次；

Employees Earning More Than Their Managers

描述：

大致意思就是Employee表中包含了所有员工，每个员工对应一个经理Id，表拥有一个经理栏位，通过这个表查询出所有比经理赚得多的员工；

思路：

用别名的方式进行判断，代码如下：

1
2
3

SELECT em01.`Name` AS Employee
FROM Employee em01, Employee em02
WHERE em01.ManagerId = em02.Id AND em01.Salary > em02.Salary;

Second Highest Salary

描述：

意思就是查询Employee第二高薪；

思路：

使用LIMIT OFFSET，OFFSET采用习惯的从0起始，LIMIT表示条数，代码如下：

1
2
3

SELECT Salary as SecondHighestSalary
FROM Employee01
ORDER BY Salary DESC LIMIT 1 OFFSET 1;

Combine Two Tables

描述：

大意就是按要求合并两张表；

思路：

要求里有一句“regardless if there is an address for each of those people”，感觉可以用一个left join合并表，代码如下：

1 2	SELECT FirstName, LastName, City, State FROM Person02 LEFT JOIN Address ON Person02.PersonId = Address.AddressId;

MySQL基础

配置

brew install mysql的坑
https://www.jianshu.com/p/bfb2569a017f 有具体步骤的注释；
涉及MySQL服务services https://zhuanlan.zhihu.com/p/30216245；
彻底删除过去版本的MySQL https://gist.github.com/vitorbritto/0555879fe4414d18569d 注意底下的留言；
MySQL报错 http://blog.51cto.com/732233048/1636409 虽然对我没用；
查看版本：mysql –version；
启动MySQL：mysql.server start
登录，命令行：mysql -u root -p，然后输入密码；

基本库操作

创建数据库

CREATE DATABASE 数据库名;

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

不存在则创建，存在就不创建；
创建设定编码集为utf8；

倒入数据库，比如： source /Users/yin/Downloads/cgdb.sql;

删除数据库

drop databse 数据库名;

选择数据库

use 数据库名;

查询当前选择的数据库，使用select database();或者在命令模式使用status命令；

基本表操作

创建表

通用语法：

CREATE TABLE table_name (column_name column_type);

如果不想字段为NULL可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错；
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1；
PRIMARY KEY关键字用于定义列为主键。可以使用多列来定义主键，列间以逗号分隔；
ENGINE设置存储引擎，CHARSET设置编码；

可以通过命令提示符创建表；

符号``是用来区分MySQL关键字与普通字符而引入的符号，一般表名和字段名都是用这个符号；

删除表

通用语法：

DROP TABLE table_name;

drop truncate delete区别：

drop整表删除，truncate会保留表结构，delete一般用在有条件删除部分表数据的情况；

基本表数据操作

插入数据

1
2
3

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号；

查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

select * ：返回所有记录；
limitN ：返回N条记录；
offset M ：跳过M条记录，默认 M=0，单独使用似乎不起作用；
limit N,M ：相当于offset N limit M，从第N条记录开始，返回M条记录；

WHERE语句

通用语法：

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

WHERE中比较运算符需要注意：<>，它表示不等于；

UPDATE查询

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DELETE语句

DELETE FROM table_name [WHERE Clause]

LIKE子句

在WHERE子句中使用LIKE，如果没有％单独使用LIKE，则效果和＝相同。

通用语法：

SELECT field1, field2,…fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = ‘somevalue’

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

UNION操作符

通用语法：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

UNION操作符用于连接两个以上的SELECT语句，将结果组合到一个集合中，多个SELECT语句会删除重复的数据；

UNION只会选取不同的值(类似set)，如果要保留重复的值，使用UNION ALL；

ORDER BY语句

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

你可以使用ASC或DESC关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列；

字符集采用utf8需转码：

ORDER BY CONVERT([xxxxx] using gbk);

GROUP BY语句

通用语法：

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

WITH ROLLUP：统计之后再做统计；

事务

在 MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务；

事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行；

事务用来管理insert, update, delete语句；

A原子性C一致性I隔离性D持久性；

BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE；

临时表

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

通用写法：

CREATE TEMPORARY…

可以使用DROP TABLE手动删除MySQL临时表；

SQL注入

要注意的几点：

通过字符串拼接容易产生SQL注入，Java里可以用PreparedStatement处理，原理就是转义单引号，但是％没有转义(可以通过严格限制用户输入来解决)；
mybatis不会对${}参数进行预处理，会对#{name}进行预处理；

一些概念

SET FOREIGN_KEY_CHECKS = 0; 取消外键约束；

\G 按列打印；

参考