MySQL Must Know must Know – Concepts Section

MySQL must know must – Install application section

MySQL must know must – retrieve data sections

MySQL must know must know – join tables and advanced query sections

MySQL Must know must know – text search article

The database files used below are available in themysql_scriptsTo find it.

Create and manipulate tables

Create a table

To CREATE a TABLE using CREATE TABLE, you must give the name of the new TABLE, followed by the name and definition of the TABLE column, separated by commas.

A NULL value

A NULL value means no value or missing value. Columns that allow NULL values are also allowed to insert rows without giving the value of that column. NOT NULL indicates that a column with a NULL value is NOT allowed to accept rows that have no value. When a row is inserted or updated, the column must have a value. NULL is the default setting. If NOT NULL is NOT specified, NULL is assumed to be specified.

A primary key

The primary key must be unique, that is, each row in the table must have a unique primary key value, or if the primary key uses a single column. If multiple columns are used, the combined values of the columns must be unique.

Use statements like PRIMARY KEY (ID). To create a PRIMARY KEY consisting of multiple columns, each column name should be given in a comma-separated list, such as the PRIMARY KEY (order_num, order_item) used in the creation of the ORDERItems table.

Primary keys can be defined when the table is created or after the table is created.

A primary key is a column that uniquely identifies each row in a table. Only columns that do not allow NULL values can be used in a primary key.

AUTO_INCREMENT

MySQL automatically increments the column each time an INSERT operation is performed.

Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (e.g. by using it as a primary key)

You can assign an AUTO_INCREMENT value to an INSERT, as long as it is unique so far, and that value will be used instead of the automatically generated value. Subsequent increments will start using the manually inserted value.

This function returns the last AUTO_INCREMENT value

Specify default values

Specifies the DEFAULT keyword in the column definition. MySQL, like most DBMSS, does not allow functions as default values; it only supports constants.

Engine type

Most of the time, CREATE TABLE statements all end with ENGINE=InnoDB statements. MySQL has multiple engines that are packaged and hidden within MySQL servers, all of which can execute commands like CREATE TABLE and SELECT. Each of these engines has different functions and features, and choosing the right engine for different tasks gives you great power and flexibility.

InnoDB is a reliable transaction engine that does not support full-text search.

MyISAM is a very high performance engine that supports full-text search but does not support transaction processing

MEMORY is equivalent to MyISAM in function, but because the data is stored in MEMORY (not disk), it is fast (especially suitable for temporary tables)

# # # # # # # # # # # # # # # # # # # # # # # # # see customers TABLE CREATE # # # # # # # # # # # # # # # # # # # # # # # # the CREATE TABLE customers (cust_id int the NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cust_state char(5) NULL , cust_zip char(10) NULL , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL , PRIMARY KEY (cust_id) ) ENGINE=InnoDB;Copy the code

Update the table

To update the TABLE definition, use the ALTER TABLE statement.

ALTER TABLE vendors ADD VEND_phone CHAR(20); ALTER TABLE vendors DROP COLUMN VEND_phone; ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id)Copy the code

Delete table

DROP TABLE customers2;
Copy the code

Rename table

### RENAME TABLE customers2 TO customers; ## RENAME multiple tables RENAME TABLE backup_customers TO customers,backup_vendors TO vendors;Copy the code

Pay attention to

  • When creating a new table, the specified table name must not exist, or an error will occur. IF you only want to create a table IF it does NOT exist, you should specify IF NOT EXISTS after the table name.
  • Be extremely careful with ALTER tables and make a full backup (backup of schema and data) before making changes


Insert data

Insert the full row

In the following statement, a value must be supplied for each column, or NULL should be used if a column has no value (assuming the table allows NULL values for that column). Each column must be given in order, auto-increment values cannot be ignored, and if you do not want to assign, you need to specify NULL. INSERT INTO customers VALUES( NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);Copy the code

The syntax above should be avoided because it is unsafe. Instead, use the following statement, which can be filled out of order, as long as the order in VALUES is the same as the order of the column names given earlier.

INSERT INTO customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew'.NULL.NULL.'100 Main Street'.'Los ANGELES'.'CA'.'90046'.'USA');
Copy the code

Insert multiple rows

### You can use multiple INSERT statements, or even commit them once, ending each statement with a semicolon. INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('Pep E. LaPew','100 Main  Street','Los Angeles','CA','90046','USA'); INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('M. Martian','42 Galaxy Way','New York','NY','11213','USA'); ### or the same column names (and order) in each INSERT statement, INSERT INTO CUSTOMERS (CUST_NAME, CUST_ADDRESS, CUST_CITY, CUST_STATE, CUST_ZIP, CUST_country) VALUES('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA'), ('M. Martian','42 Galaxy Way','New York','NY','11213','USA');Copy the code

Pay attention to

  • Columns can be omitted in INSERT operations if they are defined to allow NULL values (no value or NULL value) or if a default value is given in the table definition, which means that if no value is given, the default value will be used, otherwise omission will be an error when inserting.
  • You can lower the PRIORITY of an INSERT statement by using INSERT LOW PRIORITY INTO.


Update the data

Update the email with the id of customer 10009. UPDATE customers SET cust_name = 'The Fudds', cust_email = '[email protected]' WHERE cust_id = 10009; UPDATE customers SET CUST_email = NULL WHERE CUST_ID = 10009;Copy the code

Pay attention to

  • When using UPDATE, you must not omit the WHERE clause, otherwise all rows in the table will be updated.
  • If the UPDATE operation fails, the entire UPDATE operation is cancelled and all rows updated before the error occur are restored to their original values. If you want to continue updating even when an error occurs, you can use the IGNORE keywordUPDATE IGNORE customers


Delete the data

DELETE FROM customers WHERE CUST_id = 10011;Copy the code

Pay attention to

  • When using DELETE, you must not omit the WHERE clause, otherwise all rows in the table will be deleted. Even if all rows are deleted, DELETE does not DELETE the table itself.
  • If you want to DELETE all rows from a table, do not use DELETE. The TRUNCATE TABLE statement can be used to be faster (TRUNCATE actually deletes the original TABLE and creates a new TABLE, rather than deleting data in the TABLE row by row).

Front- end-Basics: Watch, Star, Front-End-Basics: Front-End-Basics

MySql must know must know this article