1. 表的創(chuàng)建、修改、刪除
2. 數(shù)據(jù)的插入、修改、刪除
3. 常用的sql查詢和使用
0）and、or、in、between...and...、exist、is null、like、四則運(yùn)算符
1）where子句
2）order by子句
3）group by子句
4）having子句
5）全連接、內(nèi)連接、外連接
6）子查詢、集合查詢
7）函數(shù) max()/min()/avg()/sum()/count()/ifnull()
如果您把這些語(yǔ)句一句句去執(zhí)行一遍，您就會(huì)發(fā)現(xiàn)這些語(yǔ)句的用途；心動(dòng)不如行動(dòng)！
如果大家有更好的SQL語(yǔ)句學(xué)習(xí)，不妨共享出來(lái)大家學(xué)習(xí)下！
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表中插入如下數(shù)據(jù)（可以拷貝如下語(yǔ)句直接在客戶端運(yùn)行即可）
方法1：
第1步，在本地磁盤E盤創(chuàng)建文本文件，其中的文本內(nèi)容如題5格式；保存為emp.txt
第2步，在mysql客戶端運(yùn)行命令 load data local e:\emp.txt into table emp；
效率高，載入數(shù)據(jù)易出錯(cuò)
方法2：
insert into temp values('','','','','','','');
一行行插入數(shù)據(jù)，效率比較低
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個(gè)橫線
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';
刪除表中重復(fù)記錄
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);
本文來(lái)自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u3/105894/showart_2167276.html |
免費(fèi)注冊(cè)
發(fā)表于 2010-02-02 13:14
