Shulou(Shulou.com)06/01 Report--

This article is reproduced from: lattice object

Author: Jioby

Original: https://url.cn/5psTHf7

Windows service

-- start MySQL

Net start mysql

-- create a Windows service

Sc create mysql binPath= mysqld_bin_path (Note: there is a space between equal sign and value)

Connect and disconnect the server

Mysql-h address-P port-u user name-p password

SHOW PROCESSLIST-shows which threads are running

SHOW VARIABLES-displays system variable information

Database operation

-- View current database

SELECT DATABASE ()

-- displays the current time, user name, and database version

SELECT now (), user (), version ()

-- create a library

CREATE DATABASE [IF NOT EXISTS] Database name Database option

Database options:

CHARACTER SET charset_name

COLLATE collation_name

-- View existing libraries

SHOW DATABASES [LIKE PATTERN]

-- View the current library information

SHOW CREATE DATABASE database name

-- modify the option information for the library

ALTER DATABASE Library name option Information

-- delete the library

DROP DATABASE [IF EXISTS] database name

At the same time, delete the directory and its contents related to the database.

Operation of the table

-- create a table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [library name.] Table name (structural definition of the table) [table options]

Each field must have a data type

There cannot be a comma after the last field

TEMPORARY temporary table, which disappears automatically at the end of the session

The definition of the field:

Field name data type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT string]

-- Table options

-- character set

CHARSET = charset_name

If the table is not set, the database character set is used

-- Storage engine

ENGINE = engine_name

The different data structures used by tables in managing data will lead to different processing methods, characteristic operations, and so on.

Common engine: InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive

Different engines use different ways to save the structure and data of tables.

MyISAM table file meaning: .frm table definition, .MYD table data, .MYI table index

InnoDB table file meaning: .frm table definition, tablespace data and log files

SHOW ENGINES-displays the status information of the storage engine

SHOW ENGINE engine name {LOGS | STATUS}-displays the log or status information of the storage engine

-from the beginning of the increase

AUTO_INCREMENT = number of Lin

-- data file directory

DATA DIRECTORY = directory

-- Index file directory

INDEX DIRECTORY = directory

-- Table comments

COMMENT = string

-Partition option

PARTITION BY... (see manual for details)

-- View all tables

SHOW TABLES [LIKE pattern]

SHOW TABLES FROM table name

-- View the table mechanism

SHOW CREATE TABLE table name (for more information)

DESC Table name / DESCRIBE Table name / EXPLAIN Table name / SHOW COLUMNS FROM Table name [LIKE PATTERN]

SHOW TABLE STATUS [FROM db_name] [LIKE pattern]

-modify the table

-- modify the options for the table itself

Options for the ALTER TABLE table name table

Eg: ALTER TABLE table name ENGINE=MYISAM

-rename the table

RENAME TABLE original table name TO new table name

RENAME TABLE original table name TO library name. Table name (table can be moved to another database)

-- RENAME can exchange two table names

-- modify the field mechanism of the table (13.1.2. ALTER TABLE syntax)

ALTER TABLE table name operation name

-- Operation name

ADD [COLUMN] field definition-add field

AFTER field name-indicates that it is added after the field name

FIRST-indicates that it is added in the first

ADD PRIMARY KEY (field name)-create a primary key

ADD UNIQUE [Index name] (Field name)-create a unique index

ADD INDEX [Index name] (Field name)-create a normal index

DROP [COLUMN] Field name-Delete field

MODIFY [COLUMN] Field name Field attribute-you can modify the field property, but not the field name (all existing attributes should also be written)

CHANGE [COLUMN] original field name new field name field attribute-field name modification is supported

DROP PRIMARY KEY-Delete the primary key (delete its AUTO_INCREMENT attribute before deleting the primary key)

DROP INDEX Index name-Delete Index

DROP FOREIGN KEY Foreign key-Delete Foreign key

-- delete the table

DROP TABLE [IF EXISTS] table name.

-- emptying table data

TRUNCATE [TABLE] Table name

-- copy the table structure

CREATE TABLE table name LIKE the name of the table to copy

-- copy table structure and data

CREATE TABLE table name [AS] SELECT * the name of the table to be copied

-- check the table for errors

CHECK TABLE tbl_name [, tbl_name]... [option]...

-- optimized table

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

-- repairing the table

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]... [QUICK] [EXTENDED] [USE_FRM]

-- Analysis Table

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

Data operation

-- increase

INSERT [INTO] Table name [(field list)] VALUES (values list) [, (values list),...]

-- if the values list you want to insert contains all fields and is in the same order, you can omit the field list.

Multiple data records can be inserted at the same time!

REPLACE is exactly the same as INSERT and is interchangeable.

INSERT [INTO] table name SET field name = value [, field name = value,...]

-- check

SELECT field list FROM table name [other clause]

-- multiple fields that can come from multiple tables

-- other clauses may not be used

-- the list of fields can be replaced with * to indicate all fields

-- delete

DELETE FROM table name [delete conditional clause]

If there is no conditional clause, all will be deleted

-- change

UPDATE table name SET field name = new value [, field name = new value] [update condition]

Character set coding

-- MySQL, database, table and field can all be encoded.

-- data encoding does not need to be consistent with client encoding

SHOW VARIABLES LIKE character_set_%-View all character set code entries

The encoding used by the character_set_client client when sending data to the server

The encoding used by the character_set_results server to return the result to the client

Character_set_connection connection layer coding

SET variable name = variable value

SET character_set_client = gbk

SET character_set_results = gbk

SET character_set_connection = gbk

SET NAMES GBK;-- equivalent to completing the above three settings

-- proofreading set

Proofreading set is used to sort

SHOW CHARACTER SET [LIKE pattern] / SHOW CHARSET [LIKE pattern] View all character sets

SHOW COLLATION [LIKE pattern] View all proofreading sets

CHARSET character set Encoding set character set Encoding

COLLATE proofing set Encoding Settings

Data type (column type)

1. Numerical type

-a. Integer-

Type byte range (signed bits)

Tinyint 1 byte-128127 unsigned bits: 0 ~ 255Bytes

Smallint 2 bytes-32768 ~ 32767

Mediumint 3 bytes-8388608 ~ 8388607

Int 4 byt

Bigint 8 byt

Int (M) M represents total digits

-symbol bits exist by default, and unsigned attribute is modified.

-displays the width. If a number is not enough to define the number of digits set when the field is defined, fill it with 0 in front of it, and modify the zerofill property.

Example: int (5) inserts a number of 123.After filling, it is 00123.

-when the requirements are met, the smaller the better.

-1 indicates that the bool value is true and 0 indicates that the bool value is false. MySQL has no Boolean type and is represented by integers 0 and 1. Tinyint (1) is commonly used to denote a Boolean.

-b. Floating point type-

Type byte range

Float (single precision) 4 bytes

Double (double) 8 bytes

Floating-point types support both the symbolic bit unsigned attribute and the display width zerofill attribute.

Unlike the integer type, 0. 5% will be filled before and after.

When defining a floating point type, you specify the total number of places and the number of decimal places.

Float (M, D) double (M, D)

M represents total places and D represents decimal places.

The size of M and D determines the range of floating-point numbers. A fixed range different from an integer.

M represents both the total number of digits (excluding decimal points and plus or minus signs) and the display width (all display symbols are included).

Support the scientific counting method.

Floating-point numbers represent approximate values.

-- c. Fixed points-

Decimal-variable length

Decimal (M, D) M also represents the total number of places and D represents the number of decimal places.

Save an exact value without changing the data, unlike floating-point rounding.

Convert floating point numbers to strings and save every 9 digits as 4 bytes.

two。 String type

A. Char, varchar-

Char fixed-length string, fast, but a waste of space

Varchar becomes a long string, which is slow but saves space

M represents the maximum length that can be stored, which is the number of characters, not bytes.

Different codes take up different space.

Char, up to 255characters, regardless of encoding.

Varchar, up to 65535 characters, related to encoding.

A valid record cannot exceed 65535 bytes.

The maximum is 21844 characters for utf8, 32766 characters for gbk, and 65532 characters for latin1.

Varchar is long and needs to use storage space to save the length of varchar. If the data is less than 255bytes, one byte is used to save the length, otherwise two bytes are needed to save the length.

The maximum effective length of a varchar is determined by the maximum line size and the character set used.

The maximum valid length is 65532 bytes, because when varchar stores a string, the first byte is empty, there is no data, and then two more bytes are needed to hold the length of the string, so the valid length is 64432-1-2 bytes 65532 bytes.

Example: if a table is defined as CREATE TABLE tb (C1 int, c2 char (30), c3 varchar (N)) charset=utf8;, what is the maximum value of N? Answer: (65535-1-2-4-303) / 3

-- B. blob, text-

Blob binary string (byte string)

Tinyblob, blob, mediumblob, longblob

Text non-binary string (character string)

Tinytext, text, mediumtext, longtext

When text defines, it does not need to define the length, nor does it calculate the total length.

You cannot give a default value to a text type when it is defined

-- C. binary, varbinary-

Similar to char and varchar, it is used to save binary strings, that is, byte strings instead of character strings.

Char, varchar, text correspond to binary, varbinary, blob.

3. Date time type

The timestamp is generally saved with an integer, because PHP can easily format the timestamp.

Datetime 8-byte date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59

Date 3 byte dates 1000-01-01 to 9999-12-31

Timestamp 4-byte timestamp 19700101000000 to 2038-01-19 03:14:07

Time 3-byte time-838 59 to 838 Rd 59

Year 1 byte year 1901-2155

Datetime YYYY-MM-DD hh:mm:ss

Timestamp YY-MM-DD hh:mm:ss

YYYYMMDDhhmmss

YYMMDDhhmmss

YYYYMMDDhhmmss

YYMMDDhhmmss

Date YYYY-MM-DD

YY-MM-DD

YYYYMMDD

YYMMDD

YYYYMMDD

YYMMDD

Time hh:mm:ss

Hhmmss

Hhmmss

Year YYYY

YY

YYYY

YY

4. Enumeration and collection

-enumeration (enum)-

Enum (val1, val2, val3...)

Select among the known values. The maximum quantity is 65535.

Enumerated values are saved as 2-byte integers (smallint) when saved. Each enumerated value is incremented from 1 in the order in which it is saved.

It is represented as a string type, but the storage is an integer.

The index of the NULL value is NULL.

The index value of the empty string error value is 0. -- set (set)-

Set (val1, val2, val3...)

Create table tab (gender set (male, female, none))

Insert into tab values (male, female)

There can be up to 64 different members. Stored in bigint, a total of 8 bytes. Take the form of bit operation.

When you create a table, the trailing space of the SET member value is automatically deleted.

Select Typ

-- PHP Angl

1. Functional satisfaction

two。 Storage space is as small as possible and processing efficiency is higher.

3. Consider compatibility issues

-- IP storage

1. Only need to store, available string

two。 If you need to calculate, find, etc., it can be stored as a 4-byte unsigned int, namely unsigned

1) PHP function conversion

Ip2long can be converted to an integer, but there is a problem with carrying symbols. Need to be formatted as an unsigned integer.

Using sprintf function to format string

Sprintf ("% u", ip2long (192.168.3.134))

Then use long2ip to convert the integer back to the IP string

2) MySQL function conversion (unsigned integer, UNSIGNED)

INET_ATON (127.0.0.1) converts IP to integer

INET_NTOA (2130706433) converts integers to IP

Column properties (column constraints)

1. PRIMARY primary key

-A field that uniquely identifies a record and can be used as a primary key.

-A table can have only one primary key.

The primary key is unique.

-when declaring a field, it is identified by primary key.

You can also declare 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 made up of multiple fields. At this point, you need to declare the method after the field list.

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 field cannot be repeated.

3. NULL constraint

Null is not a data type, but a property of a column.

Indicates whether the current column can be null, indicating that there is nothing.

Null, empty allowed. Default.

Not null, empty is not allowed.

Insert into tab values (null, val)

This means that the value of the first field is set 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 that the default value is enforced.

Create table tab (add_time timestamp default current_timestamp)

-- sets the timestamp of the current time to the default value.

Current_date, current_time

5. AUTO_INCREMENT automatic growth constraint

Auto-growth must be an index (primary key or unique)

Only one field can exist for automatic growth.

The default is 1 to start automatic growth. You can set it through the table property auto_increment = x, or alter table tbl auto_increment = x

6. COMMENT comments

Example: create table tab (id int) comment comment content

7. FOREIGN KEY foreign key constraint

Used to limit the data integrity of master and slave tables.

Alter table T1 add constraint `t1roomt2fk` 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

A table with a foreign key is called a slave table (child table), and the table pointed to by a foreign key is called a master table (parent table).

Function: to 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 master table name (associated field) [actions when master table records are deleted] [actions when master table records are updated]

At this point, the foreign key of a 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. Provided that the foreign key column does not have not null.

You can not specify the action when the master table records changes or updates, then the operation of the master table is rejected.

If on update or on delete is specified: when deleting or updating, you can choose from the following actions:

1. Cascade, cascade operation. The master table data is updated (primary key value update) and the slave table is updated (foreign key value update). Master table records are deleted, and slave table related records 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 master table record is deleted and the foreign key of the slave table related record is set to null. Note, however, that the foreign key column is required and there is no not null attribute constraint.

3. Restrict, reject parent table deletion and update.

Note that foreign keys are only supported by the InnoDB storage engine. Other engines are not supported.

Table building specification

-- Normal Format, NF

-each table holds one entity information

-each has an ID field as the primary key

-ID primary bond + atomic table

-- 1NF, the first paradigm

If the field can no longer be divided, it satisfies the first paradigm.

-- 2NF, the second paradigm

Under the premise of satisfying the first paradigm, there can be no partial dependence.

Partial dependencies can be avoided by eliminating matching primary keys. Add single-column keywords.

-- 3NF, the third paradigm

Under the premise of satisfying the second paradigm, there can be no transitive dependency.

One field depends on the primary key, while other fields depend on that field. This is transitive dependency.

Implement 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

-- all fields can be represented by *.

Select * from tb

-- you can use expressions (evaluation formulas, function calls, fields are also expressions)

Select stu, 29025, now () from tb

-- you can use aliases for each column. Suitable for simplifying column identifiers to avoid duplication of multiple column identifiers.

-use the as keyword, or omit as.

Select stu+10 as add10 from tb

B. FROM clause

Used to identify the source of the query.

You can give the watch an alias. Use the as keyword.

SELECT * FROM tb1 AS tt, tb2 AS bb

After the from clause, multiple tables can appear at the same time.

Multiple tables are superimposed horizontally, and the data form a Cartesian product.

SELECT * FROM tb1, tb2

-- prompt the optimizer how to select the index

USE INDEX 、 IGNORE INDEX 、 FORCE INDEX

SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3

SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3

C. WHERE clause

-- filter from the data sources obtained by from.

-- Integer 1 indicates true and 0 indicates false.

-- an expression consists of operators and operands.

-Operand: variable (field), value, function return value

-operator:

=,! =,!, & &, | |

In (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor

Is/is not plus ture/false/unknown to check whether a value is true or false

Same as function, can be used for null comparison

D. GROUP BY clause, grouping clause

GROUP BY field / alias [sort by]

Sorting will be carried out after grouping. Ascending order: ASC, descending order: DESC

The following [aggregate functions] need to be used with GROUP BY:

Count returns different number of non-null values count (*), count (field)

Sum summation

Max to find the maximum

Min to find the minimum

Avg to calculate the average value

Group_concat returns a string result with a non-null value for a connection from a group. String concatenation within the group.

E. HAVING clause, conditional clause

It has the same function and usage as where, but the execution time is different.

Where performs detection data at the beginning to filter the original data.

Having filters the filtered results again.

The having field must be queried, and the where field must exist in the data table.

Where cannot use aliases for fields, but having can. Because the column value may not have been determined when the WHERE code is executed.

Where cannot use aggregate functions. Generally speaking, you need to use the aggregate function to use having.

The SQL standard requires that HAVING must refer to columns in the GROUP BY clause or for columns in aggregate functions.

F. ORDER BY clause, sort clause

Order by sort field / alias sort method [, sort field / alias sort method].

Ascending order: ASC, descending order: DESC

Support the sorting of multiple fields.

G. LIMIT clause, limit the number of result clauses

Only the number of processed results is limited. Treat the processed result as a collection, and the index starts at 0 in the order in which the records appear.

Limit start position, get the number of entries

Omit the first parameter, indicating that it starts at index 0. Limit gets the number of entries

H. DISTINCT, ALL option

Distinct removes duplicate records

Default is all, all records

UNION

Combine the results of multiple select queries into a single result set.

SELECT... UNION [ALL | DISTINCT] SELECT...

Default DISTINCT mode, that is, all returned rows are unique

It is recommended that each SELECT query be wrapped in parentheses.

When sorting ORDER BY, it needs to be combined with LIMIT.

The same number of fields are required for each select query.

The list of fields (quantity, type) should be consistent for each select query, because the field names in the results are subject to the first select statement.

Subquery

-subqueries need to be wrapped in parentheses.

-- from type

After from, it is required to be a table, and the results of the subquery must be given individual names.

-simplify the conditions within each query.

-from needs to generate a temporary table that can be used to release the lock of the original table.

-A subquery returns a table, a tabular subquery.

Select * from (select * from tb where id > 0) as subfrom where id > 1

Where type

-the subquery returns a value, scalar quantum query.

-there is no need to alias the subquery.

Tables in where subqueries cannot be used to update directly.

Select * from tb where money = (select max (money) from tb)

-- column subquery

If the subquery result returns a column.

Use in or not in to complete the query

Exists and not exists conditions

If the subquery returns data, it returns 1 or 0. Commonly used in judging conditions.

Select column1 from T1 where exists (select * from T2)

-- Row subquery

The query condition is a row.

Select * from T1 where (id, gender) in (select id, gender from T2)

Line constructor: (col1, col2,...) Or ROW (col1, col2,...)

Row constructors are typically used to compare with subqueries that can return two or more columns.

-- Special operator

! = all () is equivalent to not in

= some () is equivalent to in. Any is an alias for some

! = some () is not the same as not in, not one of them.

All, some can be used with other operators.

Join query (join)

Join the fields of multiple tables to specify the join conditions.

-- Internal connection (inner join)

-Internal connection is the default, but inner can be omitted.

-the connection can be sent only if the data exists. That is, there can be no blank lines in the connection result.

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)

Where can also be used to represent the connection condition.

There is also using, but the field name is the same. Using (field name)

-- Cross connect cross join

That is, there is no conditional internal connection.

Select * from tb1 cross join tb2

-external connection (outer join)

-if the data does not exist, it will also appear in the connection result.

-- left external connection left join

If the data does not exist, the record in the left table appears and the table on the right is populated with null

-- right external connection right join

If the data does not exist, the record in the right table appears, while the left table is populated with null

-- Natural connection (natural join)

Automatically judge the connection conditions to complete the connection.

It is equivalent to omitting using and automatically looks for the same field name.

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

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 the generated data is the tab

If local is not specified, the data file must be on the server

The replace and ignore keywords control the duplicate processing of existing unique key records

-- Control format

Fields control field format

Default: fields terminated by enclosed by escaped by

Terminated by string-termination

Enclosed by char-package

Escaped by char-escape

-- example:

SELECT a dint bjorn a comm b INTO OUTFILE / tmp/result.text

FIELDS TERMINATED BY, OPTIONALLY ENCLOSED BY "

LINES TERMINATED BY

FROM test_table

Lines controls line format

Default: lines terminated by

Terminated by string-termination

INSERT

The data obtained by the select statement can be inserted using insert.

You can omit the column assignment and require the values provided in values () parentheses to all fields that appear in column order.

Or use the set syntax.

INSERT INTO tbl_name SET field=value,...

Multiple values can be used at one time in the form of (), (), ();.

INSERT INTO tbl_name VALUES (), ()

You can use expressions when column values are specified.

INSERT INTO tbl_name VALUES (field_value, 10: 10, now ())

You can use a special value, DEFAULT, to indicate that the column uses the default value.

INSERT INTO tbl_name VALUES (field_value, DEFAULT)

You can use the result of a query as the value to be inserted.

INSERT INTO tbl_name SELECT...

You can specify that the information of other non-primary key columns be updated when there is a primary key (or unique index) conflict in the inserted value.

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 the condition. Where

Specifies the maximum number of records to delete. Limit

Can be deleted by sorting criteria. Order by + limit

Multiple table deletions are supported, using similar join syntax.

Delete from needs to delete data multi-table 1, table 2 using table join operation conditions.

TRUNCATE

TRUNCATE [TABLE] tbl_name

Emptying data

Delete rebuild table

Difference:

1 delete truncate deletes the table and then creates it, while dint deletes it one by one

2the value of auto_increment is reset by dint truncate. And delete won't.

3Grammer truncate doesn't know how many entries have been deleted, but delete knows.

4, when used for partitioned tables, truncate retains partitions

Backup and restore

Backup to save the structure of the data and the data in the table.

Using mysqldump instructions to complete.

-- Export

Mysqldump [options] db_name [tables]

Mysqldump [options]-database DB1 [DB2 DB3...]

Mysqldump [options]-- all--database

1. Export a table

Mysqldump-u user name-p password library name table name > file name (D:/a.sql)

two。 Export multiple tables

Mysqldump-u user name-p password library name Table 1 Table 2 Table 3 > File name (D:/a.sql)

3. Export all tables

Mysqldump-u user name-p password library name > file name (D:/a.sql)

4. Export a library

Mysqldump-u username-p password-- lock-all-tables-- database library name > file name (D:/a.sql)

Can carry WHERE condition with-w

-- Import

1. When logging in to mysql:

Source backup files

two。 Without logging in

Mysql-u username-p password library 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, the view does not exist in the database as a set of stored data values. Row and column data to freely define the table referenced by the view's query, and dynamically generated when the view is referenced.

The view has a table structure file, but no data file exists.

For the underlying tables referenced in it, the view acts like a filter. Filters that define views can come from one or more tables in the current or other database, or from other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them.

View is stored in the database query sql statement, it is mainly for two reasons: security reasons, the view can hide some data, such as: social insurance fund table, you can use the view to show only the name, address, but not the social security number and salary, etc., another reason is that it can make complex queries easy to understand and use.

-create a view

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement

-the view name must be unique and cannot be duplicated with 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 to be executed by the view, which is specified by ALGORITHM.

-column_list if present, the number must be equal to the number of columns retrieved by the SELECT statement

-- View structure

SHOW CREATE VIEW view_name

-- delete 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

-the view is generally not modified because not all updated views are mapped to the table.

ALTER VIEW view_name [(column_list)] AS select_statement

-- View function

1. Simplify business logic

two。 Hide the real table structure from the client

-- View algorithm (ALGORITHM)

MERGE merger

The query statement of the view and the external query need to be merged before execution!

TEMPTABLE temporary table

After the execution of the view, form a temporary table, and then do the outer query!

UNDEFINED is not defined (default), which means that MySQL chooses the appropriate algorithm independently.

Transaction (transaction)

A transaction is a logical set of operations that make up the units of this group of operations that either succeed or fail.

-support the collective success or collective revocation of continuous SQL.

Transaction is a function of database in data evening self-study.

-need to use InnoDB or BDB storage engine to support auto-commit.

-InnoDB is called a transaction security engine.

-- transaction start

START TRANSACTION; or BEGIN

When a transaction is opened, all executed SQL statements are recognized as SQL statements within the current transaction.

-- transaction commit

COMMIT

-- transaction rollback

ROLLBACK

If there is a problem with some of the operations, map to before the transaction starts.

-- characteristics of transactions

1. Atomicity (Atomicity)

A transaction is an indivisible unit of work, and either all or none of the operations in the transaction occur.

two。 Consistency (Consistency)

The integrity of data must be consistent before and after the transaction.

-external data is consistent at the beginning and end of the transaction

-the operation is continuous throughout the transaction

3. Isolation (Isolation)

When multiple users visit the database concurrently, the transaction of one user can not be disturbed by the things of other users, and the data of multiple concurrent transactions should be isolated from each other.

4. Persistence (Durability)

Once a transaction is committed, it changes the data in the database permanently.

-- implementation of transactions

1. The requirement is a table type supported by the transaction

two。 Start a transaction before performing a set of related operations

3. After the whole set of operations are completed, they are successful, then commit; if there is a failure, select rollback, it will go back to the backup point where the transaction started.

The principle of transaction

It is completed by using the automatic commit (autocommit) feature of InnoDB.

After a normal MySQL executes a statement, the current data submission operation is visible to other clients.

On the other hand, the transaction temporarily turns off the "autocommit" mechanism, which requires commit to commit persistent data operations.

-- attention

1. Data definition language (DDL) statements cannot be rolled back, such as those that create or cancel databases, and statements that create, cancel, or change tables or stored subroutines.

two。 Transactions cannot be nested

-- Save point

SAVEPOINT SavePoint name-sets a transaction SavePoint

ROLLBACK TO SAVEPOINT SavePoint name-rollback to SavePoint

RELEASE SAVEPOINT SavePoint name-Delete SavePoint

-- InnoDB autocommit feature settin

SET autocommit = 0 | 1; 0: disable autocommit, 1: enable autocommit.

-if closed, the results of ordinary operations are not visible to other clients, and data operations can only be persisted after commit submission.

-you can also turn off autocommit to start a transaction. But unlike START TRANSACTION,

SET autocommit permanently changes the server's settings until the next time it is changed again. (for current connection)

START TRANSACTION records the state before opening, and once the transaction commits or rolls back, it needs to start the transaction again. (for current transaction)

Lock table

Table locking is only used to prevent improper reads and writes by other clients

MyISAM supports table locks and InnoDB supports row locks

-- lock

LOCK TABLES tbl_name [AS alias]

-- unlock

UNLOCK TABLES

Trigger

The trigger is a named database object associated with a table that is activated when a specific event occurs in the table.

Monitoring: addition, modification and deletion of records.

-- create a 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 that the trigger is triggered before or after the statement that activates it.

Trigger_event indicates 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: activates the trigger when a row is deleted from the table

Tbl_name: listeners must be permanent tables, and triggers cannot be associated with TEMPORARY tables or views.

Trigger_stmt: the statement that is executed when the trigger is activated. Execute multiple statements, using the BEGIN...END compound statement structure

-- Delete

DROP TRIGGER [schema_name.] trigger_name

You can use old and new instead of old and new data

Update operation, before update is old, after update is new.

Delete operation, only old.

Add operation, only new.

-- attention

1. For a given table with the same trigger action time and event, there cannot be two triggers.

-- character concatenation function

Concat (str1,str2,...])

Concat_ws (separator,str1,str2,...)

-- Branch statement

If conditional then

Execute statement

Elseif conditional then

Execute statement

Else

Execute statement

End if

-- modify the outermost statement Terminator

Delimiter custom closing symbol

SQL statement

Custom closing symbol

Delimiter;-- change back to the original semicolon

-- statement block package

Begin

Sentence block

End

-- Special execution

1. Whenever a record is added, the program will be triggered.

2. Insert into on duplicate key update grammar triggers:

If there are no duplicate records, before insert and after insert will be triggered.

If there are duplicate records and updates, before insert, before update, after update will be triggered.

If there is a duplicate record but no update occurs, before insert, before update is triggered

3. Replace syntax if there is a record, execute before insert, before delete, after delete, after insert

SQL programming

-- / / Local variable-

-- 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. 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 the function-/ / global variables-

-- definition, assignment

Set statements can define and assign values to variables.

Set @ var = value

You can also use the select into statement to initialize and assign values to variables. This requires the select statement to return only one row, but it can be multiple fields, which means that multiple variables are assigned at the same time, and the number of variables needs to be the same as the number of columns in the query.

You can also think of the assignment statement as an expression that is executed through select. 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 by the query in the table to the variable.

-| select max (height) into @ max_height from tb

-- Custom variable name

To avoid conflicts between user-defined variables and system identifiers (usually field names) in select statements, user-defined variables use @ as the opening symbol before the variable name.

@ var=10

-after the variable is defined, it is valid for the entire session cycle (login to logout)

-/ / Control structure-

-- if statement

If 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 cycle

[begin_label:] while search_condition do

Statement_list

End while [end_label]

-if you need to terminate the while loop in advance within the loop, you need to use tags; tags need to appear in pairs.

-exit the loop

Exit the entire loop leave

Exit the current loop iterate

Decide which loop to exit by the exit label

-/ / built-in function-

-- numerical function

Abs (x)-absolute value abs (- 10.9) = 10

Format (x, d)-- formatted percentile values format (1234567.456, 2) = 1234567.46

Ceil (x)-- rounding up ceil (10.1) = 11

Floor (x)-rounding down floor (10.1) = 10

Round (x)-rounding off

Mod (m, n)-m% n m mod n ask for the remainder 10% 331

Pi ()-- get pi

Pow (m, n)-m ^ n

Sqrt (x)-arithmetic square root

Rand ()-- Random number

Truncate (x, d)-intercept d decimal places

-- 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 section

Time (yyyy-mm-dd hh:ii:ss);-- get the time part

Date_format (yyyy-mm-dd hh:ii:ss, d% y% a% d% m% b% j);-formatting time

Unix_timestamp ();-- get the unix timestamp

From_unixtime ();-- get the time from the timestamp

-- string function

Length (string)-string length, byte

Char_length (string)-the number of characters in the string

Substring (str, position [, length])-- start with the position of str and take length characters

Replace (str, search_str, replace_str)-replace search_str with replace_str in str

Instr (string, substring)-returns the location where substring first appeared in string

Concat (string [,...])-- concatenate string

Charset (str)-returns 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 content from a file

Locate (substring, string [, start_position])-same as instr, but start position can be specified

Lpad (string, length, pad)-add pad to the beginning of string repeatedly until the string length is length

Ltrim (string)-remove front-end spaces

Repeat (string, count)-repeat count times

Rpad (string, length, pad)-supplemented with pad after str until the length is length

Rtrim (string)-remove backend whitespace

Strcmp (string1, string2)-- compare the size of two strings character by character

-- flow function

Case when [condition] then result [when [condition] then result...] [else result] end multi-branch

If (expr1,expr2,expr3) has two branches.

-- aggregate function

Count ()

Sum ()

Max ()

Min ()

Avg ()

Group_concat ()

-- other common functions

Md5 ()

Default ()

-- / / Storage function, custom function-

-- New

CREATE FUNCTION function_name (parameter list) RETURNS return value type

Function body

-function name, should be a legal identifier, and should not conflict with existing keywords.

-A function should belong to a database. You can use db_name.funciton_name to execute the database to which the current function belongs, otherwise it is the current database.

-Parameter section, which consists of "parameter name" and "parameter type". Multiple parameters are separated by commas.

-the function body consists of several available mysql statements, flow control, variable declaration and other statements.

-multiple statements should be contained 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 options

-- / / stored procedure, custom function-

-- definition

A stored procedure is a piece of code (procedure) that is composed of sql stored in the database.

A stored procedure is usually used to complete a piece of business logic, such as registration, delivery fee, order storage, etc.

While a function usually focuses on a certain function and is regarded as a service of other programs, it needs to call the function in other statements, while the stored procedure cannot be called by others, it is executed by itself and executed through call.

-- create

CREATE PROCEDURE sp_name (parameter list)

Process body

Parameter list: different from the parameter list of the function, you need to specify the parameter type

IN, which represents the input type

OUT, which represents the output type

INOUT, which means mixed type

Notice that there is no return value. / * stored procedure * /-

A stored procedure is a collection of executable code. It is more business logic than function.

Call: CALL procedure name

-- attention

-there is no return value.

-can only be called separately and cannot be included in other statements

-- parameters

IN | OUT | INOUT parameter name data type

IN input: parameters that enter data into the body of the process during the call

OUT output: the result processed by the procedure body is returned to the client during the call

INOUT input and output: both input and output

-Grammar

CREATE PROCEDURE procedure name (parameter list)

BEGIN

Process body

END

User and rights management

-- root password reset

1. Stop the MySQL service

two。 [Linux] / usr/local/mysql/bin/safe_mysqld-- skip-grant-tables &

[Windows] mysqld-- skip-grant-tables

3. Use mysql

4. UPDATE `user`SET PASSWORD=PASSWORD ("password") WHERE `user` = "root"

5. FLUSH PRIVILEGES

User information table: mysql.user

-- refresh permissions

FLUSH PRIVILEGES

-increase the number of users

CREATE USER username IDENTIFIED BY [PASSWORD] password (string)

-you must have global CREATE USER permissions for the mysql database or INSERT permissions.

-you can only create users, not grant permissions.

-user name, note the quotation marks, such as user_name @ 192.168.1.1

-passwords also need quotation marks, and pure numeric passwords also need quotation marks

-to specify a password in plain text, ignore the PASSWORD keyword. To specify the password as the mixed value returned by the PASSWORD () function, include the keyword PASSWORD

-rename the user

RENAME USER old_user TO new_user

-- set password

SET PASSWORD = PASSWORD (password)-sets the password for the current user

SET PASSWORD FOR username = PASSWORD (password)-- sets the password for the specified user

-- Delete user

DROP USER user name

-- assign permissions / add users

GRANT permission list ON table name TO user name [IDENTIFIED BY [PASSWORD] password]

-all privileges indicates all permissions

-*. * represents all tables of all libraries

-name of the library. The table name indicates a table under a library.

GRANT ALL PRIVILEGES ON `pms`. * TO pms @% IDENTIFIED BY pms0817

-- View permissions

SHOW GRANTS FOR user name

-- 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 level

To use GRANT or REVOKE, you must have GRANT OPTION permissions, and you must use the permissions 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.

Surface 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.

-- list of permissions

ALL [PRIVILEGES]-- set all simple permissions except GRANT OPTION

ALTER-ALTER TABLE is allowed

ALTER ROUTINE-change or cancel stored subroutines

CREATE-CREATE TABLE is allowed

CREATE ROUTINE-create stored subroutines

CREATE TEMPORARY TABLES-CREATE TEMPORARY TABLE is allowed

CREATE USER-CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES are allowed.

CREATE VIEW-CREATE VIEW is allowed

DELETE-DELETE is allowed

DROP-DROP TABLE is allowed

EXECUTE-allows users to run stored subroutines

FILE-SELECT...INTO OUTFILE and LOAD DATA INFILE are allowed

INDEX-CREATE INDEX and DROP INDEX are allowed

INSERT-INSERT is allowed

LOCK TABLES-allows you to use LOCK TABLES on tables for which you have SELECT permission

PROCESS-SHOW FULL PROCESSLIST is allowed

REFERENCES-not implemented

RELOAD-FLUSH is allowed

REPLICATION CLIENT-allows the user to ask for the address of the secondary server or the primary server

REPLICATION SLAVE-for replicated secondary servers (reading binary log events from the master server)

SELECT-SELECT is allowed

SHOW DATABASES-shows all databases

SHOW VIEW-SHOW CREATE VIEW is allowed

SHUTDOWN-mysqladmin shutdown is allowed

SUPER-allows the use of CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL statements, mysqladmin debug commands; allows you to connect (once), even if max_connections has been reached.

UPDATE-UPDATE is allowed

USAGE-- synonymous with "no authority"

GRANT OPTION-allows permission to be granted

Table maintenance

-- analyze and store the keyword distribution of tables

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}

-- defragmentation of data files

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

Miscellaneous

1. You can use backquotes (`) to wrap identifiers (library name, table name, field name, index, alias) to avoid duplicates with keywords! Chinese can also be used as an identifier!

two。 There is an option file db.opt for saving the current database in each library directory.

3. Note:

Single-line comment # comment content

Multiline comments / * comment content * /

Single-line comments-comment content (standard SQL comment style, requiring double dashes followed by a space character (space, TAB, newline, etc.)

4. Pattern wildcards:

_ any single character

% any number of characters, even zero characters

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. Other places still end with a semicolon. Delimiter modifies the statement Terminator of the current conversation.

6. SQL is not case-sensitive.

7. Clear the existing statement: C there are two ways to obtain the "2019 data Technology Carnival PPT": 1. Reply: 2019dtc in the official account of "data and Cloud", you can download ppt and watch video! two。 In "Mo Tian Lun", we have sorted out all the open PPT according to 13 venues. You can download the topics you are interested in. Details: https://www.modb.pro/db/11553, copy it to the web page and open it. Another: this carnival video has been newly released (group purchase for 5 people can be watched for free) details: https://www.modb.pro/course/38 copy to the web page to open, watch immediately!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.