Common SQL code

1. sort by strokes of the Last Name:
Select * From tablename order by customername collate chinese_prc_stroke_ci_as
2. database encryption:
Select encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different
3. Retrieve the fields in the table:
Declare @ list varchar (1000), @ SQL nvarchar (1000)
Select @ list = @ list + ',' + B. name from sysobjects A, syscolumns B where a. ID = B. ID and A. Name = 'table'
Set @ SQL = 'select' + right (@ list, Len (@ list)-1) + 'from table'
Exec (@ SQL)
4. View hard disk partitions:
Exec master .. xp_fixeddrives
5. Compare whether tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'
6. Kill all event Inspector processes:
Declare hcforeach cursor global for select 'Kill '+ rtrim (spid) from Master. DBO. sysprocesses
Where program_name in ('SQL filer', n' SQL event profiler ')
Exec sp_msforeach_worker '? '
7. Record Search:
Starting with N records
Select Top N * from table
-------------------------------
N to M records (primary index ID required)
Select top M-N * from table where ID in (select top m id from Table) order by ID DESC
----------------------------------
N to the end record
Select Top N * from Table order by ID DESC
8. How to modify the Database Name:
Sp_renamedb 'old _ name', 'new _ name'
9: Get all user tables in the current database
Select name from sysobjects where xtype = 'U' and status> = 0
10: Get all fields of a table
Select name from syscolumns where id = object_id ('table name ')
11. View views, stored procedures, and functions related to a table
Select a. * From sysobjects A, syscomments B where a. ID = B. ID and B. Text like '% table name %'
12: view all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype = 'P'
13: Query all databases created by the user
Select * from Master .. sysdatabases d Where sid not in (select Sid from Master .. syslogins where name = 'sa ')
Or
Select dbid, name as db_name from Master .. sysdatabases where Sid <> 0x01
14: query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'
[N]. [title]:
Select * From tablename order by customername
[N]. [title]:
Select * From tablename order by customername

 

Workshop class ---------------------------------------------------------------------------------------------------------------------

1. Obtain the first five persons with the largest age in the mystudents table, and the first 20%

Select top 5 * From mystudents order by Fage DESC
Select top 20 percent * From mystudents order by Fage DESC

2. Obtain the current time.

Select getdate ()

3. Remove duplicate distinct

Select distinct * From mystudents

4. query the maximum, minimum, total, and average English scores.

Select
Highest score = (select max (fenglish) from mystudents ),
Minute = (select Min (fenglish) from mystudents ),
Total score = (select sum (fenglish) from mystudents ),
Average score = (select AVG (fenglish) from mystudents)

5. query the total number of class sets

Select count (*) as total count from mystudents

6. query the number of people whose mathematical score is null (set N in the database to NULL for each student)

Select * From mystudents where fmath is null

7. maximum and minimum values of male students' birthdates (design the birthdate field)

Select
Max Birthday = (select max (fbirthday) from mystudents where fgender = 'male '),
Minimum Birthday = (select Min (fbirthday) from mystudents where fgender = 'male ')

 

1 manually add the [primary key constraint] pk_employees_empid

Alter table employees add constraint pk_employees_empid primary key (empid)

2 manually add non-empty constraints for empname

Alter table employees alter column empname varchar (50) not null

3 manually add a unique key constraint for empname

Alter table employees add constraint uq_employees_empname unique (empname)

4. Delete the unique key constraint.

Alter table employees drop constraint uq_employees_empname

5. Add a default constraint for gender so that the default value is "male"
Alter table employees add constraint df_employees_empgender
Default ('male') for empgender

6 alter table employees add constraint ck_employees_empage
Check (empage> = 0 and empage <= 120) increases the age detection constraint 120-with 0 and

7. Add check constraints for Gender: Non-male or female

Alter table employees add constraint ck_employees_empgender
Check (empgender = 'male' or empgender = 'femal ')

8 -- add a foreign key constraint to the employee table
-- First, set the depid in the department table as the primary key, and the foreign key cannot be blank.

Alter table Department add constraint pk_deparment_depid primary key (depid)

Alter table employees add constraint fk_employees_empdepid
Foreign key (empdepid) References Department (depid) on Delete Cascade

9 Delete multiple constraints in one statement

Alter table employees drop constraint fk_employees_empdepid,
Ck_employees_empage,
Uq_employees_empname

One statement adds multiple constraints to the table.

Alter table employees add Constraint
Uq_employees_empname
Unique (empname ),
Constraint
Cksss
Check (empage> = 0and empage <= 150)

 

 

1. -- query the students whose math scores fail

Select
Fname as name, fmath as mathematical score
From mystudents
Where 60 <= fmath and fmath <80

2. -- Query male students older than 20

Select fname as name, Fage as age from mystudents where Fage> 27 and fgender = 'male'

3. -- Query male students older than 27and less than 33

Select fname as name, Fage as age from mystudents where Fage> 27 and Fage <33 and fgender = 'male'

--- Between... And (between 27 and 30)

Select fname as name, Fage as age from mystudents where Fage between 27 and 30 and fgender = 'male'

4. -- Query employees in Department and Department 5

Select empname as name, empdepid as department no. From employees where depid in (, 5)

5. -- Query all the students surnamed Zhang in mystudents. % Represents 0 or multiple arbitrary characters.

Select * From mystudents where fname like 'sheet %'

6. -- Query all the students whose mathematics scores are null and replace null with 'absent example'

Select fname as name, Fage as age, math score = isnull (cast (fmath as varchar (50), 'uncheck') from mystudents where fmath is null

7. -- sort by English score

Select * From mystudents order by fenglish DESC

8. -- query the information in the student table and display the name, gender, English score, mathematics score, and average score in descending order of average score.

Select name = fname, Gender = fgender, English score = fenglish, mathematics score = fmath, average score = (fenglish + fmath)/2 from mystudents order by average score DESC

9. -- count the number of male and female students in mystudent.

Select gender = fgender, count (*) as count from mystudents group by fgender

10. -- count the number of male employees in each department in the employee table.

Select Department number = empdepid, Department Male COUNT = count (*) from employees where empgender = 'male' group by empdepid having count (*)> 3

11. query the following table myorder:

(1) calculate a hot-selling goods ranking table, that is, according to the sales quantity of each commodity.

Select Product Name, total sales quantity = sum (sales quantity) from myorders group by product name order by total sales quantity DESC

(2) count the names and total prices of goods whose total prices exceed 3000 yuan, and sort them in descending order according to the total sales prices.

Select sum (sales quantity) * AVG (sales price) as 'total sale', product name from myorders group by product name having sum (sales quantity) * AVG (sales price)> 3000 order by 1 DESC

(3) Measure the customer's preferences for "Coca-Cola" (that is, the number of purchases made by each purchaser for "Coca-Cola)

Select purchaser, purchase volume = count (*), product name = 'Coca cola 'from myorders group by purchaser order by purchase volume DESC select * From myorders