T-SQL高级查询技巧
5星
- 浏览量: 0
- 大小:None
- 文件类型：SQL
简介：
《T-SQL高级查询技巧》是一本深入讲解SQL Server T-SQL语言中高级查询技术的专业书籍，适合数据库开发人员及DBA学习参考。
高级查询在数据库中的使用频率非常高，并且应用范围广泛。
### 基本常用查询
- **select**
```sql
select * from student;
```
- **all 查询所有**
```sql
select all sex from student;
```
- **distinct 过滤重复**
```sql
select distinct sex from student;
```
- **count 统计记录数**
- 总统计：
```sql
select count(*) from student;
```
- 按列统计：
```sql
select count(sex) from student;
```
- 去重后按列统计：
```sql
select count(distinct sex) from student;
```
- **top 取前N条记录**
```sql
select top 3 * from student;
```
- **alias column name 列重命名**
```sql
select id as 编号, name 名称, sex 性别 from student;
```
- **alias table name 表重命名**
```sql
select id, name, s.id, s.name from student s;
```
- **column 列运算**
- 运算表达式：
```sql
select (age + id) col from student;
```
- 字符串连接：
```sql
select s.name + - + c.name from classes c, student s where s.cid = c.id;
```
- **where 条件**
```sql
select * from student where id > 7 and sex = 1;
```
- **and 并且、or 或者**
- 使用 `AND`：
```sql
select * from student where id > 2 and sex = 1;
```
- 使用 `OR`：
```sql
select * from student where id = 2 or sex = 1;
```
- **in, not in**
```sql
SELECT * FROM student WHERE cid IN (SELECT cid FROM classes);
SELECT * FROM student WHERE cid NOT IN (SELECT cid FROM classes);
```
- **between and**
```sql
select * from student where id between 10 and 20;
```
### 高级查询
#### 连接查询（JOIN）
- 简化连接：
```sql
select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id;
```
- 左外连接：
```sql
select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;
```
#### 集合运算
- 并集（union）:
```sql
select id, name from student where name like ja% union all select * from student;
```
- 差集（except）：
```sql
select * from student where name like ja% except select * from student where name like jas%;
```
#### 连接查询类型
1. **inner join 内连接**
```sql
select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id;
```
2. **right join 右外连接**
```sql
select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;
```
3. **cross join 笛卡尔积交叉连接**
```sql
select * from student cross join classes;
```
4. **自连接（同一张表进行连接查询）**
```sql
select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;
```
#### 函数
- 聚合函数：
- 最大值、最小值等统计信息：
```sql
SELECT MAX(age), MIN(age), COUNT(*), AVG(age), SUM(age) FROM student;
```
- 日期时间函数：
```sql
SELECT DATEADD(day, 3, GETDATE()); -- 加天数
```
- 数学函数：
- PI值、随机数等数学计算：
```sql
SELECT ROUND(rand(), 2); -- 四舍五入到小数点后两位的随机数；
```
以上是数据库中常用的查询方法和技巧，通过这些工具可以更有效地管理和分析数据。
全部评论 (0)
