低调诺的博客

/*查找:*/
SELECT, FROM, ORDER BY, WHERE, IS NULL, DISTINCT, BETWEEN AND, AND, OR, IN, NOT, LIKE, %,  _,  -- 不支持[],
, 子查询， AS
/*计算字段:*/
TRIM, AS, 
/*分组*/
ORDER BY -- 位置在WHERE后，Having之前，过滤用HAVING(注意与WHERE的区别，WHERE过滤行),eg:SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING num_prods >= 2;  
/*函数*/
-- 字符串处理函数
Concat -- 连接表项, eg:SELECT Concat(prod_name, '(', prod_id, ')') FROM Products; 
UPPER, LOWER, TRIM, LTRIM, LENGTH, 
SOUNDEX, --返回字符串的SOUNDEX值, eg:SELECT * FROM Products WHERE SOUNDEX(prod_name) = SOUNDEX('inch teddy bear');
-- 日期处理函数
YEAR
-- 数值处理函数
ABS, COS, EXP, PI, SIN, SQRT,
-- 汇集函数
AVG, MAX, MIN, SUM -- 参数中使用DISTINCT表示只看不重复的行,eg:SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products;
COUNT -- 参数为 *  时，不忽略NULL，参数为列名时，省略该列的NULL值
/*内、外连接*/
natural join, join .. on,  left outer join, right outer join, full outer join 
/*组合*/
UNION, UNION ALL
/*插入*/
INSERT INTO ... VALUES(...); # 使用时必须使用列名，可省略部分列(有默认值或可为NULL)
/*更新*/
UPDATE ... SET ... WEHRE
# 使用同一个表的列更新另一个列
UPDATE blog_article SET weight=2*likes+views;
/*删除*/
DELETE; 
TRUNCATE TABLE ...; # 清空表的内容，但不删除表
DELETE FROM ...;
/*表操作*/
CREATE TABLE ... AS SELECT * FROM ...;
CREATE TABLE ...(item CHAR(...) NULL DEFAULT ..);
# create table customers (id int, name varchar(48), age int, address text, salary float);
ALTER TABLE ... ADD ... # 增加列
ALTER TABLE ... DROP COLUMN ... # 删除列
DROP TABLE ... # 删除表
RENAME
# 查看表结构
show create table table_name;
describe table_name
show full columns from table_name;
/*视图*/
CREATE VIEW ... AS SELECT ...
/*存储过程*/  -- 重要
mysql> create procedure insertInnoDB()  
    -> begin  
    -> set @i = 1;  
    -> while @i <= 1000000  
    -> do   
    -> insert into testInnoDB(name) values(concat("wy", @i));  
    -> set @i = @i + 1;  
    -> end while;  
    -> end//  
Query OK, 0 rows affected (0.00 sec)  
mysql> call insertInnoDB;  
/*事务处理*/
# 事务 ACID Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
# 事务，回退， 提交，保留点
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO Customers(cust_id, cust_name) VALUES('1000000010', 'Toys Emporium');
Query OK, 1 row affected (0.00 sec)
mysql> SAVEPOINT delete1
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE Customers
    -> SET cust_name='CTG'
    -> WHERE cust_id='1000000010';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> ROLLBACK TO delete1;
Query OK, 0 rows affected (0.02 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)
/*游标*/   #用的较少
/*约束*/
# 主键、外键
CREATE TABLE Orders(
    order_num     INTEGER     NOT NULL  PRIMARY KEY
    order_date     DATETIME   NOT NULL
    cust_id           CHAR(10)    NOT NULL  REFERENCES Customers(cust_id)
);
# another method
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGH KEY(cust_id) REFERENCES Customers(cust_id);
#检查约束
CHECK
/*触发器*/
# update, insert, delete触发(可自己定义)
# 效率低于约束
/*索引*/
CREATE INDEX prod_name_ind
ON Products(prod_name);

查找A、B两表中相同和不同的行

查找A表中有的，而B表中无的行

SELECT t1.*, t2.*  from A as t1 LEFT JOIN B as t2 ON t1.`name`=t2.`name` OR t1.`name`=t2.new_name WHERE t2.`name` is NULL

查找A表中无的，而B表中有的行
把上面查询的LEFT改为RIGHT就可以

查找A表和B表都有的行

SELECT t1.*, t2.*  from A as t1 JOIN B as t2 ON t1.`name`=t2.`name` OR t1.`name`=t2.new_name

利用B表的数据更新A表的数据

UPDATE A, B set A.name=B.name WHERE A.id=B.id

NULL 最好用is NULL来判断，尽量不用=NULL

MySQL关键字

附件: