Shulou(Shulou.com)06/01 Report--

I. Overview of SQL structured query language

SQL is the standard language used in relational databases, and the initial implementation based on IBM was approved in 1986. In 1987, the International Organization for Standardization (ISO) took ANSI (American National Organization for Standardization) SQL as an international standard.

1. SQL language specification

In database systems, SQL statement keywords are not case-sensitive (uppercase is recommended)

The resource objects of the database are case-sensitive. Resources such as tables and databases are stored as independent files in the operating system. If the file system names files are case-sensitive, the elements within the resource object resources referenced in the SQL language are not case-sensitive. For example, the field names of tables are not case-sensitive, because they are not independent files in the operating system.

SQL statements can be written on a single line or multiple lines, ending with ";".

Keywords cannot span multiple lines or abbreviations.

Use spaces and indentation to improve the readability of the statement (but not mandatory).

Clauses are usually on separate lines, making it easy to edit and improve readability ().

two。 Note:

Comment type comment symbol description standard single-line comment-there is a space between the comment content and the SQL standard multiline comment / * multiline content is / * wrapped SQL standard single-line comment # multiline content is # wrapped MYSQL standard

3.SQL language classification

DDL: Data Defination Language data definition language

CREATE,DROP,ALTER > DML: Data Manipulation Language data manipulation language

INSERT,DELETE,UPDATE > DQL:Data Query Language data query language

SELECT > DCL:Data Control Language data control language

GRANT,REVOKE,COMMIT,ROLLBACK

4.SQL language help

Log in to help in SQL SEHLL and add a keyword to view help.

Example: mysql > HELP KEYWORD

Log in to SQL SEHLL and enter help contents; to view help by class.

Example: view help by class

MariaDB [hellodb] > help contents

You asked for help about help category: "Contents"

For more information, type 'help', where is one of the following

Categories:

Account Management

Administration

Compound Statements

Data Definition

Data Manipulation

Data Types

Functions

Functions and Modifiers for Use with GROUP BY

Geographic Features

Help Metadata

Language Structure

Plugins

Procedures

Table Maintenance

Transactions

User-Defined Functions

Utility

5. Database objects and naming

> components (objects) of the database:

Database, table, index, view, user, stored procedure, function, trigger, event scheduler and other resource objects basically have independent file storage in the operating system. > naming rules:

Must start with a letter and can include numbers and three special characters (# _ $)

Do not use the reserved word of MySQL

Objects under the same database (Schema) cannot have the same name

Database management operations:

Database management operations include: create, modify, delete, view information and other operations

1. View character set

Show character set

two。 View the database

SHOW DATABASES; looks at what databases there are, that is, a list of databases.

SHOW DATABASES like 'vmlab'; views the specified database.

SHOW CREATE DATABASE vmlab; looks at the process of creating a database, and you can quickly view the character set used by the database.

3. Create a database

CREATE DATABASE vmlab; creates a database with DBMS default settings

CREATE DATABASE microcisco CHARACTER SET "utf8mb4" COLLATE 'utf8mb4_bin'; specifies that the character set is "utf8mb4" the character set collation is "utf8mb4_bin"

4. Modify the database

ALTER DATABASE vmlab CHARCATER SET 'utf8mb4' COLLATE' utf8mb4_unicode_ci'

Change the vmlab database character set to: 'utf8mb4', and the sort of character set is:' utf8mb4_unicode_ci'.

Note:

Modifying the database character set is only valid for subsequent newly stored data, but not for existing data. Modification is not recommended in production unless it is necessary.

Set the default character set of the server. You can specify the character set character-set-server=utf8mb4 in the [mysqld] option section of the configuration file vim / etc/my.cnf, so that all databases created later will inherit the character set of the word DBMS.

5. Delete the database:

DROP DATABASE like 'microcisco'

6. Create a database based on the SQL script:

Mysql < hellodb_innodb.sql-uroot-ppassword

Table management operation

Note: be sure to specify which database to create before creating the table.

1. View table information:

DESC students; / / desc specify the table name to view the table structure

SHOW TABLES FROM mysql; / / see which tables are in the specified database.

SHOW COLUMNS FROM mysql.user; / / check which fields are in the specified table, and the execution result is the same as desc.

SHOW CREATE TABLE vmlab\ G; / / look at the command executed when the vmlab table was created, that is, how the table was created. You can refer to this method to create script files.

SHOW TABLE STATUS LIKE 'students'\ G; / / View table status information, which is commonly used to display character set, creation time, update time, table engine and other information.

SHOW TABLE STATUS FROM mysql\ G; / / View the status of all tables in the specified database.

two。 Create a tabl

Note that after the field modifier no NULL in the table is specified, the field must specify a value when adding a record. It is generally recommended that the first column ID be the primary key and grow automatically.

Method 1: directly create the specified field name, stored data type, modifier and other information in (). Different field definitions are separated by commas.

CREATE TABLE students (id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR (10) NOT NULL,age TINYINT UNSIGNED)

Method 2: by querying the creation of the existing table, the new table will be directly inserted into the data from the query. (note: this method replicates the table structure, but does not copy the field modifier information, and the data of the original table will be copied. )

CREATE TABLE vmlab SELECT * FROM students; extracts data from the students table to create a new table vmlab

Method 3: copy the table structure of the existing table, but not the data (note: this method replicates the table structure, but does not copy the data of the original table. )

CREATE TABLE vmlab LIKE students; refers to the existing table to create a new table vmlab.

3. Delete the table:

Drop table vmlab

4. Modify the table:

Table modifications are basically column-specific operations, production environments rarely need to be modified, and required fields mean that requirements change. All modification operations on the table need to use ALTER TABLE and then specify the table name, and then specify the operation type: add operation ADD, delete operation DROP, change operation CHANGE, add modification operation is equivalent to re-literal field, so you must specify the data type.

Table rename: ALTER TABLE vmlab RENAME vmlab1

Insert field: the insert field is actually a newly defined field, so you must specify a data type.

ALTER TABLE vmlab ADD phone CHAR (11) AFTER age; / / inserts the new column name phone after the age column of the vmlab1 table, and the data type is char with a length of 11 characters.

ALTER TABLE vmlab ADD ID int UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST; adds a column to the first column and defines it as the primary key.

ALTER TABLE vmlab ADD Gender ENUM ('ENUM ENUM'); / / if you do not specify a location, add a new field to the last column of the table.

Modify the field data type:

ALTER TABLE microcisco MODIFY phone int; / / modify the phone field data type of microcisco table to int type

Rename the field:

ALTER TABLE vmlab CHANGE COLUMN phone mobile char (11); / / rename the phone field in the vmlab table to mobile and change the data type to char type with a length of 11 characters. Note: field renaming must specify the field data type after renaming.

Delete a field:

ALTER TABLE vmlab DROP COLUMN phone; / / Delete the phone field of the vmlab table.

Modify the table character set:

ALTER TABLE vmlab CHARACTER SET utf8mb4; / / modify the character set of the vmlab table to utf8mb4.

Modify the field data type:

ALTER TABLE vmlab CHANGE name name VARCHAR (20)

Delete the primary key:

ALTER TABLE vmlab DROP PRIMARY KEY

Define the primary key:

ALTER TABLE vmlab CHANGE id id int UNSIGNED NOT NULL PRIMARY KEY; / / Note: defining a primary key is equivalent to redefining a field, so you must specify the field data type, especially consistent with the original data type.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.