当前位置:Gxlcms > 数据库问题 > MySQL(三)

MySQL(三)

时间:2021-07-01 10:21:17 帮助过:26人阅读

  1. --- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任
  2. ----主表
  3. CREATE TABLE ClassCharger(
  4. id TINYINT PRIMARY KEY auto_increment,
  5. name VARCHAR (20),
  6. age INT ,
  7. is_marriged boolean -- show create table ClassCharger: tinyint(1)
  8. );
  9. INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
  10. ("丹丹",14,0),
  11. ("歪歪",22,0),
  12. ("姗姗",20,0),
  13. ("小雨",21,0);
  14. ----子表
  15. CREATE TABLE Student(
  16. id INT PRIMARY KEY auto_increment,
  17. name VARCHAR (20),
  18. charger_id TINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致
  19. -- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
  20. ) ENGINE=INNODB;
  21. INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
  22. ("alvin2",4),
  23. ("alvin3",1),
  24. ("alvin4",3),
  25. ("alvin5",1),
  26. ("alvin6",3),
  27. ("alvin7",2);
  28. DELETE FROM ClassCharger WHERE name="冰冰";
  29. INSERT student (name,charger_id) VALUES ("yuan",1);
  30. -- 删除居然成功,可是 alvin3显示还是有班主任id=1的冰冰的;
  31. -----------增加外键和删除外键---------
  32. ALTER TABLE student ADD CONSTRAINT abc
  33. FOREIGN KEY(charger_id)
  34. REFERENCES classcharger(id);
  35. ALTER TABLE student DROP FOREIGN KEY abc;

多表查询

准备表

技术分享
  1. -- 准备两张表
  2. -- company.employee
  3. -- company.department
  4. create table employee(
  5. emp_id int auto_increment primary key not null,
  6. emp_name varchar(50),
  7. age int,
  8. dept_id int
  9. );
  10. insert into employee(emp_name,age,dept_id) values
  11. (‘A‘,19,200),
  12. (‘B‘,26,201),
  13. (‘C‘,30,201),
  14. (‘D‘,24,202),
  15. (‘E‘,20,200),
  16. (‘F‘,38,204);
  17. create table department(
  18. dept_id int,
  19. dept_name varchar(100)
  20. );
  21. insert into department values
  22. (200,‘人事部‘),
  23. (201,‘技术部‘),
  24. (202,‘销售部‘),
  25. (203,‘财政部‘);
  26. mysql> select * from employee;
  27. +--------+----------+------+---------+
  28. | emp_id | emp_name | age | dept_id |
  29. +--------+----------+------+---------+
  30. | 1 | A | 19 | 200 |
  31. | 2 | B | 26 | 201 |
  32. | 3 | C | 30 | 201 |
  33. | 4 | D | 24 | 202 |
  34. | 5 | E | 20 | 200 |
  35. | 6 | F | 38 | 204 |
  36. +--------+----------+------+---------+
  37. 6 rows in set (0.00 sec)
  38. mysql> select * from department;
  39. +---------+-----------+
  40. | dept_id | dept_name |
  41. +---------+-----------+
  42. | 200 | 人事部 |
  43. | 201 | 技术部 |
  44. | 202 | 销售部 |
  45. | 203 | 财政部 |
  46. +---------+-----------+
  47. 4 rows in set (0.01 sec)
技术分享

多表查询之连接查询

1.笛卡尔积查询

技术分享
  1. mysql> SELECT * FROM employee,department;
  2. -- select employee.emp_id,employee.emp_name,employee.age,
  3. -- department.dept_name from employee,department;
  4. +--------+----------+------+---------+---------+-----------+
  5. | emp_id | emp_name | age | dept_id | dept_id | dept_name |
  6. +--------+----------+------+---------+---------+-----------+
  7. | 1 | A | 19 | 200 | 200 | 人事部 |
  8. | 1 | A | 19 | 200 | 201 | 技术部 |
  9. | 1 | A | 19 | 200 | 202 | 销售部 |
  10. | 1 | A | 19 | 200 | 203 | 财政部 |
  11. | 2 | B | 26 | 201 | 200 | 人事部 |
  12. | 2 | B | 26 | 201 | 201 | 技术部 |
  13. | 2 | B | 26 | 201 | 202 | 销售部 |
  14. | 2 | B | 26 | 201 | 203 | 财政部 |
  15. | 3 | C | 30 | 201 | 200 | 人事部 |
  16. | 3 | C | 30 | 201 | 201 | 技术部 |
  17. | 3 | C | 30 | 201 | 202 | 销售部 |
  18. | 3 | C | 30 | 201 | 203 | 财政部 |
  19. | 4 | D | 24 | 202 | 200 | 人事部 |
  20. | 4 | D | 24 | 202 | 201 | 技术部 |
  21. | 4 | D | 24 | 202 | 202 | 销售部 |
  22. | 4 | D | 24 | 202 | 203 | 财政部 |
  23. | 5 | E | 20 | 200 | 200 | 人事部 |
  24. | 5 | E | 20 | 200 | 201 | 技术部 |
  25. | 5 | E | 20 | 200 | 202 | 销售部 |
  26. | 5 | E | 20 | 200 | 203 | 财政部 |
  27. | 6 | F | 38 | 204 | 200 | 人事部 |
  28. | 6 | F | 38 | 204 | 201 | 技术部 |
  29. | 6 | F | 38 | 204 | 202 | 销售部 |
  30. | 6 | F | 38 | 204 | 203 | 财政部 |
  31. +--------+----------+------+---------+---------+-----------+
技术分享

2.内连接

技术分享
  1. -- 查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
  2. select * from employee,department where employee.dept_id = department.dept_id;
  3. --select * from employee inner join department on employee.dept_id = department.dept_id;
  4. +--------+----------+------+---------+---------+-----------+
  5. | emp_id | emp_name | age | dept_id | dept_id | dept_name |
  6. +--------+----------+------+---------+---------+-----------+
  7. | 1 | A | 19 | 200 | 200 | 人事部 |
  8. | 2 | B | 26 | 201 | 201 | 技术部 |
  9. | 3 | C | 30 | 201 | 201 | 技术部 |
  10. | 4 | D | 24 | 202 | 202 | 销售部 |
  11. | 5 | E | 20 | 200 | 200 | 人事部 |
  12. +--------+----------+------+---------+---------+-----------+
技术分享

3.外连接

技术分享
  1. --(1)左外连接:在内连接的基础上增加左边有右边没有的结果
  2. select * from employee left join department on employee.dept_id = department.dept_id;
  3. +--------+----------+------+---------+---------+-----------+
  4. | emp_id | emp_name | age | dept_id | dept_id | dept_name |
  5. +--------+----------+------+---------+---------+-----------+
  6. | 1 | A | 19 | 200 | 200 | 人事部 |
  7. | 5 | E | 20 | 200 | 200 | 人事部 |
  8. | 2 | B | 26 | 201 | 201 | 技术部 |
  9. | 3 | C | 30 | 201 | 201 | 技术部 |
  10. | 4 | D | 24 | 202 | 202 | 销售部 |
  11. | 6 | F | 38 | 204 | NULL | NULL |
  12. +--------+----------+------+---------+---------+-----------+
  13. --(2)右外连接:在内连接的基础上增加右边有左边没有的结果
  14. select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;
  15. +--------+----------+------+---------+---------+-----------+
  16. | emp_id | emp_name | age | dept_id | dept_id | dept_name |
  17. +--------+----------+------+---------+---------+-----------+
  18. | 1 | A | 19 | 200 | 200 | 人事部 |
  19. | 2 | B | 26 | 201 | 201 | 技术部 |
  20. | 3 | C | 30 | 201 | 201 | 技术部 |
  21. | 4 | D | 24 | 202 | 202 | 销售部 |
  22. | 5 | E | 20 | 200 | 200 | 人事部 |
  23. | NULL | NULL | NULL | NULL | 203 | 财政部 |
  24. +--------+----------+------+---------+---------+-----------+
  25. --(3)全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
  26. -- mysql不支持全外连接 full JOIN
  27. -- mysql可以使用此种方式间接实现全外连接
  28. select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
  29. UNION
  30. select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
  31. +--------+----------+------+---------+---------+-----------+
  32. | emp_id | emp_name | age | dept_id | dept_id | dept_name |
  33. +--------+----------+------+---------+---------+-----------+
  34. | 1 | A | 19 | 200 | 200 | 人事部 |
  35. | 2 | B | 26 | 201 | 201 | 技术部 |
  36. | 3 | C | 30 | 201 | 201 | 技术部 |
  37. | 4 | D | 24 | 202 | 202 | 销售部 |
  38. | 5 | E | 20 | 200 | 200 | 人事部 |
  39. | NULL | NULL | NULL | NULL | 203 | 财政部 |
  40. | 6 | F | 38 | 204 | NULL | NULL |
  41. +--------+----------+------+---------+---------+-----------+
  42. -- 注意 union与union all的区别:union会去掉相同的纪录<br><br>

多表查询之子查询

技术分享
  1. -- 子查询是将一个查询语句嵌套在另一个查询语句中。
  2. -- 内层查询语句的查询结果,可以为外层查询语句提供查询条件。
  3. -- 子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
  4. -- 还可以包含比较运算符:= 、 !=、> 、<等
  5. -- 1. 带IN关键字的子查询
  6. ---查询employee表,但dept_id必须在department表中出现过
  7. select * from employee
  8. where dept_id IN
  9. (select dept_id from department);
  10. +--------+----------+------+---------+
  11. | emp_id | emp_name | age | dept_id |
  12. +--------+----------+------+---------+
  13. | 1 | A | 19 | 200 |
  14. | 2 | B | 26 | 201 |
  15. | 3 | C | 30 | 201 |
  16. | 4 | D | 24 | 202 |
  17. | 5 | E | 20 | 200 |
  18. +--------+----------+------+---------+
  19. 5 rows in set (0.01 sec)
  20. -- 2. 带比较运算符的子查询
  21. -- =、!=、>、>=、<、<=、<>
  22. -- 查询员工年龄大于等于25岁的部门
  23. select dept_id,dept_name from department
  24. where dept_id IN
  25. (select DISTINCT dept_id from employee where age>=25);

MySQL(三)

标签:innodb   员工   constrain   创建   tool   any   一个   基础   利用   

人气教程排行