mysql 字段为空 赋值为其他 mysql赋值null

目录

SQL

DBMS

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

 数据库中如何保存数据

SQL语句格式要求: 

数据库相关SQL 

表相关的SQL语句

数据类型

整数:

浮点数:

字符串

日期

主键约束 primary key

主键约束+自增  primary key auto_increment

数据库SQL语句的分类

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

and 和 or 和 not

is null 和 is not null

between x and y两者之间(包含x和y)

in(x,y,z)  

distinct去重

模糊查询like

排序  order by

分页查询 

别名 

聚合函数

分组查询group by

having关键字 

各种查询相关的关键字顺序

子查询(嵌套查询)

关联关系

关联查询 

关联查询之等值链接

关联查询之内连接 

外连接

JDBC

DBCP数据库连接池

---

SQL

• Structured Query Language 结构化查询语言, 此语言是用于程序员和数据库软件进行交流的y语言

DBMS

• DataBaseManagementSystem: 数据库管理系统(俗称数据库软件) 
• 常见的数据库管理系统(DBMS)
• MySQL:  Oracle公司产品,  MySQL08年被Sun公司收购, Sun公司09年被Oracle收购.    开源产品   市占率第一  
• Oracle:     Oracle公司产品,  闭源产品, 性能最强  价格最贵  市占率第二  
• SQLServer: 微软公司产品,   闭源产品, 市占率第三
• DB2: IBM公司产品 ,闭源产品    
• SQLite: 轻量级数据, 安装包只有几十k ,只具备基础的增删改查功能  

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

• 执行SQL语句之前需要先和数据库软件进行链接   
• 从开始菜单中找到MariaDB或MySQL文件夹 展开后找到 MySQL Client 然后输入密码回车 
• 如果使用的Linux或苹果系统  打开终端 然后输入 mysql -uroot -p  回车 然后输入密码回车
• 显示下图内容 代表成功
• 执行SQL语句之前需要先和数据库软件进行链接   
• 从开始菜单中找到MariaDB或MySQL文件夹 展开后找到 MySQL Client 然后输入密码回车 
• 如果使用的Linux或苹果系统  打开终端 然后输入 mysql -uroot -p  回车 然后输入密码回车
• 显示下图内容 代表成功

• 退出命令exit; 

 数据库中如何保存数据

• 数据库中以表为单位保存数据,表是存在于数据库之中, 保存数据则需要先建库再建表
   

  

SQL语句格式要求: 

• 以;结尾 
• 不区分大小写 
• 可以包含空格和换行 
   

数据库相关SQL 

• 查询所有数据库
• 格式: show databases;
• 
  
• 创建数据库
• 格式: create database 数据库名 charset=utf8/gbk;
• 举例:
• create database db1;
• create database db2 charset=utf8;
• create database db3 charset=gbk;
• show databases;   
• 
  
• 查看数据库信息
• 格式: show create database 数据库名;
• 举例:
• show create database db1;
• show create database db2;
• show create database db3;
• 
  
• 删除数据库
• 格式: drop database 数据库名;
• 举例:
• drop database db2;
• drop database db3;
• 
  
• 使用数据库
• 执行表相关和数据相关的SQL语句之前必须使用了某一个数据库 否则会报错.
• 格式:  use 数据库名;
• 举例:
• use db1;
   

表相关的SQL语句

• 执行表相关的SQL语句必须提前使用了某个数据库,否则会报错 
• use db1;
• 
  
• 查询所有表
• show tables;
• 
  
• 创建表
• 格式: create table 表名(字段1名 类型,字段2名 类型)charset=utf8/gbk;
• 举例:
• create table person(name varchar(50), age int); 
• create table hero(name varchar(50),money int,type varchar(10))charset=utf8;
• create table student(name varchar(50),chinese int,math int,english int)charset=gbk;
• show tables;
• 
  
• 查看表信息
• 格式: show create table 表名;
• show create table student;
• 
  
• 删除表
• 格式: drop table 表名;
• drop table student; 
• 
  
• 修改表名
• 格式: rename table 原名 to 新名;
• 举例:
• rename table person to p;
• 
  
• 查看表字段
• 格式: desc 表名;              desc = description 描述
• 举例:
•   desc p;
• 
  
• 添加表字段
• 最后面添加格式:   alter table 表名 add 字段名 类型;
• 最前面添加格式: alter table 表名 add 字段名 类型 first;
• 在xxx字段的后面添加格式: alter table 表名 add 字段名 类型 after xxx;
• 举例:
• alter table emp add age int;
• alter table emp add id int first;
• alter table emp add gender varchar(5) after name;
• desc emp;  
• 
  
• 删除表字段
• 格式: alter table 表名 drop 字段名;
• 举例:
• alter table emp drop gender;
• alter table emp drop age;
• 
  
• 修改表字段
• 格式: alter table 表名 change 原名 新名  新类型;
• 举例:
• alter table emp change name age int;
• alter table emp change id name varchar(50);

---

数据类型

整数:

• int(m) 和 bigint(m) , bigint等效java中的long,  m代表显示长度,举例m=5,存18  查询到 00018   , 需要补零的话必须使用zerofill关键字 
• create table t1(age int(5) zerofill);
• insert into t1 values(18);
• select * from t1;

浮点数:

• double(m,d)  m代表总长度,d代表小数长度   54.432  m=5 d=3  
• create table t2(price double(5,3));
• insert into t2 values(45.2312312123);
• insert into t2 values(455.231);   //报错 

字符串

char(m): 固定长度字符串 , m=5  存abc  占5个字符长度 , 应用场景: 当存储长度固定时,比如存储性别char(1)  , 最大字符长度255

varchar(m):可变长度字符串, m=5 存abc 占3个字符长度, 最大值65535但是建议保存255以内长度的数据

text(m):可变长度字符串, 最大值65535 建议保存长度大于255的数据

日期

• date: 只能保存年月日
• time: 只能保存时分秒
• datetime: 保存年月日时分秒, 最大值9999-12-31, 默认值为null
• timestamp(时间戳:保存1970年1月1日到现在的毫秒数):保存年月日时分秒, 最大值2038-1-19 , 默认值为当前系统时间(当赋值为null时触发默认值)
• 举例:
• create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
• insert into t_date values("2022-06-09",null,null,null);
• insert into t_date values(null,"16:16:20","20
• 11-11-22 10:20:30",null);
• select * from t_date;

主键约束 primary key

• 约束: 创建表时给表字段添加的限制条件
• 主键: 表示数据唯一性的字段称为主键 
• 主键约束: 限制主键的值唯一且非空
• 举例:
• create table t5(id int primary key,name varchar(30));
•  insert into t5 values(1,"a"); 
•  insert into t5 values(2,"b"); 
•  insert into t5 values(2,"c");    //重复报错
•  insert into t5 values(null,"d");      //不能为null 报错  

主键约束+自增  primary key auto_increment

• 当字段设置为自增后 插入null值时会触发自增
• 自增规则: 从历史最大值+1
• 举例:
• create table t6(id int primary key auto_increment,name varchar(50));
• insert into t6 values(null,"a");
• insert into t6 values(null,"b");
• insert into t6 values(10,"c");
• insert into t6 values(null,"d");
• delete from t6 where id>=10;
• insert into t6 values(null,"e");

数据库SQL语句的分类

• DDL: 数据定义语言, 包括数据库相关和表相关的SQL, truncate table 表名;  删除表并创建新表
• DML: 数据操作语言,包括:增删改查相关的SQL语句
• DQL: 数据查询语言,包括:select查询相关
• TCL: 事务控制语言,包含和事务相关的SQL语句
• DCL: 数据控制语言, 包含创建用户以及用户权限分配相关SQL 

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

• 查询工资大于等于3000的员工姓名,工资和工作
• select name,sal,job from emp where sal>=3000;
• 查询1号部门的员工姓名,工资,部门id
• select name,sal,dept_id from emp where dept_id=1;
• 查询程序员的名字和工资
• select name,sal from emp where job="程序员";
• 查询员工表中不是销售的员工姓名和工作(两种写法)
• select name,job from emp where job!="销售";
• select name,job from emp where job<>"销售";
   

and 和 or 和 not

• and: 等效java中的&&,  需要多个条件同时满足时使用 
• or: 等效java中的|| ,  多个条件满足一个时使用 
• 查询1号部门工资高于2000的员工信息
• select * from emp where dept_id=1 and sal>2000;
• 查询3号部门或工资等于5000的员工信息
• select * from emp where dept_id=3 or sal=5000;
• 查询出CEO和项目经理的名字
• select name from emp where job="CEO" or job="项目经理";
• 查询有奖金的销售名字和奖金
• select name,comm from emp where comm>0 and job="销售";
   

is null 和 is not null

• 查询有上级领导的员工姓名和领导id
• select name,manager from emp where manager is not null;
• 查询没有上级领导的员工姓名和领导id
• select name,manager from emp where manager is null;

between x and y两者之间(包含x和y)

• 查询工资在2000到3000之间的员工信息
• select * from emp where sal>=2000 and sal<=3000;
• select * from emp where sal between 2000 and 3000;
• 查询工资在2000到3000之外的员工信息
• select * from emp where sal not between 2000 and 3000;
   

in(x,y,z)  

• 当查询某个字段的值等于多个值的时候使用
• 查询工资等于1500,3000和5000的员工姓名和工资
• select name,sal from emp where sal=1500 or sal=3000 or sal=5000;
• select name,sal from emp where sal in(1500,3000,5000);
• 查询工作不是销售和程序员的信息
• select  * from emp where job not in("销售","程序员");
   

distinct去重

• 去掉重复的数据
• 查询员工表中出现了哪几种不同的工作
• select distinct job from emp;
• 查询员工表中出现了哪几个部门的id
• select distinct dept_id from emp;
   

模糊查询like

• %: 代表0或多个未知字符
•  _:代表1个未知字符
• 举例:
• 以x开头       x%
• 以x结尾       %x
• 包含x           %x%
• 第二个字符是x       _x%
• 以x开头以y结尾    x%y
• 第三个是x倒数第二个是y       __x%y_
• 查询姓孙的员工信息
• select * from emp where name like "孙%";
• 查询名字以精结尾的员工姓名
• select name from emp where name like "%精";
• 查询工作中包含销售的员工信息
• select * from emp where job like "%销售%";
• 查询工作中第二个字是售的员工姓名和工作
• select name,job from emp where job like "_售%";
• 查询名字中包含僧 并且工资高于2000的员工姓名和工资
• select name,sal from emp where name like "%僧%" and sal>2000;
• 查询1号和2号部门中工作以市开头的员工信息
• select * from emp where job like "市%" and dept_id in(1,2);
   

排序  order by

• 格式: order by 字段名 asc升序(默认)/desc降序;
• 查询员工姓名和工资,按照工资升序排序
• select name,sal from emp order by sal;
• select name,sal from emp order by sal desc;
• 查询工资高于2000的员工姓名和工资, 按照工资降序排序
• select name,sal from emp where sal>2000 order by sal desc;
• 查询每个员工的姓名,工资和部门id 按照部门id升序排序,如果部门id一致则按照工资降序
• select name,sal,dept_id from emp order by dept_id,sal desc;
   

分页查询 

• 格式: limit 跳过的条数,请求的条数(每页的条数)
• 跳过的条数= (请求的页数-1)*每页的条数
• 举例:
• 查询第一页的5条数据(1-5)   limit 0,5  
• 查询第2页的5条数据(6-10)   limit 5,5
• 查询第5页的5条数据             limit 20,5
• 查询第8页的10条数据        limit  70,10
• 查询第7页的8条数据          limit  48,8
• 查询工资最低的3个人的信息
• select * from emp order by sal limit 0,3;
• 查询工资最高的员工信息
• select * from emp order by sal desc limit 0,1;
• 按照入职日期排序 查询第2页的3条数据
• select * from emp order by hiredate limit 3,3;
• 按照工资升序排序查询第3页的2条数据
• select * from emp order by sal limit 4,2; 
   

别名 

select name as "名字" from emp;
select name "名字" from emp;
select name 名字 from emp;
 

聚合函数

• 通过聚合函数可以对查询的多条数据进行统计查询
• 有哪些统计方式? 
• 平均值avg
•  最大值max
•  最小值min
• 求和sum
• 计数count
• 平均值avg()
• 查询1号部门的平均工资
• select avg(sal) from emp where dept_id=1;
• 最大值max()
• 查询1号部门的最高工资
• select max(sal) from emp where dept_id=1;
• 最小值min()
• 查询1号部门的最低工资
• select min(sal) from emp where dept_id=1;
• 求和sum()
• 查询程序员的工资总和
• select sum(sal) from emp where job="程序员";
• 计数count(*)
• 查询员工表的人数
• select count(*) from emp;
   

分组查询group by

• 可以将某个字段相同值的数据划分为一组,然后以组为单位进行统计查询  
• 如果题目中出现每个或每种这样的关键字 并且查询的内容为聚合函数的结果
• 查询每个部门的平均工资
• select dept_id,avg(sal) from emp group by dept_id;
• 查询每个部门的最高工资
• select dept_id,max(sal) from emp group by dept_id;
• 查询每种工作的平均工资
• select job,avg(sal) from emp group by job;
• 查询每个部门工资高于2000的人数
• select dept_id,count(*) from emp where sal>2000 group by dept_id;
• 查询每种工作的最低工资
• select job,min(sal) from emp group by job;
• 查询1号部门和2号部门的人数   
• select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;
• 查询平均工资最高的部门id和平均工资  
• select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;
   

having关键字 

• where后面只能写普通字段条件,不能写聚合函数条件
• having后面专门写聚合函数条件,而且需要和group by分组查询结合使用,写在group by 的后面
• 查询每个部门的平均工资,要求平均工资大于2000   
• select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;
• select dept_id,avg(sal) a from emp group by dept_id having a>2000;
• 查询每种工作的人数,只查询人数大于1的 
• select job,count(*) c from emp group by job having c>1;
• 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.
• select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;
• 查询每个部门的平均工资, 只查询工资在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 跳过条数,请求条数.
   

子查询(嵌套查询)

• 可以将一条SQL语句查询的结果当做另外一条SQL语句条件的值 
• 查询工资大于2号部门平均工资的员工信息
• select avg(sal)  from emp where dept_id=2;
• select * from emp where sal>(select avg(sal)  from emp where dept_id=2);
• 查询工资高于程序员最高工资的员工信息
• select max(sal) from emp where job="程序员";
• select * from emp where sal>(select max(sal) from emp where job="程序员");
• 查询工资最高的员工信息
• select * from emp where sal=(select max(sal) from emp);
• 查询和孙悟空相同工作的员工信息
• select * from emp where job=(select job from emp where name="孙悟空") and name!="孙悟空";
• 查询工资最低的员工的同事们的信息(指同一部门)
• 得到最低工资
• select min(sal) from emp;
• 通过最低工资得到拿最低工资人的部门id
• select dept_id from emp where sal=(select min(sal) from emp);
• 通过部门id查询员工
• 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);
   

关联关系

• 指创建表时,表和表之间存在的业务关系
• 有哪几种关系:

1. 一对一:有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据
2. 一对多:有AB两张表, A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据
3. 多对多:有AB两张表, A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据

• 如果两张表之间存在关联关系,如何建立关系?

4.  一对一: 可以在两张关系表中任何一个表里面添加建立关系的字段,指向另外一张表的主键
5. 一对多: 在一对多的两张表里面的表示"多"的表中添加建立关系的字段指向另外一张表的主键
6. 多对多:需要创建一个单独的关系表,关系表中两个字段分别指向另外两张表的主键

关联查询 

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

关联查询之等值链接

• 格式: select * from A,B where 关联关系 and 其它条件
• 查询每个员工的姓名和对应的部门名  
• select e.name,d.name
• from emp e,dept d where e.dept_id=d.id;
• 查询工资高于2000的员工姓名,工资和部门信息
• select e.name,e.sal,d.* 
• from emp e,dept d where e.dept_id=d.id and sal>2000;

关联查询之内连接 

• 内连接和等值链接作用一样,查询到的都是两个表的交集数据,查询不到交集以外的数据
• insert into emp(name,sal) values("灭霸",5);
• 格式: select * from A join B on 关联关系 where 条件
• 查询每个员工的姓名和对应的部门名  
• select e.name,d.name
• from emp e join dept d on e.dept_id=d.id;
• 查询工资高于2000的员工姓名,工资和部门信息
• select e.name,sal,d.*
• from emp e join dept d on e.dept_id=d.id
• where sal>2000;

外连接

• 作用:查询一张表的全部和另外一张表的交集数据
• 格式:select * from A left/right join B on 关联关系 where 条件;
• 查询所有员工名和对应的部门名
• select e.name,d.name
• from emp e left join dept d on e.dept_id=d.id;
• 查询所有部门名称,地点和对应的员工姓名和工资,只查询有领导的员工
• select d.name,loc,e.name,sal
• from emp e right join dept d on dept_id=d.id where e.manager is not null;
   

JDBC

• 作用: 通过Java代码执行所学的SQL语句
• JavaDataBaseConnectivity: Java数据库链接
• JDBC是Sun公司提供的一套专门用于Java语言和数据库进行连接的API(Application Programma Interface应用程序编程接口)
•  Sun公司为了避免Java程序员每一种数据库软件都学习一套新的方法, 通过JDBC接口将方法名定义好,各个数据库厂商根据此接口中的方法名写各自的实现类(jar包(驱动)), 这样Java程序员只需要掌握JDBC接口中方法的调用即可访问任何数据库, 即使在工作中换了数据库软件,代码也不需要发生改变.

DBCP数据库连接池

• DataBaseConnectionPool: 数据库连接池 
• 作用: 将链接重用,避免频繁的开关链接所带来的资源浪费.