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

This article mainly introduces the basic sentences of the introduction to mysql, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the basic sentences of introduction to mysql.

Basic sentences for getting started with MYSQL

Login link MYSQL

Mysql-u (username)-p (password)-h (hostname)

DDL statement database defines language databases, tables, views, indexes, stored procedures, such as CREATE DROP ALTER

Create database database name; # create a database, uppercase is also possible

Show databases; # View the database

Select database (); # shows the current library

Use database name; # which database to use

Drop databas database name; # Delete database

Help drop View Command Gang

Numeric type:

Integer type TINYINT SMALLTNT MEDIUMINT INT BIGINT

Floating point type FLOAT DOUBLE

Fixed point type DEC

Bit type BIT

String type:

CHAR series CHAR VARCHAR

TEXT series TINYTEXT TEXT MEDIUMTEXT LONGTEXT

BLOB series TINYBLOB BLOB MEDIUMBLOB LONGBLOB

BINARY series BINARY VARBINARY

Enumeration type: ENUM

Collection type SET

Time and date type: DATETIME DATETIME TIMESTAMP YEA

PRIMARY KEY (contention) # Primary key

FOREIGN KEY (FK) # Foreign key

NOT NULL # indicates that the field cannot be empty

UNIQUE KEY (UK) # indicates that the field is unique, can be empty, and can have multiple UNIQUE KEY in a table

AUTO_INCREMENT # identifies that the value of this field grows automatically (integer type and primary key)

DEFAULT # sets the default value for this field

UNSIGNED # unsigned, positive number

ZEROFILL # is populated with 0, for example, 00000001

Desc table,show create table; # View

Create table table name (

Field name 1 Type ((width) constraint)

Field name 2 Type ((width) constraint)

Field name 3 Type ((width) constraint)

); # create a

Default 'value' # default value

Not null # is not empty

Enum ('masking dwelling f') # enumerated types

Age int unsigned # unsigned integers

Set ('disc','boos','music') # sets the value of a collection

Primary key auto_increment; # primary key is self-incrementing

Primary key (ip,service); # defines a compound primary key

Primary key (name); # define an external

The name of the insert into table is values (3); insert numeric values, which can be multiple

Add field

ALTER TABLE table name # add field

ADD field name data type [integrity constraint.]

ALTER TABLE table name

ADD field name data type [integrity constraint.] FIRST

ALTER TABLE table name

ADD field name data type [integrity constraint.] AFTER field name

Delete a field

ALTER TABLE table name DROP field name

Modify field

ALTER TABLE table name

MODIFY field name data type [integrity constraint.]

ALTER TABLE table name

CHANGE old field name new field name new data type [integrity constraint.]

ALTER TABLE table name

CHANGE old field name new field name new data type [integrity constraint.]

Delete primary key

Delete the self-increasing attribute first, and then delete the primary key attribute, otherwise an error will be reported.

MySQL [school] > alter table T1 modify id int not null; delete the self-increment attribute first

MySQL [school] > alter table T1 drop primary key; is deleting the primary key attribute

Alter table T1 drop name

Copy tabl

Copy structure + record (key does not copy: primary key, foreign key and index)

Create table t1_new select * from T1; create a new table to copy the structure and data of the old table

Create table t1_new3 select * from T1 where 1x5; replicate table structure without data

MySQL [school] > create table T4 like T1; # copy table structure, including ke

Delete table DROP TABLE table name; # delete table name

Drop table table name

# find out the complete data #

Insert into table name values (value 1, value 2, value 3.

# specify fields to insert data #

Insert into table name (field 2, field 3...) Values (value 2, value 3...

# insert multiple records #

Insert int table name values

(value 1, value 2, value 3.

(value 1, value 2, value 3.

(value 1, value 2, value 3.

# insert query result #

Insert into Table 1 (Field 1, Field 2.)

Select (Field 1, Field 2) from Table 2

Where...

# updating data UPDATE###

Update table name set

Field 1 = value 1

Field 2 = value 2

Where...

# deleting data DELETE###

Delete from table name

Where.; # be sure to add conditions or delete them all

The place of the MYSQL transaction

Begin

Start transaction; # start things manually

Insert into t_user (name) values ('pp')

Commit; after # commit, you can change the underlying database data

Roollback # rollback

Commit; # submission

1. There is a certain isolation between thing An and thing B.

two。 Isolation has isolation level (4)

Read not submitted: read uncommitted

Read submitted: read committed

Repeatable: repeatable read

Serialization: serializabl

Default auto-commit transaction

Set autocommit=0 forbids automatic submission

Set autocommit=1 enables auto-submission

# set foreign key constraint FOREIGN key

Parent table company.demofu

Mysql > create employees (

-> name varchar (50) not null

-> mail varchar (20)

-> primary key (name))

Child table company.payroll

Mysql > create table demozi (

Id int auto_increment

Name varchar (50) not null

Payroll float (8 dint 2) not null

Primary key (id)

Foreign key (name) references demofu (name) on update

Cascade on delete cascade); # set the foreign key to synchronize the name word in the schedule

Insert numeric value

Mysql > insert into demofu values

-> ('Yuanyuan', 'yuanyuan@163.com')

-> ('Big', 'dada@126.com')

Mysql > insert into demozi (name,payroll) values (Zhao Zhao, 1000)

Error 1452 (23000): unable to add or update child lines: foreign key constraint failed ("student", "demozi", constraint "demozi_ibfk_1"

The foreign key ("name") references "demofu" ("name") on the update cascading DELETE CASCADE

Explanation:

After the foreign key is set, the subtable will synchronize the field data in the appearance. If the data is not available in the schedule, then

Unable to add

Mysql > update demofu set name=' Yaqing 'where name=' Circle'

Mysql > select * from demozi

+-- +

| | id | name | payroll | # come to the conclusion that the foreign key of the child table and the fields in the schedule |

+ be consistent

| | 1 | Yaqing | 8000.00 | |

+-+-

Mysql > delete from demofu where name=' Yaqing'; # parent table data deletion child tables will also be deleted

Mysql > select * from demozi

Empty set (0.00 sec)

After reading the above basic sentences about the introduction to mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.