Task1 introduction database
1, Basic SQL statement
1.1 create database
CREATE DATABASE < Database name > ;
1.2 create data table
CREATE TABLE < Table name > ( < Column name 1> < data type > < Constraints required for this column > , < Column name 2> < data type > < Constraints required for this column > , < Column name 3> < data type > < Constraints required for this column > , < Column name 4> < data type > < Constraints required for this column > , . . . < Constraint 1 for this table> , < Constraints for this table 2> ,......);
1.2.1 naming rules
- Only half width English letters, numbers and underscores () can be used as the names of databases, tables and columns
- The name must begin with a half width English letter
- The name cannot be duplicate
1.2.2 designation of data type
|INTEGER type||Used to specify the data type (numeric) of the column where integers are stored. Decimal numbers cannot be stored.|
|CHAR type||It is used to store fixed length strings. When the length of the string stored in the column is less than the maximum length, half width spaces are used to supplement. It is generally not used because it will waste storage space.|
|VARCHAR type||It is used to store variable length strings. Fixed length strings will be supplemented with half width spaces when the number of characters does not reach the maximum length, but variable length strings are different. Even if the number of characters does not reach the maximum length, half width spaces will not be supplemented.|
|DATE type||The data type (date type) of the column used to specify the storage date (month, year, day).|
1.2.3 constraint settings
|Constraint settings category||describe|
|NOT NULL||Non NULL constraint, that is, data must be entered for this column.|
|PRIMARY KEY||The primary key constraint represents that the column is a unique value, and the data of a specific row can be retrieved through the column.|
1.3 delete data table
Syntax for deleting tables:
DROP TABLE < Table name > ;
It should be noted that the deleted table cannot be recovered and can only be reinserted. Please be careful when deleting.
1.4 new columns in data sheet
ALTER TABLE statement to add columns:
ALTER TABLE < Table name > ADD COLUMN < Definition of columns >;
1.5 deleting rows and columns in data table
- ALTER TABLE statement to delete a column:
ALTER TABLE < Table name > DROP COLUMN < Listing >;
- To delete an ALTER TABLE statement for a specific row:
-- Be sure to add WHERE Condition, otherwise all data will be deleted DELETE FROM product WHERE COLUMN_NAME='XXX';
The ALTER TABLE statement, like the DROP TABLE statement, cannot be restored after execution. Mistakenly added columns can be deleted through the ALTER TABLE statement, or the tables can be deleted and recreated.
1.6 empty table contents
Statement to empty table contents:
TRUNCATE TABLE TABLE_NAME;
Advantages: compared with drop / delete, truncate is the fastest to clear data.
1.7 data update
1.7.1 single line update
UPDATE <Table name> SET <Listing> = <expression> [, <Column name 2>=<Expression 2>...]; WHERE <condition>; -- Optional, very important. ORDER BY clause; --Optional LIMIT clause; --Optional
When using update, pay attention to adding the where condition, otherwise all rows will be modified according to the statement.
1.7.2 multi line update
Multi column update
The SET clause of the UPDATE statement supports multiple columns as UPDATE objects at the same time.
-- Basic writing, one UPDATE Statement updates only one column UPDATE product SET sale_price = sale_price * 10 WHERE product_type = 'kitchenware'; UPDATE product SET purchase_price = purchase_price / 2 WHERE product_type = 'kitchenware';
This writing method can get the correct results, but the code is cumbersome. You can simplify the code by merging.
-- Combined writing UPDATE product SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 WHERE product_type = 'kitchenware';
It should be clear that the columns in the SET clause can be not only two columns, but also three columns or more.
1.8 inserting data into data sheets
INSERT INTO <Table name> (Column 1, Column 2, Column 3, ......) VALUES (Value 1, Value 2, Value 3, ......);
When performing full column INSERT on a table, you can omit the column list after the table name. At this time, the VALUES of the VALUES clause will be assigned to each column from left to right by default.
2.1 function of index
The establishment of MySQL index is very important for the efficient operation of MySQL. Index can greatly improve the retrieval speed of MySQL.
For example, if MySQL with reasonable design and index is a Lamborghini, MySQL without design and index is a human tricycle.
Take the table of contents page (index) of a Chinese dictionary for example. We can quickly find the required words according to the table of contents (index) sorted by pinyin, strokes, partial radicals, etc.
The index creates an orderly data structure. When the dichotomy is used to search data, its complexity is log2(N). If more than 10 million data are searched 23 times, its efficiency is very efficient.
2.2 index creation
When creating a table, you can directly create an index. The syntax is as follows:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
You can also use the following statement to create:
-- Method 1 CREATE INDEX indexName ON table_name (column_name) -- Method 2 ALTER table tableName ADD INDEX indexName(columnName)
2.3 index classification
- primary key
Indexes built on primary keys are called primary key indexes. A data table can only have one primary key index. Index column values are not allowed to have empty values. They are usually created together when creating tables.
- unique index
The index built on the UNIQUE field is called a UNIQUE index. A table can have multiple UNIQUE indexes. The index column value can be null. Multiple null values in the column value will not cause repeated conflicts.
- General index
An index built on a common field is called a common index.
- Prefix index
Prefix index refers to the index established for the first few characters of character type field or the first few bytes of binary type field, rather than the index established on the whole field. Prefix indexes can be built on columns of char, varchar, binary and varbinary types, which can greatly reduce the storage space occupied by the index and improve the query efficiency of the index.
- Full text index
Using "word segmentation technology" to realize an index of searching keywords in long text.
SELECT * FROM article WHERE MATCH (col1，col2，...) AGAINST (expr [ search _ modifier ])
1. In versions before MySQL 5.6, only MyISAM storage engine supports full-text indexing;
2. MySQL 5.6 and later versions, MyISAM and InnoDB storage engines support full-text indexing;
3. Full text indexes can be created only when the data types of fields are char, varchar, text and their series.
4. If possible, try to create a table and insert all data before creating a full-text index, instead of directly creating a full-text index when creating a table, because the former is more efficient than the latter.
- single column
An index built on a single column is called a single column index.
- Federated index (composite index, multi column index)
Indexes built on multiple columns are called joint indexes, also known as composite indexes and composite indexes.
1. Write a CREATE TABLE statement to create a table Addressbook containing the items listed in table 1-A and register_ Set the primary key constraint in the NO (registration number) column
create table Addressbook ( regist_no integer not null, name varchar(128) not null, address varchar(128) not null, tel_no char(10), mail_address char(10), primary key(regist_no) );
2. Suppose you forget to add the following column postal when creating the Addressbook table in Exercise 1.1_ Code, please write SQL to add this column to the Addressbook table
alter table Addressbook add column postal_code char(8) not null;
3. Use SQL statements to delete the Addressbook table
drop table Addressbook;
4. There is no SQL statement to restore the deleted Addressbook table. You can only reinsert it.