Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use SQL statements to convert rows and columns, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

How to convert rows and columns using SQL statements

If exists (select * from sysdatabases where [name] = 'TestDB')

Print 'Yes, the DB exists'

Else

Print'No, need a new one?'

-create a new database

Create database TestDB on

(

Name = 'TestData'

Filename = 'GpurDBSKeyTest.mdf'

Size = 3

Filegrowth = 2

)

Log on

(

Name = 'TestLog'

Filename = 'GpurDBSKeyTest.ldf'

Size = 3

Filegrowth = 10

)

-- drop database TestDB

Use TestDB

Go

-create a new table

Create table [Scores]

(

[ID] int identity (1Pol 1) primary key

[Student] varchar (20)

[Subject] varchar (30)

[Score] float

)

-- drop table [Scores]

How to convert rows and columns using SQL statements

-- modify a column in the table

Alter table Scores alter column [Student] varchar (20) not null

-- add a new column

Alter table Scores add Birthday datetime

-- Delete a column

Alter table Scores drop column Birthday

-- insert a single piece of data into the table, method 1: with column name

Insert into Scores (Student,Subject,Score)

Values ('Zhang San', 'Chinese', '90')

-- insert a single piece of data into the table, method 2: no column name, but requires that the type of the value corresponds to the column field type

Insert into Scores

Values ('Zhang San', 'English','95')

-insert multiple pieces of data: use union or union all

Insert into Scores (Student,Subject,Score)

Select'Li Si', 'Chinese', '89'

Union all

Select'Li Si', 'English','78'

Delete the data in the table. If there are no conditions, delete all

Delete from Scores where ID in (7 and 8)

-- modify the data in the table

Update Scores

Set Student=' Wang Wu', Score='94'

Where ID=10

-- View data

Select * from Scores

Look at the largest identity value in the table

Select @ @ identity

Or use the dbcc command to view the maximum identity value in the table

Dbcc checkident ('Scores',noreseed)

-- create a view, omitting all the property column names of the view, and consisting of the fields of the target column of the subquery

Create view StudentView

As

Select Student,Subject,Score

From Scores

-- with with check option, future operations on the view (add, change, delete, query) will automatically add where ID > 3

/ *

Create view StudentView

As

Select Student,Subject,Score

From Scores

Where ID > 3

With check option

* /

-- create a view, all define attribute column names, and need to define column names:

-A target column (subquery) is not a simple attribute column, but an aggregation function or column expression

-several columns with the same name are selected when multiple tables are joined.

-you need to enable a new and more appropriate name for a column in the view

Create view IS_Student (Student,Subject,MaxScore)

As

Select Student,Subject,Score

From Scores

Where Score= (select max (Score) from Scores)

The query view is exactly the same as the basic table, except that if there is a with check option in the view, that condition will be added automatically

Select *

From StudentView

-- query the view of custom column names

Select *

From IS_Student

-- the insert/delete/update of the view is the same as the operation of the base table, and eventually it is automatically converted to update the base table with RDBMS.

Not all views are updatable, because some view updates cannot be meaningfully converted into updates to the corresponding base tables

-- delete the view

Drop view StudentView

-- query whether the database exists

The above is all the contents of the article "how to use SQL statements to transform rows and columns". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.