SQL常用语句及操作

1.建表，并设置主键自增与外键

	create table user_week_goal(
	week_goal_index int primary key identity(1,1),
	user_acc varchar(50) not null,
	goal_date varchar(255) not null,
	goal_content varchar(max),
	goal_iscomplete bit,
	foreign key(user_acc) references user_info(user_acc)
	)

2.设置某表某列的默认值（DEFAULT的括号内为默认值）

ALTER TABLE user_week_goal ADD  CONSTRAINT test_week_goal_stamp  DEFAULT (0) FOR goal_iscomplete

3.清空某个表

	truncate table 表名称                     
	eg: truncate  table dbo.Sys_Test   

4.update语句：

UPDATE Person
SET Address = 'Zhongshan 23', City = 'Nanjing'

5.insert语句

INSERT INTO user_memo (user_acc, memo_set_date, memo_content)
VALUES ('123', '2019-11-11', '今晚取快递')

6.删除某个字段的默认值（其约束名为test_week_goal_stamp）

ALTER TABLE user_week_goal
	DROP CONSTRAINT test_week_goal_stamp

7.更改某个字段的类型

alter table user_week_goal alter column goal_iscomplete int not null

8.给表增加一个字段

ALTER TABLE 表名
	ADD COLUMN 字段名 VARCHAR(20) NULL

9.修改字段名

exec sp_rename 'table.oldname',newname

10.给表中某个字段增加外键约束(在从表上建立)

ALTER TABLE contract_list
	ADD FOREIGN KEY (contract_master) REFERENCES user_info (user_acc);

添加外键约束并设置级联删除，

ALTER TABLE home_shown
	ADD FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE

参考: https://www.cnblogs.com/ouyangping/p/6500626.html

11.给表中某个字段增加唯一约束

ALTER TABLE USER
	ADD UNIQUE (username)

解释：alter table 表名 add constraint 约束名（随意起） unique(列名)

12.删除某个表的唯一约束

ALTER TABLE contract_list
	DROP CONSTRAINT uqurl

（最后为约束名）

13.三个表的关联查询

SELECT a.*, b.Location, b.entity_status_id, c.name
FROM out_in_storage a, fixure_entity b, fixure_definition c
WHERE a.fixure_id = b.fixure_id
	AND b.code = c.code

14.模糊查询

SELECT *
FROM user_sign_in
WHERE user_acc = '2019200503'
	AND sign_time LIKE '2019-12-10%'

15.将两个字段查询拼接到一起

SELECT Workcell, RegDate, Location, UsedCount, UsedFor
	, PartNo, Model, Name
	, code + '-' + seqID AS codeTest
FROM fixure_test

16.对查询结果排序

​ select order by 排序: varchar 类型数据将其按int类型排序, varchar 类型字段排序, 会將数字当成字符串来处理. 排序规则一般是从左到右一位位来比较. +0之后 就转化成INT 类型排序

eg: 
SELECT *
FROM fixure_detail_view
ORDER BY fixure_id + 0

参考：https://www.cnblogs.com/stsinghua/p/6420295.html

17.按多个条件排序

SELECT *
FROM fixure_detail_view
ORDER BY entity_status_id, fixure_id + 0

18.插入查询的语句，将

INSERT INTO user_sign_in
SELECT user_acc = '2019210857', sign_date, sign_time, sign_weekday
	, sign_position, sign_type, sign_time, sign_weeks, sign_week_day
FROM user_sign_in
WHERE user_acc = '2019200509'
	AND sign_time LIKE '2019-12-10%'

19.删除某个表的多条数据

DELETE FROM contract_list
WHERE contract_master = '123'

20.将查询结果插入到另一张表中

INSERT INTO contract_list (contract_master, contract_url, contract_born_time, contract_signature_position, contract_group_id
	, contract_img_index)
SELECT '499', contract_url, contract_born_time, contract_signature_position, contract_group_id
	, contract_img_index
FROM contract_list
WHERE contract_master = '456'

​ PS：更改字段的值写在select语句中，要插入的字段写出来

21.不等于号：<>

22.update更新使数据库中某个字段值加一

UPDATE student
SET score = score + 1
WHERE id = 1

23.多个条件的模糊查询，where 子句中and和or可以同时使用

SELECT *
FROM contract_group_list
WHERE user_acc = '123'
	AND (receiver_name LIKE '%李四%'
		OR contract_receiver LIKE '%李四%')

此例查询receiver_name或contract_receiver字段包含李四，user_acc为123的所有记录

24.查询字段值为空(null)的所有记录

SELECT *
FROM abandon_record_view
WHERE result IS NULL
ORDER BY id

25.创建表并建立复合主键

CREATE TABLE role_action (
	role_id int,
	action_id int,
	role_name nvarchar(255),
	action_name nvarchar(255),
	FOREIGN KEY (role_id) REFERENCES role (role_id),
	FOREIGN KEY (action_id) REFERENCES action (action_id),
	PRIMARY KEY (name, birth, sex, class)
)

​

26.关于外键约束

CASCODE:从父表删除或删除且自动删除或更新子表中匹配的行；

SET NULL:从父表删除或更新行，并设置子表的外键列为NULL，如果使用该选项，必须保证子列表没有指定NOT NULL.

RESTRICT:拒绝父表的删除或更新操作。

NO ACTION:与RESTRICT相同。

参考：https://www.cnblogs.com/moxuexiaotong/p/6586508.html

27.问题：使用语句 insert into article select * from article_bin where id=22时报错：仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表’article’中的标识列指定显式值

原因：该表的主键设置了自增长
解决办法：

1、每次插入时都要先使用语句 set IDENTITY_INSERT article on 允许插入
2、再改语句为

INSERT INTO article (id, title, category, content, author_acc
	, author_name, create_time, last_edit_time, page_view, upload_names
	, save_names)
SELECT *
FROM article_bin
WHERE id = 22

​ 要指定显式的列才能插入
​

28.sql表的全字段条件搜索

SELECT *
FROM hfw_dd
WHERE concat(需要搜索的字段) LIKE CONCAT(关键字)

例子1:全字段关键字搜索’呵呵’

SELECT *
FROM hfw_dd
WHERE CONCAT(id, name, china, english) LIKE CONCAT('%', '呵呵', '%')

例子2:全字段关键字搜索’小’,在结果的基础上再搜索 70

SELECT *
FROM hfw_dd
WHERE CONCAT(id, name, china, english) LIKE CONCAT('%', '小', '%', '%', 70, '%')

参考：https://blog.csdn.net/cheerful_smalltail/article/details/89967568

29.创建视图的语句

示例1：

CREATE VIEW role_action_view
AS
SELECT a.*, b.*
FROM role a, action b, role_action c
WHERE a.role_id = c.role_id
	AND b.action_id = c.action_id

示例2：

CREATE VIEW repair_record_view
AS
SELECT a.*, b.Location, b.entity_status_id, c.name
FROM repair a, fixure_entity b, fixure_definition c
WHERE a.fixure_id = b.fixure_id
	AND b.code = c.code

30.设置某个字段的值+1(字段类型为int，且已有初值)

UPDATE article
SET page_view = page_view + 1
WHERE id = 5

​

31.SQLserver给已有的某个字段设置默认值

ALTER TABLE article ADD  CONSTRAINT page_view_0  DEFAULT (0) FOR page_view

​ (其中constraint后为约束名，可自取，for后为字段名)

32.SQL分组获取记录的第一条数据(单个字段去重并同时取其其他字段的值)

​ ——PARTITION BY
​ 先用这个建立新字段new_index，然后再内嵌进行select查询

SELECT id, detail_title, detail_href, download_href, download_time
	, ROW_NUMBER() OVER (PARTITION BY detail_title ORDER BY id DESC) AS new_index
FROM user_download_record
WHERE user_acc = '123'

SELECT id, detail_title, detail_href, download_href, download_time
FROM (
	SELECT id, detail_title, detail_href, download_href, download_time
		, ROW_NUMBER() OVER (PARTITION BY detail_title ORDER BY id DESC) AS new_index
	FROM user_download_record
	WHERE user_acc = '123'
) t
WHERE t.new_index = 1
ORDER BY id DESC

参考：https://blog.csdn.net/yiyelanxin/article/details/72801005