首页 > 代码库 > MySQL1-基础知识点3-杂七杂八

MySQL1-基础知识点3-杂七杂八

目录:

一、内置函数
二、日期时间类型详解
三、内连接和外连接
四、别名
五、存储引擎
六、事务处理
七、变量
八、字符集和校对
九、创建和管理用户账号
十、emoji与utf8mb4
十一、杂七杂八
 
 
一、内置函数
(1)在不同的DB实现中,函数的移植性不如SQL语句强。
(2)函数功能分类:文本函数、数值函数、日期和时间函数、系统函数。
(3)文本函数:concat()/length()/locate()/substring()/left()/right()/trim()/ltrim()/rtrim()/lower()/upper()/soundex()【其中locate()求字串出现的位置,substring()/left()/right()求字串,soundex()找出发音类似的串
where soundex(name) = soundex(Y Lie)#该语句可以找出name=Y Lee的数据
(4)数值函数:(+-*/操作符)mod()/abs()/exp()/sin()/cos()/tan()/pi()/rand()/sqrt()
(5)日期和时间函数
当前:now()/curDate()/curTime()
时间选取:date()/time()返回日期部分和时间部分;year()/month()/day()/dayOfWeek()返回细分日期;hour()/minute()/second()返回细分时间
计算:addDate()/addTime()/date_add()【参数都是(date, Interval expr type),表示在date基础上加expr个type;三者分别在日期级别、时间级别和任意级别】dateDiff(date1,date2)【date1的天数值-date2的天数值,只有日期部分参加运算】
格式化:date_format()任意格式的日期/时间
(6)其他
cast(value as type)和convert(value,type)可以实现类型转换,其中type表示待转换的类型;转换的类型受到一些限制。
 
 
 
二、日期时间类型详解
(1)MySQL支持的时间类型包括Date、DateTime和TimeStamp,分别表示日期、日期时间和时间戳。MySQL以 ‘YYYY-MM-DD‘ 格式检索与显示 DATE 值,无论是插入更新还是检索,最好都使用这个格式;同理,DateTime最好使用‘YYYY-MM-DD HH:MM:SS‘ 格式。
(2)TimeStamp的显示:可以指定长度,最长与DateTime显示相同,其他的递减,不用细究。
(3)如果在创建表或修改表时,没有指定TimeStamp列的其他属性,则遵循以下规律:
第一个TimeStamp列:insert时没有指定该列的值,则使用当前时间;update时没有指定该列的值,且其他列的值有发生变化(即该行数据真的变化了),则使用当前时间;明确设定该列为null或now()时,使用当前时间。。
其他TimeStamp列:只有明确将该列设为null或now()时,才会使用当前时间。
(4)在创建表或修改表(增加列可以,更改列属性不可以)时,可以对TimeStamp列的属性进行设定,指定插入数据时该TimeStamp列的默认值,以及是否随着update更新该TimeStamp列。
 
 
 
三、内连接和外连接(参考:http://www.cnblogs.com/Ewin/archive/2009/10/05/1578322.html)
(1)准备
1)表A结构:Aid:主键;数据见下图:
技术分享
2)表B结构如下: Bid:主键;数据见下图:
技术分享

技术分享

3)连接字段:要做连接必须有连接字段,表A的Aid和表B的Bnameid就是两个连接字段。
4)记录集分类:连接的所有记录集的关系如下图所示:
技术分享
技术分享
A1:A1/A4/A5/A9
B1:B9
C:A2(B1/B7/B8)、A3(B3)、A6(B2/B5)、A7(B4)、A8(B6)
(2)内连接
1)作用:获取两表公共部分的记录,即记录集C
2)语法:Select * from A JOIN B ON A.Aid=B.Bnameid,效果等同于select * from A,B where A.Aid=B.Bnameid
3)结果:如图所示
技术分享
技术分享
(3)外连接-左连接
1)作用:获取C+A1;其中A1部分的B列为null
2)语法:select * from A Left JOIN B ON A.Aid=B.Bnameid 
3)结果:如图所示【A是基础表】
技术分享
技术分享
(4)外连接-右连接
1)作用:获取C+B1;其中B1部分的A列为null
2)语法:select * from A Right JOIN B ON A.Aid=B.Bnameid
3)结果:如图所示【B是基础表】
技术分享
技术分享
(5)A left join B与B right join A效果是相同的,可能返回集合的列顺序不相同;同理,A join B与B join A的结果也是相同的。
 
 
 
四、别名
(1)列/表达式别名:可以返回到客户机上。
(2)表别名:只在查询执行中使用,不会返回到客户机上。为什么需要使用表别名呢?
情况1:缩短SQL语句
情况2:允许在单条select语句中多次使用相同的表;下例实现查询products表中与DTNTR相同的vendor的所有产品信息。
select p1.prod_id, p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = ‘DTNTR‘
 
 
 
五、存储引擎
(1)MySQL的存储引擎架构,将查询处理(Query Processing)及其他系统任务(Server Task)和数据的存储/提取相分离。
技术分享
技术分享
(2)MySQL的存储引擎有很多,最主要的有以下3种
InnoDB:可靠地事务处理引擎,但是不支持全文本搜索
MyISAM:查询性能极高的引擎,支持全文本搜索,不支持事务处理;表锁,update等语句开销大,适用于大量查询的应用
MYMORY:功能等同于MyISAM,但由于数据存储在内存而不是磁盘,速度极快,因此特别适合临时表
(3)注意
外键不能跨引擎
如果创建表时没有指定存储引擎,使用的默认引擎是不可靠的,因此最好自己指定
 
 
 
六、事务处理
(1)背景
事务处理与隔离级别:本节中的事务处理,是MySQL底层的事务操作,与《Hibernate-事务》一文中不同,后者是更加更层次的概念,即各种隔离级别都是通过MySQL底层事务操作的不同逻辑组合来实现的。
事务处理与存储引擎:MyISAM不支持事务处理,InnoDB支持。
(2)术语
事务:一组SQL语句
回退:撤销SQL语句的过程
提交:将未存储的SQL语句结果写入数据库表
保留点:事务处理中设置的临时占位符,可以回退到这里而不是回退到事务开始
(3)语法
start transaction:开始事务,rollback和commit都结束事务
rollback:只能在事务中使用,即start transaction之后;rollback可以回退insert/update/delete,不能回退create/drop
commit:如果没有start transaction,则为隐含提交,即提交是自动进行的;rollback/commit之后便不在事务中,同样为隐含提交【使用set autocommit=0,则不会自动提交,手动commit才会提交;该设置针对连接而不是服务器】
使用保留点:savepoint point1;……rollback to point1;rollback或commit可以自动释放保留点,也可以使用release savepoint自动释放保留点
 
 
 
七、变量
1、全局变量
全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改。
使用语句更改:
set @@global.variable_name = variable_value;#方法1
set global variable_name = variable_value;#方法2
2、会话变量
会话变量在每次建立一个新的连接的时候,由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份,来做为会话变量(也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的)全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话。
使用语句更改:
set @@session.variable_name = variable_value;#方法1
set session variable_name = variable_value;#方法2
3、用户变量
用户变量与数据库连接有关,在这个连接中声明的变量,在连接断开的时候,就会消失;在此连接中声明的变量无法在另一连接中使用。
用户变量和会话变量起作用的区域都是会话变量,但是二者是非常不同的:会话变量在每次连接服务器时通过赋值全局变量获得,每次连接使用的会话变量值虽然不同,但是变量是相同的;而用户变量如果要使用则需要自己创建。
创建/赋值用户变量:
set @variable_name = variable_value;
4、局部变量
函数中声明,只在函数中有效。
DECLARE variable_name1,variable_name2,…… datatype(size) DEFAULT default_value;
 
 
 
八、字符集和校对(校对牵涉到排序和检索方法)
1、一般的数据库操作,如update、insert等不需要考虑字符集和校对;关于它们的决定在服务器、数据库和表级进行。实际上,很少是服务器/数据库的设置,不同的表、甚至是不同的列使用的字符集和校对也可能不同2、查看
#查看所有字符集及相关信息
SHOW CHARACTER SET
#查看所有校对信息
SHOW COLLATION
#查看目前字符集
SHOW VARIABLES LIKE ‘character%‘
#查看目前校对信息
SHOW VARIABLES LIKE ‘collation%‘
3、设置:语句>列>表>数据库>服务器
CREATE TABLE test(
col1 INT,
col2 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci
SELECT * FROM user ORDER BY name COLLATE latin1_general_cs
除了select,collate还可以用于group by/having/聚集函数和别名等。
 
 
 
九、创建和管理用户账号
1、用户信息存储在mysql数据库的user表里;针对用户账号的操作如果可以不操作user表,就使用其他语句完成。
2、需要通过user表的操作【可以用其他语句完成的,不再给出user表实现方式】
获得所有用户账号:select * from user
3、账号管理
CREATE USER lizy IDENTIFIED BY ‘1234‘
RENAME USER lizy TO ben
DROP USER ben
 
#新增用户没有任何权限
#GRANT USAGE ON *.* TO ‘lizy‘@‘%‘ ……表示没有任何权限
SHOW GRANTS FOR lizy
GRANT SELECT,INSERT ON sample.* TO lizy
REVOKE SELECT,INSERT ON sample.* FROM lizy
 
#不指定for lizy,则为修改自己账号的密码
SET PASSWORD FOR lizy = PASSWORD(‘11111‘)
关于权限控制的补充
(1)grant和revoke可以在几个层次上控制访问权限:整个服务器、整个数据库、特定的表、特定的列、特定的存储过程。
(2)权限:ALL(除grant option外的所有权限)以及其他各种特别细的权限
(3)当心未来的授权:如果某个表/数据库/方法等,被删除了,权限还是在的;如果未来这个表/数据库/方法又添加了,则权限仍有,应该当心。
 
 
 
十、emoji与utf8mb4
1、emoji表情使用越来越多,而它们在utf8下使用4个字节编码,需要使用utf8mb4(most bytes 4)。
2、但是使用utf8mb4要谨慎,一方面是需要在整个请求链中支持,另一方面如果支持表情的字段过多,会导致数据库占用空间过大。
3、MySQL从5.5开始支持utf8mb4,下面描述了一个由支持utf8的系统,到支持utf8mb4数据插入的实例;整个系统输入、存储、展示等对utf8mb4的全面支持没有研究。
(1)当前状态(支持utf8)
MySQL版本:5.6
MySQL服务器:SHOW VARIABLES LIKE ‘%character_set_server%‘,返回结果为latin1
数据库:数据库创建时指定字符集为utf8
table及字段:utf8
Hibernate/JDBC连接:jdbc.url=jdbc:mysql://host:port/dbname?useUnicode=true&characterEncoding=utf8
库版本:mysql-connector-java-5.1.17.jarspring-jdbc-4.1.1.RELEASE.jarhibernate-commons-annotations-4.0.5.Final.jarhibernate-core-4.3.6.Final.jarhibernate-ehcache-4.3.6.Final.jar
(2)所做改动(改动后支持将utf8mb4的字符串插入数据库)
在数据库的配置文件(windows下是my.ini,Linux下在/etc/my.cnf)中,加入如下代码,然后重启MySQL服务器,此时执行SHOW VARIABLES LIKE ‘%character_set_server%‘,返回结果为utf8mb4
[client]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
将需要支持utf8mb4的字段,字符集设置为utf8mb4,不需要重启服务器。注意,只需要在字段级别修改,表级别和数据库级别不需要修改。
传参方式:使用对象传参,而不是form传参;具体见《HttpClient》涉及utf8mb4的部分。
 
 
 
十一、杂七杂八
1、主键生成:一般MySQL有多种主键生成策略,自增、uuid等。有几点需要注意:
(1)uuid与自增相比,一个好处是难以预测。
(2)使用MySQL自动生成有一个问题:如果在插入后立马需要这个主键值,可能会比较麻烦【通过插入的返回值,这个问题应该可以解决】
2、动态表名:使用prepare实现。
 
 

MySQL1-基础知识点3-杂七杂八