KeepNotes blog

Stay hungry, Stay Foolish.

0%

初学SQL（二）

Posted on Edited on In ,
Symbols count in article: 2.8k Reading time ≈ 3 mins.

通过DataCamp数据分析在线学习平台中Introduction to SQL Server教程的学习，对于初学SQL（一）进行知识点的补充，都是些简单的用法。。。 > DataCamp作为一个“learning by doing”的数据分析学习平台，可以在线交互式练习，方便入门数据分析；DataCamp提供了交互式的R、Python、Sheets、SQL和Shell的课程，覆盖了数据科学，统计学和机器学习上的所有话题

TOP 语句可用于返回对应数目的行

select top(100) * from eurovision
select top 50 percent * from eurovision

在 Microsoft SQL Server中，日期的默认格式是YYYY-MM-DD，即Year-Month-Day

select description,affected_customers from  grid where event_date = '2013-12-22';

如果ORDER BY后面不接列名，则相当于order所有返回结果列

select * from grid group by

LEN 函数可以返回字符串字段的长度

select len (description) as description_length from grid

LEFT和RIGHT 函数可以返回字符串字段前/后的字符串

select left(description, 25) as first_25_left from grid

CHARINDEX 函数可以返回字符或者字符串在另一个字符串中的起始位置

select description, CHARINDEX('Weather', description)  from grid where description LIKE '%Weather%'

SUBSTRING 函数可以截取字符串中的指定位置的字符串

select SUBSTRING(description, 15, len(description)) from grid where description LIKE '%Weather%'

Joining tables

INNER JOIN 返回key匹配上的行

select album_id,title,name as artist from album 
  inner join artist on album.artist_id = artist.artist_id

LEFT JOIN 返回左表所有的行和右表key匹配上的行

select invoiceline_id,unit_price,quantity,billing_state from invoiceline 
  left join invoice on invoiceline.invoice_id = invoice.invoice_id

RIGHT JOIN 返回右表所有的行和左表key匹配上的行

select album.album_id,title,album.artist_id,artist.name as artist from album 
  inner join artist on album.artist_id = artist.artist_id 
  right join track on album.album_id = track.album_id
where album.album_id in (213,214)

UNION 语句用于合并两个或多个SELECT语句的结果，默认UNION 语句返回不重复的结果。如果允许重复，则用UNION ALL

select album_id as ID,title as description,'Album' as Source from album
union
select artist_id as ID,name as description,'Artist'  as Source from artist

CREATE/INSERT/UPDATE/DELETE

CREATE TABLE 语句用于创建数据表，而CREATE DATABASE 则是用于创建数据库

CREATE TABLE results (
    track VARCHAR(200),
    artist VARCHAR(120),
    album VARCHAR(160),
    track_length_mins INT,
    )

INSERT INTO 语句用于向表中插入数据

INSERT INTO tracks (track, album, track_length_mins)
VALUES ('Basket Case', 'Dookie', 3)

Update 语句用于修改表中的数据

UPDATE 
  album
SET 
  title = 'Pure Cult: The Best Of The Cult'
WHERE album_id = 213

DELETE 语句用于删除表中的行

DELETE FROM 
  album 
WHERE 
  album_id = 1

DECLARE and SET a variable

SQL中用declare申明变量，SET对变量赋值，然后select调用

DECLARE @region VARCHAR(10)
SET @region = 'RFC'
select description,
       nerc_region,
       demand_loss_mw,
       affected_customers
from grid
where nerc_region = @region

也可以多变量申明，然后一起调用

-- Declare your variables
DECLARE @start DATE
DECLARE @stop DATE
DECLARE @affected INT;
-- SET the relevant values for each variable
SET @start = '2014-01-24'
SET @stop  = '2014-07-02'
SET @affected =  5000 ;

SELECT 
  description,
  nerc_region,
  demand_loss_mw,
  affected_customers
FROM 
  grid
where event_date between @start AND @stop
AND affected_customers >= @affected

创建局部临时表，表格需以#开头

# 方法1
select * into #tmpStudent from student
# 方法2
create table #tmpStudent(Tid int,Name varchar(50),Age int)
insert into #tmpStudent values('xiaowang',25)

本文出自于http://www.bioinfo-scrounger.com转载请注明出处

Related Posts