数据库语句

菜鸟总结

creat not null

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
1
2
3
4
5
6

join

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
1
2
3
4
5

题1：组合两个表

select firstName ,lastName,city,state 
from Person left join Address 
on Person.personId=Address.personId;
1
2
3

题2 第二高的薪水

//降序
select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary
1
2
3
4
5

group by

group by

SELECT site_id, SUM(WEB.count) AS nums
FROM WEB GROUP BY site_id;
1
2

SELECT Websites.name,COUNT(access_log.aid) AS nums 
FROM access_log LEFT JOIN Websites ON access_log.site_id=Websites.id
GROUP BY Websites.name;
1
2
3

between / in

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');
1
2
3

delete

DELETE FROM Websites
WHERE name='Facebook' AND country='USA';
1
2

update

UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';
1
2
3

insert

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');
1
2

view(创建 更新 删除)

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
1
2
3
4

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
1
2
3
4

DROP VIEW view_name
1

order by desc/ DISTINCT

SELECT DISTINCT url FROM Websites
ORDER BY alexa DESC;
1
2

别名

• 列的别名

SELECT name AS n, country AS c
FROM Websites;
1
2

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;
1
2

• 表的别名

count

SELECT COUNT(DISTINCT site_id) 
AS nums 
FROM access_log;
1
2
3

SELECT COUNT(count) 
AS nums 
FROM access_log
WHERE site_id=3;
1
2
3
4

avg sum

SELECT AVG(count) AS CountAverage FROM access_log;
1

SELECT SUM(count) AS nums FROM access_log;
1