MySQL新手学习随笔

首先创建新的数据库:

create database Mydb;


使用新建的数据库先创建一个新的表:

create table employee(
  employee_id int(10) primary key auto_increment,
 employee_name varchar(20),
  empolyee_department varchar(20),
  employee_salary double,
  employee_department_id int(6)
);

    也可以使用MySQL workbench创建

创建完表,开始插入数据,这里插入三条数据

insert into employee(employee_id,employee_name,empolyee_department,employee_salary,employee_department_id)
value(1001,"TYS","研发部",15000,2018);
insert into employee(employee_id,employee_name,empolyee_department,employee_salary,employee_department_id)
value(1002,"PXF","销售部",12000,2019);
insert into employee(employee_id,employee_name,empolyee_department,employee_salary,employee_department_id)
value(1003,"ZDH","采购部",20000,2017);

再创建一个department表

create table department(
		department_id  int(10)  primary key,
        department_name varchar(20),
        department_location varchar(20),
        department_manager_id int(10),
        department_manager_name varchar(20)
        
        );
        insert into department(department_id,department_name,department_location,department_manager_id,department_manager_name)
								value(2018,"研发部","重庆",3001,"TYS");
		insert into department(department_id,department_name,department_location,department_manager_id,department_manager_name)
								value(2019,"销售部","重庆",3002,"PXF");
		insert into department(department_id,department_name,department_location,department_manager_id,department_manager_name)
								value(2017,"采购部","重庆",3003,"LCP");

数据库删除修改操作不做过多描述,接下来重点关注下查询操作:

1.首先查询表中所有员工

select  employee_name from employee;

2.查询工资为10000,,15000:

select employee_name from employee where employee_salary in (10000,15000)

 

3.本公司最高工资

select max(employee_salary) from employee;

4.年龄18和30之间的

select employee_name from employee where employee_age bewteen 18 and 30;

5.每个部门的最高工资

select max(employee_salary)
	from employee
	group by department_id;
6.部门的最大工资的员工的信息
select * from 
	(select max(employee_salary) max
		from employee
		group by department_id) e join employee on employee.employee_salary == e.max;

7.两个数据表的合并查询 join on 操作:

(1)简单的将两个表合并查询

select * from left join department on  employee.employee_department_id=department.department_id;

还有很多操作就不在累述了,接下来该用JDBC操作数据库了。











阅读更多
想对作者说点什么? 我来说一句

JS学习笔记(全)

2016年03月31日 73KB 下载

ACE学习随笔

2008年01月15日 43KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭