MySQL——Java数据库编程技术

码农然 2019-11-19

200

数据库系统是用来管理数据的，建立的数理逻辑和集合操作基础上的。具有高效、可靠、完整、自同步等特性，是业务系统进行数据控制的最佳选择。数据库系统一般提供高效的数据控制和数据检索功能，采用SQL语言来进行数据操作。

应用程序

作用:响应操作并显示结果、向数据库请求数据

要求:美观、操作简单方便。

数据库

作用:存储数据、检索数据、生成新的数据。

要求:统一、安全、性能等。

一、数据库的基本概念：

数据表：

在关系型数据库中，数据表是一系列二维数据的集合，用来存储数据和操作数据的逻辑结构。它由纵向的列和横向的行组成，行被称之为记录，是组织数据的单位，列被称之为字段，每一列表示一条记录的某一个属性，都有相应的描述信息，如数据类型、数据长度等。

数据类型：

数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。常用的数据类型有:整数数据类型、浮点数据类型、精确小数类型、二进制数据类型、日期/时间数据类型、字符串数据类型等，表中的每一个字段必须指定某种数据类型，例:上图中的姓名字段为字符串类型。

主键：

主键(Primary Key:简称PK)，又称主码，用于唯一地标识表中的每一条记录。可以定义表中的一列或多列为主键，主键列上不能存在两行相同的值，也不能为空值。例如:上图中的编号为主键

二、MySQL数据库：

第一步:启动服务（在修改MySQL的配置后需重启服务）

使用net start mysql（dos命令）

第二步:配置MySQL的环境变量(略)

第三步:使用 mysql [-h host] -u root -p 连接MySQL

-h host：host代表要连接的主机地址可以省略

-u root：root为MySQL中的管理员用户名

-p ：root用户对应的密码

#创建MySQL用户cmy
CREATE USER ‘cmy’@’%’ IDENTIFIED BY‘123’;
#授予cmy用户全部权限
GRANT ALL ON *.* TO ‘cmy’@’%’;
#刷新权限 立即生效
FLUSH PRIVILEGES;
#查看表中的详细属性
DESC student;    DESCRIBE student;  
#查看数据库的定义      
SHOW CREATE DATABASE school;  
#查看数据表的定义       
SHOW CREATE TABLE student;

在 MySQL 中常见的数据类型如下：

1) 整数类型

包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型 FLOAT 和 DOUBLE，定点数类型 DECIMAL。

2) 日期/时间类型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

3) 字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

4) 二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

三、SQL常用语句：

SQL语言，包含以下四部分(重点、难点):

(1) 数据定义语言DDL：DROP CREATE ALTER等语句

--增--
1) CREATE DATABASE school;    #新建school数据库
2) CREATE TABLE student( );   #新建student数据表
3) CREATE TABLE studentCopy(SELECT stuId,stuName,stuAge,stuSex FROM student);  #复制student数据表


--删--
1) DROP TABLE student;            #删除表
2) DROP TABLE IF EXISTS abc;      #删除表如果存在abc表


--改--
1) ALTER TABLE `student` ADD CONSTRAINT UNIQUE `user`(`stuName`); #为student表的stuName列设置唯一约束
2) ALTER TABLE `student` RENAME `student_new`;                    #为数据表重命名
3) ALTER TABLE `student` ALTER `stuName` SET DEFAULT '张三';      #为name列添加默认约束  
4) ALTER TABLE `student` MODIFY `stuName` VARCHAR(50);            #将stuName的长度进行更改
5) ALTER TABLE `student` CHANGE `stuName` `stuNames` VARCHAR(50); #将stuName改成stuNames

(2) 数据操作语言（表） DML：INSERT(插入)UPDATE（修改） DELETE(删除)

--插入--
1) INSERT INTO student VALUES( DEFAULT, ‘张三’ , 22 , ‘男’ , ‘2010-09-09’ );    #为表属性赋值
2) INSERT INTO student (stuId, stuName , stuAge , stuSex) VALUES( DEFAULT , ‘张三’ , 22 , ‘男’ , ‘2010-09-09’ );
3) INSERT INTO student VALUES( DEFAULT, ‘王五’,24 , ‘女’ , now( ) ), ( DEFAULT, ‘赵六’,26 , ‘男’ ,NULL );


--修改--
1) UPDATE student SET stuAge=23 [WHERE stuId=1];      #将主键stuId=1的列中的stuAge值改为23
2) UPDATE student SET stuAge=23,stuSex=’女’ WHERE stuId=2;
3) UPDATE student SET stuAge=23 = stuAge + 1, WHERE stuId=3;
4) UPDATE student SET stuBirthday= ADDDATE(stuBirthday, INTERVAL 2 DAY) WHERE EXISTS ( SELECT * FROM score WHERE course = ‘Java’ );    #将查询出来的学生生日加两天


--删除--
1) DELETE FROM student [WHERE stuId=3];    #删除stuId=3的值

(3) 数据库查询语言DQL：SELECT 语句

1) SELECT * FROM student;              #查询student表中的信息
2) SELECT stuName,stuAge FROM student;      #查询student表中姓名年龄的信息
3) SELECT stuName AS 姓名FROM student;      #带别名的查询
4) SELECT * FROM student WHERE stuSex=‘男’;    #查询student表中男生信息
5) SELECT * FROM student WHERE stuSex!=‘男’;    #查询student表中非男生（女生）的信息
6) SELECT * FROM student WHERE stuBirthday IS NULL;  #查询没有添加生日信息的数据
7) SELECT *,’第一中学’ schoolName FROM student;    #添加”第一中学”表头为schoolName的常量列
8) SELECT * FROM student ORDER BY stuAge DESC LIMIT 3  #查看倒序排列的前三条数据
9) SELECT * FROM student ORDER BY stuId LIMIT 2,2  #查看起始下标2的两条数据

聚合函数：count( ), avg( ), max( ), min( ), sum( )

字符串函数：concat(str, str2…) #连接函数str+str2

insert(str, pos, len, newstr) , lower(str) , upper(str)

substring(str, pos, len); # 截取字符串

日期函数：curDate(), curTime( ), now( ), week(date) , year(date) ,

当前日期当前时间时间日期摘取星期摘取年

dateDiff(date1,date2) , addDate(date,n);

两个时间的时间差天数日期修改

数学函数：ceil(x) ,floor(x) , rand( );

向下取整向上取整随机数

1) SELECT COUNT(*) FROM student
2) SELECT CONCAT(stuName, ‘同学’) FROM student

(4) 数据库控制语言DCL：GRANT(授权) REVOKE(除权) COMMIT(提交事务) ROLLBACK(操作回滚)

创建一组完整的表的例子：

CREATE DATABASE YRtest;
USE YRtest;


CREATE TABLE student(
sno INT(3) PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(5) NOT NULL COMMENT '姓名',
ssex CHAR(2) DEFAULT '男' NOT NULL COMMENT '性别',
sbirthday DATETIME  COMMENT '出生年月',
class VARCHAR(5) COMMENT '班级编号'
)COMMENT '学生信息表';


INSERT INTO student(sname,ssex,sbirthday,class) VALUE('李军','男','1976-02-20 00:00:00',95000);
INSERT INTO student(sname,ssex,sbirthday,class) VALUE('陆军','男','1974-06-03 00:00:00',95031);
INSERT INTO student(sname,ssex,sbirthday,class) VALUE('匡明','男','1975-10-02 00:00:00',95031);
INSERT INTO student(sname,ssex,sbirthday,class) VALUE('王丽','女','1976-01-23 00:00:00',95033);
INSERT INTO student(sname,ssex,sbirthday,class) VALUE('曾华','男','1977-09-01 00:00:00',95033);
INSERT INTO student(sname,ssex,sbirthday,class) VALUE('王芳','女','1975-02-10 00:00:00',95031);


CREATE TABLE teacher(
tno INT(3) PRIMARY KEY AUTO_INCREMENT COMMENT '教师编号',
tname VARCHAR(5) NOT NULL COMMENT '姓名',
tsex CHAR(2) DEFAULT '男' NOT NULL COMMENT '性别',
tbirthday DATETIME COMMENT '生日',
prof VARCHAR(5) COMMENT '职称',
depart VARCHAR(10) COMMENT '所在部门' 
)COMMENT '教师信息表';


INSERT INTO teacher(tname,tsex,tbirthday,prof,depart) VALUE('李诚','男','1958-12-02 00:00:00','副教授','计算机系');
INSERT INTO teacher(tname,tsex,tbirthday,prof,depart) VALUE('王萍','女','1972-05-05 00:00:00','助教','计算机系');
INSERT INTO teacher(tname,tsex,tbirthday,prof,depart) VALUE('刘冰','女','1972-08-14 00:00:00','助教','电子工程');
INSERT INTO teacher(tname,tsex,tbirthday,prof,depart) VALUE('张旭','男','1969-03-12 00:00:00','讲师','电子工程');


CREATE TABLE course(
cno VARCHAR(10) PRIMARY KEY COMMENT '课程编号' ,
cname VARCHAR(5) NOT NULL COMMENT '名称' ,
tno INT(3) COMMENT '教师编号' ,
CONSTRAINT FK_course_teacher FOREIGN KEY(`tno`) REFERENCES teacher(`tno`)
)COMMENT '课程信息表';


INSERT INTO course(cno,cname,tno) VALUE('3-105','计算机导论','825');
INSERT INTO course(cno,cname,tno) VALUE('3-245','操作系统','804');
INSERT INTO course(cno,cname,tno) VALUE('6-166','数字电路','856');
INSERT INTO course(cno,cname,tno) VALUE('9-888','高等数学','831');


CREATE TABLE score(
sno INT(3) COMMENT '学生编号',
cno VARCHAR(10) COMMENT '课程编号',
degree INT(3) NOT NULL COMMENT '成绩',
CONSTRAINT FK_score_student FOREIGN KEY(`sno`) REFERENCES student(`sno`),
CONSTRAINT FK_score_course FOREIGN KEY(`cno`) REFERENCES course(`cno`)
)COMMENT '学生成绩表';


INSERT INTO score(sno,cno,degree) VALUE('103','3-245','86');
INSERT INTO score(sno,cno,degree) VALUE('105','3-245','75');
INSERT INTO score(sno,cno,degree) VALUE('109','3-245','68');