SQL Server basic Syntax (ii)

First look at the function of the waist to achieve:

One: Create a database

/* Check to see if the database exists, delete this database if it exists */if exists (SELECT * from sysdatabases where name= ' bankdb ')  drop database bankdb Go */create a DB BA nkdb*/CREATE Database Bankdb on (   name= ' Bankdb_data ',   filename= ' d:\bank\bankDB.mdf ',   size=10,   -- Growth rate   filegrowth=15%   ) Log on   (   --log file   name= ' Bankdb_log ',   filename= ' d:\bank\ Bankdb_log.ldf ',   size=5,   filegrowth=15%   )   

Two: Create a statement for the table:

    /* CREATE TABLE *    /Use BANKDB go   CREATE TABLE userInfo   -User Information table   (    CustomerID int identity),    CustomerName Char (8) NOT NULL,    PID  char (+) NOT NULL,    telephone char (all) not NULL,    address varchar (50 )        go to        CREATE TABLE Cardinfo  --bank card information Sheet    (     CardID char) not NULL,     Curtype char (5) Not NULL,     Savingtype char (8) is not NULL,     opendate datetime isn't null, Openmoney money is not     null,     balance Money is not NULL,     pass char (6) is not NULL,     isreportloss bit is not NULL, CustomerID int is not NULL     ) 
   go         CREATE TABLE  transinfo  --Transaction information table    (      transdate  datetime NOT NULL,       Transtype  char (4) is not NULL,     CardID  char (+) is not NULL, Transmoney money is not     null,     remark text     )     Go          

Three: Adding Constraints to a table

     /* Add constraint to UserInfo table CustomerID (customer number): Auto Number (identity column), starting from 1, primary key PID (Social Security Number): only 18-bit or 15-bit, Social Security number UNIQUE constraint telephone (contact phone): Format xxxx-xxxxxxxx or phone number 13 bit */ALTER TABLE USERINFO ADD constraint Pk_customerid primary key (custom Erid), constraint chk_pid check (len (PID) =18 or Len (PID) =15), constraint uq_pid unique (PID), constraint Chk_                                     Telephone check (telephone like ' [0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] ' or Len (telephone) =13) go//cardinfo table constraints Cardid card number required, primary health, bank card number rules and phone numbers, the general first 8 delegates Special meaning, such as a branch of a headquarters. Assume that the line requires its Business Hall card number format: 1010 3576 xxxx xxx start, every 4 digits after a space, card number is generally randomly generated. Curtype currency type is required, the default is Rmbsavingtype deposit type current/scheduled maturity/Regular Opendate account opening date is required, the default is the current date of the system Openmoney account must be filled, not less than 1 yuan balance balance must be filled, not less than 1 yuan, Otherwise will be the pin home pass password is required, 6 digits, the default for opening an account is 6 "8" Isreportloss whether the loss is required, yes/No value, the default is "no" CustomerID customer number foreign key, must be filled, indicating the card corresponding customer number, a customer is allowed to handle more than one card number * * ALTER TABLE CARDINFOADD constraint Pk_cardid primary KEY (CardID), constraint Ck_cardidCheck (CardID like ' 1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9] '), constraint df_curtype default ' $ ' for Curtyp E, constraint ck_savingtype check (savingtype in (' Current ', ' live maturity ', ' regular ')), constraint df_opendate default getdate () for Open Date, constraint Ck_openmoney check (openmoney>=1), constraint ck_balance check (balance>=1), constraint Ck_pa SS Check (pass like ' [0-9][0-9][0-9][0-9][0-9][0-9] '), constraint df_pass default ' 888888 ' for pass, constraint df_isre Portloss default 0 for Isreportloss, constraint Fk_customerid foreign key (CustomerID) references UserInfo (CustomerID) go/ * Transinfo table Constraint TransDate trading day is required, the default is the system current date Cardid card number must be filled, external health, repeatable index TRANSTYPE transaction type is required, can only be deposit/withdrawal Transmoney transaction amount required, More than 0remark notes optional input, other instructions */alter table Transinfoadd constraint df_transdate default getdate () for transdate, constraint Ck_ Transtype Check (Transtype in (' Deposit ', ' withdrawal ')), constraint Fk_cardid foreign key (Cardid) references Cardinfo (cardid), con Straint Ck_transmoney Check (TRANSMONEY>0)   

Four: Insert data:

/* Zhang San account, ID: 123456789012345, Tel: 010-67898978, Address: Beijing Haidian   Account amount:  £   1010 3576 1234 5678    John Doe account, ID card : 321245678912345678, Tel: 0478-44443333,   Account amount: 1  regular   card number: 1010 3576 1212 1134   *  /INSERT INTO UserInfo ( customername,pid,telephone,address) VALUES (' Zhang San ', ' 123456789012345 ', ' 0102-67898978 ', ' Beijing Haidian ')  insert INTO Cardinfo (Cardid,savingtype, Openmoney, balance, CustomerID) VALUES (' 1010 3576 1234 5678 ', ' current ', 1000,1000,3)      Insert into UserInfo (Customername,pid,telephone) VALUES (' John Doe ', ' 321245678912345678 ', ' 0478-44443333 ')  insert Into Cardinfo (Cardid,savingtype, Openmoney, balance, CustomerID) VALUES (' 1010 3576 1212 1134 ', ' regular ', 1,1,2)  SELECT * From UserInfo SELECT * FROM Cardinfo select * from Transinfo

/*   Zhang San card number withdrawal 900 Yuan, John Doe card number deposit 5000 yuan   required to save transactions for customer inquiries and banking statistics */--Zhang San/       *  Transaction Information Table Insert Trading Information *   /INSERT INTO Transinfo (Transtype,cardid,transmoney)   values (' withdrawals ', ' 1010 3576 1234 5678 ', ')/     * Update the existing balance in the Bank card information table *  / Update Cardinfo   set balance=balance-900  where cardid= ' 1010 3576 1234 5678 '    --John Doe/    *  Trading Information Table Insert Trading Information *    /INSERT INTO Transinfo (Transtype,cardid,transmoney)    values (' Deposit ', ' 1010 3576 1212 1134 ', 5000) /       * Update the existing balance in the Bank card information table */update   cardinfo    set balance=balance+5000   where cardid= ' 1010 3576 1212 1134 '

V: general business operations:

/*---------Change the password-----*/--1. Zhang San (card No. 1010 3576 1234 5678) Modify the bank card password to 123456--2. John Doe (card number 1010 3576 1212 1134) Modify the bank card password to 123123   Update Cardinfo    set pass= ' 123456 '   where CardID = ' 1010 3576 1234 5678 '      update  cardinfo    Set pass = ' 123123 '   where cardid= ' 1010 3576 1212 1134 '/         *---------John Doe card number loss---------*/  update cardinfo   set Isreportloss=1  where CardID = ' 1010 3576 1212 1134 '    select * from Cardinfo   

Six: Some of the more complex operations:

/* Statistics bank balance of funds and profit settlement * *-Statistics: storage represents the inflow of funds, withdrawals represent the outflow of funds, assuming the deposit rate is 3 per thousand, the loan interest rate is 8 per thousand     declare @inMoney money  DECLARE @ Outmoney Money  Declare @profit money  select * from Transinfo   select @inMoney =sum (Transmoney)  from Transinfo  where transtype= ' deposit '  select @outMoney =sum (Transmoney) from transinfo where transtype = ' Withdraw '  The total amount of print ' Bank flows is: ' +convert (varchar), @inMoney-@outMoney) + ' RMB '  set @profit = @outMoney *0.008-@inMoney *0.003  print ' profit settlement: ' +convert (varchar), @profit) + ' $ '  go  

DATEDIFF: The function returns the number of days between two dates.

SQL Server basic Syntax (ii)