Creating SQL statements for databases and tables

To create the SQL statement for the database:

2  on Primary  --The default is the primary filegroup, which can be omitted
3 (
4 specific description of the/*--data file--*/
5     name= ' Studb_data ',  --The logical name of the master data file
6     filename= ' D:\stuDB_data.mdf ',--the physical name of the master data file
7     SIZE=5MB,--Initial size of master data file
8     MAXSIZE=100MB,--maximum value of main data file growth
9     growth rate of filegrowth=15%--master data files
10)
Log on
12 (
13/*--log file of the specific description, the meaning of each parameter ibid.--*/
     name= ' Studb_log ',
     filename= ' D:\stuDB_log.ldf ',
     SIZE=2MB,
     FILEGROWTH=1MB
18)

So how do you delete this database, SQL Server stores the list of databases in the sysdatabases table of the Master System database, and only needs to see if the table exists in that database.

, with the following statement:

Use master--set the current database to master for access to the sysdatabases table
Go
if exists (SELECT * from sysdatabases where name= ' studb ')
Drop Database Studb
Go

The SQL statements that create the table and delete the table are as follows:

Use Studb

if exists (select * from sysobjects where name= ' Stumarks ')
drop table Stumarks
CREATE TABLE Stumarks
(
    Examno      int     identity (primary) key,
    Stuno       char (6) NOT NULL,
    Writtenexam int not     null,
    Labexam     int not     null
)
Go

--Where the column attribute "identity (start value, increment)" means "Examno" is automatically numbered, also known as the identity column

ALTER TABLE table name
Add constraint constraint name constraint type specific constraint description
ALTER TABLE table name
DROP CONSTRAINT constraint name

ALTER TABLE Stumarks
Add constraint Uq_stuno Unique (Stuno)

ALTER TABLE Stumarks
Drop constraint Uq_stuno

/*--Add SQL Login account--*/
exec sp_addlogin ' Xie ', ' 123456 '--account name Xie, password 123456

--Delete Xie account name
exec sp_droplogin ' Xie '

/*--Add two users (must exist) in the STUDB database--*/
Use Studb
Go
exec sp_grantdbaccess ' Xie ', ' 123456 '
Go

--Hint: the dbo user in SQL Server is the user who has all the active permissions in the database, representing the owner of the database (owner), in general,
-If you create a database that is the owner of the database, the dbo user, the dbo user is a special database user and cannot be deleted, and this
--The user always appears in each database
/*-Authorization to database user--*/
--The syntax for authorization is as follows
--grant permissions [on table name] to database user
Use Studb
Go
Grant Select,update,insert on Stumarks to Xie
Grant CREATE table to Xie
Go

Creating SQL statements for databases and tables