[MySQL] 01- Basic sql

Select * from emp where sal > 2000 and sal < 3000;
Select * from emp where sal > 2000 or comm > 500;
select * from emp where not sal > 1500;

Select * from emp where ename like 'M%';
Select * from emp where ename like '[CK]ars[eo]n';# 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）

SELECT * FROM websites WHERE name REGEXP '^[GFs]';
SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';# 选取 name 不以 A 到 H 字母开头的网站。第一个^是开头的意思；第二个^表否定。

SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';
SELECT * FROM Websites WHERE name NOT BETWEEN 'A' AND 'H';SELECT * FROM access_log WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

CREATE DATABASE my_db;CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

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

SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";

select * from Table A inner join Table B on Table A.id=Table B.id
select * from Table A left join Table B on Table A.id=Table B.id
select * from Table A right join Table B on Table A.id=Table B.id
select * from Table A full outer join Table B on Table A.id=Table B.id

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

INSERT INTO websites (name, country) SELECT app_name, country FROM apps;

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
OrderDate date DEFAULT GETDATE()
)

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

ALTER TABLE Persons AUTO_INCREMENT=100

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ALTER TABLE Persons
DROP INDEX uc_PersonID# 撤销 UNIQUE

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

ALTER TABLE Persons
DROP PRIMARY pk_PersonID# 撤销 PRIMARY KEY

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,

PRIMARY KEY (O_Id),# 这里没有命名
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

ALTER TABLE Persons
ADD DateOfBirth date

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

ALTER TABLE Persons
DROP CHECK chk_Person

CREATE INDEX PIndex ON Persons (LastName, FirstName)

SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);

SELECT SUM(count) AS nums FROM access_log;

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

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

SELECT MAX(alexa) AS max_alexa FROM Websites;
SELECT MIN(alexa) AS min_alexa FROM Websites;

select 类别, sum(数量) as 数量之和
from A
group by 类别

SELECT websites.name, COUNT(access_log.aid) AS nums FROM access_log 【GROUP BY site_id】
LEFT JOIN websites 【websites只是个给出site_id对应名字的参考表】
ON access_log.site_id=websites.id
GROUP BY websites.name;

select 类别, sum(数量) as 数量之和 from A
group by 类别
having sum(数量) > 18

select 类别, SUM(数量)from A
where 数量 gt;8
group by 类别
having SUM(数量) gt; 10

select *
from A
where 数量>8
compute max(数量),min(数量),avg(数量)

select *
from A
where 数量>8
order by 类别
compute max(数量),min(数量),avg(数量) by 类别

SELECT UCASE(name) AS site_title, url FROM Websites;SELECT LCASE(name) AS site_title, url FROM Websites;

SELECT MID(name,1,4) AS ShortTitle FROM Websites;

SELECT name, LENGTH(url) as LengthOfURL FROM Websites;

mysql> select ROUND(1.298, 1);
-> 1.3
mysql> select ROUND(1.298, 0);
-> 1

SELECT name, url, Now() AS date FROM Websites;

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date FROM Websites;

#!/usr/bin/env python3
# -*- coding: utf-8 -*-import pandas as pd# Please update .csv path here before running this .py.
str_path = './data_analyst_sample_data.csv'
cols = ["week_sold",'price','num_sold','store_id','product_code','department_name']dataset=pd.read_csv(str_path,header=None, sep=',',names=cols)#######
# Q1
#######
total_price = 0.0
for i in range(1,len(dataset)):
if (dataset['department_name'][i] == 'BEVERAGE'):
each_price = float(dataset['price'][i]) * float(dataset['num_sold'][i])
each_price = round(each_price, 2)
total_price += each_priceprint("Total price is %.2f" % total_price)
print("")###############################################################################
# SELECT SUM(price*num_sold) AS sales FROM <table name> where department_name='BEVERAGE'
###############################################################################from collections import Countertotal_counts = Counter()
for i in range(1,len(dataset)):
product_code = dataset['product_code'][i]
total_counts[product_code] += 1#print(total_counts)
print("There are %d unique products in the store." % len(total_counts) )
print("")###############################################################################
# SELECT product_code, COUNT(*) FROM <table name> GROUP BY product_code
######################################################################################
# Q2
#######
from collections import Counter
from decimal import *
#from datetime import datetime#def convert_to_month(week_sold):
# time_str = week_sold
# time = datetime.strptime(time_str, '%Y-%m-%d')
# return time.strftime('%Y-%m')total_counts = Counter()
for i in range(1,len(dataset)):
# month_sold = convert_to_month(dataset['week_sold'][i])
store_id = dataset['store_id'][i]
product_code = dataset['product_code'][i] each_price = float(dataset['price'][i]) * float(dataset['num_sold'][i])
total_counts[store_id, product_code] += Decimal(each_price/3).quantize(Decimal('0.00'))print(total_counts)###############################################################################
# SELECT store_id, product_code, round(SUM(price*num_sold)/3.0, 2) FROM <table name> GROUP BY store_id
################################################################################ Save
import csv
row = ['store_id', 'product_code', 'average_monthly_revenue']
out = open("result.csv", "a", newline = "")
csv_writer = csv.writer(out, dialect = "excel")
csv_writer.writerow(row)row = ['store_id', 'product_code', 'average_monthly_revenue']for k, v in total_counts.items():
row = [k[0], k[1], v]
out = open("result.csv", "a", newline = "")
csv_writer = csv.writer(out, dialect = "excel")
csv_writer.writerow(row)

SELECT
CASE
WHEN A + B > C THEN
CASE
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
WHEN A != B AND B != C OR A != C THEN 'SCALENE'
END
ELSE
'Not A Triangle'
END
FROM TRIANGLES;

SELECT id, (case sex
when ' ' then 'bbbbb'
when null then 'aaaaa'
else sex
end) as sex
FROM aa;

SELECT concat( NAME, concat("(",concat( substr(OCCUPATION,1,1), ")")) ) FROM OCCUPATIONS ORDER BY NAME ASC;

SELECT "There are a total of ", count(OCCUPATION), concat(lower(occupation),"s.") FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY count(OCCUPATION), OCCUPATION ASC

set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
select case when Occupation='Doctor' then (@r1:=@r1+1)
when Occupation='Professor' then (@r2:=@r2+1)
when Occupation='Singer' then (@r3:=@r3+1)
when Occupation='Actor' then (@r4:=@r4+1)
end as RowNumber,
case when Occupation='Doctor' then Name end as Doctor,
case when Occupation='Professor' then Name end as Professor,
case when Occupation='Singer' then Name end as Singer,
case when Occupation='Actor' then Name end as Actor
from OCCUPATIONS
order by Name
) Temp
group by RowNumber;

SET sql_mode = '';
SELECT Start_Date, End_Date
FROM
(SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
(SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date