jdbc2_jdbc设置非空-CSDN博客

本文链接：https://blog.csdn.net/liu1028701143/article/details/6779479

1. 数据库概念

1.1. 数据库服务器、数据库和表之间的关系

所谓安装数据库服务器, 只是在机器上安装了一个数据库管理程序, 这个管理程序可以管理多个数据库.

一般开发人员在设计项目的时候会针对每一个应用创建一个数据库.

为了保存每一类实体, 在一个数据库中创建多个表.

1.2. 数据在数据库中的存储方式

id=1 id=2

name=Tom name=Jerry

age=18 age=20

列(column) 列(column) 列(column)

id	name	age
1	Tom	18
2	Jerry	20

行(row)

每一行(一条记录）对应Java中的一个对象

每一列对应对象的一个属性

2. 操作库

创建库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [参数[ 参数] [ 参数]...];

参数:

CHARACTERSET 码表名

COLLATE校对规则名, 详见文档10.10.1

显示库

SHOW DATABASES;

显示数据库创建语句

SHOW CREATE DATABASE 数据库名;

修改库

ALTER DATABASE 数据库名[ 参数[ 参数][ 参数]...];

注意: 不能改数据库的名字

删除库

DROP DATABASE [IF EXISTS] 数据库名;

练习:

创建一个数据库db1

创建一个数据库db2指定字符编码, 校对规则

create database db2 character set utf8 collate utf8_bin;

显示所有数据库

显示db2的字符编码

将db2的字符编码设置为gbk alter database db2 character set gbk;

删除db2数据库drop database db3;

3. 操作表

操作表之前使用需要先确定使用哪个数据库

USE 数据库名

创建表

CREATE TABLE 表名(列名类型[,列名类型][,列名类型]...);

具体类型说明可参见MySQL文档第11章

查看所有表

SHOW TABLES;

查看表的创建语句

SHOW CREATE TABLE 表名;

显示表结构

DESC 表名;

修改表名

RENAME TABLE 原表名 TO 新表名;

修改字符集

ALTER TABLE 表名 CHARACTER SET 字符集名;

删除表

DROP TABLE 表名;

练习

创建表employee, 包含以下字段:

id 整型主键, 自动生成

name 字符非空

gender 字符或bit 非空

birthday 日期

entry_date 日期非空

position 字符

salary 小数

resume 大文本

将表名改为user

显示表结构

4. 操作列

追加列

ALTERTABLE 表名 ADD 列名类型[,列名类型][,列名类型]...;

修改列类型

ALTER TABLE 表名 MODIFY 列名类型[,列名类型][,列名类型]...;

修改列

ALTER TABLE 表名 CHANGE COLUMN 原列名新列名类型;

删除列

ALTER TABLE 表名 DROP 列名;

练习

添加一列用来存储照片

将name长度修改为30 alter table user modifyname varchar(30) not null;

将name改名为username

删除photo列

5. 操作数据(增删改)

5.1. 插入

语法:

INSERT INTO 表名[(列名[,列名]...)]VALUES(值[,值]...);

注意事项:

插入值类型必须与对应列的数据类型一致

数据不能超出长度

插入值得为之必须与列名顺序一致

字符和日期数据要放在单引号中

插入空值使用null

如果不指定插入哪一列, 就是插入所有列

中文数据

由于默认码表是utf8, 而cmd.exe的码表是gbk, 在插入中文数据的时候会报错, 所以我们需要修改客户端码表

先查看系统变量: SHOWVARIABLES LIKE 'character%';

修改客户端码表: SETcharacter_set_client=gbk;

这样就解决了中文插入的问题, 但在查询数据的时候仍然显示为乱码, 这是因为mysql向cmd传输数据的时候使用的是utf8

修改输出数据的码表: SETcharacter_set_results=gbk;

练习

向user表中插入3条包含中文的数据

insert into user(id,username,gender,birthday,position,salary,resume)

values(1,'张三','男','1990-9-9','程序员',6000,'介绍');

insert intouser(id,username,gender,birthday,position,salary,resume)

values(2,'李四','男','1990-9-9','程序员',6000,'介绍');

insert intouser(id,username,gender,birthday,position,salary,resume)

values(3,'王五','男','1990-9-9','测试',6000,'介绍');

insert intouser(id,username,gender,birthday,position,salary,resume)

values(4,'赵六','男','1990-9-9','美工 ',6000,'介绍');

5.2. 修改

语法

UPDATE 表名 SET 列名=值[,列名=值]...[WHERE条件语句];

注意事项

WHERE子句选择满足条件的行进行更新, 如果不写, 则更新所有行

练习

将所有员工的薪水改为5000

将姓名为张三的员工薪水改为7000

给李四加薪3000, 并且职位改为经理

5.3. 删除

语法

DELETE FROM 表名 [where 条件语句]

注意事项

如果不加where子句, 将删除表中所有记录

delete只能用作删除行, 不能删除某一列的值, 需要用update

在delete和update的时候需要注意表与表之间的关联关系

删除表中所有数据可以使用: TRANCATE 表名, 这种方式会删除旧表重新创建, 在数据较多的时候使用

练习

删除所有美工

删除表中所有数据

6. 备份恢复数据库

备份数据库

输入quit退出mysql, 在cmd.exe中输入:

mysqldump –u用户名 –p密码数据库名 > 文件名

恢复数据库

进入mysql.exe之后, 使用数据库之后

source 文件名

练习:

备份指定数据库, 删除数据库, 恢复

7. 操作数据(查询)

7.1. DISTINCT

语法

SELECT [DISTINCT] 列名[, 列名]...FROM 表名

注意事项

*可以替代列名, 表示所有列, 但是通常我们为了提高代码的可读性, 不使用*

DISTINCT为过滤重复记录

如果DISTINCT后面跟多列, 是过滤掉多列合并之后的重复

练习

查询employee表中所有记录

select id,username,gender,birthday,position,salary,resumefrom user;

查询employee表中所有人的薪水

select username,salary from user;

查询employee表中包含哪些岗位

7.2. 列名表达式

语法

SELECT 列名|表达式[,列名|表达式]... FROM 表名

注意事项

表达式只是显示时起作用, 不会改变数据库中的值

练习

导入student.sql

source .....

查询所有学生的总分

selectname,chinese+math+english from student;

查询employee表, 将所有员工薪水*2显示

selectusername,salary*2 from user;

7.3. AS

SELECT 列名 AS 别名 FROM 表名

注意事项

起别名时AS可以省略

不会改变数据库中的值

练习

查询所有学生总分, 别名为总分

selectname '姓名',chinese+math+english'总分' fromstudent;

7.4. WHERE

语法

SELECT 列名 FROM 表名 [WHERE 条件语句]

WHERE子句中的运算符

比较运算符	>, <, >=, <=, =, <>, <=>	注意不等于和Java中不同, 是<>
	BETWEEN ... AND ...	某一区间内的值, 从 ... 到 ...
	IN(列表)	在列表之中, 例: in(1,2,3) 代表1或2或3
	LIKE(表达式)	模糊查询, %代表多个字符, _代表单个字符
	IS NULL	判断是否为NULL
逻辑运算符	AND &&	与, 两边都为TRUE结果为TRUE
	OR \|\|	或, 一边为TRUE结果就为TRUE
	NOT !	非, 将表达式结果取反

练习

查询英语分数在80-90分之间的

selectname,english from student where english>=80 and english<=90;

查询语文分数为81,82,83的学生

select name,english from student where english in(80,90,82);

查询所有姓张的学生的成绩

select name,english,math,chinese from student where name like '张%';

查询除了姓张和姓李的学生总分

selectname,english,math,chinese

from student

where name notlike '张%'

and name notlike '李%';

selectname,english,math,chinese

from student

where name like'张%'

or name like '李%';

7.5. ORDER BY

语法

SELECT 列名 FROM 表名 ORDER BY 列名ASC|DESC;

注意事项

ORDERBY 指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名

ASC为升序, DESC为降序

ORDERBY应在查询语句的结尾

练习

对数学成绩排序后输出

selectname,math from student order by math;

查询总分, 从高到低显示

selectname '姓名',chinese+math+english'总分' fromstudent order by 总分 desc;

选择所有姓张的学生的英语成绩, 并从高到低排序

selectname,english from student where name like '张%' order by english desc;

查询学生成绩, 按照语文从高到低排序, 如果语文相同, 按照英语从高到低排序

select* from student order by chinese desc,english desc;

7.6. COUNT函数

语法

SELECT COUNT(*)|COUNT(列名) from 表名[WHERE 条件语句]

注意事项

COUNT(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

COUNT(*)则是统计表中有多少条数据

COUNT(DISTINCT列名) 统计不重复的记录数

如果加上WHERE子句, 则是统计满足条件的记录

练习

统计student表中有多少条记录

selectcount(*) from student;

统计学生语文成绩大于80的有多少人

selectcount(*) from student where chinese>80;

统计总分大于250的有多少人

selectcount(*) from student where english+math+chinese>250;

统计参加英语考试的有多少人

selectcount(english) from student;

等价于select count(*) from student where english is not null;

7.7. SUM函数

语法

SELECT SUM(列名) FROM 表名 [WHERE 条件语句];

注意事项

计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

练习

计算所有学生的数学成绩总和

selectsum(math) from student;

显示所有学生的语文成绩总和, 数学成绩总和, 英语成绩总和

selectsum(chinese),sum(math),sum(english) from student;

计算所有学生的分数总和

selectsum(chinese)+sum(math)+sum(english) from student;

统计英语平均分

selectsum(english)/count(*) from student;

selectsum(english)/count(english) from student;

7.8. AVG函数

语法

SELECT AVG(列名) FROM 表名 [WHERE 条件语句];

注意事项

计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

AVG()统计平均数不包含NULL值

练习

计算英语平均分

selectavg(english) from student;

计算总分平均分, MySQL不支持组函数嵌套使用.

selectsum(english+math+chinese)/count(*) from student;

7.9. MAX / MIN函数

语法

SELECT MAX(列名) FROM 表名 [WHERE 条件语句];

SELECT MIN(列名) FROM 表名 [WHERE 条件语句];

注意事项

获取指定列最高/最低值, NULL不参与统计

练习

统计总分最高分和最低分

selectmax(english+math+chinese),min(english+math+chinese) from student;

7.10. GROUP BY(重点)

语法

SELECT 列名 FROM 表名 GROUP BY 列名[HAVING 条件语句]

注意事项

按照某列归类

HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数

SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名

HAVING中可以使用组函数, WHERE不能.

练习

导入order.sql

对订单表归类, 显示购买过哪些商品

selectproduct from orders group by product;

selectdistinct product from orders;

对订单表归类, 显示购买过哪些商品, 并显示每种购买了几个, 以及总价

selectproduct,count(product),sum(price) from orders group by product;

查询总价大于5000的商品有哪几类

selectproduct,count(product),sum(price) sum_price from orders group by product havingsum_price>5000;

8. 函数

8.1. 时间函数

注意date,datetime, timestamp之间的区别

ADDTIME(原时间, 增加值) 在某个时间上增加一段时间

selectaddtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0');

CURRENT_DATE() 当前日期

selectcurrent_date();

CURRENT_TIME() 当前时间

selectcurrent_time();

CURRENT_TIMESTAMP() 当前时间戳

selectcurrent_timestamp();

DATE(时间) 返回制定时间的日期部分

selectdate('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL增加值类型) 在指定日期上对某个字段增加

selectdate_add('2011-02-14 23:00:00', interval 10 month);

DATE_SUB(日期,INTERVAL减少值类型) 在指定日期上对某个字段减少

select date_sub('2011-02-14 23:00:00',interval 1 year);

DATEDIFF(日期1, 日期2) 计算两个日期之间的差值

selectdatediff('2000-02-14', '2001-02-14');

NOW() 当前时间

selectnow();

selectyear('2011-02-14 23:00:00');

selecthour('2011-02-14 23:00:00');

8.2. 字符串函数

CHARSET(字符串) 返回字符串字符集

selectcharset(name) from student;

CONCAT(字符串1[,字符串2]... ) 连接字符串

selectconcat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串) 查找子字符串出现位置, 注意序号从1开始

selectinstr('abc', 'a');

UCASE(字符串) 将字符串转为大写

selectucase('aBc');

LCASE(字符串) 将字符串转为小写

selectlcase('aBc');

LEFT(字符串, 长度) 从字符串左边取指定长度个字符

selectleft('aBc',2);

LENGTH(字符串) 计算字符串长度

selectlength('aBc');

REPLACE(字符串, 搜索字符串,替换字符串) 将字符串中指定字符串替换为其他字符串

selectreplace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2) 逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值???

selectstrcmp('abcc', 'abde');

selectstrcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[,个数]) 从字符串中截取

selectsubstring('abcdef', 3);

selectsubstring('abcdef', 3, 2);

LTRIM(字符串) 去掉左边空白

selectltrim(' abc ');

selectconcat('--', ltrim(' abc '), '--');

RTRIM(字符串) 去掉右边空白

selectconcat('--', rtrim(' abc '), '--');

TRIM(字符串) 去掉左右两边空白

selectconcat('--', trim(' abc '), '--');

8.3. 数学函数

ABS(数字) 求绝对值

selectabs(10);

selectabs(-10);

BIN(十进制数) 将十进制转换为二进制

selectbin(5);

HEX(十进制数) 将十进制转换为十六进制

selecthex(10);

CONV(数字, 原进制, 目标进制) 转换进制

selectconv(12, 10, 16);

selectconv(12, 10, 2);

selectconv(12, 16, 2);

CEILING(小数) 向上取整

selectceiling(3.4);

FLOOR(小数) 向下取整

selectfloor(3.4);

ROUND(小数) 四舍五入

selectround(3.4);

select round(3.5);

FORMAT(小数, 保留位数) 保留小数位

selectformat(3.1415926, 2);

LEAST(值,值[,值]...) 取最小值

selectleast(1,2,3,4);

selectleast('a', 'b', 'c', 'd');

GREATEST(值,值[,值]...) 取最大值

selectgreatest(1,2,3,4);

selectgreatest('a', 'b', 'c', 'd');

MOD(数字, 数字) 取余

selectmod(3,2);

select3%2;

RAND() 生成随机数, 14位小数, 0 <= n <= 1

selectrand();

9. 表的约束

约束的作用

限定某一列上的数据, 阻止非法数据录入, 提高程序健壮性.

9.1. 唯一约束 unique

unique约束的字段在整张表中唯一, 不可重复, 不包括多个NULL

创建表时设置唯一

create table test (

idint,

name varchar(20) unique

);

删除唯一约束

show create table test; 发现唯一索引名叫name

alter table test dropindex name;

添加唯一约束

alter table test changename name varchar(20) unique;

9.2. 非空约束 not null

not null约束的字段不能为空

创建表时设置非空

create table test1 (

idint,

name varchar(20) not null

);

删除非空约束

alter table test1 changename name varchar(20);

添加非空约束

alter table test1 changename name varchar(20) not null;

9.3. 主键约束 primary key

通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.

primary key用来标识一个字段, 这个字段是非空且唯一的.

创建表时设置主键

create table test2(

id int primary key,

name varchar(20)

);

删除主键

alter table test2 dropprimary key;

在制定列上添加主键

alter table test2 changeid id int primary key;

alter table test2 addprimary key(id);

设置主键自动增长

create table test3(

id int primary key auto_increment,

name varchar(20)

);

删除自增长

alter table test3 changeid id int;

设置自增长

alter table test3 changeid id int auto_increment;

UUID主键

128位的2进制, 32位16进制加上4个-

java.util.UUID.randomUUID().toString()

3c2372a4-da2a-4470-b17a-f2e50ac79636

9.4. 外键约束 foreign key

foreignkey约束某一列的值是参照另外一列

创建表时添加外键约束

create tablehusband(

id int primary key,

name varchar(20) not null

);

create tablewife(

id int primary key,

name varchar(20) not null,

husband_id int,

constrainthusband_id_fk foreign key(husband_id) references husband(id)

);

wife表的husband_id的值必须是husband表中的id

create tablewife(id int primary key,name varchar(20) not null, husband_id int referenceshusband(id));

被外键引用的记录不能删除, 如果想要删除某条被引用的记录, 需要找到引用这条记录的记录, 解除关联

被外键引用的表不能删除, 如果想要删除被引用的表, 需要删除所有引用此表的外键

删除外键约束

alter table wife dropforeign key husband_id_fk;

添加外键约束

alter table wife add constraint husband_id_fkforeign key(husband_id) references husband(id)

10. 表的设计

10.1. 单独的实体

user

id	name	age
1	张三	18
2	李四	20
3	王五	19

10.2. 一对多、多对一

foreign key

department employee

id	name
1	开发部
2	市场部

id	name	department_id
1	张三	1
2	李四	1
3	王五	2

多的一方设置外键

create table department(

idint primary key auto_increment,

namevarchar(20)

);

create table employee(

idint primary key auto_increment,

namevarchar(20),

department_idint,

constraintdepartment_id_fk foreign key(department_id) references department(id)

);

insert into department(name) values('开发部');

insert into department(name) values('市场部');

insert into employee(name, department_id)values('张三',1);

insert into employee(name, department_id)values('李四',1);

insert into employee(name, department_id)values('王五',2);

10.3. 一对一

unique

husband wife

id	name
1	张三
2	李四

id	name	husband_id
1	冰冰	2
2	志玲	1

独立外键, 没有依赖关系, 两个表的对象都可以独立存在.

create table husband(

idint primary key auto_increment,

namevarchar(20)

);

create table wife(

idint primary key auto_increment,

namevarchar(20),

husband_idint,

constrainthusband_id_fk foreign key(husband_id) references husband(id)

);

insert into husband(name) values('张三');

insert into husband(name) values('李四');

insert into wife(name, husband_id) values('冰冰',2);

insert into wife(name, husband_id) values('志玲',1);

foreign key

person idcard

id	name
1	张三
2	李四

id	num
2	110123199009091234
1	120123200001011234

主键即外键, 分为主表和从表, 从表依赖于主表, 从表中的对象不能单独存在.

注意从表的主键, 不能自动增长.

create table person (

idint primary key auto_increment,

namevarchar(20)

);

create table idcard (

idint primary key,

numvarchar(20),

constraintid_fk foreign key(id) references person(id)

);

insert into person(name) values('张三');

insert into person(name) values('李四');

insert into idcard(id,num) values(2,'110123199009091234');

insert into idcard(num,id) values('110123199009091234',1);

10.4. 多对多

foreign key

student student_teacher teacher

id	name
1	张三
2	李四
3	王五

student_id	teacher_id
1	1
2	1
2	2
3	2

id	name
1	张孝祥
2	黎活明

用一张关系表保存多对多的关系,有两列分别引用两张表的主键,并且这两列组合起来成为联合主键

create table student (

idint primary key auto_increment,

namevarchar(20)

);

create table teacher (

idint primary key auto_increment,

namevarchar(20)

);

create table student_teacher(

student_idint,

teacher_id int,

primary key(student_id,teacher_id),

constraint student_id_fkforeign key(student_id) references student(id),

constraint teacher_id_fkforeign key(teacher_id) references teacher(id)

);

insert into student(name) values('张三');

insert into student(name) values('李四');

insert into student(name) values('王五');

insert into teacher(name) values('zxx');

insert into teacher(name) values('lhm');

insert into student_teacher values(1,1);

insert into student_teacher values(2,1);

insert into student_teacher values(2,2);

insert into student_teacher values(3,2);

11. 多表查询

11.1. 连接查询

当我们要插叙的数据不只是在一张表中, 我们就需要使用多表连接查询.

例如: 查询员工所在的部门名称, 查询部门中员工名称, 都需要查询两张表.

注意:

在多表连接查询的时候, 如果没有有效的连接条件, 所有表中的行会互相连接, 形成笛卡尔集.

为了避免笛卡尔集, 可以再where后加入有效的连接条件

select … from table1, table2 wheretable1.xx=table2.xx

练习:

查询出公司所有员工姓名, 所在部门名

查询出开发部所有员工名

查询出张三所在部门名称

11.2. 多表连接

多张表连接查询, 一张表外键引用另外一张表, 另外一张表再引用其他表.

例如: 员工表引用部门, 部门表引用城市表. 这时如果想根据员工查城市, 或者根据城市查员工就需要将三张表连接查询

准备工作:

创建城市表, id主键自动生成, 带有名称

插入两条记录, 北京和上海

在部门表添加city_id,外键引用城市表的id

将开发部的地址改为北京, 市场部的地址改为上海

练习:

查询所有员工, 员工所属部门以及部门所在城市

查询北京的所有员工

11.3. 自连接

自己和自己连接, 当前表的外键引用自己的主键.

例如: 员工的经理也是员工, 应该在员工表中添加一列经理id. 之后添加一个外键, 引用员工表的主键.

准备工作:

在员工表中添加manager_id,外键引用员工表id

插入赵六, 孙七. 分别属于开发部和市场部.

将张三和李四的经理设置为赵六,王五经理设置为孙七.

练习:

查询王五的经理姓名

查询赵六手下的员工名

11.4. 内连接、左外连接、右外连接(SQL99)

准备工作:

插入记录周八, 部门为空

插入部门财务部

1. 内连接

之前我们使用逗号和where子句进行的连接就是内连接. 标准语法应使用 inner join 和 on, 例如:

select e.name,d.name from employee e,departmentd where e.department_id=d.id;

select e.name,d.name from employee e innerjoin department d on e.department_id=d.id;

内连接会将两张表完全匹配连接条件的记录查询出来, 不满足的不会显示

2. 左外连接

使用 left outerjoin 和 on 关键字进行连接查询, 这时左表中不满足条件的记录也会被查询出来

例如: 查询所有员工的部门, 要将没有部门的周八查询出来. 这时需要查询出员工表中所有记录, 即使不满足连接条件, 周八也要显示.

3. 右外连接

使用 right outerjoin 和 on 关键字进行连接查询, 这时右表中不满足条件的记录也会被查询出来

例如: 查询每个部门下都有哪些员工, 要将没有员工的财务部显示出来.

4. 全外连接

MySql不支持全外连接full outer join, 可以使用union distinct来实现.