Basic mysql operations,

Basic mysql operations,

Database and table operations

Add:

Create database: create database mpc [charset utf8]; (sets the database encoding)

Create a data table: create table [if not exists] table Name (Field 1, Field 2... [Index 1, index 2 ..., Constraint 1, constraint 2…]) [Table option 1, Table option 2…];

Field setting format:
Field name type [field property 1, field property 2…]
Type: int, tinyint, float, double, char, varchar, text, datetime, date, time, timestamp...
Field Property:
Auto_increment: integer auto-increment (must be a primary key)
Primary key: Specifies the primary key and is unique. Only one primary key can be set in a table. It cannot be null)
Unique key: unique index. Multiple unique indexes can be set in a data table. Multiple values in this field can be null)
Not null: cannot be empty
Default: Set the default value.
Comment 'field description text': Field description;

Create table if not exists createtest (id int auto_increment primary key, name varchar (40) not null unique key, password varchar (40) not null, age tinyint default 18, email varchar (40) comment 'email ');

Index: sorts all data in a table in the database in advance;
Common Index: key (field name );
Unique index: unique key (field name );
Primary key Index: primary key (field name );
Joint primary key: primary key (multiple field names are separated by commas );
Full-text index: fulltext (field name );
Foreign key index: foreign key (field name) references table name (field name); (a field in one table is a field in another table (it must be a primary key ), if the input value is no longer in the field of the External table, it cannot be entered)

create table tab1_key(id int auto_increment,name varchar(20) not null,emai varchar(50) not null,age int not null default 18,grades1 int,primary key(id),unique key(name),foreign key(grades1) references tab2_key(grades2));create table tab2_key(stu_id int not null,teacher varchar(20) not null,floor int not null,grades2 int auto_increment primary key);

Constraint: a condition for data to meet
Unique constraint: unique key (field name );
Primary key constraint: primary key (field name );
Foreign key constraint: foreign key (field name) references table name (field name); (a field in one table is a field in another table (it must be a primary key ), if the input value is no longer in the field of the External table, it cannot be entered)
Non-empty constraint: not null; it cannot be null when a field is set
Default constraint: default XX value. Set the default value of a field to xx;
Check constraints: check (a judgment Statement)

Create table tab_check (age tinyint, check (age> 0 and age <100); -- however, mysql does not take effect and the syntax can pass

Table option: when creating a table, set the table as a whole:
Charset = character encoding to be used;
Engine = storage engine to be used (also called table type); (InnoDB by default)

A relational database table is a data structure used to store and organize information. It can be understood as a table composed of rows and columns, similar to a workbook in Excel. Some tables are simple, some tables are complex, and some tables do not need to store any long-term data at all. Some tables read very quickly, but are poorly inserted; in the actual development process, we may need a variety of tables. Different tables mean that different types of data are stored and data processing is also different. For MySQL, it provides many types of storage engines. We can select different storage engines based on the data processing requirements to maximize the use of MySQL's powerful functions. This blog post will summarize and analyze the characteristics of each engine, as well as its applicability, and will not be entangled in deeper things. My learning method is to learn how to use it first, know how to use it, and then know how to use it. The following describes the storage engines supported by MySQL.

Auto_increment = set the initial value of the auto-increment field of the current table. The default value is 1.
Comment = 'table description text' create table 2 like table 1;

Create a new table according to the structure of a table: created table 1 like table 2;

Insert all data in a similar table structure table in the existing table structure: insert into Table 1 select * from table 2;

Delete:

Delete database: drop database [if exists] database name; (if exists does not exist, no error is reported)

Delete A data table: drop table [if exists] table name. (if exists does not exist, no error is reported)

Change:

Modify a data table:
Add field: alter table name add [column] New Field Name field type [field attribute list];
Modify Field: alter table name change [column] Old field name new field Name field type [field attribute list];
Delete field: alter table Name drop [column] field name;
Add a common index: alter table name add key (field name 1 [, field name 2, field name 3…]);
Add a unique index (constraint): alter table name add unique key (field name 1 [, field name 2, field name 3…]);
Add a primary key index (constraint): alter table name add primary key (field name 1 [, field name 2, field name 3…]);

 

Data Operations

 

Add:

Form 1: insert [into] Table name [(field name 1, field name 2,...)] values (value expression 1, value expression 2 ,...), (...),...;

The most common insert statement can insert multiple rows of data at a time, separated by commas. The value expression can be a "direct value" or "function call result "; if the corresponding field is of the character or time type, the value should use single quotation marks;

Form 2. replace [into] Table name [(field name 1, field name 2,...)] values (value expression 1, value expression 2 ,...), (...),...;

The usage is the same as the format 1. The only difference is that if the primary key or unique key of the insert statement is the same, the existing data is directly replaced;

Form 3: insert [into] Table name [(field name 1, field name 2,...)] select field name 1, field name 2,... from other table names;

Insert the result data queried by the select statement into the specified table. Note the corresponding fields queried by the select statement;

Form 4: insert [into] Table name set field name 1 = value expression 1, field name 2 = value expression 2 ,...;

Format 5: load data infile' file name (absolute path) 'into table name;

Load data: Suitable for loading Structured Text data, as shown in. (Note that some editors add BOM characters to the document header to hold the data so that it cannot be loaded)

Delete:

Delete from table name [where condition] [order by sorting field] [limit row];

Data is still deleted in the unit of "row". Generally, the where condition should be used to delete data. Otherwise, all data will be deleted (extremely dangerous ), order by and limit are used to filter specific data and are rarely used in practical applications;

Change:

Update table name set field 1 = value 1, Field 2 = value 2,... [where condition] [order by sorting field] [limit row];

Generally, the update statement carries the where condition. Otherwise, all data is modified (extremely dangerous). order by and limit are used to filter specific data, it is rarely used in practical applications;

Query:

Select [all | distinct] field or expression list [from clause] [where clause]
[Group by clause] [having clause] [order by clause] [limit clause]

Field: source table, expression: similar to 8 + 3, now (), etc.

 

All default value: show all data;
Distinct: displays all non-duplicate data;
As: alias. Select id as serial number from tab1;

Where is similar to the if condition judgment statement in other languages. true and false indicate permanent and permanent false.

The where clause is used to set conditions for filtering data in the "Data Source" in the from clause. The filtering mechanism is to make judgments based on "one row and one row", which is similar to if statements in other languages; the where clause usually uses various operators: Arithmetic Operators (+,-, *,/, %), comparison operators (>,<,>=, <=, [=, <>] equal to and not equal to, = ,! =), Logical operators (and, or, not ).

Select * from test where id> 2 and sex! = 'Man '; id greater than 2 and sex is not equal to man;

Select * from test where id> 2 or sex = 'man '; id is greater than 2 or sex is equal to man;

Select * from test where not (sex = 'man '); sex is not equal to man;

The group by clause is used to group the queried data.

Group by field 1 [desc | asc], Field 2 [desc | asc],...; you can specify the sorting method for grouping results. The default sorting method is asc (positive order). Generally, one field is used for grouping, and more than two fields are few.

The Data After grouping is left in one group, so try not to output all fields. Some fields cannot be grouped for one group (for example, data tables with names, ages, and gender fields are grouped by gender fields, the name and age fields do not make sense), so the useful information after grouping is the total amount [count (*) to get the total amount of data in this group], and the aggregated information of the original data type field [max (age) to get the maximum age, min (age) to get the minimum age, avg (age) to get the average age, sum (age) get the total age of all people].

Select class, count (*) as student count, max (sports score) as sports score highest score, min (sports score) as sports score lowest score, avg (sports score) as sports score average score, sum (sports score) as total sports score for all from test group by class;

The having clause works exactly the same as the where clause, but filters existing grouped data. Therefore, the having clause must be used only after the group by clause is used.

Select class id, count (*) as student count, max (sports score) as sports score highest score, min (sports score) as sports score lowest score, avg (sports score) average score of as sports, sum (sports score) as total score of all sports from test group by class id having average score> 60;

The order by clause sorts the previously obtained data by the set fields and then outputs the result (desc reverse order, asc order, asc by default)

Order by field 1 [asc | desc], Field 2 [asc | desc],...; the sorting of multiple fields is based on the previous sorting. If there is still "equal value", the sorting will continue;

Select * from test order by age;

Select * from test order by age, weight;

The limit clause is used to extract the specified rows of the obtained data.

The START row number of the limit statement. The number of rows to be retrieved is counted from 0. The start row number can be omitted. By default, multiple uplink operations are retrieved from 0th rows;

Select * from test where order by age desc limit 0, 1;

Many clauses can be omitted in the form of select statements. However, if their order cannot be disrupted, the where clause depends on the from clause and the having clause depends on the group by clause, the select field depends on the from clause. Dependency-dependent fields cannot appear before the dependent object or appear out of thin air. Data is queried using the from statement, use the where statement to filter the queried data as needed, use the group by statement to group the filtered data, and use the having clause to filter the grouped data, use the order by statement to sort filtered data, and use the limite x and y statements to output specific rows after sorting.

Select classid, avg (age) as age from test where sex = 'man' group by classid order by desc limit 0, 1;