MySQL数据库的那点小总结
发布时间：2021-01-14 16:57:46 所属栏目：MySql教程 来源：网络整理
导读：今天PHP站长网 52php.cn把收集自互联网的代码分享给大家，仅供参考。 CREATE DATABASE ?ku1; CREATE TABLE person( ? ?id INT PRIMARY KEY, ? ?username VARCHAR(20) ? ?);? ? ? CREATE TABLE car ( ? ?id INT PRIMARY KE
|
以下代码由PHP站长网 52php.cn收集自互联网 现在PHP站长网小编把它分享给大家，仅供参考 CREATE DATABASE ?ku1;CREATE TABLE person( ? ?id INT PRIMARY KEY, ? ?username VARCHAR(20) ? ?);? ? ? CREATE TABLE car ( ? ?id INT PRIMARY KEY, ? ?carname VARCHAR(20), ? ?pid INT, ? ?FOREIGN KEY(pid) REFERENCES person (id) ?? ) ?? INSERT INTO car VALUES(1,'baoma',NULL) INSERT INTO person VALUES(1,'zhangsan'); INSERT INTO car VALUES(2,'qq',1); DELETE FROM person /* 给张三买汽车 */ INSERT INTO person VALUES(1,'zhangsan'); INSERT INTO car VALUES (2,'hanma',1); INSERT INTO car VALUES (3,'luhu',1); /*李四买汽车*/ INSERT INTO person VALUES(2,'lisi'); INSERT INTO car ?VALUES(4,'xiali',2); INSERT INTO car ?VALUES(5,2)? /*莉莉没车*/ INSERT INTO person VALUES(3,'lili'); /*某人有某车*/ SELECT person.username,car.carname FROM person,car;? SELECT p.username,c.carname FROM person p,car c WHERE p.id = c.pid;? SELECT p.username,c.carname FROM ?person p INNER JOIN car c ON p.id = c.pid; SELECT p.username,c.carname FROM ?person p RIGHT JOIN car c ON p.id = c.pid; ? ? ? /*谁没有车*/? SELECT p.username,c.carname FROM ?person p LEFT JOIN car c ON p.id = c.pid WHERE c.carname IS NULL;? ? /*某人有悍马车*/? SELECT * FROM person WHERE id = 1 ? SELECT pid FROM car WHERE carname='hanma' ? SELECT * FROM person WHERE id = ( SELECT pid FROM car WHERE carname='hanma' ); ?/******/ ? ?CREATE TABLE idcard ( ? ?id INT PRIMARY KEY, ? ?gov VARCHAR(30), ? ?FOREIGN KEY (id) REFERENCES person (id) ?) ? INSERT INTO idcard VALUES(1,'北京发证'); INSERT INTO idcard VALUES(2,'上海发证'); /*某人在某地发证*/ SELECT username,gov FROM ?person INNER JOIN ?idcard ON person.id = idcard.id; SELECT username,gov FROM person LEFT JOIN idcard ON person.id = idcard.id; SELECT username,gov FROM person LEFT JOIN idcard ON person.id = idcard.id WHERE idcard.gov IS NULL;? SELECT username,gov FROM person RIGHT JOIN idcard ON person.id = idcard.id /* ?*/? CREATE TABLE ?student ( ? id INT PRIMARY KEY, ? username VARCHAR(20)? )? CREATE TABLE course( ? ?id INT PRIMARY KEY, ? ?cname VARCHAR(20) ) /*联合主键*/ CREATE TABLE sc( ? ? sid INT, ? ? cid INT, ? ? PRIMARY KEY(sid,cid), ? ? FOREIGN KEY(sid) REFERENCES student (id), ? ? FOREIGN KEY(cid) REFERENCES course (id) ) INSERT INTO student VALUES (1,'zhangsan'); INSERT INTO student VALUES (2,'lisi'); INSERT INTO course VALUES(1,'java'); INSERT INTO course VALUES(2,'php'); INSERT INTO course VALUES(3,'ios'); INSERT INTO sc VALUES (1,1); INSERT INTO sc VALUES (1,2); INSERT INTO sc VALUES (2,3); /*某人选择了某课*/ SELECT username,cname FROM student INNER JOIN sc ON student.id = sc.sid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?INNER JOIN course ON sc.cid = course.id SELECT username,cname FROM student LEFT JOIN sc ON student.id = sc.sid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?INNER JOIN course ON sc.cid = course.id SELECT username,cname FROM student LEFT JOIN sc ON student.id = sc.sid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?LEFT JOIN course ON sc.cid = course.id SELECT username,cname FROM student RIGHT JOIN sc ON student.id = sc.sid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?RIGHT JOIN course ON sc.cid = course.id ? 以上内容由PHP站长网【52php.cn】收集整理供大家参考研究 如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
