sql-数据库操作

CREATE DATABASE test;
USE test;
二、创建表
CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT, # int 类型，不为空，自增
  col1 INT NOT NULL DEFAULT 1, # int 类型，不可为空，默认值为 1，不为空
  col2 VARCHAR(45) NULL, # 变长字符串类型，最长为 45 个字符，可以为
  col3 DATE NULL,  # 日期类型，可为空
  PRIMARY KEY (`id`)); # 设置主键为 id
ALTER TABLE mytable ADD col CHAR(20); 添加列
ALTER TABLE mytable DROP COLUMN col; 删除列
DROP TABLE mytable; 删除表

INSERT INTO mytable(col1, col2) VALUES(val1, val2); 普通插入
INSERT INTO mytable(col1, col2) SELECT col1, col2 FROM mytable2; 插入检索出的数据
CREATE TABLE newtable AS SELECT * FROM mytable;  将一个表的内容插入到一个新表

UPDATE mytable SET col = val WHERE id = 1; 五、更新
DELETE FROM mytable  WHERE id = 1;六、删除

TRUNCATE TABLE 可以清空表，也就是删除所有行。
TRUNCATE TABLE mytable;

七、查询
DISTINCT 相同值只会出现一次
LIMIT：限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
SELECT * FROM mytable LIMIT 5; 返回前 5 行：
SELECT * FROM mytable LIMIT 0, 5 ; 返回前 5 行：
SELECT *FROM mytable LIMIT 2, 3; ;返回第 3 ~ 5 行
八、排序
ASC ：升序（默认）  DESC ：降序
可以按多个列进行排序，并且为每个列指定不同的排序方式：
SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;
九、过滤
SELECT * FROM mytable WHERE col IS NULL;
AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
NOT 操作符用于否定一个条件。

十、通配符
通配符也是用在过滤语句中，但它只能用于文本字段。
% 匹配 >=0 个任意字符；_ 匹配1个 
[ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。
用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配
SELECT * FROM mytable WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
不要滥用通配符，通配符位于开头处匹配会非常慢。

十一、计算字段
计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias FROM mytable;
CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col FROM mytable;
十二、函数
各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。
AVG()	返回某列的平均值（ 会忽略 NULL 行）   MAX()MIN() COUNT()SUM()

使用 DISTINCT 可以汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;
文本处理 LEFT()	
函数	说明
LEFT()	RIGHT() LOWER()	 UPPER() LTRIM()RTRIM() LENGTH()分组
日期和时间处理
ADDDATE()	ADDTIME() CURDATE() DATE()	 DATEDIFF() DATE_ADD()	
DAY()	DAYOFWEEK()	HOUR()NOW()	TIME()	
mysql> SELECT NOW();
2018-4-14 20:25:11

十三：分组
SELECT col, COUNT(*) AS num FROM mytable WHERE col > 2
GROUP BY col HAVING num >= 2;
子查询
SELECT * FROM mytable1
WHERE col1 IN (SELECT col2   FROM mytable2);
连接：
inner join，on 自连接 LEFT OUTER JOIN

NION 来组合两个查询

视图，存储过程
事务：不能回退 SELECT、 CREATE 和 DROP 语句。：
游标：游标可以对一个结果集进行移动遍历
触发器：触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。
二十三、权限管理
USE mysql;
SELECT user FROM user;
新创建的账户没有任何权限。
CREATE USER myuser IDENTIFIED BY 'mypassword'; 创建账户
RENAME USER myuser TO newuser; 修改账户名
DROP USER myuser; 删除账户
SHOW GRANTS FOR myuser; 查看权限
账户用 username@host 的形式定义，username@% 使用的是默认主机名。
GRANT SELECT, INSERT ON mydatabase.* TO myuser; 授予权限
删除权限

GRANT 和 REVOKE 可在几个层次上控制访问权限：
整个服务器，使用 GRANT ALL 和 REVOKE ALL；
整个数据库，使用 ON database.*；
特定的表，使用 ON database.table；
特定的列；
特定的存储过程。
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
必须使用 Password() 函数进行加密。
SET PASSWROD FOR myuser = Password('new_password');更改密码

具体参考：https://github.com/CyC2018/CS-Notes/blob/master/notes/SQL.md

其他

limit

LIMIT m,n : 表示从第m+1条开始，取n条数据； LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。 SELECT * FROM employees LIMIT 5 OFFSET 5

用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0）

在SQLite数据库中，可以用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节，具体说明与用法可参考：

连接concat

SELECT last_name||" "||first_name AS Name FROM employees

插入

INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33') 数据已存在忽略ignore insert IGNORE into actor values(3,'ED','CHASE','2006-02-15 12:34:33');

UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01' WHERE to_date = '9999-01-01';

UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录 REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

索引

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name); CREATE INDEX idx_lastname ON actor(last_name);

视图

注意 CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，

增加列

ALTER TABLE actor ADD （COLUMN） create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

触发器

用 BEFORE或AFTER ，执行的内容写出 BEGIN与END 之间 3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

CREATE TRIGGER audit_log AFTER INSERT ON employees_test BEGIN INSERT INTO audit VALUES (NEW.ID, NEW.NAME); END;

其他

ALTER TABLE titles_test RENAME TO titles_2017

外键： DROP TABLE audit; CREATE TABLE audit( EMP_no INT NOT NULL REFERENCES employees_test(ID), create_date datetime NOT NULL);

case

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, (CASE b.btype WHEN 1 THEN s.salary * 0.1 WHEN 2 THEN s.salary * 0.2 ELSE s.salary * 0.3 END) AS bonus FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'