The construction of MySQL and the basic operation of adding, deleting and modifying. 04/09 Update SLTechnology News&Howtos

The construction of MySQL and the basic operation of adding, deleting and modifying.

2025-04-09 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

MySQL

Build MySQL server-> CPU, memory, hard disk (storage)

First, install MySQL

Yum-y install perl-Data-Dumper perl-JSON perl-Time-HiRes / / install dependency files

Mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar

Mysql-community-client-5.7.17-1.el7.x86_64.rpm

Mysql-community-common-5.7.17-1.el7.x86_64.rpm

Mysql-community-devel-5.7.17-1.el7.x86_64.rpm

Mysql-community-embedded-5.7.17-1.el7.x86_64.rpm

Mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm

Mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm

Mysql-community-libs-5.7.17-1.el7.x86_64.rpm

Mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

Mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm

Mysql-community-server-5.7.17-1.el7.x86_64.rpm

Mysql-community-test-5.7.17-1.el7.x86_64.rpm

Rpm-Uvh mysql-community-*.rpm / / Source package installation

Rpm-qa | grep-I mysql / / View the post-installation status

View configuration fil

Ls / etc/my.cnf

Start the service

Systemctl status mysqld

Ps-C mysqld

Netstat-utnalp | grep: 3306

Data catalog

Ls / var/lib/mysql/

Grep mysql / etc/passwd View the owner's group

Modify the table

Modify MySQL password

# ls / var/log/mysqld.log

# grep password / var/log/mysqld.log View the initialization password of the local database

# rpm-qf / usr/bin/mysql

# mysql-hlocalhost-uroot-p 'password'

Mysql > set global validate_password_policy=0; / / change password to verify length only

Query OK, 0 rows affected (0.00 sec)

Mysql > set global validate_password_length=6; / / modify password length to 6

Query OK, 0 rows affected (0.00 sec)

Mysql > alter user user () identified by "123456" / / change login password

Use permanent Profil

Vim / etc/my.cnf

Validate_password_policy=0

Validate_password_length=6

The process of storing data on a database server

1. Connect to the database server

The client provides its own connection tools (graphics command line)

-- use mysql on the command line

2. Create a library (folder)

Create a library

-- Library names are available with numbers, letters, and underscores

-- can not be pure numbers, keywords, special symbols

Create database library name

View existing libraries

Show databases

Delete Library

Drop database library name

Switching library

Use library name

Check the existing tables in the library

Show tables; / / table, equivalent to system files

View the current library

Select database ()

3. Create a table (file)

Create table library name. Table name (

Field name character type

Field name numeric type

. Name char (10)

. Age int

)

Insert table record

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

View table structure

Describe table name

View table records

Select * from library name. Table name

Delete table record

Delete from library name. Table name

Delete tabl

Drop table

Mysql data type

What are the supported data types?

-- numerical type: weight, height, performance, salary

-- character type: name, work unit, mailing address

Enumerated: hobbies, gender

Date and time type: date of birth, time of registration

Numeric types: integer, floating point

Depending on the range integer type of the stored value, the type is:

Tinyint smallint mediumint int bigint

Unsigned unsigned

Floating-point type: divided according to the range of stored values

Single precision (nMagne m) double precision (nMagne m)

N represents total digits

M represents the number of decimal places

Pay float (5par 2)

Up to 999.99

Minimum-999.99

Mysql > create table T1 (id tinyint unsigned zerofill)

Query OK, 0 rows affected (0.40 sec)

Mysql > desc T1

+-- +

+-- +

| | id | tinyint (3) unsigned zerofill | YES | | NULL |

+-- +

1 row in set (0.00 sec)

Mysql > create table T2 (pay float (7penny 2))

Query OK, 0 rows affected (0.46 sec)

Mysql > desc T2

+-+ +

+-+ +

| | pay | float (7Page2) | YES | | NULL | |

+-+ +

1 row in set (0.00 sec)

Mysql > create table T4 (

-> age float (7 and 2)

-> high float (3pr 2)

->)

Query OK, 0 rows affected (0.36 sec)

Mysql > desc T4

+-+ +

+-+ +

| | age | float (7Page2) | YES | | NULL | |

+-+ +

2 rows in set (0.00 sec)

Insert into T4 values (11211 dint 1.82)

# # #

Character type

-fixed length: char (number of characters)

Maximum length of 255 characters

Fill in the space on the right if it is not enough to specify the number of characters.

Unable to write data when the number of characters exceeds

-varchar (number of characters)

Allocate storage space according to the actual size of the data

Unable to write data when the number of characters exceeds

Large text type: text/blob

Large number of characters and used for 65535 storage

Mysql > create table T8 (

-> name char (10)

-> class char (7)

Address char (15)

> mail varchar (30)

->)

Mysql > desc T8

+-+ +

+-+ +

+-+ +

4 rows in set (0.00 sec)

Mysql > insert into T8 values ("jim", "nsd1709", "beijing", "123456@qq.com")

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T8

+-+

+-+

| | jim | nsd1709 | beijing | 123456@qq.com | |

+-+

1 row in set (0.00 sec)

Date time type:

Year YYYY 2017

Date date YYYYMMDD 20171220

Time time HHMMSS 155302

Date and time:

Datetime YYYYMMDDHHMMSS

Timestamp YYYYMMDDHHMMSS

Mysql > create table T9 (

-> name char (10)

-> age tinyint

-> s_year year

-> uptime time

-> birthday date

-> party datetime

->)

Query OK, 0 rows affected (0.37 sec)

Mysql > desc T9

+-+ +

+-+ +

| | age | tinyint (4) | YES | | NULL |

+-+ +

6 rows in set (0.00 sec)

Mysql > insert into T9 values ("Tom", 1992, 073000, 19920221122020, 20180131122100)

Query OK, 1 row affected, 1 warning (0.04 sec)

Mysql > select * from T9

+-+ +

+-+ +

| | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |

+-+ +

1 row in set (0.00 sec)

# # #

Time function

Now () gets the system date and time when the function is called

Dynamically get the system date and time when sysdate () executes

Sleep (N) hibernate for N seconds

Curdate () gets the current system date

Curtime () gets the current system moment

Month () gets the month in the specified time

Date () gets the date in the specified time

Time () gets the time in the specified time

Mysql > select from T9

+-+ +

+-+ +

| | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |

| | Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 |

+-+ +

2 rows in set (0.00 sec)

Mysql > insert into T9 values ("kenji", 19 year (now ()), time (now ()), date (now ()), now ())

Query OK, 1 row affected (0.04 sec)

Mysql > select from T9

+-+ +

+-+ +

| | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |

| | Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 |

| | kenji | 19 | 2017 | 03:55:12 | 2017-12-20 | 2017-12-20 03:55:12 |

+-+ +

3 rows in set (0.00 sec)

# # #

Enumeration type: the value of a field can only be selected within the range of the list

The field name enum (values list) can only select one value, which can be selected by a number when assigning a value.

Field name set (values list) multiple selection

Mysql > create table T12 (name char (10), sex enum ("boy", "girl"), yourlikes set ("book", "film", "game", "study"))

Query OK, 0 rows affected (0.43 sec)

Mysql > desc T12

+-- +

+-- +

| | sex | enum ('boy','girl') | YES | | NULL |

+-- +

3 rows in set (0.00 sec)

Mysql > insert into T12 values ("ZhouMing", "boy", "book,film")

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T12

+-+

+-+

+-+

1 row in set (0.00 sec)

# # #

Constraints: action limit assignment

-- Null is allowed to be empty. Default setting

-- NO NULL cannot be empty

Key index type

Default sets the default, which defaults to NULL

Mysql > create table T13 (name char (10) not null, sex enum ('man','woman') not null default "man", age tinyint not null default 23)

Query OK, 0 rows affected (0.37 sec)

Mysql > desc T13

+-+ +

+-+ +

| | sex | enum ('man','woman') | NO | | man |

| | age | tinyint (4) | NO | | 23 | |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into T13 (name) values ("chihiro")

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T13

+-+

| | name | sex | age | |

+-+

| | chihiro | man | 23 | |

+-+

1 row in set (0.00 sec)

# # #

Modify table structure

Mysql > alter table table name execute action

Add a new field

-add field (width) constraint

-add field (width) constraint first; / / added to the front of the table

-add field (width) constraint after field name; / / added after the specified field name

Delete a field

-drop field name

Modify field type

-modify field type (width) constraint; / / cannot be modified to a type that conflicts with an existing value

Modify field name

-change source field name new field name type (width) constraint

Modify table name

Alter table source table name rename new table name

MySQL

The width of the numeric type is the display width, and the size of the assignment to the field cannot be limited, which is determined by the type.

Mysql > create table T21 (

-> name char (5)

-> age int (2)

->)

Mysql > insert into T21 values ("coco", 1992)

Mysql > select * from T21

+-+ +

| | name | age |

+-+ +

| | coco | 1992 | |

+-+ +

1 row in set (0.00 sec)

The width cannot limit the size of the field, depending on the type. Save storage space. The width of age (2) is 2, but the assignment can be greater than 2.

Mysql > create table T24 (id int (2) zerofill,age int (5) zerofill)

Set the width, not enough to fill with 0. (zerofii uses 0 to fill bits)

Mysql > insert into T24 values

Mysql > select * from T24

+-+ +

| | id | age |

+-+ +

| | 07 | 00007 | |

+-+ +

1 row in set (0.00 sec)

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

1.1 General Index index

1.1.1 what is an index? Similar to the "catalogue of books" tree directory structure

Eg:500 Page-> Catalog Information 1-20-> body 21-500

1.1.2 advantages of indexing

Speed up the query

1.1.2 shortcomings of the index

Slow down writing (insert update delete)

Take up physical storage space

1.1.3 using the normal index index

-Index usage rules

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

The KEY flag of the INDEX field is MUL

-View Index

Desc table name

Show index from table name; / / View the specific value of the index information

Create an index

Default index type: BTREE (binary tree) 1-10 1-5 6-10 hash B+Tree

Create index index name on table name (field name)

1) create an index when creating a table

Mysql > create table T25 (

-> name char (10)

-> age int

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

-> index (sex)

-> index (name)

->)

Mysql > desc T25

+-+ +

+-+ +

| | age | int (11) | YES | | NULL |

| | sex | enum ('boy','girl') | YES | MUL | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > create index name on T21 (name); / / it is customary to make the index name the same as the field name

Query OK, 0 rows affected (0.42 sec)

2) create an index on an existing table

Mysql > desc T21

+-+ +

+-+ +

| | age | int (2) | YES | | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > create index name on T21 (name)

Query OK, 0 rows affected (0.42 sec)

Ysql > desc T21

+-+ +

+-+ +

| | age | int (2) | YES | | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > show index from T21\ G

1. Row

Table: t21

Non_unique: 1

Key_name: name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

Delete

Drop index index name on table name

# # #

Primary key primary key

Matters needing attention

-there can be only one primary key field in a table

-the corresponding field values cannot be duplicated, and NULL values are not allowed to be assigned.

-if multiple fields are used as PRIMARY KEY, called compound primary keys, they must be created together

-the KEY flag of the primary key field is PRI

Usually used with AUTO INCREMENT

-often set the field in the table that uniquely identifies the record as the primary key field [record number field]

1) existing tables have primary keys

Mysql > drop index name on T25

Mysql > desc T25

+-+ +

+-+ +

| | age | int (11) | YES | | NULL |

| | sex | enum ('boy','girl') | YES | MUL | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > select * from T25

Empty set (0.00 sec)

Mysql > alter table T25 add primary key (name); / / there can be only one primary key

Query OK, 0 rows affected (0.47 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table T25 drop primary key; / / remove primary key

2) create a new table and set a primary key

Mysql > create table T26 (

-> name char (10)

-> age int

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

-> primary key (name)

->)

Mysql > desc T26

+-+ +

+-+ +

| | age | int (11) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

When building a table, the above example can also be added in the middle.

Eg:name char (10) primary key

The effect is the same as T26

3) compound primary key: multiple fields are used as primary keys, and fields are not allowed to be repeated at the same time.

Mysql > create table T28 (cip char (15), port smallint, status enum ("allow", "deny") default "deny", primary key (cip,port)

Query OK, 0 rows affected (0.31 sec)

Mysql > desc T28

+-- +

+-- +

| | cip | char (15) | NO | PRI | NULL |

+-- +

3 rows in set (0.00 sec)

Mysql > alter table T28 drop primary key; / / remove primary key

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

4) used with auto_increment

Automatic increase of field value

Eg: id name age class

Jim 21 1709

Let the value of the id field automatically increase by + 1

Condition: primary key and numeric

Mysql > create table T29 (

-> id int (2) zerofill primary key auto_increment

-> name char (10)

-> class char (10)

-> index (name)

->)

Query OK, 0 rows affected (0.22 sec)

Mysql > desc T29

+-- +

+-- +

| | id | int (2) unsigned zerofill | NO | PRI | NULL | auto_increment |

+-- +

3 rows in set (0.00 sec)

Mysql > insert into T29 (name,class) values ("tom", "1709")

Mysql > insert into T29 (name,class) values ("jerry", "1709")

Mysql > insert into T29 values (9, "jack", "1709"); / / you can assign id yourself, but id belongs to the primary key and cannot have the same name

Mysql > insert into T29 (name,class) values ("rose", "1709"); / / Auto growth will select the value with the highest number for automatic growth. If you set id=9 before, and then enable auto growth, it will be 10.

Mysql > select * from T29

+-- +

+-- +

| | 01 | tom | 1709 | |

| | 02 | jerry | 1709 | |

| | 09 | jack | 1709 | |

| | 10 | rose | 1709 | |

+-- +

4 rows in set (0.00 sec)

Mysql > alter table T29 drop primary key; / / cannot delete the primary key because id is set to auto_increment auto-growth, and the command must be a primary key to set up.

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Mysql > alter table T29 modify id int; / / modify field type to cancel automatic growth

Mysql > alter table T29 drop primary key; / / Primary key deleted successfully

Query OK, 4 rows affected (1.00 sec)

# # #

UNIQUE unique index

Unique index cannot be assigned the same value, it can be NULL

1) specify the UNIQUE field when creating the table

Mysql > create table t211 (stu_id char (9), name char (10), sex enum ("boy", "girl"), unique (stu_id)); specify the student number as the unique index

Mysql > desc t211

+-+ +

+-+ +

| | sex | enum ('boy','girl') | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

# # #

Mysql > create table t212 (stu_id char (9) not NULL, name char (10), sex enum (boy, "girl"), unique (stu_id))

Query OK, 0 rows affected (0.26 sec)

/ / if stu_id is specified as a unique index, but it is not allowed to be null, the description information shows that stu_id is RPI, but in fact the primary key does not exist and cannot be deleted

Mysql > desc t212

+-+ +

+-+ +

| | sex | enum ('boy','girl') | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

# #

Practice

Mysql > desc stuinfo

+-- +

+-- +

+-- +

3 rows in set (0.00 sec)

Alter table stuinfo add stu_id char (7) first

Create ubique index stu_id on stuinfo (name)

Alter table stuinfo add id int (2) zerofill primary key auto_increment

# # #

Foreign key

Foreign key (field name) references table name (field name) on uptate cascade on delete cascade synchronous update, synchronous deletion

Function: if you restrict the assignment to a field, the value must be selected within the range of the field value specified in the specified table

Mysql > create table jfb (

-> id int (2) primary key auto_increment

-> name char (10)

-> pay float (7 and 2)

->) engine=innodb

Establish a reference table

Insert into jfb (name,pay) values ("tom", 20000), ("lucy", 20000)

Query OK, 2 rows affected (0.07 sec)

Mysql > create table xsb (num int (2), name char (10), class char (9), foreign key (num) references jfb (id) on update cascade on delete cascade) engine=innodb

Synchronous modification

Update table name set field name = value where condition; / / the condition is the original (field name = value)

Synchronous deletion

Delete from table name where condition

The referenced table cannot be deleted at will

Delete foreign key field

Show create table table name

Alter table table name drop foreign key foreign key name

Eg:

This case requires familiarity with the types and methods of operation of MySQL indexes, and focuses on the following tasks:

Creation / deletion of normal index, unique index, primary key index

Creation / deletion of self-increasing primary key index

Establish employee table yg and payroll gz, set up foreign keys to achieve synchronous update and synchronous deletion

To implement this case, you need to follow these steps.

Step 1: index creation and deletion

Specify the INDEX index field when creating the table

Create the library home:

Mysql > create database home

Query OK, 1 row affected (0.00 sec)

Multiple INDEX index fields are allowed. For example, the following action creates a tea4 table in the home library, with id and name as index fields:

Mysql > USE home

Database changed

Mysql > CREATE TABLE tea4 (

-> id char (6) NOT NULL

-> name varchar (6) NOT NULL

-> age int (3) NOT NULL

-> gender ENUM ('boy','girl') DEFAULT' boy'

-> INDEX (id), INDEX (name)

->)

Query OK, 0 rows affected (0.59 sec)

Looking at the field structure of the newly created tea4 table, you can find that the KEY flag of the two non-empty index fields is MUL:

Mysql > DESC tea4

+-+ +

+-+ +

| | id | char (6) | NO | MUL | NULL |

| | age | int (3) | NO | | NULL |

| | gender | enum ('boy','girl') | YES | | boy |

+-+ +

4 rows in set (0.00 sec)

2) Delete an INDEX index field of an existing table

For example, delete the INDEX index field named named in the tea4 table:

Mysql > drop INDEX name ON tea4; / / Delete the index of the name field

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > DESC tea4; / / confirm deletion result

+-+ +

+-+ +

| | id | char (6) | NO | MUL | NULL |

| | age | int (3) | NO | | NULL |

| | gender | enum ('boy','girl') | YES | | boy |

+-+ +

4 rows in set (0.00 sec)

3) set the INDEX index field in an existing table

For example, index the age field of the tea4 table with the name nianling:

Mysql > CREATE INDEX nianling ON tea4 (age); / / create an index on the specified field

Query OK, 0 rows affected (0.62 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > DESC tea4; / / confirm the creation result

+-+ +

+-+ +

| | id | char (6) | NO | MUL | NULL |

| | age | int (3) | NO | MUL | NULL |

| | gender | enum ('boy','girl') | YES | | boy |

+-+ +

4 rows in set (0.00 sec)

4) View the index information of the specified table

Use the SHOW INDEX directive:

Mysql > SHOW INDEX FROM tea4\ G

1. Row

Table: tea4

Non_unique: 1

Key_name: id

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE / / use the B-tree algorithm

Comment:

Index_comment:

2. Row

Table: tea4

Non_unique: 1

Key_name: nianling / / Index name

Seq_in_index: 1

Column_name: age / / field name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.00 sec)

5) specify the UNIQUE index field when creating the table

UNIQUE means uniqueness, and multiple fields in the same table can be unique.

For example, create the tea5 table, set the id and name fields to set the UNIQUE index, and set the age field to set the INDEX index:

Mysql > CREATE TABLE tea5 (

-> id char (6)

-> name varchar (4) NOT NULL

-> age int (3) NOT NULL

-> UNIQUE (id), UNIQUE (name), INDEX (age)

->)

Query OK, 0 rows affected (0.30 sec)

Looking at the field structure of the newly created tea5 table, we can find that the KEY flag of the UNIQUE field is UNI;. In addition, because the field name must meet the non-empty constraint of "NOT NULL", it will automatically become the PRIMARY KEY primary key field when it is set to UNIQUE:

Mysql > DESC tea5; / / confirm the setting result

+-+ +

+-+ +

| | id | char (6) | YES | UNI | NULL |

| | age | int (3) | NO | MUL | NULL |

+-+ +

3 rows in set (0.03 sec)

6) delete the UNIQUE index and set the UNIQUE index field in the existing table

First delete the unique index of the name field of the tea5 table (the same way you delete the INDEX index):

Mysql > DROP INDEX name ON tea5; / / clear the UNIQUE index

Query OK, 0 rows affected (0.97 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > DESC tea5; / / confirm deletion result

+-+ +

+-+ +

| | id | char (6) | YES | UNI | NULL |

| | age | int (3) | NO | MUL | NULL |

+-+ +

3 rows in set (0.00 sec)

Re-index the name field of the tea5 table and confirm the result:

Mysql > CREATE UNIQUE INDEX name ON tea5 (name); / / build UNIQUE index

Query OK, 0 rows affected (0.47 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > DESC tea5; / / confirm the setting result

+-+ +

+-+ +

| | id | char (6) | YES | UNI | NULL |

| | age | int (3) | NO | MUL | NULL |

+-+ +

3 rows in set (0.00 sec)

7) set the PRIMARY KEY primary key index when creating the table

The primary key index has actually been touched before and can be specified directly when creating the table. If there is no primary key field in the table at first, the newly set non-empty UNIQUE field is equivalent to having a PRIMARY KEY primary key constraint.

There can be only one primary key field in each table.

When creating a table, you can specify PRIMARY KEY; directly in the constraints section of a field or PRIMARY KEY (a field name) at the end. For example:

Mysql > CREATE TABLE biao01 (

-> id int (4) PRIMARY KEY, / / constrain the field definition directly

-> name varchar (8)

->)

Query OK, 0 rows affected (0.19 sec)

Or:

Mysql > CREATE TABLE biao02 (

-> id int (4)

-> name varchar (8)

-> PRIMARY KEY (id) / / all fields are defined and finally specified

->)

Query OK, 0 rows affected (0.17 sec)

When creating a table, if the primary key field is of type int, you can also set the AUTO_INCREMENT self-increment property for it, so that when you add a new table record, the value of this field will automatically increase one by one, without having to specify it manually. For example, create a new tea6 table with the id column as a self-incrementing primary key field:

Mysql > CREATE TABLE tea6 (

-> id int (4) AUTO_INCREMENT

-> name varchar (4) NOT NULL

-> age int (2) NOT NULL

-> PRIMARY KEY (id)

->)

Query OK, 0 rows affected (0.29 sec)

8) delete the PRIMARY KEY primary key index of an existing table

If you want to remove the PRIMARY KEY constraint on a table, you need to modify it through the ALTER TABLE directive. For example, the following action clears the primary key index of the biao01 table.

Before clearing (primary key is id):

Mysql > DESC biao01

+-+ +

+-+ +

| | id | int (4) | NO | PRI | NULL |

+-+ +

2 rows in set (0.00 sec)

Clear operation:

Mysql > ALTER TABLE biao01 DROP PRIMARY KEY

Query OK, 0 rows affected (0.49 sec)

Records: 0 Duplicates: 0 Warnings: 0

After clearing (no primary key):

Mysql > DESC biao01

+-+ +

+-+ +

| | id | int (4) | NO | | NULL |

+-+ +

2 rows in set (0.00 sec)

When you try to delete the primary key of the tea6 table, an exception occurs:

Mysql > ALTER TABLE tea6 DROP PRIMARY KEY

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

This is because the primary key field id of the tea6 table has the AUTO_INCREMNET self-increment attribute, indicating that this field must exist as a primary key, so to clear this primary key, you must first clear the self-increment attribute-modify the field definition of the id column:

Mysql > ALTER TABLE tea6 MODIFY id int (4) NOT NULL

Query OK, 0 rows affected (0.75 sec)

Records: 0 Duplicates: 0 Warnings: 0

Then clear the primary key attribute and OK:

Mysql > ALTER TABLE tea6 DROP PRIMARY KEY; / / clear primary key

Query OK, 0 rows affected (0.39 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc tea6; / / confirm the cleanup result

+-+ +

+-+ +

| | id | int (4) | NO | | NULL |

| | age | int (2) | NO | | NULL |

+-+ +

3 rows in set (0.01sec)

9) add a PRIMARY KEY primary key index to an existing table

Re-specify the primary key field for the tea6 table, still using the id column:

Mysql > ALTER TABLE tea6 ADD PRIMARY KEY (id); / / set primary key field

Query OK, 0 rows affected (0.35 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > DESC tea6; / / confirm the setting result

+-+ +

+-+ +

| | id | int (4) | NO | PRI | NULL |

| | age | int (2) | NO | | NULL |

+-+ +

3 rows in set (0.00 sec)

Step 2: create a database and set a foreign key to achieve synchronous update and deletion

According to the requirements of the experimental task, the field structure of the two tables is shown in Table-1 and Table-2.

1) create a yg table to record the employee's job number and name

Where the yg_id column is the primary key and the self-incrementing property is set

Mysql > CREATE TABLE yg (

-> yg_id int (4) AUTO_INCREMENT

-> name char (16) NOT NULL

-> PRIMARY KEY (yg_id)

->)

Query OK, 0 rows affected (0.15 sec)

2) create a gz table to record the salary information of employees

Gz_id needs to refer to the employee's job number, that is, the gz_id field of the gz table is set to the foreign key, and the yg_id field of the yg table is used as the reference key:

Mysql > CREATE TABLE gz (

-> gz_id int (4) NOT NULL

-> name char (16) NOT NULL

-> gz float (7, 2) NOT NULL DEFAULT 0

-> INDEX (name)

-> FOREIGN KEY (gz_id) REFERENCES yg (yg_id)

-> ON UPDATE CASCADE ON DELETE CASCADE

->)

Query OK, 0 rows affected (0.23 sec)

3) add 2 employee information records to the yg table

Because yg_id has an AUTO_INCREMENT attribute, it is automatically populated, so you just need to assign a value to the name column.

Inserting table records can use the INSERT directive, which starts with the following actions, as you will learn in the next chapter:

Mysql > INSERT INTO yg (name) VALUES ('Jerry'), (' Tom')

Query OK, 2 rows affected (0.16 sec)

Records: 2 Duplicates: 0 Warnings: 0

Confirm the data record of the yg table:

Mysql > SELECT FROM yg

+-+ +

| | yg_id | name |

+-+ +

| | 1 | Jerry |

| | 2 | Tom |

+-+ +

2 rows in set (0.00 sec)

4) add 2 salary information records to the gz table

As above, refer to figure-2 for data and insert the corresponding salary record (the default value is not specified in the gz_id field, and the self-increment attribute is not set, so you need to assign the value manually):

Mysql > INSERT INTO gz (gz_id,name,gz)

-> VALUES (1), (2)), (2)

Query OK, 2 rows affected (0.06 sec)

Records: 2 Duplicates: 0 Warnings: 0

Confirm the data record of the gz table:

Mysql > SELECT FROM gz

+-+

+-+

| | 1 | Jerry | 12000.00 | |

| | 2 | Tom | 8000.00 | |

+-+

2 rows in set (0.05sec)

5) verify the UPDATE update linkage of table records

Change the yg_id of the Jerry user in the yg table to 1234:

Mysql > update yg SET yg_id=1234 WHERE name='Jerry'

Query OK, 1 row affected (0.05sec)

Rows matched: 1 Changed: 1 Warnings: 0

Confirm the result of the modification:

Mysql > SELECT FROM yg

+-+ +

| | yg_id | name |

+-+ +

| | 2 | Tom |

| | 1234 | Jerry |

+-+ +

2 rows in set (0.00 sec)

At the same time, you will also find that the gz_id of Jerry users in the gz table has changed:

Mysql > SELECT FROM gz

+-+

+-+

| | 1234 | Jerry | 12000.00 | |

| | 2 | Tom | 8000.00 | |

+-+

2 rows in set (0.00 sec)

6) verify the DELETE deletion linkage of the table record

Delete the record of user Jerry in the yg table:

Mysql > DELETE FROM yg WHERE name='Jerry'

Query OK, 1 row affected (0.05sec)

Confirm the deletion result:

Mysql > SELECT FROM yg

+-+ +

| | yg_id | name |

+-+ +

| | 2 | Tom |

+-+ +

1 row in set (0.00 sec)

Check the changes in the gz table (Jerry's record is also gone):

Mysql > SELECT FROM gz

+-+

+-+

| | 2 | Tom | 8000.00 | |

+-+

1 row in set (0.00 sec)

7) Delete the foreign key constraint of the specified table

First obtain the foreign key constraint name of the table through the SHOW instruction:

Mysql > SHOW CREATE TABLE gz\ G

1. Row

Table: gz

Create Table: CREATE TABLE gz (

Gz_id int (4) NOT NULL

Name char (16) NOT NULL

Gz float (7 dint 2) NOT NULL DEFAULT '0.00'

KEY name (name)

KEY gz_id (gz_id)

CONSTRAINT gz_ibfk_1 FOREIGN KEY (gz_id) REFERENCES yg (yg_id) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Where gz_ibfk_1 is the name to be used when deleting foreign key constraints.

Delete operation:

Mysql > ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Confirm the deletion result:

Mysql > SHOW CREATE TABLE gz\ G

1. Row

Table: gz

Create Table: CREATE TABLE gz (

Gz_id int (4) NOT NULL

Name char (16) NOT NULL

Gz float (7 dint 2) NOT NULL DEFAULT '0.00'

KEY name (name)

KEY gz_id (gz_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

# # #

MySQL storage engine

Mysql service architecture: (8 functional modules)

Connection pool

Sql interface

Analyzer

Optimizer

Query cache

Storage engine

File system

View the storage engines supported by the database service

Mysql > show engines

Set the default storage engine

Vim / etc/my.cnf

[mysqld]

......

Default-storage-engine=InnoDB

The commonly used storage engines are InnoDB and MyISAM

Create table table name (constraint) ENGINE= storage engine name

Characteristics of commonly used storage engines

MyISAM

Table. MYI index information

Table. MYD data

Table. Frm table structure

Support for table-level locks (locking a table)

Transaction rollback is not supported

InnoDB storage engine

Table. Idb index information + data

Table. Frm table structure

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

Support for transaction rollback

MySQL locking mechanism

Lock granularity: table-level lock, row-level lock, page-level lock

Lock types: read (select) and write (insert delete update)

Transaction: the process of accessing data from start to finish

Transaction rollback: if any step fails during an access, all operations will be resumed

Characteristics of transactions: consistency, atomicity, isolation

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

How to determine the storage engine used by the table at work

Tables with many writes are suitable for using the innodb storage engine

Tables with many read operations are suitable for using 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.