SQL 2000 practice Accumulation

Code:
1. If possible, conditional judgment is required to avoid unnecessary errors.

Use hferpdb -- select database

Select * From hr_person -- it is best to specify fields when querying data, which improves efficiency.

Select role name, NAM, sex, age, identityno, workdate, duty, educationlevel, tel, telbak from hr_person

Select * into hr_personbackup from hr_person -- copies the table and is only used for testing.

Update hr_person set Tel = B. Phone, telbak = B. Duan from hr_person, phone B
Where hr_person.nam = B. Nam -- extract data from another table to update the current table

Drop table hr_personbackup -- delete the entire table

Select count (NAM) 'name displayed in this column 'from phone -- count the number of fields

 

Delete from hr_person where Tel is null -- delete, it is best to add a condition

Select top 0 * from people -- used to check the field name

Delete from people where Xingming in (select Nam from hr_personbackup group by NAM)
-- The condition can be a dataset, not necessarily a table.

Insert into phone (NAM) Select Xingming from people P where P. Xingming is not null
-- Insert a field into the target table

-- Inserts data from one table into the specified column of another table
Insert into hr_person (partition name, NAM, sex, identityno, workdate, duty, educationlevel, lastopdate, createdate, issocialsec, id_person)
Select bumen, Xingming, sex1, sfz, worktime, job2, xueli, getdate (), getdate (), 0, Id2 from people where Id2 is not null

-- Truncate the date of birth of the ID card
Select substring (identityno, 7,8) from hr_personbackup

-- Convert ID card to date of birth
Select cast (substring (identityno, 7,8) as datetime) from hr_personbackup

-- Get the age of the ID card to the present time, but generally it is the design age field = current time minus current time
Select datediff (year, substring (identityno, 7,8), getdate () from hr_personbackup

-- Cast is used to convert the field format, but it must be different from the format of the target field
Update hr_person set Birthday = cast (substring (identityno, 7,8) as datetime) from hr_person

 

-- Search for the same record
Select Nam from hr_person group by Nam having count (NAM)> 1

 

Not complete...