| 狂神说笔记 | 您所在的位置：网站首页 › mysql字段默认值使用null还空字符串 › 狂神说笔记 |
狂神说笔记
|
MySQL快速入门
1.初识MySQL
JavaEE：企业级Java开发、Web 前端（页面：展示——数据）；后端（连接点：连接数据库JDBC，连接前端——控制视图跳转和给前端传递数据）；数据库（存数据，Txt，Excel，world）。程序员等级： 只会写代码，没学好数据库，基本混饭吃。操作系统，数据结构预算法！当一个不错的程序员！离散数学、数字电路、体系结构、编译原理。+实战经验=高级程序/优秀的程序员。 1.为什么要学习MySQL 岗位需求；现在的世界，大数据时代，得数据库者得天下。被迫需求：存数据；数据库是所有软件体系中最核心的存在 DBA。 2.什么是数据库 数据库（DB 、DataBase）概念：数据仓库，软件，安装在操作系统（windows，linux，mac，…）之上！SQL，可以存储大量的数据。500万！作用：存储数据、管理数据。 3.数据库分类关系型数据库：（SQL） MySQL，Oracle、Sql server ， DB2，SQLlite通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表…非关系型数据库（NoSQL）not only Redis、MongDB非关系型数据库，对象存储，通过对象的自身属性来决定。DBMS（数据库管理系统） 数据库管理系统 ( DataBase Management System )数据库管理软件 , 科学组织和存储数据 , 高效地获取和维护数据
为什么要说这个呢? 因为我们要学习的MySQL应该算是一个数据库管理系统. 4.MySQL简介与安装
概念 : 是现在流行的开源的,免费的 关系型数据库； 历史 : 由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。 特点 : 免费 , 开源数据库；小巧 , 功能齐全；使用便捷；可运行于Windows或Linux操作系统；适用于中小型甚至大型网站应用。安装MySQL 建议使用压缩版,安装快,方便.不复杂。不建议使用exe文件，不易卸载干净！！软件下载，mysql5.7 64位下载地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip 电脑是64位的就下载使用64位版本的！ 安装步骤 下载后得到zip压缩包。 解压到自己想要安装到的目录，本人解压到的是"F:\java\MySQL" 添加环境变量： 此电脑->属性->高级->环境变量；选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹；在F:\java\MySQL目录下新建my.ini文件；编辑 my.ini 文件 ,注意替换路径位置； [mysqld] basedir=F:\java\MySQL datadir=F:\java\MySQL\data\ port=3306 skip-grant-tables 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)；再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件；然后输入命令net start mysql再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）；
注：出现ERROR 29 (HY000): File './mysql/user.MYD' not found (Errcode: 2 - No such file or directory)问题。 MySQL文件在C盘未卸载干净。需检查C盘，删除MySQL相关文件夹。 5.SQLyog安装可手动操作,管理MySQL数据库的软件工具。 特点 : 简洁 , 易用 , 图形化。 下载地址：地址①；地址②（提取码: 8fqx）； 无脑安装，直接下一步；注册软件；连接数据库，运行如下图：
每一个 sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看。 新建一张表
打开MySQL命令窗口 在DOS命令行窗口进入 安装目录\mysql\bin如果之前设置了环境变量，可以在任意目录打开！连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 ! 注：用键盘上的“向上的箭头”按一下，就可以直接复制上一行的dos命令。 基本的数据库操作命令： mysql -uroot -proot -- 连接数据库 update user set password=password('123456')where user='root'; -- 修改密码 flush privileges; -- 刷新权限 show databases; -- 显示所有数据库 use school; -- 选择school数据库 show tables; -- 显示数据库中所有的表 describe student; -- 显示表mysql数据库中student表的列信息 create database book; -- 创建数据库book use book; -- 选择book数据库 exit; -- 退出Mysql ? 命令关键词 : 寻求帮助 -- 表示单行注释 /* SQL的多行注释 */ 2.数据库操作结构化查询语句分类 名称解释命令数据定义语言(DDL)定义和管理数据对象，如数据库、数据表等CRATE、DROP、ALTER数据操作语言(DML)用于操作数据库对象中所包含的数据INSERT、VPDATE、DELETE数据查询语言(DQL)用于查询数据库数据SELECT数据控制语言(DCL)用于管理数据的语言，包括权限及数据更改GRANT、COMMIT、ROLLBACK 1.操作数据库命令行操作数据库 创建数据库 : create database [if not exists] 数据库名;删除数据库 : drop database [if exists] 数据库名;查看数据库 : show databases;使用数据库 : use 数据库名;看到这种语句中有中括号的，都是可写可不写的。 CREATE DATABASE westos; DROP DATABASE westos; SHOW DATABASES; -- tab键上面，如果你的表名或字段名是一个特殊字符，就需要带 `` USE book; 2.数据值和列类型参考于：简书 列类型： 规定数据库中该列存放的数据类型。数值类型
字符串类型
日期和时间型数值类型
NULL值 理解为 “没有值” 或 “未知值”；不要用NULL进行算术运算 , 结果仍为NULL；如何选择合适的数据类型 整数和浮点：如果你存的这一列没有小数，就选整数类型（货币选decimal）。日期类型：一般选DATETIME类型；以后可能会用到TIMESTAMP类型。但是后面这个的存储范围是小于前面这个的。char和varchar：char是固定长度的，varchar是可变长度的。如果存储的东西对速度要求很高而对空间要求比较小，就用char；反之则用varchar。 3.数据字段属性UnSigned 无符号的。声明该数据列不允许负数。ZEROFILL 0填充的；不足位数的用0来填充 , 如int(3),5则为005；Auto_InCrement 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)； 通常用于设置主键 , 且为整数类型； 可定义起始值和步长； 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表；SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)；
NULL 和 NOT NULL 默认为NULL , 即没有插入该列的数值；如果设置为NOT NULL , 则该列必须有值；DEFAULT 默认的；用于设置默认值；每一个表 ，都必须存在以下五个字段： id 主键；vorsion 乐观锁；is_delete 伪删除；gmt_create 创建时间；gmt_update 修改时间； 4.创建数据表 属于DDL的一种，语法 : create table [if not exists] `表名`( '字段名1' 列类型 [属性][索引][注释], '字段名2' 列类型 [属性][索引][注释], #... '字段名n' 列类型 [属性][索引][注释] )[表类型][表字符集][注释]; 说明 : 反引号用于区别MySQL保留字与普通字符而引入的 (键盘esc下面的键)。小括号之间创建的各个列之间有逗号，但是最后一个没有逗号。表明随便取；字段名也是随便取（可中文可英文）。 案例代码： CREATE TABLE `user`( `id` INT(10) NOT NULL COMMENT '学员ID', `name` VARCHAR(100) NOT NULL COMMENT '学员姓名', `age` INT(3) NOT NULL COMMENT '学员年龄', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值； -- 目标 : 创建一个school数据库 -- 创建学生表(列,字段) -- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email -- 创建表之前, 一定要先选择数据库 USE school; CREATE TABLE IF NOT EXISTS `student` ( `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别', `birthday` datetime DEFAULT NULL COMMENT '出生日期', `address` varchar(100) DEFAULT NULL COMMENT '地址', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 查看数据库的定义 SHOW CREATE DATABASE school; -- 查看数据表的定义 SHOW CREATE TABLE student; -- 显示表结构 DESC student; -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES'; 5.数据表的类型设置数据表的类型 CREATE TABLE 表名( -- 省略的代码 -- Mysql注释 -- 1. # 单行注释 -- 2. /*...*/ 多行注释 )ENGINE = MyISAM (or InnoDB) -- 查看mysql所支持的引擎类型 (表类型) SHOW ENGINES;
常见的 MyISAM 与 InnoDB 类型： 名称MyISAMInnoDB事务处理不支持支持数据行锁定不支持支持外键约束不支持支持全文索引支持不支持表空间大小较小较大，约2倍适用场合: 适用 MyISAM : 节约空间及相应速度；适用 InnoDB : 安全性 , 事务处理，多用户操作数据表；在物理空间存在的位置 MySQL数据表以文件方式存放在磁盘中； 包括表文件 , 数据文件 , 以及数据库的选项文件；位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表；注意 : * . frm – 表结构定义文件；* . MYD – 数据文件 ( data )；* . MYI – 索引文件 ( index )；InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件；MyISAM类型数据表对应三个文件：
设置数据表字符集 我们可为数据库,数据表,数据列设定不同的字符集，设定方法： 创建时通过命令来设置 , 如 : CREATE TABLE 表名() CHARSET = utf8;如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定。 6.修改数据库修改表 ( ALTER TABLE ) 修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名； 添加字段 : ALTER TABLE 表名 ADD 字段名 列属性[属性]； 修改字段 : ALTER TABLE 表名 MODIFY 字段名 列类型[属性]；ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]；删除字段 : ALTER TABLE 表名 DROP 字段名； USE school; CREATE TABLE `teacher`( `id` INT(10) NOT NULL COMMENT '教师ID', `name` VARCHAR(100) NOT NULL COMMENT '教师姓名', `age` INT(3) NOT NULL COMMENT '教师年龄', PRIMARY KEY (`id`) ) ENGINE=INNOBASE CHARSET=utf8 COLLATE=utf8_general_ci; -- 修改表名 :`ALTER TABLE 旧表名 RENAME AS 新表名；` ALTER TABLE teacher RENAME AS teacher1; -- 添加字段 : `ALTER TABLE 表名 ADD 字段名 列属性[属性]；` ALTER TABLE teacher1 ADD age INT(12); -- 修改字段 : `ALTER TABLE 表名 MODIFY 字段名 列类型[属性]；` ALTER TABLE teacher1 MODIFY age VARCHAR(12); -- 修改约束 ALTER TABLE teacher1 CHANGE age age1 INT(12); -- 字段重命名 -- 删除字段 : `ALTER TABLE 表名 DROP 字段名；` ALTER TABLE teacher1 DROP age1;删除数据表 语法：DROP TABLE [IF EXISTS] 表名； IF EXISTS 为可选 , 判断是否存在该数据表；如删除不存在的数据表会抛出错误；
注意点： 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！每个库目录存在一个保存当前数据库的选项文件db.opt。注释： 单行注释： # 注释内容多行注释 ：/* 注释内容 */单行注释： – 注释内容 (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）) 模式通配符： _ ： 任意单个字符% ： 任意多个字符，甚至包括零字符单引号需要进行转义 \’ CMD命令行内的语句结束符可以为 “;”, “\G”, “\g”，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。SQL对大小写不敏感 （关键字）清除已有语句：\c 3.MySQL数据管理 1.外键外键概念
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。 在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。 外键作用 保持数据一致性，完整性，主要目的是控制存储在外键表中的数据，约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。创建外键 方式一：建表时指定外键约束。 -- 创建外键的方式一 : 创建子表同时创建外键 -- 年级表 (id\年级名称) CREATE TABLE `grade` ( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号) CREATE TABLE `student2` ( `studentno` INT(4) NOT NULL COMMENT '学号', `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `sex` TINYINT(1) DEFAULT '1' COMMENT '性别', `gradeid` INT(10) DEFAULT NULL COMMENT '年级', `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机', `address` VARCHAR(255) DEFAULT NULL COMMENT '地址', `borndate` DATETIME DEFAULT NULL COMMENT '生日', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', PRIMARY KEY (`studentno`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; 当创建完子表之后，点开你创建的这个表，如果可以在你创建的这个表的indexes下面看到你创建的外键名，则创建外键就成功了。如下：
删除外键 操作：删除 grade 表，发现报错；
数据库意义 ： 数据存储、数据管理。 管理数据库数据方法： 通过SQLyog等管理工具管理数据库数据；通过DML语句管理数据库数据；DML语言 ：数据操作语言。 用于操作数据库对象中所包含的数据； 包括 : INSERT (添加数据语句)；UPDATE (更新数据语句)；DELETE (删除数据语句)； 3.添加数据INSERT命令 语法：INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3'); 注意 : 字段或值之间用英文逗号隔开；’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致；可同时插入多条数据 , values 后用英文逗号隔开； -- 插入语句（添加） -- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3') INSERT INTO grade(gradename) VALUES ('大一'); -- 主键自增,那能否省略呢? INSERT INTO grade VALUES ('大二'); -- 查询:INSERT INTO grade VALUE ('大二') -- 错误代码：1136 Column count doesn`t match value count at row 1 -- 一次插入多条数据 INSERT INTO grade(gradename) VALUES ('大三'),('大四'); -- 结论:'字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.
update命令 语法：UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition]; 注意 : column_name 为要更改的数据列；value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果；condition 为筛选条件 , 如不指定则修改该表的所有列数据； -- 案例 UPDATE `student` SET `name`='subei',`birthday`=CURRENT_DATE WHERE id = 5;测试： -- 修改年级名字，带了简介 UPDATE grade SET gradename = '高中' WHERE gradeid = 1; -- 不指定条件情况下，会改动所有的表 UPDATE `grade` SET `gradename`='果汁'; -- 修改多个属性 UPDATE `student` SET `name`='admin',`email`='[email protected]' WHERE id = 2;
where条件子句，可以简单理解为 : 有条件地从表中筛选数据。 运算符含义范例结果=等于5=6false< > 或 !=不等于5!=6true>大于5>6false=6false2falseOR或5>1 OR 1>2true 测试2： UPDATE `student` SET `name` = '高中' WHERE `id` =95 AND studentresult=95 AND studentresult80 AND subjectname='JAVA第一学年' ORDER BY StudentResult DESC LIMIT 0,10; 6.子查询什么是子查询? 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句;嵌套查询可由多个子查询组成,求解的方式是由里及外;子查询返回的结果一般都是集合,故而建议使用IN关键字; -- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列 -- 方法一:使用连接查询 SELECT studentno,r.subjectno,StudentResult FROM result r INNER JOIN `subject` sub ON r.`SubjectNo`=sub.`SubjectNo` WHERE subjectname = '数据库结构-1' ORDER BY studentresult DESC; -- 方法二:使用子查询(执行顺序:由里及外) SELECT studentno,subjectno,StudentResult FROM result WHERE subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1' ) ORDER BY studentresult DESC; -- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名 -- 方法一:使用连接查询 SELECT s.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo` WHERE subjectname = '高等数学-2' AND StudentResult>=80; -- 方法二:使用连接查询+子查询 -- 分数不小于80分的学生的学号和姓名 SELECT r.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo` WHERE StudentResult>=80; -- 在上面SQL基础上,添加需求:课程为 高等数学-2 SELECT r.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo` WHERE StudentResult>=80 AND subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2' ); -- 方法三:使用子查询 -- 分步写简单sql语句,然后将其嵌套起来 SELECT studentno,studentname FROM student WHERE studentno IN( SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2' ) ); 练习 /* 题目: 查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数) 使用子查询,查询郭靖同学所在的年级名称 */ -- 需求：查询C语言-1的前5名学生的成绩信息：学号、姓名、分数 -- 方式一：连接查询 SELECT s.studentno,studentname,studentresult FROM student AS s INNER JOIN result AS r ON s.StudentNo=r.StudentNo INNER JOIN `subject` AS su ON r.SubjectNo=su.subjectno WHERE subjectname='C语言-1' ORDER BY studentresult DESC LIMIT 0,5; -- 方式二：连接查询和子查询并用 SELECT s.studentno,studentname,studentresult FROM student AS s INNER JOIN result AS r ON s.StudentNo=r.StudentNo WHERE subjectno= (SELECT subjectno FROM `subject` WHERE subjectname='C语言-1') ORDER BY studentresult DESC LIMIT 0,5; -- 使用子查询：查询郭靖同学所在的年级名称 SELECT gradename FROM grade WHERE gradeid= (SELECT gradeid FROM student WHERE studentname='郭靖'); 7.分组和过滤 -- 查询不同课程的平均分,最高分,最低分 -- 前提:根据不同的课程进行分组 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分 FROM result AS r INNER JOIN `subject` AS s ON r.subjectno = s.subjectno GROUP BY r.subjectno HAVING 平均分>80; /* where写在group by前面. 要是放在分组后面的筛选 要使用HAVING.. 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的 */ 5.MySQL函数官网：链接 1.常用函数 数据函数 SELECT ABS(-8); -- 绝对值函数 SELECT CEILING(9.4); -- 向上取整/ SELECT FLOOR(9.4); -- 向下取整 SELECT RAND(); -- 随机数,返回一个0-1之间的随机数 SELECT SIGN(0); -- 符号函数: 负数返回-1,正数返回1,0返回0 字符串函数 SELECT CHAR_LENGTH('Java坚持就能成功'); -- 返回字符串包含的字符数 SELECT CONCAT('我','改','程序'); -- 合并字符串,参数可以有多个 SELECT INSERT('我在编程hello world',1,2,'为了咸鱼'); -- 替换字符串,从某个位置开始替换某个长度 SELECT LOWER('subeiLY'); -- 小写 SELECT UPPER('unremittingly'); -- 大写 SELECT LEFT('hello,world',5); -- 从左边截取 SELECT RIGHT('hello,world',5); -- 从右边截取 SELECT REPLACE('Java坚持就能成功','咸鱼','努力'); -- 替换字符串 SELECT SUBSTR('Java坚持就能成功',4,6); -- 截取字符串,开始和长度 SELECT REVERSE('Java坚持就能成功'); -- 反转 -- 查询姓郭的同学,改成邹 SELECT REPLACE(studentname,'郭','邹') AS 新名字 FROM student WHERE studentname LIKE '郭%'; 日期和时间函数 SELECT CURRENT_DATE(); -- 获取当前日期 SELECT CURDATE(); -- 获取当前日期 SELECT NOW(); -- 获取当前日期和时间 SELECT LOCALTIME(); -- 获取当前日期和时间 SELECT SYSDATE(); -- 获取当前日期和时间 -- 获取年月日,时分秒 SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW()); SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT SECOND(NOW()); 系统信息函数 SELECT VERSION(); -- 版本 SELECT USER(); -- 用户 2.聚合函数 函数名称描述COUNT()返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】SUM()返回数字字段或表达式列作统计，返回一列的总和。AVG()通常为数值字段或表达列作统计，返回一列的平均值MAX()可以为数值字段，字符字段或表达式列作统计，返回最大的值。MIN()可以为数值字段，字符字段或表达式列作统计，返回最小的值。从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。 count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。很多人认为count(1)执行的效率会比count(* )高，原因是count( * )会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。 两者之间的对比： 1）在表没有主键时，count(1)比count(*)快；2）有主键时，主键作为计算条件，count(主键)效率最高；3）若表格只有一个字段，则count(*)效率较高。 -- 聚合函数 -- COUNT:非空的 SELECT COUNT(studentname) FROM student; -- count(指定列 SELECT COUNT(*) FROM student; -- count(*) SELECT COUNT(1) FROM student; -- count(1) 推荐 SELECT SUM(StudentResult) AS 总和 FROM result; SELECT AVG(StudentResult) AS 平均分 FROM result; SELECT MAX(StudentResult) AS 最高分 FROM result; SELECT MIN(StudentResult) AS 最低分 FROM result; 3.数据库级别的MD5 加密一、什么是MD5？ 主要增强算法复杂度和不可逆性。MD5不可逆，具体的值的md5是一样的；MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值。二、实现数据加密 新建一个表 testmd5： CREATE TABLE `testmd5` ( `id` INT(4) NOT NULL, `name` VARCHAR(20) NOT NULL, `pwd` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; 插入一些数据： INSERT INTO testmd5 VALUES(1,'subei','123456'),(2,'wahaha','456789'); 如果我们要对pwd这一列数据进行加密，语法是： update testmd5 set pwd = md5(pwd); 如果单独对某个用户(如wahaha)的密码加密： INSERT INTO testmd5 VALUES(3,'admin','123456'); UPDATE testmd5 SET pwd = MD5(pwd) WHERE NAME='admin'; 插入新的数据自动加密 INSERT INTO testmd5 VALUES(4,'party',md5('123456')); 查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对） SELECT * FROM testmd5 WHERE `name`='subei' AND pwd=MD5('123456'); 6.事务 1.什么是事务?要么都成功，要么都失败。 事务就是将一组SQL语句放在同一批次内去执行； 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行； MySQL事务处理只支持InnoDB和BDB数据表类型； 事务原则：ACID原则、原子性、一致性、隔离性、持久性。 参考博客链接：https://blog.csdn.net/dengjili/article/details/82468576 原子性(Atomic) 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。一致性(Consist) 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。持久性(Durable) 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。隔离性(Isolated) 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。隔离所导致的一些问题： 脏读：指一个事务读取了另外一个事务未提交的数据。不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）。虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。 2.执行事务 注意: MySQL中默认是自动提交；使用事务时应先关闭自动提交。 -- ==========事务========== -- MySQL默认开启事务的 SET autocommit = 0; -- 关闭 SET autocommit = 1; -- 开启(默认的) -- 手动处理事务 -- 事务开启 START TRANSACTION; -- 开始一个事务,标记事务的起始点 INSERT XX INSERT XX -- 提交:持久化 COMMIT; -- 回滚:数据回到本次事务的初始状态 ROLLBACK; -- 事务结束 SET autocommit =1; -- 开始自动提交 -- 保存点 SAVEPOINT 保存点名称; -- 设置一个事务保存点 ROLLBACK TO SAVEPOINT 保存点名称; -- 回滚到保存点 RELEASE SAVEPOINT 保存点名称; -- 删除保存点测试 /* 题目： A在线买一款价格为500元商品,网上银行转账. A的银行卡余额为2000,然后给商家B支付500. 商家B一开始的银行卡余额为10000 创建数据库shop和创建表account并插入2条数据 */ CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; USE `shop`; CREATE TABLE `account` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL, `money` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO account (`name`,`money`) VALUES('A',2000.00),('B',10000.00); -- 模拟转账 SET autocommit = 0; -- 关闭自动提交 START TRANSACTION; -- 开始一个事务 UPDATE account SET money=money-500 WHERE `name`='A'; UPDATE account SET money=money+500 WHERE `name`='B'; COMMIT; -- 提交事务，持久化了！！！ # rollback; -- 回滚 SET autocommit = 1; -- 恢复自动提交 7.索引 1.概述MySQL官方对索引的定义为：索引（index）是帮助 MysQL高效获取数据的数据结构。 提取句子主千，就可以得到索引的本质：索引是数据结构。 索引的作用： 提高查询速度；确保数据的唯一性；可以加速表和表之间的连接 , 实现表与表之间的参照完整性；使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间；全文检索字段进行搜索优化。 2.索引的分类主键索引 (Primary Key) 唯一的标识，主键不可重复，只能有一个列作为主键。唯一索引 (Unique) 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引。常规索引 (Index) 默认的，key关键字来设置。全文索引 (FullText) 在特定引擎下才可以实现，MyISAM。 MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。 快速定位数据。语法： #方法一：创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); #方法二：CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; #方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; #删除索引：DROP INDEX 索引名 ON 表名字; #删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY; -- 索引的使用 -- 1.在创建表时给字段增加索引 -- 2.创建完毕后，增加索引 -- 显示所有的索引信息 SHOW INDEX FROM student; -- 增加全文索引 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`studentname`); -- EXPLAIN : 分析SQL语句执行性能 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('张');报错：#1292 – Incorrect datetime value: ‘0000-00-00 00:00:00’ 原因与解决方法： 原因： 当前的MySQL模式不支持datetime为0的情况。解决方法：只需要直接修改“sql_mode”即可！以Mysql5.7版本为例子，具体解决方法如下： 在MySQL配置文件my.ini 的 [mysqld] 下添加： sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION重启MySQL。 3.测试索引 建表app_user： CREATE TABLE `app_user` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称', `email` VARCHAR(50) NOT NULL COMMENT'用户邮箱', `phone` VARCHAR(20) DEFAULT'' COMMENT'手机号', `gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）', `password` VARCHAR(100) NOT NULL COMMENT '密码', `age` TINYINT(4) DEFAULT'0' COMMENT '年龄', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'; 批量插入数据：100w -- 插入百万条数据 DROP FUNCTION IF EXISTS mock_data; DELIMITER $$ -- 写函数之前必须写，标志 CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i < num DO -- 插入语句 INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`) VALUES(CONCAT('用户', i), '[email protected]', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100)); SET i = i + 1; END WHILE; RETURN i; END; SELECT mock_data();索引效率测试 无索引 SELECT * FROM app_user WHERE NAME = '用户9999'; -- 查看耗时 SELECT * FROM app_user WHERE NAME = '用户9999'; SELECT * FROM app_user WHERE NAME = '用户9999'; EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999'; 创建索引 -- CREATE INDEX 索引名 ON 表(字段) CREATE INDEX id_app_user_name ON app_user(`name`); 测试普通索引 SELECT * FROM app_user WHERE NAME = '用户9999'; 4.索引原则 索引不是越多越好；不要对经常变动的数据加索引；小数据量的表建议不要加索引；索引一般应加在查找条件的字段。索引的数据结构，参考博文 在创建上述索引的时候，为其指定索引类型，分两类： hash类型的索引：查询单条快，范围查询慢；btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）。 不同的存储引擎支持的索引类型也不一样： InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引； 8.权限管理及备份 1.用户管理使用SQLyog 创建用户，并授予权限。
SQL命令操作 用户表：mysql.user本质：对这张表进行增删改查。 -- 刷新权限 FLUSH PRIVILEGES; -- 增加用户 CREATE USER subei IDENTIFIED BY '123456'; CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串) - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。 - 只能创建用户，不能赋予权限。 - 用户名，注意引号：如 'user_name'@'192.168.1.1' - 密码也需引号，纯数字密码也要加引号 - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD -- 重命名用户 RENAME USER subei TO subei2; RENAME USER old_user TO new_user; -- 设置密码 SET PASSWORD = PASSWORD('admin'); SET PASSWORD = PASSWORD('密码'); -- 为当前用户设置密码 SET PASSWORD FOR 用户名 = PASSWORD('密码'); -- 为指定用户设置密码 -- 删除用户 DROP USER subei2; DROP USER 用户名; -- 分配权限/添加用户 GRANT all privileges ON *.* TO subei2; GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password'] - all privileges 表示所有权限 - *.* 表示所有库的所有表 - 库名.表名 表示某库下面的某表 -- 查看权限 SHOW GRANTS FOR root@localhost; SHOW GRANTS FOR 用户名 -- 查看当前用户权限 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER(); -- 撤消权限 REVOKE 权限列表 ON 表名 FROM 用户名 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限权限解释 -- 权限列表 ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限 ALTER -- 允许使用ALTER TABLE ALTER ROUTINE -- 更改或取消已存储的子程序 CREATE -- 允许使用CREATE TABLE CREATE ROUTINE -- 创建已存储的子程序 CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 CREATE VIEW -- 允许使用CREATE VIEW DELETE -- 允许使用DELETE DROP -- 允许使用DROP TABLE EXECUTE -- 允许用户运行已存储的子程序 FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE INDEX -- 允许使用CREATE INDEX和DROP INDEX INSERT -- 允许使用INSERT LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES PROCESS -- 允许使用SHOW FULL PROCESSLIST REFERENCES -- 未被实施 RELOAD -- 允许使用FLUSH REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址 REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件） SELECT -- 允许使用SELECT SHOW DATABASES -- 显示所有数据库 SHOW VIEW -- 允许使用SHOW CREATE VIEW SHUTDOWN -- 允许使用mysqladmin shutdown SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。 UPDATE -- 允许使用UPDATE USAGE -- “无权限”的同义词 GRANT OPTION -- 允许授予权限 -- 表维护 -- 分析和存储表的关键字分布 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ... -- 检查一个或多个表是否有错误 CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} -- 整理数据文件的碎片 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... 2.MySQL备份数据库备份必要性： 保证重要数据不丢失；数据转移；MySQL数据库备份方法： mysqldump备份工具；数据库管理工具，如SQLyog；
mysqldump客户端 作用 : 转储数据库；搜集数据库进行备份；将数据转移到另一个SQL服务器,不一定是MySQL服务器。
当数据库比较复杂时我们需要设计数据库。 糟糕的数据库设计 : 数据冗余,存储空间浪费；数据更新和插入的异常【屏蔽使用物理外键】；程序性能差；良好的数据库设计 : 节省数据的存储空间；能够保证数据的完整性；方便进行数据库应用系统的开发；软件项目开发周期中数据库设计 : 需求分析阶段: 分析客户的业务和数据处理需求；概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整。设计数据库步骤： 分析需求：分析业务和需要处理的数据库的需求；概要设计：设计关系E-R图； 收集信息与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.标识实体[Entity] 标识数据库要管理的关键对象或实体,实体一般是名词；标识每个实体需要存储的详细信息[Attribute]；标识实体之间的关系[Relationship]。 2.三大范式问题 : 为什么需要数据规范化? 不合规范的表设计会导致的问题： 信息重复 更新异常 插入异常 无法正确表示信息删除异常 丢失有效信息三大范式 第一范式 (1NF) 第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元，则满足第一范式。第二范式(2NF) 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。 第二范式要求每个表只描述一件事情。 第三范式(3NF) 如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式。 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。 规范化和性能的关系 为满足某种商业目标 , 数据库性能比规范化数据库更重要；在数据规范化的同时 , 要综合考虑数据库的性能；通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间；通过在给定的表中插入计算列,以方便查询。 10.JDBC 1.数据库驱动这里的驱动的概念和平时听到的那种驱动的概念是一样的，比如平时购买的声卡，网卡直接插到计算机上面是不能用的，必须要安装相应的驱动程序之后才能够使用声卡和网卡，同样道理，我们安装好数据库之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程 序去和数据库打交道，如下所示：
SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。 这套接口由数据库厂商去实现，这样，开发人员只需要学习jdbc接口，并通过jdbc加载具体的驱动，就 可以操作数据库。 如下图所示：
搭建实验环境 CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE users( id INT PRIMARY KEY, NAME VARCHAR(40), PASSWORD VARCHAR(40), email VARCHAR(60), birthday DATE ); INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(1,'zhansan','123456','[email protected]','1980-12-04'), (2,'lisi','123456','[email protected]','1981-12-04'), (3,'wangwu','123456','[email protected]','1979-12-04');新建一个Java工程，并导入数据驱动。 jar包下载地址：maven仓库。
编写程序从user表中读取数据，并打印在命令行窗口中。 package com.github.lesson01; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * 第一个JDBC程序 */ public class JdbcFirstDemo { public static void main(String[] args) throws Exception{ // 1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动 // 2.用户信息和url // uesUnicode=true 支持中文编码 // characterEncoding=utf8 设定字符集 // useSSL=true 使用安全的连接 String url = "jdbc:mysql://localhost:3306/jdbcStudy?uesUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "root"; String password = "root"; // 3.连接成功，数据库对象 connection 代表数据库 Connection connection = DriverManager.getConnection(url, username, password); // 4.执行SQL对象 Statement statement = connection.createStatement(); // 5.执行SQL对象，执行SQL String sql = "SELECT * FROM users"; ResultSet resultSet = statement.executeQuery(sql); // 返回结果集,结果集中封装了我们全部查询出来的对象 while(resultSet.next()){ System.out.println("id=" + resultSet.getObject("id")); System.out.println("name=" + resultSet.getObject("NAME")); System.out.println("pwd=" + resultSet.getObject("PASSWORD")); System.out.println("email=" + resultSet.getObject("email")); System.out.println("both=" + resultSet.getObject("birthday")); } // 6.释放连接 resultSet.close(); statement.close(); connection.close(); } }步骤总结： 加载驱动；连接数据库 DriverManager；获得执行SQL的对象 Statement；获得返回的结果集；释放连接。 4.对象说明DriverManager JDBC程序中的DriverManager用于加载驱动，并创建与数据库的链接，这个API的常用方法： // DriverManager.registerDriver(new Driver()) // DriverManager.getConnection(url, user, password) Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动 Connection connection = DriverManager.getConnection(url, username, password); // connection 代表数据库 connection.createStatement();// 创建向数据库发送sql的statement对象 connection.prepareStatement(sql);// 创建向数据库发送预编译sql的PrepareSatement对象 connection.rollback(); // 事务回滚 connection.commit(); // 事务提交 connection.setAutoCommit(); // 设置数据库自动提交注意：在实际开发中并不推荐采用registerDriver方法注册驱动。原因如下： 查看Driver的源代码可以看到，如果采用此种方式，会导致驱动程序注册两次，也就是在内存中会 有两个Driver对象。程序依赖mysql的api，脱离mysql的jar包，程序将无法编译，将来程序切换底层数据库将会非常麻 烦。推荐方式：Class.forName("com.mysql.jdbc.Driver"); 采用此种方式不会导致驱动对象在内存中重复出现，并且采用此种方式，程序仅仅只需要一个字符串， 不需要依赖具体的驱动，使程序的灵活性更高。 URL说明 URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，URL地址的写法为： String url = "jdbc:mysql://localhost:3306/jdbcStudy?uesUnicode=true&characterEncoding=utf8&useSSL=true"; // mysql 端口号 3306 // jdbc:mysql://localhost:3306/数据库名?参数1&参数2&参数3 // oracle 端口号 1521 // jdbc:oracle:thin:@localhost:1521:sid 常用数据库URL地址的写法： Oracle写法：jdbc:oracle:thin:@localhost:1521:sidSqlServer写法：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sidMySql写法：jdbc:mysql://localhost:3306/sid 如果连接的是本地的Mysql数据库，并且连接使用的端口是3306，那么的url地址可以简写为 jdbc:mysql:///数据库Statement执行SQL的对象 String sql = "SELECT * FROM users"; ResultSet resultSet = statement.executeQuery(sql); // 返回结果集,结果集中封装了我们全部查询出来的对象 // Statement对象常用方法 statement.executeQuery(sql); // 查询操作，返回结果集 statement.execute(sql); // 执行任何SQL statement.executeUpdate(sql); // 更新、插入、删除，均为这个。唯一受影响的是行数 statement.addBatch(sql); // 把多条sql语句放到一个批处理中 statement.executeBatch(sql); // 向数据库发送一批sql语句执行ResultSet 查询的结果集：封装了所有的查询结果。 获取指定的数据类型 resultSet.getString(); // 不知道数据类型时使用 // 如果知道列的类型就用指定的类型 resultSet.getString(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDouble(); resultSet.getDate(); resultSet.getObject(); .... ResultSet还提供了对结果集进行滚动的方法： resultSet.next(); // 移动到下一行 resultSet.previous(); // 移动到前一行 resultSet.absolute(int row); // 移动到指定行 resultSet.beforeFirst(); // 移动resultSet的最前面。 resultSet.afterLast(); // 移动到resultSet的最后面。释放资源 // 6.释放连接 resultSet.close(); statement.close(); connection.close(); // 用完即关掉 5.statement对象JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象 向数据库发送增删改查语句即可。 Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行 完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。 Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的 ResultSet对象。 CRUD操作-create 使用executeUpdate(String sql)方法完成数据添加操作，示例操作： Statement st = conn.createStatement(); String sql = "insert into user(….) values(…..) "; int num = st.executeUpdate(sql); if(num>0){ System.out.println("插入成功！！！"); }CRUD操作-delete 使用executeUpdate(String sql)方法完成数据删除操作，示例操作： Statement st = conn.createStatement(); String sql = "delete from user where id=1"; int num = st.executeUpdate(sql); if(num>0){ System.out.println(“删除成功！！！"); }CRUD操作-update 使用executeUpdate(String sql)方法完成数据修改操作，示例操作： Statement st = conn.createStatement(); String sql = "update user set name='' where name=''"; int num = st.executeUpdate(sql); if(num>0){ System.out.println(“修改成功！！！"); }CRUD操作-read 使用executeQuery(String sql)方法完成数据查询操作，示例操作： Statement st = conn.createStatement(); String sql = "select * from user where id=1"; ResultSet rs = st.executeUpdate(sql); while(rs.next()){ //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中 }案例：使用jdbc对数据库增删改查 新建一个 lesson02 的包；在src目录下创建一个db.properties文件，写入如下内容： driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=root 在lesson02 下新建一个 utils 包，新建一个类 Utils： package com.github.lesson02.utils; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class Utils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static{ try{ // 读取db.properties文件中的数据库连接信息 InputStream in = Utils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); // 获取数据库连接驱动 driver = properties.getProperty("driver"); // 获取数据库连接URL地址 url = properties.getProperty("url"); // 获取数据库连接用户名 username = properties.getProperty("username"); // 获取数据库连接密码 password = properties.getProperty("password"); // 加载数据库驱动，只需加载一次！ Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } // 获取连接对象 public static Connection getConnection() throws SQLException{ return DriverManager.getConnection(url,username,password); } // 释放资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } 执行增删改查数据 package com.github.lesson02; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; /** * 插入数据 */ public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try{ // 获取一个数据库连接 conn = Utils.getConnection(); // 通过conn对象获取负责执行SQL命令的Statement对象 st = conn.createStatement(); // 要执行的SQL String sql = ""; // 执行操作 int num = st.executeUpdate(sql); if(num>0){ System.out.println("插入数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭资源 Utils.release(conn,st,rs); } } } package com.github.lesson02; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; /** * 删除数据 */ public class TestDelete { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); st = conn.createStatement(); String sql = "delete from users where id=4"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("删除数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } } package com.github.lesson02; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; /** * 修改数据 */ public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); st = conn.createStatement(); String sql = "update users set name='王伟',email='[email protected]' where id=3"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("更新数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } } package com.github.lesson02; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; /** * 查询数据 */ public class TestSelect { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); st = conn.createStatement(); // String sql = "select * from users"; String sql = "select * from users where id=2"; rs = st.executeQuery(sql); while(rs.next()){ System.out.println("查询数据成功！！！"); // System.out.println(rs.getString("name")); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } }SQL注入 通过巧妙的技巧来拼接字符串，造成SQL短路，从而获取数据库数据 SQL存在漏洞，会被攻击。 package com.github.lesson03; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; public class SQL注入 { public static void main(String[] args) { login("lisi","123456"); // 正常登录 ==结果==>结果输出 // login("'or '1=1","123456"); // SQL注入 ==结果==>输出数据库内容 } // 登陆业务 public static void login(String username,String password){ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); st = conn.createStatement(); // select * from users where name=''or '1-1' and password='123456' String sql = "select * from users where name='"+username +"' and password='"+password+"'"; rs = st.executeQuery(sql); while(rs.next()){ System.out.println("查询数据成功！！！"); System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); System.out.println("=========="); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } } 6.PreparedStatement对象PreperedStatement是Statement的子类，它的实例对象可以通过调用 Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避 免SQL注入的问题。 Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。 PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于 sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。 使用PreparedStatement对象完成对数据库的CRUD操作 插入数据 package com.github.lesson03; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = Utils.getConnection(); String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)"; st = conn.prepareStatement(sql); // 为SQL参数赋值，索引是从1开始的 st.setInt(1,5); // id是int类型 st.setString(2,"subei"); // name是字符串类型 st.setString(3,"123456"); // password是字符串类型 st.setString(4,"[email protected]"); // email是字符串类型 st.setDate(5,new java.sql.Date(System.currentTimeMillis())); // birthday是date类型 /* * 这里有个小问题: * 在使用 new Date().getTime() 时，会报错：请使用System.currentTimeMillis()代替new Date().getTime() * 对于这个问题，百度一下： * new Date()所做的事情其实就是调用了System.currentTimeMillis()。 * 如果仅仅是需要或者毫秒数，那么完全可以使用System.currentTimeMillis()去代替new Date()， * 效率上会高一点。况且很多人喜欢在同一个方法里面多次使用new Date()， * 通常性能就是这样一点一点地消耗掉，这里其实可以声明一个引用。 * */ // 执行插入数据操作 int num = st.executeUpdate(); if(num>0){ System.out.println("插入数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { // SQL释放资源 Utils.release(conn,st,rs); } } } 删除数据 package com.github.lesson03; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * 删除数据 */ public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); String sql = "delete * from users where id=?"; st = conn.prepareStatement(sql); st.setInt(1,4); int num = st.executeUpdate(sql); if(num>0){ System.out.println("删除数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } } 更新数据 package com.github.lesson03; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * 修改数据 */ public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); String sql = "update users set name=?,email=? where id=?"; st = conn.prepareStatement(sql); st.setString(1,"叶凡"); st.setString(2,"[email protected]"); st.setInt(3,1); int num = st.executeUpdate(); if(num>0){ System.out.println("更新数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } } 查询数据 package com.github.lesson03; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * 查询数据 */ public class TestSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); String sql = "select * from users where id=?"; st = conn.prepareStatement(sql); st.setInt(1,1); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getString("name")); System.out.println("查询数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } }避免SQL注入 package com.github.lesson03; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; public class SQL注入 { public static void main(String[] args) { // login("lisi","123456"); // 正常登录 ==结果==>结果输出 login("'or '1=1","123456"); // SQL注入 ==结果==>无结果输出 } // 登陆业务 public static void login(String username,String password){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = Utils.getConnection(); String sql = "select * from users where name=? and password=?"; st = conn.prepareStatement(sql); st.setString(1,username); st.setString(2,password); rs = st.executeQuery(); while(rs.next()){ System.out.println("查询数据成功！！！"); System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); System.out.println("=========="); } } catch (Exception e) { e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } } 原理：执行的时候参数会用引号包起来，并把参数中的引号作为转义字符，从而避免了参数也作为条件 的一部分。 7.使用IDEA连接数据库 打开IDEA2020.2的如下图：
事务：逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。 ACID原则： 原子性(Atomic)：要么全部完成，要么都不完成。一致性(Consist)：总数不变。隔离性(Isolated)：多个进程互不干扰。持久性(Durable)：一旦提交不可逆，持久化到数据库了。隔离导致的一些问题： 脏读：一个事务读取了另外一个事务未提交的数据。不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）。虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交 在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列的 JDBC控制事务语句。 Connection.setAutoCommit(false);//开启事务(start transaction) Connection.rollback();//回滚事务(rollback) Connection.commit();//提交事务(commit) 模拟转账成功时的业务 package com.github.lesson04; import com.github.lesson02.utils.Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * 模拟转账成功时的业务场景 */ public class TestTransaction { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { // 关闭数据库的自动提交，自动开启事务 conn = Utils.getConnection(); // 开启事务 conn.setAutoCommit(false); String sql1 = "update account set money=money-100 where name='A'"; st = conn.prepareStatement(sql1); st.executeUpdate(); String sql2 = "update account set money=money+100 where name='B'"; st = conn.prepareStatement(sql2); st.executeUpdate(); // 业务完毕，提交事务 conn.commit(); System.out.println("转账成功！！！"); } catch (Exception e) { try { conn.rollback(); // 失败，则事务回滚 } catch (SQLException throwables) { throwables.printStackTrace(); } e.printStackTrace(); } finally { Utils.release(conn,st,rs); } } }
用户每次请求都需要向数据库获得链接，而数据库创建连接通常需要消耗相对较大的资源，创建时间也 较长。假设网站一天10万访问量，数据库服务器就需要创建10万次连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、拓机。 数据库连接池的基本概念 数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现的尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正式针对这个问题提出来的。数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的 数据库连接,而不是重新建立一个。 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数 超过最大连接数量时，这些请求将被加入到等待队列中。 数据库连接池的最小连接数和最大连接数的设置要考虑到以下几个因素: 最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作。如果最小连接数与最大连接数相差很大：那么最先连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，他将被放到连接池中等待重复使用或是空间超时后被释放。编写连接池，需实现java.sql.DataSource接口。 开源数据源实现 现在很多WEB服务器(Weblogic, WebSphere, Tomcat)都提供了DataSoruce的实现，即连接池的实现。 通常我们把DataSource的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实 现。 也有一些开源组织提供了数据源的独立实现： DBCP 数据库连接池C3P0 数据库连接池Druid 数据库连接池 —— 阿里巴巴在使用了数据库连接池之后，在项目的实际开发中就不需要编写连接数据库的代码了，直接从数据源获得数据库的连接。 DBCP 要使用DBCP数据源，需要导入如下两个 jar 文件： Commons-dbcp-1.4.jarCommons-pool-1.6.jar
C3P0 C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。 c3p0与dbcp区别： dbcp没有自动回收空闲连接的功能；c3p0有自动回收空闲连接功能。要使用C3P0数据源，需要导入如下两个 jar 文件： c3p0-0.9.5.5.jarmchange-commons-java-0.2.19.jar在src目录下加入C3P0的配置文件：c3p0-config.xml com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/jdbcStudy? useUnicode=true;characterEncoding=utf8;useSSL=true root root 5 10 5 20 com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true;characterEncoding=utf8;useSSL=true root root 5 10 5 20 编写工具类 Utils_C3P0.java package com.github.lesson05.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * C3P0工具类 */ public class Utils_C3P0 { private static ComboPooledDataSource ds = null; static{ try { // 使用C3P0的默认配置来创建数据源 ds = new ComboPooledDataSource(); // 使用C3P0的命名配置来创建数据源 // ds = new ComboPooledDataSource("MySQL"); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } /** * 获取数据库连接 * @return * @throws SQLException */ public static Connection getConnection() throws SQLException { return ds.getConnection(); } /** * 释放资源 * @param conn * @param st * @param rs */ public static void release(Connection conn, Statement st, ResultSet rs) { if(rs != null){ try { rs.close(); } catch (Exception e) { e.printStackTrace(); } } if(st!=null) { try { st.close(); } catch (Exception e) { e.printStackTrace(); } } if(conn!=null) { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } } } 测试类 package com.github.lesson05; import com.github.lesson05.utils.Utils_C3P0; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * C3P0测试类 */ public class TestC3P0 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = Utils_C3P0.getConnection(); String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setInt(1,7); st.setString(2,"pink"); st.setString(3,"263223"); st.setString(4,"[email protected]"); st.setDate(5,new java.sql.Date(System.currentTimeMillis())); // 执行插入数据操作 int num = st.executeUpdate(); if(num>0){ System.out.println("插入数据成功！！！"); } } catch (Exception e) { e.printStackTrace(); } finally { // SQL释放资源 Utils_C3P0.release(conn,st,rs); } } } 无论使用什么数据源，本质都是一样的！！！ MySQL快速入门完结🎉🎉🎉🎉 |
【本文地址】
公司简介
联系我们