int(11) VS int(21)
存储空间，还是存储范围有区别？
答案是：两者完全一样，只是在显示的时候补全0的位数不一样。
可以通过下面的例子来验证：
create table t(a int(11) zerofill, b int(21) zerofill); insert into t values (1, 1); select * from t;
MySQL默认是不带0补全的。
只是在一些特殊情况下两者显示有区别，其本质完全一样。
浮点型 FLOAT(M, D) DOUBLE(M, D) 属性 存储空间 精度 精确性 Float 4 bytes 单精度 非精确 Double 8 bytes 双精度 比Float精度高 精度丢失问题 精度丢失一个例子：
create table t(a int(11), b float(7, 4)); insert into t values (2, 123.12345); select * from t;定点数-更精确的数字类型 DECIMAL
一般使用tinyint、char(1)、enum类型。
经验之谈 存储性别、省份、类型等分类信息时选择TINYINT或者ENUM BIGINT存储空间更大，INT和BIGINT之间通常选择BIGINT 交易等高精度数据选择使用DECIMAL 存储用户名的属性 CHAR VARCHAR TEXT CAHR与VARCHAR CHAR和VARCHAR存储的单位都是字符 CHAR存储定长，容易造成空间的浪费 VARCHAR存储变长，节省存储空间 字符与字节的区别 编码\输入字符串 网易 netease gbk(双字节) varchar(2)/4 bytes varchar(7)/7 bytes utf8(三字节) varchar(2)/6 bytes varchar(7)/7 bytes utf8mb4(四字节) varchar(2) ? varchar(7)/7 bytes对于utf8mb4号称占用四字节但是并不绝对。如果在utf8可以覆盖到的范围则仍然占用3字节。
utf8mb4最有优势的应用场景是用于存储emoji表情
emoji表情 MySQL版本 > 5.5.3 JDBC驱动版本 > 5.1.13 库和表的编码设为utf8mb4 TEXT与CHAR和VARCHAR的区别 CHAR和VARCHAR存储单位为字符 TEXT存储单位为字节，总大小为65535字节，约为64KB CHAR数据类型最大为255字符 VARCHAR数据类型为变长存储，可以超过255个字符 TEXT在MySQL内部大多存储格式为溢出页，效率不如CHAR一个例子：
create table t (a char(256)); create table t (a varchar(256));存储头像 BLOB BINARY
性能太差，不推荐
经验之谈 CHAR与VARCHAR定义的长度是字符长度不是字节长度 存储字符串推荐使用VARCHAR(N),N尽量小 虽然数据库可以存储二进制数据，但是性能低下，不要使用数据库存储文件音频等二进制数据 存储生日信息 DATE TIME DATETIME TIMESTAMP BIGINT 时间类型的区别在哪里存储空间上的区别
DATE三字节，如：2015-05-01 TIME三字节，如：11:12:00 TIMESTAMP，如：2015-05-01 11::12:00 DATETIME八字节，如：2015-05-01 11::12:00存储精度的区别
DATE精确到年月日 TIME精确到小时分钟和秒 TIMESTAMP、DATETIME都包含上述两者 TIMESTAMP VS DATETIME 存储范围的区别MySQL在5.6.4版本之后，TimeStamp和DateTime支持到微妙
字段类型与市区的关联关系一个例子：
create table test (a datetime, b timestamp); select now(); insert into test values (now(), now()); select * from test; set time_zone = '+00:00'; select * from test;BIGINT如何存储时间类型 应用程序将时间转换为数字类型 mysql数据对象">2.2-MySQL数据对象 MySQL常见的数据对象有哪些 DataBase/Schema Table Index View/Trigger/Function/Procedure 库、表、行层级关系 一个DataBase对应一个Schema 一个Schema包含一个或多个表 一个表里面包含一个或多个字段 一个表里包含一条或多条记录 一个表包含一个或多个索引 多DataBase用途 业务隔离 资源隔离 表上有哪些常用的数据对象 索引 约束 视图、触发器、函数、存储过程 什么是数据库索引 读书的时候如何快速定位某一章节
我要评论
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
如何创建索引（二）
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
ADD [COLUMN] (col_name column_definition,...)
ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
约束
生活中的约束有哪些以如下这张表为例
CREATE TABLE `order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `orderid` int(10) unsigned NOT NULL, `bookid` int(10) unsigned NOT NULL DEFAULT '0', `userid` int(10) unsigned NOT NULL DEFAULT '0', `number` tinyint(3) unsigned NOT NULL DEFAULT '0', `address` varchar(128) NOT NULL DEFAULT '', `postcode` varchar(128) NOT NULL DEFAULT '', `orderdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `status` tinyint(3) unsigned zerofill DEFAULT '000', PRIMARY KEY (`id`), UNIQUE KEY `idx_orderid` (`orderid`), UNIQUE KEY `idx_uid_orderid` (`userid`, `orderid`), KEY `bookid` (`bookid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8索引有哪些
show privileges命令可以查看全部权限
权限粒度 Data Privileges
GRANT
priv_type [(column_list)]
[, priv_type [column_list]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
如何新建一个用户并赋权
使用MySQL自带的命令DROP USER user [, user] ...With Grant Option 允许被授予权利的人把这个权利授予其他的人 MySQL权限信息存储结构 MySQL权限信息是存在数据库表中 MySQL账号对应的密码也加密存储在数据库表中 每一种权限类型在元数据里都是枚举类型，表明是否有该权限 有哪些权限相关的表 user db table_pirv columns_pirv host 权限验证流程
查询时从user->db->table_pirv->columns_pirv依次验证，如果通过则执行查询。
小结 MySQL权限信息都是以数据记录的形式存储在数据库的表中。 MySQL的权限验证相比网站登录多了白名单环节，并且粒度更细，可以精确到表和字段。 MySQL权限上有哪些问题 使用Binary二进制安装管理用户没有设置密码 MySQL默认的test库不受权限控制，存在安全风险 mysql_secure_installation You can set a Password for root accounts. You can remove root accounts that are accessible from outside the localhost. You can remove anonymous-user accounts. You can remove the test database. 小结 权限相关的操作不要直接操作表，统一使用MySQL命令。 使用二进制安装MySQL安装后，需要重置管理用户(root)的密码。 线上数据库不要留test库 实践课：数据库对象 何为表结构设计 表结构设计需要在正式进行开发之前完成 根据产品需求将复杂的业务模型抽象出来 设计表的时候需要注意哪些 理解各个表的依赖关系 理解各个表的功能特点create table tb_account( account_id int not null auto_increment primary key, nick_name varchar(20), true_name varchar(20), sex char(1), mail_address varchar(50), phone1 varchar(20) not null, phone2 varchar(20), password varchar(30) not null, create_time datetime, account_state tinyint, last_login_time datetime, last_login_ip varchar(20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 create table tb_goods( good_id bigint not null auto_increment primary key, goods_name varchar(100) not null, pic_url varchar(500) not null, store_quantity int not null, goods_note varchar(4096), producer varchar(500), category_id int not null ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 create table tb_goods_category( category_id int not null auto_increment primary key, category_level smallint not null, category_name varchar(500), upper_category_id int not null ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 create table tb_order( order_id bigint not null auto_increment primary key, account_id int not null, create_time datetime, order_amount decimal(12,2), order_state tinyint, update_time datetime, order_ip varchar(20), pay_method varchar(20), user_notes varchar(500) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 create table tb_order_item( order_item_id bigint not null auto_increment primary key, order_id bigint not null, goods_id bigint not null, goods_quantity int not null, goods_amount decimal(12,2), uique key uk_order_goods(order_id, goods_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4数据类型——命名规范 所有表名，字段名全部使用小写字母 不同业务，表名使用不同前缀区分。 生成环境表名字段名要有实际意义 单个字段尽量使用字段全名；多个字段之间用下划线分隔 字段设计规范 字段类型选择，尽量选择能满足应用要求的最小数据类型 尽量使用整形代替字符型。整形在字段长度、索引大小等方面开销小效率更高，如邮编字段，手机号码等 注释，每个字段必须以comment语句给出字段的作用 经常访问的大字段需要单独放到一张表中，避免降低sql效率，图片、电影等大文件数据禁止存数据库 新业务统一建议使用utf8mb4字符集 用户赋权 理解用户到底需要什么权限
本课程涉及建表SQL
-- 查看全部歌单 select * from play_list; -- 按创建时间排序 select * from play_list order by createtime; -- MySQL默认升序，如果按降序排列，则使用如下语句。 select * from play_list order by createtime desc; -- 也可以按照多个字段来排序 select * from play_list order by bookedcount, trackcount;SQL进阶语法——distinct
场景2：统计云音乐创建歌单的用户
-- 有重复 select userid from play_list; -- 去重 select distinct userid from play_list; -- 多个字段 select distinct userid, play_name from play_list;distinct用于返回唯一不同的值 可以返回多列的唯一组合 底层实现使用排序，如果数据量大会消耗较多的IO和CPU SQL进阶语法——group by
场景3-1：统计云音乐创建歌单的用户列表和每人创建歌单的数量。
-- 每个用户歌单的最大订阅数 select userid, max(bookedcount) from play_list group by userid; -- 每个用户歌单的数量 select userid, count(*) from play_list group by userid;group by 根据单列或多列对数据进行分组，通常结合聚合函数使用，如count(*). SQL进阶语法——group by having
场景3-2：统计云音乐创建歌单的用户列表和每人创建歌单的数量，并且只显示歌单数量排序大于等于2的用户
select userid, count(*) from play_list group by userid having count(*) >= 2;having 是对结果进行过滤
SQL进阶语法-like
select * from play_list where play_name like '%男孩%';通配符 描述 % 代替一个或多个字符 _ 代替单个字符 [charlist] 中括号中的任何一个字符 [^charlist] 或者 [!charlist] 不在中括号中的任何单一字符
* 除了百分号在最右面的情况以外，他会对这个表中所有的记录进行一次查询匹配，而没办法使用索引，效率较低。大表中需要慎用like。可以使用全文检索的手段。
SQL进阶语法-limit, offset场景4：查询一个月内创建歌单（从第6行开始显示10条记录）
select * from play_list where (createtime between 1427791323 and 1430383307) limit 10 offset 6;offset后的值不建议太大，需要消耗的IO较大 case when case when 实现类似编程语言的if else功能，可以对SQL的输出结果进行选择判断。
场景5：对于未录入歌曲的歌单(trackcount = null)，输出结果时歌曲数返回0.
select case when play_name, trackcount is null then 0 else trackcount end from play_list;select相关进阶语法
SELECT
[DISTINCT]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT { [offset, ] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]]
连接-Join
连接的作用是用一个SQL语句把多个表中相互关联的数据查出来
场景6：查询收藏“老男孩”歌单的用户列表
select * from play_list, play_fav where play_list.id=play_fav.play_id; select play_fav.userid from play_list, play_fav where play_list.id=play_fav.play_id and play_list.play_name='老男孩'; -- 另一种写法 select f.userid from play_list lst join play_fav f on lst.id=f.play_id where lst.play_name = '老男孩';子查询 MySQL还有另一种写法，可以实现同样的功能。
select userid from play_fav where play_id=(select id from play_list where play_name = '老男孩');
子查询：内层查询的结果作为外层的比较条件。一般子查询都可以转换成连接，推荐使用连接。
不利于MySQL的查询优化器进行优化，可能存在性能问题 连接的实现是嵌套循环，选择一个驱动表，遍历驱动表，查询内层表，依次循环。驱动表会至少查询一边，如果有索引等，内层表可以非常快，查询优化器会选择数据小的表作为驱动表。 子查询由人为规定驱动表和内层表 连接- left Joinselect lst.play_name from play_list lst left join play_fav f on lst.id = f.play_id where f.play_id is null;LEFT JOIN从左表(play_list)返回所有的行，即使在右表中(play_fav)中没有匹配的行。 与LEFT JOIN相对应的有RIGHT JOIN关键字，会从右表那里返回所有的行，即使在左表中没有匹配的行。
场景7：查询出没有用户收藏的歌单
SQL进阶语法-union场景8：老板想看创建和收藏歌单的所有用户，查询play_list和play_fav两表中所有的userid
select userid from play_list union select userid from play_fav; -- 默认会去重， 不想去重的话使用union all代替union。DML进阶语法 多值插入： insert into table values(….),(….)
实例还是上节中的那些表
场景1：查询每张专辑总的点播次数和每首歌的平均点播次数。
select album, sum(playcount), avg(playcount) from song_list group by album;
场景2：查询全部歌曲中的最大的播放次数和最小的播放次数。
select max(playcount), min(playcount) from song_list;
场景2续：查询播放次数最多的歌曲
-- 错误查法 select song_name, max(playcount) from song_list; -- 正确查法 select song_name, playcount from song_list order by playcount desc limit 1;select count(*) from song_list; select count(1) from song_list; select count(song_name) from song_list;
count(*)和count(1)基本一样，没有明显的性能差异。
count(*)和count(song_name)差别在于count(song_name)会除去song_name is null的情况
场景3：显示每张专辑的歌曲列表
select album, GROUP_CONCAT(song_name) from song_list group by album; -- 默认最大只能连接1024个字符，但是可以通过改数据库参数来改变。使用聚合函数做数据库行列转换
select user, max(case when 'key'='age' then value end) age, max(case when 'key'='sex' then value end) sex, max(case when 'key'='dep' then value end) dep, from tbl_test1 group by user;预定义函数 预定义函数面向单值数据，返回一对一的处理结果(聚合函数可以理解成多对一)。 预定义函数基本语法：
* 字符串函数 - 实例
SELECT SUBSTRING('abcdef', 3);
-- 'cdef'
SELECT SUBSTRING('abcdef', -3);
-- 'def'
SELECT SUBSTRING('abcdef', 3, 2);
-- 'cd'
SELECT LOCATE('bar', 'foobarbar');
-- 4
SELECT LOCATE('xbar', 'foobar');
-- 0
SELECT LOCATE('bar', 'foobarbar', 5);
-- 7
预定义函数-时间处理函数
函数
描述
CURDATE()
当前日期
CURTIME()
当前时间
NOW()
显示当前时间日期(常用)
UNIX_TIMESTAMP()
当前时间戳
DATE_FORMAT(date, format)
按指定格式显示时间
DATE_ADD(date, INTERVAL unit)
计算指定日期向后加一段时间的日期
DATE_SUB(date, INTERVAL unit)
计算指定日期向前减一段时间的日期
* 实例：
SELECT NOW() + INTERVAL 1 MONTH; SELECT NOW() - INTERVAL 1 WEEK;预定义函数-数字处理函数 函数 描述 ABS() 返回数值的绝对值 CEIL() 对小数向上取整 CEIL(1.2)=2 ROUND() 四舍五入 POW(num, n) num的n次幂 POW(2, 2)=4 FLOOR() 对小数向下取整 CELL(1.2)=1 MOD(N, M) 取模(返回n除以m的余数)=N % M RAND() 取0~1之间的一个随机数 算数、逻辑运算 比较运算 函数 描述 IS, IS NOT 判定布尔值 IS True, IS NOT False, IS NULL , >= 大于，大于等于 <, <= 小于，小于等于 = 等于 !=, <> 不等于 BETWEEN M AND N 取M和N之间的值 IN, NOT IN 检查是否在或不在一组值之中
实例：查询一个月内userid为1,3,5的用户创建的歌单
select * from play_list where (createtime between 1427791323 and 1430383307) and userid in (1,3,5);*,/,DIV,%,MOD,-,+ NOT, AND, &&, XOR, OR, || 2.6-触发器与存储过程 触发器 是什么
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_body t
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
触发器-实例
学生表：
CREATE TABLE `stu` ( `name` varchar(50), `course` varchar(50), `score` int(11), PRIMARY KEY (`name`) ) ENGINE=InnoDB;
用于更正成绩的触发器：
DELIMITER //
CREATE TRIGGER trg_upd_score
BEFORE UPDATE ON `stu`
FOR EACH ROW
BEGIN
IF NEW.score < 0 THEN
SET NEW.score = 0;
ELSEIF NEW.score > 100 THEN
SET NEW.score = 100;
END IF;
END; //
DELIMITER ;
注意事项
触发器对性能有损耗，应慎重使用。 同一类事件在一个表中只能创建一次。 对于事务表，触发器执行失败则整个语句回滚。 Row格式的主从复制，触发器不会在从库上执行。 使用触发器时应防止递归执行。
存储过程
定义：存储过程是存储在数据库的一组SQL语句集，用户可以通过存储过程名和传参多次调用的程序模块。 特点：
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| [NOT] DETERMINISTIC
routine_body:
Valid SQL routine statement
存储过程-实例
CREATE PROCEDURE proc_test1
(IN total INT, OUT res INT)
BEGIN
DECLARE i INT;
SET i = 1;
SET res = 1;
IF total <= 0 THEN
SET total = 1;
END IF;
WHILE i <= total DO
SET res = res * i;
INSERT INTO tbl_proc_test(num) VALUES (res);
SET i = i + 1;
END WHILE;
END;
存储过程-流控制语句
流控制
描述
IF
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list][ELSE statement_list] END IF
CASE
CASE case_value WHEN when_value THEN statement_list [ELSE statement_list] END CASE
WHILE
WHILE search_condition DO statement_list END WHILE
REPEAT
REPEAT statement_list UNTIL search_condition END REPEAT
存储过程-调用
set @total=10; set @res=1; call proc_test1(@total, @res); select @res;自定义函数 自定义函数与存储过程类似，但是必须带有返回值(RETURN)。 自定义函数与sum(), max()等MySQL原生函数使用方法类似：
CREATE
[DEFINER = { user | CURRENT_USER}]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| [NOT] DETERMINISTIC
routine_body:
Valid SQL routine statement
自定义函数-实例
CREATE FUNCTION func_test1 (total INT)
RETURNS INT
BEGIN
DECLARE i INT;
DECLARE res INT;
SET i = 1;
SET res = 1;
IF total <= 0 THEN
SET total = 1;
END IF;
WHILE i < total DO
SET res = res * i;
SET i = i + 1;
END WHILE;
RETURN res;
END;
自定义函数-调用
select func_test1(4);小结 知识点：触发器、存储过程、自定义函数 互联网场景：触发器和存储过程不利于水平扩展，多用于统计和运维操作中。 2.7-MySQL字符集 字符集基础 字符集：数据库中的字符集包含两层含义
SHOW CHARACTER SET;新增字符集
# 编译时加入： --with-charset= ./configure --prefix=/usr/local/mysql3 --with-plugins=innobase --with-charset=gbk字符集与字符序 charset和collation
查看字符序
show collation;
mysql的字符序遵从命名惯例：以_ci(表示大小写不敏感)，以_CS(表示大小写敏感)，以_bin(表示用编码值进行比较)。
字符集设置级别 charset和collation的设置级别：配置文件 [mysqld] character_set_server=utf8 collation_server=utf8_general_ci数据库级
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;character_set_database：当前选中数据库的默认字符集
主要影响load data等语句的默认字符集，CREATE DATABASE的字符集如果不设置，默认使用character_set_server的字符集。
表级CREATE TABLE tbl1 (....) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;列级
CREATE TABLE tbl1 (col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);字符集设置级别 数据存储字符集使用规则：
-- 查看字符集 show [global] variables like 'character%'; show [global] variables like 'collation%'; -- 修改字符集 set global character_set_server=utf8; -- 全局 alter table xxx convert to character set xxx; -- 表客户端连接与字符集 连接与字符集
mysql > set names utf8; 配置文件设置： [mysql] default-character-set=utf8字符转换过程
client > character_set_client > character_set_connection > Storage > character_set_results >client
推荐使用统一的字符集
常见乱码原因：
数据存储字符集不能正确编码(不支持)client发来的数据：client(utf8)->Storage(latin1) 程序连接使用的字符集与通知mysql的character_set_client等不一致或不兼容。使用建议
创建数据库/表时显式的指定字符集，不使用默认。 连接字符集与存储字符集设置一致，推荐使用utf8。 驱动程序连接时显式指定字符集(set names XXX).mysql CAPI:初始化数据库句柄后马上用mysql_options设定MYSQL_CHARSET_NAME属性为utf8.
mysql php API:连接到数据库以后显式用SET NAMES语句设置一次连接字符集。 mysql JDBC: url=”jdbc:mysql://localhost:3306/blog_dbo?user=xx&password=xx&userUnicode=true&characterEncoding=utf8” 小结 字符集：表示的字符集和/字符编码方式 字符的设置级别：服务器/数据库/表/列 客户端字符集：乱码产生的原因与解决方式 2.8程序连接MySQL 程序连接MySQL基本原理JDBC客户端应用 -> java.sql.*或javax.sql.* -> 驱动程序 -> SQLserver/Oracle/MySQL
Java代码示例结构：
DriverManager
-> Driver(是驱动程序对象的接口，指向具体数据库驱动程序对象)=DriverManager.getDriver(String URL)
-> Connectinon(是连接对象接口，指向具体数据库连接对象)=DriverManager.getConnection(String URL)
-> Statement(执行静态SQL语句接口)=Connection.CreateStatement()
-> ResultSet(是指向结果集对象的接口)=Statement.excuteXXX()
import java.sql.*;
/**
* 使用JDBC连接MySQL
*/
public class DBTest {
public static Connection getConnection() throws SQLException,
java.lang.ClassNotFoundException
{
//第一步：加载MySQL的JDBC的驱动
Class.forName("com.mysql.jdbc.Driver");
//设置MySQL连接字符串,要访问的MySQL数据库 ip,端口,用户名,密码
String url = "jdbc:mysql://localhost:3306/blog";
String username = "blog_user";
String password = "blog_pwd";
//第二步：创建与MySQL数据库的连接类的实例
Connection con = DriverManager.getConnection(url, username, password);
return con;
}
public static void main(String args[]) {
Connection con = null;
try
{
//第三步：获取连接类实例con，用con创建Statement对象类实例 sql_statement
con = getConnection();
Statement sql_statement = con.createStatement();
/************ 对数据库进行相关操作 ************/
//如果同名数据库存在，删除
sql_statement.executeUpdate("drop table if exists user;");
//执行了一个sql语句生成了一个名为user的表
sql_statement.executeUpdate("create table user (id int not null auto_increment," +
" name varchar(20) not null default 'name', age int not null default 0, primary key (id) ); ");
//向表中插入数据
System.out.println("JDBC 插入操作:");
String sql = "insert into user(name,age) values('liming', 18)";
int num = sql_statement.executeUpdate("insert into user(name,age) values('liming', 18)");
System.out.println("execute sql : " + sql);
System.out.println(num + " rows has changed!");
System.out.println("");
//第四步：执行查询，用ResultSet类的对象，返回查询的结果
String query = "select * from user";
ResultSet result = sql_statement.executeQuery(query);
/************ 对数据库进行相关操作 ************/
System.out.println("JDBC 查询操作:");
System.out.println("------------------------");
System.out.println("userid" + " " + "name" + " " + "age ");
System.out.println("------------------------");
//对获得的查询结果进行处理，对Result类的对象进行操作
while (result.next())
{
int userid = result.getInt("id");
String name = result.getString("name");
int age = result.getInt("age");
//取得数据库中的数据
System.out.println(" " + userid + " " + name + " " + age);
}
//关闭 result,sql_statement
result.close();
sql_statement.close();
//使用PreparedStatement更新记录
sql = "update user set age=? where name=?;";
PreparedStatement pstmt = con.prepareStatement(sql);
//设置绑定变量的值
pstmt.setInt(1, 15);
pstmt.setString(2, "liming");
//执行操作
num = pstmt.executeUpdate();
System.out.println("");
System.out.println("JDBC 更新操作:");
System.out.println("execute sql : " + sql);
System.out.println(num + " rows has changed!");
//关闭PreparedStatement
pstmt.close();
//流式读取result，row-by-row
query = "select * from user";
PreparedStatement ps = (PreparedStatement) con.prepareStatement
(query,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
result = ps.executeQuery();
/************ 对数据库进行相关操作 ************/
System.out.println("JDBC 查询操作:");
System.out.println("------------------------");
System.out.println("userid" + " " + "name" + " " + "age ");
System.out.println("------------------------");
//对获得的查询结果进行处理，对Result类的对象进行操作
while (result.next())
{
int userid = result.getInt("id");
String name = result.getString("name");
int age = result.getInt("age");
//取得数据库中的数据
System.out.println(" " + userid + " " + name + " " + age);
}
//关闭 result,ps
result.close();
ps.close();
con.close();
} catch(java.lang.ClassNotFoundException e) {
//加载JDBC错误,所要用的驱动没有找到
System.err.print("ClassNotFoundException");
//其他错误
System.err.println(e.getMessage());
} catch (SQLException ex) {
//显示数据库连接错误或查询错误
System.err.println("SQLException: " + ex.getMessage());
}
}
}
JDBC使用技巧
Statement与PreparedStatement的区别 connection, Statement与ResultSet关闭的意义 jdbc连接参数的使用 ResultSet游标的使用(setFetchSize)
Statement与PreparedStatement的区别
PreparedStatement在数据库端预编译，效率高，可以防止SQL注入。 对数据库执行一次性存取的时候，用Statement对象进行处理。 线上业务推荐使用PreparedStatement.
PreparedStatement背后的故事
PREPARE -> EXECUTE -> DEALLOCATE PREPARE
PREPARE stmt1 FROM 'SELECT productCode, productName
From products
WHERE productCode = ?';
SET @pc = 'S10_1678';
EXECUTE stmt1 USING @pc;
DEALLOCATE PREPARE stmt1;
connection, Statement与ResultSet关闭的意义
MySQL数据库端为connection与ResultSet维护内存状态，一直不关闭会占用服务端资源。 MySQL最大连接数受max_connections限制，不能无限创建连接，所以用完要及时关闭。 JDBC connection关闭后ResultSet, Statement会自动关闭。但是如果使用连接池将不会关闭，因此推荐主动关闭。
jdbc连接参数的使用
字符集设置：
url=”jdbc:mysql://localhost:3306/blog_dbo?userUnicode=true&characterEncoding=utf8”;
超时设置：url=”jdbc:mysql://localhost:3306/blog_dbo?connectionTimeout=1000&socketTimeout=30000”;
ResultSet游标的使用 默认的ResultSet对象不可更新，仅有一个向前移动的指针。因此，只能迭代它一次，并且只能按从第一行到最后一行的顺序进行。可以生成可滚动和/或可更新的ResultSet对象。 setFetchSize()是设置ResultSet每次向数据库取的行数，防止数据返回量过大将内存爆掉。 Python连接MySQL Python：脚本语言，无需编译、易开发 DBA使用Python的一般场景是编写自动化运维工具、报表、数据迁移 Python MySQL驱动：python-mysqldb
import MySQLdb
# 建立和mysql数据库的连接
conn = MySQLdb.connect(host='localhost', port=3306,user='bloguser',passwd='xxxx')
# 获取游标
curs = conn.cursor()
# 选择数据库
conn.select_db('blog')
# 执行SQL，创建一个表
curs.execute("create table blog (id int, name varchar(200))")
# 插入一条记录
value = [1, 'user1']
curs.execute("insert into blog values(%s, %s)", value)
# 插入多条记录
values = [(2, "user2"), (3, "user3")]
curs.executemany("insert into blog values(%s, %s)", values)
# 提交
conn.commit()
# 关闭游标
curs.close()
# 关闭连接
conn.close()
2.9-DAO框架的使用
DAO框架
在应用程序中使用数据访问对象(DAO),使我们可以将底层数据访问逻辑与业务逻辑分离开来。DAO框架构建了为每一个数据源提供CRUD(创建、读取、更新、删除)操作的类。 DAO模式是标准J2EE设计模式之一。开发人员用这种模式将底层数据访问操作与高层业务逻辑分离开。一个典型的DAO框架实现有以下组操作：示例代码在sorence/DAO框架代码示例.rar
MyBatis工作流程 加载配置并初始化，内部生成MappedStatement对象。 调用MyBatis提供的API(SqlSession.select/insert….)，将SQL ID与数据对象传递给处理层。 处理层解析MappedStatement对象，获取MySQL的连接，执行相应的SQL语句，接收返回结果。 MyBatis将接收到的返回结果封装成对应的数据对象返回。 MyBatis使用技巧 区分#{}和${}的不同应用场景：
如对本文有疑问， 点击进行留言回复！！
服务器Centos部署MySql并连接Navicat过程详解
网友评论