1. 表的创建、修改、删除
2. 数据的插入、修改、删除
3. 常用的sql查询和使用
0）and、or、in、between...and...、exist、is null、like、四则运算符
1）where子句
2）order by子句
3）group by子句
4）having子句
5）全连接、内连接、外连接
6）子查询、集合查询
7）函数 max()/min()/avg()/sum()/count()/ifnull()
如果您把这些语句一句句去执行一遍，您就会发现这些语句的用途；心动不如行动！
如果大家有更好的SQL语句学习，不妨共享出来大家学习下！
1、create database emp;
2、
create table emp(
EMPNO INT(4) not null,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE,
SAL INT(7),
COMM INT(7),
DEPTNO INT(2)
);
3、
CREATE TABLE DEPT(
DEPTNO INT(2) not null,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
4、
insert into dept(DEPTNO,DNAME,LOC) values('10','ACCONTING','NEWYORK');
insert into dept(DEPTNO,DNAME,LOC) values('20','RESEARCH','DALLAS');
insert into dept(DEPTNO,DNAME,LOC) values('30','SALES','CHICAGO');
insert into dept(DEPTNO,DNAME,LOC) values('40','OPERATION','BOSTON');
5、向emp表中插入如下数据（可以拷贝如下语句直接在客户端运行即可）
方法1：
第1步，在本地磁盘E盘创建文本文件，其中的文本内容如题5格式；保存为emp.txt
第2步，在mysql客户端运行命令 load data local e:\emp.txt into table emp；
效率高，载入数据易出错
方法2：
insert into temp values('','','','','','','');
一行行插入数据，效率比较低
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7369,'smith','clerk',7902,'1986-12-17','1100.00',null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7499,'allen','salesman',7698,'1981-2-20','1600.00',500.00,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7521,'ward','salesman',7698,'1981-2-22','1250.00',500.00,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7566,'jones','manager',7839,'1981-4-2','2975.00',null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7654,'martin','salesman',7698,'1981-9-28','1250.00',1400.00,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7698,'blake','manager',7839,'1981-5-1','2850.00',null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7782,'clark','manager',7839,'1981-6-9','2450.00',null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7788,'scott','analyst',7566,'1987-4-19','3000.00',null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7839,'king','president',NULL,'1981-11-17','5000.00',null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7844,'turner','salesman',7698,'1981-9-8','1500.00',0.00,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7876,'adams','clerk',7788,'1987-5-23','1100.00',null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7900,'james','clerk',7698,'1981-12-3','950.00',null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7902,'ford','analyst',7566,'1981-12-3','3000.00',null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7934,'miller','clerk',7782,'1982-1-23','1300.00',null,10);
6、select * from DEPT;
7、select ENAME,JOB,SAL,DEPTNO FROM EMP;
8、SELECT all DEPTNO,JOB FROM EMP;
9、SELECT DISTINCT DEPTNO,JOB FROM emp;
10、select ENAME,IFNULL((SAL+COMM)*12,SAL*12) from emp ORDER BY SAL DESC;
11、 select ENAME,SAL FROM EMP WHERE SAL>2000;
12、SELECT JOB,SAL FROM EMP WHERE ENAME='SCOTT';
13、select ENAME FROM EMP WHERE HIREDATE>'1982-1-1';
14、SELECT ENAME,SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000;
15、SELECT ENAME,SAL FROM EMP WHERE ENAME LIKE 'S%';
16、SELECT ENAME,SAL FROM EMP WHERE ENAME LIKE '__A%'; //注释：中间2个横线
17、SELECT ENAME,JOB FROM EMP WHERE SAL='800' OR SAL='1250';
18、SELECT ENAME,SAL FROM EMP WHERE MGR IS NULL;
19、SELECT ENAME,SAL,JOB,DEPTNO FROM EMP WHERE DEPTNO='20' AND JOB='CLERK';
20、SELECT ENAME,SAL,JOB,DEPTNO FROM EMP WHERE SAL>2500 OR JOB='MANAGER';
21、SELECT * FROM EMP where COMM is NOT NULL;
22、SELECT ENAME,SAL,COMM FROM EMP WHERE DEPTNO='30' ORDER BY SAL asc;
23、SELECT ENAME,SAL,COMM FROM EMP WHERE DEPTNO='30' ORDER BY SAL,COMM DESC;
24、UPDATE EMP SET SAL='2460' WHERE ENAME='SCOTT';
25、UPDATE EMP SET SAL=SAL*(1.0+0.1),COMM=SAL*(0.1) WHERE DEPTNO='20';
26、SELECT DEPTNO,SAL,MAX(SAL),MIN(SAL) FROM EMP WHERE DEPTNO='10';
27、SELECT AVG(SAL),SUM(SAL) FROM EMP WHERE DEPTNO='20';
28、select count(ENAME) FROM EMP WHERE DEPTNO='30';
29、SELECT COUNT(DISTINCT DEPTNO) FROM EMP;
30、SELECT DEPTNO,JOB,AVG(SAL),MAX(SAL) FROM EMP group by deptno,JOB
31、SELECT DEPTNO,JOB,AVG(SAL),MAX(SAL) FROM EMP group by deptno,JOB having avg (sal)
32、DELETE * FROM EMP WHERE ENAME='SMITH';
删除表中重复记录
delete from emp
where empno in (select empno from emp group by empno having count(empno) > 1)
and rowid not in (select min(rowid) from emp group by empno having count(empno)>1);
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/105894/showart_2167276.html |
免费注册
发表于 2010-02-02 13:14
