MySQL数据库的常用命令

create database mydb;//建立数据库
use mydb;//使用数据库
show databases;//显示数据库
drop database mydb;//删除数据库

use mydb;
set names gbk;     //输入中文前先输入此句话，否则报错Invalid default value for。。。。

create table student(
name  char(20) not null,  
age smallint default 0,
nomber  varchar(15) not null unique, 
enterdate date comment '入学日期',  
gender varchar(2) default '男'
);

insert into student (name,age,nomber,enterdate,gender)
value('小明',20,'001','1993-01-14','男');

insert into student
value('小红',18,'002','1995-12-20','女');

insert into student (name,nomber,enterdate)
value('小才','003','1995-12-20');

 

show tables;
show create table student;
desc student;
drop table student;

set names gbk;
create table student(
ID int primary key auto_increment comment '主键',//注释字段
name  char(20) not null,  
age smallint default 0,
nomber  varchar(15) not null unique, 
enterdate date comment '入学日期',  
gender varchar(2) default '男'
);

insert into student (ID,name,age,nomber,enterdate,gender)
value(1,'小明',20,'001','1993-01-14','男');

insert into student
value(2,'小红',18,'002','1995-12-20','女');

insert into student (name,age,nomber,enterdate,gender)
value('小明',20,'001','1993-01-14','男');

insert into student
value(null,'小红',18,'002','1995-12-20','女');

desc student;
select * from student;

 

修改表数据
update student set age=21,enterdate='1992-10-01' where ID=1;
删除表数据
delete from student;
delete from student where ID=2;

数据库查询
select ename,sal from emp;
select ename name,sal*12 income from emp; //起别名
select ename,sal+comm from emp;   // null与任何运算或比较都为null
select ename,sal+ifnull(comm,0) from emp;
select distinct job from emp; //去重

 

导入以下表

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50027
Source Host           : localhost:3306
Source Database       : scott_db

Target Server Type    : MYSQL
Target Server Version : 50027
File Encoding         : 65001

Date: 2013-08-14 10:25:49
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `bonus`
-- ----------------------------
DROP TABLE IF EXISTS `bonus`;
CREATE TABLE `bonus` (
  `ENAME` varchar(10) default NULL,
  `JOB` varchar(9) default NULL,
  `SAL` double default NULL,
  `COMM` double default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of bonus
-- ----------------------------

-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(11) NOT NULL default '0',
  `DNAME` varchar(14) default NULL,
  `LOC` varchar(13) default NULL,
  PRIMARY KEY  (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL default '0',
  `ENAME` varchar(10) default NULL,
  `JOB` varchar(9) default NULL,
  `MGR` int(11) default NULL,
  `HIREDATE` date default NULL,
  `SAL` double default NULL,
  `COMM` double default NULL,
  `DEPTNO` int(11) default NULL,
  PRIMARY KEY  (`EMPNO`),
  KEY `FK_DEPTNO` (`DEPTNO`),
  CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO EMP VALUES 
(7369,'SMITH','CLERK',7902,str_to_date('17-12-1980','%d-%m-%Y'),800,NULL,20); 
INSERT INTO EMP VALUES 
(7499,'ALLEN','SALESMAN',7698,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30); 
INSERT INTO EMP VALUES 
(7521,'WARD','SALESMAN',7698,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30); 
INSERT INTO EMP VALUES 
(7566,'JONES','MANAGER',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20); 
INSERT INTO EMP VALUES 
(7654,'MARTIN','SALESMAN',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30); 
INSERT INTO EMP VALUES 
(7698,'BLAKE','MANAGER',7839,str_to_date('1-5-1981','%d-%m-%Y'),2850,NULL,30); 
INSERT INTO EMP VALUES 
(7782,'CLARK','MANAGER',7839,str_to_date('9-6-1981','%d-%m-%Y'),2450,NULL,10); 
INSERT INTO EMP VALUES 
(7788,'SCOTT','ANALYST',7566,str_to_date('13-7-1987','%d-%m-%Y'),3000,NULL,20); 
INSERT INTO EMP VALUES 
(7839,'KING','PRESIDENT',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10); 
INSERT INTO EMP VALUES 
(7844,'TURNER','SALESMAN',7698,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30); 
INSERT INTO EMP VALUES 
(7876,'ADAMS','CLERK',7788,str_to_date('13-7-1987', '%d-%m-%Y'),1100,NULL,20); 
INSERT INTO EMP VALUES 
(7900,'JAMES','CLERK',7698,str_to_date('3-12-1981','%d-%m-%Y'),950,NULL,30); 
INSERT INTO EMP VALUES 
(7902,'FORD','ANALYST',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20); 
INSERT INTO EMP VALUES 
(7934,'MILLER','CLERK',7782,str_to_date('23-1-1982','%d-%m-%Y'),1300,NULL,10); 

-- ----------------------------
-- Table structure for `salgrade`
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` int(11) default NULL,
  `LOSAL` double default NULL,
  `HISAL` double default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

 

练习

 

练习：求每个员工的年总收入(包含提成)
select ename,(sal+ifnull(comm,0))*12 from emp;

where查询
select ename,sal from emp where sal>1500;
select ename,hiredate from emp where hiredate<='1983-01-01';
select deptno,ename from emp where deptno!=30;

select ename,sal from emp where sal>=1500 and sal<=2000;
select ename,sal from emp where sal between 1500 and 2000;
select deptno,ename from emp where deptno=20 or deptno=30;

练习：
1 查询部门为20且工资大于1200的员工
2 查询工资大于1500或者入职日期早于 23-JAN-82 的员工

select ename,deptno,sal from emp where deptno=20 and sal>1200;
select ename,sal,hiredate from emp where  sal>1500 or hiredate<'1982-01-23';

select ename,comm from emp where comm!=null;
select ename,comm from emp where comm is not null;
select ename,mgr from emp where mgr is null;

模糊查询
 - ： 代表1个任意字符
  % : 代表0到多个任意字符
select ename from emp where ename like 'sm%';
select ename from emp where ename like '%m%';
select ename from emp where ename like '_a%';
select ename from emp where ename like '%a%' and ename like '%e%' ;
select ename from emp where ename like '%r';

between and 练习
select ename,(sal+ifnull(comm,0))*12 from emp where(sal+ifnull(comm,0))*12 between 5000 and 15000;
select ename,hiredate from emp where hiredate not between '1981-02-22'and '1981-06-09';

in 查询
（频率出来高的放在前面）
select empno from emp where empno in(7900,7902,7908,7566);

order by排序

select ename,sal from emp order by sal asc;
select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal desc,ename;

练习：
找出所有有提成的员工，列出名字、工资、提成
，显示结果按工资从小到大，提成从大到小

select ename,sal,comm from emp where comm is not null
order by sal,comm desc;

 lower :转成小写
  - upper : 转成大写
  - concat:连接多个字符串
  - length :求字符串长度
  - replace: 替换字符串中指定的字符
  - substr　：截取子串，下标从１开始算起，可以是负数，表示从后面开数

 select lower(ename) from emp;
 select ename from emp where upper(ename)='SMITH';
select upper('hello');
select now();
select 15*15+100;
 
select concat(ename,'=',sal) from emp;
select ename, length(ename) from emp;

char - 定长字符串，不够就补空格，直到补够
varchar - 可变长字符串，是多少就是多少

select ename,replace(ename,'A','#') from emp;
select ename,substr(ename,2) from emp;
select ename,substr(ename,2,3) from emp;

练习：
找出名字中含有A和E的员工，将他们的名字按首字母大写，其它小写，并且降序列出
select ename, concat(upper(substr(ename,1,1)),
lower(substr(ename,2))) en
from emp where ename like '%a%' and ename like '%e%'
order by ename desc;

round() -四舍五入
floor() - 函数只返回整数部分，小数部分舍弃,下舍入
ceiling() - 函数只返回整数部分，小数部分舍弃，上舍入

select round(45.932,2);
select round(45.932,0);
select round(45.932,-1);
select floor(-45.932);
select ceiling(45.9);

now() - 当前系统时间
week(日期) - 取得某个时间点的在一年中是哪一周
month(日期) - 取得传入的日期属于哪个月
day(日期) - 取得传入的日期属于一个月中的哪一天
date_add(now(),interval 1 day); -在某个日期上加上相应的数量，可以负数
datediff(日期1,日期2):求2个日期之间的天数
str_to_date():字符串转日期
date_format(): 将日期类型格式化成指定格式的字符串显示

select week(now());
select month();
select dayofyear(now());
select date_add(now(),interval 15 day);
select date_add(now(),interval -1 year);
select datediff(now(),'2016-01-01');
select str_to_date('2016年2月15日','%Y年%m月%d日');
select date_format('2016-02-15','%Y年%m月%d日');
select ename,date_format(hiredate,'%Y年%m月%d日') from emp;

所有的组函数会自动忽略空值记录
select avg(sal) from emp;
select avg(comm) from emp;
select avg(ifnull(comm,0)) from emp;
select count(*) from emp;
select count(comm) from emp where comm is not null;  //有风险，除非都不为空
select max(sal) from emp;
select sum(sal) from emp;

练习：
1 求30号部门最高的工资，最低的工资，工资的平均值
2 求员工一共分布在几个部门

select max(sal),min(sal),avg(sal) from emp where deptno=30;
select count(distinct deptno) from emp;

分组查询
1 单个字段分组
求各个部门的工资总和
select deptno, sum(sal) from emp group by deptno;

2 多个字段一起分组,一定是多个字段全部相同才认为是一组
求各个部门，各种职位的平均工资
select deptno,job,avg(sal) from emp group by deptno,job;

3 对分组的结果进行过滤(having)
where是用来过滤非分组的结果，一般用于分组前进行过滤
求部门总人数大于4的部门
select deptno,count(*) from emp group by deptno having count(*)>4;

 

练习：
1、哪些部门工资高于1000的人数超过2人，列出部门编号 和相应工资。
2、平均工资大于1500的部门的人数
select sal,deptno,count(*) from emp where sal>1000 group by deptno having count(*)>2;
select deptno,avg(sal),count(*) from emp group by deptno having avg(sal)>1500;

多表连接查询
等值
select ename,dname from dept,emp;
select ename,dname from dept,emp where emp.deptno=dept.deptno;
select ename,dname from dept d,emp e where e.deptno=d.deptno and upper(ename)='smith';

非等值
select ename,sal,grade from emp e,salgrade sg where e.sal
between sg.losal and sg.hisal;

select ename,sal,grade from emp,salgrade
where sal between losal and hisal order by sal;

练习：
查询工资是第三等级的员工姓名和所在部门名称。
select ename,dname,sal,grade from emp,dept,salgrade where salgrade.grade=3
and sal between losal and hisal and emp.deptno=dept.deptno;

查询工资是第四等级的员工人数
select count(*),grade from emp,salgrade where salgrade.grade=4
and sal between losal and hisal;

自连接
select e.ename, e.mgr, m.ename, m.empno from emp e,emp m where e.mgr = m.empno;

1 左外连接 left  join
ps:会在右表模拟空记录匹配
select e.ename, e.mgr, m.ename, m.empno
from emp e left join emp m on e.mgr = m.empno;

2 右外连接 (right join)
ps:在左表补空
需求：查询员工和员工工作的部门，包括没有员工的部门也要查出
select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;

子查询

select ename,job from emp where job in(select job from emp where ename='smith')
and ename!='smith';

练习：
找出与20号部门员工干相同工作的其它部门的员工
select distinct job from emp where deptno=20;
select * from emp where deptno!=20 and job in (select distinct job from emp where deptno=20);

练习:
找出最高工资的员工是谁？
select ename,sal from emp where sal=(select max(sal) from emp);

哪些部门的人数比20号部门的人数多。
select deptno,count(*) from emp group by deptno
having count(*)>(select count(*) from emp where deptno=20);

哪些员工的工资，介于20和30部门平均工资之间。
select ename,sal from emp where sal between(select avg(sal) from emp where deptno=30)
and (select avg(sal) from emp where deptno=20);

哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
select ename,sal from emp where sal>(select avg(sal) from emp)
order by sal desc,ename desc;

是否有员工在同一个部门，而且工资相同，列出这样的部门号和平均工资、人数.
select deptno,count(*),avg(sal) from emp
where deptno in(select deptno from emp group by deptno,sal having count(*)>=2)
group by deptno;

哪些员工的工资，高于他的领导，列出员工的名字。
select e.ename,e.sal,m.ename,m.sal from emp e,emp m where e.mgr = m.empno and e.sal>m.sal;

找出平均工资最高的部门，列出该部门的编号，名称，所在位置。

select DEPTNO ,DNAME ,LOC  from DEPT
where DEPTNO = (select DEPTNO from EMP group by DEPTNO
  having avg(SAL) = (select max(avgsal) from (select avg(SAL) avgsal from EMP
  group by DEPTNO
     ) TMP
  )
);