mysql常用

MySQL查询

select name from table_name；

select distinct name from table_name;

select name from table_name where is=1;

select name from table_name order by age desc；#降续

select name from table_name limit 3；

SELECT * FROM table_name WHERE date_column< '2020-01-01';

上述语句将检索date_column列值小于2020年1月1日的行。

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites INNER JOIN access_log
ON Websites.id=access_log.site_id; 
# "Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"


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;

MySQL增

insert into Mans (id,name,age,will) values (1,"李明",32,"象棋,游泳");

MySQL删

delete from Womans where id=3;

MySQL改

update Mans set name="TOM",age=22 where id=1;

like 'G%'--以字母 "G" 开始的所有
between 1 and 20 --介于 1 和 20 之间
in ('Google','菜鸟教程') --"Google" 或 "菜鸟教程" 
country='CN'and alexa > 50  --国家为 "CN" 且alexa排名大于 "50" 
country='CN'or alexa > 50  --国家为 "CN" 或alexa排名大于 "50" 
ID int NOT NULL  --强制 "ID" 列不为空
primary key
default
AVG(age) - 返回平均值
MAX(age) - 返回最大值
MIN(age) - 返回最小值
SUM(age) - 返回总和
count(name)  -返回个数
group by

group by 和 having

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id; #按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;#按Websites.name分组后，多表求access_log.aid个数

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200; #HAVING 子句可以筛选分组后的各组数据