datetime 8 bytes date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59
date 3 bytes date 1000-01-01 to 9999- 12-31
timestamp 4 bytes Timestamp 19700101000000 to 2038-01-19 03:14:07
time 3 bytes Time -838:59:59 to 838:59:59
year 1 byte Year 1901 - 2155
datetime “YYYY-MM-DD hh:mm:ss”
timestamp “YY-MM-DD hh:mm:ss”
“YYYYMMDDhhmmss”
“YYMMDDhhmmss”
YYYYMMDDhhmmss
“YYYYMMDDhhmmss”
“YY-MM-DD”
“YYYYMMDD”
“ YYMMDD"
"YYYYMMDD
"YYMMDD
time "hh:mm:ss"
"hhmmss"
“YY”
YYYY
YY
4. Enumerations and collections--
Enumeration (enum) ----- -----enum(val1, val2, val3...)
Make a single selection among known values. The maximum number is 65535.When the enumeration value is saved, it is saved as a 2-byte integer (smallint). Each enumeration value, in the order of the saved position, starts from 1 and increases one by one. Performs as string type, but stores it as integer type. The index of NULL value is NULL. The index value of the empty string error value is 0.
-- Set ----------
set(val1, val2, val3 ...)
create table tab ( gender set('male', 'female', 'none') );insert into tab values ('male, female');Can have up to 64 different members. Stored in bigint, 8 bytes in total. Takes the form of bit operation
. When creating a table, trailing spaces in SET member values will be automatically removed.
/* Select type*/-- PHP perspective
1. Functions are satisfied2. Storage space should be as small as possible and processing efficiency is higher
3. Consider compatibility issues
--IP Storage----------1. Just store, available string
2. If you need to calculate, search, etc., you can store it as a 4-byte unsigned int, that is, unsigned
1)
PHP functionConversion ip2long can be converted to an integer type, but there will be a problem of carrying symbols. Needs to be formatted as an unsigned integer.
Use the sprintf function to format the stringsprintf("%u", ip2long('192.168.3.134'));Then use long2ip to convert the integer back IP string
2) MySQL function conversion (unsigned integer, UNSIGNED)
INET_ATON('127.0.0.1') Convert IP to integerINET_NTOA(2130706433) Convert integer to IP
/* Column attributes (column constraints) */ -------- ----------
1. Primary key
- can uniquely identify The field of the record can be used as the primary key. - A table can only have one primary key.
- The primary key is unique. - When declaring a field, use primary key to identify it.
You can also declare it after the field list
Example: create table tab (id int, stu varchar(10), primary key (id));
- The value of the primary key field cannot be null.
- The primary key can be composed of multiple fields. At this time, a method declared after the field list is required.
Example: create table tab (id int, stu varchar(10), age int, primary key (stu, age));
2. unique unique index (unique constraint )
so that the value of a certain field cannot be repeated.
3. Null constraint
null is not a data type, but an attribute of the column.
Indicates whether the current column can be null, which means there is nothing.
null, allowed to be empty. default.
not null, empty is not allowed.
insert into tab values (null, 'val');
-- This means setting the value of the first field to null, depending on whether the field is allowed to be null
4. default default value attribute
The default value of the current field.
insert into tab values (default, 'val'); -- This means forcing to use the default value.
create table tab (add_time timestamp default current_timestamp);
-- means setting the timestamp of the current time as the default value.
current_date, current_time
##5. auto_increment automatic growth constraint
Automatic growth must be an index (primary key or unique)Only one field can be automatically grown. The default is 1 to start automatic growth. It can be set through the table attribute auto_increment = x, or alter table tbl auto_increment = x;
6. comment Comment
Example: create table tab (id int) comment 'comment content';
7. foreign key foreign key constraint
is used to limit the main table and the slave table Data integrity. alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);--Associate the t1_id foreign key of table t1 to the id field of table t2.
-- Each foreign key has a name, which can be specified through constraint
The table in which the foreign key exists is called the slave table (subtable), and the table pointed to by the foreign key is called the master table (parent table). Function: Maintain data consistency and integrity. The main purpose is to control the data stored in the foreign key table (slave table). In MySQL, you can use foreign key constraints on the InnoDB engine:
Syntax:
foreign key (foreign key field) references main table name ( Related fields) [Action when the master table record is deleted] [Action when the master table record is updated]At this time, it is necessary to detect that a foreign key from the slave table needs to be constrained to the existing value of the master table. The foreign key can be set to null when there is no association. The premise is that the foreign key column is not null. You can not specify the action when the main table record is changed or updated, then the operation of the main table will be rejected at this time.
If on update or on delete is specified: When deleting or updating, there are several operations to choose from:
1. cascade , cascade operation. The master table data is updated (primary key value update), and the slave table is also updated (foreign key value update). The master table records are deleted, and the related records from the slave table are also deleted. 2. set null, set to null. The master table data is updated (the primary key value is updated), and the foreign key of the slave table is set to null. The primary table record is deleted, and the foreign key of the secondary table related record is set to null. But note that the foreign key column is required to have no not null attribute constraints. 3. restrict, deny deletion and update of the parent table. Note that foreign keys are only supported by the InnoDB storage engine. Other engines are not supported.
/* Table creation specifications */ ------------------
-- Normal Format, NF
- Each table saves one entity information
- Each has an ID field as a primary key
- ID primary key + atomic table
-- 1NF, First Normal Form
If the field cannot be further divided, it satisfies the first normal form.
-- 2NF, Second Normal Form
Under the premise of satisfying the first normal form, partial dependence cannot occur.
but the partial dependence can be avoided by eliminating the matching primary key. Add single column keywords.
-- 3NF, Third Normal Form
Under the premise of satisfying the second normal form, transitive dependencies cannot occur.
A certain field depends on the primary key, and other fields depend on this field. This is transitive dependency.
Put the data of an entity information in a table.
/* select */ ------------------
select [all |distinct] select_expr from -> where -> group by [total function] -> having -> order by -> limit
##a. select_expr
-- You can use * to represent all fields. use using
expressions
(calculation formulas, function calls, and fields are also expressions) ‐ ‐ ‐ ‐ select stu, 29+25, now() from tb; -- You can use aliases for each column. Suitable for simplifying column identification and avoiding duplication of multiple column identifiers.
-Use AS keywords, can also omit AS.
SELECT STU+10 ADD10 from TB;
# B. From ## is used to identify the query
source. --You can give the table an alias. Use the as keyword. select * from tb1 as tt, tb2 as bb;
- from clause, you can also appear multiple tables at the same time. —Multiple tables will be superimposed horizontally, and the data will form a Cartesian product. select * from tb1, tb2;
c. where clause
-- The data source obtained from from Filter in. -- Integer 1 represents true, 0 represents false.
-- The expression is composed of operator and operation
array.
but ;, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, no, d. group by clause, grouping clause
group by field/alias[sort method]
Grouping Will be sorted later. Ascending order: ASC, descending order: DESC
The following [total function] needs to be used with group by: count
Returns the number of different non-NULL values count(*), count(field) sum summation max finds the maximum value
min finds the minimum value avg finds the average value
group_concat returns a string result with non-NULL values from the concatenation of a group.
String concatenation
within the group.
e. having clause, conditional clause
The function and usage are the same as where, but the execution timing is different.
Where executes the detection data at the beginning and filters the original data.
Having to filter the filtered results again.
The having field must be queried, and the where field must exist in the data table.
Where cannot use field aliases, but having can. Because when the WHERE code is executed, the column value may not have been determined yet.
Where the total function cannot be used. Generally, having
is used when an aggregate function is required. The SQL standard requires that HAVING must reference columns in the GROUP BY clause or columns used in the aggregate function.
f. order by clause, sorting clause
order by sorting field/alias sorting method[, sorting field/ Alias sorting method]...
Ascending order: ASC, Descending order: DESC
Supports sorting of multiple fields.
g. limit clause, the clause that limits the number of results
only limits the number of processed results. Treat the processed results as a collection, with the index starting from 0 in the order in which the records appear.
limit starting position, get the number of items
Omitting the first parameter means starting from index 0. limit Get the number of records
h. distinct, all options
##distinct Remove duplicate records Default is all, all records
/* UNION */ ------------------
Combine the results of multiple select queries into a result set. SELECT ... UNION [ALL|DISTINCT] SELECT ...
The default DISTINCT method, that is, all returned rows are unique
It is recommended to wrap each SELECT query in parentheses.
When sorting by ORDER BY, LIMIT needs to be added for combination.
It is required that the number of fields queried by each select is the same.
The field list (number, type) of each select query should be consistent, because the field names in the results are based on the first select statement.
/* Subquery*/ ------------------
- Subqueries need to be wrapped in parentheses.
--from type
The requirement after from is a table, and the subquery result must be aliased. - Simplify the conditions within each query.
- The from type needs to generate a temporary
table from the result, which can be used to release the lock of the original table. - Subquery returns a table, table type subquery.
select * from (select * from tb where id>0) as subfrom where id>1;
--where type
- Subquery returns a value, scalar subquery. - No need to alias the subquery.
- The table in the where subquery cannot be updated directly.
select * from tb where money = (select max(money) from tb);
-- column subquery
if sub The query result returns a column. Use in or not in to complete the query
Exists and not exists conditions.
If the child query returns the data, return 1 or 0. Often used to determine conditions.
select column1 from t1 where exists (select * from t2);
-- Row subquery
The query condition is a row .
# The constructor is usually used to compare with a sub -query that can return two or more columns.
-- Special operator != all() Equivalent to not in
= some() Equivalent to in. any is an alias of some
!= some() It is not equivalent to not in, not equal to one of them.
All, some can be used with other operators.
/* Join query (join) */ ------------------
To connect the fields of multiple tables, you can specify the connection conditions.
-- Inner join(inner join)
- The default is inner join, inner can be omitted.
- The connection can only be sent if the data exists. That is, the connection result cannot contain blank lines.
on represents the connection condition. Its conditional expression is similar to where. You can also omit the condition (indicating that the condition is always true)
You can also use where to express the connection condition.
There is also using, but the field names must be the same. using(field name)
-- Cross join cross join
That is, an inner join without conditions.
select * from tb1 cross join tb2;
-- Outer join (outer join)
- If the data does not exist, it will also appear in the connection result.
-- Left outer join left join
If the data does not exist, the left table records will appear, and the right table will be filled with nulls
-- Right outer joinright join
If the data does not exist, the right table Records will appear, and the left table is filled with null
-- Natural join(natural join)
Automatically determine the connection conditions to complete the connection.
It is equivalent to omitting using, and the same field name will be automatically found.
natural join
natural left join
natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info. stu_num = extra_info.stu_id;
/* Import and Export*/ ------------------
select * into outfile File address [control format] from table name; -- Export table data
load data [local] infile file address [replace|ignore] into table table name [control format]; -- Import data
The default delimiter for generated data is the tab character
If local is not specified, the data file must be on the server
The replace and ignore keyword control pairs Duplicate processing of existing unique key records
-- Control format
fields Control field format
Default: fields terminated by '\t' enclosed by '' escaped by '\\'
terminated by 'string' -- Termination
enclosed by 'char' -- Package
escaped by 'char' -- Escape
-- Example:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table ;
lines Control line format
Default: lines terminated by '\n'
terminated by 'string' -- terminate
/* insert */ ------------------
select The data obtained by the statement can be inserted using insert.
You can omit the specification of columns, requiring values () within brackets to provide values for all fields that appear in column order.
Or use set syntax.
insert into tbl_name set field=value,...；
You can use multiple values at one time, in the form of (), (), ();.
insert into tbl_name values (), (), ();
You can use expressions when specifying column values.
insert into tbl_name values (field_value, 10+10, now());
You can use a special value default, which means that the column uses the default value.
insert into tbl_name values (field_value, default);
The result of a query can be used as the value to be inserted.
insert into tbl_name select ...;
You can specify to update the information of other non-primary key columns when the inserted value has a primary key (or unique index) conflict.
insert into tbl_name values/set/select on duplicate key update field=value, …;
/* delete */ ------------ ------
DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
Delete according to conditions
Specify the maximum number of records to delete. Limit
Can be deleted by sorting conditions. order by + limit
supports multi-table deletion, using similar connection syntax.
delete from needs to delete data from multiple tables 1 and 2 using table connection operation conditions.
/* truncate */ ------------------
TRUNCATE [TABLE] tbl_name
Clear data
Delete and rebuild the table
Difference:
1, truncate is to delete the table and create it again , delete is to delete
2 one by one, truncate resets the value of auto_increment. And delete doesn't know how many items have been deleted by truncate, but delete does.
4, when used on a partitioned table, truncate will retain the partition
/* Backup and restore*/ ------ ------------Backup, save the data structure and the data in the table.
Use the mysqldump command to complete.
-- Export
1. Export a table
mysqldump -u username -p password database name table name > file name (D :/a.sql)2. Export multiple tables
Mysqldump -u username -p password database name table 1 table 2 table 3 > file name (D:/a.sql)
3. Export all tables
Mysqldump -u username-p password library name> File name (D:/a.sql)
4. Export a library
mysqldump -u user name-p password-B library Name> File name (D:/a.sql)
Can carry backup conditions with -w
-- Import
1 . When logging in to mysql:
Source Backup file
2. When not logging in
mysql -u username -p password database name < Backup file
/* View
*/ ------------------
What is a view:
#A view is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and rows of data. However, views do not exist as stored sets of data values in the database. Row and column data come from the table referenced by the query that defines the view, and are generated dynamically when the view is referenced.
The view has a table structure file, but no data file exists.
For the underlying table referenced in it, the view acts like a filter. Filters that define a view can come from one or more tables in the current or
other databases, or from other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them.
The view is the sql statement of the query stored in the database. It is mainly for two reasons:
SecurityFor reasons, the view can hide some data, such as: Social Security Fund table , you can use the view to display only the name and address without displaying the social security number and salary number. Another reason is that it can make complex queries easy to understand and use.
--Create view
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
- The view name must be unique and cannot have the same name as the table. - The view can use the column names queried by the select statement, or you can specify the corresponding column names yourself.
- You can specify the algorithm executed by the view, specified through ALGORITHM.
- If column_list exists, the number must be equal to the number of columns retrieved by the SELECT statement
-- View structure
SHOW CREATE VIEW view_name
-- Deleting the view
- After deleting the view, the data still exists.
- Multiple views can be deleted at the same time.
DROP VIEW [IF EXISTS] view_name ...
--Modify the view structure
- Generally do not modify the view, because not all updates Views are mapped to tables.
ALTER VIEW view_name [(column_list)] AS select_statement
-- View function
1. Simplify business logic
2. For customers Hide the real table structure
--View algorithm (ALGORITHM)
MERGE Merge
Merge the query statement of the view , need to be merged with the external query before executing! TEMPTABLE Temporary table
After the view is executed, a temporary table is formed, and then the outer query is performed!
UNDEFINED Undefined (default) means that MySQL independently selects the corresponding algorithm.
/* Transaction (transaction) */ ------------------ -
A transaction refers to a logical set of operations. Each unit that makes up this set of operations will either succeed or fail.
- Supports collective success or collective revocation of continuous SQL.
- Transaction is a function of the database in data self-study.
- You need to use InnoDB or BDB storage engine to support the automatic submission feature.
- InnoDB is called a transaction-safe engine.
-- Transaction opening
START TRANSACTION; or BEGIN;
After the transaction is opened, all executed SQL statements are recognized as SQL statements within the current transaction.
-- Transaction submission
COMMIT;
-- Transaction rollback
ROLLBACK;
If there is a problem with some operations, it will be mapped to before the transaction is started.
-- Characteristics of transactions
1. Atomicity
A transaction is an indivisible unit of work. Either all operations in the transaction occur or none occur.
2. Consistency
The integrity of the data before and after the transaction must be consistent.
-At the beginning and end of the transaction, the external data is consistent
-The throughout the transaction, the operation is continuous
3. isolation (isolation)
When multiple users send a database, a user Transactions cannot be interfered with by other users' transactions, and data between multiple concurrent transactions must be isolated from each other.
4. Durability
Once a transaction is committed, its changes to the data in the database are permanent.
-- Transaction implementation
1. The requirement is the table type supported by the transaction
2. Open the transaction before executing a set of related operations
3. After the entire set of operations is completed, all are successful , then commit; if there is a failure, choose to roll back, and you will return to the backup point where the transaction started.
-- Principle of transaction
It is completed by using the automatic submission (autocommit) feature of InnoDB.
After ordinary MySQL executes the statement, the current data submission operation can be visible to other clients.
The transaction temporarily turns off the "auto-commit" mechanism and requires commit to submit the persistent data operation.
-- Note
1. Data definition language (DDL) statements cannot be rolled back, such as statements that create or cancel a database, and create, cancel, or change tables or storage Subroutine statements.
2. Transactions cannot be nested
-- Savepoint
SAVEPOINT Savepoint name--Set a transaction savepoint
ROLLBACK TO SAVEPOINT Savepoint name--Roll back to the savepoint
RELEASE SAVEPOINT Save point name--delete save point
--InnoDB automatic commit feature setting
SET autocommit = 0|1; 0 means to turn off automatic commit, 1 means to turn on automatic commit.
- If it is closed, the results of ordinary operations will not be visible to other clients, and a commit is required before the data operation can be persisted.
- You can also turn off automatic submission to open transactions. But unlike START TRANSACTION,
SET autocommit permanently changes the server settings until the settings are modified again next time. (For the current connection)
And START TRANSACTION records the state before opening, and once the transaction is committed or rolled back, the transaction needs to be opened again. (For the current transaction)
/* Lock table*/
Table locking is only used to prevent other clients from improperly reading and writing
MyISAM supports table locks, InnoDB supports Row Lock
-- Lock
LOCK TABLES tbl_name [AS alias]
-- Unlock
UNLOCK TABLES
/* Trigger*/ ------ ------------
The trigger is a named database object related to the table. When a specific event occurs in the table, the object will be activated
Listening: Addition, modification, deletion of records .
-- Create trigger
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
Parameters:
trigger_time is the action time of the trigger program. It can be before or after to indicate whether the trigger is fired before or after the statement that activates it.
trigger_event specifies the type of statement that activates the trigger
INSERT: activates the trigger when a new row is inserted into the table
UPDATE: activates the trigger when a row is changed
DELETE: deletes a row from the table When activating the trigger program
tbl_name: The monitored table must be a permanent table. The trigger program cannot be associated with the TEMPORARY table or view.
trigger_stmt: statement executed when the trigger program is activated. To execute multiple statements, you can use the BEGIN...END compound statement structure
-- Delete
DROP TRIGGER [schema_name.]trigger_name
You can use old and new to replace the old and new Data
Update operation, old before update, new after update.
Delete operation, only old.
Add operation, only new.
-- Note
1. There cannot be two triggers for a given table with the same trigger action time and event.
-- Character concatenation function
concat(str1[, str2,...])
-- Branch statement
if condition then
Execution statement
elseif condition then
Execution statement
else
Execution statement
end if;
--Modify the end symbol of the outermost statement
delimiter Custom end symbol
SQL statement
Custom end symbol
delimiter ; end
-- Special execution
1. As long as a record is added, the program will be triggered.
2. Insert into on duplicate key update syntax will trigger:
If there is no duplicate record, it will trigger before insert, after insert;
If there is a duplicate record and it is updated, it will trigger before insert, before update, after update; If there are duplicate records but no update occurs, trigger before insert, before update
3. Replace syntax If there is a record, execute before insert, before delete, after delete, after insert
/* SQL
Programming
*/ ------------------
--// Local variables---- ------
-- Variable declaration declare
var_name[,...] type [default value] This statement is used to declare local variables. To provide a default value for a variable, include a default clause. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null.
-- Assignment Use set and select into statements to assign values to variables.
- Note: Global variables (user-defined variables) can be used within functions
--// Global variables----------
--Definition and assignmentThe set statement can define and assign values to variables.
set @var = value;You can also use the select into statement to initialize and assign a value to a variable. This requires that the select statement can only return one row, but it can be multiple fields, which means that multiple variables are assigned values at the same time. The number of variables needs to be consistent with the number of columns in the query.
You can also regard the assignment statement as an expression and execute it through select. At this time, in order to avoid = being treated as a relational operator, use := instead. (The set statement can use = and :=).
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into can assign the data obtained from the query in the table to a variable.
-| select max(height) into @max_height from tb;
-- Custom variable name
In order to avoid user-defined variables and system identifiers (usually fields) in the select statement name) conflict, user-defined variables use @ as the starting symbol before the variable name. @var=10;
- After the variable is defined, it is valid throughout the session cycle (login to logout)
--// Control structure---- ------
-- if statementif search_condition then
statement_list [elseif search_condition then
statement_list]
...
[else
statement_list ]
end if;
-- case statement
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ ELSE result]
END
--
while loop
[begin_label:] while search_condition do statement_list
end while [end_label];- If you need to terminate the while loop early within the loop, you need to use labels; labels need to appear in pairs.
-- Exit the loop
Exit the entire loop leave
Exit the current loop iterate
Determine which loop to exit through the exit label
--// Built-in function ----------
-- Numerical function
abs(x) —Absolute value abs(-10.9) = 10
format(x, d) -- Format the thousandths value format(1234567.456, 2) = 1,234,567.46
ceil(x) -- Round up ceil(10.1) = 11
floor(x) -- Round down floor (10.1 ) = 10
Round (X) -She Sa Wu Enter
Mod (M, N) - M%N m Mod N to find more 10%3 = 1
pi () - #Pow (m, n) - m^n
SQRT (x) - -the arithmetic square root
Rand () - Random number
Truncate (x, d) - intercept D position
##-- Time and date function
now(), current_timestamp(); -- Current date and time
current_date(); -- Current date
current_time(); -- Current time
date('yyyy-mm-dd hh:ii:ss'); -- Get the date part
time('yyyy-mm-dd hh:ii:ss'); -- Get the time partdate_format
('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- Format time
unix_timestamp(); - Get unix timestamp
from_unixtime(); -- Get time from timestamp
-- String function
length(string) -- String length, bytes
char_length(string ) -- The number of characters in string
substring(str, position [,length]) -- Starting from the position of str, take length characters
replace(str, search_str, replace_str) -- Used in str replace_str replaces search_str
instr(string,substring) -- Returns the position where substring first appears in string
concat(string [,...]) -- Concatenates the string
charset(str) -- Return the string character set
lcase(string) -- Convert to lowercase
left(string, length) -- Take length characters from the left in string2
load_file(file_name) -- Read from the file Get the content
locate(substring, string [,start_position]) -- Same as instr, but you can specify the starting position
lpad(string, length, pad) -- Repeat adding pad to the beginning of the string until the length of the string For length
# LTRIM (String) -Remove the front -end spaces
Repeat (String, Count) -Repeat the COUNT times
RPAD (String, LENGTH, PAD) - is length
rtrim(string) --Remove back-end spaces
strcmp(string1,string2) --Compare the size of two strings character by character
-- Process function
case when [condition ] then result [when [condition] then result ...] [else result] end Multi-branch
if(expr1,expr2,expr3) Double branch.
-- Aggregation function
count()
sum();
max();
min();
avg();
group_concat()
-- Other commonly used functions
md5();
default();
--// Storage function, Custom function
----------
-- New CREATE FUNCTION function_name (parameter list) RETURN
S Return value type
Function body### - The function name should be a legal identifier and should not conflict with existing keywords.
- A function should belong to a certain database. You can use the form of db_name.funciton_name to execute the database to which the current function belongs, otherwise it is the current database.
- The parameter part consists of "parameter name" and "parameter type". Multiple parameters are separated by commas.
- The function body consists of multiple available mysql statements, process control, variable declaration and other statements.
- Multiple statements should be included using begin...end statement blocks.
- There must be a return return value statement.
-- Delete
DROP FUNCTION [IF EXISTS] function_name;
-- View
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;
--Modify
ALTER FUNCTION function_name function option
--// Stored procedure, custom function----------
-- Definition
A stored procedure is a piece of code (procedure) consisting of sql stored in the database.
A stored procedure is usually used to complete a piece of business logic, such as registration, shift payment, order warehousing, etc.
A function usually focuses on a certain function and is regarded as a service for other programs. It needs to call the function in other statements. However, the stored procedure cannot be called by other people and is executed by itself through call.
-- Create
CREATE PROCEDURE sp_name (parameter list)
Process body
Parameter list: Different from the function parameter list, the parameter type needs to be specified
IN, represents input type
OUT, represents output type
INOUT, represents mixed type
Note, there is no return value.
/* Stored procedure */ ------------------
A stored procedure is a collection of executable codes. It prefers business logic to functions.
Call: CALL procedure name
-- Note
- There is no return value.
- Can only be called alone and cannot be mixed with other statements
-- Parameter
IN|OUT|INOUT Parameter name Data type
IN Input: During the calling process, input the data To the parameters inside the process body
OUT Output: During the calling process, the result of the process body is returned to the client
INOUT Input and output: It can be input or output
-- Syntax
CREATE PROCEDURE process name (parameter list)
BEGIN
Process body
END
/* User and Permission Management */ -- ----------------
User information table: mysql.user
-- Refresh permissions
FLUSH PRIVILEGES
-- Add user
CREATE USER Username IDENTIFIED BY [PASSWORD] Password (characters String)
- You must have the global CREATE USER permission of mysql database, or have the INSERT permission.
- Users can only be created, but permissions cannot be granted.
- User name, pay attention to the quotation marks: such as 'user_name'@'192.168.1.1'
- The password also needs quotation marks, and the pure numeric password also needs quotation marks
- To specify the password in plain text, ignore it PASSWORD keyword. To specify the password as the hashed value returned by the PASSWORD() function, include the keyword PASSWORD
-- Rename user
RENAME USER old_user TO new_user
-- Set password
SET PASSWORD = PASSWORD('password') -- Set a password for the current user
SET PASSWORD FOR username = PASSWORD('password') -- Set a password for the specified user
-- Delete the user
DROP USER username
-- Assign permissions/Add user
GRANT permission list ON table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
- all privileges means all permissions
- *.* represents all tables in all libraries
- Library name. Table name represents a table under a certain library
--View permissions
SHOW GRANTS FOR username
--View current user permissions
SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
-- revoke permissions
REVOKE permission list ON table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name -- Revoke All Permissions
-- Permission Hierarchy
-- To use GRANT or REVOKE, you must have the GRANT OPTION permission, and you must be using the permission you are granting or revoking.
Global level: Global permissions apply to all databases in a given server, mysql.user
GRANT ALL ON *.* and REVOKE ALL ON *.* only grant and revoke global permissions.
Database level: Database permissions apply to all targets in a given database, mysql.db, mysql.host
GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* only grant and revoke database permissions.
Table level: Table permissions apply to all columns in a given table, mysql.talbes_priv
GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name only grant and revoke table permissions.
Column level: Column permissions apply to a single column in a given table, mysql.columns_priv
When using REVOKE, you must specify the same column as the authorized column.
-- Permission list
ALL [PRIVILEGES] -- Set all simple permissions except GRANT OPTION
ALTER -- Allow the use of ALTER TABLE
ALTER ROUTINE -- Change or cancel stored subtitles Procedure
CREATE and and and USER, RENAME USER and REVOKE ALL PRIVILEGES.
CREATE VIEW -- Allow the use of CREATE VIEW
DELETE -- Allow the use of DELETE
DROP -- Allow the use of DROP TABLE
EXECUTE -- Allow the user to run a stored subroutine
FILE -- Allow the use of SELECT...INTO OUTFILE and LOAD DATA INFILE
INDEX -- Allow the use of CREATE INDEX and DROP INDEX
INSERT -- Allow the use of INSERT
LOCK TABLES -- Allow the use of tables for which you have SELECT permissions LOCK TABLES
PROCESS -- Allow the use of SHOW FULL PROCESSLIST
REFERENCES -- Not implemented
RELOAD -- Allow the use of FLUSH
REPLICATION CLIENT -- Allow the user to ask for the address of a slave or master server
REPLICATION SLAVE -- For replication slave servers (read binary log events from the master server)
SELECT -- Allowed to use SELECT
SHOW DATABASES -- Show all databases
SHOW VIEW -- Allowed to be used SHOW CREATE VIEW
SHUTDOWN -- Allow
to use mysql
admin shutdown
SUPER -- Allow to use CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL statements, mysqladmin debug command ;Allows you to connect (once) even if max_connections has been reached.
UPDATE -- Allow the use of UPDATE
USAGE -- Synonyms for "no permissions"
GRANT OPTION -- Allow permissions to be granted
/* Table maintenance*/
--Analysis and Keyword distribution of storage table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table name...
-- Check one or more tables for errors
CHECK TABLE tbl_name [, tbl_name] ... [option ] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment the data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
/* Miscellaneous*/ ------------------
1. Backticks (`) can be used as identifiers (library names, table names , field name, index, alias) package to avoid duplicate names with keywords! Chinese can also be used as an identifier!
2. Each library directory contains an option file db.opt that saves the current database.
3. Comment:
Single-line comment# Comment content
Multi-line comment/* Comment content*/
Single-line comment--Comment content (Standard SQL comment style, double Add a space after the dash (space, TAB, newline, etc.))
4. Pattern wildcard character :
_ Any single character
% Any multiple characters , even including the zero character
Single quotation marks need to be escaped\'
5. The statement terminator in the CMD command line can be ";", "\G", "\g", which only affects the display result. Elsewhere, end with a semicolon. delimiter can modify the statement terminator of the current conversation.
6. SQL is not case sensitive
7. Clear existing statements:\c
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
