MySQL数据库的那点小总结

1. <div class="blockcode"><blockquote>CREATE DATABASE  ku1;
   
2. CREATE TABLE person(
   
3.    id INT PRIMARY KEY,
   
4.    username VARCHAR(20)
   
5.    );  
   
6.    
   
7. CREATE TABLE car (
   
8.    id INT PRIMARY KEY,
   
9.    carname VARCHAR(20),
   
10.    pid INT,
    
11.    FOREIGN KEY(pid) REFERENCES person (id)   
    
12. )   
    
13. 
    
14. 
    
15. INSERT INTO car VALUES(1,'baoma',NULL)
    
16. INSERT INTO person VALUES(1,'zhangsan');
    
17. 
    
18. INSERT INTO car VALUES(2,'qq',1);
    
19. 
    
20. DELETE FROM person
    
21. 
    
22. /* 给张三买汽车 */
    
23. INSERT INTO person VALUES(1,'zhangsan');
    
24. INSERT INTO car VALUES (2,'hanma',1);
    
25. INSERT INTO car VALUES (3,'luhu',1);
    
26. 
    
27. /*李四买汽车*/
    
28. INSERT INTO person VALUES(2,'lisi');
    
29. INSERT INTO car  VALUES(4,'xiali',2);
    
30. INSERT INTO car  VALUES(5,'qq',2)  
    
31. 
    
32. /*莉莉没车*/
    
33. INSERT INTO person VALUES(3,'lili');
    
34. 
    
35. /*某人有某车*/
    
36. 
    
37. SELECT person.username,car.carname FROM person,car;  
    
38. 
    
39. SELECT p.username,c.carname FROM person p,car c WHERE p.id = c.pid;  
    
40. 
    
41. SELECT p.username,c.carname FROM  person p INNER JOIN car c ON p.id = c.pid;
    
42. 
    
43. 
    
44. 
    
45. 
    
46. SELECT p.username,c.carname FROM  person p RIGHT JOIN car c ON p.id = c.pid;
    
47. 
    
48.   
    
49.   
    
50.   
    
51. /*谁没有车*/  
    
52. SELECT p.username,c.carname FROM  person p LEFT JOIN car c ON p.id = c.pid WHERE c.carname IS NULL;  
    
53.   
    
54. /*某人有悍马车*/  
    
55. SELECT * FROM person WHERE id = 1   
    
56. 
    
57. SELECT pid FROM car WHERE carname='hanma'   
    
58. 
    
59. SELECT * FROM person WHERE id = ( SELECT pid FROM car WHERE carname='hanma' );
    
60. 
    
61. /******/
    
62.   
    
63. CREATE TABLE idcard (
    
64.    id INT PRIMARY KEY,
    
65.    gov VARCHAR(30),
    
66.    FOREIGN KEY (id) REFERENCES person (id)
    
67. )
    
68.   
    
69. INSERT INTO idcard VALUES(1,'北京发证');
    
70. INSERT INTO idcard VALUES(2,'上海发证');
    
71. 
    
72. /*某人在某地发证*/
    
73. 
    
74. SELECT username , gov FROM  person INNER JOIN  idcard ON person.id = idcard.id;
    
75. 
    
76. SELECT username ,gov FROM person LEFT JOIN idcard ON person.id = idcard.id;
    
77. 
    
78. 
    
79. SELECT username ,gov FROM person LEFT JOIN idcard ON person.id = idcard.id WHERE idcard.gov IS NULL;  
    
80. 
    
81. SELECT username ,gov FROM person RIGHT JOIN idcard ON person.id = idcard.id
    
82. 
    
83. /*  */  
    
84. 
    
85. CREATE TABLE  student (
    
86. 
    
87.   id INT PRIMARY KEY,
    
88.   username VARCHAR(20)  
    
89. 
    
90. )  
    
91. 
    
92. 
    
93. CREATE TABLE course(
    
94. 
    
95.    id INT PRIMARY KEY,
    
96.    cname VARCHAR(20)
    
97. )
    
98. 
    
99. /*联合主键*/
    
100. CREATE TABLE sc(
     
101. 
     
102.     sid INT,
     
103.     cid INT,
     
104.     PRIMARY KEY(sid,cid),
     
105.     FOREIGN KEY(sid) REFERENCES student (id),
     
106.     FOREIGN KEY(cid) REFERENCES course (id)
     
107. )
     
108. 
     
109. 
     
110. 
     
111. INSERT INTO student VALUES (1,'zhangsan');
     
112. INSERT INTO student VALUES (2,'lisi');
     
113. 
     
114. 
     
115. 
     
116. INSERT INTO course VALUES(1,'java');
     
117. INSERT INTO course VALUES(2,'php');
     
118. INSERT INTO course VALUES(3,'ios');
     
119. 
     
120. INSERT INTO sc VALUES (1,1);
     
121. INSERT INTO sc VALUES (1,2);
     
122. 
     
123. INSERT INTO sc VALUES (2,3);
     
124. 
     
125. 
     
126. /*某人选择了某课*/
     
127. 
     
128. SELECT username,cname FROM student INNER JOIN sc ON student.id = sc.sid
     
129.                                    INNER JOIN course ON sc.cid = course.id
     
130. 
     
131. 
     
132. SELECT username,cname FROM student LEFT JOIN sc ON student.id = sc.sid
     
133.                                    INNER JOIN course ON sc.cid = course.id
     
134. 
     
135. SELECT username,cname FROM student LEFT JOIN sc ON student.id = sc.sid
     
136.                                    LEFT JOIN course ON sc.cid = course.id
     
137. 
     
138. SELECT username,cname FROM student RIGHT JOIN sc ON student.id = sc.sid
     
139.                                    RIGHT JOIN course ON sc.cid = course.id

复制代码

来源：http://www.open-open.com/code/view/1448983351264