SQL server 统计语句
时间: 2024-06-04 11:10:42 浏览: 11
1. SELECT COUNT(*) FROM table_name; -- 统计表中所有行数
2. SELECT COUNT(column_name) FROM table_name; -- 统计表中特定列的非空值数量
3. SELECT SUM(column_name) FROM table_name; -- 计算表中特定列的总和
4. SELECT AVG(column_name) FROM table_name; -- 计算表中特定列的平均值
5. SELECT MAX(column_name) FROM table_name; -- 找出表中特定列的最大值
6. SELECT MIN(column_name) FROM table_name; -- 找出表中特定列的最小值
7. SELECT COUNT(DISTINCT column_name) FROM table_name; -- 统计表中特定列的不同值数量
8. SELECT TOP 10 column_name FROM table_name ORDER BY column_name DESC; -- 找出表中特定列的前十大值
9. SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; -- 按照特定列进行分组统计
10. SELECT column_name, AVG(column_name2) FROM table_name GROUP BY column_name; -- 按照特定列进行分组计算平均值
相关问题
sql server 查重语句
以下是两种SQL Server查重的语句:
1.查询单列重复数据:
```sql
SELECT name, COUNT(*) FROM test GROUP BY name HAVING COUNT(*) > 1
```
该语句将会查询test表中name列重复的数据,并统计重复次数。
2.查询多列重复数据:
```sql
SELECT a.* FROM test a, ( SELECT name,code FROM test GROUP BY name,code HAVING COUNT(1)>1 ) AS b WHERE a.name=b.name AND a.code=b.code
```
该语句将会查询test表中name和code列同时重复的数据,并返回所有列的数据。
SQL Server语句
好的,以下是根据您的要求编写的SQL Server语句,包括创建数据库、表、视图、索引、约束、存储过程和触发器,以及各种查询语句:
1. 创建数据库:
```
CREATE DATABASE MyDatabase;
```
2. 创建表:
```
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Gender VARCHAR(10),
Major VARCHAR(50)
);
CREATE TABLE Courses (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Teacher VARCHAR(50)
);
CREATE TABLE Scores (
ID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Score FLOAT,
CONSTRAINT FK_Student FOREIGN KEY (StudentID) REFERENCES Students(ID),
CONSTRAINT FK_Course FOREIGN KEY (CourseID) REFERENCES Courses(ID)
);
CREATE TABLE Departments (
ID INT PRIMARY KEY,
Name VARCHAR(50),
ManagerID INT,
CONSTRAINT FK_Manager FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
);
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Gender VARCHAR(10),
DepartmentID INT,
CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
);
```
3. 插入数据:
```
INSERT INTO Students (ID, Name, Age, Gender, Major)
VALUES (1, 'Tom', 20, 'Male', 'Computer Science'),
(2, 'Jane', 21, 'Female', 'English'),
(3, 'Mike', 19, 'Male', 'Mathematics'),
(4, 'Lily', 22, 'Female', 'History'),
(5, 'John', 20, 'Male', 'Physics');
INSERT INTO Courses (ID, Name, Teacher)
VALUES (1, 'Database', 'Mr. Smith'),
(2, 'English Composition', 'Ms. Lee'),
(3, 'Calculus', 'Dr. Johnson'),
(4, 'World History', 'Prof. Brown'),
(5, 'Quantum Mechanics', 'Dr. Kim');
INSERT INTO Scores (ID, StudentID, CourseID, Score)
VALUES (1, 1, 1, 95),
(2, 2, 2, 88),
(3, 3, 3, 92),
(4, 4, 4, 85),
(5, 5, 5, 90);
INSERT INTO Departments (ID, Name, ManagerID)
VALUES (1, 'Computer Science', 1),
(2, 'English', 2),
(3, 'Mathematics', 3),
(4, 'History', 4),
(5, 'Physics', 5);
INSERT INTO Employees (ID, Name, Age, Gender, DepartmentID)
VALUES (1, 'Mr. Smith', 40, 'Male', 1),
(2, 'Ms. Lee', 35, 'Female', 2),
(3, 'Dr. Johnson', 45, 'Male', 3),
(4, 'Prof. Brown', 50, 'Male', 4),
(5, 'Dr. Kim', 38, 'Female', 5);
```
4. 模式匹配查询:
```
SELECT * FROM Students WHERE Name LIKE '%Tom%';
```
5. TOP查询:
```
SELECT TOP 3 * FROM Courses ORDER BY Name DESC;
```
6. IN查询:
```
SELECT * FROM Students WHERE Major IN ('Computer Science', 'Mathematics');
```
7. 降序查询:
```
SELECT * FROM Scores ORDER BY Score DESC;
```
8. COUNT集合函数查询:
```
SELECT COUNT(*) FROM Students;
```
9. 分组统计查询:
```
SELECT Major, AVG(Age) AS AvgAge, COUNT(*) AS Total FROM Students GROUP BY Major;
```
10. 连接条件的多表查询:
```
SELECT Students.Name, Courses.Name AS CourseName, Scores.Score
FROM Students
INNER JOIN Scores ON Students.ID = Scores.StudentID
INNER JOIN Courses ON Scores.CourseID = Courses.ID;
```
11. 比较运算符的子查询:
```
SELECT * FROM Students WHERE Age > (SELECT AVG(Age) FROM Students);
```
12. IN的子查询:
```
SELECT * FROM Courses WHERE ID IN (SELECT CourseID FROM Scores WHERE Score > 90);
```
13. 创建视图:
```
CREATE VIEW StudentInfo AS
SELECT Students.Name, Courses.Name AS CourseName, Scores.Score
FROM Students
INNER JOIN Scores ON Students.ID = Scores.StudentID
INNER JOIN Courses ON Scores.CourseID = Courses.ID;
```
14. 创建索引:
```
CREATE INDEX idx_StudentName ON Students (Name);
```
15. 创建唯一性约束、检查约束、默认值约束、外键约束:
```
ALTER TABLE Employees ADD CONSTRAINT UC_EmployeeName UNIQUE (Name);
ALTER TABLE Departments ADD CONSTRAINT CK_DepartmentName CHECK (LEN(Name) > 0);
ALTER TABLE Courses ALTER COLUMN Teacher SET DEFAULT 'Unknown';
ALTER TABLE Scores ADD CONSTRAINT FK_Student FOREIGN KEY (StudentID) REFERENCES Students(ID) ON DELETE CASCADE;
```
16. 创建存储过程:
```
CREATE PROCEDURE sp_GetStudentInfo @StudentName VARCHAR(50)
AS
BEGIN
SELECT Students.Name, Courses.Name AS CourseName, Scores.Score
FROM Students
INNER JOIN Scores ON Students.ID = Scores.StudentID
INNER JOIN Courses ON Scores.CourseID = Courses.ID
WHERE Students.Name = @StudentName;
END;
```
17. 创建插入、更新的DML触发器:
```
CREATE TRIGGER tr_StudentInsert ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO AuditTrail (TableName, Action, UserName, DateTime)
VALUES ('Students', 'Insert', SUSER_SNAME(), GETDATE());
END;
CREATE TRIGGER tr_StudentUpdate ON Students
AFTER UPDATE
AS
BEGIN
INSERT INTO AuditTrail (TableName, Action, UserName, DateTime)
VALUES ('Students', 'Update', SUSER_SNAME(), GETDATE());
END;
```
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)