mysql-Data Sheet

mysql-data table Mysql-create table creation

To create a new table in the database, you can use the MySQL CREATE TABLE statement.

CREATE table [IF not EXISTS] table_name (        column_list #在 column_list section specifies a list of tables. ) engine=table_type;

You need to engine specify the storage engine for the tables in the clause. You can use any storage engine, such as:InnoDB,MyISAM,HEAP,EXAMPLE,CSV,
ARCHIVE,MERGE,Federated or Ndbcluster. If the storage engine is not explicitly declared, MySQL will use InnoDB by default . 

MySQL-CREATE TABLEDefine columns for a table in a statement

MySQL-CREATE TABLEThe statement defines the column statement for the table:

column_name Data_type[size] [not null|  NULL] [DEFAULT value] [auto_increment]

The most important components of the above syntax are:

1.column_nameSpecifies the name of the column. Each column has a specific data type and size, for example: VARCHAR(255) .

2.NOT NULLOr NULL indicates whether the column accepts a NULL value.

3.DEFAULTThe value is used to specify the default value for the column.

4.AUTO_INCREMENTIndicates that the value of the column is automatically incremented whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

If you want to set a specific column of a table as the primary key, use the following syntax:

PRIMARY KEY (col1,col2,...)

Data table Creation Instance:

Create this Tasks table using a CREATE TABLE statement creating table IF not EXISTS tasks (  task_id INT (one) not NULL auto_increment,
    subject VARCHARdefault NULL,  start_date date default NULL,  end_date date default NULL,  Description VARCHAR (+) DEFAULT NULL,  PRIMARY KEY (task_id)) ENGINE=innodb;

mysql-data table Structure modified Mysql-alter Table statement

Feature Description: You can use ALTER TABLE statements to change the structure of an existing table. ALTER TABLEstatements can be used to add columns, delete columns, change the data type of a column, add a primary key, rename a table, and so on

ALTER TABLE statement syntax: ALTER TABLE table_name ACTION1[,ACTION2,...]

To change the structure of an existing table:

First, ALTER TABLE specify the name of the table you want to change after the clause.

Second, list the set of actions to apply to the table. Actions can be any operation that adds a new column, adds a primary key, renames a table, and so on. ALTER TABLEstatement allows ALTER TABLE multiple operations to be applied in a single statement, each separated by a comma ( ， ).

Create a tasks new table named:

DROP TABLE IF EXISTS tasks; CREATE TABLE Tasks (    task_id INT not NULL,    subject VARCHARnull,    start_date date null,    End_ Date Date null,    description VARCHAR ($) null,    PRIMARY KEY (task_id),    UNIQUE INDEX task_id_ Unique (task_id ASC));

Use the MySQL ALTER table statement to set the auto-increment property of a column

When you insert a new row in the Task table, task_id the value of the column is automatically incremented 1 . You can then use the ALTER TABLE statement to task_id set the properties of the column as follows AUTO_INCREMENT :

ALTER TABLE taskschange COLUMN task_id task_id INT (11) Not NULL auto_increment;

To add a new column to a table using the MySQL ALTER table statement

Because of the new business requirements, you need to add a complete new column named to store the percent complete for each task in the Task table. In this case, you can use to ALTER TABLE add a new column to the tasks table

ALTER TABLE Tasks ADD COLUMN complete DECIMAL (2,1) nullafter description;

Use MySQL ALTER table to remove columns from a table

Suppose you do not want to store the description of a task in tasks a table, and you must delete it. The following statement allows you tasks to delete a table's description columns:

ALTER TABLE tasksdrop COLUMN description;

To rename a table using the MySQL ALTER table statement

ALTER TABLE tasksrename to Work_items;

mysql-Data Table renaming

To change one or more tables, use the RENAME TABLE statement:

RENAME TABLE old_table_name to New_table_name;

RENAME TABLEbefore you execute a statement, you must make sure that there are no active transactions or locking tables.

MySQL RENAME Table Example

Create a hrdb new database named two tables consisting of: employees anddepartments

Create a database

CREATE DATABASE IF not EXISTS Hrdb;

Create a table

Use Hrdb; CREATE TABLE departments (    department_id INT auto_increment PRIMARY KEY,    dept_name VARCHAR); CREATE TABLE employees (    ID int auto_increment primary key,    first_name varchar (not  null,    last_name varchar ( not null,    not  null,    FOREIGN KEY (department_id )        REFERENCES Departments (department_id));

Inserting sample data into employees and departments tables

--insert data into the Departments table insert into departments (dept_name) VALUES ('Sales'),('markting'),('Finance'),('Accounting'),('Warehouses'),('Production');--insert data into the Employees table insert into employees (FIRST_NAME,LAST_NAME,DEPARTMENT_ID) VALUES ('John','Doe', 1), ('Bush','Lily', 2), ('David','Dave', 3), ('Mary','Jane', 4), ('Jonatha','Josh', 5), ('Mateo',' More', 1);

Renaming a table referenced by a view

employees departments Create a view that is named based on the and table v_employee_info

CREATE VIEW V_employee_info    as SELECT         ID, first_name, last_name, Dept_name    from        Employees            INNER JOIN        Departments USING (DEPARTMENT_ID);

Rename the v_employee_info table in the view employees to people , and query the view's data.

RENAME TABLE employees to people; -- query data     Select* from    v_employee_info;

Use CHECK TABLE statements to check v_employee_info the state of a view

Check table V_employee_info;mysql> Check table v_employee_info;

You need to manually change v_employee_info the view so that it references a people table instead of a employees table.

Renaming a table referenced by a stored procedure

If you want to rename a table that is referenced by a stored procedure, you must manually adjust it like a view.

First, rename the people table to a employees table.

RENAME TABLE people to employees;

Create a get_employee new stored procedure named procedure that references the employees table.

= p_id; END $ $DELIMITER;

Next, execute the get_employee stored procedure from the employees table to get the id 1 data for the employee

Call Get_employee (1);

We employees re-renamed the table to a people table.

RENAME TABLE employees to people;

Finally, call get_employee the stored procedure to id get 2 the employee information for:

Call Get_employee (2);

MySQL returns an error message, and you must manually change the table in the stored procedure to a employees people table.

Renaming a table referencing a foreign key

departmentsTables are department_id linked to tables using columns employees . A employees column in a table department_id is a column referencing a departments table as a department_id foreign key

If you rename departments a table, departments all foreign keys that point to the table are not automatically updated. In this case, we must manually delete and re-create the foreign key.

RENAME TABLE departments to Depts;

Deleted ID as 1 a department, because of foreign key constraints, people all rows in the table should also be deleted. However, we rename the department table to a depts table without manually updating the foreign keys.

DELETE from depts WHERE     = 1;

MySQL will return an error

Renaming multiple tables

Use RENAME TABLE statements to rename multiple tables at once

RENAME TABLE old_table_name_1 to new_table_name_2,             old_table_name_2 to new_table_name_2,...

The following statements are renamed people and renamed depts to employees and departments table:

RENAME TABLE depts to departments,             people to employees;

To rename a table by using the ALTER TABLE statement

ALTER TABLE old_table_namerename to New_table_name;

Renaming a temporary representation example

The first step is to create a temporary table that contains employees all the last_name unique last names of the columns from the table:

 from Employees;

Second step, using the RENAME TABLE rename last name table

RENAME TABLE lastnames to Unique_lastnames;

MySQL will return an error message

The third step is to use the ALTER TABLE statement to rename the last name table.

ALTER TABLE lastnamesrename to Unique_lastnames;

mysql-Data Sheet