我爱编程

MySQL(2)数据库对象与应用

2017-01-09  本文已影响0人  极客圈

MySQL数据库对象与应用

2.1-MySQL数据类型

库建立好之后基本不动,和我们接触最频繁的是表. 建表就是声明字段的过程!

Number有2种

整形

选择合适的类型[速度快 减少硬盘占用]

type Storage Minumun Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127 最高位是符号位
0 255 = 2^8-1
SMALLINT 2 -32768 32767 = 2^15-1
0 65535 = 2^16-1
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

老生常谈的问题int(11) VS int(21)

存储空间，还是存储范围有区别？
答案：两者本质完全一样，只是在一些特殊情况下两者显示有区别(只是在显示的时候补全0的位数不一样)
实验

create table t(a int(11) zerofill, b int(21) zerofill); -- MySQL默认是不带0补全 请在字段定义后加可选属性 如:零补全zerofill
insert into t values (1, 1);
select * from t;

+-------------+-----------------------+
| a           | b                     |
+-------------+-----------------------+
| 00000000001 | 000000000000000000001 |
+-------------+-----------------------+
字段的可选属性

*zerofill 零填充(本字段同时即自动带有unsigned属性,因为负数不能零填充)
如 数字2在固定宽度4时 零填充 即为0002

agetest tinyint(5); --此时字段agetest属性为tinyint M值(固定宽度值)为5 即字段agetest在zerofill时,保持固定宽度5

M值是一个整数(固定宽度值)，只有在字段有零填充zerofill属性时 规定M值才有意义！

M值只是显示效果,不会影响实际数据值！
如M值为1,实际值255,一样会显示255
列可以声明默认值(推荐声明)

not null default 0  # 该字段默认为0

因为null无法和别的值比较
null = 0 返回null
null <> 0 返回null

null只能用is或is not比较 null is null当然对的。

浮点型

属性 存储空间 精度 精确性
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;

+-------------+-----------------------+
| a           | b                     |
+-------------+-----------------------+
| 00000000002 | 000000000000000000123 |
+-------------+-----------------------+

【浮点型】有误差，不稳定！定点数更精确。
实际测试数据
Float(M,D)
M精度(总位数,不包含点) 精度值M 影响 存储的 值的范围.
D标度(小数位) 小数点后有几位（mysql比较特殊，mssql/oracle都不能指定）
testcolumn float(5,2) unsigned; 范围0到999.99
float(5,2)的范围-999.99到999.99

给float(5,2)这样的字段插入值在进位时有一些规矩:暂时没搞清楚，不是简单的四舍五入
插入值688.826实际是688.83 末尾6 进位
插入值688.825实际是688.83 末尾5 进位
插入值688.824实际是688.82 末尾4 舍去
插入值688.005实际是688.00
插入值688.015实际是688.01 末尾5 5前面是1 舍去
插入值688.025实际是688.02 末尾5 5前面是2 舍去
插入值688.035实际是688.03 末尾5 5前面是3 舍去
插入值688.045实际是688.04 末尾5 5前面是4 舍去

定点数-更精确的数字类型

性别、省份信息

一般使用tinyint、char(1)、enum类型。

经验之谈

存储用户名的属性

CAHR与VARCHAR

varchar(M)
M代表宽度 即可容纳的【字符数】 (并不是字节数) varchar占用的字节数与编码有关:
utf-8 一个汉字3字节　英文字母1字节

字符与字节的区别

编码\输入字符串 测下 abcdef
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号称占用4字节但是并不绝对(在utf8可以覆盖到的范围则仍然占用3字节)

utf8mb4最有优势的应用场景:存储emoji表情

emoji表情

TEXT与CHAR和VARCHAR的区别

例子：

create table t (a char(256));
create table t (a varchar(256));

存储头像

性能太差，不推荐

经验之谈

存储生日信息

日期/时间型

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如何存储时间类型

2.2-MySQL数据对象

MySQL常见的数据对象

库、表、行层级关系

多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

添加唯一约束

外键约束

创建外键约束

View

View的作用

创建View

Trigger

除此之外还有哪些

2.3-MySQL权限管理

连接MySQL的必要条件

数据有哪些权限

show privileges命令可以查看全部权限

权限粒度

MySQL赋权操作

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]

如何新建一个用户并赋权

其他方法

更简单的办法

查看用户的权限信息

如何更改用户的权限

如何更改用户密码

删除用户

DROP USER user [, user] ...

With Grant Option

MySQL权限信息存储结构

有哪些权限相关的表

权限验证流程

查询时从user->db->table_pirv->columns_pirv依次验证，如果通过则执行查询。

小结

MySQL权限上有哪些问题

mysql_secure_installation

小结

实践课：数据库对象

何为表结构设计

设计表的时候需要注意哪些

收集表属性

理解表的功能特点——数据用途

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

数据类型——命名规范

字段设计规范

用户赋权

2.4-SQL语言进阶

本课程涉及建表SQL

-- ----------------------------
-- Table structure for `play_fav`
-- ----------------------------
DROP TABLE IF EXISTS `play_fav`;
CREATE TABLE `play_fav` (
  `userid` bigint(20) NOT NULL COMMENT '收藏用户id',
  `play_id` bigint(20) NOT NULL COMMENT '歌单id',
  `createtime` bigint(20) NOT NULL COMMENT '收藏时间',
  `status` int(11) DEFAULT '0' COMMENT '状态，是否删除',
  PRIMARY KEY (`play_id`,`userid`),
  KEY `IDX_USERID` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌单收藏表';

-- ----------------------------
-- Records of play_fav
-- ----------------------------
INSERT INTO play_fav VALUES ('2', '0', '0', '0');
INSERT INTO play_fav VALUES ('116', '1', '1430223383', '0');
INSERT INTO play_fav VALUES ('143', '1', '0', '0');
INSERT INTO play_fav VALUES ('165', '2', '0', '0');
INSERT INTO play_fav VALUES ('170', '3', '0', '0');
INSERT INTO play_fav VALUES ('185', '3', '0', '0');
INSERT INTO play_fav VALUES ('170', '4', '0', '0');
INSERT INTO play_fav VALUES ('170', '5', '0', '0');

-- ----------------------------
-- Table structure for `play_list`
-- ----------------------------
DROP TABLE IF EXISTS `play_list`;
CREATE TABLE `play_list` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `play_name` varchar(255) DEFAULT NULL COMMENT '歌单名字',
  `userid` bigint(20) NOT NULL COMMENT '歌单作者账号id',
  `createtime` bigint(20) DEFAULT '0' COMMENT '歌单创建时间',
  `updatetime` bigint(20) DEFAULT '0' COMMENT '歌单更新时间',
  `bookedcount` bigint(20) DEFAULT '0' COMMENT '歌单订阅人数',
  `trackcount` int(11) DEFAULT '0' COMMENT '歌曲的数量',
  `status` int(11) DEFAULT '0' COMMENT '状态,是否删除',
  PRIMARY KEY (`id`),
  KEY `IDX_CreateTime` (`createtime`),
  KEY `IDX_UID_CTIME` (`userid`,`createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌单';

-- ----------------------------
-- Records of play_list
-- ----------------------------
INSERT INTO play_list VALUES ('1', '老男孩', '1', '1430223383', '1430223383', '5', '6', '0');
INSERT INTO play_list VALUES ('2', '情歌王子', '3', '1430223384', '1430223384', '7', '3', '0');
INSERT INTO play_list VALUES ('3', '每日歌曲推荐', '5', '1430223385', '1430223385', '2', '4', '0');
INSERT INTO play_list VALUES ('4', '山河水', '2', '1430223386', '1430223386', '5', null, '0');
INSERT INTO play_list VALUES ('5', '李荣浩', '1', '1430223387', '1430223387', '1', '10', '0');
INSERT INTO play_list VALUES ('6', '情深深', '5', '1430223388', '1430223389', '0', '0', '1');

-- ----------------------------
-- Table structure for `song_list`
-- ----------------------------
DROP TABLE IF EXISTS `song_list`;
CREATE TABLE `song_list` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `song_name` varchar(255) NOT NULL COMMENT '歌曲名',
  `artist` varchar(255) NOT NULL COMMENT '艺术节',
  `createtime` bigint(20) DEFAULT '0' COMMENT '歌曲创建时间',
  `updatetime` bigint(20) DEFAULT '0' COMMENT '歌曲更新时间',
  `album` varchar(255) DEFAULT NULL COMMENT '专辑',
  `playcount` int(11) DEFAULT '0' COMMENT '点播次数',
  `status` int(11) DEFAULT '0' COMMENT '状态,是否删除',
  PRIMARY KEY (`id`),
  KEY `IDX_artist` (`artist`),
  KEY `IDX_album` (`album`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌曲列表';

-- ----------------------------
-- Records of song_list
-- ----------------------------
INSERT INTO song_list VALUES ('1', 'Good Lovin\' Gone Bad', 'Bad Company', '0', '0', 'Straight Shooter', '453', '0');
INSERT INTO song_list VALUES ('2', 'Weep No More', 'Bad Company', '0', '0', 'Straight Shooter', '280', '0');
INSERT INTO song_list VALUES ('3', 'Shooting Star', 'Bad Company', '0', '0', 'Straight Shooter', '530', '0');
INSERT INTO song_list VALUES ('4', '大象', '李志', '0', '0', '1701', '560', '0');
INSERT INTO song_list VALUES ('5', '定西', '李志', '0', '0', '1701', '1023', '0');
INSERT INTO song_list VALUES ('6', '红雪莲', '洪启', '0', '0', '红雪莲', '220', '0');
INSERT INTO song_list VALUES ('7', '风柜来的人', '李宗盛', '0', '0', '作品李宗盛', '566', '0');

-- ----------------------------
-- Table structure for `stu`
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `id` int(10) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of stu
-- ----------------------------

-- ----------------------------
-- Table structure for `tbl_proc_test`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_proc_test`;
CREATE TABLE `tbl_proc_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_proc_test
-- ----------------------------
INSERT INTO tbl_proc_test VALUES ('11', '1');
INSERT INTO tbl_proc_test VALUES ('12', '2');
INSERT INTO tbl_proc_test VALUES ('13', '6');
INSERT INTO tbl_proc_test VALUES ('14', '24');
INSERT INTO tbl_proc_test VALUES ('15', '120');
INSERT INTO tbl_proc_test VALUES ('16', '720');
INSERT INTO tbl_proc_test VALUES ('17', '5040');
INSERT INTO tbl_proc_test VALUES ('18', '40320');
INSERT INTO tbl_proc_test VALUES ('19', '362880');
INSERT INTO tbl_proc_test VALUES ('20', '3628800');
INSERT INTO tbl_proc_test VALUES ('21', '1');
INSERT INTO tbl_proc_test VALUES ('22', '2');
INSERT INTO tbl_proc_test VALUES ('23', '6');
INSERT INTO tbl_proc_test VALUES ('24', '24');
INSERT INTO tbl_proc_test VALUES ('25', '1');
INSERT INTO tbl_proc_test VALUES ('26', '2');
INSERT INTO tbl_proc_test VALUES ('27', '6');
INSERT INTO tbl_proc_test VALUES ('28', '24');
INSERT INTO tbl_proc_test VALUES ('29', '120');

-- ----------------------------
-- Table structure for `tbl_test1`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_test1`;
CREATE TABLE `tbl_test1` (
  `user` varchar(255) NOT NULL COMMENT '主键',
  `key` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`user`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行列转换测试';

-- ----------------------------
-- Records of tbl_test1
-- ----------------------------
INSERT INTO tbl_test1 VALUES ('li', 'age', '18');
INSERT INTO tbl_test1 VALUES ('li', 'dep', '2');
INSERT INTO tbl_test1 VALUES ('li', 'sex', 'male');
INSERT INTO tbl_test1 VALUES ('sun', 'age', '44');
INSERT INTO tbl_test1 VALUES ('sun', 'dep', '3');
INSERT INTO tbl_test1 VALUES ('sun', 'sex', 'female');
INSERT INTO tbl_test1 VALUES ('wang', 'age', '20');
INSERT INTO tbl_test1 VALUES ('wang', 'dep', '3');
INSERT INTO tbl_test1 VALUES ('wang', 'sex', 'male');

-- ----------------------------
-- Procedure structure for `proc_test1`
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test1`;
DELIMITER ;;
CREATE DEFINER=`root` 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
;;
DELIMITER ;

说明

SQL进阶语法——order by

场景1：歌单按时间排序

-- 查看全部歌单
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;

SQL进阶语法——group by

场景3-1：统计云音乐创建歌单的用户列表和每人创建歌单的数量。

-- 每个用户歌单的最大订阅数
select userid, max(bookedcount) from play_list group by userid;

-- 每个用户歌单的数量
select userid, count(*) from play_list group by userid;

SQL进阶语法——group by having

场景3-2：统计云音乐创建歌单的用户列表和每人创建歌单的数量，并且只显示歌单数量排序大于等于2的用户

select userid, count(*) from play_list group by userid having count(*) >= 2;

SQL进阶语法-like

select * from play_list where play_name like '%男孩%';
通配符 描述
% 代替一个或多个字符
_ 代替单个字符
[charlist] 中括号中的任何一个字符
[^charlist] 或者 [!charlist] 不在中括号中的任何单一字符

SQL进阶语法-limit, offset

场景4：查询一个月内创建歌单（从第6行开始显示10条记录）

select * from play_list where (createtime between 1427791323 and 1430383307) limit 10 offset 6;

case when

场景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 = '老男孩';

子查询

select userid from play_fav where play_id=(select id from play_list where play_name = '老男孩');

子查询：内层查询的结果作为外层的比较条件。一般子查询都可以转换成连接，推荐使用连接。

连接- left Join

select lst.play_name from play_list lst left join play_fav f on lst.id = f.play_id where f.play_id is null;

场景7：查询出没有用户收藏的歌单

SQL进阶语法-union

场景8：老板想看创建和收藏歌单的所有用户，查询play_list和play_fav两表中所有的userid

select userid from play_list union select userid from play_fav;
-- 默认会去重， 不想去重的话使用union all代替union。

DML进阶语法

总结

2.5-内置函数

聚合函数

函数 描述
AVG() 返回列的平均值
COUNT(DISTINCT) 返回列去重后的行数
COUNT() 返回列的行数
MAX() 返回列的最大值
MIN() 返回列的最小值
SUM() 返回列的总和
GROUP_CONCAT() 返回一组值的连接字符串(MySQL独有)

实例还是上节中的那些表

场景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;

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;

预定义函数

预定义函数-字符串函数

函数 描述
LENGTH() 返回列的字节数
CHAR_LENGTH() 返回列的字符数
TRIM()/RTRIM()/LTRIM() 去除两边空格/去除右边空格/去除左边空格
SUBSTRING(str, pos, [len]) 从pos位置截取字符串str，截取len长度
LOCATE(substr, str, [pos]) 返回substr在str字符串中的位置
REPLACE(str, from_str, to_str) 将str字符串中的from_str替换成to_str
LOWER(), UPPER() 字符串转换为小写/大写 
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
>, >= 大于，大于等于
<, <= 小于，小于等于
= 等于
<=> NULL安全的(NULL-safe)等于,与"等于"类似,但 NULL<=>NULL可得正确比较结果1
!= 即 <> 不等于
BETWEEN M AND N 取M和N之间的值
IN, NOT IN 是否 在/不在 指定集合(一组值)中
IS NULL 和IS NOT NULL 为NULL 和 不为NULL
LIKE 通配符匹配
RLIKE即REGEXP 正则表达式匹配

实例：查询一个月内userid为1,3,5的用户创建的歌单

select * from play_list where (createtime between 1427791323 and 1430383307) and userid in (1,3,5);

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 ;

注意事项

存储过程

存储过程-基本语法

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;

自定义函数

自定义函数-基本语法

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字符集

字符集基础

字符集-分类

MySQL字符集

SHOW CHARACTER SET;

# 编译时加入： --with-charset=
./configure --prefix=/usr/local/mysql3 --with-plugins=innobase --with-charset=gbk

字符集与字符序

查看字符序

show collation;

mysql的字符序遵从命名惯例：以_ci(表示大小写不敏感)，以_CS(表示大小写敏感)，以_bin(表示用编码值进行比较)。

字符集设置级别

字符集设置级别

配置文件

[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

主要影响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

推荐使用统一的字符集

小结

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的区别

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关闭的意义

jdbc连接参数的使用

url="jdbc:mysql://localhost:3306/blog_dbo?userUnicode=true&characterEncoding=utf8";

url="jdbc:mysql://localhost:3306/blog_dbo?connectionTimeout=1000&socketTimeout=30000";

ResultSet游标的使用

Python连接MySQL

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框架的特点

MyBatis简介

MyBatis代码示例

示例代码在sorence/DAO框架代码示例.rar

MyBatis工作流程

MyBatis使用技巧

上一篇 下一篇

猜你喜欢

热点阅读