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

The following mainly brings you MySQL commonly used SQL statements and use examples, I hope these contents can bring you practical use, which is also my main purpose of editing MySQL commonly used SQL statements and using examples of this article. All right, don't talk too much nonsense, let's just read the following.

Relational database design paradigm:

The first normal form: when each relational attribute of the relational schema R is an inseparable data item, then the schema in which R is the first normal form (1NF)

Simply put: fields are atomic

The second paradigm: if the party relation pattern R is 2NF, and all non-principal attributes in the relation pattern R (Urecom F) do not have transitive dependence on any candidate keywords, then relation R is said to belong to the third paradigm.

To put it simply: there are available primary keys

The third pattern: when the relational schema R is 2NF and all non-primary attributes in the relational schema R have no transitive dependence on any candidate keywords, the relational R is said to belong to the third-party normal form.

To put it simply: no table should have fields that depend on other tables that are not primary keys

Database components:

Database, table, index, view, user, permission

Components programmatically contacted: (triggers, stored procedures, stored functions, event schedulers)

Security configuration after mysql installation is complete (mysql_secure_installation: one-click configuration script)

1. Delete mysql anonymous users

2. Set the password for all root users

3. Turn off the host name anti-resolution function

SQL statement type:

DDL: database definition language

Create,drop,alter

DML: database operation language

Insert,delete,update,select

DCL: data control language

Grant,revoke

Commonly used SQL statements:

Create:

CREATE DATABASE # create a database

CREATE DATABASE CHARACTER SET charset_name db_name; # create the database and set the character encoding

CREATE TABLE table_name (field name, field data type, constraint)

# create a table

Data type:

* × ×: int

Tinyint # 1byte

Smallint # 2byte

Mediumint # 3byte

Int # 4byte

Bigint # 8byte

Character type:

Char # fixed-length characters (case-insensitive)

Varchar # variable length characters (case-insensitive)

Binary # fixed length characters (case sensitive)

Varbinary # variable length characters (case sensitive)

Constraints:

NOT NULL # does not allow null

DEFAYULT # default value

PRIMARY KEY # primary key

UNIQUE # unique key

Unsigned # unsigned (for int type)

Auto_increment # is self-increasing and needs to be defined in a key (for int types)

CREATE INDEX

# create an index. An index is a special data structure used for search acceleration. It generally defines the fields that are used as search criteria when searching.

Example: create table test (id int primary key auto_increment, name varchar (50))

Mycli: a more powerful mysql client, developed using python

# mysql client tools

Examples of use:

Show global variables

# check the global parameters, some can be modified and take effect immediately, and some need to modify the configuration file

Current session parameters for show session variables; # mysql

Show character set; # View supported character sets

Show collation; # View supported collation

Show engines; # View supported storage engines

Show table status like 'user'\ G # View table status

Show global variables like'% server%'; # database id

Show master logs; # View binary logs

Show master status; # View the status of the primary CVM

Show grants for 'dj'@'localhost'; # View di user authorization information

Show index from mysql.user; # View Index

Show databases; # View the database

Show tables; # View the tables in the database

Desc table_name # View table structure

Select field name from table name [where query criteria] # View the contents of the table

Select * from user\ G; # View the details of the user

Select database (); # View the default database

Select * from test where id > 2 and id

=

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.