Mysql常用语句整理

目录

1.数据库

2.表

2.1创建表

2.2 修改表

2.3 删除表

3.数据

3.1增

3.2删

3.3改

3.4查

3.5窗口函数

---

1.数据库

CREATE DATABASE name;

USE name;

2.表

2.1创建表

CREATE TABLE mytable(

id INT NOT NULL AUTO_INCREMENT,

col1 INT NOT NULL DEFAULT 1,

col2 VARCAR(45) NULL,

col3 DATE NULL,

PRIMARY KEY (`id`));

2.2 修改表

添加列	ALTER TABLE mytable ADD COLUMN mycol CHAR(20);
删除列	ALTER TABLE matable DROP COLUMN mycol;

2.3 删除表

DROP TABLE mytable;

3.数据

3.1增

普通插入	INSERT INTO mytable(col1,  col2)
插入检索数据	INSERT INTO mytable1(col1,  col2) SELECT col1, col2 FROM mytable2;
插入新表	CREATE TABLE newtable AS SELECT * FROM mytable;

3.2删

DELETE FROM mytable

WHERE id=1;

3.3改

UPDATE mytable

SET col = val

WHERE id = 1;

3.4查

DISTINCT	SELECT DISTINCT col1, col2 FROM mytable;
LIMIT	SELECT *           FROM mytable LIMIT 5;		SELECT * FROM mytavle LIMIT 0,5;		SELECT * FROM mytable LIMIT 3, 5;
ODER BY ASC,DESC	SELECT * FROM mytable DRDER BY col1 ASC, col2 DESC;
WHERE	SELECT * FROM mytable WHERE col IS NULL		SELECT * FROM mytable WHERE col < id;		SELECT * FROM mytable WHERE col BETWEEN value1 AND value2
%  _  [] LIKE	SELECT * FROM mytable WHERE col LIKE ‘[^AB]%’
AS	SELECT CONCAT(TRIM(col1),  ‘（‘,  TRIM(col2),  ‘)’ )  AS concat_c FROM mytable
AVG  COUNT MAX  MIN SUM LEFT   RIGHT LOWER UPPER LTRIM  RTRIM LENGTH SOUNDEX	SELECT AVG(DISTINCT col) AS avg_col FROM mytable			SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple')
GROUP BY HAVING	SELECT col, COUNT(*) AS num FROM mytable WHERE col > 2 GROUP BY col			HAVING num >= 2; GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前； 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
IN	SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2 )
	SELECT cust_name, (SELECT COUNT(*)  FROM orders WHERE orders.cust_id=customers.cust_id) AS orders_num FROM customers ORDER BY cust_name;
INNER  JOIN ON	SELECT A.value,  B.value FROM tablea AS A INNER JOIN tableb AS B ON A.key = B.key;
	SELECT name FROM employee AS e1 INNER JOIN employee AS e2 ON e1.department = e2.department AND e2.name = “Jim”;
NATURAL JOIN ON	SELECT A.value, B.value FORM tablea AS A NATURAL JOIN tableb AS B
LEFT OUTER JOIN ON RIGHT OUTER JOIN ON		SELECT Customers.cust_id, Oders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
UNION	SELECT col FROM mytable WHERE col = 1 UNION SELECT col FROM mytable WHERE col = 2;
VIEW	CREATE VIEW myview AS SELECT Concat(col1, col2) AS concat_col, col3*col4 AS computer_col FROM mytable WHERE col5 = val;
PROCEDURE	CREATE PROCEDURE myprocedure(out ret int) BEGIN     DECLARE y INT;     SELECT SUM(col1)     FROM mytable     INTO y;     SELECT y*y INTO ret; END
CURSOR	CERATE PROCEDURE myprocedure(OUT ret INT) BEGIN     DECLARE done BOOLEAN DEFAULT 0;     DECLARE mycursor CURDOR FOR     SELECT col1 FROM mytable;     DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;     OPEN mycuesor;     REPEAT         Fetch mycursor INTO ret;         SELECT ret;     UNTIL done END REPEAT;     CLOSE mycursor; END
TRIGGER	CREATE TRIGGER mytrigger AFTER INSERT ON mytable FOR EACH ROW SELECT NEW.col INTO @result; SELECT  @result;

3.5窗口函数

什么是窗口函数 - 知乎