Shulou(Shulou.com)05/31 Report--

In this article, we will learn about how to organize mysql in the early stages of learning. There is a certain reference value, friends in need can refer to, hope to help you.

Mysql pre-study arrangement

Advantages and disadvantages of 1.mysql

Advantages: good transaction management ability, crash repair ability.

Disadvantages: the reading and writing rate is relatively poor and takes up a large amount of data space.

two。 Connect to the server

1. Desktop win+r

two。 Enter mysql-u root-p

3. Enter password

3.sql statement classification

DDL

Data Defintion (definition) Language

Database, table creation, modification, deletion

DML:

Data Manipulation (operation) Language

Operation, insert, delete, modify, query of data in table

DCL:

Data Control (control) Language

Access control, who can do what and who can't do what?

DQL:

Data Query (query) Language

Query the data in the database

DDL (database and table operations)

Keywords:

Create: creatin

Drop: deletin

Show: viewin

Alter: modifyin

Database: database

Table: tabl

Use: select / switch databases

Field type:

Integer: int long short

Floating point: float double (5, 2) 5 total length, 5 digits, 2 small places

Character: char (fixed length) varchar (variable length)

Text: text (large pieces of text)

View all databases

Show databases

View all tables

Show tables

View table structure

Desc table name

Add a field

Alert table table name add new field name field type (constraint)

Field rename

Alert table table name change original field name new field name field type

Modify field type

Alert table table name modify field name new field type

Delete a field

Alert table table name drop field name

Modify table name

Rename table original table name rename to new table name

Five constraints

1. Primary key constraint: unique, non-repetitive, non-empty

Add method 1: create primary key constraints when creating tables.

Create table table name (

Column name 1 data type primary key

Column name 2 data type

);

Add method 2: existing table, add primary key constraint.

Alter table table name add constraint primary key constraint name add primary key (column name)

Delete primary key constraint

Alter table table name drop primary key

Federated primary key 1: add a federated primary key when creating a table

Create table table name (

Column name 1 data type

Column name 2 data type

Primary key (column 1, column 2)

);

Federated primary key 2: existing table, add federated primary key

Alter table table name add constraint primary key constraint name primary key (column name 1, column name 2)

Unique constraint: unique unique constraint name

Add method 1: create unique constraints when creating a table.

Create table table name (

Column name 1 data type unique

Column name 2 data type

);

Add method 2: existing table, add unique constraint.

Alter table table name add unique (column name)

Delete unique constraint

Alter table table name the name of the drop index unique constraint

Default constraint default: when no data is written, it is also given a value by default.

Add method 1: create default constraints when creating a table.

Create table table name (

Column name 1 data type default 'string or date type'

Column name 2 data type default numeric type

Column name 3 data type

);

Add method 2: existing table, add default constraint.

Alter table table name modify column name data type default 'value'

Delete default constraint

Alter table table name modify column name data type

Non-null constraint not null: must have a value

Add method 1: create a non-empty constraint when creating a table.

Create table table name (

Column name 1 data type not null

Column name 2 data type

);

Add method 2: existing table, add non-empty constraint.

Alter table table name modify column name data type not null

Delete non-empty constraint

Alter table table name modify column name data type

Foreign key constraint foreign key foreign key name for table

Add method 1: create foreign key constraints when creating a table.

Build the master table first

Create table main table name (

Column name 1 data type

Column name 2 data type

);

Re-create the slave table-- the foreign key is created in this table

Create table from the table name (

Column name 1 data type

Column name 2 data type

Constraint foreign key name foreign key (slave table column name) references master table name (master table column name)

);

Build the master table first

Create table main table name (

Column name 1 data type

Column name 2 data type

);

Re-create the slave table-- the foreign key is created in this table

Create table from the table name (

Column name 1 data type

Column name 2 data type

Constraint foreign key name foreign key (slave table column name) references master table name (master table column name)

);

Add method 2: existing table, add foreign key constraint.

Alter table slave table name add constraint foreign key name foreign key (slave table column name) references master table name (master table column name)

Delete foreign key constraint

Alter table from table name drop foreign key foreign key name

DML operation (operation of data in the table: add, delete, modify and query)

Insert data: insert into

Modify data: update

Delete data: delete

Query data: select

Insert data: insert into

Syntax:

Write method 1: insert into table name (field name 1, field name 2. ) value (data (quantity is the same as the previous parenthesis, note the order))

Method 2: insert into table name set field name = field value, field name = field value. .

Note:

1. The field name is exactly the same as the field name in the table

two。 The data is consistent by field type.

3. If you insert an empty slot null, the numeric type and the type are written directly, and the rest are enclosed in single quotation marks

Modify data: update

Syntax:

Update table name set field to be changed = value changed where condition

Note:

In general, the whole table field is modified.

Add a condition to amend a clause

Delete data: DELETE

Syntax:

Delete from table name where condition

Delete the whole table without where condition

The above is a brief introduction of how to organize mysql in the early stage of learning, of course, the detailed use of the above differences have to be used by everyone to understand. If you want to know more, welcome to follow the industry information channel!

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.