MySQL练习一

#创建mis数据库
create database mis;
#创建部门表部门编号主键，自增长，部门名称，唯一约束
create table Dept(DeptID int primary key auto_increment,DeptName varchar(20) unique);
#创建员工表，员工编号主键，自增长，1：男，0：女，部门编号外键
create table Employee(EmpID int primary key auto_increment,EmpName varchar(20),Birthday date,Gender varchar(6),Salary double,DeptID int,
foreign key(DeptID) references Dept(DeptID));
#插入dept数据
insert into dept (DeptID,DeptName) values (1,'开发部门');
mysql> insert into dept (DeptID,DeptName) values (2,'测试部门');
#插入Employee数据
insert into Employee (EmpID,EmpName,Birthday,Gender,Salary,DeptID) values (1,'林冲','1981-10-10','男',2800,1);
insert into Employee (EmpID,EmpName,Birthday,Gender,Salary,DeptID) values (2,'宋江','1992-6-21','男',3100,2);
 insert into Employee (EmpID,EmpName,Birthday,Gender,Salary,DeptID) values (3,'扈三娘','1984-3-8','女',3000,1);
insert into Employee (EmpID,EmpName,Birthday,Gender,Salary,DeptID) values (4,'孙二娘','1985-6-7','女',2950,2);
#查询工资大于2900的员工信息
select * from Employee where Salary>2900;
#查询年龄大于23岁的男员工信息
select *,TIMESTAMPDIFF(YEAR,Birthday, CURDATE()) 年龄 from Employee where Gender='男'
 and TIMESTAMPDIFF(YEAR,Birthday, CURDATE())>23;
#将孙二娘的出生日期改为1985-6-8
update Employee set Birthday='1985-6-8' where EmpName='孙二娘';
#删除员工扈三娘
delete from Employee where EmpName='扈三娘';
#统计男员工和女员工人数
select count(if(Gender='男',1,null)) 男员工,count(if(Gender='女',1,null)) 女员工 from Employee;
#统计最高工资与最 低工资差
select max(Salary)-min(Salary) 工作差 from Employee;
#查找工资最低的两个员工
 select * from Employee order by Salary asc limit 0,2;
#按照工资降序排序员工信息
select * from Employee order by Salary desc;
#删除测试部门及其员工
delete Employee from Dept,Employee where Dept.DeptID=Employee.DeptID and Dept.DeptName='测试部门';
alter table Employee drop foreign key employee_ibfk_1;
delete from Dept where DeptName='测试部门';
#查询员工信息，显示姓名，部门，薪水
select e.EmpName,d.DeptName,e.Salary from Dept d,Employee e where d.DeptID=e.DeptID;

MySQL其他

show create table 表名 查看创建表结构

auto_increment 设置自增长

limit x,y 设置查询结果为前y-x个

将多个列的值合并

select concat(列名1,列名2,.........) as 别名 from 表名;

删除表中数据的两种方式

1.delete from 表名; 逐行删除，开销较大。

2.truncate table 表名; 截断表，效率高。

注：

1.对于有外键约束的表应先删除子表再删除主表。

2.在开发中查询数据库尽量不使用 *（星号）。

CRUD操作

创建表

create table 表名(列名1 数据类型1,..........);

查询表内容

select 查询内容 from 要查询的表名 where 查询的条件;

注：查询数据需要多个表时应尽量使用多表连接查询，不使用笛卡儿积。

更新表内容

update 表名 set 列名1=value1，........where 更新条件;

删除表内容

delete from 表名 where 删除条件;

插入表内容

insert into 表名 (字段1,字段2,.....) values (值1,值2,.........);

约束

主键（primary key）alter table 表名 add primary key(列名);

外键（foreign key）alter table 子表名 add [constraint fk_sno] foreign key(子表的外键名称) references 父表名（父表的主键名称）;

默认（default） alter table 表名 change column 列名 新列名 新数据类型 default 默认值;

非空（not null）

唯一（unique） alter table 表名 add comstraint unique(列名);

获取当前日期函数

current-date()

now()

curdate()

连接查询

1.笛卡儿积

select * from 表1,表2,........；

2.select * from 表1 join 表2 on 表1.列1=表2.列2;