SQL Server Common Operations commands

The Use mytestgo--nchar type is exactly the same as the char type except that nchar stores characters in Unicode format rather than ANSI format. The--unicode format has a larger character set range than the ANSI format. The ANSI character set has only 256 characters, and the Unicode character set has 65,536 different characters. --in Sql server, the Unicode data type consumes more storage space, and Sql server allocates double the internal-storage space for the Unicode data type, so unless the database does need to save characters in this format, Otherwise, you should use ANSI. --New Database--create database mytest--Delete database--drop database mytest--new Table--create table  Salemanager (--number varchar)  not null,--name vachar  not null,--sex  CHAR (4)  not null)--Delete table--drop table salemanager--Modify table name--exec sp_rename  ' Salemanager ', ' Student '--inserting data--insert into salemanager values (1501,n ' Jia ', n ' man '), (1502,n ' ze ', n ' man ') )--Change the column name note:  Changing any part of an object name can break scripts and stored procedures. --exec sp_rename  ' Salemanager. [Birthday] ', ' birthday  ', ' column  '--Change the column type--alter table salemaneger alter column  birthday date--Add Column--alter table salemanager add birthday  date--Updating Data--update salemanager set Birthday =  ' 07/15/2015 '  where number = 1501--Delete column--alter  Use of table salemanager drop column birthday--variables--declare  @number  int--set @ number = 2015--print  @number--all data--update salemanager set [birthday&nbsp of the assignment column;]  =  ' 02/12/2014 '--using aggregate functions--select n ' number of people '  = count (*),--   n ' mean age '  =  avg (age),--   n ' minimum ages '  = min,--   n ' Maximum age '  = max ( Age),--   n ' ages variance '  = stdev--from salemanager--use mathematical functions--select n ' natural logarithm '  = log,--   n ' index '  = exp,--   n ' symbol '  = sign ( 2),--   n ' pi '  = pi (),--   n ' sine '  = sin (PI ()/2.0),--    n ' cosine '  = cos (PI ()/2.0)--Using a string function--select name,--   n ' name length '  =  len (name)--from salemanager--Select rows of data with IDs 1 and 2, in translatable to ' yes ', '--select * from score--where id in  '-wildcard% Instead of one or more characters, _ replaces only one character, [charlist] characters columns any one of the first characters, [!charlist]--select * from score--where name  like  ' j% '--where name like  ' z_ '--where name like  ' [j]% '--operator   between ... and  selects a range of data between two values. These values can be numeric, text, or date. --select * from score--where name between  ' Jia '  and  ' ze '--by using   SQL, you can specify aliases for column names and table names (alias), as can be omitted--Specify table aliases, multi-table query--SELECT SC.NAME,SA.NUMBER--FROM SCORE AS SC, salemanager as sa--where sc.name =  ' Jia '  and sa.number = 1501-- Specifies the column alias--select name as n,sex as s--from score--union  operator for merging two or more   The result set of the select  statement.--The default,union  operator selects a different value. If duplicate values are allowed, use  union all--select name from salemanager--union--(Union all)--select  name from scoreThe--select into  statement selects data from one table and then inserts the data into another table--select into  statements are often used to create a backup copy of a table or to archive records--select  * into score1--(in  ' Backup.mdb ')--from score--(where id = 01)/*UNIQUE  Constraints uniquely identify each record in a database table. Both unique  and  PRIMARY KEY  constraints provide a unique guarantee for a column or column collection. primary key  has auto-defined  UNIQUE  constraints. Note that each table can have more than one  UNIQUE  constraint, but only one  PRIMARY KEY  constraint per table. Example:id_p int not null unique,*//*primary key  constraint uniquely identifies each record in a database table. The primary key must contain a unique value. Primary key columns cannot contain  NULL  values. Each table should have a primary key, and each table can have only one primary key. Example: id_p int not null primary key,*//*  FOREIGN KEY  in one table points to another table   Primary key. foreign key  constraints are used to prevent actions that disrupt the connection between tables. The foreign key  constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to. Example:foreign key  (id_p)  references persons (id_p) *//*check  constraints are used to limit the range of values in a column. If a  CHECK  constraint is defined on a single column, the column only allows a specific value. If a  CHECK  constraint is defined on a table, the constraint restricts the value in a particular column. Example: ID_P INT NOT&NBSp The null check  (id_p>0),*//*default  constraint is used to insert a default value into a column. If no other value is specified, the default value is added to all new records. Example: City varchar (255)  DEFAULT  ' Sandnes ' *//*auto-increment  will generate a unique number when the new record is inserted into the table. We usually want to automatically create a value for the primary key field each time we insert a new record. We can create a  auto-increment  field in the table. ms sql  using the  IDENTITY  keyword to perform  auto-increment  task default,identity  start value is   1, each new record increment  1. To specify   "P_ID"   column to  20  start and increment  10, change  identity  to  identity (20,10) the following  sql The   statement defines the   "p_id"   column in the   "Persons"   table as the  auto-increment  primary key: P_id int primary  KEY IDENTITY*//*SQL Server  use the following data types to store date or date/time values in the database:date-  format: yyyy-mm-dd  datetime-  Format: yyyy-mm-dd hh:mm:ss smalldatetime   -  Format:  yyyy-mm-dd hh:mm:ss timestamp-  format:  Unique number  *//*NULL  value is handled differently than other values. null  is used as a placeholder for unknown or inapplicable values. Note: You cannot compare  NULL  and  0; they are not equivalent. *//* Total in  SQL Server Number: Function description  avg (column) returns the number of rows of a column    count (column) returns a column of rows (excluding null values)  count (*) returns the number of rows selected  count ( Distinct column)    Returns the number of different results  first (column) returns the value of the first record in the specified field (sqlserver2000  not supported)  last (column) Returns the value of the last record in the specified field (sqlserver2000  not supported)  max (column) returns the highest value of a column  min (column) returns the lowest value of a column    SUM (column) returns the sum of a column   example: the Select avg (age)  as aveage from salemanager*//*sql Index has two types, Clustered indexes and nonclustered indexes, clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous. Clustered index A table can have only one, and a table of non-clustered indexes may exist multiple. */--CREATE View--create view [firstview] as--select name, score--from score--(where  SCORE>80)--go--Query view--select * from [firstview]--group by  statements are used to combine aggregate functions, Groups The result set based on one or more columns. --select name, avg (Score)  as AveScore from Score--group by  name--having  clause, add  HAVING  clause in  SQL  because the,where  keyword cannot be used with the aggregate function. --select name, avg (Score)  AS AVESCORE FROM&NBSp score--where sex =  ' Women '--group by name--having avg (Score) >80

SQL Server Common Operations commands