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

】 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-杂七杂八

标签：部分   group   初始化   适用于   window   htm   format   cond   height