SQL基础笔记
2018-06-17 23:25:10来源:未知 阅读 ()
CREATE TABLE celebs
(
id INTEGER,
name TEXT,
age INTEGER
); # 第一列id,数据类型整数;第二列name,数据类型文本;第三列age,数据类型整数
INSERT INTO celebs ( id, name, age)
VALUES ( 1, 'Alan Mathison Turing', 42); # 在celebs表最下方插入数据:id列为1,name列为Alan Mathion Turing,age列为42
SELECT
*
FROM
celebs; # 显示celebs表所有数据
UPDATE celebs
SET
age = 22
WHERE
id = 1; # 将celebs表中id=1的行的age改为22
ALERT TABLE celebs
ADD COLUMN twitter_handle TEXT AFTER id; # 在celebs表的id列后增加twitter_handle列
ALERT TABLE celebs
DROP email, DROP instagram; # 同时删除celebs的email和instagram两列
ALERT TABLE 'test'.'data'
CHANGE COLUMN 'Mobile' 'Mobile' BLOB NULL DEFAULT NULL; # 将表test.data的Mobile列的数据类型改为BLOB,该列数据默认为NULL
DELETE FROM celebs
WHERE
twitter_handle IS NULL; # 删除表celebs中twitter_handle为NULL的行
在Safe Update模式下,WHERE指令必须指定KEY列
DELETE FROM course
WHERE
Cno IN (8,9);
SELECT DISTINCT
genre
FROM
movies; # 查询movies表中genre列的所有不重复值
SELECT
*
FROM
movies
WHERE
imdb_rating > 8; # 查询movies表中imdb_rating大于8的行
SELECT
*
FROM
movies
WHERE
name LIKE ' Se_en';
SELECT
*
FROM
movies
WHERE
name LIKE 'a%';
SELECT
*
FROM
KPI
WHERE
count_process LIKE '%50\%%';
SELECT
*
FROM
movies
WHERE
name BETWEEN 'A' AND 'J'; # 查询movies中name以A至J开头的所有行
SELECT
*
FROM
movies
WHERE
year BETWEEN 1990 AND 2000; # 查询movies中year在1990至2000年间的行
SELECT
*
FROM
movies
WHERE
year BETWEEN 1990 AND 2000
AND genre = 'comedy'; # 查询movies中year在1990至2000间,且genre为comedy的行
SELECT
*
FROM
movies
WHERE
genre = 'comedy' OR year < 1980; # 查询movies中genre为comedy,或year小于1980的行
SELECT
*
FROM
movies
ORDER BY imdb_rating DESC; # 查询movies中的行,结果以imdb_rating降序排列
SELECT
*
FROM
movies
ORDER BY imdb_rating ASC
LIMIT 3; # 查询movies中的行,结果以imdb_rating升序排列,仅返回前3行
SELECT
COUNT(*)
FROM
fake_apps
WHERE
price = 0; # 返回fake_apps中price=0的行数
SELECT
price, COUNT(*)
FROM
fake_apps
WHERE
downloads > 2000
GROUP BY price; # 查询fake_apps表中downloads大于2000的行,将结果集根据price分组,返回price和行数
SELECT
category, SUM(downloads)
FROM
fake_apps
GROUP BY category;
SELECT
name, category, MAX(downloads)
FROM
fake_apps
GROUP BY category;
SELECT
name, category, MIN(downloads)
FROM
fake_apps
GROUP BY category;
SELECT
price, AVG(downloads)
FROM
fake_apps
GROUP BY price;
SELECT
price, ROUND(AVG(downloads), 2)
FROM
fake_apps
GROUP BY price;
CREATE TABLE artists
(
id INTEGER PRIMARY KET,
name TEXT
);
SELECT
*
FROM
albums
WHERE
artist_id = 3;
SELECT
albums.name, albums.year, artists.name
FROM
albums,
artists;
SELECT
*
FROM
albums
JOIN
artists ON albums.artist_id = artists.id; # INNER JOIN等价于JOIN,写JOIN默认为INNER JOIN
SELECT
*
FROM
albums
LEFT JOIN
artists ON albums.artist_id = artists.id;
SELECT
albums.name AS 'Album',
albums.year,
artists.name AS 'Artist'
FROM
albums
JOIN
artists ON albums.artist_id = artists.id
WHERE
albums.year > 1980;
SELECT
*
FROM
flights
WHERE
origin IN (SELECT
code
FROM
airports
WHERE
elevation > 2000);
SELECT
a.dep_month,
a.dep_day_of_week,
AVG(a.flight_count) AS average_flights
FROM
(SELECT
dep_month,
dep_day_of_week,
dep_date,
COUNT(*) AS flight_count
FROM
flights
GROUP BY 1 , 2 , 3) a
WHERE
a.dep_day_of_week = 'Friday'
GROUP BY 1 , 2
ORDER BY 1 , 2; # 返回每个月中,每个星期五的平均航班数量
SELECT
a.dep_month,
a.dep_day_of_week,
AVG(a.flight_distance) AS average_distance
FROM
(SELECT
dep_month,
dep_day_of_week,
dep_date,
SUM(distance) AS flight_distance
FROM
flights
GROUP BY 1 , 2 , 3) a
GROUP BY 1 , 2
ORDER BY 1 , 2; # 返回每个月中,每个周一、周二……至周日的平均飞行距离
SELECT
id
FROM
flights AS f
WHERE
distance > (SELECT
AVG(distance)
FROM
flights
WHERE
carrier = f.carrier); # the list of all flights whose distance is above average for their carrier
SELECT
carrier,
id,
(SELECT
COUNT(*)
FROM
flights f
WHERE
f.id < flights.id
AND f.carrier = flights.carrier) + 1 AS flight_sequence_number
FROM
flights; # 结果集为航空公司,航班id以及序号。相同航空公司的航班,id越大则序号越大
SELECT
item_name
FROM
legacy_products
UNION SELECT
item_name
FROM
new_products;
SELECT
AVG(sale_price)
FROM
(SELECT
id, sale_price
FROM
order_items UNION ALL SELECT
id, sale_price
FROM
order_items_historic) AS a;
SELECT category FROM new_products
INTERSECT
SELECT category FROM legacy_products;
SELECT DISTINCT
category
FROM
new_products
INNER JOIN
legacy_products USING (category);
或
SELECT DISTINCT
category
FROM
new_products
WHERE
category IN (SELECT
category
FROM
legacy_products);
SELECT
category, COUNT(*)
FROM
(SELECT
category
FROM
new_products UNION ALL SELECT
category
FROM
legacy_products) a
GROUP BY category
HAVING COUNT(*) > 1;
SELECT category FROM legacy_products
EXCEPT # 在Oracle中为MINUS
SELECT category FROM new_products;
SELECT DISTINCT
category
FROM
legacy_products
LEFT JOIN
new_products USING (category)
WHERE
new_products.category IS NULL;
或
SELECT DISTINCT
category
FROM
legacy_products
WHERE
category NOT IN (SELECT
category
FROM
new_products);
SELECT
COUNT(*)
FROM
flights
WHERE
arr_time IS NOT NULL
AND destination = 'ATL';
SELECT
CASE
WHEN elevation < 250 THEN 'Low'
WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium'
WHEN elevation >= 1750 THEN 'High'
ELSE 'Unknown'
END AS elevation_tier
, COUNT(*)
FROM airports
GROUP BY 1;
SELECT
state,
COUNT(CASE
WHEN elevation < 1000 THEN 1
ELSE NULL
END) AS count_low_elevaton_airports
FROM
airports
GROUP BY state;
SELECT
origin,
SUM(distance) AS total_flight_distance,
SUM(CASE
WHEN carrier = 'DL' THEN distance
ELSE 0
END) AS total_delta_flight_distance
FROM
flights
GROUP BY origin;
SELECT
origin,
100.0 * (SUM(CASE
WHEN carrier = 'DL' THEN distance
ELSE 0
END) / SUM(distance)) AS percentage_flight_distance_from_delta
FROM
flights
GROUP BY origin;
SELECT
state,
100.0 * COUNT(CASE
WHEN elevation >= 2000 THEN 1
ELSE NULL
END) / COUNT(elevation) AS percentage_high_elevation_airports
FROM
airports
GROUP BY 1;
或
SELECT
state,
100.0 * SUM(CASE
WHEN elevation >= 2000 THEN 1
ELSE 0
END) / COUNT(elevation) AS percentage_high_elevation_airports
FROM
airports
GROUP BY 1;
CREATE TABLE Orders (
OrderId INT NOT NULL,
ProductName VARCHAR(50) NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT NOW (),
PRIMARY KEY (OrderId)
);
SELECT
id,
carrier,
origin,
destination,
DATE_FORMAT(NOW(), '%Y-%c-%d %T') AS datetime
FROM
flights;
SELECT
DATE(delivery_time), TIME(delivery_time)
FROM
baked_goods;
SELECT
DATE_ADD(delivery_time,
INTERVAL '2 5:20:00' DAY_SECOND) AS package_time
FROM
baked_goods;
SELECT
id, GREATEST(cook_time, cool_down_time)
FROM
baked_goods;
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM
bakeries;
SELECT
state, GROUP_CONCAT(DISTINCT (city)) AS cities
FROM
bakeries
WHERE
state IN ('California' , 'New York', 'Texas')
GROUP BY state;
SELECT
CONCAT(CAST(distance AS CHAR), ' ', city)
FROM
bakeries;
SELECT
id,
REPLACE(ingredients,
'enriched_flour',
'flour')
FROM
baked_goods;
SELECT
DATE(ordered_at), ROUND(SUM(amount_paid), 2)
FROM
orders
JOIN
order_items ON orders.id = order_items.order_id
WHERE
name = 'kale-smoothie'
GROUP BY 1
ORDER BY 1;
SELECT
name, ROUND(SUM(amount_paid), 2)
FROM
order_items
GROUP BY name
ORDER BY 2 DESC;
SELECT
name,
ROUND(SUM(amount_paid) / (SELECT
SUM(amount_paid)
FROM
order_items) * 100.0,
2) AS PCT
FROM
order_items
GROUP BY 1
ORDER BY 2 DESC;
SELECT
*,
CASE name
WHEN 'kale-smoothie' THEN 'smoothie'
WHEN 'banana-smoothie' THEN 'smoothie'
WHEN 'orange-juice' THEN 'drink'
WHEN 'soda' THEN 'drink'
WHEN 'blt' THEN 'sandwich'
WHEN 'grilled-cheese' THEN 'sandwich'
WHEN 'tikka-masala' THEN 'dinner'
WHEN 'chicken-parm' THEN 'dinner'
ELSE 'other'
END AS category
FROM
order_items
ORDER BY id;
SELECT
CASE name
WHEN 'kale-smoothie' THEN 'smoothie'
WHEN 'banana-smoothie' THEN 'smoothie'
WHEN 'orange-juice' THEN 'drink'
WHEN 'soda' THEN 'drink'
WHEN 'blt' THEN 'sandwich'
WHEN 'grilled-cheese' THEN 'sandwich'
WHEN 'tikka-masala' THEN 'dinner'
WHEN 'chicken-parm' THEN 'dinner'
ELSE 'other'
END AS category,
ROUND(1.0 * SUM(amount_paid) / (SELECT
SUM(amount_paid)
FROM
order_items) * 100,
2) AS PCT
FROM
order_items
GROUP BY 1
ORDER BY 2 DESC;
SELECT
name,
ROUND(1.0 * COUNT(DISTINCT order_id) / COUNT(DISTINCT delivered_to),
2) AS reorder_rate
FROM
order_items
JOIN
orders ON orders.id = order_items.order_id
GROUP BY 1
ORDER BY 2 DESC;
SELECT
DATE(created_at), ROUND(SUM(price), 2)
FROM
purchases
GROUP BY 1
ORDER BY 1;
SELECT
DATE(created_at), ROUND(SUM(price), 2) AS daily_rev
FROM
purchases
WHERE
refunded_at IS NULL
GROUP BY 1
ORDER BY 1;
SELECT
DATE(created_at), COUNT(DISTINCT user_id) AS DAU
FROM
gameplays
GROUP BY 1
ORDER BY 1;
SELECT
DATE(created_at), platform, COUNT(DISTINCT user_id) AS DAU
FROM
gameplays
GROUP BY 1 , 2
ORDER BY 1 , 2;
SELECT
DATE(created_at),
ROUND(SUM(price) / COUNT(DISTINCT user_id), 2) AS ARPPU
FROM
purchases
WHERE
refunded_at IS NULL
GROUP BY 1
ORDER BY 1;
WITH daily_revenue AS
(
SELECT
date(created_at) AS dt,
ROUND(SUM(price), 2) AS rev
FROM
purchases
WHERE refunded_at IS NULL
GROUP BY 1
)
SELECT
*
FROM
daily_revenue
ORDER BY dt;
WITH daily_revenue AS (
SELECT
DATE(created_at) AS dt,
ROUND(SUM(price), 2) AS rev
FROM purchases
WHERE refunded_at IS NULL
GROUP BY 1
),
daily_players AS (
SELECT
DATE(created_at) AS dt,
COUNT(DISTINCT user_id) AS players
FROM gameplays
GROUP BY 1
)
SELECT
daily_revenue.dt,
daily_revenue.rev / daily_players.players
FROM daily_revenue
JOIN daily_players USING (dt);
SELECT
DATE(g1.created_at) AS dt, g1.user_id
FROM
gameplays AS g1
JOIN
gameplays AS g2 ON g1.user_id = g2.user_id
ORDER BY 1
LIMIT 100;
SELECT
DATE(g1.created_at) AS dt,
ROUND(100 * COUNT(DISTINCT g2.user_id) / COUNT(DISTINCT g1.user_id)) AS retention
FROM
gameplays AS g1
LEFT JOIN
gameplays AS g2 ON g1.user_id = g2.user_id
AND DATE(g1.created_at) = DATE(DATE_SUB(g2.created_at, INTERVAL 1 DAY))
GROUP BY 1
ORDER BY 1;
SELECT
COUNT(DISTINCT amount_paid)
FROM
order_items;
或
SELECT
COUNT(1)
FROM
(SELECT
1
FROM
order_items
GROUP BY amount_paid) a;
或
SELECT
SUM(1)
FROM
(SELECT
1
FROM
order_items
GROUP BY amount_paid) a;
分别是在运算和存储上的权衡:
SELECT
*
FROM
orders
WHERE
EXISTS( SELECT
1
FROM
orders o
JOIN
order_items i ON o.id = i.order_id);
MySqlStr="SELECT * FROM table WHERE";
IF(Age.Text.Lenght>0)
{
MySqlStr=MySqlStr+"Age="+"'Age.Text'";
}
IF(Address.Text.Lenght>0)
{
MySqlStr=MySqlStr+"AND Address="+"'Address.Text'";
}
SELECT
*
FROM
table
WHERE
1 = 0;
CREATE TABLE newtable AS SELECT * FROM
oldtable
WHERE
1 = 0;
CREATE TABLE newtable AS SELECT * FROM
oldtable;
SELECT
Customer, SUM(OrderPrice)
FROM
Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000;
SELECT
Customer, SUM(OrderPrice)
FROM
Orders
WHERE
Customer = 'Bush' OR Customer = 'Adams'
GROUP BY Customer
HAVING SUM(OrderPrice) > 1500;
SELECT
*
FROM
world.City
JOIN
world.Country ON (City.CountryCode = Country.Code)
WHERE ...
SELECT
film.title, film_id # film_id is not prefixed
FROM
film
JOIN
film_actor USING (film_id)
WHERE ...
SELECT
film.title, film.film_id # film.film_id is required here
FROM
film
JOIN
film_actor ON (film.film_id = film_actor.film_id)
WHERE ...
SELECT
ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM
Products;
SELECT
ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM
Products;
或
SELECT
ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM
Products;
SELECT
LCASE(first_name) AS first_name,
last_name,
city,
UCASE(state) AS state
FROM
bakeries;
SELECT
MID(column_name, start, length)
FROM
table_name;
SELECT
MID(state, 1, 3) AS smallstate
FROM
bakeries;
SELECT
LENGTH(City) AS LengthOfCity
FROM
bakeries;
SELECT
Sname
FROM
student
WHERE
EXISTS( SELECT
*
FROM
sc,
course
WHERE
sc.Sno = student.Sno
AND sc.Cno = course.Cno);
SELECT
Cname
FROM
course
WHERE
NOT EXISTS( SELECT
*
FROM
sc
WHERE
Sno = 200215123
AND Cno = course.Cno);
SELECT
Sname
FROM
student
WHERE
NOT EXISTS( SELECT
*
FROM
course
WHERE
NOT EXISTS( SELECT
*
FROM
sc
WHERE
Sno = student.Sno AND Cno = course.Cno));
SELECT
Cname
FROM
course
WHERE
NOT EXISTS( SELECT
*
FROM
student
WHERE
NOT EXISTS( SELECT
*
FROM
sc
WHERE
Cno = course.Cno AND Sno = student.Sno));
SELECT
DISTINCT Sno
FROM
sc scx
WHERE
NOT EXISTS( SELECT
*
FROM
sc scy
WHERE scy.Sno = 200215123
AND NOT EXISTS( SELECT
*
FROM
sc
WHERE
Sno = scx.Sno AND Cno = scy.Cno));
SELECT
Sname
FROM
student
WHERE
EXISTS( SELECT
*
FROM
sc,
course
WHERE
sc.Sno = student.Sno
AND sc.Cno = course.Cno
AND course.Cname = '操作系统');
SELECT
Sname
FROM
student
WHERE
Sno IN (SELECT
Sno
FROM
sc,
course
WHERE
sc.Sno = student.Sno
AND sc.Cno = course.Cno
AND course.Cname = '操作系统');
SELECT
*
FROM
my_contacts
WHERE
location = 'Grover\'s Mill, NJ';
SELECT
*
FROM
my_contacts
WHERE
location = 'Grover\'s Mill, NJ';
SELECT
word
FROM
dictionary
WHERE
word REGEXP '^[acg]|vity$';
SELECT
STR_TO_DATE(DATE_FORMAT(FLOOR(created_at / 500) * 500,
'%Y-%c-%d %T'),
'%Y-%c-%d %T') AS every_five_minutes,
COUNT(DISTINCT user_id) AS count_user_id
FROM
purchases
GROUP BY 1
ORDER BY 1;
SELECT
c2 AS '课程',
SUM(IF(c1 = '数据库', c3, NULL)) AS '数据库',
SUM(IF(c1 = '数学', c3, NULL)) AS '数学',
SUM(IF(c1 = '信息系统', c3, NULL)) AS '信息系统',
SUM(IF(c1 = '操作系统', c3, NULL)) AS '操作系统',
SUM(IF(c1 = '数据结构', c3, NULL)) AS '数据结构',
SUM(IF(c1 = '数据处理', c3, NULL)) AS '数据处理',
SUM(IF(c1 = 'PASCAL语言', c3, NULL)) AS 'PASCAL语言'
FROM
(SELECT
Cname AS c1, 'Cpno' AS c2, Cpno AS c3
FROM
course ca UNION ALL SELECT
Cname, 'Ccredit' AS c2, Ccredit AS c3
FROM
course cb) AS row_column_convert
GROUP BY 1
ORDER BY c2 DESC;
或
SELECT
c2 AS '课程',
SUM(CASE
WHEN c1 = '数据库' THEN c3
ELSE NULL
END) AS '数据库',
SUM(CASE
WHEN c1 = '数学' THEN c3
ELSE NULL
END) AS '数学',
SUM(CASE
WHEN c1 = '信息系统' THEN c3
ELSE NULL
END) AS '信息系统',
SUM(CASE
WHEN c1 = '操作系统' THEN c3
ELSE NULL
END) AS '操作系统',
SUM(CASE
WHEN c1 = '数据结构' THEN c3
ELSE NULL
END) AS '数据结构',
SUM(CASE
WHEN c1 = '数据处理' THEN c3
ELSE NULL
END) AS '数据处理',
SUM(CASE
WHEN c1 = 'PASCAL语言' THEN c3
ELSE NULL
END) AS 'PASCAL语言'
FROM
(SELECT
Cname AS c1, 'Cpno' AS c2, Cpno AS c3
FROM
course ca UNION ALL SELECT
Cname, 'Ccredit' AS c2, Ccredit AS c3
FROM
course cb) AS row_column_convert
GROUP BY 1
ORDER BY c2 DESC;
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- MySQL replace函数怎么替换字符串语句 2020-03-09
- PHP访问MySQL查询超时怎么办 2020-03-09
- mysql登录时闪退 2020-02-27
- MySQL出现1067错误号 2020-02-27
- mysql7.x如何单独安装mysql 2020-02-27
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash