mysql数据库

1.增

insert into books(book_name) VALUES('盗墓笔记')
insert into chapters(chapter_name,book_id) VALUES('盗墓笔记第三章',1)

2.查

select id from books
select * from chapters
select id,chapter_name from chapters

-- 根据表里面的某一个字段的具体值查询，where后面加某种条件
select * from books where book_name="奇遇罗盘"
select * from books where id<39 and id>35

-- 使用limit
-- 第一个参数表示从哪开始（第一条数据用0表示）
-- 第二个参数表示一共查几条（包含起始那条数据）
select * from books limit 4
select * from books limit 1,3

-- 排序用order by
-- 根据id正序排序
select * from chapters order by id asc
-- 根据id倒序排序
select * from chapters order by id desc
-- 以book_id为优先排序，book_id相同才会对id排序
select * from chapters order by book_id desc,id desc

-- 模糊查询like
-- %指代省略掉的东西
select * from chapters where chapter_name like ' 第1章%'
select * from chapters where chapter_name like '%1%'

3.两表联查

select books.id,book_name,chapter_name from books INNER JOIN chapters on books.id=chapters.book_id

SELECT
	b.id,
	book_name,
	chapter_name 
FROM
	books b
	left JOIN chapters c ON b.id = c.book_id
-- 使用left join 可以做到包含左表的所有数据，即使右表中没有匹配项

4.给表起别名

SELECT
	b.id,
	book_name,
	chapter_name 
FROM
	books b
	INNER JOIN chapters c ON b.id = c.book_id

5.分组查询

-- 分组查询 只能查询你分组的数据 
-- GROUP BY 进行分组
select star,count(*) from js_data GROUP BY star ORDER BY count(*) 
select star,count(*) 消息数量 from js_data GROUP BY star ORDER BY 消息数量
-- 分组查询不能用where 要用having
select star,count(*) 消息数量 from js_data GROUP BY star HAVING 消息数量>1000 ORDER BY 消息数量 

6.嵌套子查询

-- 嵌套子查询 以一个语句的查询结果作为另一个查询语句的查询条件
select * from chapters where book_id in (select id from books)	
select * from books where id in (select book_id from chapters)


SELECT
	* 
FROM
	js_data 
WHERE
	id IN (
	SELECT
		js_data_id 
	FROM
		users_js_data_shiper 
	WHERE
	users_id IN ( SELECT id FROM users WHERE username = "孙晴" OR username = "孟紫云" OR username = "刁贵明" ))
	

7.三表联查

SELECT
	username,
	country_info,
	star 
FROM
	users us
	INNER JOIN users_js_data_shiper uj ON us.id = uj.users_id
	INNER JOIN js_data jd ON uj.js_data_id = jd.id 
WHERE
	username LIKE '叶%' or username LIKE '刘%'