设为首页 - 加入收藏 华夏网 ()- 云主机,资讯,互联网,人工智能,云计算,大赢家论坛,区块链,VR,站长网!
热搜: 平台 google 2019 用户
当前位置: 主页 > 水果奶奶论坛 > 正文

MySQL数据库的那点小总结

发布时间:2021-01-14 09:18 所属栏目:[水果奶奶论坛] 来源:网络整理
导读:今天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】收集整理供大家参考研究

如果以上内容对您有帮助,欢迎收藏、点赞、推荐、分享。

【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

网友评论
推荐文章