Common SQL statements for database operations

On weekdays, DBMS is used much more, and SQL statements are not used for operations. Which of the following statements can be used in a written test.

Create a database first

 
 

  
  
Create database mical
  
  
On
  
  
Primary (name = miacal_data1,
  
  
Filename = 'e: \ SQL Server 200 \ MSSQL $ MICAL_BO \ SQL server 2000 Personal Edition run \ MSSQL \ Data \ mical_data1.mdf ',
  
  
Size = 2 MB,
  
  
Maxsize = 10,
  
  
Filegrowth = 1 ),
  
  
(Name = mical_data2,
  
  
Filename = 'e: \ SQL Server 2000 \ MSSQL $ MICAL_BO \ SQL server 2000 Personal Edition run \ MSSQL \ Data \ mical_data2.mdf ',
  
  
Size = 2 MB,
  
  
Maxsize = 10,
  
  
Filegrowth = 1)
  
  
Log on
  
  
(Name = mical_log1,
  
  
Filename = 'e: \ SQL Server 2000 \ MSSQL $ MICAL_BO \ SQL server 2000 Personal Edition run \ MSSQL \ Data \ mical_log1.ldf ',
  
  
Size = 2 MB,
  
  
Maxsize = 8,
  
  
Filegrowth = 1 ),
  
  
(Name = mical_log2,
  
  
Filename = 'e: \ SQL Server 2000 \ MSSQL $ MICAL_BO \ SQL server 2000 Personal Edition run \ MSSQL \ Data \ mical_log2.ldf ',
  
  
Size = 2 MB,
  
  
Maxsize = 8,
  
  
Filegrowth = 1)
  
  
Go
 
 

Modify the database name exec sp_renamedb 'mical', 'ss'

1. Start with table creation:

 
 

  
  
Use ss
  
  
Create table lxp_table
  
  
(
  
  
Coll1 char (50) not null,
  
  
Coll2 int,
  
  
Coll3 int identity (1, 1) not null automatically increases by 1
  
  
Primary key (coll3)/* Create a primary key */
  
  
 
  
  

  
  
 
  
  
Create table lxp_ B
  
  
(
  
  
B1 varchar not null,
  
  
B2 varchar not null,
  
  
B3 int identity (1, 1) not null,
  
  
Primary key (b3)
  
  
)
 
 

2. Modify the table name

 
 

  
  
EXEC   sp_rename   'lxp_table',   'lxp_a' 
 
 

3. Modify the column name

 
 

  
  
Exec sp_rename 'lxp_a.[coll1]','a1' 
  
  
exec sp_rename 'lxp_a.[coll2]','a2' 
  
  
exec sp_rename 'lxp_a.[coll3]','a3' 
 
 

4. Add new columns

 
 

  
  
alter table lxp_a  
  
  
add a_3 varchar 
  
  
exec sp_rename 'lxp_a.[a_3]','a4' 
 
 

5. Modify the column type

 
 

  
  
alter table lxp_a  
  
  
alter column a4 char(50)   
 
 

When you modify a data type, you can only convert it to a data type that can be converted. When you modify a data type, the system automatically converts the data in this column. If the data cannot be converted, the data cannot be modified)

6. Add a foreign key when creating a table

 
 

  
  
Create table a_ B
  
  
(
  
  
A_id int not null
  
  
Constraint aa foreign key (a_id) references lxp_a (a3)
  
  
B _id int not null)
  
  
 
  
  
Drop table a_ B
 
 

7. Add a foreign key to the created table

 
 

  
  
alter table a_b  
  
  
add constraint bb foreign key (b_id) references lxp_b(b3) 
 
 

8. delete a foreign key from a created table

 
 

  
  
alter table a_b  
  
  
drop bb 
 
 

9. Find out who is connected to the database

 
 

  
  
select * from master..sysprocesses where hostname<>'' 
  
  
exec sp_who 
 
 

10. query related information of a specified database

 
 

  
  
select * from sysobjects where type = 'U';   
  
  
select name from sysobjects where type = 'F';   
  
  
select name from sysobjects where type = 'P'; 
 
 

Because the system table sysobjects stores all database objects, type indicates the types of various objects, including:

U = User table
S = system table
C = CHECK Constraints
D = DEFAULT value or DEFAULT Constraint
F = foreign key constraint
L = Log
FN = scalar function
IF = embedded table functions
P = Stored Procedure
PK = primary key the constraint type is K)
RF = copy and filter the Stored Procedure
TF = table functions
TR = trigger
UQ = the UNIQUE constraint type is K)
V = View
X = Extended Stored Procedures and Related Object Information.

PS: Open the database

 
 

  
  
use DNN_LH_493 
 
 

11. query all user Databases

 
 

  
  
exec sp_databases 
 
 

12. query all tables in the specified database.

 
 

  
  
use ss  
  
  
exec sp_tables 
 
 

Original article title: SQL statements for Common Database Operations

Edit recommendations]