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

I. create and view the database

1. Create a database

CREATE DATABASE [IF NOT EXISTS] # optional content db_name [create_specification] # is defined as follows:

[DEFAULT] CHARACTER SET charset_name# character set | [DEFAULT] COLLATE collation_name# proofreading rules

(1) create a database named mydb1

Create database mydb1

(2) create a database of mydb3 using gbk character set

Create database mydb2 character set gbk

(3) create a mydb3 database using utf8 character set and proofreading rules

Create database mydb3 character set utf8 collate utf8_bin

two。 View the database

(1) display database statements:

Show databases

(2) display the database creation statement:

Show create database mydb3

3. Modify the database

ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [, alter_specification [,. # specifications are as follows

[DEFAULT] CHARECTER SET charset_name [DEFAULT] COLLATE collation_name

(1) View the database in the server and change the character set of one of the libraries to utf8

Alter database mydb2 character set utf8

4. Modify the database

DROP DATABASE [IF EXISTS] db_name

(1) Delete database mydb3

Drop database mydb3

5. Select a database

Use database

Select database (); # View the database

II. Operation of tables in the database

1. Learn the data types of the database

two。 Add table

CREATE TABLE table_name (

Field1 datatype

Field2 datatype

Field3 datatype

Field4 datatype

) character set character set collate proofreading rules # recommended default

Field: specify column name datatype: specify column type

(1) create an employee table employee

Create table employee (

Id int

Name varchar (20)

Gender char (1)

Birthday date

Entry_data date

Job varchar (50)

Salay double

Resume text

);

3. View tabl

View table structure: desc tab_name

Query all tables in the current database: show tables

View the table-building statement of the table: show create table

4. Modify the table

ALTER TABLE table_name ADD (column datatype [DEFAUL expr] [, column datatype].)

ALTER TABLE table_name MODIFY (column datatype [DEFAUL expr] [, column datatype].)

ALTER TABLE table_name DROP (column)

Modify the name of the column: ALTER TABLE table_name change [column]

Old_col_name column_definition

Modify the name of the table: rename table table name to new table name

Modify the character set encoding of the table: alter table tab_name character set utf8

(1) basically add an image column to the above employee table

Alter table employee add image blob

(2) modify the job column so that its length is 60

Alter table employee modify job varchar (60)

(3) Delete gender column

Alter table employee drop gender

(4) change the table name to user

Alter table employee to user

(5) modify the character set of the table to utf8

Alter table user character set utf8

(6) change the column name name to username

Alter table user change name username varchar (20)

5. Delete tabl

Drop table table_name

III. Constraints of the table

1.PRIMARY KEY primary key constraint: non-null & unique

Create table employee (

Id int primary key

Name varchar (20)

Gender char (1)

Birthday date

Entry_data date

Job varchar (50)

Salay double

Resume text

);

2.NOT NULL non-null constraint: cannot be empty

Create table employee (

Id int primary key

Name varchar (20)

Gender char (1) not null

Birthday date

Entry_data date

Job varchar (50)

Salay double

Resume text

);

3.UNIQUE unique constraint: unique

Create table employee (

Id int primary key

Name varchar (20) unique

Gender char (1) not null

Birthday date

Entry_data date

Job varchar (50)

Salay double

Resume text

);

4.DEFAULT default constraint:

Create table employee (

Id int primary key

Name varchar (20) unique

Gender char (1) not null

Birthday date

Entry_data date

Job varchar (50) DEFAULT 'sxs'

Salay double

Resume text

);

The fields of the 5.AUTO_INCREMENT settings table grow automatically

Create table employee (

Id int primary key auto_increment

Name varchar (20) unique

Gender char (1) not null

Birthday date

Entry_data date

Job varchar (50) DEFAULT 'Intern'

Salay double

Resume text

);

IV. Index

1. General index

Can be created in any data type, and there is no fixed requirement whether its value is unique and non-null

two。 Uniqueness index

Index defined by UNIQUE

3. Full-text index

Is an index defined by FULLTEXT, which can only be created on fields of type CHAR,VARCHAR or TEXT, and now only the MyISAM storage engine supports full-text indexing

4. Single column index

The index of a single field in a table

5. Multi-column index

An index created on multiple fields in a table that is used only if the first of these fields is used in the query condition

6. Spatial index

Is an index defined by SPATIAL and can only be created on fields of spatial data types (GEOMETRY,POINT,LINESTRING and POLYGON)

7. Create an index

(1) create an index when creating a table

CREATE TABLE table name (field name data type [full constraint], field name data type [full constraint]

.

Field name data type

[UNIQUE # unique index | FULLTEXT# full-text index | SPATIAL# spatial index] INDEX | KEY# field index [alias] # index name (field name table field [(length)] # index length) [ASC# ascending order | DESC# descending order])

);

Create a normal index

Create table T1 (

Id INT

Name VARCHAR (20)

Score FLOAT

INDEX (id)

);

Find

Explain select * from T1 where id = 1

Create a uniqueness index

Create table T2 (

Id INT

Name VARCHAR (20)

Score FLOAT

UNIQUE INDEX unique_id (id ASC)

);

View: show create table T2

Create a full-text index

Create table T3 (

Id INT

Name VARCHAR (20)

Score FLOAT

FULLTEXT INDEX fulltext_name (name)

) ENGINE=MyISAM

Create a single-column index

Create table T4 (

Id INT

Name VARCHAR (20)

Score FLOAT

INDEX single_name (name)

);

Create a multi-column index

Create table T5 (

Id INT

Name VARCHAR (20)

Score FLOAT

INDEX mutil_name (id,name (20))

);

Create a spatial index

Create table T6 (

Id INT

Space GEOMETRY NOT NULL

SPATIAL INDEX space_name (space)

) ENGINE=MyISAM

8. Use the CREATE INDEX statement to create an index on the created table

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index name ON table name (field name [(length)] [ASC | DESC])

Create table book (

Bookid INT NOT NULL

Bookname VARCHAR (225)

Authors VARCHAR (225)

Info VARCHAR (225)

Comment VARCHAR (225)

Publicyear YEAR NOT NULL

);

(1) create a normal index on the bookid column

CREATE INDEX index_id on book (bookid)

(2) create a unique index on the bookid column

CREATE UNIQUE INDEX uniquex on book (bookid)

(3) create a multi-column index

CREATE INDEX mutilx on book (authors,info)

(4) create a full-text index

Drop table book

Create table book (

Bookid INT NOT NULL

Bookname VARCHAR (225)

Authors VARCHAR (225)

Info VARCHAR (225)

Comment VARCHAR (225)

Publicyear YEAR NOT NULL

) ENGINE=MyISAM

Create FULLTEXT INDEX fulltextdx on book (bookname)

9. Use the ALTER TABLE statement to create an index on an existing table

ALTER TABLE table name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index name (field name [(length)] [ASE | DESC])

ALTER TABLE book ADD UNIQUE INDEX year (publicyear)

10. Delete index

(1) ALTER TABLE table name DROP INDEX index name

ALTER TABLE book drop index year

(2) DROP INDEX index name ON table name

DROP INDEX fulltextdx on book

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.