SQL语句（2）

分组数据： 

SELECT 
    COUNT(*) AS hanshu
FROM Author
GROUP BY age;

过滤分组：

SELECT 
    COUNT(*) AS hanshu
FROM Author
GROUP BY age
HAVING COUNT(*)>=2;

子查询 ：

子查询过滤

SELECT *
FROM Author
WHERE name_s IN (SELECT name_publish FROM Book
                                 WHERE name_publish='鲁迅')

 SELECT *,
(SELECT title FROM Book
WHERE Book.name_publish=Author.name_s) AS tang
FROM Author;

联结表 

 

SELECT name_s,age,title
FROM Author,Book
WHERE Author.name_s=Book.name_publish;

  创建高级联结: 

#使用表别名

SELECT name_s,age,title
FROM Author as A,Book AS B
WHERE A.name_s=B.name_publish;

组合查询 

SELECT *
FROM Author as A
WHERE A.name_s='鲁迅'
UNION
SELECT *
FROM Author
WHERE name_s='冰心';

 插入数据

INSERT INTO Author(id,name_s,age)

VALUES(6,'汤紫涵',20);

 更新和删除数据

更新数据

UPDATE Author
SET name_s='邓宗华'
WHERE name_s='汤紫涵';

删除数据

DELETE FROM Author
WHERE name_s='邓宗华'