untitled

Size: px

Start display at page:

Download "untitled"

抑莲滑
7 years ago
Views:

2 Ted Codd Codd 1.2 Web World Wide Web Internet CERN Web Web 3 HTML Web HTML 3

3 Web HTML <html> <head> HTML </head> <body> <br> <a href=abcd.html></a> </body> </html> HTML Internet Web HTTP Web Internet Web Web /papers/bdgtti/ http /papers/bdgtti/ eegtti.html Web /papers/bdgtti/ eegtti.html Web Web Web IIS Apache Web Web Java Internet Explorer Nescape 1.3 Web Web 4

org/papers/bdgtti/eegtti.html www.eff.org Web /papers/bdgtti/ http www.eff.org /papers/bdgtti/ eegtti.

4 1 Web Internet Web Web Web Web 1.1 Web Internet 1.1 Web HTTP 5

5 Web Web CGI ISAPI Perl ASP PHP JSP HTML 6

6 E.F.Codd Oracle Sybase Inforxmix Ingrex SQL Server MySQL

7 Web Oracle ROWID 2.3 ROWID AAAAqcAABAAADFNAAH 85 AAAAqcAABAAADFNAAI 85 SELECT,, FROM ORDER BY DESC 8

8 (E-R Entity-Ralationship) n m n n 1 n 2.2 n n 9

9 Web m n 2.3 m n 2.3 m n n 1 n n 1 n 10

10 2 E-R 1 n 1 n m n n 11

11 Database 2. Database Management System DBMS 3 (1) Data Definition Language DDL (2) Data Manipulation Language DML (3) (4) (5) 12

12 3 2 5 DOS dir UNIX ls Windows 9x Windows NT DOS (Metadata)

13 Web RDBMS

14 3 SELECT FROM WHERE ' ' 70 SELECT FROM WHERE >= AND < SQL Column

15 Web Varchar(20) Varchar(2) Date Varchar(10) 3.3 Varchar(20) 20 Date 2. row tuple NULL NULL NULL NULL NULL NULL NOT NULL NULL 16

16 3 2. Unique Primary key

17 Web Foreign key 18

18 3 8 1~ (Check) Default

19 Web Index (GB) Internet Internet B B BALANCE n SMITH 2 ALLEN 3 JONES 4 CLARK 5 MARTIN 6 ADAMS 7 CLARK 8 SCOTT 9 JAMES 20

20 3 SCOTT SCOTT 1 SCOTT n 3.2 loga n SCOTT a a ADAMS-CLARK JAMES-JONE MARTIN-SMITH ADAMS CLARK JONE MARTIN SMITH 3.2 CPU Cluster

21 Web KB n (n-1)/n CPU

22 A B B A B A A B A B 1 B 2 B 1 2 B 2 A 1 A 2 23

23 Web 1 A B 1 B A B 1 2 A A B B A A B B A

24 (Atomicicy) Consistency 25

25 Web Isolation Durability A B A B A A B 0 A B A B A B 26

26 4.1 SQL SQL IBM San Jose SQL Boyce Chamberlin 1974 SEQUEL IBM RDBMS SYSTEM SQL SQL SQL Structure Query Language IBM DB2 DB2 80 ANSI American National Standards Institute SQL 1986 SQL SQL-86 SQL ISOInternational Standardization Organization SQL, SQL-89 SQL 1992 SQL-92 International Standard ISO/IEC 9075:1992,Database Language SQL 4.2 SQL SQL SQL C PASCAL 27

27 Web C int getsum() { int all[100]; int i; int sum;......// all sum=0; for (i=0;i<100;i++) { if ((all[i]>=100) (all[i]<=1000)) { sum+=all[i]; } } return sum; } int getsum() { int all[100]; int i; int sum; all sum=0; i=0; while(i<100) { if (all[i]>=100) { if (all[i]<=1000) { sum+=all[i]; } else { 28

28 4 SQL return sum; } } i++; } return sum; } SQL SELECT SUM(value) FROM all WHERE value=>100 and value<=1000 SQL SQL SQL SQL C void addone() { int all[100]; int i; // all i=0; while(i<100) { if (all[i]>=100) { if (all[i]<=1000) { 29

29 Web all[i]++; } else { return } } i++; } } 1 1 SQL UPDATE all SET value=value+1 WHERE value>=100 and value<=1000 SQL SQL PL/SQL TRANSACTION-SQL SQL SQL SQL SQL SQL 4.3 SQL SQL SQL-92 do While if then SQL SQL ISO SQL-96 SQL Oracle PL/SQL Transaction-SQL SQL SQL 30

30 4 SQL 4.4 SQL SQL DML DDL DCL INSERT UPDATE DELETE SELECT CREATE ALTER GRANT REVOKE SQL 4.5 SQL SQL SQL SQL SQL SQL 1. SELECT * FROM dept DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON DEPTNO DNAME LOC SELECT 31

31 Web DEPT SELECT * SELECT SELECT dname, loc FROM dept DNAME LOC ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON SQL SQL SELECT FROM 2. SQL> SELECT dname, loc FROM dept; ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON SQL> SQL 3. SELECT SELECT SQL> SELECT ' ', dname, ' ', loc FROM dept; ' DNAME ' LOC

32 4 SQL ACCOUNTING RESEARCH SALES OPERATIONS NEW YORK DALLAS CHICAGO BOSTON 4. SQL>SELECT dname, loc FROM dept; DNAME LOC ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON TECH BOSTON SQL SQL>SELECT loc FROM dept; LOC NEW YORK DALLAS CHICAGO BOSTON BOSTON DISTINCT SELECT SQL>SELECT DISTINCT loc FROM dept; LOC NEW YORK 33

33 Web DALLAS CHICAGO BOSTON WHERE SQL>SELECT deptno, dname, loc FROM dept WHERE deptno>=30; SALES CHICAGO 40 OPERATIONS BOSTON SQL SQL SQL SQL 2. AND OR AND AND OR OR AND OR AND OR SQL>SELECT * FROM dept WHERE deptno<=10 OR deptno>=30; DEPTNO DNAME LOC SALES CHICAGO 40 OPERATIONS BOSTON 10 ACCOUNTING NEW YORK SQL 34

34 4 SQL SQL SQL SQL ( ) SQL>SELECT * FROM dept WHERE deptno>=20 AND ( deptno<=25 or deptno>=35); DEPTNO DNAME LOC RESEARCH DALLAS 40 OPERATIONS BOSTON 1. >= <= SQL SQL * / > < >= <= <>!= NOT AND OR BETWEEN AND IN 35

35 Web SQL>SELECT empno, ename, sal, deptno FROM EMP; EMPNO ENAME SAL DEPTNO SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER empno ename sal deptno 10 SQL>SELECT ename, sal*1.1 FROM emp; ENAME SAL* SMITH 880 ALLEN 1760 WARD 1375 JONES MARTIN 1375 BLAKE 3135 CLARK 2695 SCOTT 3300 KING 5500 TURNER

36 4 SQL ADAMS 1210 JAMES 1045 FORD 3300 MILLER 1430 WHERE SQL>SELECT ename,sal*1.1,deptno FROM emp WHERE deptno=30; ENAME SAL*1.1 DEPTNO ALLEN WARD MARTIN BLAKE TURNER JAMES SQL>SELECT ename, sal*1.1 FROM emp WHERE SAL*1.1>1400; ENAME SAL* ALLEN 1760 JONES BLAKE 3135 CLARK 2695 SCOTT 3300 KING 5500 TURNER 1650 FORD 3300 MILLER BETWEEN BTWEEN SQL>SELECT ename, sal*1.1 FROM emp WHERE (sal*1.1) BETWEEN 1400 AND 3000; 37

37 Web ENAME SAL* ALLEN 1760 CLARK 2695 TURNER 1650 MILLER 1430 BETWEEN SQL>SELECT ename, sal*1.1 FROM emp WHERE (sal*1.1)>=1400 AND (sal*1.1)<=3000; ENAME SAL* ALLEN 1760 CLARK 2695 TURNER 1650 MILLER IN IN MILLER KING SCOTT SQL>SELECT ename,sal*1.1 FROM emp WHERE ename IN ('MILLER', 'KING', 'SCOTT'); ENAME SAL* SCOTT 3300 KING 5500 MILLER 1430 SQL>SELECT ename, sal*0.9 FROM emp WHERE ename NOT IN ('MILLER', 'KING', 'SCOTT'); ENAME SAL* SMITH 720 ALLEN 1440 WARD 1125 JONES MARTIN

38 4 SQL BLAKE 2565 CLARK 2205 TURNER 1350 ADAMS 990 JAMES 855 FORD 2700 IN NOT IN SQL>SELECT ename, sal*0.9 FROM emp WHERE ename <>'MILLER' AND ename<>'king' and ename<>'scott'; ENAME SAL* SMITH 720 ALLEN 1440 WARD 1125 JONES MARTIN 1125 BLAKE 2565 CLARK 2205 TURNER 1350 ADAMS 990 JAMES 855 FORD 2700 NOT IN IN BETWEEN SQL 1 SQL 'abcd' 'ABCD1234' ' ' ' SQL A'BC 'A''BC' ABC' 'ABC''' 'ABC' 'abc' SQL 39

39 Web 2. <> > < SQL>SELECT ename FROM emp WHERE ename<>'allen' AND ename<>'jones'; ENAME SMITH WARD MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 3. '0', '1', '2', '3', 'A', 'B', 'C' 'Z', 'a', 'b', 'z' ASCII 'BCD'>'ABCD' 'Ab'>'AB' 'ABC'>'AB' 40

40 4 SQL KING SQL>SELECT ename FROM emp Where ename>='king'; ENAME SMITH WARD MARTIN SCOTT KING TURNER MILLER (ai) 1. R SQL SQL>SELECT ename FROM emp WHERE ename LIKE '%R%'; ENAME WARD MARTIN CLARK TURNER FORD MILLER R LIKE LIKE 41

41 Web 2. _ A SQL>SELECT ename FROM emp WHERE ename LIKE '%A%'; A ENAME ALLEN WARD MARTIN BLAKE CLARK ADAMS JAMES _ A SQL>SELECT ename FROM emp WHERE ename LIKE '_A%'; ENAME WARD MARTIN JAMES LIKE NOT A SQL>SELECT ename FROM emp WHERE ename NOT LIKE '_A%'; ENAME SMITH ALLEN 42

42 4 SQL JONES BLAKE CLARK SCOTT KING TURNER ADAMS FORD MILLER _ LIKE 1. SQL>SELECT ename, sal, dname, loc FROM emp, dept WHERE emp.deptno=dept.deptno; ENAME SAL DNAME LOC SMITH 800 RESEARCH DALLAS ALLEN 1600 SALES CHICAGO WARD 1250 SALES CHICAGO JONES 2975 RESEARCH DALLAS MARTIN 1250 SALES CHICAGO BLAKE 2850 SALES CHICAGO CLARK 2450 ACCOUNTING NEW YORK SCOTT 3000 RESEARCH DALLAS KING 5000 ACCOUNTING NEW YORK TURNER 1500 SALES CHICAGO ADAMS 1100 RESEARCH DALLAS JAMES 950 SALES CHICAGO 43

43 Web FORD 3000 RESEARCH DALLAS MILLER 1300 ACCOUNTING NEW YORK 2. deptno SELECT deptno SQL deptno deptno. SQL> SELECT ename, sal, dname, loc,dept.deptno FROM emp, dept WHERE emp.deptno=dept.deptno; ENAME SAL DNAME LOC DEPTNO SMITH 800 RESEARCH DALLAS 20 ALLEN 1600 SALES CHICAGO 30 WARD 1250 SALES CHICAGO 30 JONES 2975 RESEARCH DALLAS 20 MARTIN 1250 SALES CHICAGO 30 BLAKE 2850 SALES CHICAGO 30 CLARK 2450 ACCOUNTING NEW YORK 10 SCOTT 3000 RESEARCH DALLAS 20 KING 5000 ACCOUNTING NEW YORK 10 TURNER 1500 SALES CHICAGO 30 ADAMS 1100 RESEARCH DALLAS 20 JAMES 950 SALES CHICAGO 30 FORD 3000 RESEARCH DALLAS 20 MILLER 1300 ACCOUNTING NEW YORK Department Employee FROM FROM emp e e emp e SQL SQL>SELECT e.ename, e.sal, d.dname, d.loc, d.deptno FROM emp e, dept d WHERE e.deptno=d.deptno; 4. 44

44 4 SQL ID NAME SUPERIORID ID ID ID 0 SQL SQL>SELECT e2.id, e2.name FROM employee e1,employee e2 WHERE e1.superiorid=e2. id AND e1.name=' '; ID NAME SQL SQL ORDER BY 1. SQL>SELECT ename, sal FROM emp e,dept ORDER BY sal; ENAME SAL SMITH 800 JAMES 950 ADAMS 1100 WARD 1250 MARTIN 1250 MILLER

45 Web TURNER 1500 ALLEN 1600 CLARK 2450 BLAKE 2850 JONES 2975 SCOTT 3000 FORD 3000 KING DESC SQL>SELECT ename, sal FROM emp ORDER BY sal DESC; ENAME SAL KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850 CLARK 2450 ALLEN 1600 TURNER 1500 MILLER 1300 WARD 1250 MARTIN 1250 ADAMS 1100 JAMES 950 SMITH SQL SQL>SELECT e.ename,d.dname,e.sal FROM emp e,dept d WHERE e.deptno=d.deptno 46

46 4 SQL ORDER BY d.dname, e.sal; ENAME DNAME SAL MILLER ACCOUNTING 1300 CLARK ACCOUNTING 2450 KING ACCOUNTING 5000 SMITH RESEARCH 800 ADAMS RESEARCH 1100 JONES RESEARCH 2975 SCOTT RESEARCH 3000 FORD RESEARCH 3000 JAMES SALES 950 WARD SALES 1250 MARTIN SALES 1250 TURNER SALES 1500 ALLEN SALES 1600 BLAKE SALES 2850 SQL>SELECT e.ename, d.dname,e.sal FROM emp e, dept d WHERE e.deptno=d.deptno ORDER BY d.dname DESC, e.sal DESC; ENAME DNAME SAL BLAKE SALES 2850 ALLEN SALES 1600 TURNER SALES 1500 WARD SALES 1250 MARTIN SALES 1250 JAMES SALES 950 SCOTT RESEARCH 3000 FORD RESEARCH 3000 JONES RESEARCH 2975 ADAMS RESEARCH 1100 SMITH RESEARCH

47 Web KING ACCOUNTING 5000 CLARK ACCOUNTING 2450 MILLER ACCOUNTING 1300 SQL SQL>SELECT SUM(sal) FROM emp; SUM(SAL) SUM SUM AVG( ) MAX MIN COUNT( ) SQL>SELECT COUNT(*), SUM(sal), MAX(sal), MIN(sal), AVG(sal) FROM emp; SELECT SUM(sal), ename FROM emp SQL SUM ename 1. GROUP BY SQL GROUP BY SQL>SELECT d.dname, COUNT(*), SUM(e.sal), MAX(e.sal), MIN(e.sal), AVG(e.sal) FROM emp e,dept d 48

48 4 SQL WHERE e.deptno=d.deptno GROUP BY d.dname; ACCOUNTING RESEARCH SALES emp NULL SELECT SQL d.dname SUM MAX SELECT GROUP BY d.dname SUM d.dname SUM SELECT GROUP BY SQL SQL>SELECT d.dname, COUNT(*), SUM(e.sal), MAX(e.sal), MIN(e.sal), AVG(e.sal),d.deptno FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname; d.deptno * ERROR 3 : ORA-00979: GROUP BY 2. HAVING HAVING 2000 SQL>SELECT d.dname, COUNT(*), SUM(e.sal), MAX(e.sal), MIN(e.sal), AVG(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno 49

49 Web GROUP BY d.dname HAVING avg(e.sal)>2000; ACCOUNTING RESEARCH HAVING COUNT MAX AVG SQL>SELECT d.dname, COUNT(*), SUM(e.sal), MAX(e.sal), MIN(e.sal), AVG(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname HAVING e.sal>2000; HAVING e.sal>2000 * ERROR 6 : ORA-00979: GROUP BY WHERE SQL>SELECT d.dname, COUNT(*), SUM(e.sal), MAX(e.sal), MIN(e.sal), AVG(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno and e.sal>2000 GROUP BY d.dname HAVING AVG(e.sal)>2900; ACCOUNTING RESEARCH

50 4 SQL UNION SELECT 1. SQL>SELECT * FROM boss; BOSSNO NAME SAL JAMES TOM JOHN CHANG SQL>SELECT ename, sal FROM emp UNION SELECT name, sal FROM boss; ADAMS 1100 ALLEN 1600 BLAKE 2850 CHANG CLARK 2450 FORD 3000 JAMES 950 JAMES JOHN JONES 2975 KING 5000 MARTIN

51 Web MILLER 1300 SCOTT 3000 SMITH 800 TOM TURNER 1500 WARD SELECT SQL>SELECT ename, sal FROM emp UNION SELECT name, sal FROM boss; SELECT SQL SQL>SELECT ename, sal FROM emp UNION SELECT sal, name FROM boss; SELECT ename, sal * ERROR 1 : ORA-01790: 3. UNION casual SQL> SELECT * FROM casual; ENAME SAL SMITH 800 JAMES 950 MARRY

52 4 SQL JERRY 650 SQL>SELECT ename, sal FROM emp UNION SELECT name,sal FROM boss UNION SELECT ename,sal FROM casual; ADAMS 1100 ALLEN 1600 BLAKE 2850 CHANG CLARK 2450 FORD 3000 JAMES 950 JAMES JERRY 650 JOHN JONES 2975 KING 5000 MARRY 750 MARTIN 1250 MILLER 1300 SCOTT 3000 SMITH 800 TOM TURNER 1500 WARD UNION ALL JAMES SMITH JAMES 53

53 Web JAMES SELECT SQL UNION UNION DISTINCT UNION ALL SQL>SELECT ename, sal FROM emp UNION ALL SELECT name,sal FROM boss UNION ALL SELECT ename,sal FROM casual; SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 JAMES TOM JOHN CHANG SMITH 800 JAMES 950 MARRY 750 JERRY

54 4 SQL ALL ALL SELECT SQL 5. UNION SQL>SELECT ename, sal FROM emp UNION ALL SELECT name,sal FROM boss UNION ALL SELECT ename,sal FROM casual ORDER BY sal; ENAME SAL JERRY 650 MARRY 750 SMITH 800 SMITH 800 JAMES 950 JAMES 950 ADAMS 1100 WARD 1250 MARTIN 1250 MILLER 1300 TURNER 1500 ORDER BY 1 SELECT SQL SQL>SELECT ename, sal FROM emp UNION ALL SELECT name,sal FROM boss 55

55 Web UNION ALL SELECT ename,sal FROM casual ORDER BY 2; sal SQL>SELECT ename, sal FROM emp ORDER BY 2 UNION ALL SELECT name,sal FROM boss ORDER BY 2 UNION ALL SELECT ename,sal FROM casual ORDER BY 2; SQL SQL ORDER BY NULL NULL SQL NewDEPT 50 SQL>INSERT INTO dept VALUES(50, 'NewDEPT', NULL); INSERT INSERT NULL 56

56 4 SQL 1. '' 'NULL' SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 50 LOC NULL '' ASCII 32 '' 0 NULL NULL SQL 'NULL' SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT NULL 'NULL' NULL 'NULL' NULL 'NULL' SQL Oracle SQLPLUS NULL SQL Server 7.0 QUERY ANALYZER 'NULL' NULL 57

57 Web NULL Oracle NULL SQLPLUS 2. NULL NULL SQL SQL>SELECT * FROM dept WHERE loc='null'; SQL>SELECT * FROM dept WHERE loc=''; SQL>SELECT * FROM dept WHERE loc=' '; SQL NULL 'NULL' '' SQL IS SQL>SELECT * FROM dept WHERE loc IS NULL; DEPTNO DNAME LOC NewDEPT NULL NOT SQL>SELECT * FROM dept WHERE loc IS NOT NULL; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 58

58 4 SQL INSERT INTO INSERT INTO 1. INSERT INTO TableName ( column1,column2, ) VALUES ( value1, value2, ) TableName column1 column2 value1 value2 column1 column2 SQL>INSERT INTO dept (deptno, dname, loc) VALUES ( 60, 'TECH', 'Beijing'); SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 60 TECH Beijing INSERT INTO column value INSERT INTO column value column value SQL>INSERT INTO dept (deptno, dname, loc) VALUES ('China', '123', 'Tianjin'); 59

59 Web VALUES ('China', '123','Tianjin') * ERROR 3 : ORA-01722: deptno 'China' SQL Oracle SQL> INSERT INTO dept (deptno, dname, loc) VALUES ('70', '123','Tianjin'); '70' Oracle SQL '70' 70 'China' SQL SQL> INSERT INTO dept (deptno, dname, loc) VALUES (70, 123, 'Tianjin'); Oracle SQL 123 '123' SQL SQL 2. INSERT INTO SQL>INSERT INTO dept VALUES ( 60, 'TECH', 'Beijing'); SQL dept deptno dname loc (column1,column2, )SQL INSERT INTO INSERT INTO INSERT INTO 60

60 4 SQL NULL NULL 3. SELECT INSERT INTO SELECT dept1 SQL>SELECT * FROM dept1; DEPTNO DNAME LOC SALES-CHN Shanghai 15 RESERACH-CHN Shanghai 25 TECH-CHN Beijing dept SQL>INSERT INTO dept (deptno, dname, loc) SELECT deptno,dname,loc FROM dept1; dept SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 5 SALES-CHN Shanghai 15 RESERACH-CHN Shanghai 25 TECH-CHN Beijing dept1 dept SELECT WHERE GROUP BY SELECT 61

61 Web dept1 'Shanghai' dept SQL>INSERT INTO dept (deptno, dname, loc) SELECT deptno,dname,loc FROM dept1 WHERE loc='shanghai'; dept SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 5 SALES-CHN Shanghai 15 RESERACH-CHN Shanghai TECH-CHN dept 4. NULL SELECT NULL IS INSERT NULL NULL SQL 'NULL' SQL>INSERT INTO dept VALUES(50, 'NewDEPT', NULL); UPDATE UPDATE TableName SET ColumnName1=value1,ColumnName2=value2 WHERE TableName ColumnName1 ColumnName2 value1 value2 WHERE 62

62 4 SQL SQL SQL>UPDATE dept SET loc='guangzhou' WHERE loc='shanghai'; loc 'Shanghai' SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 5 SALES-CHN Guangzhou 15 RESERACH-CHN Guangzhou WHERE WHERE SET 100 SQL>UPDATE emp SET sal=sal+100; UPDATE NULL WHERE NULL IS SET NULL INSERT NULL SQL>UPDATE dept SET loc=null WHERE loc IS NOT NULL; UPDATE emp sal SQL>UPDATE emp 63

63 Web SET sal=sal*0.9 sal SQL DELETE DELETE FROM TableName WHERE TableName WHERE WHERE WHERE dept1 SQL>DELETE FROM dept1; dept1 dept1 dept1 DROP TABLE WHERE dept1 SQL>DELETE FROM dept1 WHERE loc='shanghai'; 1. SQL>SELECT * FROM dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 5 SALES-CHN Guangzhou 64

64 4 SQL 15 RESERACH-CHN Guangzhou SQL>SELECT * FROM dept1; DEPTNO DNAME LOC SALES-CHN Shanghai 15 RESERACH-CHN Shanghai 25 TECH-CHN Beijing 2. SELECT SQL>SELECT * FROM dept WHERE deptno NOT IN (SELECT deptno FROM dept1 WHERE loc='shanghai' ); WHERE SELECT deptno FROM dept1 WHERE loc 'Shanghai' 3. SQL>SELECT * FROM dept WHERE deptno NOT IN (SELECT deptno FROM dept1 WHERE loc IN (SELECT loc FROM dept ) ); SQL 65

65 Web 4. SQL SQL>SELECT * FROM dept d, dept1 d1 WHERE d.deptno d1.deptno AND d1.loc='shanghai'; SQL>SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM dept1 WHERE loc='shanghai' ); DEPTNO DNAME LOC SALES-CHN Guangzhou 15 RESERACH-CHN Guangzhou SQL>SELECT * FROM dept WHERE deptno NOT IN (SELECT deptno FROM dept1 WHERE loc='shanghai' ); 5. EXISTS IN IN EXISTS SELECT FROM WHERE EXISTS ( SELECT ) EXISTS EXISTS 66

66 4 SQL SQL>SELECT * FROM dept WHERE EXISTS ( SELECT * FROM dept1 WHERE loc='shanghai' ); DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDEPT 5 SALES-CHN Guangzhou 15 RESERACH-CHN Guangzhou EXISTS NOT SQL>SELECT * FROM dept WHERE NOT EXISTS ( SELECT * FROM dept1 WHERE loc='shanghai' ); EXISTS IN SQL>SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM dept1 WHERE loc='shanghai' ); SQL>SELECT * FROM dept WHERE EXISTS (SELECT deptno 67

67 Web FROM dept1 WHERE dept1.loc='shanghai' AND dept.deptno=dept1.deptno ); 6. ALL ANY SQL>SELECT * FROM dept1 WHERE dept1.deptno>any (SELECT deptno FROM dept ); dept deptno dept1 dept1 SQL dept1 deptno dept dept.deptno dept dept.deptno dept1.deptno dept1 ANY(value1,value2 value3 ) value1 value2 value3 >ANY(1,2,3) >MIN(1,2,3)ALL(value1, value2, value3) value1 value2 value3 >ALL(1,2,3) MAX(1,2,3) =ANY IN SQL>SELECT * FROM dept1 WHERE dept1.deptno=any (SELECT deptno FROM dept ); SQL>SELECT * FROM dept1 WHERE dept1.deptno IN (SELECT deptno FROM dept ); DEPTNO DNAME LOC SALES-CHN Shanghai 68

68 4 SQL 15 RESERACH-CHN Shanghai <>ALL NOT IN SOME ANY SQL 1 CREATE TABLE TableName (ColumnName1 type1 [NOT NULL] ColumnName2 type2 [NOT NULL] ) TableName ColumnName1 ColumnName2 type1 type2 NOT NULL NULL NULL INT CHAR VARCHAR SQL>CREATE TABLE mytable (workerid INT NOT NULL, workername VARCHAR(8) NOT NULL, age INT ); mytable ID NULL 8 NULL NULL SQL>ALTER TABLE mytable 69

69 Web ADD sex CHAR(1) NOT NULL; mytable 1 NULL SQL>DROP TABLE mytable; mytable 2. CREATE VIEW ViewName ( column1, column2 ) AS ViewName column1 column2 SELECT column1 column2 SQL>CREATE VIEW emp_dept (ename,dname) AS SELECT e.ename,d.dname FROM emp e, dept d WHERE e.deptno=d.deptno; emp_dept SQL>SELECT * FROM emp_dept; ENAME DNAME SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH 70

70 4 SQL MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER SALES SALES ACCOUNTING RESEARCH ACCOUNTING SALES RESEARCH SALES RESEARCH ACCOUNTING SQL>CREATE VIEW emp_dept_sal (ename,dname,sal) AS SELECT ed.ename, ed.dname, e.sal FROM emp_dept ed, emp e WHERE ed.ename =e.ename; DROP VIEW ViewName ViewName emp_dept SQL>DROP VIEW emp_dept; emp_dept_sal 3. CREATE [UNIQUE] INDEX IndexName ON TableName (column1,column2 ) UNIQUE IndexName TableName Column1 column2 71

71 Web SQL>CREATE INDEX index_ename ON emp (ename); DROP INDEX IndexName IndexName SQL>DROP INDEX index_ename; SQL A GRANT 1, 2, ON TO 1 2 [WITH GRANT OPTION] 1 2 SELECT INSERT UPDATE DELETE ALTER 1 2 WITH GRANT OPTION SQL>GRANT SELECT ON dept TO mike WITH GRANT OPTION; REVOKE REVOKE REVOKE 1, 2 ON FROM 1 2 GRANT SQL>REVOKE SELECT ON dept FROM mike; mike dept 72

73 5 MySQL 5.1 MySQL MySQL UNIX Web FreshMeat MySQL MySQL 1996 TcX MySQL msql msql MySQL MySQL UNIX 1. MySQL Oracle Sybase AIX BSDI Digital UNIX FreeBSD HP-UX Linux MacOS NetBSD OpenBSD OS/2 IRIX SunOS/Solaris Sco Unix Tru64 UNIX Windows 9x Windows NT/2000 MySQL C C++ GNU Automake Autoconf libtool MySQL MySQL 2. MySQL ANSI SQL Web Transaction MySQL 2 3 MySQL CPU MySQL 75

74 Web 3. MySQL MySQL SQL /FLOAT DOUBLE CHAR VARCHAR TEXT BLOB DATE TIME DATETIME TIMESTAMP YEAR SET ENUM SELECT WHERE mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name WHERE income/dependents > AND age > 30; SQL GROUP BY ORDER BY ( COUNT() COUNT(DISTINCT) AVG() STD() SUM() MAX() MIN() ) ANSI SQL ODBC LEFT OUTER JOIN ODBC 2.5, Access MySQL ( MySQL ) CHAR VARCHAR INSERT @"o gbk big5 DELETE INSERT REPLACE UPDATE MySQL SHOW EXPLAIN 4. MySQL MySQL TcX GB 500 MySQL Netease Fre fre .263.net 371.net fre .263.net 200 Oracle MySQL 5. MySQL C C++ Eiffel Java Perl PHP Python TCL 76

75 5 MySQL API MyODBC ODBC MySQL TMySQL Delphi MySQL MySQL MySQL 6. MySQL Internet MySQL MySQL MySQL 1. Transaction MySQL MySQL Web Web Web Oracle Commit/Rollback Commit MySQL 2. Stored ProcedureTrigger MySQL MySQL MySQL MySQL MySQL 3. MySQL SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); DELETE FROM table1 WHERE id IN(SELECT id FROM table2); 1 2 SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL DELETE JOIN 77

76 Web MySQL MySQL MySQL MySQL MySQL Microsoft MySQL 30 UNIX MySQL MySQL MySQL MySQL MySQL Web 1 Windows NT/9x MySQL Web 2 UNIX MySQL MySQL MySQL 3 MySQL 4 MySQL MySQL UNIX MySQL UNIX 5.2 MySQL MySQL Internet MySQL MySQL MySQL mysql--.tar.gz 78

77 5 MySQL mysql-.tar.gz Linux RPM RPM RedHat Package Manager RedHat Linux Mandrake Linux TurboLinux RPM tar.gz MySQL 5 MySQL 1. MySQL (1) MySQL MySQL /usr/local cd /usr/local (2) MySQL GNU gzip GNU tar gzip tar gunzip < mysql--.tar.gz tar xvf - (3) MySQL /usr/local/mysql ln s mysql-- mysql (4) MySQL MySQL MySQL MySQL cd mysql scripts/mysql_install_db (5) MySQL bin/safe_mysqld & MySQL mysql MySQL 2. MySQL MySQL C++ gcc egcs SGI C++ SunPro C++ gcc2.7.x MySQL gcc gcc libg++ Make GNU Make Make MySQL 79

78 Web 1 MySQL cd /tmp gunzip < mysql-.tar.gz tar xv cd mysql- 2 configure MySQL configure configure - - help MySQL -- prefix= MySQL /usr/local/mysql./configure prefix=/usr/local/mysql 3 MySQL make vitual memory exhausted configure with-low-memory inline MySQL 4 MySQL make install MySQL root 5 MySQL /usr/local/mysql/bin/mysql_install_db 6 MySQL /usr/local/mysql/bin/safe_mysqld & MySQL MySQL 5.3 MySQL MySQL MySQL MySQL Oracle SQLPlus UNIX Shell Windows MySQL SQL MySQL MySQL MySQL mysql MySQL mysql 1 UNIX 80

79 5 MySQL Windows DOS mysql mysql options mysql options mysql mysql mysql help -h host _ name mysql -u user_name MySQL UNIX MySQL mysql MySQL Windows ODBC USER set doggie set USER=doggie -p mysql MySQL -pyour_password -p mysql mysql u doggie p Enter password: Enter password: MySQL UNIX Windows -p mysql mysql -h -u -p -p MySQL mysql h mysqldb.doggie.net.cn u doggie p Enter password:( ) Welcome to the MySQL monitor, Comands end with ; or \g. Your MySQL Connection id is 5678 to server version: Type 'help' for help. 81

80 Web mysql> mysql> quit 2. mysql mysql ; mysql \ g go mysql mysql mysql mysql>select NOW(); NOW() :51:11 1 row in set (0.01 sec) mysql mysql mysql> SELECT NOW(), -> USER(), -> VERSION() ->; mysql> -> mysql mysql SELECT NOW() select now() SeLeCt NoW() SQL 82

81 5 MySQL SQL NOW() NOW () \ c mysql ego mysql mysql> SELECT * FROM user -> EGO *************************** 1. row *************************** Host: localhost User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y 1 rows in set (0.01 sec) 3. mysql mysql SQL SQL MySQL SQL MySQL mysqladmin mysqladmin SQL CREATE DATABASE database_name mysql> CREATE DATABASE myfirstdb; Query OK, 1 row affected (0.02 sec) SHOW DATABASES 83

82 Web mysql> SHOW DATABASES; Database myfirstdb mysql test 3 rows in set (0.02 sec) DROP DATABASE mysql> DROP DATABASE myfirstdb; Query OK, 0 rows affected (0.01 sec) mysql MySQL No Database Selected USE database_name 4. MySQL mysql SQL CREATE TABLE tbl_name (column_specs) DROP TABLE tbl_name SHOW TABLES DESCRIBE tbl_name ALTER TABLE ADD/DELETE/CHANGE tbl_name(column_specs) 5. mysql MySQL MySQL MySQL mysql_install_db MySQL MySQL MySQL a.com root b.com root * 84

83 5 MySQL mysql SQL GRANT GRANT priv_type [(column_list)] [, priv_type [(column_list)]...] ON {tbl_name * *.* db_name.*} TO user_name [IDENTIFIED BY 'password'] [, user_name [IDENTIFIED BY 'password']...] [WITH GRANT OPTION] GRANT SELECT,DELETE,INSERT ON mydb.* TO doggie@localhost IDENTIFIED BY doggie, piggy@% IDENTIFIED BY piggy doggie piggy mydb piggy piggy doggie doggie FLUSH PRIVILEGES mysqladmin MySQL MySQL MySQL MySQL mysqladmin mysqladmin [OPTIONS] command command... OPTIONS mysql -h p MySQL mysqladmin create databasename drop databasename extended-status MySQL flush-hosts flush-logs flush-status flush-tables flush-privileges reload kill id,id,... MySQL password new-password new-password ping MySQL processlist MySQL reload refresh shutdown MySQL status 85

84 Web variables version password mysql GRANT mysqladmin shutdown MySQL killall 9 safe_mysqld; killall mysqld MySQL MySQL MySQL MySQL MySQL MySQL SQL mysqlimport mysqldump mysqlimport [OPTIONS] database_name textfile_name mysqldump [OPTIONS] database_name [tables] OPTIONS mysql h p -u -f SQL SQL -l -e mysqldump INSERT SQL mysqldump h piggy.net.cn u piggy p f mydb > sqldump.txt piggy piggy.net.cn mydb sqldump.txt mysqlimport h doggie.net.cn u doggie p f yourdb sqldump.txt doggie doggie.net.cn MySQL sqldump.txt yourdb MySQL MySQL MySQLManager MySQL Win32 MySQL MySQLManager

85 5 MySQL 5.1 MySQLManager Register Server MySQL MySQLManager SQL Query SQL MySQLManager SQL MySQLManager MySQL 87

86 Web 5.4 MySQL MySQL MySQL MySQL MySQL Web phpmyadmin Windows GUI MySQLWinAdmin Xwindows GUI kmysql MySQLWinAdmin David B.Mansel Delphi Windows MySQL MySQLWinAdmin ServerListKey MySQLWinAdmin MySQL 8 15 MySQLWinAdmin 5.4 doggie piggy MySQL doggie doggie piggy acm acm Sessions MySQL MySQL Servers Add Server MySQLWinAdmin 88

87 5 MySQL 5.5 MySQLWinAdmin Interactive SQL Console Run SQL on Database Interactive SQL Console MySQLWinAdmin Interactive SQL Console SQL Command SQL SQL Run SQL MySQLWinAdmin phpmyadmin PHP Web MySQL PHP phpmyadmin PhpMyAdmin 89

88 Web phpmyadmin PHP MySQL Web PHP+MySQL Web phpmyadmin MySQL 1 phpmyadmin phpmyadmin URL KB tar.gz tar zxvf phpmyadmin_2.0.5.tar.gz Web Server config.inc.php3 $cfgservers[1]['host'] = 'localhost'; MySQL MySQL phpmyadmin MySQL 'doggie.net.cn' $cfgservers[1]['user'] = 'root'; MySQL $cfgservers[1]['password'] = ''; MySQL Web Server require("english.inc.php3"); english.inc.php3 chinese_gb.inc.php3 phpmyadmin 2 phpmyadmin phpmyadmin phpmyadmin Web Server Web Server phpmyadmin phpmyadmin phpmyadmin 90

89 5 MySQL MySQLWinAdmin phpmyadmin SQL SQL phpmyadmin 5.10 phpmyadmin SQL phpmyadmin 91

90 Web kmysql UNIX X Window MySQL MySQL msql PostgresSQL kmysql KDE kmysql RPM.tar.gz RPM rpm-uvh kmysql-x.x.x-i386.rpm tar zxfv kmysql-x.x.x.tar.gz cd kmysql-x.x.x./configure make make install kmysql MySQL kmp_mysql RPM tar.gz kmysql Kmysql kmysql Server->Add Server

91 5 MySQL kmysql PHP Web 5.5 MySQL MySQL 0x 09af 0x0a 10 0xffff x0X 0x0a 0x0A 0X0a 0X0A MySQL ee E e E12 e - MySQL E MySQL M D 0 30 TINYINT BIGINT SMALLINT FLOAT MEDIUMINT DOUBLE INT DECIMAL 93

92 Web TINYINT[(M)] SMALLINT[(M)] MEDIUMINT[(M)] INT[(M)] BIGINT[ (M) ] FLOAT[(M, D) ] E 38 FLOAT(4) E + 38 DOUBLE[(M,D)] E 308 FLOAT(8) E DECIMAL(M,D) M D TINYINT[(M)] 1 SMALLINT[(M)] 2 MEDIUMINT[(M)] 3 INT[(M)] 4 BIGINT[(M)] 8 FLOAT[(M,D)], FLOAT(4) 4 DOUBLE[(M,D)], FLOAT(8) 8 DECIMAL(M,D) M MySQL<3.23 M+2 MySQL >= 3.23 ZEROFILL ZEROFILL CREATE TABLE mytable (my_zerofill INT(5) ZEROFILL); INSERT INTO mytable VALUES(1,100,10000, ); SELECT my_zerofill FROM mytable SELECT 94

93 5 MySQL my_zerofill AUTO_INCREMENT AUTO_INCREMENT AUTO_INCREMENT 1 1 NULL AUTO_INCREMENT MySQL 1 AUTO_INCREMENT AUTO_INCREMENT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT CREATE TABLE ai (i INT AUTO_INCREMENT NOT NULL PRIMARY KEY) CREATE TABLE ai (i INT AUTO_INCREMENT NOT NULL PRIMARY KEY(i)) CREATE TABLE ai (i INT AUTO_INCREMENT NOT NULL UNIQUE(i)) UNSIGNED UNSIGNED CREATE TABLE mytable ( itiny TINYINT, itiny_u TINYINT UNSIGNED ) itiny itiny_u TINYINT 256 itiny itiny_u UNSIGNED UNSIGNED UNSIGNED NULL NOTNULL NULL NOT NULL NULL DEFAULT MySQL NULL NULL NULL 0 95

94 Web INT -1 1 NULL CREATE TABLE t ( i1 INT DEFAULT -1, i2 INT DEFAULT 1, i3 INT DEFAULT NULL ) gzip MySQL 4GB / 24MB 5.4 MySQL 5.5 L CHAR VARCHAR TINYBLOB BLOB MEDIUMBLOB LONGBLOB TINYTEXT TEXT MEDIUMTEXT LONGTEXT ENUM SET BLOB BLOB BLOB BLOB 96

95 5 MySQL CHAR(M) M M VARCHAR(M) M L+1 TINYBLOB, TINYTEXT L+1 BLOB, TEXT L+2 MEDIUMBLOB, MEDIUMTEXT L+3 LONGBLOB, LONGTEXT L+4 ENUM ( value1, value2,...) SET ( value1, value2,...) CHAR VARCHAR BINARY NULL NOT NULL NULL NOT NULL NOT NULL BLOB TEXT DEFAULT MySQL NULL NULL NULL ENUM ENUM SET NULL MySQL MySQL DATE DATETIME TIME TIMESTAMP YEAR YEAR MySQL 3.22 MySQL DATE TIME DATETIME TIMESTAMP YEAR YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss YYYYMMDDhhmmss YYYY 97

96 Web DATE TIME -838:59:59838:59:59 DATETIME :00: :59:59 TIMESTAMP[(M)] YEAR[(M)] DATE TIME DATETIME TIMESTAMP YEAR 3 MySQL MySQL ANSI MySQL 10 12/3/993/12/99 MySQL MySQL 1 DATE TIME DATETIME DATE TIME DATETIME YYYY-MM-DDhh:mm:ssYYYY-MM-DD hh:mm:ss DATETIME DATE DATETIME MySQL 00:00:00 MySQL DATETIME TIME DATETIME TIME TIME 12:30 TIME 00:12: :30:00 2 TIMESTAMP TIMESTAMP YYYYMMDDhhmmss UNIX UNIX TIMESTAMP UNIX

97 5 MySQL TIMESTAMP MySQL TIMESTAMP TIMESTAMP NULL TIMESTAMP MySQL TIMESTAMP MySQL MySQL 3 YEAR YEAR YEAR YEAR YEAR 4 2 YEAR 4 TINYINT YEAR YEAR SMALLINT YEAR MySQL MySQL MySQL MySQL MySQL MySQL MySQL mysqld MySQL MySQL mysqld MySQL my_db DATADIR/ m y _ d b SQL CREATE DATABASE mkdir DATADIR/db_name chmod 700 DATADIR/db_name MySQL DROP DATABASE rm -rf DATADIR/db_name 99

98 Web SHOW DATABASE ProFTPD FTP /usr/local/var/proftpd MySQL /usr/local/var proftpd 5.9 ISAM MyISAM.frm.isd(ISAM).myd(MyISAM).ism(ISAM).myi(MyISAM) CREATE TABLE tbl_name mysqld tbl_name.frm MySQL ALTER TABLE ALTER TABLE CREATE DROP INDEX ALTER TABLE DROP TABLE my_db DROP TABLE my_tbl rm DATADIR/my_db/my_tbl.* MySQL 5.10 HOSTNAME 100

99 5 MySQL ID HOSTNAME.pid ID HOSTNAME.err HOSTNAME.log / HOSTNAME.nnn MySQL --log --log-update ID PID MySQL ID (PID) PID PID MySQL ID mysqladmin shutdown MySQL mysqladmin PID safe_mysqld mysqld safe_mysqld mysqld mysqld safe_mysqld mysqld safe_mysqld mysqld --log --log-update SQL SQL mysql MySQL SQL mysql 5.7 MySQL MySQL MySQL MySQL mysql_install_db MySQL GRANT REVOKE 101

100 Web 1 GRANT GRANT GRANT privileges (columns) ON what TO user IDENTIFIED BY 'password' WITH GRANT OPTION privileges 5.11 GRANT ALTER CREATE DELETE DROP INDEX INSERT REFERENCES SELECT UPDATE FILE PROCESS RELOAD SHUTDOWN ALL USAGE ALL PRIVILEGES 5.11 SHUTDOWN ALL USAGE columns what COL UMNS 102

101 5 MySQL user MySQL MySQL MySQL UNIX Windows UNIX MySQL root MySQL password IDENTIFIED BY IDENTIFIED BY GRANT PASSWORD() WITH GRANT OPTION GRANT GRANT (1) MySQL GRANT ALL ON mydb.* ON IDENTIFIED BY 'pig' mydb.* mydb GRANT ALL ON mydb.* TO IDENTIFIED BY 'dog' % SQL LIKE % doggie doggie@% cat kitty.net.cn %.kitty.net.cn GRANT ALL ON mydb.* TO cat@%.kitty.net.cn IDENTIFIED BY 'catty' IP 103

102 Web DNS DNS MySQL DNS hijacking DNS IP IP MySQL IP MySQL 3.23 IP GRANT ALL ON mydb.* TO IDENTIFIED BY 'pig' GRANT ALL ON mydb.* TO IDENTIFIED BY 'dog' GRANT ALL ON mydb.* TO IDENTIFIED BY 'catty' C IP / IP C (2) superman SQL GRANT ALL ON *.* TO superman@localhost IDENTIFIED BY 'super! ' WITH GRANT OPTION ON *.* superman FILE PROCESS RELOAD SHUTDOWN ON *.* flush FLUSH GRANT RELOAD ON *.* TO flush@localhost IDENTIFIED BY 'flushme' ON db_name.* GRANT ALL ON mydb.* TO doggie@localhost IDENTIFIED BY 'dog' GRANT SELECT ON mydb.* TO ro_user@localhost IDENTIFIED BY 'readonly' 104

103 5 MySQL mydb doggie ro_user menagerie SELECT GRANT SELECT INSERT DELETE UPDATE ON mydb.* TO IDENTIFIED by 'pig' GRANT SELECT ON mydb.tablea TO IDENTIFIED BY'catty' GRANT UPDATE (columna) ON mydb.tablea TO cat tablea UPD ATE columna cat tablea colunmb colunmc colunmd UPDATE GRANT UPDATE(columnb,columnc,columnd) ON mydb.tablea TO 2 REVOKE REVOKE FROM TO IDENTIFIED BY WITH GRANT OPTION REVOKE GRANT REVOKE privileges (columns) ON what FROM user user GRANT user privileges GRANT REVOKE REVOKE user DELETE user MySQL mysql MySQL MySQL 5 user db host tables_priv columns_priv 105

104 Web user db host Host Host Host User Db Db Password User Alter_priv Alter_priv Alter_priv Create_priv Create_priv Create_priv Delete_priv Delete_priv Delete_priv Drop_priv Drop_priv Drop_priv Index_priv Index_priv Index_priv Insert_priv Insert_priv Insert_priv References_priv References_priv References_priv Select_priv Select_priv Select_priv Update_priv Update_priv Update_priv Grant_priv Grant_priv Grant_priv File_priv Process_priv Reload_priv Shutdown_priv table_priv columns_priv Host Db User Table_name Host Db User Table_name Column_name Table_priv Column_priv user user user DELETE 106

105 5 MySQL user root db db host host db GRANT REVOKE tables_priv tables_priv columns_priv columns_priv MySQL GRANT REVOKE GRANT INSERT INSERT MySQL GRANT GRANT GRANT user User Host Password GRANT INSERT PASSWORD() INSERT GRANT db User Host Db tables_priv columns_priv GRANT GRANT ALL ON *.* TO IDENTIFIED BY 'super! ' WITH GRANT OPTION INSERT INSERT INTO mysql.user VALUES ( localhost, superman, PASSWORD( super! ), Y, Y, Y, Y, Y, Y, Y, Y, Y, Y, Y, Y, Y, Y ) INSERT MySQL MySQL user 14 SHOW COLUMNS INSERT GRANT flush RELOAD GRANT RELOAD ON *.* TO flush@localhost IDENTIFIED BY 'flushme' INSERT 107

106 Web N INSERT INTO user (Host,User,Password,Reload_priv) VALUES( localhost, flush,password( flushme ), Y ) ON db_name.* ON *.* GRANT ALL ON mydb.* TO cat@localhost IDENTIFIED BY 'catty' user user db INSERT INTO user (Host,User,Password) VALUES( localhost, cat,password( catty )); INSERT INTO db VALUES( localhost, mydb.*, cat, Y, Y, Y, Y, Y, Y, N, Y, Y, Y ) N GRANT WITH GRANT OPTION GRANT Y tables_priv columns_priv INSERT tables_priv.table_priv columns_priv.column_priv SELECT INSERT tables_priv Table_priv Select, Insert MySQL UPDATE DELETE REVOKE MySQL user MySQL / 1 Host IP localhost localhost piggy.net.cn user Host localhost piggy.net.cn localhost localhost piggy.net.cn ( -h 108

107 5 MySQL ) user Host SQL %_ LIKE IP %.doggie.net.cn doggie.net.cn % B % C % Host % db Host host User User %% User Password Password GRANT mysqladmin password INSERT REPLACE UPDATE SET PASSWORD PASSWORD( NEW_PASSWORD ) new_ password Db columns_priv tables_priv Db db host Db SQL %_ % Table_name Column_name 5.14 Table_name UNIX Windows Host No Db Yes User Yes Table_name Yes Password Yes Column_name No 2. user db table_priv columns_priv (1) user 109

108 Web (2) db (3) tables_priv columns_priv (4) user OR db OR tables_priv OR columns_priv host host GRANT REVOKE host host host user OR (dn AND host) OR tables_priv OR columns_priv db Host host host host db Db host Host db host AND db host MySQL MySQL FLUSH PRIVILEGES mysqladmin flush-privileges MySQL 3 MySQL MySQL MySQL MySQL user user Host User Host Host User piggy.net.cn piggy.net.cn %.net.cn Host MySQL piggy.net.cn %.net.cn %.cn%.cn % IP % % 10.% % Host 110

109 6 MySQL 6.1 MySQL C MySQL C MySQL (API) C API API C MySQL 1. C MySQL C gcc MySQL MySQL MySQL MySQL MySQL RPM MySQL-devel- RPM /usr/local/include /usr/local/lib RPM /usr/include/mysql /usr/lib/mysql 2. C MySQL MySQL MySQL /usr/local/include/mysql /usr/local/lib/mysql MySQL -I/usr/local/include/mysql gcc c -I/usr/local/include/mysql myclient.c 111

110 Web -L/usr/local/lib/mysql -lmysqlclient gcc o myclient myclient.o -L/usr/local/lib/mysql -lmysqlclient floor() -lm gcc o myclient myclient.o -L/usr/local/lib/mysql lmysqlclient -lm Solaris -lsocket -lnsl MySQL make make myclient main.c aux.c myclient.h Makefile # Makefile for myclient cc = gcc INCLUDES = -I/usr/local/include/mysql LIBS = -L/usr/local/lib/mysql -lmysqlclient all: myclient main.o: main.c myclient.h $(CC) -c $(INCLUDES) main.c aux.o: aux.c myclient.h $(CC) -c $(INCLUDES) aux.c myclient: main.o aux.o $(CC) -o myclient main.o aux.o $(LIBS) clean: rm -f myclient main.o aux.o LIBS -lm LIBS = -L/usr/local/lib/mysql lmysqlclient -lm -lsocket -lnsl LIBS Makefile make gcc GNU make 3. MySQL C API C MySQL MySQL 112

111 6 MySQL /* myclient1.c */ #include <stdio.h> #include <mysql.h> #define def_host_name NULL // localhost #define def_user_name "root" // #define def_password NULL // #define def_db_name NULL //, MYSQL *conn; // MYSQL int main (int argc, char *argv[]) { conn = mysql_init(null); if (conn == NULL) { fprintf( stderr,"mysql_init() failed\n"); exit (1); } if (mysql_real_connect( conn, // def_host_name, // def_user_name, // def_password, // def_db_name, // 0, //, NULL, // Unix, 0) // == NULL) { 113

112 Web fprintf( stderr, "mysql_real_connect() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); exit (1); } mysql_close(conn); exit(0); } Unix gcc o myclient1 I /usr/local/include/mysql L /usr/local/lib/mysql lmysqlclient myclient1.c myclient1./myclient1 mysql_real_connect() failed: Error 1045(Access denied for user: 'root@localhost' (Using password :YES)) root myclient2.c 4. MySQL C API SQL SELECT INSERT mysql_real_query() mysql_real_query() mysql_real_query C NULL mysql_query( ) NULL mysql_query mysql_query() NULL \0 \0 114

113 6 MySQL mysql_query ( ) C strcpy ( ) sprintf ( ) mysql mysql_escape_string() '' \ '' C if (mysql_query(conn, query)!=0) { //, } else { // } mysql_query() mysql_real_query() SELECT mysql_query() INSERT DELETE UPDATE SELECT SHOW MySQL MYSQL_RES NULL (1) mysql_query() mysql_real_query() mysql_affected_rows() 115

114 Web if (mysql_query(conn,"insert INTO mytbl SET name = 'My Name'")!=0) { fprintf (stderr,"insert failed\n"); } else { printf("insert ok: %lu rows affected\n", (unsigned long)mysql_affected_rows(conn)); } mysql_rows_affected() INSERT DELETE UPDATE MySQL MySQL UPDATE WHERE UPDATE CLIENT_FOUND_ROWS flags mysql_real_connect ( ) (2) mysql_query() mysql_real_query() MySQL SELECT SHOW DESCRIBE EXPLAIN mysql_store_result() mysql_use_result() MYSQL_RES NULL mysql_store_result() mysql_use_result() mysql_store_result ( ) mysql_fetch_rows( ) MYSQL_ROW mysql_fetch_rows() NULL mysql_free_result() MYSQL_RES *res_set; 116

115 6 MySQL if (mysql_query(conn,"show TABLES FROM mysql")!=0) fprintf(stderr,"mysql_query() failed\n"); else { res_set = mysql_store_result(conn); if (res_set == NULL) fprintf(stderr,"mysql_store_result() failed"); else { MYSQL_ROW row; while ((row = mysql_fetch_row(res_set))!=null) { printf("%s\n",row[0]); } } } mysql_free_result(res_set); mysql_fetch_row() MYSQL_ROW row[i] i 0 1 MYSQL_ROW MYSQL_ROW MYSQL_ROW row MYSQL_ROW *row MYSQL_ROW NULL 0 NULL MYSQL_ROW NULL NULL NOT NULL NULL mysql_store_result() mysql_use_result() mysql_store_result() mysql_use_result() 117

116 Web mysql_store_result() mysql_fetch_row() mysql_fetch_row() NULL mysql_use_result() mysql_fetch_row() mysql_fetch_row ( ) NULL mysql_errno() mysql_error() mysql_use_result() mysql_store_result() mysql_use_result ( ) mysql_use_result() mysql_use_result() mysql_store_result() mysql_data_seek() mysql_row_seek ( ) mysql_row_tell() mysql_use_result() mysql_fetch_row() mysql_store_result( ) mysql_store_result ( ) mysql_store_result() mysql_use_result() mysql_num_rows() MYSQL_FIELD max_width mysql_use_result ( ) mysql_num_rows() max_width mysql_use_result() mysql_store_result() mysql_use_result ( ) mysql_store_result() mysql_fetch_row ( ) mysql_store_result() (3) mysql_num_rows() mysql_num_fields() mysql_fetch_lengths() MYSQL_FIELD mysql_fetch_fields() 118

117 6 MySQL (4) MySQL MySQL C API SQL myclient1.c SQL MySQL test mytable CREATE TABLE mytable ( numcol int, charcol varchar(255), timecol DATETIME ) numcol 100 charcol This is a string contains ''&' timecol NOW() charcol mysql_escape_string() '' ' numcol 1 charcol timecol mytable myclient2.c /* myclient2.c */ #include <stdio.h> #include <string.h> #include <mysql.h> #define def_host_name NULL // localhost #define def_user_name "root" // #define def_password NULL // #define def_db_name "test" //, #define special_string "This is a string contains \" & \'" MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; // MYSQL // MYSQL // MYSQL 119

118 Web static char cmd[500]; static char temp[500]; int main (int argc, char *argv[]) { printf("init MySQL client libs.\n"); conn = mysql_init(null); if (conn == NULL) { fprintf( stderr,"mysql_init() failed\n"); exit (1); } printf("connecting to MySQL server.\n"); if (mysql_real_connect( conn, // def_host_name, // def_user_name, // def_password, // def_db_name, // 0, //, NULL, // Unix, 0) // == NULL) { fprintf( stderr, "mysql_real_connect() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); exit (1); } printf("creating table\n"); if (mysql_query(conn,"create TABLE mytable (numcol int,charcol varchar(255),timecol DATETIME)")!=0) { fprintf( stderr, "mysql_query() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); 120

119 6 MySQL exit (1); } printf("inserting data\n"); printf("original data is %s\n",special_string); mysql_escape_string(temp,special_string,strlen(special_string)); printf("escaped to %s\n",temp); sprintf(cmd,"insert INTO mytable VALUES(100,'%s',NOW())",temp); if (mysql_query(conn,cmd)!=0) { fprintf( stderr, "mysql_query() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); exit (1); } printf("selecting data\n"); if (mysql_query(conn,"select * FROM mytable")!=0) { fprintf( stderr, "mysql_query() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); exit (1); } printf("storing result\n"); if (( res =mysql_store_result(conn)) == NULL) { fprintf( stderr, "mysql_store_result() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); exit (1); } printf("processing data\n"); while ((row = mysql_fetch_row(res))!= NULL) { int i; i = atoi(row[0]); i++; printf("%d %s %s\n",i,row[1],row[2]); 121

120 Web } printf("dropping table\n"); if (mysql_query(conn,"drop TABLE mytable")!=0) { fprintf( stderr, "mysql_query() failed:\n error %u (%s)\n", mysql_errno(conn), mysql_error(conn)); exit (1); } printf("freeing resources\n"); mysql_free_result(res); mysql_close(conn); exit(0); } gcc -o myclient2 I /usr/local/include/mysql -L /usr/local/lib/mysql/ myclient2.c lmysqlclient bash#./myclient2 Init MySQL client libs. Connecting to MySQL server. Creating table Inserting data Original data is This is a string contains " & ' Escaped to This is a string contains \" & \' Selecting data Storing result Processing data 101 This is a string contains " & ' :12:36 Dropping table Freeing resources MySQL C API Web fastcgi Web C PHP 122

121 6 MySQL mysql_escape_string Web '' ' CGI MySQL C API MySQL conn MYSQL result MYSQL_RES field MYSQL_FIELD row MYSQL_ROW 1. my_bool mysql_change_user(mysql *mysql, const char *user, const char *password, const char *db) conn db_name NULL mysql_change_user() void mysql_close(mysql *mysql) conn mysql_init() mysql_close() mysql_close ( ) MYSQL *mysql_init(mysql *mysql) conn MYSQL mysql_init() conn NULL mysql_init() 123

122 Web int mysql_options(mysql *mysql, enum mysql_option option, const char *arg) mysql_real_connect() mysql_init() mysql_real_connect() mysql_options option arg arg arg NULL option MYSQL_INIT_COMMAND arg NULL mysql_ping( ) MYSQL_OPT_COMPRESS / a rg NULL mysql_real_connect() MYSQL_OPT_CONNECT_TIMEOUT arg unsigned int MYSQL_OPT_NAMED_PIPE arg NULL Windows 95/98/NT Windows NT MySQL MYSQL_READ_DEFAULT_FILE [client] MYSQL_READ_DEFAULT_GROUP MYSQL_READ_DEFAULT_FILE [client] mysql_options() option int mysql_ping(mysql *mysql) conn mysql_ping() mysql_real_connect() mysql_ping ( ) MYSQL *mysql_real_connect(mysql *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char*unix_socket, unsigned int client_flag) conn mysql_init() NULL conn mysql_errno() mysql_error() flags flag CLIENT_FOUND_ROWS CLIENT_NO_SCHEMA CLIENT_COMPRESS CLIENT_ODBC UPDATE db_name.tbl_name.col_name ODBC 124

123 6 MySQL 2. unsigned int mysql_errno(mysql *mysql) 0 MySQL errmsg.h mysqld_error.h char *mysql_error(mysql *mysql) 3. unsigned int mysql_escape_string(char *to, const char *from, unsigned int length) SQL from_str from_len mysql_escape_string() to_str to_str from_len*2 +1 from_str NULL int mysql_query(mysql *mysql, const char *query) mysql_query() NULL mysql_query() mysql_real_query() mysql_query ( ) mysql_query mysql_real_query SQL \ g mysql_query() int mysql_real_query(mysql *mysql, const char *query, unsigned int length) mysql_real_query mysql_query int mysql_select_db(mysql *mysql, const char *db) db mysql_select_db() mysql_select_db() 4. mysql_store_result() mysql_use_result()

124 Web mysql_store_result() mysql_fetch_row() NULL mysql_num_rows ( ) mysql_store_result ( ) mysql_affected_rows() mysql_num_ rows() mysql_data_seek()mysql_row_seek() mysql_ row_tell() mysql_fetch_row( ), MYSQL_FIELD max_length mysql_use_result() mysql_fetch_row() mysql_fetch_row() NULL mysql_num_rows() mysql_affected_rows() mysql_data_seek( ) mysql_row_seek( ) mysql_row_tell() mysql_fetch_row() max_length my_ulonglong mysql_affected_rows(mysql *mysql) DELETE INSERT REPLACE UPDATE DELETE INSERT REPLACE UPDATE -1 void mysql_data_seek(mysql_res *result, unsigned long long offset) offset 0 mysql_num_rows(result) - 1 offset mysql_data_seek() mysql_store _result() MYSQL_FIELD *mysql_fetch_fields(mysql_res *result) mysql_fetch_field() NULL mysql_field_tell() mysql_field_seek() mysql_fetch_field() MYSQL_FIELD *mysql_fetch_field_direct(mysql_res *result, unsigned int fieldnr) field_num 0 mysql_num_fields() - 1 field_num mysql_fetch_field_direct() MySQL

展开

untitled

untitled Database System Principle Database System Principle 1 SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 2 3.1 SQL SQL Structured Query Language SQL Database System Principle 3 SQL 3.1.1 SQL 3.1.2