mysql1-Basic Knowledge point 3-Assorted

Directory:

One, the built-in function two, the date time type elaboration three, the internal connection and the outer connection four, the alias five, the storage engine six, the transaction processing seven, the variable eight, the character set and the proofreading nine, creates and manages the user account ten, emoji and utf8mb4 11, assorted one, built-in functions (1) in different DB implementations, The portability of functions is not as strong as SQL statements. (2) Function function Classification: text function, numerical function, date and time function, system function. (3) Text function: Concat ()/length ()/locate ()/substring ()/left ()/right ()/trim ()/ltrim ()/rtrim ()/lower ()/upper ()/soundex () where locate () asks for the word string to appear, substring ()/left ()/right (), Soundex () find a string with similar pronunciation】 where Soundex ( name) = Soundex (y lie) #该语句可以找出 name =y lee (4) Numeric function: (+-*/operator) mod ()/abs ()/exp ()/sin ()/cos ()/tan ()/pi ()/rand ()/sqrt () (5) Date and time function current: Now ()/curdate ()/curtime () Time selection: Date ()/time () returns the dates and time parts; year ()/month ()/day ()/dayofweek () returns the subdivision date; hour ()/minute ()/second () Returns the subdivision time calculation: Adddate ( The/addtime ()/date_add () "parameter is all (date, Interval expr type), which indicates that a type is added to date, and that the three are at the date level, the time level, and any level" DateDiff (Date1, DATE2) "Date1 days value-date2 day values, only date part join operation" format: Date_format () Date/time in any format (6) Other cast (value as type) and CONVERT (Value,type)   You can implement type conversions where type represents the type to be converted, and the type of conversion is subject to some restrictions. Second, date and time type detailed (1) the time types supported by MySQL include date, datetime, and timestamp, respectively, representing dates, date times, and timestamps. MySQL retrieves and displays DATE values in the ' YYYY-MM-DD ' format, preferably using this format, whether it is inserting updates or retrieving them, and, similarly, DateTime is best used in the ' Yyyy-mm-dd HH:MM:SS ' format. (2) Timestamp display: can specify the length, the longest and the datetime display is the same, the other decrements, not scrutiny. (3) If you do not specify other properties of the timestamp column when you create a table or modify the table, the following rule is followed: The first timestamp column: Insert without specifying the value of the column, the current time is used, the value of the column is not specified in update, and the value of the other column is changed ( That the row's data really changed), the current time is used, and the current time is used when explicitly setting the column null or Now (): Other timestamp columns: The current time is used only if the column is explicitly set to null or now ().   (4) When you create a table or modify a table (you can add columns to change column properties), you can set the properties of the timestamp column, specify the default value for the timestamp column when the data is inserted, and update the timestamp column with the update. Three, inner Connection and outer connection (reference: http://www.cnblogs.com/Ewin/archive/2009/10/05/1578322.html) (1) Prepare1) Table A structure: Aid: primary key; data see:
2) Table B is structured as follows: Bid: primary key; data see:

3) connection field: To do the connection must have the Connection field, table A's Aid and Table B's Bnameid is two connection fields.
4) Recordset Classification: The relationship of all record sets connected is as follows:

A1:a1/a4/a5/a9
B1:b9
C:A2 (B1/B7/B8), A3 (B3), A6 (B2/B5), A7 (B4), A8 (B6) (2) Inner connection1) Function: Gets the record of the public part of the two tables, that is, the record set C
2) Syntax: SELECT * from A joins B on A.aid=b.bnameid, the effect is equivalent to Select * from A, where A.aid=b.bnameid
3) Results: (3) External connection-left join1) function: Get c+a1; where the A1 part of the B column is null
2) Syntax: SELECT * from A left JOIN B on A.aid=b.bnameid
3) Result: "A is the base table" (4) Outer connection-Right connection1) function: Get c+b1; where the B1 part of a column is null
2) Syntax: SELECT * from A right JOIN B on A.aid=b.bnameid
3) Result: "B is the base table" (5) a LEFT join B and B right join a effect is the same, may return the collection The column order is not the same, similarly, A join B and B Join a result is the same. Four, alias (1) column/Expression alias: Can be returned to the client. (2) Table aliases: Used only in query execution and not returned to the client. Why do I need to use a table alias? Scenario 1: Shortening the SQL statement Scenario 2: Allows the same table to be used more than once in a single SELECT statement, and the following example implements all product information for querying the Products table for the same vendor as DTNTR. 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‘V. Storage engine (1) MySQL's storage engine architecture separates query processing (processing) and other system tasks (Server Task) from the storage/extraction of data. (2) MySQL storage engine has many, the most main has the following 3 kinds of InnoDB: The reliable transaction processing engine, but does not support the full text search MyISAM: The query performance extremely high engine, supports the full text search, does not support the transaction processing, the table lock, the update and so on the statement overhead, Application for a large number of queries Mymory: function is equivalent to MyISAM, but because the data is stored in memory instead of disk, it is extremely fast, so it is particularly suitable for temporary tables (3) Note that foreign keys cannot cross the engine if you do not specify a storage engine when creating a table, the default engine used is unreliable, so it is best to specify six, Transaction processing (1) Background transaction processing and isolation levels: The transaction in this section, which is the bottom-level transactional operation of MySQL, differs from the hibernate-transaction article, which is a more hierarchical concept, where various isolation levels are implemented through different logical combinations of MySQL's underlying transactional operations. transaction processing and storage engine: MyISAM does not support transactional processing, INNODB support. (2) Term transaction: A set of SQL statement fallback: Process commit to undo SQL statement: Writes the result of an unsaved SQL statement Database TablesReserved point: A temporary placeholder set in a transaction that can be rolled back here instead of being rolled back to the start of the transaction (3) syntax start transaction: BEGIN TRANSACTION, rollback and commit both end transaction rollback: can only be used in transactions, that is, start After transaction; rollback can fallback insert/update/delete, cannot fallback create/dropcommit: if there is no start transaction, then the commit is implicit, that is, the submission is automatic ; Rollback/commit is not in the transaction after the same, the implicit commit "using Set autocommit=0, is not automatically committed, manual commit will not commit; This setting uses the retention point for connections instead of servers: SavePoint point1; Rollback to Point1;rollback or commit can automatically release a retention point, or you can use release savepoint to automatically release retention points seven, variable 1, Global variables Global variables the server automatically initializes them to default values when MySQL starts, and these defaults can be changed by changing the My.ini file. Use statement changes: set @@global.variable_name = variable_value;#方法1 set global variable_name = variable_value;#方法22. Session variable session variables are initialized by MySQL each time a new connection is established. MySQL will copy the value of all current global variables as a session variable (that is, if the session variable and global variable values are not changed manually after the session has been established, the values of all those variables are the same) global variables the difference between sessions variables is that Modifications to global variables affect the entire server, but changes to session variables affect only the current session. Use statement changes: set @@session.variable_name = variable_value;#方法1 set session variable_name = variable_value;#方法23. User variable user variable is related to database connection, the variable declared in this connection disappears when the connection is broken; the variable declared in this connection cannot be used in another connection. The areas in which user variables and session variables work are session variables, but they are very different: Session variables are obtained by assigning global variables each time they connect to the server, but the variables are the same for each connection, but the variable is the same, but the user variable needs to be created if it is to be used. Create/Assign User variables: set @variable_name = variable_value;4, declared in the local variable function, only valid in the function. DECLARE variable_name1,variable_name2,…… datatype(size) DEFAULT default_value;Character set and proofreading (collation involves sorting and retrieving methods) 1, general database operations, such as update, INSERT, etc. do not need to consider character sets and proofing; decisions about them are made at the server, database, and table level. In fact, very few server/database settings, different tables, even different columns use the character set and proofreading may be different 2, view #查看所有字符集及相关信息 SHOW CHARACTER SET #查看所有校对信息 SHOW COLLATION #查看目前字符集 SHOW VARIABLES LIKE ‘character%‘ #查看目前校对信息 SHOW VARIABLES LIKE ‘collation%‘3. Settings: statements > Columns > Tables > Databases > Servers 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_csIn addition to Select,collate can also be used for group by/having/aggregation functions and aliases. IX. Create and manage user accounts 1. User information is stored in the user table of the MySQL database; If you can do this, you will use a different statement if you do not have to manipulate it. 2, need to pass the user table operation "can be completed with other statements, no longer give the user table implementation" To get all users account: SELECT * from User3, account management 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‘)Supplemental rights Control (1) grant and revoke can control access at several levels: the entire server, the entire database, a specific table, a specific column, and a specific stored procedure.   (2) Permissions: All (except grant option) and various other special fine-grained permissions (3) Beware of future authorizations: If a table/database/method is deleted, the permissions are still there, and if the table/database/method is added again in the future, the permissions are still there and should be careful. Ten, emoji and utf8mb41, emoji expression use more and more, and they use 4 byte encoding under UTF8, need to use UTF8MB4 (most bytes 4).
2, but the use of utf8mb4 to be cautious, on the one hand is required in the entire request chain support, on the other hand, if there are too many fields to support the expression, it will cause the database to occupy too much space. 3, MySQL support UTF8MB4 starting from 5.5, the following describes a system supported by UTF8, to support utf8mb4 data insertion, the entire system input, storage, display and so on UTF8MB4 full support no research. (1) Current status (support 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 Library version:Mysql-Connector-Java-5.1.17.jar；Spring-Jdbc-4.1.1.release. Jar;hibernate-commons -annotations-4.0.< Span class= "lit" >5.final. Jar;hibernate-core-< Span class= "lit" >4.3. 6.final. Jar;hibernate-ehcache -4.3. 6.final. Jar (2) changes (after the change to support the UTF8MB4 string inserted into the database) in the database configuration file (under Windows My.ini,linux under/ETC/MY.CNF), add the following code, and then restart the MySQL server, at this time to execute show VARIABLES like '%character_set_server% ', returning the result to UTF8MB4 [client] default-character-set=utf8mb4 [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_general_ciYou will need a field that supports UTF8MB4, the character set to UTF8MB4, and no need to restart the server. Note that you only need to modify at the field level, and the table level and database level do not have to be modified.   The method of transmitting the parameter: Use the object to pass the parameter, not the form to pass the parameter, see "HttpClient" to refer to UTF8MB4 part specifically. Xi. Assorted 1, primary key generation: General MySQL has a variety of primary key generation strategy, self-increment, uuid and so on. There are a few points to note: (1) UUID is unpredictable compared to self-increment. (2) Using MySQL automatic generation has a problem: if you need this primary key value immediately after insertion, it may be troublesome to "pass the inserted return value, this problem should be resolved" 2, Dynamic Table name: Using prepare implementation.

mysql1-Basic Knowledge point 3-Assorted