mysql基础语句实列

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

-- 查
select id from books
select * from chapters
select id,chapter_name from chapters
-- 两表联查
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
INNER JOIN chapters c ON b.id = c.book_id

-- 查
select * from books where id<39 and id>35
select * from books limit 4
-- 第一个参数表示从哪开始（第一条数据用0表示）
-- 第二个参数表示一共查几条（包含起始那条数据）
select * from books limit 1,3
-- 根据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
-- 模糊查询 %指代省略掉的东西
select * from chapters where chapter_name like ' 第1章%'
select * from chapters where chapter_name like '%1%'
-- 分组查询 只能查询你分组的数据 分组查询不能用where 要用having
select star,count(*) 消息数量 from js_data GROUP BY star HAVING 消息数量>1000 ORDER BY 消息数量
-- 三表联查
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 '刘%' or username LIKE '曹%'
-- 嵌套子查询 以一个语句的查询结果作为另一个查询语句的查询条件
select * from chapters where book_id in (select id from books)
select * from books where id in (select book_id from chapters)
select * from books,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 = "刁贵明" ))