MySQL 基础语法

1、SQL语句（CRUD）

 下载地址Mac版点我，能够熟练地运用CRUD那么后端开发就成功了一半。

以下是数据库基础语法：

# 创建表
CREATE TABLE customer(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    height DOUBLE
);


# 插入一条数据
INSERT INTO 
    customer(name, age, height) 
VALUES
    ('小马哥', 23, 34.5);

# 查询数据
SELECT * FROM customer;

# 更新语句
UPDATE skill SET 
    name = '小马哥', level = 1000 
WHERE id = 1

# 删除数据 （逐行删除）
DELETE FROM customer

# 删除数据 （先删除后创建表，效率高）
TRUNCATE customer

# 查询年龄为空的(NULL 值判断，不能用=、!=、<、>符号)
SELECT * FROM customer age IS NULL

# 模糊查询（_代表单个任意字符，%代表多个任意字符）
SELECT * FROM customer WHERE name LIKE '_马_' #代表name是3个字符并且中间是“马”
SELECT * FROM customer WHERE name LIKE '___' #代表name查询是3个字符的
SELECT * FROM customer WHERE name LIKE '马%' #代表name查询以“马”开头的名字
SELECT * FROM customer WHERE name LIKE '_马%' #代表name查询第二个是“马”的名字

以下是多表查询：

SELECT customer.name , company.name FROM company, customer

SELECT cr.name , cy.name FROM company AS cy, customer AS cr

SELECT cr.name , cy.name FROM company  cy, customer  cr


# 以下会产生笛卡尔乘积

SELECT
	cr.NAME cr_name,
	cy.NAME cy_name 
FROM
	company cy,
	customer cr

# 以下不会产生笛卡尔乘积

SELECT
	cr.NAME cr_name,
	cr.company_id cr_company_id,
	cy.NAME cy_name,
	cy.id cy_id
	
FROM
	company cy,
	customer cr	
WHERE
    cr.company_id = cy.id

以下查询会产生笛卡尔乘积

以下查询不会产生笛卡尔乘积 

 连表查询更推荐以下做法：

# 左外连接
SELECT * FROM
	 customer a LEFT JOIN company b
	ON 
	a.company_id = b.id 
	

# 右外连接
SELECT * FROM
	 customer a RIGHT JOIN company b
	ON 
	a.company_id = b.id 
	
	
	# 内连接
SELECT * FROM
	 customer a JOIN company b
	ON 
	a.company_id = b.id 
	
	
	# 这个和下面效果一样
SELECT * FROM
	 customer a LEFT JOIN company b
	ON 
	a.company_id = b.id 
	WHERE
	b.id IS NULL
	
	# 和上面等价 
SELECT * FROM customer WHERE company_id IS NULL
	

 注意一下WHERE 和 AND 区别

SELECT * FROM
	customer a LEFT JOIN company b 
ON
	a.company_id = b.id
WHERE
	b.name LIKE '%阿里%'
	
	
	
SELECT * FROM
	customer a LEFT JOIN company b 
ON
	a.company_id = b.id AND b.name LIKE '%阿里%'
	
	

排序：

SELECT * FROM customer WHERE id > 0 ORDER BY age DESC # ASC：升序   DESC：降序 

 分页：

SELECT * FROM customer WHERE id > 0 LIMIT (n-1) * pageSize, pageSize;

子查询：

SELECT * FROM customer WHERE company_id = (SELECT id FROM company WHERE name = '阿里巴巴')


SELECT * FROM customer WHERE company_id IN (SELECT id FROM company WHERE name LIKE '%里%')

以上就是数据库SQL语句的基础用法，祝玩的愉快！