MySQL数据库的那点小总结

发布时间：2020-05-23 07:17:40 所属栏目：MySql 来源：互联网

导读：MySQL数据库的那点小总结

下面是脚本之家 jb51.cc 通过网络收集整理的代码片段。

脚本之家小编现在分享给大家，也给大家做个参考。

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

以上是脚本之家(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得脚本之家网站内容还不错，欢迎将脚本之家网站推荐给程序员好友。

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!