SQL笔记（MySQL）

创建数据库

CREATE DATABASE dream;

创建数据库并指定默认的字符集

CREATE DATABASE dream 
DEFAULT CHARACTER SET utf8;

选择数据库

USE dream;

删除数据库

DROP DATABASE IF EXISTS dream;

显示数据库

SHOW DATABASES;

显示数据表

SHOW TABLES;

显示数据表中各个列的属性

SHOW COLUMNS FROM writers;

显示创建数据库的mysql语句

SHOW CREATE DATABASE dream;

显示创建数据表的mysql语句

SHOW CREATE TABLE writers;

创建数据表

CREATE TABLE writers
(
   writer_id INTEGER NOT NULL,
   writer_name VARCHAR(50) NOT NULL,
   sex CHAR(1),
   birthday DATE,
   nationality VARCHAR(50),
   PRIMARY KEY (writer_id)
) ENGINE=InnoDB 
  DEFAULT CHARSET='utf8';

CREATE TABLE books
(
   book_id INTEGER NOT NULL,
   title VARCHAR(100) NOT NULL,
   author_id INTEGER,
   publisher VARCHAR(100),
   isbn VARCHAR(13),
   price DOUBLE,
   PRIMARY KEY (book_id),
   FOREIGN KEY (author_id) REFERENCES writers (writer_id)
) ENGINE=InnoDB 
  DEFAULT CHARSET='utf8';

CREATE TABLE book_sales
(
   sales_id INTEGER AUTO_INCREMENT PRIMARY KEY,
   book_id INTEGER UNIQUE NOT NULL,
   sales INTEGER DEFAULT 0,
   KEY (book_id)
) ENGINE=InnoDB 
  DEFAULT CHARSET='utf8';

用检索出的数据创建表

CREATE TABLE writers_bak AS
SELECT * FROM writers;

修改表字段类型

ALTER TABLE writers MODIFY writer_name VARCHAR(100);

添加表字段

ALTER TABLE writers ADD email VARCHAR(100);

删除表字段

ALTER TABLE writers DROP COLUMN email;

修改表字段名

# change和modify都可修改字段的定义，change后面需要写两次列名，但是change可以修改字段名，modify不能。
ALTER TABLE writers CHANGE sex gender CHAR(1);

添加主键

ALTER TABLE writers ADD PRIMARY KEY (writer_id);

添加外键

ALTER TABLE books ADD CONSTRAINT FK_books_writers 
   FOREIGN KEY (author_id) REFERENCES writers (writer_id);

添加唯一约束

ALTER TABLE writers ADD UNIQUE (writer_name);

删除表

DROP TABLE IF EXISTS writers_bak_20140815;

重命名表

RENAME TABLES writers_bak TO writers_bak_20140815;

检索单个列

SELECT writer_name FROM writers;

检索多个列

SELECT writer_name, sex, nationality FROM writers;

检索所有列

SELECT * FROM writers;

DISTINCT关键字，过滤重复行

SELECT DISTINCT nationality FROM writers;

LIMIT字句，限制结果范围

SELECT * FROM writers LIMIT 3;    # 最多不返回3个结果
SELECT * FROM writers LIMIT 2, 5; # 检索从行2开始的5行，行从0开始计数

使用完全限定名

SELECT writers.nationality FROM dream.writers;

排序数据

SELECT * FROM writers ORDER BY birthday;

多列排序，排序顺序按列出现的顺序

SELECT * FROM writers ORDER BY nationality, birthday;

指定排序方向，DESC降序、ASC升序，默认为ASC升序

# 如果想在多个列上进行降序排序，则每个列都要指定DESC关键字
SELECT * FROM writers ORDER BY nationality DESC, birthday ASC;

随机排序

SELECT * FROM writers ORDER BY RAND();

WHERE字句过滤数据

SELECT * FROM writers WHERE nationality = '中国';
SELECT * FROM writers WHERE nationality != '中国'; # <>也表示不等于
SELECT * FROM writers WHERE birthday < '1900-01-01';
SELECT * FROM writers WHERE birthday >= '1980-01-01'
SELECT * FROM writers WHERE birthday BETWEEN '1900-01-01' AND '1999-12-31';
SELECT * FROM writers WHERE birthday IS NULL;

AND操作符

SELECT * FROM writers WHERE sex = 'M' AND nationality = '中国';

OR操作符（AND操作符的优先级大于OR操作符）

SELECT * FROM writers WHERE sex = 'F' OR birthday >= '1980-01-01';

IN操作符

SELECT * FROM writers WHERE nationality IN ('中国', '美国');

NOT操作符

SELECT * FROM writers WHERE nationality NOT IN ('中国', '美国');

LIKE操作符

SELECT * from writers WHERE writer_name like '张%'; # %匹配任意个字符
SELECT * from writers WHERE writer_name like '韩_'; # _匹配一个字符

用正则表达式进行检索

SELECT * FROM writers WHERE writer_name REGEXP '[a-zA-Z]+[\s·]?[a-zA-Z]+';

使用计算字段

SELECT CONCAT('[', nationality, ']', writer_name) FROM writers;

使用别名，关键字AS可以省略

SELECT YEAR(NOW()) - YEAR(birthday) AS age FROM writers;

使用函数

SELECT UPPER(writer_name) FROM writers; # 文本函数
SELECT YEAR(NOW()) - YEAR(birthday) AS age FROM writers; # 日期时间函数
SELECT ABS(-1); # 数值处理函数

聚集函数

SELECT AVG(price) FROM books;   # 忽略列值为NULL的行
SELECT COUNT(price) FROM books; # 忽略列值为NULL的行
SELECT COUNT(*) FROM books;     # 不会忽略列值为NULL的行
SELECT MAX(price) FROM books;   # 忽略列值为NULL的行
SELECT MIN(price) FROM books;   # 忽略列值为NULL的行
SELECT SUM(price) FROM books;   # 忽略列值为NULL的行

聚集不同的值

SELECT COUNT(DISTINCT publisher) FROM books; # 其他聚集函数也可以指定DISTINCT，COUNT(*)不允许指定

分组

SELECT nationality, COUNT(*) FROM writers GROUP BY nationality;
SELECT author_id, publisher FROM books GROUP BY author_id, publisher;

分组过滤 HAVING

# HAVING与WHERE的区别：
# HAVING是基于分组聚集值过滤，WHERE是基于特定行值顾虑；
# 可以这样理解：WHERE是在数据分组前过滤，HAVING是在数据分组后过滤。

SELECT nationality, COUNT(*) 
FROM writers 
GROUP BY nationality
HAVING COUNT(*) > 1;

完整SELECT字句出现顺序

SELECT nationality, COUNT(*) AS cnt
FROM writers
WHERE sex = 'M'
GROUP BY nationality
ORDER BY cnt
LIMIT 5;

子查询

SELECT * FROM books WHERE author_id IN (
   SELECT writer_id FROM writers WHERE writer_name LIKE '张%'
);

子查询作为计算字段（相关子查询）

# 查询每个作家出书的数量
SELECT writer_name, 
          (SELECT COUNT(*) FROM books b WHERE b.author_id = w.writer_id) AS book_num
FROM writers w
ORDER BY book_num DESC;

表连接（笛卡尔积）

SELECT title, writer_name, publisher, isbn, price
FROM books b, writers w
WHERE b.author_id = w.writer_id;

内部连接

SELECT title, writer_name, publisher, isbn, price
FROM books b INNER JOIN writers w
ON b.author_id = w.writer_id;

多表连接

SELECT writer_name, title, sales
FROM writers w, books b, book_sales s
WHERE w.writer_id = b.author_id
AND b.book_id = s.book_id;

自连接

# 查询比某本书价格更高的书
SELECT b1.title, b1.price
FROM books b1, books b2
WHERE b1.price > b2.price
AND b2.title = '嫌疑人X的献身';

自然连接

SELECT * FROM books NATURAL JOIN book_sales;

左外连接

SELECT title, sales
FROM books b LEFT OUTER JOIN book_sales s
ON b.book_id = s.book_id;

右外连接

SELECT title, sales
FROM book_sales s RIGHT OUTER JOIN books b
ON b.book_id = s.book_id;

表连接与分组结合

SELECT nationality, COUNT(book_id) AS book_num
FROM books b INNER JOIN writers w
ON b.author_id = w.writer_id
GROUP BY w.nationality ;

组合查询 UNION

SELECT title FROM books NATURAL JOIN book_sales WHERE sales > 5000
UNION
SELECT title FROM books, writers WHERE author_id = writer_id AND nationality = '中国';

组合查询UNION，包含重复行 （默认情况下，UNION会过滤掉重复行）

SELECT title FROM books NATURAL JOIN book_sales WHERE sales > 5000
UNION ALL
SELECT title FROM books, writers WHERE author_id = writer_id AND nationality = '中国';

组合查询UNION，排序

SELECT title FROM books NATURAL JOIN book_sales WHERE sales > 5000
UNION
SELECT title FROM books, writers WHERE author_id = writer_id AND nationality = '中国'
ORDER BY title;

插入数据

INSERT INTO writers(writer_id, writer_name, sex, birthday, nationality)
VALUES (1007, '三毛', 'F', '1943-03-26', '中国');

插入数据，省略指定列名，但是要依赖列的顺序

INSERT INTO writers VALUES (1008, '毕淑敏', 'F', '1952-10-10', '中国');

插入部分数据

INSERT INTO writers(writer_id, writer_name, sex, nationality)
VALUES (1009, '老子', 'M', '中国');

插入多个行

INSERT INTO writers(writer_id, writer_name, sex, birthday, nationality)
VALUES (1010, '大仲马', 'M', '1802-07-24', '法国'),
          (1011, '小仲马', 'M', '1824-07-27', '法国');

插入检索出的数据

INSERT INTO writers_bak(writer_id, writer_name, sex, birthday, nationality)
SELECT writer_id, writer_name, sex, birthday, nationality FROM writers;

更新数据

UPDATE writers
SET writer_name = '亚历山大·仲马'
WHERE writer_name = '大仲马';

删除数据

DELETE FROM writers WHERE writer_id = 1000;

删除所有行，但是没有删除表

DELETE FROM writers_bak;

创建视图

CREATE VIEW writer_books AS
SELECT writer_name, title
FROM writers w, books b
WHERE w.writer_id = b.author_id
ORDER BY writer_id;

删除视图

DROP VIEW writer_books;

创建存储过程

DELIMITER //
CREATE PROCEDURE book_avg_price()
BEGIN
	SELECT AVG(price) FROM books;
END;//
DELIMITER ;

调用存储过程

CALL book_avg_price();

删除存储过程

DROP PROCEDURE book_avg_price;

存储过程使用参数
MySQL支持IN（传递给存储过程）、OUT（从存储过程中传出）、INOUT（对存储过程传入传出）三种类型的参数。

DELIMITER //
CREATE PROCEDURE get_writer_book_num(IN wid INT, OUT writer_book_num INT)
BEGIN
<span style="white-space:pre">	</span>SELECT (SELECT COUNT(*) FROM books b WHERE b.author_id = w.writer_id) INTO writer_book_num
<span style="white-space:pre">	</span>FROM writers w
<span style="white-space:pre">	</span>WHERE w.writer_id = wid;
END;//
DELIMITER ;

CALL get_writer_book_num(1001, @writer_book_num);

SELECT @writer_book_num;

游标

DELIMITER //
CREATE PROCEDURE writer_book_num()
BEGIN
	DECLARE wid INT;
	DECLARE done INT DEFAULT 0;

	-- 声明游标
	DECLARE wid_cur CURSOR
	FOR
	SELECT writer_id FROM writers;

	-- 当游标到达尾部时，mysql自动设置done = 1
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	-- 打开游标
	OPEN wid_cur;

	REPEAT
		-- 使用游标
		FETCH wid_cur INTO wid;
		CALL get_writer_book_num(wid, @writer_book_num);
		SELECT wid, @writer_book_num;
	UNTIL done END REPEAT;

	-- 关闭游标
	CLOSE wid_cur;
	
END;//
DELIMITER ;

CALL writer_book_num();

创建触发器

delimiter //
CREATE TRIGGER trg_writers_insert
AFTER INSERT ON writers
FOR EACH ROW
BEGIN
	INSERT INTO writers_bak
		SELECT * FROM writers w WHERE w.writer_id = NEW.writer_id;
END;//
delimiter 

删除触发器

DROP TRIGGER IF EXISTS trg_writers_insert;

开启事务

START TRANSACTION;

提交事务

COMMIT;

回滚事务

ROLLBACK;

默认的MySQL行为是自动提交所有更改，可以修改这个设置

SET autocommit = 0;

创建保留点（事务处理块中的占位符）

SAVEPOINT point;

回退到保留点

ROLLBACK TO point;

创建用户并指定口令

CREATE USER huey IDENTIFIED BY 'hueypass';

重命名用户账号

RENAME USER huey TO huey2672;

删除用户账号

DROP USER huey;

查看用户权限
SHOW GRANTS FOR huey;
赋予用户权限
GRANT SELECT ON dream.* TO huey;

撤销用户权限

REVOKE SELECT ON dream.* FROM huey;

修改用户口令

SET PASSWORD FOR huey = PASSWORD('huey');

修改自己的口令

SET PASSWORD = PASSWORD('root');

MySQL中的ENUM类型：

CREATE TABLE tmp_table(  
    season ENUM('Spring', 'Summer', 'Autumn', 'Winter')  
);  
INSERT INTO tmp_table VALUES('Spring');  
INSERT INTO tmp_table VALUES('summer');  
INSERT INTO tmp_table VALUES(1);  
INSERT INTO tmp_table VALUES(2);  
INSERT INTO tmp_table VALUES(3);  
INSERT INTO tmp_table VALUES(4);  
INSERT INTO tmp_table VALUES(NULL);  
SELECT * FROM tmp_table;

MySQL中的SET类型：

CREATE TABLE tmp_table(  
    season SET('Spring', 'Summer', 'Autumn', 'Winter')  
);  
INSERT INTO tmp_table values('Spring,Autumn,Winter');  
INSERT INTO tmp_table values('summer,WINTER');  
INSERT INTO tmp_table values(1);  
SELECT * FROM tmp_table;