mysql

创建表：
	create table 表名(
	字段名 类型 约束,
	字段名 类型 约束
	…
);
例：create table students(
	id int unsigned primary key auto_increment,
	name varchar(10),
	age int unsigned,
	height decimal(5,2)
	);
	姓名(长度为10)，身高(保留小数点2位)，年龄，
需要主键，id 设置成主键，整数、无符号、自动递增

　　

增加表：
语法一：所有字段设置值，==值的顺序与表中字段的顺序对应,值的个数必须与字段的个数相同==
	insert into 表名 values(...)
	例：插入一个学生，设置所有字段的信息
	insert into students values(0,'凯',20,170.5);
	注意：对于自动增长的字段，插入时需要占位，通常使用0或者 default 或者 null 来占位

语法二：部分字段设置值，==值的顺序与给出的字段顺序对应，值的个数必须与字段的个数相同==
	insert  into  表名(字段1,...)  values(值1,...)
	例：插入一个学生，只设置姓名
	insert  into  students(name)  values('王昭君')
	例：插入多个学生，设置所有字段的信息
	insert into students values(null,'凯31',20,100.5),(null,'凯32',20,100.5);
	例：插入多个学生，只设置姓名
	insert into students(name) values('王昭君33'),('王昭君34');

　　

删除表：
语法1
	drop table 表名
	drop table students;
	注意：删除不存在的表，会报错
语法二：
	drop table if exists 表名
	例：删除学生表
	drop table if exists students2;
注意：删除不存在的表，不会报错，如果表存在才删除

truncate table 表名（删除表的所有数据，保留表结构）
delete from 表名 where 条件
例：删除id为6的学生数据
delete from students where id=6;
注意：如果没有写where条件，会删除所有数据

　　

修改表：
语法一：
	update 表名 set 列1=值1,列2=值2... where 条件
	例：修改id为5的学生数据，姓名改为 狄仁杰，年龄改为 20
	update students set name='狄仁杰',age=20 where id=5
	注意：如果同时修改多个字段，需要用逗号分隔，如果没有写where条件，会修改所有的数据
	修改表----alter
	修改表名
	alert table 表原名rename 新名：
	修改表-添加字段
	alter table 表名add 列名 类型;例：alter table students add birthday datetime;
	修改表-修改字段：重命名版
	alter table 表名change 原名 新名 类型及约束;例：alter table students change birthday birth datetime not null;
	修改表-修改字段（等价于添加字段）：不重命名版 modify：修改
	alter table 表名modify 列名 类型及约束;例：alter table students modify birth date not null;
	修改表-删除字段
	alter table 表名drop 列名;例：alter table students drop birthday;

　　

查询表：

部门表：（departments）
	create table departments (
	  deptid int(10) primary key, 
	  deptname varchar(20) not null -- 部门名称
	);
	insert into departments values ('1001', '市场部');
	insert into departments values ('1002', '测试部');
	insert into departments values ('1003', '开发部');

员工表：（employees）
create table employees (                                                            
  empid int(10) primary key,
  empname varchar(20) not null, -- 姓名
  sex varchar(4) default null, -- 性别
  deptid int(20) default null, -- 部门编号
  jobs varchar(20) default null, -- 岗位
  politicalstatus varchar(20) default null, -- 政治面貌
  leader int(10) default null
);
insert into employees values ('1', '王昭君', '女', '1003', '开发', '群众', '9');
insert into employees values ('2', '诸葛亮', '男', '1003', '开发经理', '群众', null);
insert into employees values ('3', '张飞', '男', '1002', '测试', '团员', '4');
insert into employees values ('4', '白起', '男', '1002', '测试经理', '党员', null);
insert into employees values ('5', '大乔', '女', '1002', '测试', '党员', '4');
insert into employees values ('6', '孙尚香', '女', '1001', '市场', '党员', '12');
insert into employees values ('7', '百里玄策', '男', '1001', '市场', '团员', '12');
insert into employees values ('8', '小乔', '女', '1002', '测试', '群众', '4');
insert into employees values ('9', '百里守约', '男', '1003', '开发', '党员', '9');
insert into employees values ('10', '妲己', '女', '1003', '开发', '团员', '9');
insert into employees values ('11', '李白', '男', '1002', '测试', '团员', '4');
insert into employees values ('12', '孙膑', '男', '1001', '市场经理', '党员', null);

工资表：（salary）
create table salary (
  sid int(10) primary key,
  empid int(10) not null,
  salary int(10) not null -- 工资
);
insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');

　

-- **1）列出总人数大于4的部门号和总人数
-- 查询每个部门的总人数
select deptid,count(*) from employees group by deptid;
-- 过滤数据
select deptid,count(*) from employees group by deptid having count(*)>4;
​
-- **2）列出开发部和和测试部的职工号、姓名
-- 连接部门与员工表
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid;
-- 过滤
select emp.empid,emp.empname,dep.deptname from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where dep.deptname in('开发部','测试部');
​
-- **3）求出各部门党员的人数，要求显示部门名称
-- 连接部门与员工表
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid;
-- 过滤党员
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员';
-- 分组统计
select dep.deptname,count(*) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员'
group by dep.deptname;
​
-- **4）列出市场部的所有女职工的姓名和政治面貌
-- 连接部门与员工表
select * from employees as emp
inner join departments as dep on emp.deptid=dep.deptid;
-- 过滤女职工 且是市场部
select emp.empname,emp.politicalstatus from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.sex='女' and dep.deptname='市场部';
​
-- **5）显示所有职工的姓名、部门名和工资数
-- 连接3个表
select emp.empname,dep.deptname,sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;
​
-- **6）显示各部门名和该部门的职工平均工资
-- 连接3个表
select emp.empname,dep.deptname,sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;
-- 分组统计
select dep.deptname,avg(sa.salary) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by dep.deptname;
​
-- **7）显示工资最高的前3名职工的职工号和姓名
-- 连接员工表和工资表
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
-- 按照工资倒序，取前3名
select emp.empid,emp.empname from employees as emp
inner join salary as sa on sa.empid=emp.empid
order by sa.salary desc
limit 3
​
-- **8）列出工资在1000－2000之间的所有职工姓名
-- 连接员工表和工资表
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
-- 过滤工资在1000－2000之间
select emp.empname,sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary between 1000 and 2000;
​
-- **9）列出工资比王昭君高的员工
-- 查询王昭君的工资
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君'
-- 查询所有人的工资，条件 比王昭君的工资高
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary>(select salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君');
​
-- **10）列出每个部门中工资小于本部门平均工资的员工信息
-- 查询每个部门的平均工资
select dep.deptname,avg(sa.salary) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by dep.deptname;
-- 查询每个人的工资
select emp.empname,dep.deptname,sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;
-- 连接前面两个结果
select emp.empname,dep.deptname,sa.salary,avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empip
inner join 
​(select dep.deptname,avg(sa.salary) 
as avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by dep.deptname) as temp on dep.deptname=temp.deptname
where sa.salary<temp.avg_salary;

转载于:https://www.cnblogs.com/liwenhua666/p/11110401.html