Sqlserver 数据库基本查询
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
--8、Group 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(列名类表) Select…From 插入到现存的表中
--注意:列名的数据类型,个数必须相同
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 条件种类
--1、ColumnName Between 低值 And 高值
Select * from stuInfo Where stuAge Between 20 And 25
--2、And Or Not(与,或,非)
--3、In(值列表)
Select * from stuInfo Where stuAge IN (21,25)
--4、Like(模糊查询)
-- % 表示任意数量字符 _ 一个字符 [] 一个范围 [^]不在某个范围
Select * from stuInfo Where stuName like '小%'

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

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

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

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.

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

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.

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())

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.

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.

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.

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.
