This is the third day of my participation in the November Gwen Challenge. Check out the details: the last Gwen Challenge 2021
An overview of the
After the last part of the study, we have been able to operate a simple SQLite database. In this study note, we will learn the commands for manipulating tables in SQL.
Start by creating a new database and corresponding tables:
This is a food database with the following tables:
episodes
sqlite3-> create table episodes (
...> id integer primary key,
...> season int.> name text);
Copy the codefoods
sqlite3-> create table foods (
...> id integer primary key,
...> type_id integer.> name text);
sqlite3-> .tables
episodes foods
Copy the codefood_types
sqlite3-> create table food_types(
...> id integer primary key,
...> name text);
sqlite3-> .tables
episodes food_types foods
Copy the codefoods_episodes
sqlite3-> create table foods_episodes(
...> food_id integer.> episode_id integer);
sqlite3-> .tables
episodes food_types foods foods_episodes
Copy the codeThe main table in the above table is foods, where each row corresponds to a different food and the name is stored in the name field. Type_id is a table of referenced food_types used to store various food categories. Finally, the foods_episodes table joins the food in the Foods table to the episodes in the episodes table.
grammar
The declarative syntax of SQL reads like natural language. Statements are expressed in command mode, starting with verbs that describe actions. Here is a simple query statement that contains a subject and a predicate:
select id from foods where name = 'apple';
Copy the codeWhere, select is the verb, indicating the action we want to perform, id from foods is the subject, indicating where we take the value and which value we take; Where name =’apple’ is the predicate that says what conditions should be satisfied by the values we take.
The command
SQL consists of commands, each with a semicolon; The end. Here are three separate commands:
select id, name from foods;
insert into foods values(null.'Whataburger');
delete from foods where id = 413;
Copy the codeCommands, in turn, consist of a series of tokens, which can be constants, keywords, identifiers, expressions, or special strings.
constant
Constants, also called constants, represent exact values. It contains three types: [string constant],[numeric constant] and [binary constant].
-
String constants consist of one or more alphanumeric or numeric characters enclosed by single quotes, for example:
‘Jerry’,’Newman’,’JujyFruit’
Although it is possible to enclose constants in double quotes, it is recommended to use single quotes because this is standard in SQL.
In addition, if the string already contains single quotes, use two consecutive single quotes. For example, Kenny’s Chicken is written as:
Kenny''s chicken -
Numeric constants include integers, decimal numbers, and numbers in scientific notation. Here are some examples:
1, 3.14, 6.00342423 electronicsCopy the code -
Binary values are represented by x’0000′, where each is a hexadecimal number. Binary values must consist of integer multiples (8bits) of two hexadecimal numbers. Here are some examples:
x'01' X'0fff' x'0F0EFF' X'0f0effeb' Copy the code
Keywords and identifiers
The keywords you have specified in the SQL of the meaning of the words, these words including the select, update, insert, create, drop, and the begin.
An identifier is a concrete object in an index database, such as a table or index.
Keywords are reserved words and cannot be used as identifiers.
In addition, SQL is case-insensitive to keywords and identifiers. For example, here is the same statement:
SELECT * FROM foo;
Select * frOm Foo;
Copy the codeNote, however, that the default character constant values in SQL are case sensitive. So, ‘Mike’ and ‘Mike’ are different.
annotation
Single-line comments in SQL are represented by two consecutive hyphens (–). Multi-line comments use the C-style /**/ form, for example:
sqlite> .headers on
sqlite> .mode column
sqlite> select *.> - the food table.> from foods;
id type_id name
---------- ---------- ----------
1 1 apple
Copy the codeThe multi-line comments are as follows:
sqlite> select *.> / *... > * Food with id 1 in the food table... > * /.> from foods where id = 1;
id type_id name
---------- ---------- ----------
1 1 apple
Copy the codeNote: It is recommended to use — as SQL annotations if not absolutely necessary.
Create a table
Like the relational model, SQL is made up of several parts. It has structural parts, such as the part for creating and destroying database objects. This part of the language is often called database Definition Language (DDL). Similarly, there are functional parts that perform operations on these objects, such as retrieving and controlling data, called database Operations Language (DML). The statement for creating a table belongs to the DDL section. You can use the create table command to create a table. The general structure of the create table command is as follows:
create [temp] table table_name(column_definitions [,constraints]);
Copy the code-
In the preceding command, the table declared with the keyword tempor TEMPORARY is a temporary table. A temporary table is a table that only exists in the current session and is automatically destroyed (if not manually destroyed) once the connection is disconnected. Beside the temp square brackets [] said this is optional, in addition, pipe symbol | indicates both choose, is the meaning of the OR OR, for example the following syntax:
create [temp|temporary] table. ;Copy the codeCreate temp table table_name create temp table table_name create temp table table_name You can also write create temporary table table_name… , these two statements are essentially the same. If temporary tables are not explicitly created, the base tables are created and will persist in the database. This is also the most common table in a database. In general, base tables are used to distinguish tables created by create Table from system tables, as well as other table-like structures, such as views.
create tableThe command requires at least one table name and one field nametable_nameRepresents the table name, which must be different from all other identifiers.
column_definationsConsists of a comma-separated list of fields, with each field definition including a name, a field, and a comma-separated field constraint. A type is sometimes calledThe domain, both of which refer to the type of information stored in the column.
SQLiteThere are five local types:integer.real.text.blobandnull.
Constraints constraintsUsed to control what values can be stored in a table or in a particular field. For example: YesuniqueConstraint to specify that the value of a field in all records should be different.
-
The create table command allows an additional field constraint to be followed by a list of fields, as follows:
create table contacts( id integer primary key, name text not null collate nocase, phone text not null default 'UNKNOW'.unique(name,phone) ); Copy the code-
The above command creates a Contacts table with a field ID declared as type INTEGER, limiting the primary key. This combination has special meaning in SQLite, where an integer primary key basically means that the field is self-growing.
-
The field name is declared to be of type TEXT, the constraint cannot be empty, and the sorting is case insensitive.
-
The field phone is also of text type and has two constraints. It cannot be empty and defaults to unknown.
-
After that, the table level constraint is unique, defined on the fields name and phone.
The following actions illustrate the above summary:
Create the Contacts table sqlite> create table contacts( ...> --id.> id integer primary key, ...> --name.> name text not null collate nocase, ...> --phone.> phone text not null default 'UNKNOW'.> unique(name,phone)); Print the definition information for the Contacts table sqlite> .schema contacts CREATE TABLE contacts( --id id integer primary key, --name name text not null collate nocase, --phone phone text not null default 'UNKNOW'.unique(name,phone)); Insert a piece of data into it sqlite> insert into contacts(name,phone) values ('zyf'.'13567890987'); sqlite> select * from contacts; id name phone ---------- ---------- ----------- 1 zyf 13567890987 Continue to insert a piece of data into it sqlite> insert into contacts(name) values ('ddd'); sqlite> select * from contacts; id name phone ---------- ---------- ----------- 1 zyf 13567890987 2 ddd UNKNOW Continue to insert data sqlite> insert into contacts(name) values ('eee'); sqlite> select * from contacts; id name phone ---------- ---------- ----------- 1 zyf 13567890987 2 ddd UNKNOW 3 eee UNKNOW Insert the first and first data that you want to use name sqlite> sqlite> insert into contacts(name) values ('zyf'); sqlite> select * from contacts; id name phone ---------- ---------- ----------- 1 zyf 13567890987 2 ddd UNKNOW 3 eee UNKNOW 4 zyf UNKNOW The next attempt to insert data with the same name as the first one will not be able to insert the data because one already exists in the contacts table after the previous step -- name = zyf, phone = unknown sqlite> sqlite> insert into contacts(name) values ('zyf'); Error: UNIQUE constraint failed: contacts.name, contacts.phone sqlite> Copy the code -
Modify the table
After you have created a table, you can change part of the structure of the table with the ALTER TABLE command. In SQLite, the ALTER table command can either change table names or add fields. The general format of this command is as follows:
alter table table_name {rename to new_name | add column column_def}
Curly braces {} appear in the command above, enclosing a list of options, indicating that one of the options must be selected. For the above command, you can run ALTER table table_name rename to new_name to change the name of the table or run ALTER table table_name add column column_def to add a new column.
If you need to change the table name, you only need to provide the new table name. If you want to add a new column, the definition of the new column should be the same as the definition of the column when the table is created, that is, the list constraint.
Modify the name of the table
The following changes the name of the contacts table to new_contacts, as shown in the following code:
-- Tables in the original database
sqlite> .tables
contacts schema test test2
You can see that it contains a Contacts table and prints the data in it
sqlite> select * fromThe contacts; id name phone---------- ---------- -----------
1 zyf 13567890987
2 ddd UNKNOW
3 eee UNKNOW
4 zyf UNKNOW
5 ddd 12321321
Alter table name
sqlite> -- Change the contacts table name to new_contacts
sqlite> alter table contacts rename to new_contacts;
-- Reprint the tables in the database
sqlite> .tables
new_contacts schema test test2
As you can see, the contacts table is gone and replaced with the new_contacts table, reprinting the data in it
sqlite> select * from new_contacts;
id name phone
---------- ---------- -----------
1 zyf 13567890987
2 ddd UNKNOW
3 eee UNKNOW
4 zyf UNKNOW
5 ddd 12321321
Copy the codeAs you can see from the above print, we successfully changed the name of the contacts table to new_contacts, and the data in the contacts table is the same as before.
Add a new column
If you need to add a new column, you can use the following code:
sqlite> -- Add a new column named email to the new_contacts table
sqlite> alter table new_contacts add column email TEXT NOT NULL DEFAULT ' ' COLLATE NOCASE;
sqlite>
sqlite> -- Look at the definition of the new_contacts table
sqlite> .schema new_contacts
CREATE TABLE IF NOT EXISTS "new_contacts"(
--id
id integer primary key,
--name
name text not null collate nocase,
--phone
phone text not null default 'UNKNOW', email TEXT NOT NULL DEFAULT ' ' COLLATE NOCASE,
unique(name,phone));
sqlite>
sqlite> -- Query data in the new_contacts table
sqlite> select * from new_contacts;
id name phone email
---------- ---------- ----------- ----------
1 zyf 13567890987
2 ddd UNKNOW
3 eee UNKNOW
4 zyf UNKNOW
5 ddd 12321321
Copy the codeIn the above code, we first add a new column named email to the new_contacts table and constrain it not to be empty, which defaults to an empty string and is sorted case insensitive. Then look at the table definition to see that the column has been added to the table. Finally, the data was printed and found to be normal.
In the above code, we specify that email cannot be null and specify the default value. We know that when creating a table, we can set a field that cannot be null and specify the default value. What if we do not specify the default value when modifying the table?
sqlite> -- Add a column named address to the new_contacts table
sqlite> alter table new_contacts
...> add column.> Address -.> address text not null;
Error: Cannot add a NOT NULL column with default value NULL
Copy the codeAs you can see, we tried to add a new column named address to the new_contacts table. We specified that it could not be empty, but we got an error because the default value of the address column was empty, but we specified that it could not be empty, so there was a conflict. Therefore, when we insert a new column, if the value of the new column cannot be empty, we need to set a default value to it to resolve the conflict.