数据库语句

菜鸟总结

creat not null

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

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：组合两个表

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

题2 第二高的薪水

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

group by

group by

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

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;

between / in

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

delete

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

update

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

insert

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

view(创建 更新 删除)

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

DROP VIEW view_name

order by desc/ DISTINCT

SELECT DISTINCT url FROM Websites
ORDER BY alexa DESC;

别名

• 列的别名

SELECT name AS n, country AS c
FROM Websites;

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

• 表的别名

count

SELECT COUNT(DISTINCT site_id) 
AS nums 
FROM access_log;

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

avg sum

SELECT AVG(count) AS CountAverage FROM access_log;

SELECT SUM(count) AS nums FROM access_log;