SQL Basic Operations

Basic operations: CRUD
The basic operations of SQL are categorized into three categories: Library Operations, table operations (fields), data manipulation , and so on.

Library operations

To change the database and delete the search

New Database

Basic syntax
Create database name "library Options"

Library options: Used to constrain a database, divided into two options
1. Character set: Charset/character set specific character set (encoding format for data storage), common character sets: GBK and UTF8
2. Proofing Set Settings: Collate specific proofing sets (rules for data comparison)

Where: The database name cannot use keywords (characters that have been used) or reserved words (which may be used in the future).

What happens after the SQL statement that created the database was executed?
1. In the database system, the corresponding database information is added
2. In the folder where the data is saved: A folder that corresponds to the database name will be created
3. There is an opt file under each database, saving the library options

View Database

1. View all databases: show databases;
   
2. View the database for the specified section: Fuzzy query
   Show databases like ' pattern '; –pattern is a matching pattern
   %: Indicates that multiple characters are matched
   -: Indicates a match for a single character
   
3. To view the creation statement for the database:
   Show CREATE DATABASE name
   

Update Database

Database names cannot be modified.
Database modification limited to library options: Character set and proofing set (collation set dependent character set)
ALTER DATABASE name "library option";

Deleting a database

In all operations, the deletion is the simplest
Drop database name

What happens when a database statement is deleted?
1. The corresponding database is not visible within the database
2. In the corresponding database storage folder, the database name corresponding folder is also deleted (Cascade Delete: All the data tables inside delete)

Note: The deletion of the database is not a joke, do not arbitrarily delete, should be first after the backup operation (delete irreversible)

Table Operations

Tables are inseparable from fields.

New Data Sheet

The CREATE table [if not EXISTS] table name (
Field name data type,
Field name data type – The last line does not require commas
) [Table options];

 if not exists：如果表名不存在，那么就创建，否则不创建。 表选项：控制表的表现    1. 字符集：charset/character set 具体字符集; -- 保证表中数据存储的字符集    2.  校对集：collate 具体校对集    3.  存储引擎：engine 具体存储引擎（innodb和myisam）

The design of any one table must specify the database.

Scenario One: Display the specified database to which it belongs
CREATE table database name. Table Name – assigns the creation of the current data table to the database

Scenario Two: Implicitly specifying the database to which the table belongs: first into a database environment, and then the tables created automatically belong to a specified database.
Enter database environment: use database name;

What happens after the SQL that created the data table specifies execution?
1. The corresponding table already exists under the specified database
2. In the database corresponding to the folder, will produce the corresponding table structure file (with the storage engine has a relationship)

View Data Sheet

The way the database can be viewed, the table can be viewed
1. View all tables: show tables;

2. View part of the table, fuzzy matching: Show tables like ' pattern ';

3. View the table creation statement show create table table name;

4. View Table structure: View field information in a table
Desc/describe/show columns from table name;

modifying data tables

The table itself exists, and there are fields. Table modifications are divided into two parts: modify the table itself and modify the fields

Modify the table itself

The table itself can be modified: Table name and table options
Modify table name: Rename table cousin name to new name;

Modify Table options: Character set, proofing set, and storage engine
ALTER TABLE table name option [=]

modifying fields

Field operations are many: new, modified, renamed, deleted

New Field
ALTER TABLE name add [column] field name data type [columns properties] [position];
Location: Field names can be placed anywhere in the table
First: Position One
After: Which field after: The After field name defaults to after the last field

Modify a field: typically modify a property or data type
ALTER TABLE name modify field name data type [properties] [location]

Rename Field
ALTER TABLE name change old field new field data type [property] [position];

Delete a field
ALTER TABLE name drop field name;

Caution: If data already exists in the table, deleting the field clears all data for that field (irreversible)

Delete a data table

drop table name 1, table name 2 ...; – Multiple tables can be deleted at once

What happens when the instruction to delete the data table executes?
1. In the table space, the specified table is not
2. In the database corresponding to the folder, the table corresponding files will also be deleted
Note: The removal is dangerous, the operation should be cautious (irreversible)

Data manipulation New data

There are two scenarios
Scenario One: Inserting data into a full table field, without specifying a list of fields, requires that the values of the data appear in the same order as the fields that are designed in the table, and that all non-numeric data needs to be set using quotation marks (which are recommended as single quotes).
Insert into table name values (value list) [, (Value list)]; – Multiple records can be inserted at once

Scenario two: Inserting data into a partial field requires selecting a field list, the order in which the field list appears is independent of the order of the fields, but the order of the list of values must match the order of the selected fields.
Insert into Table name (field list) VALUES (value list) [, (Value list)];

View data

Select (*/Field list) from table name;
View All data:

View the specified field, specifying the data for the condition

Update data

Update table name Set field = value [where condition]; – Suggest where, or update all

Updates are not necessarily successful, such as no data to update.

Delete data

Delete is irreversible, be cautious
Delete from table name [where condition];

Chinese data issues

The essence of Chinese data problem is character set problem.
The computer is just not binary, human is more of a recognition symbol, need to have a binary and character correspondence (character set).

The client inserted Chinese data into the server without success.

View all character sets
Show character set;

View the server default character set for external processing
Show variables like ' character_set% ';

Root cause: Client data can only be GBK, and the server is considered UTF8.
Solution: Change the server, the default receive character set is GBK.
Set character_set_client = GBK;

Insert data Again (Chinese)

View data, find garbled

Cause: The data source is the server, the parsing data is the client (the client only recognizes GBK),
But the server gives the data is utf8, all garbled.
Solution: Modify the data set of the server to the client as GBK
Set Character_set_results =GBK;

View data

Set variable = value, modify only session level (current client is active at the time of connection, shutdown is disabled)

To set the server's awareness of the client's character set: You can use the shortcut: Set names character set
Set names GBK; –> character_set_client,character_set_results,character_set_connection;

Connection: The connection layer: is the middle of the character set transformation, if the unification of the efficiency is higher, the unity does not affect.

Proofing Set Issues

Proofing sets: how data is compared
The proofing set has three formats
_bin:binary, binary comparison, removing binary, one-to-one comparison, case-sensitive
_cs:case sensitive, case-sensitive, uppercase and lowercase
_ci:case insensitive, case insensitive, case insensitive

To view the proofing sets supported by the database
Show collation;

Proofing Set Application: The proofing set takes effect only when the data is generated by comparison
Proofing set: Must be declared before no data, if there is data, then the proofing set modification, then the modification is invalid.

Web garbled problem

Dynamic Web site consists of three parts: browser, Apache Server (PHP), database server
Character Set (Chinese), data needs to be passed back and forth between three parts: easily garbled

How to solve garbled characters: Unified coding (three kinds of unity)

But the truth is impossible: The browser is User-managed (impossible to control)
But these problems have to be solved (done by PHP)

SQL Basic Operations