介于我是mac系统，用不了sql server数据库，只能按照网课内容走，刷题再用sql server刷一遍

datacamp有sql server认证课程，按照上面学习
只记录与mysql有出入的语法

1.top percent

image.png

Q:Return all columns, but only include the top half of the table - in other words, return 50 percent of the rows.

SELECT 
  top(50) percent * 
FROM 
  eurovision;

mysql中没有top用法，需要用limit

2.charindex()

用法
https://img.haomeiwen.com/i10450029/7cefebadc01c9102.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240

3.substring()

image.png

Now we use SUBSTRING to return everything after Weather for the first ten rows. The start index here is 15, because the CHARINDEX for each row is 8, and the LEN of Weather is 7.

SELECT TOP (10)
  description, 
  CHARINDEX('Weather', description) AS start_of_string, 
  LEN ('Weather') AS length_of_string, 
  SUBSTRING(
    description, 
    15, 
    LEN(description)
  ) AS additional_description 
FROM 
  grid
WHERE description LIKE '%Weather%';

4.information_schema

information_schema is a meta-database that holds information about your current database. information_schema has multiple tables you can query with the known SELECT * FROM syntax:

tables: information about all tables in your current database
columns: information about all columns in all of the tables in your current database

5.数据类型

5.1 date 类型

• datetime
• datetime2
• smalldatetime
• date
• time
• datetimeoffset

• timestamp

  
  date

5.2 character 字符串：

• char(n)
• varchar(n)
• varchar(max)
• text
  character

5.3 unicode字符串：

• nchar(n):
• nvarchar(n)
• nvarchar(max)
• ntext
  unicode

5.4 binary 类型

• bit
• binary(n)
• varbinary(n)
• varbinary(max)
• image

binary

5.5 number 类型

• tinyint
• smallint
• int
• bigint
• decimal(p,s)
• numeric(p,s)
• smallmoney
• money
• float(n)
• real

number

numeric(3,2) 代表可以存3位数，小数位最多存2位数

5.6 其他类型

其他数据类型

6. insert into...select

image.png

7.update 用法

用法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

例子

SELECT 
  title 
FROM 
  album 
WHERE 
  album_id = 213;
-- UPDATE the title of the album
UPDATE 
  album 
SET 
  title = 'Pure Cult: The Best Of The Cult' 
WHERE 
  album_id = 213;

8.变量

declare

8.1 declare 数字变量

declare @test_int int

8.2 declare var变量

declare @my_artist varchar(100)

set

DECLARE @region VARCHAR(10)

SET @region = 'RFC'

用法

DECLARE @region VARCHAR(10)

SET @region = 'RFC'

SELECT description,
     nerc_region,
     demand_loss_mw,
     affected_customers
FROM grid
WHERE nerc_region = @region;

9.alter用法

9.1 给数据库添加数据

ALTER TABLE table_name
ADD COLUMN column_name data_type;

例子：

alter table professors
add column university_shortname text;

9.2 重命名

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

9.3 删除

ALTER TABLE table_name
DROP COLUMN column_name;

9.4 更改数据特征的属性

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE varchar(10)

9.5 设置空值/非空值

ALTER TABLE table_name
ALTER COLUMN colun_name
SET NOT NULL      /DROP NOT NULL;

9.6 设置限制

CREATE TABLE table_name(
column_name UNIQUE);

ALTER TABLE table_name
ADD CONSTRAINT some_name UNIQUE(column_name);

9.7 添加外键

ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table_name (other_column_name)

10.数据标准化

从一个表格移动数据到与之相关的小表格中，为了减少数据冗余
例子：移动不重复的数据

insert into

11. using substring

为了节省varchar的空间？

If you don't want to reserve too much space for a certain varchar column, you can truncate the values before converting its type.

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE varchar(x)
USING SUBSTRING(column_name FROM 1 FOR x)

例子：

ALTER TABLE professors 
ALTER COLUMN firstname 
TYPE varchar(16)
using substring(firstname from 1 for 16)

12. 外键的约束 Referential integrity

• no action
• cascade
• set null
• set default
• restrict

例子

ADD CONSTRAINT affiliations_organization_id_fkey FOREIGN KEY (organization_id)
REFERENCES organizations (id) ON DELETE CASCADE;

参考资料

13.order by 和fetch的用法

ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY

• The OFFSET clause specifies the number of rows to skip before starting to return rows from the query. The offset_row_count can be a constant, variable, or parameter that is greater or equal to zero.
• The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed. The offset_row_count can a constant, variable or scalar that is greater or equal to one.
• The OFFSET clause is mandatory while the FETCH clause is optional. Also, the FIRST and NEXT are synonyms respectively so you can use them interchangeably. Similarly, you can use the FIRST and NEXT interchangeably.

offset 和fetch必须用在order by后面
如果要用fetch,必须要用offset

例子：选择前十个之后的后面十个

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price,
    product_name 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

例子：选择top 10，不用top()

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price DESC,
    product_name 
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS ONLY;

In this example, the ORDER BY clause sorts the products by their list prices in descending order. Then, the OFFSET clause skips zero row and the FETCH clause fetches the first 10 products from the list.

参考资料

---

14. 用 CREATE TABLE AS 创建一个已知数据表格

14.1 Create Table - By Copying all columns from another table

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

14.2 Create Table - By Copying selected columns from another table

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

14.3 只复制数据column的结构，不复制数据

How can I create a SQL table from another table without copying any values from the old table?

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);

参考

15. sql server中的surrogate key 和natural key

• A surrogate key is a system generated (could be GUID, sequence, etc.) value with no business meaning that is used to uniquely identify a record in a table. The key itself could be made up of one or multiple columns.

• A natural key is a column or set of columns that already exist in the table (e.g. they are attributes of the entity within the data model) and uniquely identify a record in the table. Since these columns are attributes of the entity they obviously have business meaning.

Natural Key Pros

• Key values have business meaning and can be used as a search key when querying the table
• Column(s) and primary key index already exist so no disk extra space is required for the extra column/index that would be used by a surrogate key column
• Fewer table joins since join columns have meaning. For example, this can reduce disk IO by not having to perform extra reads on a lookup table

Natural Key Cons

• May need to change/rework key if business requirements change. For example, if you used SSN for your employee as in the example above and your company expands outside of the United States not all employees would have a SSN so you would have to come up with a new key.
• More difficult to maintain if key requires multiple columns. It's much easier from the application side dealing with a key column that is constructed with just a single column.
• Poorer performance since key value is usually larger and/or is made up of multiple columns. Larger keys will require more IO both when inserting/updating data as well as when you query.
• Can't enter record until key value is known. It's sometimes beneficial for an application to load a placeholder record in one table then load other tables and then come back and update the main table.
• Can sometimes be difficult to pick a good key. There might be multiple candidate keys each with their own trade-offs when it comes to design and/or performance.

Surrogate Key Pros

• No business logic in key so no changes based on business requirements. For example, if the Employee table above used a integer surrogate key you could simply add a separate column for SIN if you added an office in Canada (to be used in place of SSN)
• Less code if maintaining same key strategy across all entities. For example, application code can be reused when referencing primary keys if they are all implemented as a sequential integer.
• Better performance since key value is smaller. Less disk IO is required on when accessing single column indexes.
• Surrogate key is guaranteed to be unique. For example, when moving data between test systems you don't have to worry about duplicate keys since new key will be generated as data is inserted.
• If a sequence used then there is little index maintenance required since the value is ever increasing which leads to less index fragmentation.

Surrogate Key Cons

• Extra column(s)/index for surrogate key will require extra disk space
• Extra column(s)/index for surrogate key will require extra IO when insert/update data
• Requires more table joins to child tables since data has no meaning on its own.
• Can have duplicate values of natural key in table if there is no other unique constraint defined on the natural key
• Difficult to differentiate between test and production data. For example, since surrogate key values are just auto-generated values with no business meaning it's hard to tell if someone took production data and loaded it into a test environment.
• Key value has no relation to data so technically design breaks 3NF
• The surrogate key value can't be used as a search key
• Different implementations are required based on database platform. For example, SQL Server identity columns are implemented a little bit different than they are in Postgres or DB2.

参考

16.CASE用法

CASE
WHEN Boolean_expression THEN result_expression [...n]
[ELSE else_result_expression]
END

17.window function

参考链接：https://www.cnblogs.com/csdbfans/p/3504845.html

练习
Ultimate Power
Sometimes you might want to 'save' the results of a query so you can do some more work with the data. You can do that by creating a temporary table that remains in the database until SQL Server is restarted. In this final exercise, you'll select the longest track from every album and add that into a temporary table which you'll create as part of the query.

Create a temporary table called maxtracks. Make sure the table name starts with #.
Join album to artist using artist_id, and track to album using album_id.
Run the final SELECT statement to retrieve all the columns from your new table.

SELECT  album.title AS album_title,
  artist.name as artist,
  MAX(track.milliseconds / (1000 * 60) % 60 ) AS max_track_length_mins
# Name the temp table #maxtracks
INTO #maxtracks
FROM album
# Join album to artist using artist_id
INNER JOIN artist ON album.artist_id = artist.artist_id
# Join track to album using album_id
join track on album.album_id = track.album_id
GROUP BY artist.artist_id, album.title, artist.name,album.album_id
# Run the final SELECT query to retrieve the results from the temporary table
SELECT album_title, artist, max_track_length_mins
FROM  #maxtracks
ORDER BY max_track_length_mins DESC, artist;

练习

create table weather( 
temperature integer,
wind_apeed text);

问他们的相乘是多少

答案

select temperature * cast (wind_speed as integer) as wind_chill
from weather;

要用cast转换成一个data type

reference:
http://www.w3school.com.cn/sql/sql_datatypes.asp