免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 5833 | 回复: 2

32个普通但常用的mysql语句 [复制链接]

论坛徽章:
0
发表于 2010-02-02 13:14 |显示全部楼层
    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

论坛徽章:
0
发表于 2010-05-30 10:25 |显示全部楼层
正在学习,谢谢分享,立马行动。:wink:

论坛徽章:
3
CU大牛徽章
日期:2013-03-13 15:29:07CU大牛徽章
日期:2013-03-13 15:29:49CU大牛徽章
日期:2013-03-13 15:30:19
发表于 2010-06-25 15:59 |显示全部楼层
谢谢,学习了。
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP