DataBase数据库笔记

day01

数据库

• 学习数据库主要学习的是如何对数据进行增删改查操作.

SQL

Structured Query Language: 结构化查询语言, 通过此语言让程序员和数据库软件进行交流

刘德华 30 5000

insert into emp values("刘德华",30,5000);

DBMS

DataBaseManagementSystem: 数据库管理系统(数据库软件)

• 常见的几种DBMS:
  • MySQL: Oracle公司产品, 08年被Sun公司收购, 09年Sun公司被Oracle收购. 开源产品 , MaraDB实际上就是MySQL的一个分支使用方式和MySQL一样. 市占率排名第一
  • Oracle: Oracle公司产品, 闭源产品 ,性能最强 价格最贵, 市占率排名第二
  • SQLServer: 微软公司产品, 闭源产品 , 市占率第三
  • DB2: IBM公司产品
  • SQLite: 轻量级数据库, 安装包几十K ,只具备最基础的增删改查功能.

如何连接数据库执行SQL语句

• 执行SQL语句需要先和数据库软件建立链接之后

1. 从开始菜单中找到MariaDB或MySQL,然后打开找到里面的MySQL Client 打开 , 然后输入密码后回车

退出指令: exit

登录指令: mysql -uroot -p

数据库和表的概念

• 在MySQL数据库软件中保存数据,需要先建库,然后在库里面建表,然后把数据保存到表中

 

SQL语句格式:

1. 以;号结尾
2. 关键字不区分大小写
3. 可以有空格或换行但一定要以;结尾

数据库相关的SQL语句

1. 查询所有数据库

• 格式: show databases;

1. 创建数据库

• 格式: create database 数据库名 charset=utf8/gbk;
• 举例:
  • create database db1;
  • create database db2 charset=utf8;
  • create database db3 charset=gbk;

1. 查看数据库信息

• 格式: show create database 数据库名;
• 举例:
  • show create database db1;
  • show create database db2;
  • show create database db3;

1. 删除数据库

• 格式: drop database 数据库名;
• 举例:
  • drop database db3;
  • drop database db2;
  • show databases;

1. 使用数据库

• 执行表相关和数据相关的SQL语句之前必须先使用了某个数据库
• 格式: use 数据库名;
• 举例:
  • use db1;

数据库相关练习题:

1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk

create database mydb1 charset=utf8;

create database mydb2 charset=gbk;

1. 查询所有数据库检查是否创建成功

show databases;

1. 检查两个数据库的字符集是否正确

show create database mydb1;

show create database mydb2;

1. 先使用mydb2 再使用 mydb1

use mydb2;

use mydb1;

1. 删除两个数据库

drop database mydb2;

drop database mydb1;

表相关的SQL语句

• 执行表相关的SQL语句必须已经使用了某个数据库

use db1;

1. 创建表

• 格式: create table 表名(字段1名 类型,字段2名 类型,.......);
• 举例:
  • create table person(name varchar(50),age int);
  • create table student(name varchar(50),chinese int,math int,english int)charset=utf8;
• 创建一个员工表emp 保存名字,工资和工作

create table emp(name varchar(50),salary int,job varchar(20));

1. 查询所有表

• 格式: show tables;

1. 查询表信息

• 格式: show create table 表名;
• 举例:
  • show create table emp;

1. 查询表字段

• 格式: desc 表名;

1. 修改表名

• 格式: rename table 原名 to 新名;
• 举例:
  • rename table student to stu;

1. 删除表

• 格式: drop table 表名;

表相关练习题:

1. 创建数据库mydb3 字符集gbk 并使用

create database mydb3 charset=gbk;

use mydb3;

2. 创建t_hero英雄表, 有名字和年龄字段 默认字符集

create table t_hero(name varchar(20),age int);

3. 修改表名为hero

rename table t_hero to hero;

4. 查看表的字符集 show create table hero;

5. 查询表字段 desc hero;

6. 删除表 drop table hero;

7. 删除数据库 drop database mydb3;

UTF8字符集

• 目前utf8 和utf8mb3 一样 ,代表的是 用3个字节表示一个字符 (mb3=most byte 3)
• utf8mb4 最多不超过4个字节表示一个字符

检查自己电脑的MySQL服务是否开启

• 在我的电脑/此电脑上右键->管理 检查MySQL服务服务是否正在运行

 

表相关SQL(续)

使用db1数据库 use db1;

1. 添加表字段

• 最后面添加格式: alter table 表名 add 字段名 类型;
• 最前面添加格式: alter table 表名 add 字段名 类型 first;
• 在xxx字段后面添加: alter table 表名 add 字段名 类型 after xxx;
• 举例:
  • alter table emp add gender varchar(5);
  • alter table emp add id int first;
  • alter table emp add dept varchar(20) after name;

1. 删除表字段

• 格式: alter table 表名 drop 字段名;
• 举例:
  • alter table emp drop dept;

1. 修改表字段

• 格式: alter table 表名 change 原名 新名 新类型;
• 举例:
  • alter table emp change job dept varchar(5);

表相关SQL语句回顾:

1. 创建表 create table t1(name varchar(20),age int) charset=utf8/gbk;
2. 查询所有表 show tables;
3. 查询表信息 show create table t1;
4. 查询表字段 desc t1;
5. 删除表 drop table t1;
6. 修改表名 rename table t1 to t2;
7. 添加表字段 alter table t1 add age int first/after xxx;
8. 删除表字段 alter table t1 drop age;
9. 修改表字段 alter table t1 change 原名 新名 新类型;

表相关练习题:

1. 创建数据库mydb4 字符集utf8并使用

create database mydb4 charset=utf8;

use mydb4;

1. 创建teacher表 有名字字段

create table teacher(name varchar(50));

1. 添加表字段: 最后添加age 最前面添加id , age前面添加salary工资

alter table teacher add age int;

alter table teacher add id int first;

alter table teacher add salary int after name;

1. 删除age字段

alter table teacher drop age;

1. 修改表名为t

rename table teacher to t;

1. 删除表 drop table t;
2. 删除数据库 drop database mydb4;

数据相关SQL

• 操作数据必须保证已经使用了某个数据库并且已经准备好了保存数据的表
• create database mydb5 charset=utf8;
• use mydb5;
• create table person(name varchar(50),age int)charset=utf8;

1. 往表中插入数据

• 全表插入格式: insert into 表名 values(值1,值2);
• 指定字段插入格式: insert into 表名(字段1名,字段2名)values(值1,值2);
• 举例:
  • insert into person values('tom',18);
  • insert into person(name) values('jerry');
  • insert into person values('aaa',10),('bbb',20),('ccc',30);
  • insert into person(name) values('xxx'),("yyy"),("zzz");

day02

内容回顾:

1. 数据库相关

• 查询所有: show databases;
• 创建: create database db1 charset=utf8/gbk;
• 查看数据库信息: show create database db1;
• 删除数据库: drop database db1;
• 使用数据库: use db1;

1. 表相关

• 创建: create table t1(name varchar(20),age int)charset=utf8/gbk;
• 查询所有: show tables;
• 查看表信息: show create table t1;
• 表字段: desc t1;
• 删除表: drop table t1;
• 修改表名: rename table t1 to t2;
• 添加字段: alter table t1 add age int first/ after xxx;
• 删除字段: alter table t1 drop age;
• 修改字段: alter table t1 change 原名 新名 新类型;

1. 数据相关

• 插入数据: insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);

数据相关SQL

create database day2db charset=utf8;

use day2db;

create table person(name varchar(50),age int)charset=utf8;

insert into person values("刘德华",50);

中文问题:

• 错误原因是客户端和MySQL之间编解码字符集不一致导致的
• 解决方案: 修改MySQL的解码字符集为gbk

set names gbk;

1. 查询数据

• 格式: select 字段信息 from 表名 where 条件;
• 举例:

insert into person values('刘备',40),('关羽',30),('悟空',20),('八戒',10),('张学友',5);

• • select name from person;
  • select name,age from person;
  • select * from person;
  • select * from person where age=50;
  • select age from person where name="悟空";

1. 修改数据

• 格式: update 表名 set 字段名=值 where 条件;
• 举例:
  • update person set age=88 where name='刘备';
  • update person set name='张飞',age=18 where name='关羽';
  • update person set name='黎明' where age=5;

1. 删除数据

• 格式: delete from 表名 where 条件;
• 举例:
  • delete from person where name='张飞';
  • delete from person where age<30;
  • delete from person;

综合练习题:

1. 创建数据库day1db 字符集utf8并使用

create database day1db charset=utf8;

use day1db;

2. 创建t_hero表, 有name字段 字符集utf8

create table t_hero(name varchar(50))charset=utf8;

3. 修改表名为hero

rename table t_hero to hero;

4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段

alter table hero add money int;

alter table hero add id int first;

alter table hero add age int after name;

5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888

insert into hero values(1,'李白',50,6888),(2,'赵云',30,13888),(3,'刘备',25,6888);

6. 查询价格为6888的英雄名

select name from hero where money=6888;

7. 修改刘备年龄为52岁

update hero set age=52 where name='刘备';

8. 修改年龄小于等于50岁的价格为5000

update hero set money=5000 where age<=50;

9. 删除价格为5000的信息

delete from hero where money=5000;

10. 删除表, 删除数据库

drop table hero;

drop database day1db;

主键约束

• 主键: 表示数据唯一性的字段称为主键
• 约束: 创建表时给表字段添加的限制条件
• 主键约束: 限制主键的值 唯一且非空
• 如何使用:
  • use day2db;
  • create table t1 (id int primary key,name varchar(50))charset=utf8;
  • insert into t1 values(1,"aaa");
  • insert into t1 values(1,"bbb"); //报错 主键值重复
  • insert into t1 values(null,"ccc");

主键约束+自增

• 自增规则: 从历史最大值基础上+1
• 如何使用: create table t2(id int primary key auto_increment,name varchar(50))charset=utf8;
• insert into t2 values(null,"aaa");
• insert into t2 values(null,"bbb");
• insert into t2 values(10,"ccc");
• insert into t2 values(null,"ddd");
• delete from t2 where id>=10;
• insert into t2 values(null,"eee");

SQL语句分类

• DDL: 数据定义语言,包括数据库相关和表相关的SQL语句
• DML: 数据操作语言, 包括增删改查
• DQL: 数据查询语言, 只包含select查询相关的SQL语句
• TCL: 事务控制语言
• DCL: 数据控制语言

数据类型

1. 整数: int(m)和bigint(m) m代表显示长度, m=5 存18 查询得到00018

create table t3(age int(5) zerofill);

insert into t3 values(18);

select * from t3;

1. 浮点数: double(m,d) m代表总长度,d代表小数长度 , 存23.212 m=5 d=3

create table t5(price double(5,3));

insert into t5 values(23.32123);

insert into t5 values(233.32123); //报错

1. 字符串:
   1. char(m), 固定长度, m=10 存abc 占10, 执行效率略高, 当保存数据的长度相对固定时使用, 最大值255
   2. varchar(m),可变长度,m=10 存abc 占3,更节省空间, 最大值65535 但推荐保存短的数据(255以内)
   3. text(m),可变长度, 最大值65535,建议保存长度大于255的
2. 日期:
   1. date, 只能保存年月日
   2. time, 只能保存时分秒
   3. datetime, 保存年月日时分秒, 默认值为null , 最大值 9999-12-31
   4. timestamp(时间戳,距离1970年1月1日的毫秒数),保存年月日时分秒,默认值为当前系统时间,最大值 2038-1-19

create table t6(t1 date,t2 time,t3 datetime,t4 timestamp);

insert into t6 values("2022-5-15",null,null,null);

insert into t6 values(null,"14:20:25","2011-10-22 10:20:30",null);

导入*.sql批处理文件

1. 从老师工程中得到emp.zip 从idea中复制粘贴到 某个磁盘的根目录 ,然后右键解压到当前文件夹, 在根目录下出现emp.sql文件
2. 在客户端中 执行 source f:/emp.sql;
3. 执行以下SQL语句 检查是否成功

show tables; //检查是否出现了 emp和dept两个表

select * from emp; //检查是否出现了数据, 如果出现乱码 执行set names utf8; 如果格式错乱 正常

去重distinct

1. 查询员工表中所有不同的工作

select distinct job from emp;

1. 查询员工表中出现了哪几个不同的部门id?

select distinct dept_id from emp;

is null和is not null

1. 查询有领导的员工姓名和领导id

select name,manager from emp where manager is not null;

1. 查询没有领导的员工姓名

select name from emp where manager is null;

and 和 or

1. 查询1号部门工资高于2000的员工信息

select * from emp where dept_id=1 and sal>2000;

1. 查询3号部门或工资等于5000的员工信息

select * from emp where dept_id=3 or sal=5000;

1. 查询出孙悟空和猪八戒的员工信息

select * from emp where name="孙悟空" or name="猪八戒";

比较运算符 > < >= <= = !=和<>

1. 查询工资大于等于3000的员工信息

select * from emp where sal>=3000;

1. 查询工作不是程序员的员工信息(两种写法)

select * from emp where job!="程序员";

select * from emp where job<>"程序员";

between x and y 两者之间

1. 查询工资在2000到3000之间的员工信息

select * from emp where sal>=2000 and sal<=3000;

select * from emp where sal between 2000 and 3000;

select * from emp where sal not between 2000 and 3000;

in关键字

1. 查询工资等于5000,1500,3000的员工信息

select * from emp where sal=5000 or sal=1500 or sal=3000;

select * from emp where sal in(5000,1500,3000);

select * from emp where sal not in(5000,1500,3000);

综合练习题

1. 查询1号部门有哪几种不同的工作

select distinct job from emp where dept_id=1;

1. 查询1号部门中有上级领导的员工信息

select * from emp where dept_id=1 and manager is not null;

1. 查询工作是程序员,销售和人事的员工信息

select * from emp where job in("程序员","销售","人事");

1. 查询工资不在1000-2000之间的员工信息

select * from emp where sal not between 1000 and 2000;

1. 查询有奖金的员工信息

select * from emp where comm>0;

模糊查询like

• %: 代表0或多个未知字符
• _:代表1个未知字符
• 举例:
  • 以x开头 x%
  • 以x结尾 %x
  • 包含x %x%
  • 第二个字符是x _x%
  • 以x开头以y结尾 x%y
  • 第二个是x倒数第三个是y _x%y__

1. 查询名字姓孙的员工信息

select * from emp where name like "孙%";

1. 查询名字以精结尾的员工姓名

select name from emp where name like "%精";

1. 查询工作第二个字是售的员工姓名和工作

select name,job from emp where job like "_售%";

1. 查询名字中包含僧并且工资大于2000的员工姓名和工资

select name,sal from emp where name like "%僧%" and sal>2000;

排序order by

• 格式: order by 字段名 asc(升序默认)/desc(降序)

description描述

descend 降序

1. 查询所有员工姓名和工资并按照工资升序排序

select name,sal from emp order by sal;

select name,sal from emp order by sal asc;

1. 查询所有员工姓名和工资并按照工资降序排序

select name,sal from emp order by sal desc;

1. 查询所有员工姓名,工资和部门id并且按照部门id升序排序,如果部门id一致则按照工资降序排序

select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询

• 格式: limit 跳过的条数,请求的条数(每页的条数)
• 跳过的条数=(请求的页数-1)*请求的条数(每页条数)
• 举例:
  • 查询第1页的5条数据(1-5条) limit 0, 5
  • 查询第2页的5条数据(6-10条) limit 5, 5
  • 请求第1页的10条数据 limit 0,10
  • 请求第3页的10条数据 limit 20,10
  • 请求第8页的10条数据 limit 70,10
  • 请求第6页的8条数数据 limit 40,8

1. 查询工资最低的3个员工信息

查询按照工资升序排序的第一页的3条数据

select * from emp order by sal limit 0,3;

1. 按照入职日期(hiredate) 升序排序 查询第3页的3条数据

select * from emp order by hiredate limit 6,3;

1. 查询工资最高的员工信息

select * from emp order by sal desc limit 0,1;

1. 查询按照工资降序第2页的5条数据

select * from emp order by sal desc limit 5,5;

综合练习题

1. 查询员工表中3号部门工资高于1500的员工信息

select * from emp where dept_id=3 and sal>1500;

2. 查询2号部门员工或者没有领导的员工信息

select * from emp where dept_id=2 or manager is null;

3. 查询有领导的员工姓名,工资按照工资降序排序

select name,sal from emp

where manager is not null order by sal desc;

4. 查询2号和3号部门的员工姓名和入职日期hiredate按照入职日期降序排序

select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;

5. 查询名字中包含僧和包含精的员工姓名

select name from emp where name like "%僧%" or name like "%精%";

6. 查询工资高于2000的工作有哪几种?

select distinct job from emp where sal>2000;

1. 查询工资升序第4页的2条数据

select * from emp order by sal limit 6,2;

别名

select name as "姓名" from emp ;

select name "姓名" from emp ;

select name 姓名 from emp ;

聚合函数

• 通过聚合函数可以对查询的多条数据进行统计查询,统计查询的方式包括:求平均值, 求最大值,求最小值,求和,计数

1. 平均值avg(字段名)

• 查询1号部门的平均工资

select avg(sal) from emp where dept_id=1;

• 查询销售的平均工资

select avg(sal) from emp where job="销售";

1. 最大值max(字段名)

• 查询程序员的最高工资

select max(sal) from emp where job="程序员";

1. 最小值min(字段名)

• 查询3号部门的最低工资

select min(sal) from emp where dept_id=3;

1. 求和sum(字段名)

• 查询2号部门的工资总和

select sum(sal) from emp where dept_id=2;

1. 计数count(*)

• 查询程序员的数量

select count(*) from emp where job="程序员";

数值计算 + - * / %

1. 查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)

select name,sal,sal*5 年终奖 from emp;

1. 给3号部门的员工每人涨薪5块钱

update emp set sal=sal+5 where dept_id=3;

晚课练习题:

1. 查询工资大于等于3000的员工姓名和工资

2. 查询1号部门的员工姓名和工作

3. 查询不是程序员的员工姓名和工作(两种写法)

4. 查询奖金等于300的员工姓名,工资和工作

5. 查询1号部门工资大于2000的员工信息

6. 查询3号部门或工资等于5000的员工信息

7. 查询出CEO和项目经理的名字

8. 查询工资为3000,1500和5000的员工信息

9. 查询工资不等于3000,1500和5000的员工信息

10. 查询工资在1000到2000之间的员工信息

11. 查询工资在1000到2000以外的员工信息

12. 查询有领导的员工姓名和领导id

13. 查询没有领导的员工姓名和领导id

14. 查询员工表中出现了哪几种不同的工作

15. 查询员工表中出现了那几个部门的id

16. 查询姓孙的员工姓名

17. 查询名字最后一个字是精的员工信息

18. 查询工作中包含销售的员工信息

19. 查询工作中第二个字是售的员工信息

20. 查询名字中包含僧的员工并且工资高于2000的员工信息

21. 查询1号和2号部门中工作以市开头的员工信息

22. 查询所有员工的姓名和工资 按照工资升序排序

23. 查询所有员工的姓名和工资 按照工资降序排序

24. 查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序

25. 查询员工表中3号部门工资高于1500的员工信息

26. 查询2号部门员工或者没有领导的员工信息

27. 查询有领导的员工姓名,工资按照工资降序排序

28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序

29. 查询名字中包含僧和包含精的员工姓名

30. 查询工资高于2000的工作有哪几种?

31. 查询工资最高的前三个员工

32. 查询员工表按照id排序, 第2页的5条数据

33. 查询员工表按照id排序, 第3页的4条数据

34. 查询3号部门工资最低的员工姓名和工资

35. 查询工作不是人事的员工中工资降序第二页的3条数据

36. 查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)

37. 给3号部门所有员工涨薪5块钱

38. 查询没有领导的员工和3号部门的员工,工资降序取前三条

39. 查询2号部门的最高工资

40. 查询有领导的员工中工资在1000到2000之间的人数

41. 查询3号部门的工资总和

42. 查询程序员和销售的总人数

43. 查询1号部门有领导的员工的平均工资

44. 查询1号部门的最低工资和最高工资

45. 查询和销售相关的工作人数

46. 查询工资不是1500和3000的员工人数

47. 查询1号部门出现了哪几种工作

day03

内容回顾:

1. 数据库相关SQL

• 查询所有 show databases;
• 创建 create database db1 charset=utf8/gbk;
• 查询信息 show create database db1;
• 删除 drop database db1;
• 使用 use db1;

1. 表相关SQL

• 创建 create table t1(name varchar(20)) charset=utf8/gbk;
• 查询所有 show tables;
• 查询表信息 show create table t1;
• 表字段 desc t1;
• 删除表 drop table t1;
• 修改表名 rename table t1 to t2;
• 添加字段 alter table t1 add age int first/ after xxx;
• 删除字段 alter table t1 drop age;
• 修改字段 alter table t1 change 原名 新名 新类型;

1. 数据相关SQL

• 插入数据 insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);
• 查询数据 select 字段信息 from 表名 where 条件;
• 修改数据 update 表名 set xxx=xxx,xxx=xxx where 条件;
• 删除数据 delete from 表名 where 条件;

1. SQL语句分类

• DDL: 数据定义语言 包括 数据库相关和表相关的SQL
• DML: 数据操作语言 包括 增删改查
• DQL: 数据查询语言 包括 select查询
• TCL: 事务控制语言
• DCL: 数据控制语言

1. 去重distinct
2. and or not
3. between x and y 两者之间
4. in(xxx,xxx)
5. 模糊查询like %0或多个未知字符 _1个未知字符
6. 排序 order by 字段名 asc/desc,字段名 asc/desc
7. 分页 limit 跳过的条数,请求条数(每页条数)
8. 别名 select name 名字 from emp;
9. 聚合函数: 求平均值avg(), 最大值max(), 最小值min(),求和sum(),计数count(*)

分组查询 group by

• 格式: group by 分组的字段名

1. 查询每个部门的平均工资

select dept_id,avg(sal) from emp group by dept_id;

1. 查询每个部门的最高工资

select dept_id,max(sal) from emp group by dept_id;

1. 查询每种工作的最高工资

select job,max(sal) from emp group by job;

1. 查询每种工作的人数

select job,count(*) from emp group by job;

1. 查询每个部门工资高于2000的人数

select dept_id,count(*) from emp where sal>2000 group by dept_id;

1. 查询每个部门有领导的员工的人数

select dept_id,count(*) from emp where manager is not null group by dept_id;

having

• where后面只能写普通字段的条件,不能包含聚合函数
• having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面

1. 查询每个部门的平均工资要求平均工资高于2000

select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;

1. 查询每种工作的人数,只查询人数大于1 的

select job,count(*) from emp group by job having count(*)>1;

select job,count(*) c from emp group by job having c>1;

1. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400

select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;

4. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的

select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;

各个关键字的书写顺序

select 查询的字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;

子查询(嵌套查询)

1. 查询工资大于2号部门平均工资的员工信息

select avg(sal) from emp where dept_id=2;

select * from emp where sal>(select avg(sal) from emp where dept_id=2);

1. 查询工资高于程序员最高工资的员工信息

select max(sal) from emp where job="程序员";

select * from emp where sal>(select max(sal) from emp where job="程序员");

1. 查询工资最高的员工信息

select max(sal) from emp;

select * from emp where sal=(select max(sal) from emp);

1. 查询和孙悟空相同工作的员工信息

select job from emp where name="孙悟空";

select * from emp where job=(select job from emp where name="孙悟空") and name!="孙悟空";

1. 查询拿最低工资员工的同事们的信息(同事指同一部门)

select min(sal) from emp;

select dept_id from emp where sal=(select min(sal) from emp);

select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

关联关系

• 指创建的表和表之间存在的业务关系
• 有哪几种关系?
  • 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据

• 一对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据
• 多对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据
• 表和表之间如何建立关系 ,通过外键字段建立关系
  • 一对一: 在任意表中添加一个建立关系的字段指向另外一张表的主键 .
  • 一对多: 在多的表中添加建立关系的字段(外键) 指向另外一张表的主键 .
  • 多对多: 需要创建一个单独的关系表,里面至少包含两个字段分别指向另外两个表的主键.

关联查询

• 同时查询多张表数据的查询方式称为关联查询
• 关联查询包括: 等值链接, 内连接和外连接

关联查询之等值链接

• 格式: select * from A,B where 关联关系

1. 查询工资高于2000的员工姓名和对应的部门名

select e.name,d.name,sal

from emp e,dept d where e.dept_id=d.id and sal>2000;

关联查询之内连接

• 格式: select * from A join B on 关联关系

1. 查询工资高于2000的员工姓名和对应的部门名

select e.name,d.name,sal from emp e join dept d on e.dept_id=d.id where sal>2000;

关联查询之外连接

• 等值链接和内连接查询到的都是两张表的交集数据
• 外连接查询的是一张表的全部和另外一张表的交集数据
• 格式: select * from A left/right join B on 关联关系

insert into emp(name,sal) values("灭霸",5);

1. 查询所有员工姓名和对应的部门名

select e.name,d.name from emp e left join dept d on e.dept_id=d.id;

1. 查询所有部门的名称,地点和对应的员工姓名和工资

select d.name,loc,e.name,sal

from emp e right join dept d on e.dept_id=d.id;

关联查询总结:

1. 如果需要同时查询多张表的数据使用关联查询
2. 关联查询包括:等值链接,内连接和外连接
3. 等值链接和内连接查询的是两个表的交集数据, 推荐使用内连接
4. 如果需要查询一张表的全部和另外一张表的交集时 使用外连接,只需要掌握左外即可,因为表的位置可以交换

如何查询多对多表中的数据

• 创建表:

create table student(id int primary key auto_increment,name varchar(50))charset=utf8;

create table teacher(id int primary key auto_increment,name varchar(50))charset=utf8;

create table t_s(tid int,sid int);

• 插入数据

insert into teacher values(null,"苍老师"),(null,"传奇哥");

insert into student values(null,"小明"),(null,"小红"),(null,"小绿"),(null,"小狗"),(null,"小黄");

insert into t_s values(1,1),(1,5),(1,4),(2,2),(2,3),(2,1),(2,5);

1. 查询每个老师对应的学生

select t.name,s.name

from teacher t join t_s ts on t.id=ts.tid

join student s on s.id=ts.sid;

1. 查询苍老师的学生都有谁?

select s.name

from teacher t join t_s ts on t.id=ts.tid

join student s on s.id=ts.sid

where t.name="苍老师";

1. 查询小明的老师是谁?

select t.name

from teacher t join t_s ts on t.id=ts.tid

join student s on s.id=ts.sid

where s.name="小明";

JDBC

• JavaDataBaseConnectivity: Java数据库链接
• 学习JDBC主要学习的就是如何通过Java语言和MySQL数据库进行链接并执行SQL语句.
• JDBC是Sun公司提供的一套专门用于Java语言和数据库进行链接的API(Application Programma Interface应用程序编程接口)
• 为什么使用JDBC?

Sun公司为了避免Java程序员,每一种数据库软件都学习一套全新的方法,通过JDBC接口将方法名定义好, 让各个数据库厂商根据此接口中的方法名写各自的实现类(就是一个jar文件, 称为数据库的驱动) ,这样Java程序员只需要掌握JDBC接口中方法的调用,即可访问任何数据库软件.

• 如何通过JDBC链接数据库并执行SQL语句
  • 创建Maven工程
  • 在工程的pom.xml文件中 添加MySQL驱动的依赖坐标
  • 创建cn.tedu.Demo01.java文件 添加以下代码

//1.创建链接对象    异常抛出
Connection conn =
        DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false",
                "root","root");
System.out.println("链接对象:"+conn);
//2.创建执行SQL语句的对象
Statement s = conn.createStatement();
//3.执行SQL语句   execute执行
s.execute("create table jdbct1(age int)");
//4.关闭资源
conn.close();
System.out.println("执行完成!");

执行SQL语句的对象Statement

• execute("sql"); 此方法可以执行任意SQL语句, 但建议执行DDL(数据库相关和表相关的SQL语句)
• int row = executeUpdate("sql"); 此方法用来执行增删改相关的SQL语句, 返回值表示生效的行数
• ResultSet rs = executeQuery("sql"); 此方法用来执行查询相关的SQL语句,返回值ResultSet叫做结果集对象,查询到的数据都装在此对象中

数据库常见错误

1. 用户名或密码错误: 检查用户名或密码

1. 值的数量 和字段数量不匹配, 检查表的字段是否正确,检查插入的值是否正确

1. 主键值重复了

1. 值不能为null

1. JDBC链接数据库时的报错: 代表没有启动MySQL服务, 在我的电脑上右键管理 找到Mysql服务右键开启即可

1. 个别MySQL版本出现的SSL异常, 在连接数据库的url上面添加&useSSL=false 即可解决

1. SQL语句拼写错误