MySQL Learning-operating database (add and revise) (i)

Database is a long-term storage in the computer, organized and shareable data collection, in short, the database is a place to store data, only in its storage mode has a specific law, so that can facilitate the processing of data, database operations including the creation of databases and delete databases, as well as the database of queries and modifications, These operations are the foundation of database management.

One, create a database
To create a database is to divide a space in the database system, which is used to store the corresponding data, which is the basis of the operation of the table, and also the basis of database management, the database created in MySQL needs to be implemented by the SQL statement create databases, the syntax is as follows:

Create database name; where the ' database name ' parameter represents the name of the database to be created, you can use the Show statement to view the existing database before creating the database with the following syntax: Show database;mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | liuyandb           | | mysql              | | performance_schema | | python             | | sys                |+--------------------+6 rows in Set (0.00 sec) mysql>

Second, delete the database

Deleting a database means deleting a database that already exists in the database system, and after deleting the database, the original allocated space will be reclaimed, and it is worth noting that deleting the database deletes all the tables and all the data in the database.

The deletion of the database is implemented by the SQL statement DROP databse, whose syntax is formatted as follows/;. DROP database name;

Examples Show

Create a database called MyBook, query and then delete it to view:

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | liuyandb | | MySQL | | Performance_schema | | Python | | SYS |+--------------------+6 rows in Set (0.00 sec) mysql> CREATE database MyBook; Query OK, 1 row affected (0.04 sec) mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | liuyandb | | MyBook | | MySQL | | Performance_schema | | Python | | SYS |+--------------------+7 rows in Set (0.00 sec) mysql> drop database mybook; Query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | liuyandb | | MySQL | | Performance_schema | | Python | | SYS |+--------------------+6 rows in Set (0.01 sec) mysql>

Three: Create, modify, and delete tables

Tables are the basic unit of data stored in a database, and a table contains several fields or records, and the operations of tables include creating tables, modifying tables, and deleting tables, which are the most fundamental and important operations in database management.

3.1 Creating a Table

The table created in MySQL is implemented by the SQL statement CREATE table with the following syntax: Use database name create TABLE table name (property name  data type "full constraint",                                property name  data type "full constraint",                                .                                .                                Where the use database name means that a database is used, the ' table name ' parameter represents the name of the table to be created; the ' attribute name ' parameter represents the name of the field in the table; the ' data type ' parameter specifies the data type of the field, and the ' integrity constraints ' parameter specifies some special constraints   for the field Note: When creating a table using the CREATE TABLE statement, first select the database with the USE statement, select the base format of the database statement as ' use database name ', and if you do not select the database, the ' Error 1046 (3d000) will appear when you create the table: No SELECTED ' 's error. Table names cannot use the keywords of the SQL language, such as create,updata and order cannot do the table name, a table can have one or more properties, the definition of the letter case can be separated by commas, the last attribute does not need to add a comma.

NOTE: An integrity constraint is a restriction on a field that requires the user to operate on that property in accordance with a specific requirement, and if the integrity constraints are not met, the database system will not perform the user's actions to ensure the integrity of the data in the database, and the following table is the integrity constraint:

Example: Create a table named Example0:

mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | liuyandb | | MySQL | | Performance_schema | | Python | | SYS |+--------------------+6 rows in Set (0.00 sec) mysql> Create database text; Query OK, 1 row affected (0.01 sec) mysql> use text;database changedmysql> CREATE TABLE EXAMPLE0 (id int, nam e varchar (+), sex Boolean, etc.; Query OK, 0 rows affected (0.34 sec) mysql> desc example0;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | || name | varchar (20) |     YES | |       NULL | || sex | tinyint (1) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+3 rows in Set (0.01 sec) Mysql> 

Note: Related concepts:

A primary key is a special field of a table that uniquely identifies each piece of information in the table, the primary key and the record's relationship, like the identity card and the person's relationship, the primary key is used to identify each record, the primary key value of each record is different, the identity card is used to table the identity of celebrities, everyone has a unique ID number, Setting the primary key for a table is the primary key for the table when the table is created by setting a field for the table.

1. When the primary key of a single field is made up of one of the fields, you can add primary directly after the field Key to set the primary key, the syntax rules are as follows: Property name data Type PRIMARY key where the ' property name ' parameter represents the name of the field in the table, the ' data type ' parameter specifies the data type of the field.    Example: Set stu_id as the primary key in the example1 table, SQL code as follows:mysql> CREATE table example1 (stu_id int primary KEY, Stu_name varchar (20), , Stu_sex, Boolean); Query OK, 0 rows affected (0.33 sec) mysql> desc example1;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| stu_id | Int (11) | NO | PRI |       NULL | || Stu_name | varchar (20) |     YES | |       NULL | || Stu_sex | tinyint (1) |     YES | |       NULL | |+----------+-------------+------+-----+---------+-------+3 rows in Set (0.00 sec) Mysql>  

2. When a multi-field primary key    is composed of multiple attributes, the primary key is set uniformly after the attribute is defined, with the following syntax: PRIMARY key (Property 1, Property 2 ...). Attribute N) Example: Set stu_id and course_id fields in the Example2 table as the primary key, and the SQL code as follows:mysql>mysql> CREATE table example2 (stu_id int,    course_id int,    grade float,    primary key (stu_id,course_id)    ; Query OK, 0 rows affected (0.43 sec) mysql> desc example2;+-----------+---------+------+-----+---------+-------+| Field     | Type    | Null | Key | Default | Extra |+-----------+---------+------+-----+---------+-------+| stu_id    | int (11) | NO   | PRI | NULL    |       | | course_id | INT (11) | NO   | PRI | NULL    |       | | | grade     | float   | YES  |     | NULL    |       | +-----------+---------+------+-----+---------+-------+3 rows in Set (0.00 sec) mysql>

View table basic Structure statement (DESCRIBE)

DESCRIBE table name, or DESC table name

View table Detail Structure Statement show CREATE table

SHOW CREATE TABLE example1\g

Example:

Mysql>mysql> desc example1;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| stu_id   -int |     NO   | PRI | NULL    |       | | stu_name | varchar (20) | YES  |     | NULL    |       | | stu_sex  | tinyint (1)  | YES  |     | NULL    |       | +----------+-------------+------+-----+---------+-------+3 rows in Set (0.00 sec) mysql> Show create TABLE Example1\g 1. Row ***************************       table:example1create table:create Table ' example1 ' (  ' stu_id ' int (one) not NULL,  ' stu_name ' varchar default NULL,  ' stu_sex ' tinyint (1) default NULL,  PRIMARY KEY (' stu_id ')) Engine=innodb DEFAULT charset=latin11 Row in Set (0.00 sec) mysql>

　

MySQL Learning-operating database (add and revise) (i)