MYSQL (a) Simple syntax

MYSQL (a) Simple syntax

1, the default constraint: MySQL inside the default keyword is not parentheses after

--1.1 Creating a Database

mysql> CREATE DATABASE Holly;

Query OK, 1 row Affected (0.00 sec)

--1.2 using the database

Mysql> use Holly;

Database changed

--1.3 Creating a database table

Mysql> CREATE TABLE Student

(

-ID int Default 50

);

Query OK, 0 rows affected (0.10 sec)

--1.4 Inserting data

mysql> INSERT into student (ID) values (default);

Query OK, 1 row affected (0.04 sec)

--1.5 Querying data

Mysql> select * from student;

+------+

| ID |

+------+

| 50 |

+------+

1 row in Set (0.00 sec)

2. Set the self-increment column

MySQL's self-increment column must be an indexed column, set the seed value to be set after the table

--Set the self-increment sequence and start with the value starting from n

----(set self-increment sequence starting from 100)

Mysql> CREATE TABLE Teacher (

-ID INT PRIMARY KEY auto_increment

) auto_increment = 100;

Query OK, 0 rows affected (0.12 sec)

mysql> Insert Teacher (ID) values (NULL);

Query OK, 1 row affected (0.05 sec)

Mysql> select * from teacher;

+-----+

| ID |

+-----+

| 100 |

+-----+

1 row in Set (0.00 sec)

MySQL Gets the current table's self-increment method

SELECT @ @identity for any table

@ @identity is a system-defined global variable that represents the last time the value of the self-increment column for inserting data into a table with the identity attribute (that is, the self-increment column).

General system-defined global variables start with @@ 开头 and user-defined variables begin with @.

The @ @identity is used only if the connection is not closed when the insert operation is executed, or a null value is obtained.

3.MYSQL View Table Structure

mysql> desc Student;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       50 | |

+-------+---------+------+-----+---------+-------+

1 row in Set (0.01 sec)

4. Modify the table name

mysql> ALTER TABLE student rename Stu;

Query OK, 0 rows affected (0.05 sec)

Mysql> desc Stu;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       50 | |

+-------+---------+------+-----+---------+-------+

1 row in Set (0.01 sec)

5. Modify the data type of the field

Change the ID field's int type to bigint

ALTER TABLE stu MODIFY ID BIGINT

6. Modify field names

ALTER table name change old column name new column name Gint

7. Add fields

ALTER table name ADD column name data type ( length ) constraint

8. Delete fields

ALTER table name DROP column name

9. Delete FOREIGN KEY constraints

ALTER table name DROP FOREIGN key FOREIGN KEY constraint name

--Delete primary KEY constraint

ALTER table Name drop PRIMARY key PRIMARY KEY constraint name

10. Delete a table

--Delete a table

DROP Table Name

--Delete multiple tables

DROP table IF EXISTS table name 1, table name 2

10. Copying a table

CREATE table new table name like old table name; Duplicate table structure only

CREATE table new table name as SELECT * from old table name; Copy table structure and table data

When MySQL replicates table structure/data, it does not replicate any attributes, such as primary key, index, auto-increment, or simply copy data

MYSQL (a) Simple syntax