SQL学习笔记3（凌乱的代码）

use netmeeting select * from Employee select distinct * from Employee select employee as 员工,department as 部门,pwd as 密码 from Employee-- select 姓名=Employee,部门=Department,密码=pwd from Employee-----------以上这两种取别名的方法都可以 select em.employee,rm.RoomName from Employee em inner join Room rm on em.department=rm.RoomID --取两个表中符合on的数据 select em.*,dm.department from Employee em inner join Room rm on em.department=rm.RoomID inner join Department dm on rm.RoomID=dm.id select department,employee,pwd from employee where employee like'%凯%'--模糊查询 select Employee,Department,pwd from Employee where employee in('赵凯','曾志超') insert into Employee select top 2* from Employee select top 3* from Employee select top 50 PERCENT * from employee--返回50%的数据 insert into employee(employee,department) values('loverszhaokai',1) update Employee set pwd=123 where employee='loverszhaokai' insert into Employee ( employee, department ) select dm.department,dm.id from Department dm update Employee set employee=5 from Employee em inner join Department dm on dm.department=em.employee delete from Employee where employee='5' insert into Room (Compere,RoomName) values('loverszhaokai','SQL') select * from Room select 学号=em.id,员工=em.employee,部门=dm.department,密码=em.pwd from Employee em right join Department dm on em.department=dm.id select * from Employee em right join Department dm on em.department=dm.id insert into Employee (employee,department) values('error',5) delete from Employee where department=4 select * from Employee em full join Department dm on em.department=dm.id select em.id,em.employee from Employee em union all select dm.id,dm.department from Department dm declare @UserName varchar(50) select @UserName=employee from Employee where department=3 print @UserName select * from Employee if (2 in (1,2,3)) print 'lovers' select em.* from Employee em where em.department not in ( select dm.id from Department dm ) use netmeeting select *, case em.department when 1 then '广告部' when 2 then '宣传部' else 'others' end as 'wawa' from Employee em select COUNT_BIG(*) as xishu , MAX(department) as maxe ,MIN(department) as mine from Employee select em.* from Employee em where em.department=RAND(4) declare @int int set @int=RAND(5) print @int DECLARE @counter smallint; SET @counter = 1; WHILE @counter < 5 BEGIN SELECT RAND() Random_Number SET @counter = @counter + 1 END; GO select RAND(5) select RAND(100) select ASCII(em.employee) as asciima from Employee em where em.department=1 use netmeeting create table [admin] ( AdminID int PRIMARY KEY IDENTITY , LoginName varchar(50) not null unique ) drop table admin create table [admin] ( AdminID int primary key ) insert into admin (AdminID,loginname) values(2,'error') select * from admin use netmeeting go create view vwemployee as select em.id,em.employee,dm.department from Employee em inner join Department dm on em.department=dm.id go select * from vwemployee go drop view vwemployee go select * from View_1 use netmeeting go create proc GetEmployeeInfo as select em.id,em.employee,dm.department from Employee em inner join Department dm on em.department=dm.id exec GetEmployeeInfo drop proc GetEmployeeInfo go create proc GetEmployeeInfo (--定义参数和参数的默认值 @employee varchar(10)='lovers' ) as select em.id,em.employee,dm.department from Employee em inner join Department dm on em.department=dm.id where em.employee=@employee exec GetEmployeeInfo