MySQL多表查询
来源:互联网 发布:工业监控组态软件 编辑:程序博客网 时间:2024/06/08 16:39
1.主键约束(非空,唯一,被引用)
//方式一CREATE TABLE stu(sid INT PRIMARY KEY,sname VARCHAR(20),age INT);//方式二CREATE TABLE stu(sid INT,sname VARCHAR(20),age INT, PRIMARY KEY(sid));//方式三CREATE TABLE stu(sid INT,sname VARCHAR(20),age INT);ALTER TABLE stu ADD PRIMARY KEY(sid);//删除主键ALTER TABLE stu DROP PRIMARY KEY;
2.外键(可以为空,但是不能不存在)
//1.从表CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(20));INSERT INTO dept VALUE(10,"研发部");INSERT INTO dept VALUE(20,"财务部");INSERT INTO dept VALUE(30,"运营部");//2.主表CREATE TABLE emp( empno INT PRIMARY KEY, ename VARCHAR(20), deptno INT, CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept(deptno));
3.一对一
//1.从表CREATE TABLE hasband( hid INT PRIMARY KEY, hname VARCHAR(20));SELECT * FROM hasband;INSERT INTO hasband VALUE(1,"刘备");INSERT INTO hasband VALUE(2,"关羽");INSERT INTO hasband VALUE(3,"张飞");//2.主表(主表的主键作为外键)CREATE TABLE wife( wid INT PRIMARY KEY, wname VARCHAR(20), CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid));INSERT INTO wife VALUE(1,"小乔");INSERT INTO wife VALUE(2,"貂蝉");INSERT INTO wife VALUE(3,"吴氏");
4.多对多
//学生表CREATE TABLE student( sid INT PRIMARY KEY, sname VARCHAR(20));//老师表CREATE TABLE teacher( tid INT PRIMARY KEY, tname VARCHAR(20));//关联表CREATE TABLE stu_tea( sid INT, tid INT, CONSTRAINT fk_stu FOREIGN KEY(sid) REFERENCES student(sid), CONSTRAINT fk_tea FOREIGN KEY(tid) REFERENCES teacher(tid));INSERT INTO student VALUE(1,"老大");INSERT INTO student VALUE(2,"老二");INSERT INTO student VALUE(3,"老三");INSERT INTO student VALUE(4,"老四");INSERT INTO student VALUE(5,"老五");INSERT INTO teacher VALUE(1,"李老师");INSERT INTO teacher VALUE(2,"张老师");INSERT INTO teacher VALUE(3,"刘老师");INSERT INTO teacher VALUE(4,"武老师");INSERT INTO stu_tea VALUE(1,1);INSERT INTO stu_tea VALUE(2,1);INSERT INTO stu_tea VALUE(3,1);INSERT INTO stu_tea VALUE(4,1);INSERT INTO stu_tea VALUE(5,1);INSERT INTO stu_tea VALUE(1,2);INSERT INTO stu_tea VALUE(2,2);INSERT INTO stu_tea VALUE(3,2);INSERT INTO stu_tea VALUE(2,3);INSERT INTO stu_tea VALUE(3,3);INSERT INTO stu_tea VALUE(4,3);INSERT INTO stu_tea VALUE(5,3);INSERT INTO stu_tea VALUE(3,4);INSERT INTO stu_tea VALUE(4,4);INSERT INTO stu_tea VALUE(5,4);
5.多表查询
合并结果集连接查询 内连接 左外连接 右外连接子查询
6.合并结果集(查询的列类型必须一致)
CREATE TABLE aa(a INT,b VARCHAR(20)); CREATE TABLE bb(c INT,d VARCHAR(20));//1.查询全部 SELECT * FROM aa UNION ALL SELECT * FROM bb;//2.去重复查询 SELECT * FROM aa UNION ALL SELECT * FROM bb;
7.内连接查询
//1.方言 SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno;//2.标准 SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno//3.自然 SELECT * FROM emp NATURAL JOIN dept
8.左外连接
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno
9.右外连接
SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno
10.全外连接
左外连接与右外连接合并结果集去重复就是全外连接
11.子查询
//查询工资高于诸葛亮的员工信息 SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename="诸葛亮");
0 0
- 【MySQL】MySQL多表查询
- MySQL 多表查询
- mysql多表查询
- Mysql多表查询
- MySQL多表查询
- MYSQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- mysql 多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySql:多表查询
- MySQL多表查询
- 关于欧拉筛法和普通筛法
- RXJava
- Liferay Portal 学习笔记之<portlet:defineObjects>标签
- java中 接口
- 图解Linux命令之--export命令
- MySQL多表查询
- thinkphp之trace的程序简单调试
- nyoj613免费馅饼(动态规划)
- AngularJS内的服务
- MVC中code first方式开发,数据库的生成与更新(Ef6)
- 后台java编程规范
- 连接虚拟机mysql
- Django制作博客中为文章添加评论
- Mac苹果系统,IOS下cocos2dx建立项目 手游戏