Getting Started with SQL

Introduction to Databases

The so-called installation database server, just installed on the machine a database management program, the hypervisor can manage multiple databases, the general developer will create a database for each application.
To save data for an entity in your app, you typically create multiple tables in the database to save data for entities in your program.
Database server, database, and table relationships:

How data is stored in the database:

SQL syntax

CREATE DATABASE statement: "Create databases [IF not EXISTS] database_name [create_specification [, create_specification] ...];

Create_specification:

[DEFAULT] CHARACTER SET Charset_name
[DEFAULT] COLLATE collation_name

CHARACTER set: Specifies the character set used by the database

COLLATE: Specifying proofing rules for the database character set

Show database statements: Show DATABASES;

Show database creation statement: show create databases db_name;

DB DELETE statement: drop database [IF EXISTS] db_name;

Modify the database character set and proofing rules statements: ALTER databases [IF not EXISTS] db_name [alter_specification [, Alter_specification] ...]

Alter_specification:

[DEFAULT] CHARACTER SET Charset_name

[DEFAULT] COLLATE collation_name

Back up data statements (Execute in Windows command window after database and tables are ready): Mysqldump-u user name-p database name > file name. sql

Recover database statements (after backing up the database and deleting the database): Mode 1: (1) Create library: Name of the database; (2) Select the database to be created. (3) Source file name. sql

Way 2:mysql-uroot-p mydb1<c:\test.sql (Window command)

Create a Table statement (note: Before you create a table, use the library with use DB statements):

CREATE TABLE table_name
(Field1 datatype,
Field2 datatype,
FIELD3 datatype,
) Character set character Set collate proofing rules
Field: Specify column name datatype: Specify column type

When you create a table, you create the appropriate columns based on the data you want to save, and you define the appropriate column types based on the type of data you have, MySQL common data types are as follows:

Modify Table statement (ALTER table):

Append a column: ALTER TABLE table-name ADD (column datatype [DEFAULT expr] [, column datatype] ...);

Modify a column: ALTER TABLE table-name MODIFY (column datatype [DEFAULT expr][, column datatype] ...);

Delete a column: ALTER TABLE table-name DROP (column);

Modify table name: Rename table name to new table name

Modify the character set of the table: ALTER TABLE student character set UTF8;

Database crud Statements

Insert statement: INSERT INTO table [(column [, Column ...])] VALUES (value [, value ...]);

(1) The data inserted should be the same as the data type of the field.

(2) The size of the data should be within the specified range of columns, for example: a string of length 80 cannot be added to a column of length 40.

(3) The data location listed in values must correspond to the arranged position of the column being joined.

(4) Character and date data should be enclosed in single quotes.

(5) Insert null value, do not specify or INSERT into table value (NULL)

UPDATE statement: Update tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]

(1) The update syntax can update the columns in the original table row with the new values.

(2) The SET clause indicates which columns to modify and which values to give.

(3) WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated.

Delete statement: delete from tbl_name [WHERE where_definition]

(1) If you do not use the WHERE clause, all data in the table is deleted.

(2) Delete statement cannot delete a column's value (can use update)

(3) Use the DELETE statement to delete only records, not delete the table itself. To delete a table, use the DROP TABLE statement.

(4) As with insert and update, deleting records from one table will cause referential integrity problems for other tables, and you should always remember this potential problem in your mind when modifying database data.

(5) Delete the data in the table can also use the TRUNCATE TABLE statement, which differs from the delete, see MySQL documentation.

SELECT statement: SELECT [DISTINCT] *| {column1, Column2. Column3 ...} from table;

(1) Select specifies which columns of data to query.

(2) column specifies the list name.

(3) * Indicates that all columns are queried.

(4) from specifies which table to query.

(5) Distinct optional, indicates whether duplicate data is excluded when displaying the result

(6) In a SELECT statement, you can use expressions to perform operations on the columns of a query: select *| {column1|expression, Column2|expression,..} from table;

(7) The AS statement can be used in the SELECT statement: Select column as alias from table name;

Use the WHERE clause to filter the query, the operators that are used frequently in the WHERE clause:

To sort the query results by using the ORDER BY clause: SELECT column1, Column2. Column3. Fromtable Order BY Column Asc|desc

(1) Order by specifies the sorted column, which can be the column name in the table, or the column name specified after the SELECT statement.
(2) ASC Ascending, desc descending
(3) The ORDER by clause should be at the end of the SELECT statement.

Count (column name) returns a column with the total number of rows: Select count (*) |count (column name) from TableName [WHERE Where_definition]

The SUM function returns the sum of the rows that satisfy the WHERE condition: Select sum (column name) {, sum (column name) ... } from TableName [WHERE Where_definition]

The AVG function returns the average of a column that satisfies the WHERE condition: Select avg (column name) {, AVG (column name) ... } from TableName [WHERE Where_definition]

The Max/min function returns the maximum/minimum value of a column that satisfies the WHERE condition: Select max (column name) from TableName [WHERE Where_definition]

Use the GROUP BY clause to group columns: SELECT column1, Column2. Column3. From table GROUP By column

Constraints on tables

Define PRIMARY KEY constraint: Primary key (not allowed to be empty, not allowed to repeat)

Delete PRIMARY key: ALTER TABLE TableName drop PRIMARY key;

Define the primary key auto-grow: auto_increment

Defining UNIQUE constraints: Unique

Define a non-null constraint: notnull

Define FOREIGN KEY constraints:constraint ORDERSID_FK foreign key (ORDERSID) references orders (ID),

MySQL Character set

There are six uses of the character set in MySQL: Client, connection, database, results, server, System.
Client is the character set used by clients.
Connection is the character set type of the connection database, if the program does not indicate the character set type used by the connection database, follow the server-side default character set.
Database is a set of character sets used by a library in the databases server, and if not specified when the library is built, the character set specified when the server is installed is used.
Results is the character set that is used when the database returns to the client, and if not specified, the default character set of the server is used.
Server is the default character set that is specified when the server is installed.
System is the character set set used by the database system.

Getting Started with SQL