Home Database Mysql Tutorial Sqlserver 数据库基本查询

Sqlserver 数据库基本查询

Jun 07, 2016 pm 03:40 PM
sqlserver Basic database Inquire

SqlServer 基础语法 -- 查看数据表 select*fromStudent -- 使用 Sql 查询数据 --1 、查询表中所有类容 Select*FromTableName --2 、查询表中指定字段类容 SelectColumnName,,FromTableName SelectstuName,stuNo,stuSexFromstuInfo --3 、带 Where 条件的查询

 

                                Sql Server 基础语法

 

-- 查看数据表

select * from Student

 

-- 使用Sql查询数据

 

--1、查询表中所有类容

Select * From TableName

 

--2、查询表中指定字段类容

Select ColumnName,…, From TableName

 

Select stuName,stuNo,stuSex From stuInfo

 

--3、带Where条件的查询

Select *|ColumnName From TableName Where condition

 

select * From stuInfo where stuSex=''

 

--4、带排序的查询(Order By ColumnName)

-- 语法: Select *|ColumnName From TableName Order By ColumnName Desc|Asc

 

Select * From stuInfo Order By stuAge,stuSeat desc

 

-- 5、选择指定数量的记录,通常配合order By使用

-- 语法: Select Top num *|ColumnName From TableName 

-- 语法: Select Top num *|ColumnName From TableName Order By Desc|Asc

Select Top 2 * From stuInfo Order By stuAge Desc

 

-- 6、分组查询 Group By

-- 分组查询中包含的列必须包含在聚合函数或 GROUP BY 子句中

Select * From stuInfo

Select stuSex, Max(stuAge) As '平均年龄' From stuInfo Group By stuSex

 

--7、对分组后的结果进行过滤

-- having(相当于Where)

Select * From stuInfo

Select stuSex, Avg(stuAge) As '平均年龄' From stuInfo Group By stuSex having Avg(stuAge)>20

 

--8Group By 配合 Where 使用

Select * From stuInfo

Select stuSex, Avg(stuAge) As '平均年龄' From stuInfo where stuAge > 18 Group By stuSex having Avg(stuAge)>20 

 

 

-----------------------------------------------------------

--使用Sql插入数据

--1、不指定列插入数据

语法:Insert Into TableName Values(值列表)

Insert Into stuInfo Values('小八','S25311','',24,'北京')

 

Select * from stuInfo

 

-- 2、指定列名对数据插入

--语法:Insert Into TableName(列名列表) Values(值列表)

--注意:列名列表顺序可自己指定,但值列表的顺序应该和列名列表相同。

 

Insert Into stuInfo(stuName,stuNo,stuSex,stuAddress,stuAge) Values('小九','S25312','','上海',25)

 

select * from stuInfo

 

--3、一次插入多条记录

--1) Insert Into TableName(列名类表) SelectFrom  插入到现存的表中

--注意:列名的数据类型,个数必须相同

 

Insert Into stuInfoCopy(stuName,stuNo,stuSex,stuAge,stuSeat,stuAddress)

Select * From stuInfo

 

Select * From stuInfoCopy

 

--3、一次插入多条记录

--2) Select 列名列表 Into 新表名 From SourceTable 插入到现存的表中

--注意:列名的数据类型,个数必须相同,新表必须不存在

Select Identity(int,1,1) As 'ID',stuName,stuNo,stuSex,stuAge,stuAddress 

Into #temp

From stuInfo

 

select * from #temp

 

--3、一次插入多行记录

--3)使用Union合并数据行

Insert #temp(stuName,stuNo,stuSex,stuAge,stuAddress)

Select '宝贝','S25318','',22,'湖北' Union

Select '宝贝2','S25318','',23,'湖南

 

select * from #temp

 

 

--4、更改数据

--语法: Update TableName Set ColumnName=值 where Condititon

 

Update #temp Set stuName = '宝贝3' Where stuName = '宝贝'

 

Select * from #temp

 

--5删除数据

-- 语法: Delete From TableName Where Condition

Delete From #temp Where Id=8

 

Select * From #temp

 

--5删除数据

--语法: Truncate Table TableName(在删除表中所有数据时,比Delete效率高,但不能

--删除包含外键约束的表

 

Truncate Table stuMarks

 

 

-- Where 条件种类

--1ColumnName Between 低值 And 高值

Select * from stuInfo Where stuAge Between 20 And 25

 

--2And Or Not(与,或,非)

 

--3In(值列表)

Select * from stuInfo Where stuAge IN (21,25)

 

--4Like(模糊查询)

-- % 表示任意数量字符 一个字符 [] 一个范围 [^]不在某个范围

Select * from stuInfo Where stuName like '%'

 

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is the difference between mysql and sqlserver syntax What is the difference between mysql and sqlserver syntax Apr 22, 2024 pm 06:33 PM

The syntax differences between MySQL and SQL Server are mainly reflected in database objects, data types, SQL statements and other aspects. Database object differences include the storage engine and how filegroups are specified, and the creation of indexes and constraints. Data type differences involve differences in numeric types, character types, and date and time types. SQL statement differences are reflected in result set limitations, data insertion, update and delete operations, etc. Other differences include how identity columns, views, and stored procedures are created. Understanding these differences is important to avoid errors when using different database systems.

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

How does Hibernate implement polymorphic mapping? How does Hibernate implement polymorphic mapping? Apr 17, 2024 pm 12:09 PM

Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

Where is the navicat database file? Where is the navicat database file? Apr 23, 2024 am 10:57 AM

The location where the Navicat database configuration files are stored varies by operating system: Windows: The user-specific path is %APPDATA%\PremiumSoft\Navicat\macOS: The user-specific path is ~/Library/Application Support/Navicat\Linux: The user-specific path is ~/ .config/navicat\The configuration file name contains the connection type, such as navicat_mysql.ini. These configuration files store database connection information, query history, and SSH settings.

How to handle database connection errors in PHP How to handle database connection errors in PHP Jun 05, 2024 pm 02:16 PM

To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.

How to write navicat database connection url How to write navicat database connection url Apr 24, 2024 am 02:33 AM

The format of the Navicat connection URL is: protocol://username:password@host:port/database name? Parameters, which contain the information required for the connection, including protocol, username, password, hostname, port, database name and optional parameter.

How to connect to remote database using Golang? How to connect to remote database using Golang? Jun 01, 2024 pm 08:31 PM

Through the Go standard library database/sql package, you can connect to remote databases such as MySQL, PostgreSQL or SQLite: create a connection string containing database connection information. Use the sql.Open() function to open a database connection. Perform database operations such as SQL queries and insert operations. Use defer to close the database connection to release resources.

See all articles