Shulou(Shulou.com)05/31 Report--

This article is to share with you what the essential basics of MySQL are. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. the concept of SQL

Structured query language (Structured Query Language) referred to as SQL, is a special purpose programming language, is a database query and programming language, used to access data and query, update and manage relational database systems.

II. SQL classification

(1) data definition language (Data Definition Language) DDL

Used to define database objects: databases, tables, columns.

(2) data manipulation language (Data Manipulation Language) DML

Used to update the records of tables in the database.

(3) data query language (Data Query Language) DQL

Used to query the records of tables in the database.

(4) data Control language (Data Control Language) DCL

(1) DDL

1. Create database create database database name; create database database name character set character set; / / character set is generally uft-8, the second statement is to create data at the same time and set the character set. two。 View the database use database name; / / switch the database show databases; / / to see which databases are in MySQL select database (); / / view the database show create database database name currently in use; / / view the definition information of a database 3. Modify database character set alter database database name character set character set; 4. Delete database drop database database name; 5. Create table CREATE TABLE table name (field name 1 field type (length), field name 2 field type (length) / / Note: the last field name is not added at the end,) / * commonly used data type int integer double floating point varchar string data date type yyyy-MM-dd year-month-day Note: char also represents a string, but it is different from varchar char and varchar: the char type is fixed length, and varchar allocates appropriate space according to input characters Usually use varchar * / 6. View table show tables; / / view all the desc table names in the current database; / / view the structure of the table 7. Modify table rename table old table name to new table name; / modify table name alter table table name character set character set; / modify character set alert table table name add field name field type; / / add field alter table table name drop field name to table; / / delete field 8. Delete table drop table name if exists table name; / / delete the table

(2) DML

1. Insert data insert into table name (field name 1, field name 2...) values (field value 1, field value 2); 2. Change the data update table name set column name = value [where field name = value]; 3. Delete data delete from table name [where field name = value]

(3) DQL

1. Simple query select column name from table name; / / column name can be replaced with a * sign to indicate that all fields of the query 2. Conditional query select column name from table name where conditional expression; / /% indicates any number of strings, _ indicates matching one character, 3. Sort SELECT field name FROM table name [WHERE field = value] ORDER BY field name [ASC / DESC]; / / ASC indicates ascending sort (default), and DESC indicates descending sort 4. Aggregate function / * commonly used aggregate function * count (field) Statistics the number of rows of records that the specified column is not NULL * sum (field) calculates the numerical value of the specified column and * max (field) calculates the maximum value of the specified column * min (field) calculates the minimum value of the specified column * avg (field) calculates the average of the specified column * / SELECT aggregate function (field name) FROM table name 5. Grouping SELECT grouping field / aggregate function FROM table name GROUP BY grouping field [HAVING condition]; 6.limit keyword SELECT field 1, field 2. FROM table name LIMIT offset, length; / / offset starting number of rows, counting from 0, if omitted, the default is 0. / / number of rows returned by length

(4) DCL

1. Create user CREATE USER 'username' @ 'hostname' IDENTIFIED BY 'password'; 2. Give the user permission 1 for GRANT, permission 2... ON database name. Table name TO 'username' @ 'hostname'; 3. View permissions SHOW GRANTS FOR 'username' @ 'hostname'; 4. Delete user DROP USER 'username' @ 'hostname'; 5. Query user SELECT * FROM USER

III. SQL constraint

1. Primary key constraint / / non-repeatable unique non-empty field name field type primary key / / automatic growth of primary key AUTO_INCREMENT indicates automatic growth (field type must be an integer type) / / the default primary key starting value is 1, change the starting value / /-- create a self-incrementing table of the primary key, and customize and increase its real field name field type DEFAULT default value / / CREATE TABLE table name (/ / eid INT PRIMARY KEY AUTO_INCREMENT, / /... / /) the influence of AUTO_INCREMENT=100; / / DELETE and TRUNCATE on self-growth (the former has no effect on self-growth, while the latter increases from 1) 2. Non-empty constraint field name field type not null,3. Unique constraint field name field type unique,4. Default value Field name Field Type DEFAULT default

IV. Transaction control

1. What is a transaction as a whole, which consists of one or more SQL statements, all of which either execute successfully or fail? as long as an exception occurs in a SQL, the whole operation will be rolled back and the whole business will fail. two。 Manually commit transaction ① open transaction start transaction; ② commit transaction commit; ③ rollback transaction rollback;3. Auto commit transaction (MySQL default) / cancel auto commit SET @ @ autocommit=off; / / check the automatic status. On is automatic, and off is the four major features of manually committed SHOW VARIABLES LIKE 'autocommit';4. * * (key points) atomicity, consistency, isolation, and persistence. 5. Transaction isolation level / View isolation level select @ @ tx_isolation; / version 5.7 / set isolation level set global transaction isolation level level name; / / read uncommitted read uncommitted / / read committed read committed / / repeatable read repeatable read / / serializable serialization

5. Multiple tables

(1) Overview

1. In the actual development, a single table can not meet the business needs, and there are likely to be a large number of redundant fields in the same field, which requires us to introduce multiple tables. two。 If a field of Table 1 corresponds to the primary key of Table 2, then this field of Table 1 is called the foreign key of Table 1, the table with the foreign key is the slave table, and the table corresponding to the foreign key becomes the master table. 4. Foreign key constraints: foreign key constraints can create a corresponding relationship between two tables, thus forming a constraint. 5. The foreign key refers to the field corresponding to the primary key of the master table in the slave table. 6. Syntax format for adding foreign key constraints [CONSTRAINT] [foreign key constraint name] FOREIGN KEY (foreign key field name) REFERENCES master table name (primary key field name); alter table slave table add [CONSTRAINT] [foreign key constraint name] FOREIGN KEY (foreign key field name) REFERENCES primary table name (primary key field name); / / everything in brackets can be omitted. Delete foreign key constraint syntax format alter table from the table drop foreign key foreign key constraint name; 8. Considerations for foreign key constraints: the foreign key type of the ① slave table must be the same as the primary key type of the primary table. When ② adds data, you should first add data from the master table, and then add data from the table. When ③ deletes data from the table, delete the data from the slave table first, and then delete the data from the master table. 9. When deleting data, the data in the slave table should be deleted first, and then the data in the master table should be deleted. Setting the cascade deletion change can delete the data in the master table directly, and at the same time, the data in the slave table will disappear. On delete cascade; / / adds a foreign key constraint followed by this sentence

(2) the relationship between multiple tables

One-to-many: create foreign keys on multiple tables

Many-to-many: create a third table with at least two fields (primary keys of two tables)

One-to-one: create foreign keys on any table

(3) Multi-table query

1. Inner join query SELECT field name FROM left table, right table WHERE connection condition; SELECT field name FROM left table [inner] JOIN right table ON connection condition; 2. Outer join query SELECT field name FROM coordinate LEFT [outer] JOIN right table ON join condition; SELECT field name FROM coordinate RIGHT [outer] JOIN right table ON join condition / / Inner join: inner join, only get the data of the intersection part of the two tables. / / left outer join: left join, based on the left table, query all the data of the left table, and the part / right outer join that intersects with the right table: right join, based on the right table, query all the data of the right table, and the part that intersects with the left table 3. Subquery SELECT query field FROM table WHERE field = (subquery); SELECT query field FROM (subquery) table alias WHERE condition; SELECT query field FROM table WHERE field IN (subquery); / / if the subquery finds a field (single column), it is used as a condition after where. / / if the subquery results in multiple fields (multiple columns), it will be used as a table (alias).

VI. Three paradigms of database

A rule of database design

Objective: to create a database with less redundancy and reasonable structure.

The first normal form (1NF) is the paradigm that meets the minimum requirements-the column is atomic and the column is inseparable.

The second normal form (2NF) further satisfies more specifications on the basis of satisfying the first paradigm-- a table can only describe one thing.

The third normal form (3NF), and so on.

7. MySQL index

The function of index: in order to improve the efficiency of index

* * Classification of common indexes: * * Primary key index (primary key)

Unique index (unique); General index (index)

(1) Primary key index

1. When creating, add the primary key index CREATE TABLE table name (field name type PRIMARY KEY, / / primary key index is the only index); 2. Add the primary key index ALTER TABLE table name ADD PRIMARY KEY (column name) to the existing table

(II) unique index

1. Add a unique index CREATE TABLE table name (UNIQUE [index name] (column name)) when creating; 2. Add a unique index CREATE UNIQUE INDEX index name on table name (column name) to the existing table; ALTER TABLE table name ADD UNIQUE (column name)

(III) General index

Create index index name on table name (column name [length]); ALTER TABLE table name ADD INDEX index name (column name)

(IV) deleting the index

ALTER TABLE table_name DROP INDEX index_name

VIII. MySQL view

/ / View is a non-existent table create view view name [column_list] as select statement extracted from a table according to different requirements; / / when querying through a view, you can thank you for reading the view as a table! This is the end of this article on "what are the essential basic knowledge of MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.