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

Te database db102; create table db102.t1 (name

Char (6), age int (3))

Insert into db102.t1 values ("jerry", 91024)

Create table db102.t2 (id int (2) zerofill,name char (6))

Insert into T2 values (3, "tom"), (7, "jim"), (9, "lucy")

(12, "bob"), (191, "alic")

Select * from T2

You can use 2-digit numbers to assign values to fields of type year:

01x 69 20XX

70-99 19XX

00 0000

Create table T3 (name char (10), s_year year)

Insert into T3 values ("bob", 01), ("jim", 69), ("lucy", 70)

Select * from T3

What is the difference between date and time types datetime and timestamp?

Create table T4 (

Meetting datetime

Reg_t timestamp

);

Insert into t4 values

(20170523093900, 20170523093900)

Insert into T4 (meetting) values (20170529093058)

Insert into T4 (reg_t) values (20190529094058)

Select * from T4

+

Day02

Mysql index

1 what index? (built on the fields in the table)

Equivalent to "the catalogue of books"

5000 pages

Table of contents 1100 text 101 ~ 5000

Strokes 9 2000-2010

Radical

Pinyin

Delete, add, modify

Stuinfo database directory / stuinfo.frm .ibd

Name age sex class

Jim

Jerry

Abob

Lili

Han × ×

Select * from stuinfo where class= "1702"

2 what are the advantages and disadvantages of the index?

Advantage: speed up the query

Disadvantages: it takes up physical storage space, which slows down the speed of writing to the table.

+ +

Mysql index type:

INDEX: general index *

UNIQUE: unique index

FULLTEXT: full-text index

PRIMARY KEY: primary key *

FOREIGN KEY: foreign key *

Use INDEX: general index

Rules for the use of indexes:

There can be multiple INDEX fields in a table

The value of the field can be duplicated, and the value of NULL can be assigned.

Fields that are used as query conditions are often set to INDEX fields

The KEY flag of the INDEX field is MUL

Create an index index

To create a table is to create

Create table T5 (

Name char (10)

Age int (2)

Sex enum ("boy", "girl")

Index (name)

Index (sex)

);

Set the existing field to the index field

Mysql > create index index name on table name (field name)

View

Desc table name; Key

Show index from table name

Table: t1

Key_name: aaa

Column_name: name

Index_type: BTREE (B+TREE HASH)

Binary tree

1: 10

1-5 6-10

Delete

Drop index index name on table name

+

Use PRIMARY KEY: primary key *

Use the rules?

There can be only one primary key field in a table

Duplicates are not allowed for the corresponding field values, and NULL values are not allowed to be assigned.

The KEY flag for the primary key field is PRI

If you have multiple fields as PRIMARY KEY, called compound primary keys, you must have a

Start to create.

Use with auto_increment to automatically increase the value of the field

The fields in the table that can uniquely identify the record are often set as the primary key field [record number]

Field]

Stu_id name age

1 bob 19

2 lucy 18

3 alic 21

4 jerry 19

Create table T9 (

Stu_id int (2) primary key auto_increment

Name char (10)

Age tinyint (2) unsigned

);

Insert into T9 (name,age) values ("bob", 21)

Insert into T9 (name,age) values ("lucy", 21)

Insert into T9 (name,age) values ("lili", 21)

Select * from T9

Create table T6 (

Name char (10)

Age int (2)

Sex enum ("boy", "girl")

Primary key (name)

);

Create table T7 (

Name char (10) primary key

Age int (2)

Sex enum ("boy", "girl")

);

Set an existing field in the table as the primary key

Alter table table name add primary key (field name)

Compound primary key:

Only if the values of multiple fields that match the primary key are not repeated at the same time.

PRI PRI

Cip port status

1.1.1.1 21 deny

1.1.1.1 25 allow

2.1.1.1 25 deny

Create table T8 (

Cip varchar (15)

Port smallint (2)

Status enum ("allow", "deny")

Primary key (cip,port)

);

Insert into t8

Values

("1.1.1.1", 21, "deny")

("1.1.1.1", 25, "allow")

("2.2.3.2", 25, "deny")

Delete primary key

Alter table table name drop primary key

+ +

Unique unique Index:

There can be multiple UNIQUE fields in a table

Duplicates are not allowed for corresponding field values.

The KEY flag of the UNIQUE field is UNI

The value of the UNIQUE field is allowed to be NULL, when it is modified to not allow it to be NULL

This field limit is the same as the primary key

Create table T10 (

Name char (10)

Shf_id varchar (18)

Ks_num char (8)

Age int (2)

Sex enum ("boy", "girl")

Index (name)

Unique (shf_id)

Unique (ks_num)

);

Create unique index index name on table (field name)

Drop index index name on table

Mysql > drop index shf_id on T10

+ +

FOREIGN KEY: foreign key

Function: when assigning a value to a field in the current table, the value of the field can only be in the word of another table

Select from the segment value.

Use the rules?

The storage engine for the table must be innodb

Field types must match

The referenced field must be one of the indexes (usually primary key)

Financial statement cwb

Create table cwb (

Cwb_id int (2) primary key auto_increment

Name varchar (15)

Pay float (7 dint 2)

) engine=innodb

Create table bjb (

Bjb_id int (2)

Name varchar (15)

Age tinyint (2)

Foreign key (bjb_id) references cwb (cwb_id) on update

Cascade on delete cascade

) engine=innodb

Desc bjb

Show create table bjb

Insert into cwb (name,pay) values ("bob", 20000)

("lucy", 20000)

Insert into bjb values (1, "alic", 23)

Update cwb set cwb_id=8 where cwb_id=2

Delete from cwb where cwb_id=3

Delete foreign key

Show create table table name; # View table creation command

Alter table table drop foreign key foreign key name

Add a foreign key to an existing table.

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

Field name) on update cascade on delete cascade

Alter table bjb add foreign key (bjb_id) references

Cwb (cwb_id) on update cascade on delete cascade

+ +

2. Mysql storage engine

The working process of the mysql service:

Connection pool

Sql interface

Analyzer

Optimizer select insert update delete

Query cache

Storage engine

File system: hard disk (/ var/lib/mysql)

Administrative tools: commands that come with when installing service packages

1 what storage engine?

Is the mysql database service software self-contained program, is the table processor, different processing

The device has different functions and data storage methods.

2 View the storage engine?

View the storage engine show create table table name used by the table

View the storage engine show engines used by database service by default

InnoDB DEFAULT

3 set up the storage engine

Set the storage engine used by the table

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

Set the storage engine used by database service by default

Vim / etc/my.cnf

[mysqld]

Validate_password_policy=0

Validate_password_length=6

Default-storage-engine=myisam

: wq

# systemctl stop mysqld

# systemctl start mysqld

# mysql-uroot-p123456

Mysql > show engines

4 characteristics of commonly used storage engines

Myisam

Table level lock

Transaction and transaction rollback are not supported

Foreign key

Table. Frm table structure

Table. MYD table records select * from table

Table. MYI table index

Innodb

Row level lock

Support for transactions and transaction rollback

Foreign keys are supported

Table. Frm table structure

Table. Ibd table record + table index

What business? When accessing a database, the process of connecting to disconnecting from the beginning is called

Business.

Insert card prompt for password-- > login successful

Transfer receiving card number

The amount is 5w

confirm

The money is being transferred. Success

Refund card

Transaction rollback? Transaction execution process, any step execution fails, restore all previous

Operation.

Transaction log file

/ var/lib/mysql/

Ib_logfile0

Ib_logfile1

Ibdata1

Locks: resolving concurrent access conflicts

Read lock myisam

Select * from T1 where id > = 10

Innodb

Write lock

Update

Insert

Delete

Innodb

Pc1 update T1 set name= "tom" where name= "jim"

Pc2 update T1 set name= "lucy" where name= "jim"

Row-level locking: when the client accesses the database, only the rows operated by the sql command are locked.

Table-level lock: when the client accesses the database, the table operated by the sql command is locked

+

5 how to determine the storage engine used by the table when creating the table at work

Tables with many writes are suitable to use the innodb storage engine

Tables with many query operations are suitable to use myisam 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.