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

Mysql-day03

1. Mysql storage engine

1.1 introduction to storage engine: it is a functional program that comes with mysql database software.

The functions and data storage methods of each storage engine are also different.

The storage engine processes the processor of the table

1.2 what storage engines are supported by the database service?

Mysql > show engines

InnoDB DEFAULT

1.3 View the storage engine used by existing tables

Show create table table name

1.4 modify the storage engine used by database service by default

] # vim / etc/my.cnf

[mysqld]

Default-storage-engine=myisam

: wq

] # systemctl restart mysqld

1.5 modify the storage engine used by the table, or specify the storage engine used by the table when creating the table

Alter table table engine= storage engine name

Create table table (field list) engine= storage engine name

1.6 Features of common storage engines

Innodb features:

Support for transactions, transaction rollback, row-level locks, foreign keys

Storage method: one table corresponds to 2 storage files

Table name .frm table structure

Table name .ibd data and index

Characteristics of myisam

Transactions, transaction rollback, foreign keys are not supported

Table-level locks are supported

Storage method: one table corresponds to 3 storage files

Table name .frm table structure

Table name .MYD data

Table name .MYI index

Transaction: access to the database service (connect to the database server operation data disconnect)

Transaction rollback: if any step of the operation fails during the execution of the transaction, all previous operations will be restored.

Tables that support transactions have corresponding transaction log file records

Insert the card (establish a connection with the database server)

Transfer: the other party's card number 888888

The amount is 50000

Ok

Prompt transfer successful-50000 + 50000

Prompt transfer failed + 50000

Refund card

The mysql database service uses transaction log file records to perform sql operations on innodb storage engine tables.

Cd / var/lib/mysql/

Ib_logfile0-|

| |-> record SQL command |

Ib_logfile1-|

Insert into T1 values (8888)

Ibdata1-> data source (data information generated after the execution of the sql command)

Lock granularity:

Table-level locks (myisam) lock the entire table (no matter how many rows or rows you access)

Row-level locks (innodb) lock only the currently operated rows in the table

The role of locks: to solve the problem of concurrent access conflicts to tables.

Select * from T1 where id

Load data infile "/ var/lib/mysql-files/passwd"

Into table db3.usertab

Fields terminated by ":"

Lines terminated by "\ n"

Mysql > alter table db3.usertab

Add

Id int (2) primary key auto_increment first

Mysql > select from db3.usertab

Mysql > select from db3.usertab where id=20

Load data infile "/ mysqldata/passwd"

Into table db3.usertab

Fields terminated by ":"

Lines terminated by "\ n"

2.2 Command format for data export and matters needing attention during data export

Mysql > select username,uid from db3.usertab into outfile "/ mysqldata/user1.txt"

Mysql > select * from db3.usertab into outfile "/ mysqldata/user2.txt"

Mysql > select username,uid from db3.usertab into outfile "/ mysqldata/user3.txt" fields terminated by "#"

] # cat / mysqldata/user1.txt

] # cat / mysqldata/user2.txt

] # cat / mysqldata/user3.txt

3. Manage table records (db3.usertab)

Insert record

Mysql > insert into usertab

Values

(43, "yaya", "x", 1001m 1001, "," / home/yaya "," / bin/bash ")

Mysql > insert into usertab

Values (50, "yaya2", "x", 1002 home/7yaya 1002, "", "/ home/yaya2", "/ sbin/nologin"), (51, "7yaya", "x", 1003 people 1003, "", "/ home/7yaya", "/ sbin/nologin")

Insert into usertab (username,homedir,shell)

Values

("lucy", "/ home/lucy", "/ bin/bash")

Insert into usertab (username,homedir,shell)

Values

("lu8cy", "/ home/lu8cy", "/ bin/bash"), ("tom", "/ home/tom", "/ bin/bash"), ("lilei", "/ home/lilei", "/ bin/bash")

+ View record

Select * from db3.usertab

Select * from usertab where id = 1

Select id,username,password from db3.usertab

Select username,uid,shell from usertab where id = 1

-modify the record

Update db3.usertab set password= "A"

Update db3.usertab set password= "x" where id=1

Select * from db3.usertab

-Delete a record

Delete from db3.usertab where id=3

4. Matching conditions (conditions can be added when viewing selcet, modifying update and deleting delete records)

4.1 numerical comparison field name symbol number

=! =

=

Select username from usertab where uid=10

Select id,username,uid from usertab where uid=1001

Select * from usertab where id update usertab set uid=3000,gid=3000 where username= "lucy"

Select id from usertab where name= "yaya" and uid is not null

Update usertab set username=null where id=2

4.6 Fuzzy matching

Field name like 'expression'

% represents zero or more characters

_ table any character

Select username from usertab where username like''

Select username from usertab where username like'a _ t'

Insert into usertab (username) values ("a")

Select username from usertab where username like'a%'

Select username from usertab where username like'%'

4.7 regular matching

Field name regexp 'regular expression'

^ $. * []

Select username from usertab where username regexp'[0-9]'

Select username from usertab where username regexp'^ [0-9]'

Select username from usertab where username regexp'[0-9] $'

Select username from usertab where username regexp 'a.roomt'

Select username from usertab where username regexp'^ a.roomtress'

Select username,uid from usertab where uid regexp'..

Select username,uid from usertab where uid regexp'^.. $'

4.7 four operations (select and update operations can do mathematical calculations)

Field type must be numeric (integer or floating point)

/%

Select id,username,uid from usertab where id10 and uid10 and uid10 and uid10 and uid10 and uid10 and uid select username,uid from db3.usertab where uid > (select avg (uid) from)

Db3.usertab)

Select username from db3.usertab

Where username in

(select user from mysql.user where host= "localhost")

1.3 Multi-table query

Mysql > create table db4.t3

-> select username,uid,shell,homedir from db3.usertab

-> limit 3

Mysql > create table db4.t4

-> select username,uid,gid from db3.usertab limit 5

3 * 5 = 15

Select * from T3 T4; Dicar set

Mysql > select t3.usernameJournal t4.username from t3recoveryt4

-> where

-> t3.username = t4.username

Mysql > select t3.handwriting t4.username from t3authort4 where t3.username = t4.username

Select * from T3 and T4

Where

T3.uid = t4.uid

Select t3.*, t4.gid from T3 dint T4

Where

T3.uid = t4.uid

Select t3.username, t4.username from t3,t4

Where

T3.uid = t4.uid

Select t3.username,t4.username from t3,t4

Where

T3.uid = t4.uid

And t3.username is not null

And t4.username is not null

1.4 join query

Mysql > create table db4.t5

Select username,uid,gid,shell from db3.usertab

Where uid > = 100 and uid create table db4.t6

Select username,uid,gid,shell from db3.usertab

Where uid > = 100 and uid

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.