SQL programming for DataWhale team learning - September - task1

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

data typedescribe
INTEGER typeUsed to specify the data type (numeric) of the column where integers are stored. Decimal numbers cannot be stored.
CHAR typeIt 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 typeIt 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 typeThe data type (date type) of the column used to specify the storage date (month, year, day).

1.2.3 constraint settings

Constraint settings categorydescribe
NOT NULLNon NULL constraint, that is, data must be entered for this column.
PRIMARY KEYThe 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

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:


Advantages: compared with drop / delete, truncate is the fastest to clear data.

1.7 data update

1.7.1 single line update

Basic syntax:

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

Basic syntax:

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, Index

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(  
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.

3, Exercises

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.

Tags: Database SQL SQLite datawhale

Posted on Tue, 14 Sep 2021 18:37:49 -0400 by gordonmc