INSERT [INTO] table_name
      [(column [, column] ...)]
      VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

INSERT ... ON DUPLICATE KEY UPDATE 
    column = value [, column = value] ... 

SELECT 
 [DISTINCT]//去重 {* | {column [, column] ...} [FROM table_name] //從某個(gè)表里去提取
 [WHERE ...] //篩選條件
 [ORDER BY column [ASC | DESC], ...] //排序
 LIMIT ...    //限定篩選出來的結(jié)果條數(shù)

 CREATE TABLE exam_result ( 
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(20) NOT NULL COMMENT '同學(xué)姓名', 
 chinese float DEFAULT 0.0 COMMENT '語文成績', 
 math float DEFAULT 0.0 COMMENT '數(shù)學(xué)成績', 
 english float DEFAULT 0.0 COMMENT '英語成績' 
); 

INSERT INTO exam_result (name, chinese, math, english) VALUES 
 ('唐三藏', 67, 98, 56), 
 ('孫悟空', 87, 78, 77), 
 ('豬悟能', 88, 98, 90), 
 ('曹孟德', 82, 84, 67), 
 ('劉玄德', 55, 85, 45), 
 ('孫權(quán)', 70, 73, 78), 
 ('宋公明', 75, 65, 30); 
Query OK, 7 rows affected (0.00 sec) 
Records: 7 Duplicates: 0 Warnings: 0 

SELECT column [AS] alias_name [...] FROM table_name; 

SELECT name, english FROM exam_result WHERE english < 60;

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90; 

SELECT name, math FROM exam_result 
 WHERE math = 58 
 OR math = 59 
 OR math = 98 
 OR math = 99; 

SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

SELECT name FROM exam_result WHERE name LIKE '孫%';

SELECT name FROM exam_result WHERE name LIKE '孫_';

SELECT name, chinese, english FROM exam_result WHERE chinese > english;

SELECT name, chinese + math + english 總分 FROM exam_result 
 WHERE chinese + math + english < 200; 

SELECT name, chinese FROM exam_result 
 WHERE chinese > 80 AND name NOT LIKE '孫%';

SELECT name, chinese, math, english, chinese + math + english 總分 
FROM exam_result 
WHERE name LIKE '孫_' OR ( 
 chinese + math + english > 200 AND chinese < math AND english > 80 
); 

SELECT ... FROM table_name [WHERE ...] 
 ORDER BY column [ASC|DESC], [...]; //依據(jù)哪一列做排序

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; 

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

UPDATE table_name SET column = expr [, column = expr ...] 
    [WHERE ...] [ORDER BY ...] [LIMIT ...]

update exam_result set math=math+30 order by chinese+english+math asc limit 3;

update exam_result set chinese=chinese*2;

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

delete from exam_result where name='孫悟空'；

delete from  exam_result order by english+math+chinese asc limit 1;

TRUNCATE [TABLE] table_name

INSERT INTO table_name [(column [, column ...])] SELECT ...

select column1, column2, .. from table group by column;

//利用source將該備份文件恢復(fù)到數(shù)據(jù)庫中
DROP database IF EXISTS `scott`;//如果曾經(jīng)有這個(gè)名字是數(shù)據(jù)庫就刪掉
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;//創(chuàng)建這個(gè)數(shù)據(jù)庫
 
USE `scott`;//使用這個(gè)數(shù)據(jù)庫
 
DROP TABLE IF EXISTS `dept`;//如果有這個(gè)名字的部門表叫把他刪掉
CREATE TABLE `dept` (//創(chuàng)建部門表
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部門編號(hào)',
  `dname` varchar(14) DEFAULT NULL COMMENT '部門名稱',
  `loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點(diǎn)'
);
 
 
DROP TABLE IF EXISTS `emp`;//如果有這個(gè)名字的部門表叫把他刪掉
CREATE TABLE `emp` (//創(chuàng)建員工表
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號(hào)',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)',
  `hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',//外鍵
  `comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號(hào)'//外鍵
);
 
 
DROP TABLE IF EXISTS `salgrade`;//如果有這個(gè)名字的部門表叫把他刪掉
CREATE TABLE `salgrade` (//薪資表  可以客觀反應(yīng)這個(gè)員工在公司的重要程度
  `grade` int(11) DEFAULT NULL COMMENT '等級(jí)',
  `losal` int(11) DEFAULT NULL COMMENT '此等級(jí)最低工資',
  `hisal` int(11) DEFAULT NULL COMMENT '此等級(jí)最高工資'
);
 
 //插入部門
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');//核算部門
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, 'OPERATIONS', 'BOSTON');//運(yùn)營部門
 
//插入員工
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
 //插入不同等級(jí)的薪資
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

select deptno,max(sal) 最高,avg(sal) 平均 from EMP group by deptno; 

select deptno,job,avg(sal) 平均,min(sal) 最低 from EMP group by deptno, job; 

select deptno,avg(sal) deptavg from EMP group by deptno;

select deptno,avg(sal) deptavg from EMP group by deptno having deptavg<2000;