SQL语句

1. 基础SQL语句

查看数据库和表:

SHOW DATABASES; --显示数据库
USE test; --选择数据库
SHOW TABLES; --显示表
DESC t_table_name; --显示表结构

CREATE DATABASE MyDatabase; -- 创建数据库
CREATE DATABASE MyDatabase DEFAULT CHARSET utf8; -- 创建数据库,使用默认字符utf-8

关系数据库的一个重要概念:任何数据都表示为行和列组成的表,而每条 SELECT语句的结果也都是一个行和列组成的表。

复制表:

CREATE TABLE test2 SELECT * FROM test1; --複製表包括数据
CREATE TABLE test2 SELECT * FROM test1 WHERE 0; --複製表结构但不要数据
INSERT INTO test2 SELECT * FROM test1; --表结构相同的时候複製数据
INSERT INTO person_copy(id,name) SELECT id,name FROM person; --仅複製部分列,可以加上where语句複製部分行

批量录入数据,语法:

LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name

例子:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
           FIELDS TERMINATED BY ',' 
           [OPTIONALLY] ENCLOSED BY '"'
           LINES TERMINATED BY '\n';

导出数据,使用shell:

echo "select * from test_table" | mysql -h127.0.0.1 -P3306 -uroot -p123456 test -A > data.txt #mysql加上-N配置可以设置不要输出表头

常用MySQL函数:

SELECT COUNT(*) FROM dual; --计数
SELECT curdate();
SELECT curtime();
SELECT now();
SELECT rand();

常用Group函数:

GROUP_CONCAT -- 将group分组后的字段逗号,分隔组合起来

执行外部sql文件:

SOURCE test.SQL;
. test.SQL; --或者这样也可以

新增一个用户并设定权限

语法:

GRANT priv_type
ON what
TO USER IDENTIFIED BY "password"
WITH GRANT OPTION

如果要授权所有的权限,则用all privileges,如果是指定的CRUD,则用select,update等。

授权的对象可以是数据库:abc.*,也可以是指定的表abc.t_google

USER的格式大致为:'root'@'%'

WITH GRANT OPTION使得授权人可以将权限转给他人。

一个常见的写法例子是:

GRANT ALL PRIVILEGES ON abc.* TO 'foobar'@'%' IDENTIFIED BY 'passwd12345';

查看系统所有用户

select host,user from mysql.user;

删除用户

如果用户是abc@'%',那么可以这样删除:

drop user abc

如果用户不是@'%',那么需要明确指定用户的@xxx

管理服务器

SHOW processlist; --显示当前连接到服务器的链接

2. 取分组指定值最大(或最小)的整行记录

这是一个很常见的问题,正常来说,每行记录都有一个主键,整行记录都找到了,就可以对这些记录或不在这些记录之内的行进行处理。

假设一个表结构:

id 主键
age 年龄,int类型
birth 生日,date类型

现在的需求是:找到年龄相同的人中,生日最早的所有人。我们假定相同年龄的人会有相同生日的,当他们都是生日最早的人时,任取一个。注:如果判断相同的字段birth,没有重复的值,那么问题会更简单。下面的做法参考这篇回答

效率最高的写法:

SELECT a.* FROM t_people a LEFT JOIN t_people b
ON a.age=b.age AND (a.birth>b.birth OR (a.birth=b.birth AND a.id>b.id))
WHERE b.birth IS NULL

如果birth不会相等,那么还可以这样写简单些:

SELECT a.* FROM t_people a LEFT JOIN t_people b
ON a.age=b.age AND a.birth>b.birth
WHERE b.birth IS NULL

另外还有一种常见的join group写法,但是效率不高:

SELECT * FROM t_people WHERE id IN (
    SELECT MAX(id) FROM 
     (SELECT b.* FROM 
       (SELECT age,MIN(birth) birth FROM t_people GROUP BY age) a
          JOIN t_people b ON a.age=b.age AND a.birth=b.birth) c
    GROUP BY age)

如果birth的值不会出现相同,还可以写简单些:

SELECT b.* FROM 
       (SELECT age,MIN(birth) birth FROM t_people GROUP BY age) a
          JOIN t_people b ON a.age=b.age AND a.birth=b.birth

对于MySQL,默认还有这种写法,但是不稳定,受mysql配置项ONLY_FULL_GROUP_BY影响,且不适合mysql之外的其它关系型数据库:

SELECT * FROM 
(SELECT * FROM t_people ORDER BY age, birth) a
GROUP BY age

下面这种写法是错的SELECT *,MIN(birth) FROM t_people GROUP BY age

3. 视图

视图一般不在项目代码中使用,但是对于开发、产品经理日常常用查询的表达,还是很有用的。创建视图:

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer;
文档更新时间: 2018-11-10 17:06   作者:nick