基于mysql数据库的应用_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