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