数据库基础
数据库分类
关系型数据库 ---->MySQL、SqlServer、Oracle、DB2、Access、PostgreSQL、SqLite、OceanBase、GaussDB、TDSQL、DM
非关系型数据库---->Redis、Mongodb、CouchDB、Couchbase、HBase、Neo4j、db4o、BaseX、IMS
基本术语
-
数据库: 数据库是一些关联表的集合。
-
数据表: 表是数据的矩阵。
-
列: 一列(数据元素) 包含了相同类型的数据。
-
行：一行（=元组，或记录）是一组相关的数据。
-
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
-
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据，不能为空。
-
外键：外键用于关联两个表。
-
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
-
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
-
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
数据库表组成
行：每一行用来描述某条记录的具体信息
列（字段名称）：具有相同数据类型的数据的集合
值：行的具体信息, 每个值必须与该列的数据类型相同
键：键的值在当前列中具有唯一性
数据类型
数值
类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
TINYINT | 1 Bytes | (-128，127)即(-2^7 , 2^7-1) | (0，255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
时间日期
添加默认时间类型配置（CURRENT_TIMESTAMP）
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31**** | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |
字符串
类型 | 大小 | 用途 |
|---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
命名规范
表命名
-
采用26个英文字母(区分大小写)、0-9的自然数(经常不需要)、下划线_组成，命名简洁明确，多个单词用下划线_分隔 -
全部小写命名，禁止出现大写 -
禁止使用数据库关键字，如：name，time ，datetime，password，order，class等 -
表名称不应该取得太长（一般不超过三个英文单词） -
表的名称一般使用名词或者动宾短语并且一般使用单数 -
表必须填写描述信息（使用SQL语句建表时
字段命名
-
26个英文字母(区分大小写)、0-9的自然数(经常不需要)、下划线_组成，命名简洁明确，多个单词用下划线_分隔 -
全部小写命名，禁止出现大写 -
字段必须填写描述信息 -
禁止使用数据库关键字，如：name，time ，datetime，password 等 -
字段名称一般采用名词或动宾短语 -
单词不宜过多,不超过三个即可 -
在命名表的列时，不要重复表的名称 -
不要在列的名称中包含数据类型 -
字段命名使用完整名称，禁止缩写
表的约束
约束种类
| 约束名 | 约束关键字 |
|---|---|
| 主键 | primary key |
| 唯一 | unique |
| 非空 | not null |
| 外键 | foreign key |
主键约束
CREATE TABLE IF NOT EXISTS stu01 (
-- 设置三个字段
userid INT ( 10 ) AUTO_INCREMENT,-- userid设置自动递增(AUTO_INCREMENT)（设为自增的字段一定是主键）
PRIMARY KEY ( userid ) -- 设置主键,主键(非空,不能重复)
)
唯一约束
CREATE TABLE IF NOT EXISTS per (
-- 设置ppid为自增的主键
ppid INT ( 10 ) PRIMARY KEY AUTO_INCREMENT ,
-- 创建一个字段,字段添加唯一约束
name VARCHAR(22) UNIQUE
)
非空约束
CREATE TABLE IF NOT EXISTS per (
ppid INT ( 10 ) PRIMARY KEY AUTO_INCREMENT ,
-- 添加字段,设置非空约束
sex VARCHAR(10) NOT NULL
)
-- 设置非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
DEFAULT 默认值
CREATE TABLE IF NOT EXISTS per (
ppid INT ( 10 ) PRIMARY KEY AUTO_INCREMENT ,
-- 设置默认值为其他
sex VARCHAR(10) NOT NULL DEFAULT "其他"
)
-- 验证
INSERT INTO per VALUES (null,DEFAULT)
外键约束
外键：在从表中与主表主键对应的那一列，如：用户表中的 roleid
主表：用来约束别人的表，一方，如图2
从表（副表）：被别人约束的表，多方，如图1
语句添加外键：
创建主键的时候,必须注意主表的主键和从表的外键,也就是这关联的两个字段的字段的类型,约束等一致
-- 新建表时增加外键 -- [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) CREATE TABLE if NOT EXISTS user_c( id int(11) PRIMARY KEY auto_increment, username VARCHAR(22), password VARCHAR(255), roleid int(11) NOT NULL,-- 外键对应着主表的主键 constraint roleid_fk foreign key (roleid) references rolelist(roleid) )
-- 已有表增加外键 ALTER TABLE 从表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (从表外键字段) REFERENCES 主表名称(主表主键);
-- 删除存在的外键 alter table 表名称 drop foreign key 外键名称;
| 语法 | 描述 |
|---|---|
| ON UPDATE CASCADE | 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新 |
| ON DELETE CASCADE | 级联删除 |
-- 增加更新删除的级联 create table `emp01`( id int primary key auto_increment, name varchar(20), age int, dept_id int, -- 外键对应主表的主键 constraint emp_deptid_fk foreign key (dept_id) references department(id) on update cascade on delete cascade -- 创建外键约束 );
手动添加外键：
需要在子表中添加
CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；
RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
NO ACTION：同 RESTRICT，也是首先先检查外键
sql语句
分类
-
数据定义语言DDL（Data Definition Language）：建表，建库。create table 表名
-
数据查询语言DQL（Data Query Language）：对表中的查询操作select * from 表名
-
数据操纵语言DML（Data Manipulation Language）：对表中的记录操作增删改insert、update、delete
-
数据控制功能DCL（Data Control Language）：用户权限的设置
语法
-
每一条SQL语句都是以分号（英文状态）结束，但是在Navicat中是可以不加分号的； -
SQL中是不区分大小写，关键字中认为大小写一样； -
注释:-- 注释内容
-
反引号``：给表起名时，可能会与sql的关键字冲突，所以表名和字段统一用反引号引起来
进入mysql操作语句:
mysql -uroot -p密码
数据库创建语句DDL
建库
-- 创建数据库 sqlstudy为数据库名称 CREATE DATABASE sqlstudy; -- 删除数据库 drop DATABASE sqlstudy; -- 展示数据库 show databases; -- 使用数据库 use sqlstudy;
建表
-- 如果有这个表,就删掉这个表,重新建表 DROP TABLE IF EXISTS user1; -- 创建表,如果没有创建表的话就创建 CREATE TABLE IF NOT EXISTS user1 ( -- 设置三个字段 userid INT ( 10 ) AUTO_INCREMENT,-- userid设置自动递增(AUTO_INCREMENT) username VARCHAR ( 22 ) NOT NULL,-- 该字段不能为空 brithday date COMMENT '生日',-- COMMENT 表示注释 sex VARCHAR ( 22 ), create_time datetime, PRIMARY KEY ( userid ) -- 设置主键,主键(非空,不能重复) )
改表（ALTER）
-- ALTER 命令及 DROP 子句来删除以上创建表的 i 字段： ALTER TABLE testalter_tbl DROP i; -- 使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型: ALTER TABLE testalter_tbl ADD i INT; -- 如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 ALTER TABLE testalter_tbl MODIFY c CHAR(10) ALTER TABLE testalter_tbl CHANGE i j BIGINT;
删表（DROP）
-- 删除数据库 DROP DATABASE 数据库名; -- 删除表 DROP TABLE table_name ;
数据库操作语句DML
对表中数据的操作
增加(INSERT)
-- 第一种
-- insert into 表名 (字段名1,字段名2,...字段名n)
-- values(字段1对应数据,字段2数据,...字段n数据)
-- 例子:now() 获取当前时间
INSERT INTO `user` (`user`.username,`user`.`password`,`user`.creat_date)
VALUES ("cc","1234",NOW())
-- 第二种 -- 此方式必须将所有字段都列举出来 -- 在values中列出的数据位置必须与被加入的列的排列位置相对应 insert into 表名 values(字段1的数据,....字段n的数据) -- 例子:CURRENT_DATE通now() INSERT INTO `user` VALUES(NULL,"cl","23423",CURRENT_DATE)
改动(UPDATE)
-- 语法 -- update 表名称 set 列名称 = 新值 where 更新条件 -- 表中所有信息的username修改成cxk,但是这样有个弊端,就是回改变大多数的数据,所以多数情况下都是在后面加个where条件语句 UPDATE `user` SET username='cxk' -- 加上where条件之后 UPDATE `user` SET username='cxkas' WHERE userId=3 -- 可使用 WHERE 表名.字段 -- 更新多个字段 -- 即在修改字段后面使用逗号隔开,写多个字段 UPDATE `user` SET username='cxkas',`password`="jntm" WHERE userId=3
删除(DELETE)
-- 语法 -- delete from 表名称 where 删除条件 -- 删除userId为1得数据信息 DELETE FROM `user` WHERE userId = 1 -- TRUNCATE也可以删除数据,并且是全部删除, TRUNCATE runoob_tbl
delete，drop，truncate 都有删除表的作用，区别在于:
1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除
2、delete 是 DML 语句，操作完以后如果不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句
3、执行的速度上，drop>truncate>delete
4、如果使用truncate进行删除记录，相当于删除表的结构，再创建一张表
数据库查询语句DQL
语法与注意
SELECT 查询列表 FROM 表名称 where 条件
①查询列表可以是：表中的字段、常量值(相当于增加一列常量值)、表达式、函数(求和)。查询常量、表达式、函数时语句后面不用跟from 表名，因为它并不是表示来自哪个表
②查询的结果是一个虚拟的表格
查询实例
-- 这是一个最简单的查询语句,功能为查询名为user的表的所有信息 SELECT * FROM `user` -- 当然,不仅可以查询表的所有信息,还可以单独查询多个字段或者一个字段.需要将*换成相应的字段名就行,多个字段名用逗号隔开 SELECT `code`,shortname ,openingprice ,latestprice FROM gpinfoc -- 查询多个表 -- 这是查询两个表的内容 但是查询出来的结果是两个表的笛卡尔积(即两个表得信息交叉所得的集合) -- A表有a条数据，B表有b条数据，则查询输出a*b条数据，其中A表中的每条数据都与B表中的每条进行组合 SELECT * FROM `user`,`user_copy1` -- 常量查询 SELECT 20 SELECT 20+50
-- 查询score大于9的数据，LIMIT只输出9条 -- OFFSET偏移两条，即从socre大于9的第三条数据开始输出 SELECT * FROM book WHERE score>9 LIMIT 9 OFFSET 2 SELECT * FROM book WHERE score>9 LIMIT 2,9
别名(AS)
当我们查询了某些字段的时候,需要将字段名设置为比较好区别的名字的时候,就可以使用AS给字段设置一个别名
-- 查询某些字段,需要将字段名设置为好区别的名字时,就可以使用AS给字段设置一个别名 -- 例:将code字段设置别名为 股票编码 SELECT code as "股票编码" FROM gpinfoc -- 可以用空格代替as SELECT code "股票编码" FROM gpinfoc -- 输出时，会临时将查询字段的表头替换成别名，便于理解
去重
-- 查询book列表中得评分,只想知道有哪些评分,直接查,会有一些评分重复.所以添加DISTINCT,进行去重 SELECT DISTINCT score FROM book
联合查询
-- 将结果显示在一列中 -- union all 则将所有的结果全部显示出来，不管是不是重复 SELECT score FROM book UNION ALL SELECT bookname FROM book -- union 会自动压缩多个结果集合中的重复结果 SELECT score FROM book UNION SELECT bookname FROM book
+号
SELECT 122+20; -- 数值加法运算 SELECT "222"+20; -- 其中一个是字符型，会试图将字符型数值转换成数值型，成功就做加法运算 SELECT "john"+20; -- 转化不了数值就直接转化为0 SELECT NULL+20; -- 只要有一个字符为null，结果就是null -- 将两个字段组合在一起输出，使用concat SELECT CONCAT(username,sex) FROM user1
where语句
-- 为查询增加条件 SELECT * FROM `user` WHERE username='cc'
-- or和and关键字 -- 查询条件不止一个时 -- 查询图书评分在9.x的并且没有翻译者的图书 SELECT * FROM book WHERE score LIKE "9.%" AND translater ="无翻译" -- 查询成绩大于90或者班级号为3的数据 SELECT * FROM cjb WHERE score >90 OR clazz=3
-- between and关键字 -- 等价于大于等于第一个值，小于等于第二个值 -- 查询分数在90到95之间的数据 SELECT * FROM cjb WHERE score BETWEEN 90 AND 95
-- in关键字 -- 判断某个字段的值是否属于in列表中的某一项 -- 查询分数在列表中的数据 SELECT * FROM cjb WHERE score in (90,89,99) SELECT * FROM cjb WHERE (score,clazz) in ((90,2),(89,3),(99,1))
-- is null关键字 SELECT age,name FROM stu WHERE money IS NULL; -- is not null关键字 SELECT age,name FROM stu WHERE money IS NOT NULL;
CASE WHEN
-- 类似于java中的if else语句 -- CASE WHEN condition THEN result -- ELSE result -- END CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END
like语句
查询条件需要查询包含某个或某些字符得数据时,就需要用到like语句
-- 查询图书表里面press字段的数据中包含“出版社”三个字的数据 -- LIKE 子句中使用百分号 % 字符来表示任意字符 SELECT * FROM book WHERE press LIKE "%出版社%"
'%a' //以a结尾的数据 'a%' //以a开头的数据 '%a%' //含有a的数据 '_a_' //三位且中间字母是a的（_为占位符） '_a' //两位且结尾字母是a的 'a_' //两位且开头字母是a的 -- 查询评分在是8.*得数据 SELECT * FROM book WHERE score LIKE "8._" -- 查询名字中 SELECT * FROM user1 WHERE username LIKE "_原里%"
排序
-- ORDER BY关键字进行排序 -- 查询股票信息表中得数据 根据openingprice字段进行排序,排序规则是DESC(倒序),不设置排序规则默认为ASC(升序) SELECT * FROM gpinfoc ORDER BY openingprice DESC limit 0,5 -- 当然排序规则也可以不仅一个条件,也可以多个,先按照前一个条件条件进行排序,在按照第二条件排序 SELECT * FROM cjb ORDER BY score ASC ,clazz desc -- 甚至排序条件还可以设置为函数或者表达式,下面例子是通过成绩总分进行排序 SELECT `name`,SUM(score) FROM cjb GROUP BY name ORDER BY SUM(score) ASC
分组查询
字段值一样才能分到一组
-- 查询班级,通过班级进行分组 SELECT clazz FROM student GROUP BY student.clazz -- 根据姓名分组查询,获取每个学生得总成绩(SUM函数,求和) SELECT name,SUM(score) FROM cjb GROUP BY `name` -- 从book中查询book_name，并按分数相同的分类，并以其中一本书作为代表进行展示 SELECT book_name FROM book GROUP BY score SELECT * FROM book WHERE book_name = "窗边的小豆豆" SELECT * FROM book WHERE score = 8.80
-- 分组之后如果要额外添加条件的话,则需要加having关键字 -- SELECT 字段名 FROM 表名 group by 字段名 having 条件 -- 注意：having过滤时，是对查询结果进行过滤，即已经查询出来的字段进行过滤。如上述对book进行score分组查询时，会将score相等的分为一组，并以其中一本书为代表进行展示。在抽象层面，虽然展示出来的只有一本书，但实际上score相等的有多本书。在此基础上进行having过滤时，只会在展示出来的数据上进行过滤。 SELECT `name`,SUM(score) FROM cjb GROUP BY name HAVING `name` LIKE "李_"; -- 求平均分大于80的同学并排序 SELECT name, AVG(score) FROM cjb GROUP BY `name` HAVING AVG(IFNULL(score,0))>80 ORDER BY AVG(IFNULL(score,0)) desc
having和where的区别:
-
where是先过滤再分组，having是先分组再过滤 -
where后面不可以使用聚合函数,having后面可以使用聚合函数 -
where在分组之前就会进行筛选，过滤掉的数据不会进入分组
执行顺序
1.select 2.from 3.where 4.group by 5.having 6.order by 7.limit select 查询字段... form 表名 where 条件 group by 分组字段 having 分组条件 order by 排序字段 limit
数据库连接查询
分类
-
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
-
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
-
RIGHT JOIN（右连接）：获取右表所有记录，即使左表没有对应匹配的记录。
内连接
-- 连接用户和角色表 SELECT * FROM hqyj_student INNER JOIN hqyj_per -- 注意：连接查询时，设置条件应使用ON关键字。可在ON关键字过滤之后再使用WHERE过滤 -- 查询hqyj_student表和hqyj_per表连接之后,roleid相等得数据, 内连接 SELECT * FROM hqyj_student INNER JOIN hqyj_per ON hqyj_student.roleid = hqyj_per.roleid WHERE id = 1
匹配成功时（roleid相等时）：
未匹配成功时：
左连接
-- 将hqyj_student表和hqyj_per表进行左连接,即使没有相互匹配的数据也会将左表显示 SELECT * FROM hqyj_student LEFT JOIN hqyj_per ON hqyj_student.roleid = hqyj_per.roleid -- 当hqyj_student表中没有roleid=4的数据的话,hqyj_student表也会显示 SELECT * FROM hqyj_student LEFT JOIN hqyj_per ON hqyj_student.roleid = 4
右连接
-- 将hqyj_student表和hqyj_per表进行左连接,即使没有相互匹配的数据也会将右表显示 SELECT * FROM hqyj_student RIGHT JOIN hqyj_per ON hqyj_student.roleid = hqyj_per.roleid -- 当hqyj_student表中没有roleid=4的数据的话,hqyj_per表也会显示 SELECT * FROM hqyj_student RIGHT JOIN hqyj_per ON hqyj_student.roleid=4
全连接
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。
执行以下查询语句
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询
子查询在主查询之前执行完成，结果被主查询使用
-- 查询其他班级中数学成绩比2班李伟的数学成绩高的学生姓名和分数(用子查询) SELECT name,score FROM cjb WHERE `subject`="数学" AND score > (select score FROM cjb where `name`="李伟" AND clazz=2 AND `subject`="数学" )
子查询分类：
单行单列（一个值）
SELECT book_name FROM book WHERE book.id = 16 -- 所得结果为一个值 -- 可用 = > < 等符号 SELECT space FROM book WHERE book_name = (SELECT book_name FROM book WHERE book.id = 16)
多行单列（一个集合）
SELECT book_name,space,score FROM book WHERE book.score > 9.30 -- 所得结果为一个集合 -- 用IN等多值运算符 SELECT book_name,space,score FROM book WHERE book.score IN (SELECT score FROM book WHERE book.score > 9.3) SELECT press FROM book WHERE book.score > 9.5 SELECT book_name,press FROM book WHERE book.press IN (SELECT press FROM book WHERE book.score > 9.5)
多行多列（一个表）
SELECT score,space FROM book WHERE space > 300 -- 所得值多行多列看成表 -- 使用连接查询 SELECT * FROM book INNER JOIN (SELECT score,space FROM book WHERE space > 300) AS `data` ON book.score = `data`.score
数据库函数
标量函数
-
UCASE() - 将某个字段转换为大写 -
LCASE() - 将某个字段转换为小写 -
MID(字段,开始,长度) - 从某个文本字段提取字符，开始参数的默认值是1 -
LENGTH() - 返回某个文本字段的长度 -
ROUND() - 对某个数值字段进行指定小数位数的四舍五入 -
NOW() - 返回当前的系统日期和时间-- 一 CREATE TABLE Orders ( OrderId int NOT NULL, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT NOW(), PRIMARY KEY (OrderId) ) -- 二 INSERT INTO `user` (`user`.username,`user`.`password`,`user`.creat_date) VALUES ("cc","1234",NOW()) -- 三 SELECT NOW()
7.FORMAT() - 格式化某个字段的显示方式
-- DATE_FORMAT(date，format）函数根据format字符串格式化date值
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d;%H:%i:%s') - DATE_FORMAT("1997-08-20"，'%Y-%m-%d;%H:%i:%s')
SELECT userid,username,DATE_FORNAT(NOW(),'%Y') - DATE_FORMAT(brithday, '%Y') as "年龄" FROM user1;
8.TIMESTAMPDIFF(时间单位，开始，结束) - 求时间差
SELECT TIMESTAMPDIFF(MONTH, "1997-08-20", NOw())
聚合函数
-
count(col): 表示求指定列的总行数-- 当score列含有三行空数据的时候,这三行就不参与计算（null值不参与count计算） SELECT COUNT(score) FROM cjb -- 加了ifnull()之后,就将null替换为ifnull的第二个参数 SELECT COUNT(IFNULL(score,0)) FROM cjb
-
max(col): 表示求指定列的最大值 -
min(col): 表示求指定列的最小值 -
sum(col): 表示求指定列的和 -
avg(col): 表示求指定列的平均值 -
IFNULL(参数1，参数2)：第一个参数为字段名称，如果第一个参数为空，则用第二个参数替换 -
OVER(PARTITION BY column) / OVER(ORDER BY culumn)：PARTITION BY 子句进行分组；ORDER BY 子句进行排序SELECT *, SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资, SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资, SUM(Salary) OVER(ORDER BY ID) 累计工资, SUM(Salary) OVER() 总工资 from Employee -
LEAD()：有三个参数：第一个为待查询的参数列名，第二个为向下偏移的位数，第三个参数为超出最下面边界的默认值 -
STUFF ( character_expression , start , length ,replaceWith_expression )：将字符串插入到另一个字符串中，从第一个字符串的start位置删除length长度的字符；然后将第二个字符串插入到第一个字符串的start位置 -
coalesce()：COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值 -
LAG()：有三个参数：第一个为待查询的参数列名，第二个为向上偏移的位数，第三个参数为超出最上面边界的默认值（向上偏移，超出后，显示默认值）
SELECT user_id,
user_age,
lag(user_age, 1, 0) over(ORDER BY user_id) RESULT
FROM user_info;
例：
-- 表：Logs
-- +-------------+---------+
-- | Column Name | Type |
-- +-------------+---------+
-- | id | int |
-- | num | varchar |
-- +-------------+---------+
-- id 是这个表的主键。
-- 编写一个 SQL 查询，查找所有至少连续出现三次的数字。
-- 返回的结果表中的数据可以按 任意顺序 排列。
select
distinct t.num as ConsecutiveNums
from
(
select
num,
lag(num, 1) over(order by id) as num1,
lag(num, 2) over(order by id) as num2
from Logs
) t
where t.num = t.num1 and t.num1 = t.num2
数据库相关概念
mysql事务
事务概述
在mysql中，事务是一种机制、一个操作序列，是访问和更新数据库的程序执行单元。事务中包含一个或多个数据库操作命令，会把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
MySQL 事务主要用于处理操作量大，复杂度高的数据。
例如，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这些数据库操作语句就构成一个事务
-
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 -
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。 -
事务用来管理 insert,update,delete 语句
事务的四大特性AICD
-
原子性（Atomicity）:事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作要么都成功，要么都失败。失败时，其它已经被执行的操作都将被撤销并回滚，只有所有的操作全部成功，整个事务才算是成功完成
-
一致性（Consistency) ：事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。比如：从A账户转账到B账户，不可能使A账户扣了钱，而B账户没有加钱
-
隔离性（Isolation）:事务的隔离性是指在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的
-
持久性（Duration）:事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态
事务，能保证AID，即原子性，隔离性，持久性。但是一致性无法通过事务来保证，一致性依赖于应用层，开发者
事务分类
隐式事务：在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
显式事务：该事务具有明显的开启和结束标记，即不会自动提交
#步骤一：开启事务（可选） start transaction; #步骤二：编写事务中的sql语句（insert、update、delete） #这里实现一下"李二给王五转账"的事务过程 update t_account set balance = 50 where vname = "李二"; update t_account set balance = 130 where vname = "王五"; #步骤三：结束事务 commit; #提交事务 # rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态
事务并发问题
当多个事务同时运行时，可能出现A在操作t_account表中的数据，B也同样在操作t_account表，那么就会出现并发问题，对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会发生以下各种并发问题
脏独
A事务读取B事务尚未提交的更改数据，并在这个数据的基础上操作。如果恰巧B事务回滚，那么A事务读到的数据是不被承认的。
| 时间 | 转账(事务A) | 取款(事务B) |
|---|---|---|
| 1 | 开始 | |
| 2 | 开始 | |
| 3 | 查询账户余额1000元 | |
| 4 | 取出500元,将余额改成500元 | |
| 5 | 查询余额,500元 | |
| 6 | 放弃取款,回滚事务 | |
| 7 | 汇入100元,将余额改成600元 | |
| 8 | 提交事务 | 提交事务 |
事务错误，造成500元损失
问题：更新了别的事务没有提交的数据
解决：读已提交
不可重复读
不可重复读是指 A事务读取了B事务已经提交的更改数据。例:事务A两次读取数据,第一次余额400元,第二次余额1000元,而两次读取出现不一样的情况是因为在第一次和第二次读取数据之间的空档期,事务更改了数据.导致第二次读取数据出现偏差
问题：多次读取数据，但是读取的数据结果不同，可能是在读取之间，别的事务对数据进行了更新
解决：可重复读
幻读
A事务读取B事务提交的新增（删除）数据，这时A事务将出现幻读的问题。例:事务A先读取了表的数据,然后事务B又往表中添加了几行数据,导致A事务再次读取表的时候,会发现表的数据发生了变化
幻读和不可重复读很相似,不可重复读是针对于一条数据,而幻读是针对于多条数据
问题：多次读取表，但读取的数据有变化，可能是在读取之间，有别的事务对表进行了新增或删除
解决：串行化
丢失更新
第一类丢失更新
A事务撤销时，把已经提交的B事务的更新数据覆盖了
解决：任何隔离
第二类丢失更新
A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失
解决：可重复读
事务隔离
读未提交
允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）
读已提交
只允许事务读取已经被其他事务提交的变更。(因为只能读取已经被提交的事务,所以就能避免因为读取没提交的数据而产生的脏读)
可重复读
确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。（可以避免脏读和不可重复读，但幻读仍然存在）
串行化
确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低
mysql索引
索引概述
数据库索引是为了提高查询速度而对表字段附加的一种标识。简单来说，索引其实是一种数据结构
索引可以大大提高MySQL的检索速度
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
索引分类
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引：即一个索引包含多个列。
单列索引
普通索引
MySQL中基本索引类型，没有限制，允许在定义索引的列中插入重复值和空值，是为了查询数据更快
CREATE INDEX 索引名 ON 表名 (列名[(length)]); create index phone_index on member (phone);
唯一索引
索引列中的值必须是唯一的，但是允许为空值
CREATE UNIQUE INDEX 索引名 ON 表名(列名); create unique index address_index on member (address);
主键索引
是一种特殊的唯一索引，不允许有空值（主键约束，就是一个主键索引），就是创建主键
组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3)); create table test001 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名); create fulltext index remark_index on member (remark);
索引数据结构
索引的数据结构使用的是 B+树
B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
mysql视图
在进行查询时，会返回一张虚拟的表，将这张表转换成具体存在的表。视图是基于sql语句结果集的可视化的表
视图只是用来查看存储在别处的数据的设施，本身不包含数据，返回的数据也是从其他表检索出来的
创建
-- 语法： -- CREATE VIEW <视图名> AS (SELECT语句) -- 查看视图 select * from 视图名
视图数据修改
-- update 数据库表名 set 字段名1=字段值1,字段名2=字段值2,...字段名n=字段值n where 条件表达式; #和表的修改一样 update ss set username='cl' where userId=2; -- 注意:修改了视图的数据记录，对基表数据也有影响
mysql触发器
触发器是由事件来触发某个操作。这些事件包括insert语句、update语句和delete语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。
创建
CREATE TRIGGER 名称 -- 触发器名称 BEFORE/AFTER -- (二选一,表示在事件之前执行还是事件之后执行) UPDATE/INSERT/DELETE -- (三选一,指定在什么事件触发触发器,即增,删,改) ON 表名称 FOR EACH ROW -- (影响所有行) #触发器主体 sql语句; -- 创建触发器 此触发器作用:在对user表执行修改操作的时候执行对gxtime表的添加操作 create trigger t1 before update on user for each row insert into gxtime values(now());
删除
#删除触发器T1 DROP TRIGGER IF EXISTS T1;
查询
#在已知数据库内，查询触发器 SHOW TRIGGERS;
mysql存储过程
存储过程就是事先经过编译并存储在数据库中的一段 SQL 语句的集合，就是对数据库 SQL 语言层面的代码封装与重用
-- 创建 -- create procedure 存储过程名称() begin sql语句 end; -- 创建一个名为p2的存储过程 create procedure p2() begin DELETE FROM `user` WHERE userId = 4; end; -- 创建存储过程p21,先删除user表中的roleid=4的数据,再删除rolelist表中的roleid=4的数据 CREATE PROCEDURE p21 () BEGIN DELETE FROM `user` WHERE roleid = 4; DELETE FROM rolelist WHERE roleid=4; END; -- 调用 CALL p2(); -- 查询 -- SHOW CREATE PROCEDURE 存储过程名称; SHOW CREATE PROCEDURE p2; -- 删除 -- DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ; DROP PROCEDURE IF EXISTS p1 ;
数据库备份与还原
备份
-- 输入以下命令进行保存sql文件: mysqldump -uroot -p密码 数据库名称>保存地址及名称 mysqldump -uroot -proot sqlstudy>D:/Desktop/demo.sql
还原
-- 登录数据库: mysql -uroot -proot -- 还原到数据库: mysql -uroot -proot sqlstudy<D:/Desktop/demo.sql
范式
-
第一范式(1NF):数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性（当一列表中的同一行有多个元素时，不满足第一范式）。 -
第二范式(2NF):在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。只有一个主属性的表如果符合第一范式，那一定是第二范式。（当基本信息表为（学号，身份证号，姓名）时，学号->姓名，身份证号->姓名，所以姓名部分依赖，则不满足）
-
第三范式(3NF):在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。
主键与候选键
主键：
-
主键的属性值在表中必须唯一，不存在重复的值 -
主键的值不能为NULL，即不能为空值 -
每个关系表只能有一个主键
候选键：
由两个或多个属性可以组成候选键，候选键的属性称为主属性
-
候选键的属性值在表中必须唯一，不存在重复的值 -
候选键的值可以为空值(NULL) -
在关系表中可以存在多个候选键
区别：
-
可能存在多个候选键，但每个关系表只能有一个主键 -
候选键的属性值可以为空值，而主键的属性值不能为NULL -
主键是在候选键中选择的一个，作为关系表中唯一标识记录的标识符
JDBC编程
JDBC(java database connectivity) 指 Java 数据库连接，是一种标准Java应用编程接口（ JAVA API），用来连接 Java 编程语言和广泛的数据库
JDBC架构
DBC 的 API 支持两层和三层处理模式进行数据库访问，但一般的 JDBC 架构由两层处理模式组成：
-
JDBC API: 提供了应用程序对 JDBC 管理器的连接。 -
JDBC Driver API: 提供了 JDBC 管理器对驱动程序连接。
JDBC核心API
接口/类 | 功能 |
|---|---|
DriverManager类 | ①管理和注册数据库驱动 ②得到数据库连接对象 |
Connection接口 | 一个连接对象，可用于创建Statement和PreparedStatement对象 |
Statement接口 | 一个 SQL 语句对象，用于将 SQL 语句发送给数据库服务器 |
PreparedStatemen接口 | 一个 SQL 语句对象，是 Statement 的子接口 |
ResultSet接口 | 用于封装数据库查询的结果集，返回给客户端 Java 程序 |
连接数据库
普通连接
-
注册数据库驱动:// 两种方式:第二种方式比较常用,因为后面使用jdbc连接数据库通常是使用配置文件配置数据库信息,所以会配置成字符串,便于利用反射机制进行驱动注册,在开发中,就更倾向于后者 // 1 DriverManager.registerDriver(new com.mysql.jdbc.Driver()); // 2 Class.forName("com.mysql.jdbc.Driver"); -
获取数据库连接(地址,端口号(3306),用户名,密码,数据库名称)（1）.url: 统一资源定位符 url格式（2）.jdbc:mysql 是协议名称：是指JDBC连接方式（3）.localhost:3306 是主机：端口还可以写作127.0.0.1:3306（4）.sqlstudy：数据库名（5）.useUnicode: 是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true（6）.characterEncoding: 当useUnicode设置为true，给定编码，常用utf8，默认是：autodetect（高版本）（7）.useSSL: MySQL在高版本需要指明是否进行SSL连接 在mysql连接字符串url中加入ssl=true或者false即可（8）.serverTimezone: 设置时区 例如 serverTimezone=UTC（统一标准世界时间）或serverTimezone=Asia/Shanghai（中国时区）// sql5.7配置 String url = "jdbc:mysql://127.0.0.1:3306/sqlstudy?useUnicode=true&characterEncoding=UTF-8"; String user = "root"; String password = "root"; // 连接数据库，通过注册器获取到连接对象 Connection connection = DriverManager.getConnection(url,user,password); -
通过连接获取操作对象(执行sql语句)// 通过数据库连接对象创建数据库语句来操作对象 Statement statement = connection.createStatement();
-
使用相关方法执行sql语句(书写sql语句)// 创建sql语句 String sql = "select * from book"; // 执行sql语句 ResultSet resultSet = statement.executeQuery(sql);
-
获取结果注意：next()方法：返回一个boolean值，表示当前行的下一行是否有数据next方法是在resultSet执行sql语句后得到的虚拟表中执行操作若sql语句加入了where条件查询出来一条数据（仍看做一张表），在未执行next()方法时，游标指向第0行，每执行一次next()方法，游标就向下移动一行第一次执行时，返回true，指向单行数据，再次执行，指向单行数据下方，由于单行数据下方没有数据，所以返回false// 获取结果 resultSet.next(); // get**()：查询的sql字段是什么类型，**就是什么类型。两种参数，int是指第几列;String是指查询列的字段名称 System.out.println(resultSet.getString("book_name")); System.out.println(resultSet.getString(2)); -
释放资源：后打开的先关闭，这个步骤是不管报不报异常都会去做的步骤,如果不是释放资源,就会一直存在于内存resultSet.close(); statement.close(); connection.close();
配置文件连接
-
创建一个xxx.properties文件用于装配置信息，在配置文件中通过等号形成键值对的形式注意：配置文件汇中的字符串别加引号Driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/hqdata?useUnicode=true&characterEncoding=UTF-8 user=root password=root
-
访问配置文件连接数据库public static void main(String[] args) { // 获取properties配置文件，会将配置文件中的信息解析成键值对 ResourceBundle rb = ResourceBundle.getBundle("config.jdbc"); // 定义在外部，便于关闭资源 Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { Class.forName(rb.getString("Driver")); connection = DriverManager.getConnection(rb.getString("url"), rb.getString("user"), rb.getString("password")); statement = connection.createStatement(); String sql = "select * from user1"; resultSet = statement.executeQuery(sql); resultSet.next(); System.out.println(resultSet.getString(2)); } catch (Exception e) { e.printStackTrace(); } finally { closeAll(connection, statement, resultSet); } } -
关闭资源方法// 关闭所有资源的类 private static void closeAll(Connection connection, Statement statement, ResultSet resultSet) { if (resultSet != null) { try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } }
工具类
创建工具类三个步骤
-
将几个字符串定义在配置文件当中(地址,端口号,用户名,密码,数据库名称) -
创建工具类,创建方法getCon用于获取连接,返回Connection对象 -
创建方法用于释放资源.两个方法,同名,重载.三个参数的方法用于释放查询语句的资源,两个参数的用于释放DML语句的资源
public class JdbcUtil {
private static Connection connection = null;
// 注册驱动，获取连接
// 静态代码块，加载类时执行
static {
ResourceBundle bundle = ResourceBundle.getBundle("config.jdbc");
try {
Class.forName(bundle.getString("Driver"));
connection = DriverManager.getConnection(bundle.getString("url"), bundle.getString("user"), bundle.getString("password"));
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
// 获取执行sql语句的Statement对象
public static Statement getSta() throws SQLException {
return connection.createStatement();
}
// 获取预编译的PreparedStatement对象
public static PreparedStatement getSta(String sql) throws SQLException {
return connection.prepareStatement(sql);
}
// 方法重载
// 释放增删改资源
public static void closeAll(Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
// 释放查询资源
public static void closeAll(Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
测试类
public class TestMethod {
public static void main(String[] args) {
try {
// 通过工具类直接获取statement对象
Statement statement = JdbcUtil.getSta();
ResultSet resultSet = statement.executeQuery("select * from login");
resultSet.next();
System.out.println(resultSet.getString(1));
// 通过工具类关闭资源
JdbcUtil.closeAll(statement,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
操作数据库
在做DML语句的时候,因为是修改数据库表,所以最后返回的是一个int类型变量,表示影响的行数.
而做DQL语句的时候,因为是返回的数据库表的查询结果,所以会返回一个resultSet类型的结果.
添加数据
// 添加 String sql1 = "insert into t_teacher values(null,'张老师','4')"; // 增删改功能时使用executeUpdate()方法 // 返回int值表示改变的行数 int i = statement.executeUpdate(sql1); System.out.println(i);
删除数据
// 删除 String sql2 = "delete from t_teacher where id = 5"; int j = statement.executeUpdate(sql2); System.out.println(j);
修改数据
// 修改 String sql3 = "update user set username='cxkas' where userId=3"; int k = statement.executeUpdate(sql3); System.out.println(k);
查询信息
// 创建sql语句
String sql = "select * from book";
// 执行sql语句
ResultSet resultSet = statement.executeQuery(sql);
// 获取结果
// next()：在开始的时候，结果的游标是在第0行（没有数据），当执行next()方法后才会跳掉下一行，返回的结果显示下一行是否有数据
resultSet.next();
// get**()：查询的sql字段是什么类型，**就是什么类型。两种参数，int是指第几列;String是指查询列的字段名称
System.out.println(resultSet.getString("book_name"));
System.out.println(resultSet.getString(2));
ResultSet 接口中的注意事项：
① 如果光标在第一行之前，使用 rs.getXX()获取列值，报错：Before start of result set
② 如果光标在最后一行之后，使用 rs.getXX()获取列值，报错：After end of result set
③ 使用完毕以后要关闭结果集 ResultSet，再关闭 Statement，再关闭 Connection
练习:实现用户登录
package hqyj;
import java.sql.*;
import java.util.ResourceBundle;
import java.util.Scanner;
public class userLogin {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner sc = new Scanner(System.in);
String username = sc.nextLine();
String password = sc.nextLine();
// 获取配置文件的配置
ResourceBundle pro = ResourceBundle.getBundle("jdbc");
// 注册驱动
Class.forName(pro.getString("Driver"));
// 连接数据库 数据库配置从配置文件jbbc中获取
Connection con = DriverManager.getConnection(pro.getString("url"), pro.getString("user"), pro.getString("password"));
// 获取操作对象
Statement sta = con.createStatement();
// sql语句凭借
String sql = "select * from user where username='" + username+"'";
System.out.println(sql);
// 获取查询结果
ResultSet userData = sta.executeQuery(sql);
// 判断:没有数据,则未注册,如果密码正确则登陆成功,反之则密码错误
if (userData.next()) {
String u = userData.getString("username");
String p = userData.getString("password");
System.out.println(p);
if (username.equals(u)) {
if (password.equals(p)) {
System.out.println("登陆成功");
} else {
System.out.println("登陆失败,密码错误");
}
}
}else {
System.out.println("用户未注册");
}
// 释放资源
userData.close();
sta.close();
con.close();
}
}
sql注入问题
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
// 在定义sql类语句时 String name = scanner.next(); String sql = "select * from `user` where name = '" + name + "'" ; // 若name设置为 admin'or'aaa，则sql语句变成： // select * from `user` where name = 'admin'or'aaa'导致错误
sql注入解决
Statement 在进行输入插入的时候，都会发送一条SQL语句给数据库，数据库先编译SQL语句，然后执行，返回结果.这就是statement的工作原理.但是如果有一万条数据插入的语句，就会编译一万次，然后执行，就会造成效率低下
可通过PreparedStatement接口来解决，在进行数据插入的时候，会先发送SQL语句预编译，PreparedStatement就会引用预编译的结果，如果多次插入的内容相同的话，就只需要预编译一次，只是每一次执行SQL语句的时候参数不一样。这样就提高了效率
PreparedStatement(预编译)可以有效的防止 SQL 注入的问题，安全性更高
预编译使用步骤
-
编写 SQL 语句，未知内容使用?占位 -
获得 PreparedStatement 对象；预编译操作对象有个参数sql,是作为sql模板使用的 -
设置实际参数：setXxx(占位符的位置, 真实的值) -
执行参数化 SQL 语句；获取结果
//在connection获取到连接对象之后 // 预编译 String sql = "select * from login where name = ?"; PreparedStatement statement = connection.prepareStatement(sql); // 占位符数据替换 // 第一个参数表示第几个问号 statement.setString(1,name); resultSet = statement.executeQuery();
PreparedStatement方法
PreparedStatement中设置参数的方法 | 作用描述 |
|---|---|
void setDouble(int parameterIndex, double x) | 将指定参数设置为给定 Java double 值 |
void setLong(int parameterIndex, long x) | 将指定参数设置为给定 Java long 值 |
void setFloat(int parameterIndex, float x) | 将指定参数设置为给定 Java REAL 值 |
void setObject(int parameterIndex, Object x) | 使用给定对象设置指定参数的值 |
void setInt(int parameterIndex, int x) | 将指定参数设置为给定 Java int 值 |
void setString(int parameterIndex, String x) | 将指定参数设置为给定 Java String 值 |
表和类的关系
类的属性名称对应着表的字段名称.所以类对象的属性值就对应着表的一行的数据某个字段的值.多个类对象组成就可以表示一张表
①整个表可以当做是一个java类
②每一个表中的字段，当做是java类中的成员变量
③表的一行数据，当做是java类的一个实例对象
查询数据库数据
新建entity包，在包下新建login的实体类
public class TestLogin {
private String name;
private String password;
public TestLogin() {
}
public TestLogin(String name, String password) {
this.name = name;
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "TestLogin{" +
"name='" + name + '\'' +
", password='" + password + '\'' +
'}';
}
}
将查询到的结果放进实体类,如果只有一组数据的话,就直接存到一个类对象里面,如果是多组数据,例如slecet * from user,就声明一个ArrayList进行存储
public class TestMapper {
public static void main(String[] args) throws ClassNotFoundException {
ResourceBundle bundle = ResourceBundle.getBundle("config.jdbc");
Class.forName(bundle.getString("Driver"));
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
ArrayList<TestLogin> list = new ArrayList();
try {
connection = DriverManager.getConnection(bundle.getString("url"), bundle.getString("user"), bundle.getString("password"));
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from login");
while (resultSet.next()) {
TestLogin testLogin = new TestLogin();// 创建实体类对象
testLogin.setName(resultSet.getString(1));// 为其赋值
testLogin.setPassword(resultSet.getString(2));
list.add(testLogin);
}
System.out.println(list);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
// 释放资源
}
}
}
插入信息到数据库
public class JdbcMapperIn {
public static void main(String[] args) {
TestLogin testLogin = new TestLogin("333","333");
String sql = "insert into login values(?,?)";
try {
// 运用工具类
PreparedStatement preparedStatement = JdbcUtil.getSta(sql);
preparedStatement.setString(1,testLogin.getName());
preparedStatement.setString(2,testLogin.getPassword());
int i = preparedStatement.executeUpdate();
System.out.println(i);
JdbcUtil.closeAll(preparedStatement);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
数据库连接池？
数据库连接池实际上就是一个容器(集合),存放数据库连接的容器.当系统被初始化后,容器就被创建了.容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。简单来时,数据库连接池就是用来创建,分配,管理,释放数据库连接的.它允许应用程序重复使用一个现有的数据库连接，而不是重 新建立一个。
数据库连接池的基本思想就是为数据库连接建立一个缓冲池,预先在这个缓冲池中放入一定数量的数据库连接.当需要数据库链接的时候,就不用去创建,直接从缓冲池中拿一个.用完再放回去就行了
常用连接池
常用的主流开源数据库连接池有C3P0、DBCP、Tomcat Jdbc Pool、BoneCP、Druid等。
如果使用数据库连接池,则必须要增加几个配置.数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池 的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连 接数量时，这些请求将被加入到等待队列中。
jdbc.properties
Driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/sqlstudy?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true username=root password=root #初始化创建连接的个数, initialSize=10 #最大连接数 maxActive=20 #获取连接时最大等待时间 maxWait=1000
Druid数据库连接池
Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了 日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的 连接池之一。
package hqyj;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class ConPool {
public static void main(String[] args) throws Exception {
Properties pro = new Properties();
pro.load(new FileInputStream("src/jdbc.properties"));
// 创建数据库连接池
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
// 获取数据库连接
Connection con = ds.getConnection();
Statement sta = con.createStatement();
String sql = "select * from user";
ResultSet re = sta.executeQuery(sql);
while (re.next()) {
System.out.println(re.getInt(1));
System.out.println(re.getString(2));
System.out.println(re.getString(3));
System.out.println(re.getInt(4));
}
re.close();
sta.close();
con.close();
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
