CREATEDATABASE test01_school;USE test01_school;CREATETABLE department(
Depno int(10)primarykeynotnullunique,
depname varchar(20)notnull,
depnote varchar(50));--2CREATETABLE teacher(`number`intprimarykeynotnullunique,
name varchar(30)notnull,
sex varchar(4),
birth date,
Depno int,
salary float,
address varchar(100),--,不能少foreignkey(Depno)references department(Depno));--3insert department values(601,'软件技术系','软件技术等专业'),(602,'网络技术系','多媒体技术等专业'),(603,'艺术设计系','广告艺术设计等专业'),(604,'管理工程系','连锁经营管理等专业');insert teacher values(2001,'Tom','女','1970-01-10',602,4500,'四川省绵阳市'),(2002,'Lucy','男','1983-12-18',601,2500,'北京市昌平区'),(2003,'Mike','男','1990-06-01',604,1500,'重庆市渝中区'),(2004,'James','女','1980-10-20',602,3500,'四川省成都市'),(2005,'Jack','男','1975-05-30',603,1200,'重庆市南岸区');--输入date值,要有''--4select*from teacher;--5select`number`as'教工号',depno as'部门名称'from teacher
where address like'%北京%';--6select`number`,name from teacher where salary =(selectmax(salary)from teacher);--7select`number`,name from teacher where salary
between2500and4000;--8select name ,sex,salary ,depname from teacher
innerjoin department on teacher.depno= department.depno
where depname ='网络技术系';