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 '刘%'