第一部分 上机实验指导.doc

第一部分上机实验指导SELECT * FROM HumanResources.Employee a WHERE a.Gender=M ORDER BY a.Title DELETE grade WHERE 学号=0004 UPDATE grade SET 分数= 90 WHERE 学号=0003 and 课程编号=0005 ALTER TABLE grade ADD 备注 VARCHAR(20) NULL （1）SELECT COUNT(*) FROM grade （2）SELECT SUBSTRING(姓名,1,2) FROM student_info （3）SELECT UPPER(kelly) （4）SELECT Replicate(kelly,3) （5）SELECT SQRT(分数) FROM grade WHERE 分数=85 （6）SELECT 2,3,POWER(2,3) （7）SELECT YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE()) SELECT 姓名，出生日期 FROM student_info WHERE 性别 = (SELECT 性别 FROM student_info WHERE 姓名=刘卫平) SELECT 学号，姓名，性别 FROM student_info WHERE student_info.学号 IN (SELECT 学号 FROM grade WHERE 课程编号 IN (0002，0005)) SELECT 课程编号，分数 FROM grade WHERE 学号=0001 AND 分数ANY (SELECT 分数 FROM grade WHERE 学号=0002) SELECT student_info.学号，姓名，分数 FROM student_info，grade WHERE student_info.学号=grade.学号 AND 分数 BETWEEN 80 AND 90 SELECT student_info.学号，姓名，分数 FROM student_info INNER JOIN grade ON student_info.学号=grade.学号 INNER JOIN curriculum ON 课程名称=C语言程序设计 sp_rename grade.grade_index，grade_ind，INDEX DROP INDEX grade.grade_ind ALTER VIEW v_stu_i(学号，姓名，性别) AS SELECT 学号，姓名，性别 FROM student_info sp_rename v_stu_i，v_stu_info SELECT * INTO stu_phone FROM student_info ALTER TABLE stu_phone ADD CHAR(7) NULL CREATE phone_rule AS @phone LIKE [0-9][0-9][0-9][0-9][0-9][0-9][0-9] sp_bindrule ,stu_phone.电话号码 INSERT INTO stu_phone(学号，姓名，电话号码) VALUES(0009，王国强，1234yyy) --创建日期型默认对象df_date CREATE df_date AS 2006-4-12 GO  --创建字符型默认对象df_char CREATE DEFAULT df_char unknown GO --创建货币型默认对象df_money CREATE DEFAULT AS $100 GO CREATE TABLE stu_fee (学号 char(10) NOT NULL, 姓名 char(8) NOT NULL, 学费 money, 交费日期 datetime, 电话号码 char(7)) df_money,stu_fee.学费 GO sp_bindefault ,stu_fee.交费日期 GO sp_bindefault df_char,stu_fee.电话号码 GO INSERT INTO stu_fee(学号,姓名) values(0001,刘卫平) INSERT INTO stu_fee(学号,姓名,学费) values(0001,张卫民,$120) INSERT INTO stu_fee(学号,姓名,学费,交费日期) VALUES(0001