MYSQL study notes-Mysql Tutorial-php.cn

Basic operations of the database
1. Create a database: Crete database database name;
Before creating the database table, use use database name m; and then create the database.
2. Create a database table: create table table name (field name data type constraints);
3. View the data table structure: desc table name; and show create table table name\g

4 .Constraints; primary key constraints primary key
Non-null constraints not null
Unique constraints unique
Default constraints default
Set the auto-increment of attribute values auto_increment
5. Modify the data table; modify the table name Alter table table name Rename new table name
Modify field name Alter table table name Modify field data type data type
Modify data type Alter Table Table name, old field, new field name data type data type
Add field alter table table Name ADD field name Data Type FIRST/AFTER field Name
Delete Field Alter Table Table Drop field Name
Storage engine Alter Table Table Name Engine = Storage Engine Summary: Modify: alter table table name modify etc;
Data type and operator
Data type: string type, numeric type, date and time type
1. Integer type; tinyint smallint mediumint int bigint.
2. Floating point type; float DOUBLE decimal.
3. Date and time type; year time date datetime timestamp.
4. Text string; char varchar tinytxet text mediumtext longtext enum set.
mysql function
abs() sqrt() ceil() ceiling() floor() rand() round()
Query data and insert, update and delete
Create database table: create database database name;
Open the database: use database name;
Create a new data table: create table name (field name data type);
{
Data type: string type, numeric type, date and time type
1. Integer type; tinyint smallint mediumint int bigint.
2. Floating point type; float DOUBLE decimal.
3. Date time type; year time date datetime timestamp.
4. Text string; char varchar tinytxet text mediumtext longtext enum set .
Constraints: primary key primary key
unique primary key unique
non-null constraint not null
default constraint default default value
foreign key constraint foreign key name foreign key (field name) references primary key name Primary key column;

# Modify field name alter table table name change field name new field name data type
Modify data type alter table table name modify field name data type
Add field alter table table name add field name data type (first /after)
Delete Field Alter Table Table Drop field Name
Storage engine Alter Table Table Name Engine = Storage Engine to Delete the outer key to restrain the table name Drop Foreign Key outer key. Field name from table name ( where 1. Field = table name 2. Field;
Left join select field name from Table name 1 left outer join Table name 2 on Table name 1. Field = table name 2. Field;
Right join select field name from Table name 1 right outer join Table name 2 on Table name 1. Field = Table name 2. Field;
* The left join is the field to be queried. The result includes all the fields of the left table
Subquery (nested query) ( any(some)
exists (exists is used together with conditional expressions. If the query statement after exists returns at least one record,
then the exists result is true. At this time, the outer query statement will query, and not exists is the opposite.)
in )
Merge query results select field name from table name query condition union (all) select field name from table name query condition
Note: The number of columns and field names corresponding to the two tables must be equal. Not using all results in deleting duplicate records.
]
}
Insert into data table: insert into table name (field name) values (data to be inserted);
Update data: updata table name set field = value condition;
Delete Data: delete from table name condition;

Index
Advantages: Create a unique index to ensure the uniqueness of each row of data in the database table, greatly speeding up query speed, and in terms of referential integrity of event data, you can Accelerate the connection between tables
Using grouping and sorting clauses for data query can also reduce the time of grouping and sorting in the query.
Ordinary index index (field name): The most basic index type, without uniqueness restrictions, just to speed up access to data
Unique index unique idenx Index name (field) The index value must be unique. However, null values are allowed to reduce the execution time of query summary operations, especially for huge data tables.
Single column index A table can have multiple indexes
Combined index Create an index on multiple fields, follow the leftmost prefix when querying, and the index can only be used when the query matches such a leftmost prefix
Full text index fulltext Can be used for full-text search. Only the MYISAM storage engine supports fulltext index and only char varchar text
Spatial index spatial must be created in a MYISAM type table, and the spatial type field must be empty,

Create an index on an existing table
alter table table name add index index name (field name (index length))
create index index name on table name (field name);

alter table Table name drop index index name;

drop index index name on table name

Create stored procedures and functions
create procedure name of stored procedure () begin stored procedure body end;
Each call will execute the stored procedure body.

Create stored function
create stored function name (parameter list)
returns return value type
return (function body);

Use of variables
Define variables ：declare variable name variable type default default value;
Assign value to variable: set variable name = value;

Use of cursor
Declare cursor: declare name cursor for for query statement;
Open Cursor: open name;
Use cursor: fetch name into Save the result queried in the cursor into the parameter;
Close the cursor; close name;

Use of process control (case loop leave iterate repeat while )

Call the stored procedure: call name (parameter);
Call the stored function: select name (parameter);

View
The meaning of the view: A view is a virtual table, which is a table exported from one or more tables in the database.
create [or replace] view view name as select statement
create view view name (self-defined column name) as select Statement

desc view name;
show create view view name\G
show table status like 'view name'\G

Modifying the view is the same as creating the view.
alter view view name as select statement

updata view name set field = value;

delete view drop view if exists view name;

MYSQL trigger Trigger
Like stored procedures, they are both programs embedded in mysql. Triggers trigger an operation based on time. These events include insert update delete
Create trigger

Create only one Trigger for executing the statement
create trigger trigger name identifies the triggering time (before/after) identifies the triggering event (insert update delete) on identifies the table name of the trigger for each trigger execution statement;