The specific content mainly includes the following parts:
Creating a database is relatively simple, just specify the character set and sorting rules directly when creating it!
Example:
There are three ways to modify the name of the database. If it is MyISAM storage engine, you can directly enter the database directory mv. If it is Innodb, you will be prompted that the related table does not exist.
This syntax was added in mysql-5. 1.7, but was deleted in mysql-5. 1.23. The government does not recommend it, and there is a danger of data loss!
The idea is to create a new library first, and then import the data from the old library into the new library, thus completing the modification of the library name!
Of course, this method is safe, but if the amount of data is large, it will be time-consuming, and hardware costs such as disk space need to be considered.
Example:
Run a shell script directly!
Where p 123456, p is the abbreviation of password, and 123456 represents the database password value!
Deleting the library is relatively simple, just delete it directly!
or
MySQL mainly has the following operators:
Operator description example+addition selection1+2; The result is 3- subtraction selection1-2; The result is-1 * multiplied by 2 * 3; The result is that 6/ division chooses 6/3; The result is 2% remainder selection10% 3; The result is 1.
Note: In division and modular operation, if the divisor is 0, it is an illegal divisor, and the return result is NULL.
Conditional statements in SELECT statements usually use comparison operators. Through these comparison operators, we can judge which records in the table are qualified. If the comparison result is true,1is returned; If false, return 0; If the comparison result is uncertain, NULL is returned.
Operator description instance = equal to select * from t _ user, where user _ ID = 1 information with query user ID of 1! = not equal to select * from t_user where user_id! = 1 Query information with user ID other than 1 >; Is greater than select * from t _ user where user _ id >; 1 query user ID greater than 1 >: = greater than select * from t _ user where user _ ID > = 1 query information with user id greater than or equal to1select * from t _ user where user _ id
Note: IN mysql, the number of parameters in the in statement is infinite. But the length of the whole sql statement is limited, and the maximum is not more than 4M!
Logical operators are used to determine whether an expression is true. If the expression is true, the result will return 1. If the expression is false, the result returns 0.
Operator describes the instance NOT or! Logical non-selection is not1; The result is 0 AND logical AND to select 2 and 0; The result is 0 or logical or choose 2 or 0; The result is that 1 XOR logical XOR selects null or1; The result is 1.
Bit operators are operators that perform calculations on binary numbers. Bit operation will first change operands into binary numbers for bit operation. Then change the calculation result from binary number to decimal number.
Operator describes an instance &; Select 3&by bitwise and; 5; The result is that 1 I selects 3I5 by bitwise or, and the result is 7-bit XOR selection 3I5, and the result is 7-bit XOR selection 3 5; The result is 6 ~1844674073709551612; The result is 3 > > shift selection 3>& gt 1; The result is 1
Priority (from high to low) operator 1! 2- (minus sign), ~ (bitwise negation) 3 (bitwise XOR) 4*,/(division), %(MOD) 5+,-6 > & gt,<& lt7 & amp8I 9= (comparison operation),
Note: If you can't determine the priority, you can use parentheses () to change the priority, which will make the calculation process clearer.
A view is a virtual table, a logical table, and contains no data. Stored as a select statement in the data dictionary.
Parameter description:
Basic format:
Example of creating a view:
Deleting a view means deleting an existing view in the database. When deleting a view, only the definition of the view can be deleted, and the data will not be deleted, that is, the base table will not be moved:
Delete example:
The function description instance char_length(s) returns the character length of the string S, and selects char _ length ("hello") as the content; Concat(s 1, s2...sn) strings s 1, s2 and other strings are merged into a string select concat ("hello", "world") as the content; Format(x, n) formats the number x to n digits after the decimal point, and selects format (500.5634) as the content after rounding the last digit; Lower(s) converts all letters into lowercase letters select lower ('hello'); Current_timestamp () returns the current date and time SelectCurrent _ timestamp (); DATE_FORMAT(date, format) Format time or date select date _ format (current _ timestamp (), "%y-%m% y-% m-% d% h:% I:% s ")；); IFNULL(v 1, v2) If the value of v 1 is not NULL, return v 1, otherwise return v2select IFNULL(null,' hello word');
Parameter description:
Parameter description:
Parameter description:
Parameter description:
Example:
Parameter description:
Parameter description:
Create an example of a stored procedure for querying user information:
Output result:
Delete example:
A trigger is a database object related to a table, which is triggered when the defined conditions are met and executes the statement set defined in the trigger.
Definition grammar
Parameter description:
For example, if a trigger named trig 1 is created, the current time will be automatically inserted into the t_time table once there is an insertion action in the t_user table.
Use multiple execution statements to create trigger syntax.
Examples are as follows:
Once the insertion is successful, start ... the END statement will be executed!
All trigger information is stored in the trigger table under the information_schema database, which can be queried by using the SELECT statement. If there is too much trigger information, it is best to specify the query through the TRIGGER_NAME field.
Examples are as follows:
After deleting the trigger, it is best to check it again in the above way.
Use triggers as little as possible, because it will consume resources anyway. If you use them, you should use them carefully and make sure they are very effective: triggers are prepared for each line; Remember, don't use triggers on tables that are frequently added or deleted, because it will consume resources very much.
In MySQL, there are several ways to realize unique value:
In mysql, generally we can set the primary key field to auto-increment mode, such as:
In this mode, there is no problem in the case of single database and single table, but if the test_db table is divided into different databases and tables, the problem comes at this time. If the database is divided horizontally, the data will be inserted into test_db_ 1 and test_db_2, and the same ID will appear!
Of course, in order to avoid this situation, some great gods have created self-increasing sequence tables and maintained them separately, so that the same id will not appear when the tables are divided!
The implementation process is also very simple!
This scheme solves the problem of table splitting in some cases, but if the database is split, the same ID will still appear!
UUID is based on hexadecimal 16 and consists of 32 lowercase digits in hexadecimal 16, as shown below:
For example, D0C754A8-178E-1EB-AE3D-2A7BEA22ED3D is a typical UUID.
In the UUID () function of MySQL, the first three groups of numbers are generated by time stamps, the fourth group of numbers temporarily keeps the uniqueness of time stamps, and the fifth group of numbers is an IEEE 802 node punctuation value to ensure the uniqueness of space.
Using the UUID () function, you can generate unique values of time and space. It is said that as long as UUID is used, it is impossible to see two duplicate UUID values. Of course, this is only a theoretical situation.
The use method is also very simple, and it can be directly called as a function in sql!
After MySQL 5. 1, the UUID _ short () function is provided to generate a 64-bit unsigned integer, and the Long type in java is acceptable. In addition, it should be noted that the range of server_id must be 0-255, and statement mode copying is not supported, otherwise duplicate ids may be generated.
It should also be noted that UUID _ short () returns an unsigned long integer. When setting the field type, you must check the unsigned type, otherwise the generated ID may exceed the maximum length of long type!
Description:
Please log in with an administrator account, and then make other authorization!
Note: Users authorized by the above commands cannot authorize other users. If you want this user to be authorized, please use the following command!
Just add the grant option at the end!
You can use the following command to set the password of root user with one click, and at the same time, you have all permissions and set it as remote access!
If you want to turn off the remote access of the root user, just use the following command!
Finally, use the following command to make it work!
Create users and authorize them. You can also use the following shortcut commands!
The last thing to note is that mysql8 uses strong authentication, so if the password is too simple, it will report an error, and the password should be as complicated as possible!
This paper mainly combs and introduces the commonly used grammars in Mysql. Most of these grammars are also applicable to other databases, such as oracle, sqlserver, postgres and so on. In the data operation column, except the paging function, it is basically universal!