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

Manage table records

Increase

Insert into library. Table values (list of field values)

Insert into library. Table (field values list) values (field values list)

Insert into library. Table values (field values list), (field values list)

Query

Single table query

Select field name list from library. Table where condition

Representation of conditional matching

Numerical comparison

Field name symbol value symbol: > > =

< select id,name from user where id between 45 and 47; +-------+----------+ | id | name | +-------+----------+ | 45 | | | 46 | null | | 47 | NULL | +-------+-----------+ 3 rows in set (0.00 sec) 不显示重复值 distinct 字段名 select shell from user; select distinct shell from user; mysql>

Select distinct shell from user

+-+

| | shell |

+-+

| / bin/bash |

| / sbin/nologin |

| / bin/sync |

| / sbin/shutdown |

| / sbin/halt |

| / bin/false |

| | NULL |

+-+

7 rows in set (0.00 sec)

Logical matching: when there are multiple conditions

Multiple conditions of logic and and must be true.

One of the logical or or conditions can be established.

Logic is not! Take reverse

Select id,name from user where name= "zhangsan" and uid=500 and shell= "/ bin/bash"

Operation +-* /%

Field name symbol field name

Select uid+gid as heid from user where name='root'

Select uid+gid heid from user where name='root'

Fuzzy query

Where field name like 'expression'

_ match any one character 0 or more characters

Select name from user where name like'_ 'and uid select name,uid from user where uid regexp' ^.. $'

Function

Simple filtering / statistics

Average of avg () sets

Sum () sums the parameters of the set

The minimum value in the min () collection

The maximum value in the max () collection

Number of records recorded by count ()

Query sorting

Sql query order by field name asc/desc (descending)

Select name,uid from user where uid between 10 and 50 order by uid

Query grouping

Sql query group by field name

Select shell from user where uid between 10 and 50 group by shell

Similar to not showing repetition

Query limits the number of rows displayed limit

Select shell from user where uid between 10 and 50 limit 1

Select * from user limit 1 position # displays the previous line of the query

Select * from user limit 2 and 3 characters # set the display line range from the second line (the number of lines starts to 0 lines) to display 3 lines

Multi-table query

Select field name list from table name list; Cartesian set

Select field name list from table name list where condition

Create table studb.t1 select name,uid,shell from user limit 3

Create table studb.t2 select name,uid,homedir from user limit 4

Select t1.handwriting t2.homedir from T1 authoring T2 where t1.uid = t2.uid

Nested query

Where nested query: use the query result of the inner layer as the query condition of the outer query

Select field name list from table name where condition (select field name list from table name where condition)

Select name,uid from user where uid > (select avg (uid) from user)

Select name from user where name in (select user from mysql.user)

Copy tables: function: quickly build tables, back up tables

Create table library. Table sql query

Copy tabl

Create database dbbak

Create table dbbak.user2 select * from user

The replicated table does not have the properties and key values of the source table

Copy table structure

Create table dbbak.user3 select * from user where 1: 2

Join query

Left join query

Select field list from table A left join B on condition

Right join query

Select field list from table A right join B on condition

Create table studb.t3 select name,uid,shell from user limit 3

Create table studb.t4 select name,uid,shell from user limit 5

Mysql > select * from t3 left join T4 on t3.uidroomt4.uidtern # mainly displayed on the left

Mysql > select * from T3 right join T4 on t3.uidroomt4.uid; # mainly displayed on the right

Modify

Batch modification

Update library. Table set field name = value, field name = 'value'

Mysql > update user set age= "18"

Modify the value of the specified record field

Update library. Table set field name = value, field name = 'value' where condition

Mysql > update user set name= "zhangsan" where id=48

Delete

Delete units by behavior

Delete from library. Table where condition

Mysql > delete from user where shell is NULL

Mysql key value (limits how to assign values to fields)

General index index

What is an index: a tree directory structure similar to "the directory of a book"

The advantage of index: speed up the query

Disadvantages of index: slow down the speed of writing (insert update delete); take up physical storage space

Index index using a normal index

Rules for the use of indexes

It can be repeated by default, and the NULL value can be assigned.

Can be made up of multiple index fields

Use the query condition as an index

View decs table name

Show index from table name

Flag MUL

Create

Create an index when creating a table:

Mysql > create table T25 (

-> name char (10)

-> age int

-> sex enum ("boy", "girl")

-> index (sex) # Index name is the same as field name by default

-> index (name)

->)

Create an index on an existing table create index index name on table name (field name assigned to the index)

Mysql > create index age on T21 (age)

Mysql > show index from T21\ G

Table: t21

Non_unique: 1

Key_name: age

Seq_in_index: 1

Column_name: age

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

Default index type (Index_type): BTREE (binary tree)

Hash B+TREE is also supported

Delete drop index index name on table name

Mysql > drop index A1 on T21

Fulltext full-text index

Unique unique index

There are multiple unique fields in a table

Can be empty, but the value cannot be repeated.

Mysql > create table t211 (stu_id char (9), name char (10), sex enum ('boy','girl'), unique (stu_id))

Mysql > desc t211

The key logo is UNI

Mysql > alter table t211 modify stu_id char (9) not null

Mysql > desc t211

The key flag is PRI but not the primary key

Mysql > drop index stu_id on t211

Create unique index

Mysql > create unique index stu on t211 (stu_id)

Primary key

Primary key usage rules

There can be only one primary key in a table

Repetition is not allowed. Cannot be empty.

View decs table name

Flag PRI

Create a primary key when creating a table:

Mysql > create table T26 (

-> name char (10)

-> age int

-> likes set ("a", "b", "c")

-> primary key (name)

->)

Mysql > create table T22 (

-> id int primary key

-> name char (10)

->)

Create a primary key in an existing table:

Mysql > alter table T25 add primary key (name)

Delete alter table table name drop primary key

Mysql > alter table T25 drop primary key

Multiple fields of compound primary key together as the value of primary key field is not allowed to repeat at the same time

View

Mysql > desc T28

Create a primary key when creating a table:

Mysql > create table T28 (

Cip char (15)

-> port smallint

-> status enum ("allow", "deny") defualt "deny"

-> primary key (cip,port)

->)

Create a primary key in an existing table:

Mysql > alter table T28 add primary key (cip,port)

Delete

Mysql > alter table T28 drop primary key

Primary keys are generally used with auto_increment

Automatic increase of field value

Meet the conditional primary key numerical type

Create a tabl

Mysql > create table T27 (

-> id int (2) zerofill primary key auto_increment

-> name char (10)

-> class char (4)

-> index (name)

->)

Delete the auto-growing primary key

Mysql > alter table T27 modify id int (2) unsigned zerofill not null

Mysql > alter table T27 drop primary key

Foreign key

Function: restricts the assignment of values to fields. The value must be selected within the range of the field values specified in the specified table

The storage engine for the table must be innodb

Field types should be consistent

The referenced field must be one of the index types

Create command

Foreign key (field name) references table name (field name)

On update cascade synchronous updates

On delete cascade synchronous deletion

Update table name set field name = value where condition

Delete from table name where condition

Delete foreign key

Mysql > show create table xsb;# View Table creation Command

You can view the foreign key name

Alter table table name drop foreign key foreign key name

Add a foreign key to the table that has been created

Alter table table name add foreign key (field name) references table name (field name)

On update cascade synchronous updates

On delete cascade synchronous deletion

Architecture of mysql services: (8 functional modules)

Connection pooling: check if mysql can be connected

Sql interface: executed commands are passed to mysqld

Parser: parsing syntax errors

Optimizer: optimizing execution command

Query cache: each query allocated by the physical memory of the database finds the query cache first.

Storage engine:

File system:

Administrative tools: install some of the software tools provided by mysql

Mysql Storage engine:

Introduction to Storage Engin

Mysql database service software comes with the program.

Different storage engines have different functions and data storage methods.

View the storage engines supported by the database service

Mysql > show engines

| | InnoDB | DEFAULT | # default default storage engine |

| | MyISAM | YES |

Commonly used storage engine

Myisam

Table. Frm table structure

Table .MYI index information

Table .MYD data

Support for table-level locks (locking a table)

Transaction not supported transaction rollback is not supported

Innodb

Table. Frm table structure

Table. Ibd table structure index information

Support for row-level locks (locking only rows that are currently being accessed)

Support for transaction rollback

Transaction log files: record actions performed on the tables of the innodb storage engine

/ var/lib/mysql/ib_logfile*

Lock:

Lock type: read lock select

Write lock insert delete update

Lock granularity: row-level lock table-level lock

Locking: resolving concurrent access conflicts

Transaction: a process from the beginning to the end of the access

Transaction rollback: any step of a data access fails, resuming all operations.

Characteristics of transactions: consistency, atomicity, isolation

The most typical transaction operation: bank transfer

How the work determines the storage engine used by the table

Tables that receive many writes are suitable for using the innodb storage engine. (large concurrent access)

Tables that receive many reads are suitable for using the myisam storage engine. (save resources)

Set up the storage engine for the database service

Set the default storage engine for the service

[mysqld]

Defaulf-storage-engine=myisam

Mysql > create table tt1 (id int (2))

Mysql > show create table tt1

...

| | tt1 | CREATE TABLE `tt1` (

`id`int (2) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

...

Modify the storage engine of the table

Alter table Table name engine= Storage engine

Set up the storage engine for the table

Creat table table name (...) engine= storage engine

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.