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

1. Log in to mysql: C:\ Users\ Administrator > mysql-h localhost-u root-p under cmd and press enter.

Enter Password appears: enter the mysql if you enter the data password

2. View all existing databases: mysql > SHOW DATABASES

3. Create a new database: mysql > CREATE DATABASE test_db1

4. Delete the database: mysql > DROP DATABASE test_db1

5. Before creating a data table, you must first select the database under which you create it. Select the database command: mysql > USE test_db1

6. Create a new data table:

Mysql > CREATE TABLE tb_emp

-> (

> id INT (11)

Name VARCHAR (25)

> dptid INT (11)

-> salary FLOAT

->)

Elements to create a new data table: (1) there must be the name of the data table (2) there must be at least one column in the data table, containing the column name and data type, separated by commas when creating multiple columns.

7. Check whether the data table has been established successfully:

Mysql > SHOW TABLES

+-+

| | Tables_in_test_db1 |

+-+

| | tb_emp |

+-+

8. Primary key constraint: (1) Primary key constraint requires that the data of the primary key column is unique and not empty. (2) there are two types: single field and multiple fields.

Mysql > CREATE TABLE tb_emp2

-> (

-> id INT (11) PRIMARY KEY, define the primary key when the column is defined.

-> PRIMARY KEY (id)\\ define the primary key after all the columns are defined. You can choose one of the two methods.

->)

9. Foreign key constraints:

Mysql > CREATE TABLE tb_emp2

-> (

-> id INT (11) PRIMARY KEY

Name VARCHAR (25)

> deptid INT (11)

-> salary FLOAT

-> CONSTRAINT fk_emp2_dept FOREIGN KEY (deptid) REFERENCES dept1 (id)

->)

Query OK, 0 rows affected (0.05 sec)

Attention points for foreign key constraints:

(1) the storage engine of parent table and child table must be the same, and different creation fails. ENGINE=InnoDB

(2) the foreign key of the child table must be associated with the primary key of the parent table. This error ERROR 1215 (HY000): Cannot add foreign key constraint occurs if the primary key of the parent table is not associated.

(3) the foreign key of the child table and the primary key data type of the parent table must be the same, otherwise the creation fails.

10. Non-empty constraints:

Mysql > CREATE TABLE tb_emp3

-> (

-> id INT (11) PRIMARY KEY (primary key constraint) NOT NULL (non-null constraint) UNIQUE (uniqueness constraint),\\ non-null constraint means that its insert value cannot be empty, and different constraints are directly separated by spaces without other symbols.

11. Uniqueness constraint:

Mysql > CREATE TABLE tb_emp3

-> (

-> id INT (11) PRIMARY KEY NOT NULL

-> name VARCHAR (25) UNIQUE

-> deptid INT (11), define the primary key when defining the column.

-> salary FLOAT

-> UNIQUE (name,deptid)\\ define the primary key after all the columns are defined. You can choose one of the two methods.

->)

The difference between a unique constraint and a primary key constraint:

(1) multiple fields can be declared as UNIQUE in a table, but there can be only one PRIMARY KEY declaration.

(2) the field of PRIMARY KEY cannot be empty, but the UNIQUE field can be empty.

12. Default constraint:

Mysql > CREATE TABLE tb_emp3

-> (

-> id INT (11) PRIMARY KEY NOT NULL

Name VARCHAR (25)

-> deptid INT (11) DEFAULT 111,\\ if the newly inserted record does not specify a number, it defaults to 111.

-> salary FLOAT

->)

13. The attribute value is automatically increased:

Mysql > CREATE TABLE tb_emp3

-> (

-> id INT (11) PRIMARY KEY NOT NULL AUTO_INCREMENT

Name VARCHAR (25)

> deptid INT (11)

-> salary FLOAT

->)

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.