Database (MySQL) Table basic Operations

database table Basic Operations

• After thinking, finally the name of the blog changed to the basic operation of the database table, formerly called the SQL statement Daquan, feeling with "Daquan" this noun is too big, and the article introduced in the content is limited and not deep. If you find an imperfect place in the process of reading, you are welcome to add a note.

Create a database

　　When you start a pen, you create a database, and it feels like you're starting with a half-way. Always install a database before you create a database, whether it's SQL Server, MySQL, or Oracle. But if this write down, do not know whether this blog can be completed, then the default is to install the database, this blog is the following is the use of MySQL database, the first is to log in the database operation (Mysql-u root-p).

2. View the currently existing database:show databases; The MySQL database in the query results is required to describe the user's access rights, and the INFORMATION_SCHEMA database provides a way to access the database metadata, which holds information about all the other databases maintained by the MySQL server. such as database name, database table, table bar data type and access rights, etc. performance_schema is used to collect database server performance parameters.
4. Creating a new database: Create databasestest_db; TEST_DB is the name of the database to be created, and the name cannot be the same as the existing database.
6. View the created database definition: show create databases test_db;

8. Delete databases:drop database test_db; TEST_DB is the name of the database to be deleted.

10. View Database Storage Engine:show engines; The Support column yes indicates that the engine is available, and default represents the current default engine for the database. The default storage engine for the database can be set through the My.ini file.

Create a data table

　　In the database, the data table is the most important and basic Operation object, the base unit of data storage. This section will introduce the data table to increase, delete, change, check operation, so that the reader can master the basic operation of the data table, understand the constraints, the default and the meaning of the rules.

• Create a data table: The data table belongs to the database, and the use test_db should be used before the data table is created ; Specifies which database (test_db) to operate on, and throws no database selected error if no databases are specified. The syntax rules for creating a data table are as follows:

CREATE table < table name > (

Field name data type [column-level constraint] [default],

...

[table-level constraints]

);

2. There is no constraint on the creation of the employee table: Createtable user (id int (one), name varchar (), sex tinyint, age tinyint, salary float); After the statement is executed, a data table named user is created.
4. • single field primary key: create table user1 (id int (one) primary key, name varchar (), sex tinyint, age tinyint, salary float); Same as: create table user1 (id int (one), name varchar), sex tinyint, age tinyint, salary FLOAT,&N Bsp;primary key (ID));
   • multi-field primary key: create table User1 (name varchar), sex tinyint, age tinyint, salary float, primary key (Name,age));
5. use FOREIGN KEY constraints: foreign keys are used to establish a link between two tables, which can be one or more columns. A table can have one or more foreign keys. Foreign keys correspond to referential integrity, a table's foreign keys can be empty, and if not NULL, each foreign key value must be equal to a value of the primary key in another table. The foreign key may not be the primary key of the table, but it corresponds to the primary key of the other table, the primary role of the foreign key is to guarantee the integrity of the data reference, and after the foreign key is defined, it is not allowed to delete rows that have an association relationship in another table.
   • Create foreign key rule:[constraint < foreign key name] foreign key field name 1[, field name 2,...] references< primary table name > Primary key column 1 [, PRIMARY key column 2,...], Foreign key name is the name of the defined foreign KEY constraint, and a table cannot have a foreign key with the same name. The following is an example of student form, class table, curriculum, and score table.
   • Creating a Class Table: Createtable tb_class (ID int primary key, name varchar);
   • Creating a curriculum: Createtable Tb_cou rse (ID int primary key, name varchar);
   • Creating a Student Table: Createtable tb_student (ID int primary key, name varchar (), ClassID int, constraint Fk_class foreign key (class ID) references Tb_class (ID));
   • Create a score table: Creationtable tb_score (SID int, CID int, score float, primary key (SID,CID), Constraint fk_student foreign key (SID ) references Tb_student (ID), Constraint fk_course foreign key (CID) references Tb_course (ID));
7. Use non-null constraint: A non-null constraint means that the value of a field is not NULL, and the database system will error if the user does not specify a value when adding data to a field that specifies a non-null constraint. The constraint rule is: Field name data type NOT NULL, statement:CREATE TABLE user2 (ID int primary key, name varchar () not NULL);
9. Use uniqueness constraint: A uniqueness constraint requires that the column be unique, allowed to be empty, but only with a null value, and a uniqueness constraint ensures that a column or columns do not have duplicate values. Constraint rules: Field name data type unique, statement:CREATE TABLE User3 (ID int primary key, name varchar () unique); A table can have more than one field declared as unique, but only one column that is primary key,primary key is not allowed to be empty, and a unique can exist with a null.
11. Use default constraint: Specifies the default value for a column. Syntax rule for default constraint: Field name data type default default, defaults constraint statement:CREATE TABLE user4 (ID int primary key, name varchar, salary float default 30 00.00); Specifies that the employee's default salary is $3000.
13. The property values of the Set table are automatically incremented by adding the Auto_increment keyword to the table's primary key. The default MySQL auto_increment initial value is 1, each add a new record, the field value is automatically added 1, a table can only have one field to use the auto_increment constraint, and the field must be part of the primary key. The Auto_increment constrained field can be any integer type (tinyint, smallint, int, bigint, and so on). Syntax rules: Field name data Type Auto_increment. SQL statement:CREATE TABLE USER5 (ID int primary key auto_increment, name varchar (), salary float);

View the data table structure

After you create a database table using SQL statements, you can view the definition of the table structure to confirm that the table is defined correctly, you can use the describe and show create TABLE statements in MySQL, and the two statements are described separately in this section.

• View Table BASIC structure statements describe
  • Describe table name, or desc table name;

Null: Indicates whether the column can store null values

Key: Indicates whether the column is indexed, the PRI indicates that the column is part of a table primary key, and Mul indicates that a given value in the column allows multiple occurrences

Default: If the field is null, it indicates that there is no default value

Extra: Represents additional information that can be obtained in relation to a given column, such as Auto_increment.

• View table Detail Structure Statement show CREATE TABLE
  • Syntax format: Show create table name \g;

modifying data tables

Modifying a table is the structure of modifying a data table that already exists in the database. MySQL modifies the table using the ALTER TABLE statement. Common modification table Actions include modifying table names, modifying field data types, or field names, adding or removing fields, modifying the arrangement of fields, changing the storage engine of a table, deleting foreign key constraints for a table, and so on. This section describes the actions that are related to modifying tables.

• Modify Table Name

MySQL uses the ALTER TABLE statement to implement table name modification, syntax rules:ALTER TABLE < old table name > rename [to] < new table name >; Where to is an optional parameter.

• Modify the data type of a field

Convert a field's data type to another data type, syntax rules in MySQL:ALTER TABLE < table name > Modify < Field name > < data type >;

• Modify field names

The syntax rules for modifying field names in MySQL are: ALTER TABLE < table name > change < old field name >< new field name >< new data type >; where the old field name refers to the field name before the modification, The new data type is the modified data type, and if you do not need to modify the field's data type, you can set the new data type to be the same, but the data type cannot be empty. It is also important to note that the statement cannot modify the field name of the primary key.

• Add Field

As your business needs change, you may need to add new fields to existing tables, a complete field that includes field names, data types, integrity constraints, and the syntax for adding fields is as follows:

　　ALTER TABLE < table name > Add < new field name > < data type > [constraint][first|after to exist field name];

The new field is named as the field name that you want to add, and first is an optional parameter that sets the newly added field as the field in the table, and after is an optional parameter that adds the newly added field after the specified existing field name, without both parameters. The newly added field is set to the last column of the data table by default.

• Delete a field

Deleting a field deletes a field from the table in the data table in the syntax format:ALTER TABLE < table name > drop < field name >;

• Modify the arrangement position of a field

After the data table is created, the order in which the fields are arranged in the table is determined, but the structure of the table is not completely immutable, and you can change the relative position of the fields in the table by ALTER TABLE, with the following syntax:

Alter table< table name >modify< field 1>< data type >first|after< field 2>;

Field 1 refers to the field to modify the location, the data type is the data type of field 1, first is an optional parameter, refers to the field 1 is modified to the table of a field,after< field 2> refers to the field 1 is inserted after the field 2.

• Delete a foreign KEY constraint for a table

For foreign keys defined in the database, you can delete them if you do not need them. Once the foreign key is deleted, the association between the primary table and the table is lifted, and the foreign key deletion syntax is:

ALTER TABLE < table name > drop FOREIGN KEY < foreign KEY constraint name >;

The foreign KEY constraint name is the parameter that follows the CONSTRAINT keyword when defining the table, as detailed in the section creating the data table.

• To add a foreign key constraint for a table

If you delete the Tb_score table on the Foreign Key Association of the Tb_course table, the following is how to add the foreign key constraint back, the rule is as follows:

ALTER TABLE < from table > add Constraint fk_course foreign key (foreign key) references < Main Table > (associated primary key);

Delete a data table

The hard -to-create data table, just a statement that deletes a data table, is able to delete the definition of the table and all the data in the table, so be sure to consider it carefully before deleting it.

• Delete data tables that are not associated

In MySQL, you can use drop table to delete one or more data tables that are not associated with another table at a time, with the syntax: drop table [if exists] table 1, table 2,... Table N;

• to delete a table that is associated with another table

If you directly execute DROP table < table name > or error: Cannot delete or update a parent NOW:A FOREIGN KEY constraint fails. Therefore, before you delete the table, you need to first disassociate the other tables from the table, such as Tb_course and Tb_score, and then you can delete the Tb_course table.

Summarize

Finally make a small summary, ① table Delete operation must be cautious, because MySQL in the delete operation, there is no confirmation information, and the delete table operation is to delete the data in the table, so it is best to make a backup of the data in the table before the delete operation. Not every table in the ② database has a primary key, and in general, a primary key is required for connection operations between multiple tables, so there is no need to establish a primary key for each table, and in some cases it is better not to use a primary key. ③ in the MySQL database, the field value with the auto_increment constraint starts at 1, and if you insert the first record in the table, specifying that the Auto_increment field value is 8, the value of the field that is inserted later increases from 9 onwards.

Database (MySQL) Table basic Operations