MySQL数据库应用
参考资料：
准备软件：mysql-5.5.13-win32.msi、MySQL安装图解.doc、Navicat_for_MySQL_10.0.5.0_XiaZaiBa.exe、mysqldriver.jar、eclipse
MySQL基本操作
登陆：mysql -u 用户名 -p 密码
显示数据库：show databases;
使用数据库：use 数据库名;
显示所有表：show tables;
新建数据库：create database 数据库名;
建新表：
create table student1(
id int not null auto_increment primary key,
name varchar(10),
age int(2),
sex varchar(5) default 'man'
);
显示建表信息：desc 表名;
删除数据库：drop database 数据库名;
删除表：drop table 表名;
增加字段：alter table 表名 add 字段名 类型;
删除字段：alter table 表名 drop 字段名;
查看字段：desc 表名;
修改字段：alter table 表名 change 字段名 新字段名 新数据类型;
显示表的编码：show create table 表名;
修改表的编码：alter table 表名 default charset utf8;
MySQL字段查询
查询所有: select * from 表名;
分页查询: select 字段 from 表名 limit 从第几条开始,页长;
条件查询:
select 字段 from 表名 where 条件;
select 字段 from 表名 where 数据 between 最小值 and 最大值;
模糊查询: %:零个或多个字符 _ : 一个任意字符
select * from employee where first_name like '%jack%';
in 子语:select * from employee where emp_no in (10010,10200,10205);
count()函数: max(); min(); avg(); sum();
order by 排序: asc 升序，desc降序
分组函数: group by
MySQL多表查询
左右连接:select * from t1 right join t2 on t1.id=t2.id;
内连接: select * from t1,t2 where t1.id = t2.id;
外连接:
select * from t1 full join t2;--数据交叉相乘
select * from t1,t2;
习题
#1、查出男员工的人数
SELECT COUNT(*) FROM employees WHERE gender='M';
#2、查出1998年8月1号——10号入职的员工，升序排列。
SELECT * FROM employees WHERE
employees.hire_date BETWEEN '1998-08-01' AND '1998-08-10'
ORDER BY
employees.hire_date ASC
#3、查出最高工资员工名字及所在部门
SELECT employees.emp_no,employees.first_name,employees.last_name,departments.dept_name,salaries.salary
FROM dept_emp,employees,departments,salaries WHERE salaries.salary=(
SELECT MAX(salary)FROM salaries
)AND dept_emp.emp_no=salaries.emp_no AND employees.emp_no=salaries.emp_no AND departments.dept_no=dept_emp.dept_no
#4、查找各部门经理的员工id、名字、部门
SELECT employees.emp_no,employees.first_name,employees.last_name,departments.dept_name
FROM employees,departments,dept_manager WHERE
departments.dept_no=dept_manager.dept_no AND employees.emp_no=dept_manager.emp_no
AND EXISTS(SELECT emp_no FROM dept_manager WHERE EXISTS(SELECT * FROM employees.departments))
#5、找最早入职的员工
SELECT * FROM employees.employees WHERE employees.employees.hire_date=(
SELECT MIN(employees.employees.hire_date) FROM employees.employees)
#6、找出工资最高的部门经理
SELECT employees.emp_no,employees.first_name,employees.last_name,departments.dept_name,salaries.salary
FROM dept_manager,employees,departments,salaries WHERE salaries.salary=(
SELECT MAX(salaries.salary)
FROM employees,departments,dept_manager,salaries WHERE
departments.dept_no=dept_manager.dept_no AND employees.emp_no=dept_manager.emp_no AND salaries.emp_no=dept_manager.emp_no
AND EXISTS(SELECT emp_no FROM dept_manager WHERE EXISTS(SELECT * FROM employees.departments))
)AND dept_manager.emp_no=salaries.emp_no AND employees.emp_no=salaries.emp_no AND departments.dept_no=dept_manager.dept_no
#SQL查询作业@2018-2-8 16:04:52
#10、统计各岗位女员工数（F代表女性）
SELECT departments.dept_name,COUNT(employees.gender) FROM departments,employees,dept_emp
WHERE departments.dept_no=dept_emp.dept_no AND employees.emp_no=dept_emp.emp_no
AND EXISTS(SELECT *FROM employees WHERE gender='F')
GROUP BY departments.dept_name
#12、员工平均工资最高的部门
SELECT departments.dept_name,AVG(salaries.salary) FROM departments,employees,dept_emp,salaries
WHERE departments.dept_no=dept_emp.dept_no AND employees.emp_no=dept_emp.emp_no AND salaries.emp_no=employees.emp_no
GROUP BY departments.dept_name ORDER BY AVG(salaries.salary) DESC LIMIT 1
#13、找出除销售之外的所有员工
SELECT * FROM employees,dept_emp,departments WHERE dept_name!='Sales' AND departments.dept_no=dept_emp.dept_no AND
dept_emp.emp_no=employees.emp_no
