General Questions Commonly Used SQL Commands
1) On the database (the database)
The version of SQL Server: Enterprise, Personal, Desktop, Developer, ..
Parts of the SQL Server Enterprise Manager, Query Analyzer, Books Online, ..
The types of SQL Server files: data files (primary, secondary), the log file
The database type of SQL Server system databases, user database
create database: create database
create database
create database HoSoSinhViencreate database HoSoSinhVienon (name=’HSSV_data’, filename=’c:\HSSV_data.mdf’)
log on (name=’HSSV_log’, filename=’c:\HSSV_log.ldf’) |
drop database
drop database HoSoSinhVien |
alter database: repair information database
alter database HoSoSinhVienmodify name = HSSV |
exec sp_dboption: repair information database
exec sp_dboption HoSoSinhVien, ‘read only’, ‘true’exec sp_dboption HoSoSinhVien, ‘autoshrink’, ‘true’exec sp_dboption HoSoSinhVien, ‘single_user’ |
dbcc: control database
dbcc shrinkdatabase(HoSoSinhVien, 10) |
2) Table
Remember the types of data: text, binary, numberic, money, datetime, bit variant
Remember the bindings: default, check, unique, Foreign, primary
Me integrity types: entity, domain, referential, user
Additional memory attributes: identity, null
create table: create table
Creating tables with columns
create table SinhVien (MaSV int,TenSV nchar(50)
) |
Tạo với các ràng buộc
create table SinhVien (MaSV int primary key,TenSV nvarchar(50) not null,
QueQuan int references DiaPhuong(MaDP) ) |
drop table
drop table SinhVien |
alter table..add: add columns
alter table SinhVienadd QueQuan int |
alter table..drop column: drop columns
alter table SinhVien drop column QueQuan |
alter table..alter column: additional properties not null
alter table SinhVienalter column TenSV nchar(50) not null |
alter table..add primary key: add the primary key
alter table SinhVienalter column MaSV int not nullgo
alter table SinhVien add primary key (MaSV) |
alter table..add foreign key: add foreign key constraints
alter table SinhVien add foreign key (QueQuan) references DiaPhuong(MaDP) |
alter table..add default: add the default binding
alter table SinhVien add default ‘khong ten’ for TenSV |
exec sp_help: information table
exec sp_help SinhVien |
3) record
Remember to add conditional expressions
Remember to add the wildcard
insert..values: add records to the table
insert into SinhVien (MaSV, TenSV, QueQuan)values (1, N’Nguyễn Văn A’, 1) |
insert..select: add records from another table to table
insert into DocGiaselect MaSV, TenSV, QueQuan from SinhVieninsert into DocGia
select MaGV, TenGV, QueQuan from GiaoVien |
select..into: put option results in a new table
select MaSV, TenSV, Dieminto SinhVienKhafrom SinhVien
where Diem > 7.0 |
delete: delete records from table
delete from SinhViendelete from SinhVien where MaSV=1234 |
truncate: delete all records of the table
truncate table SinhVien |
update: edit the records in the table
update SinhVienset NhomTruong = 1where MaSV < 4
update SinhVien set NhomTruong = 4 where (MaSV > 3 and MaSV < 8) |
4) query
Boolean operations: in, like, the between
The total group functions: sum, max, min, avg
4.1) Simple query
select *: Show all tables
select *from SinhVien |
select: Currently a number of columns
select TenSV, DiemTBfrom SinhVien |
select..where: Currently a number of lines / records
select TenSV, DiemTBfrom SinhVienwhere DiemTB > 6.0 |
select..order by: Currently sorted by location and then by name
select TenSV, DiemTBfrom SinhVienorder by DiemTB desc, TenSV asc // asc sắp sếp tăng dần, desc là giảm dần |
select..distinct: Currently not list duplicate values
select distinct QueQuanfrom SinhVien |
select..top: Currently the first row in the table
select top 3 TenSV, DiemTBfrom SinhVienorder by DiemTB desc, TenSV asc |
4.2) nested query
select..where (select)
Show All employees who have salaries in the table with the biggest salaries in the company:
select TenNV, Luongfrom NhanVienwhere Luong = (select max(Luong) from NhanVien) |
select..where (in)
Show All employees who have salary table or large second largest of those companies :.
select TenNV, Luongfrom NhanVienwhere Luong in (select top 2 Luong from NhanVien order by Luong) |
Select statement will create a set of two values (top 2) it is the largest wage and salary second largest. And the first select statement will select those which lie in the collective wage largest and second largest
select..where (in sub)
Currently all those with the largest wage his room (not the biggest but the biggest company in the room or unit he belongs to)
select nv1.TenNV, nv1.Luongfrom NhanVien as nv1where nv1.Luong = (select max(Luong) from NhanVien where Phong=nv1.Phong) |
4.3) Query the total group (subtotals query / grouping query)
select..group by: Statistical criteria
select QueQuan, count(*)from NhanVien group by QueQuan |
Count the number of males and females in the company
select GioiTinh, count(*)from NhanViengroup by GioiTinh |
Calculate the total income in each room
select Phong, sum(Luong)from NhanViengroup by Phong |
select..having: Show some appropriate group
select QueQuan, count(*)from NhanViengroup by QueQuan
having (QueQuan = ‘HP’, QueQuan = ‘HN’) |
Only out of any room with incomes greater than 500000
select Phong, sum(Luong)from NhanViengroup by Phong
having sum(Luong) > 5000000 |
select QueQuan, count(*)from NhanViengroup by QueQuan
having count(*) > 10 |
4.4) cross table query / joining query
select..inner join: records transplant pairs satisfying conditions
Pairing board and staff out names and names of local employees
select NhanVien.TenNV, DiaPhuong.TenDPfrom NhanVien inner join DiaPhuong on NhanVien.QueQuan = DiaPhuong.MaDP |
select..left outer join: left and took all the puzzle (if any) to be
Get all the staff including the staff invalid native (ie native code is not in the local table)
select NhanVien.TenNV, DiaPhuong.TenDPfrom NhanVien left outer join DiaPhuong on NhanVien.QueQuan = DiaPhuong.MaDP |
select..right outer join:get all the right and match (if any) to the left
Get all the local transplant staff, the local transplant will be invalid with empty data. Not currently do not have the staff appropriate native code
select NhanVien.TenNV, DiaPhuong.TenDPfrom NhanVien right outer join DiaPhuong on NhanVien.QueQuan = DiaPhuong.MaDP |
select..full outer join: graft taken from both sides and if there
Get all the staff (if there is no match, the transplant hometown with an empty data set) and all the local staff including no.
select NhanVien.TenNV, DiaPhuong.TenDPfrom NhanVien right outer join DiaPhuong on NhanVien.QueQuan = DiaPhuong.MaDP |
select..cross join: bilities of all possible pairs of graft
select NhanVien.TenNV, DiaPhuong.TenDPfrom NhanVien cross join DiaPhuong |
ref: Refer to a command
View all DATABASE SERVER users
select *from master.dbo.sysdatabaseswhere sid <> 1 |
View all users within LIST DATABASE
Each time a table is created in a database, information that will be contained in the table sysobjects table of the database containing the table you just created. So we can see the list of tables in the database by querying the sysobjects table. The user-generated table of type ‘U’
select *from Northwind.dbo.sysobjectswhere xtype=’U’ |
In this example, we retrieve all records sysobjects table of the Northwind database but only retrieve the users table, ie the table is xtype ‘U’
See all belong to a TABLE COLUMN
Every time a column in a table is created, syscolumns table in the database will contain information about the newly created column. The id attribute of the column will contain the name of the table that column belongs. So to see the columns in a table of data, we can query the syscolumns table
declare @x intset @x = (select id from sysobjects where name=’Employees’)select * from syscolumns where id = @x |
In this example, we declare a variable of type intx, then take the id of the variablex Employees table, then we get all the columns in the table syscolumns where id inx, also means that the id of the table Employees. Therefore, we will list all of the columns in the Employees table
Use temporary tables
Declare the table, updates the table, and the table show
declare @x table(MaSV int, TenSV nvarchar(20))insert into @x values(1, N’Thưởng’)insert into @x values(2, N’Yến’)
select * from @x |
Declare the tables, load tables from the database on the board has declared, and the current table
declare @x table(MaSV int, TenSV nvarchar(20))insert into @x select EmployeeID, firstname
from Northwind.dbo.Employees select * from @x |
Use temporary variables
declare @t moneyset @t = (select max(UnitPrice) from Northwind.dbo.products)select * from Northwind.dbo.products where UnitPrice >= @t |
ref: The basic concepts of database
The data model (data model)
+ Hierarchical model (hierarchical)
+ Network structure (network)
+ Relational model (relational)
The problem of storage
+ Excess (redundance)
+ Consistency (consistence)
+ The integrity (integrity)
+ The safety (security)
+ The sharing (sharing)
The Basics
+ Database (database)
+ Table (table) or relations (relations)
+ Record (record) or line (row) or the (tuple)
+ School (fields) or column (column)
+ Relationship (relationship) is represented by the table (s) or lock (key)
The relationship between the
+ Why should save relationship
+ Method to save the relationship (user, users table)
+ Primary key (primary key) and a foreign key (foreign key operation)
+ Problem referential integrity (referential integrity)
Definition language and data processing
+ Actions on objects such as databases, tables, columns
+ The definition database and tables: create, drop, alter
+ The updated data: insert, delete, update
+ The data query: join, union, projection, selection, sort, group
+ The programming and operation: declare, set, use, go
ref: Concerning the definition of the table
The issue of data integrity
+ Integrity entity (entity integrity): primary key, unique, identity
+ Referential integrity (referential integrity): foreign key operation, check
+ Data integrity domain (domain integrity): default, foreign key operation, check, not null
+ Integrity users (user integrity) rules, stored procedures, triggers
The support with integrity
+ Check constraints (check constraint) must satisfy certain conditions
+ Binding default (default constraint): must have a default value
+ Unique constraint (unique constraint): the values in the column are not duplicated
+ Foreign key constraint (FOREIGN KEY constraint): the values must match the corresponding primary key column
+ Primary key constraint (primary key constraint) must be unique and not empty
+ Attribute identifier (identity property) can be increased automatically
+ Attribute is not empty (not null property) are not allowed to empty
The data type
Binary values: bit
The integer: bigint, int, smallint, tinyint
The real number is approximately: float, real
The value of precision: decimal, numberic
Monetary value: money, smallmoney
The value of the time: datetime, smalldatetime
The string: char, varchar, text, nchar, nvarchar, ntext,
The binary string: binary, varbinary, image
Styles: cursor, table, variant, timestamp, uniqueidentifier