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

The rise of SQL language and its grammatical Standard

In the 1970s, IBM developed SQL for DB2

In 1981, IBM launched the SQL/DS database.

Microsoft, the industry standard, and Sybase's Tmuri SQL, Oracle's PL/SQL

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

SQL:ANSI SQL

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL language specification

In database systems, SQL statements are not case sensitive (uppercase is recommended)

But string constants are case sensitive

SQL statements can be written on one or more lines, ending with ";"

Keywords cannot span multiple lines or abbreviations

Use spaces and indentation to improve the readability of sentences

Clauses are usually located on separate lines, which are easy to edit and improve readability

Note:

SQL standard:

/ * comment content * / Multi-line comment

-- comments one-line comments, pay attention to the spaces

MySQL comments:

In addition to the SQL standard, you can also use

Database object

Components (objects) of the database:

Databases, tables, indexes, views, users, stored procedures, functions, triggers, event schedulers, etc.

Naming rules:

Must start with a letter

Can include numbers and three special characters (# _ $, # and $try not to use)

Do not use the reserved word of MySQL

Objects under the same database (Schema) cannot have the same name, even if they are of different types

SQL statement classification

SQL statement classification:

DDL: Data Defination Language

CREATE, DROP, ALTER

DML: Data Manipulation Language

INSERT, DELETE, UPDATE

DCL:Data Control Language

GRANT, REVOKE

DQL:Data Query Language

SELECT

SQL statement composition

The SQL statement consists of:

Keyword forms a clause (clause)

Multiple clause constituent statements

Example:

SELECT * SELECT clause

FROM products FROM clause

WHERE price > 400 WHERE clause

Description: this is a set of SQL statements made up of three clauses. SELECT,FROM and WHERE are keywords.

Database operation

Create the database:

CREATE DATABASE | SCHEMA [IF NOT EXISTS] 'DB_NAME'; # create a database

CHARACTER SET 'character set name' # specifies the character set

COLLATE 'collate name' # specifies the sort method

Delete database

DROP DATABASE | SCHEMA [IF EXISTS] 'DB_NAME'

View all character sets supported: SHOW CHARACTER SET

View all supported collations: SHOW COLLATION

Get help for using the command:

Mysql > HELP KEYWORD

Example: HELP CREATE DATABASE

To see links to basic usage and help documentation, you can go to

URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html

View the list of databases:

Mysql > SHOW DATABASES

[root@centos7 ~] # cat / var/lib/mysql/db1/db.opt

Default-character-set=latin1

Default-collation=latin1_swedish_ci

Table

Tables: 2D relationship

Design table: follow the specification

Definitions: fields, index

Fields: field name, field data type, modifier

Constraints, indexes: should be created on fields that are often used as query criteria

Create a tabl

Create a table: CREATE TABLE

(1) create directly

(2) create by querying existing tables; the new table will be inserted directly into the data from the query.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options]

[partition_options] select_statement

(3) create by copying the table structure of an existing table, but not replicating data

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name {LIKE old_tbl_name | (LIKE old_tbl_name)}

Note:

Storage Engine refers to the table type, that is, the storage engine it uses when the table is created. Different tables in the same library can use different storage engines.

Tables in the same library are recommended to use the same storage engine type

Create a tabl

The CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 modifier, col2 type2 modifier,...) # [IF NOT EXISTS] field is usually added to the script.

Field information

Col type1

PRIMARY KEY (col1,...)

INDEX (col1,...)

UNIQUE KEY (col1,...)

Table options:

ENGINE [=] engine_name

SHOW ENGINES; to view supported engine types

ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}

Get help: mysql > HELP CREATE TABLE

Table operation

Check out all the engines: SHOW ENGINES

View table: SHOW TABLES [FROM db_name]

View the table structure: DESC [db_name.] tb_name

Delete table: DROP TABLE [IF EXISTS] tb_name

View table creation command: SHOW CREATE TABLE tbl_name

View table status: SHOW TABLE STATUS LIKE 'tbl_name'

View the status of all tables in the library: SHOW TABLE STATUS FROM db_name

Data type

Data type:

What does the data look like?

How much space does the data need to store?

System built-in data types and user-defined data types

MySql supports multiple column types:

Numerical type

Date / time type

String (character) type

Https://dev.mysql.com/doc/refman/5.5/en/data-types.html

Choosing the right data type is critical to achieving high performance. there are three principles:

Smaller ones are usually better, try to use the smallest data type that can store data correctly

As long as it's simple, operations with simple data types usually require less CPU cycles.

Try to avoid NULL, including columns with NULL, and it is more difficult to optimize for MySQL (you can fill in a default value)

Data type

Data type

1. Integer

Tinyint (m) 1 byte range (- 128 to 127)

Smallint (m) 2 byte range (- 32768mm 32767)

Mediumint (m) 3 byte range (- 8388608 / 8388607)

Int (m) 4 byte range (- 214748364802147483647)

Bigint (m) 8 byte range (+-9.22 to the power of 18)

If unsigned is added, the maximum value doubles. For example, the value range of tinyint unsigned is (0,255).

The m in int (m) represents the display width of the SELECT query result set, does not affect the actual range of values, and specifies the number of characters used by some interactive tools of MySQL (such as MySQL command line client). Int (1) and Int (20) are the same for storage and computing

BOOL,BOOLEAN: Boolean, synonymous with TINYINT (1). The Zero value is considered false. Non-zero values are regarded as true

2. Floating point type (float and double), approximate value

Float (m) single precision floating point 8-bit precision (4 bytes) m total, d decimal places

Double (m) double precision floating point 16-bit precision (8 bytes) m total, d decimal places

Let a field be defined as float (6p3). If you insert a number of 123.45678, the actual number stored in the database is 123.457, but the total number still depends on the actual number, that is, 6 digits.

3. Fixed point number

The exact value is stored in the database as decimal

Decimal (m from students order by d) parameters masking, select * from students order by-score desc; order, but NULL comes last.

LIMIT [[offset,] row_count]: limits the number of output rows on the results of a query

Impose a "lock" on data requests in the query results

FOR UPDATE: write lock, exclusive or exclusive lock, with only one read and write

LOCK IN SHARE MODE: read lock, shared lock, multiple reads at the same time (but only one write)

Example

DESC students;INSERT INTO students VALUES (1), (2) INSERT INTO students (id,name) VALUES (3), (4) SELECT * FROM students WHERE id

< 3;SELECT * FROM students WHERE gender='m';SELECT * FROM students WHERE gender IS NULL;SELECT * FROM students WHERE gender IS NOT NULL;SELECT * FROM students ORDER BY name DESC LIMIT 2;SELECT * FROM students ORDER BY name DESC LIMIT 1,2;SELECT * FROM students WHERE id >

= 2 and id use hellodb # using the hellodb database MariaDB [hellodb] > show tables; # there are 7 tables

+-+

| | Tables_in_hellodb |

+-+

| | classes |

| | coc |

| | courses |

| | scores |

| | students |

| | teachers |

| | toc |

+-+

M

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.