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

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

2024-10-01 15:05:01   220人阅读

目录：

一、内置函数
二、日期时间类型详解
三、内连接和外连接
四、别名
五、存储引擎
六、事务处理
七、变量
八、字符集和校对
九、创建和管理用户账号
十、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.jar；spring-jdbc-4.1.1.RELEASE.jar；hibernate-commons-annotations-4.0.5.Final.jar；hibernate-core-4.3.6.Final.jar；hibernate-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-杂七杂八


联系
我们