mysql常用DQL命令

参考文献:http://www.monkey1024.com/database/811

DQL是从数据库中查看数据.

---

# 创建database
create database monkey1024;
 
# 创建tables  岗位信息
create table dept(
    DEPTNO int(2),
    DNAME varchar(14),
    LOC varchar(13)
);
# 插入数据
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');

# 创建tables    员工信息
create table emp(
    EMPNO int(4),
    ENAME varchar(10),
    JOB varchar(9),
    MGR int(4),
    HIREDATE date,
    SAL double(7,2),
    COMM double(7,2),
    DEPTNO int(2)
);
# 插入数据
# 括号里面的内容:员工编号;姓名;岗位;上级领导的标号;入职日期;薪水;补助;部门编号
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

# 创建tables    薪水级别
create table salgrade(
    GRADE int(11),
    HISAL int(11),
    LOSAL int(11)
);
# 插入数据
INSERT INTO salgrade VALUES (1,1200,700); 
INSERT INTO salgrade VALUES (2,1400,1201); 
INSERT INTO salgrade VALUES (3,2000,1401);
INSERT INTO salgrade VALUES (4,3000,2001);
INSERT INTO salgrade VALUES (5,9999,3001);

 

 1.在一个表中查询查询

select ENAME,JOB FROM emp;

 

 2.在emp中查询姓名,工作岗位和年薪

select ENAME,JOB,SAL*12 FROM emp;

select ENAME,JOB,SAL*12 as yearsal FROM emp;

 

 注:在月薪后面*12即可;as可以省略;as后面是别名.

3.查看全部信息

select * from emp;

 

 注:不建议这样使用*,因为mysql会自动的把* 转换成表中的信息,这样会大大降低性能.

 4.查询薪水是5000的员工

select ENAME,EMPNO FROM emp where SAL=5000;

 

 5.查询工作是CLERK的员工

select ENAME,EMPNO FROM emp where JOB='CLERK';

 

 6.查询薪水不等于5000的员工

select ENAME from emp where SAL <>5000;

 

 7.查询薪水1600-3000的员工

select ENAME from emp where SAL>=1600 and SAL<=3000;

 

 8.查询津贴为空的员工

select ENAME from emp where COMM is null;

 

 9.查询工作岗位

select ENAME from emp where COMM is null;

 

10.查询工作为CLERK或MANAGER的员工

select ENAME,JOB from emp where JOB='CLERK' or JOB='MANAGER';

 

11.查询薪水大于1800并且部门编号是20或30的员工

select ENAME,JOB,SAL from emp where SAL>=1800 and (DEPTNO='20' or DEPTNO='30');

 

12.查询工作为CLERK或MANAGER的员工

select ENAME,JOB from emp where JOB='CLERK' or JOB='MANAGER';

 

 注:in的执行效率低.

13.like 模糊查询,数据量大的时候,影响性能;