use dbName --使用当前数据库
create database dbName --创建数据库
drop database dbName --删除数据库
--创建表
Create table Product(
Product_id char(4) not null,
Product_name vachar(100),
Primary key (product_id)
)
Alter table aaa add aaa_Name varchar(100) --添加字段
Drop table shop --删除表
truncate table shop
delete table shop
Alter table shop add age int --向shop表添加age字段
Alter table shop drop column age --向shop表删除age字段
Insert into shop values(‘001’,’商店’) --向表shop中添加数据 001 商店
sp_rename 'shop','shop1' 更改表shop名为shop1
Select distinct age from shop --去除重复
Select shop_id,shop_name from shop where shop_name is null--选取为空
Select shop_id,shop_name from shop where shop_name is not null--选取不为空
Select count(*),count(age) from shop --行数
Select sum(age) from shop --合计
Max最大值 min最小值 avg平均值
书写顺序
-
SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →
-
HAVING 子句 → 6. ORDER BY 子句
Sql执行顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
事物
begin tran
begin
update product set sale_price=123 where product_id=1
update aaa set Uid=123 where ID=1
if(@@error<>0)
begin
rollback
end
else
begin
commit tran
end
end
select LOWER(product_name) from product --将英文字母转换为小写
select UPPER(product_name) from product--将英文字母转换为大写
SUBSTRING(对象字符串，截取的起始位置，截取的字符数) 截取字符串
REPLACE——字符串的替换
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;--获取当前日期2020-10-16
SELECT CURRENT_TIMESTAMP;--获取当前日期和时间 2020-10-16 15:47:03.197
SELECT CURRENT_TIMESTAMP, --截取日期元素
DATEPART(YEAR , CURRENT_TIMESTAMP) AS year, --年
DATEPART(MONTH , CURRENT_TIMESTAMP) AS month,
DATEPART(DAY , CURRENT_TIMESTAMP) AS day,
DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour,
DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的。
1.--top+not in
select TOP 10 * from A WHERE ID NOT IN(SELECT TOP 30 ID FROM A)
2.--row_number over+between and
select * from ( select ROW_NUMBER() over(order by ID asc) as row ,* from A) as atable
where row between 1 and 10
linq语句分页
var list= (from e in db.Musics select e).ToList();
var reslist = list.Skip((page-1)*10).Take(limit).ToList();
case搜索表达式
Case when then
select product_name,
case when product_type='衣服'
then 'A:' + product_type
when product_type='办公用品'
then 'B:' + product_type
when product_type='厨房用具'
then 'C:'+product_type
else null
end as abc_product_type
from product
SELECT product_id, product_name
FROM Product
UNION --合并两张表的数据
SELECT product_id, product_name
FROM Product2;
将已有编号方式转换为新的方式并统计 case when then else end sum group by
在“统计结果”这张表中，“四国”对应的是表 PopTbl 中的“德岛、 香川、爱媛、高知”， “ 九州”对应的是表 PopTbl 中的“福冈、佐贺、长崎”。
Select case pref_name
When ’德岛’then ’四国’
When ’香川’then ’四国’
When ’福冈’ then ’九州’
When’佐贺’then‘九州’
Else ‘其他’ end，sum(population)
from PopTbl
Group by case pref_name
When ’德岛’then ’四国’
When ’香川’then ’四国’
When ’福冈’then ’九州’
When ’佐贺’then‘九州’
Else ‘其他’ end
select pref_name,
sum(case when sex='1' then population else 0 end) as '男',
sum(case when sex='2' then population else 0 end) as '女'
from PopTbl
group by pref_name
工资大于30000降薪10%,
UPDATE Salaries
SET salary = CASE WHEN salary >= 30000
THEN salary * 0.9
WHEN salary >= 250000 AND salary < 28000
THEN salary * 1.2
ELSE salary END;
获取只加入了一个社团的学生的社团 ID。
获取加入了多个社团的学生的主社团 ID
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;
SELECT std_id, club_id AS main_club
FROM StudentClub
WHERE main_club_flg = 'Y' ;
查找重复数据
select 姓名 from 表 group by 姓名 where count(姓名) > 1
查找n高数据
查询两张表相同的数据 select * from table1 intersect select * from table2
查询两张表不同的数据
select * from table1 except select * from table2
