參考資料：

準備軟件：mysql-5.5.13-win32.msi、MySQL安裝圖解.doc、Navicat_for_MySQL_10.0.5.0_XiaZaiBa.exe、mysqldriver.jar、eclipse

MySQL基本操作

登陸：mysql -u 用戶名 -p 密碼

顯示數(shù)據(jù)庫：show databases;

使用數(shù)據(jù)庫：use 數(shù)據(jù)庫名;

顯示所有表：show tables;

新建數(shù)據(jù)庫：create database 數(shù)據(jù)庫名;

建新表：

create table student1(

id int not null auto_increment primary key,

name varchar(10),

age int(2),

sex varchar(5) default 'man'

);

顯示建表信息：desc 表名;

刪除數(shù)據(jù)庫：drop database 數(shù)據(jù)庫名;

刪除表：drop table 表名;

增加字段：alter table 表名 add 字段名 類型;

刪除字段：alter table 表名 drop 字段名;

查看字段：desc 表名;

修改字段：alter table 表名 change 字段名 新字段名 新數(shù)據(jù)類型;

顯示表的編碼：show create table 表名;

修改表的編碼：alter table 表名 default charset utf8;

MySQL字段查詢

查詢所有: select * from 表名;

分頁查詢: select 字段 from 表名 limit 從第幾條開始,頁長;

條件查詢:

select 字段 from 表名 where 條件;

select 字段 from 表名 where 數(shù)據(jù) between 最小值 and 最大值;

模糊查詢:   %:零個或多個字符    _ : 一個任意字符

select * from employee where first_name like '%jack%';

in 子語:select * from employee where emp_no in (10010,10200,10205);

count()函數(shù):  max();  min();   avg();   sum(); 

order by 排序:  asc 升序，desc降序

分組函數(shù): 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;--數(shù)據(jù)交叉相乘

select * from t1,t2;

習題

#1、查出男員工的人數(shù)

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、查找各部門經(jīng)理的員工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、找出工資最高的部門經(jīng)理

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查詢作業(yè)@2018-2-8 16:04:52

#10、統(tǒng)計各崗位女員工數(shù)（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