Introduction to mysql early learning and organization

mysql early learning and sorting

1. The advantages and disadvantages of mysql

Advantages: Good transaction management capabilities and crash repair capabilities.
Disadvantages: The read and write speed is relatively poor and takes up a large amount of data space.

2. Connect to the server

1. Desktop win r
2. Enter mysql -u root -p
3. Enter the password

3.sql statement classification

DDL
Data Definition (Definition) Language
Database, table creation, modification, deletion
DML:
Data Manipulation (Operation) Language
Operation of data in the table, insert, delete, modify, query
DCL:
Data Control (Control) Language
Permission control, who can do what and who cannot do what
DQL:
Data Query (query) Language
Query the data in the database

Related free learning recommendations: mysql video tutorial

DDL (database and table operations)

Keywords:
create: create
drop: delete
show: view
alter: modify
database: database
table: table
use: select/switch database

Field type:
integer : int long short
Floating point: float double (5,2) 5 total length 5 digits 2 decimal places
Characters: char (fixed length) varchar (variable length)
Text: text (large Paragraph text)

View all databases
show databases;
View all tables
show tables;
View Table structure
desc table name

Add field
alert table table name add new field name field type (constraints);
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 field
alert table table name drop field name;
Modify table name
rename table original table name rename to new table name;

Five major constraints

1. Primary key constraints: unique, non-duplicate, non-empty

Add method one: when creating the table Create primary key constraints.
create table table name (
column name 1 data type primary key,
column name 2 data type
);

Add method two: already exists table, add primary key constraints.
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;

Joint primary key one: Add a joint primary key when creating a table
create table table name (
column name 1 data type,
column name 2 data type,
primary key (column name 1, column name 2)
);

Joint primary key two: The table already exists, add a joint 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 when creating the table constraint.
create table table name (
Column name 1 data type unique,
Column name 2 data type
);

Add method 2: Existing table , add a unique constraint.
alter table table name add unique (column name);

Delete unique constraint
alter table table name drop index name of unique constraint;

Default constraint default: When no data is written, a value will be given by default.

Add method 1: Create default constraints when creating the 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: The table already exists, add default constraints.
alter table table name modify column name data type default 'value' ;

Delete default constraints
alter table table name modify column name data type;

Non-null constraint is not null: must have a value

Add method 1: Create a non-null constraint when creating the table.
create table table name (
column name 1 data type not null,
column name 2 data type
);

Add method two: already exists table, add a non-empty constraint.
alter table table name modify column name data type not null;

Delete non-null constraints
alter table table name modify column name data type;

Foreign key constraint foreign key The foreign key name is for the secondary table

Add method 1: Create a foreign key constraint when creating the table.
Build the main table first

create table main table name (
Column name 1 data type,
Column name 2 data type
);
Create a secondary table--foreign keys are established 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 (from the table column name) references main table name (main table column name);
);
Build the main table first
create table main table name (
Column name 1 data type,
Column name 2 data type
);
Then create the slave table--external The 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 (from the table column name) references the main table Name (main table column name);
);

Add method 2: The table already exists, add foreign key constraints.
alter table add constraint from table name foreign key name (slave table column name) references main table table name (main table column name);

Delete foreign key constraint
alter table drop foreign key foreign key name from the table name;

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

Insert data ：insert into
Modify data: update
Delete data: delete
Query data: select

Insert data: insert into

## Syntax:

Writing method 1: insert into table name (field name 1, field name 2...) value (data (the number is the same as the previous bracket, pay attention to the order));

Writing method two: insert into table name set field name = field value, field name = field value….

Notes: 1. Field name and table The field names are exactly the same
2. The data is consistent according to the field type
3. If you insert null into the empty slot, the numeric type and type are written directly, and the others are enclosed in single quotes

Modify Data: update

Syntax:

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

Note Matters:

Generally, all fields in the entire form are modified

Add a condition to modify one item

Delete data: DELETE

Grammar: delete from table name where condition
Without adding where condition, delete the entire table directly

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of Introduction to mysql early learning and organization. For more information, please follow other related articles on the PHP Chinese website!