MySQL关键字

2020-12-19 03:16:40 编辑 4 阅读量:1081

```MySql
/*查找:*/
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

附件:

20-things-i-learned.pdf
ccrdgr.doc
netease-cloud-music_0.9.0-1_amd64.deb