2. Operation database

Operation database > operation database table > operation database table data

2.1 operation database (understand)

1. Create database

create datebase  name;

2. Delete database

drop datebase  name;

3. Use database

-- tab Above the key,If your table name or field name is a special character, you need to bring it``
usE `schoo1`

4. View database

show datebases   -- View all databases

2.2 data type of database

• numerical value

1. tinyint very small data 1bite
2. smallint smaller data 2bite
3. mediumint medium size data 3bite
4. int standard integer 4bite
5. Big big data 8biye
6. float single precision floating point 4bite
7. Double double precision floating point number 8bite
8. decimal string form

• character string

1. char string fixed size 0 ~ 255
2. varchar variable string 0~65535 common string
3. Tiny text 2 ^ 8 ~ 1
4. Texttext string 2 ^ 16 ~ 1 save large text

• Time date

1. Date date XXXX XX XX
2. time HH:mm:ss
3. datetime time format: YYYY-MM-DD HH:mm:ss
4. Timestamp timestamp, milliseconds from 1970.1.1 to now! It is also commonly used
5. Year means year

• null

1. If there is no value, do not use NULL for operation. The result is NULL

2.3 field properties of database

unsigned:

• Unsigned integer
• The column cannot be declared negative

zerofill

• 0 filled

• Insufficient digits, fill int(3) with 0, 5005

Self increasing:

• It is usually understood as auto increment, which is automatically + 1 on the basis of the previous record
• It is usually used to design a unique primary key ~ index, which must be of integer type
• You can customize the starting value and step size of self increment of design primary key

Non NULL not null

• If the design is not null, an error will be reported if it is not assigned a value
• Null. If the value is not filled in, it is null by default

Default:

• The default setting is
• sex. The default value is male. If the value of this column is not specified, there will be a default value

Each table must have the following five fields

1. id primary key

2. 'version' optimistic lock

3. is_delete pseudo delete

4. gmt_create creation time

5. gmt_update modification time

2.4 creating database tables

--  Goal: create a school database
--  Create student tables (columns, fields), using sql establish
--  Student number int ，  Login password verchar(20)  ， Gender verchar(2) ,  date of birth datetime  ，Home address, email

-- Note: use English()，Table names and fields should be used whenever possible·Enclose
-- AUTO INCREMENT Self increasing
-- Strings are enclosed in single quotes!
-- All statements are followed by,(English)
-- primary key Primary key. Generally, a table has only one primary key

CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
`name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
`pwd` VARCHAR(20)NOT NULL DEFAULT '123456' COMMENT 'password',
`sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT'Gender',
`birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
`address` VARCHAR (100) DEFAULT NULL COMMENT 'Home address',
`email`  VARCHAR (50) DEFAULT NULL COMMENT 'mailbox',
PRIMARY KEY (`id`)
)ENGINE=NNODB DEFAULT CHARSET=utf8

format

create table[if not exists] `Table name`(
	`Field name` Column type [attribute] [Indexes] [notes]，
    `Field name` Column type [attribute] [Indexes] [notes]，
    `Field name` Column type [attribute] [Indexes] [notes]，
)

Common commands

SHOW CREATE DATABASE school  -- View the statement that created the database

SHOW CREATE TABLE student  -- see student Definition statement of data table

DESC student  -- Displays the structure of the table

2.5 type of data sheet

-- About database engine
/*
INNODB Default use
MYISAM Used in previous years
*/

	MYISAM	INNODB
Transaction support	I won't support it	support
Data row locking	I won't support it	support
Foreign key constraint	I won't support it	support
Full text index	support	I won't support it
Tablespace size	less	Larger, about times

General operation:

• MYISAM saves space and is fast
• INNODB has high security, transaction processing, multi table and multi-user operation

Differences of MySQL engine in physical files

lnnoDB has only one *. In the database table frm file and ibdata1 file in the parent directory

MYISAM corresponding files:

• . MYD data file (data).
• . MYI index file (index)
• Definition file of. frm table structure

Sets the character set encoding of the database table

CHARSET=utf8

If it is not set, it will be the default character set encoding of mysql (Chinese is not supported)

2.6 modifying and deleting tables

Modify table name ALTER TABLE old name RENAME AS new name

ALTER TABLE teacher RENAME AS teacher1

ADD the field of the table ALTER TABLE table table name ADD field column property

ALTER TABLE teacher1 ADD age INT(10)

Modify the fields of the table (rename, modify constraints)

-- ALTER TABLE Table name MODIFY Field column attribute []
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- Modify constraints

--ALTER TABLE Table name CHANGE Old name new name column attribute []
ALTER TABLE teacher1 CHANGE age age1 INT(11) -- Field rename

Delete table fields

--ALTER  TABLE Table name DROP Field name
ALTER  TABLE teacher1 DROP age

Delete table

-- DROP TABLE IF EXISTS Table name
DROP TABLE IF EXISTS teacher1