Mysql常用语法

创建表（主键、外键）

# 主键
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

# 外键
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

条件查询where

排序order by

分组group by

只返回指定数量的记录limit

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

 

更新update

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

删除delete

DELETE FROM table_name
WHERE some_column=some_value;

like

in

between

别名

having  可与聚合函数一起使用

 

常用函数

avg（）返回列的平均值

SELECT AVG(column_name) FROM table_name

sum（）返回列的总数

SELECT SUM(column_name) FROM table_name;

 

count 返回指定列的记录数

SELECT COUNT(column_name) FROM table_name; 

distinct 去掉重复值

SELECT DISTINCT column_name,column_name
FROM table_name;

lenth()  返回某个列的值的长度

SELECT LENGTH(column_name) FROM table_name;

 

 

索引

索引优缺点

左连接left join

左表返回所有行，右表若没匹配的值，则结果为null

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

 

右连接right join

右表返回所有行，左表若没有匹配的值，则结果为null

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

 

内连接inner join

 只返回能匹配到的记录

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

union 合并两个或多个select语句的结果，并去重

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

 union all    合并两个或多个select语句的结果，不去重

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

 

存储过程

 

触发器