MySQL and mysql

MySQL and mysql

Note: The content of the trap comes from the yibai tutorial, this is just a summary of your learning experience on the road... (attached to the yibai tutorial Web site: http://www.yiibai.com/mysql)

MySQL import Sample Database (http://www.yiibai.com/mysql/how-to-load-sample-database-into-mysql-database-server.html)

Basic statement:

SELECT statement(Obtain data from a table or an attempt)

Query all information in the employees table

 SELECT * FROM employees;

To view only the employee's name, surname, and position, use the following query:

 SELECT lastname, firstname, jobtitle FROM employees;

Where statement(Specify the row to be selected based on the specified filter expression or condition)

Assume that you only wantemployeesYou can use the following query to obtain the sales representative employees in the table:

SELECT     lastname, firstname, jobtitleFROM    employeesWHERE    jobtitle = 'Sales Rep';

 

Database Operations:

　　Create a database:

 

CREATE DATABASE [IF NOT EXISTS] database_name;

 

Show database:

SHOW DATABASES

　　Select the database to use:

USE DATABASE;

　　Delete database:

 

DROP DATABASE [IF EXISTS] database_name;

 

 

 

Database _ TABLE _ Operations:　　　　　　　

Create a table:

 

CREATE TABLE [IF NOT EXISTS] table_name(        column_list) engine=table_type;

 

Note:CREATE TABLEThe statement defines columns for the table. Use the following syntax:

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

• column_nameSpecifies the column name. Each column has a specific data type and size, for example:VARCHAR(255).
• NOT NULLOrNULLWhether the column is acceptedNULLValue.
• DEFAULTValue is used to specify the default value of a column.
• AUTO_INCREMENTIndicates that the column value is automatically increased whenever a new row is inserted into the table. Each table has one and only oneAUTO_INCREMENTColumn.

To set a specific column of a table as a primary key, use the following syntax:

 

PRIMARY KEY (col1,col2,...)

 

Eg:

CREATE TABLE IF NOT EXISTS tasks (  task_id INT(11) NOT NULL AUTO_INCREMENT,  subject VARCHAR(45) DEFAULT NULL,  start_date DATE DEFAULT NULL,  end_date DATE DEFAULT NULL,  description VARCHAR(200) DEFAULT NULL,  PRIMARY KEY (task_id)) ENGINE=InnoDB;

View Code

 

Modify a table (including deleting a table ):

First, createtasksNew table:

 

DROP TABLE IF EXISTS tasks;CREATE TABLE tasks (    task_id INT NOT NULL,    subject VARCHAR(45) NULL,    start_date DATE NULL,    end_date DATE NULL,    description VARCHAR(200) NULL,    PRIMARY KEY (task_id),    UNIQUE INDEX task_id_unique (task_id ASC));

View Code

 

Use the MySQL alter table statement to set the auto-increment attribute of a column:

ALTER TABLE tasksCHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

Verification:

INSERT INTO tasks(subject,                  start_date,                  end_date,   description)VALUES('Learn MySQL ALTER TABLE',       Now(),       Now(),      'Practicing MySQL ALTER TABLE statement');INSERT INTO tasks(subject,                  start_date,                  end_date,           description)VALUES('Learn MySQL CREATE TABLE',       Now(),       Now(),      'Practicing MySQL CREATE TABLE statement');SELECT     task_id, descriptionFROM    tasks;

View Code

Use the MySQL alter table statement to add a new column to the TABLE:

ALTER TABLE tasks ADD COLUMN complete DECIMAL(2,1) NULLAFTER description;

Use MySQL alter table to extract data from a TABLEDeleteColumn:

ALTER TABLE tasksDROP COLUMN description;

Rename a table:

ALTER TABLE tasksRENAME TO work_items;

 

 

　　　

Add, delete, modify, and query data:

　　First, create a table:　　

USE testdb;CREATE TABLE IF NOT EXISTS tasks (    task_id INT(11) AUTO_INCREMENT,    subject VARCHAR(45) DEFAULT NULL,    start_date DATE DEFAULT NULL,    end_date DATE DEFAULT NULL,    description VARCHAR(200) DEFAULT NULL,    PRIMARY KEY (task_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT)

 INSERT INTO table(column1,column2...) VALUES (value1,value2,...);

Multiple rows:

INSERT INTO table(column1,column2...)VALUES (value1,value2,...),       (value1,value2,...),...;

If the value of the corresponding column is specified for all columns in the table, ignoreINSERTThe column list in the statement is as follows:

INSERT INTO tableVALUES (value1,value2,...),       (value1,value2,...),...;

Update statement(Update data)

UPDATE table_name SET     column_name1 = expr1,    column_name2 = expr2,    ...WHERE    condition;

Eg:

UPDATE employees SET     email = 'mary.new@yiibai.com'WHERE    employeeNumber = 1056;

Multiple rows:

UPDATE employees SET     lastname = 'Hill',    email = 'mary.hill@yiibai.com'WHERE    employeeNumber = 1056;

Delete statement(Delete data)

DELETE FROM table_nameWHERE condition;