Hello, today for you kids to share is MySQL, quickly take out a small notebook to write down
First, SQL language
1, an overview of the
SQL: Structure Query Language (STRUCTURED Query Language), SQL is determined by the American National Bureau of Standards (ANSI) as the key
The American standard for systematic database languages, later adopted by the International Standards Organization (ISO) as the international standard for relational database languages.
All database vendors support ISO SQL standard, Mandarin
Each database vendor has made its own extensions to the standard, dialects
SQL is a standardized language that allows you to perform operations on a database, such as creating items, querying content, updating content,
And delete entries and other operations.
Create, Read, Update, and Delete are commonly referred to as CRUD operations.
2, SQL statement classification
Data Definition Language (DDL) : a Data Definition Language used to define database objects such as libraries, tables, and columns.
Data Manipulation Language (DML) : a Data Manipulation Language used to define additions, deletions, and changes to database records.
Data Control Language (DCL) : Data Control Language used to define access permissions and security levels.
Data Query Language (DQL) : Data Query Language used to Query records (Data).
Note: The SQL statement starts with; At the end
Keywords in mysql are case insensitive
3, DDL operation database
create
The CREATE DATABASE statement is used to CREATE a new DATABASE:
Encoding mode: GB2312, UTF-8, GBK, ISO-8859-1
show databases;
View the definition information for the MyDB2 database you created earlier
3 Modifying the Database
Alter DATABASE Database name character Set Encoding mode
4 Deleting a database Drop Database Specifies the database name
DROP DATABASE mydb3;
5 Other statements View the database in use
Select database();
Switch a database: use the database name
USE mydb2;
4. DDL operation table
The CREATE TABLE statement is used to CREATE new tables.
Grammar:
[constraint] means optional
Other table operations
Drop table name; drop table name;
All tables in the current database: show tables;
Table name desc;
Alter table table_name add new column name new data type;
Alter table table name change old column name new column name new data type
Alter table old table rename new table name;
To view the table creation details: show create table name;
5. DML operation
DML adds, deletes, and modifies data in a table. Not to be confused with DDL.
The operations include INSERT, UPDATE, and DELETE
Little knowledge:
In mysql, both string and date types are enclosed in single quotes.
Null: null
INSERT operation: INSERT into values;
– Note :1 Use commas (,) to separate multiple columns from column values. 2. Column names must correspond to column values
– Single quotation marks must be placed around non-numeric column values
Common error: Data too long for column ‘stusex’ at row 1
– Omit column names when adding data -> When adding data to all columns
– The sequence of column values is the same as that of columns in the data table
Insert into values(first row),(second row),(),();
– Note that the column name corresponds to the type, number, and sequence of the column values.
Parameter values do not exceed the length defined by the column.
Use NULL if null values are inserted
Insert dates are enclosed in quotation marks, as are characters.
UPDATE operation: UPDATE:
UPDATE table_name SET table_name 1= table_name 1, table_name 2= table_name 2… WHERE column name = value
DELETE operation: DELETE:
DELETE from table WHERE table name = value
DELETE Deletes data from a table while the table structure remains. The deleted data can be retrieved
TRUNCATE delete is to DROP a table and create a new table.
Deleted data cannot be retrieved. The execution speed is faster than DELETE.
6, DCL
Create user: create user username @specified IP identified by password;
Create user @client IP identified by password;
User authorization:
7. DQL data query
DQL Data query language (Important)
The database does not make changes to the data by executing DQL statements, but instead has the database send the result set to the client.
The result set returned by the query is a virtual table.
Query keyword: SELECT
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM))
SELECT * FROM table name;
SELECT column name, column name, column name FROM table name;
Conditions of the query
A conditional query is a query that gives a WHERE clause. The following operators and keywords can be used in the WHERE clause:
=,! =, <>, <, <=, >, >=; BETWEEN… The AND. IN (set); IS NULL. The AND. The OR. The NOT;
Fuzzy queries are used when you want to query students whose names contain the letter A. Fuzzy query requires the keyword LIKE.
Syntax: Column names like ‘expression’ // The expression must be a string
Wild card:
_(underscore): any character
% : Any 0 to N characters, ‘zhang %’
Field controlled query
(1) Remove repeated records
Remove duplicate records (two or more rows of records have the same data on the series), such as the same SAL field in the EMP table
Record. When only the SAL field of the EMP table is queried, duplicate records will appear
SELECT a sal FROM emp;
(2) Check the sum of the employee’s monthly salary and commission
Because both the sal and Comm columns are numeric, you can add. If there is a field in sal or comm no
Is a numeric type, then an error will occur.
Comm column has many records with a value of NULL, because anything added to NULL will result in NULL, so the settlement result may be given
Is NULL. The function IFNULL is used to convert NULL to the value 0:
SELECT *,sal+IFNULL(comm,0) FROM emp;
Sal +IFNULL(comm,0); sal+IFNULL(comm,0)
Total: SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
Note: As can be omitted
Order by column name asC /desc// asC ascending desc Descending order by ascending desc descending order by column name asC descending order desc descending order by column name asC descending order desc descending order by column name asC descending order desc descending order by column name asC descending order desc descending order
Order by asC /desc
Aggregation function
Aggregate functions are functions used to perform vertical operations:
COUNT(column name) : COUNT the number of rows for which the specified column is not NULL.
MAX(column name) : Computes the maximum value of the specified column, using string sort if the specified column is a string;
MIN(column name) : Evaluates the minimum value of the specified column, using string sort if the specified column is a string;
SUM(column name) : Evaluates the numeric SUM of the specified column. If the specified column type is not numeric, the result is 0.
AVG(column name) : Evaluates the average value of the specified column. If the specified column type is not numeric, the result is 0.
(1) COUNT
Use COUNT() when vertical statistics are needed.
Run the following command to query the number of records in the EMP table: SELECT COUNT(*) AS CNT FROM emp;
SELECT COUNT(comm) CNT FROM emp;
Note that because the count() function gives the COMM column, only non-null rows in the COMM column are counted.
(2) the SUM and AVG
Use the sum() function when vertical summation is required.
SELECT SUM(sal) FROM emp;
SELECT sal FROM emp where sal = null and sal = null;
(3) the MAX and MIN
SELECT MAX(sal), MIN(sal) FROM emp;
Grouping query
The GROUP BY clause is used when you need to GROUP queries, such as salary and salary for each department, indicating that parts are used for grouping.
Note: If there are groups in the query, only aggregate functions and grouped column names can be added after select
Query the department number for each department and the salary for each department:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
SQL > select * from dept. HAVING total salary > 9000 and dept. HAVING total salary > 9000
1. Having is used to filter data after grouping. Where is used to filter data before grouping
2. Having can be used with grouping functions (statistical functions)
You cannot use grouping functions after where.
WHERE is a condition for pre-grouping records, and if a row does not meet the conditions of the WHERE clause, it will not participate in the score
Group; HAVING is a constraint on grouped data.
Add: multi-column grouping
Figure out how many boys and girls are in each class in the STU table
select gradename,gender ,count(*) from stu
group by gradename,gender
LIMIT
LIMIT specifies the start row and the total number of rows in the query result.
Limit indicates the number of entries. // Start subscript starts at 0
Limit Displays the number of entries. // Data is obtained from 0 by default
1. Query five rows starting from 0
SELECT * FROM emp LIMIT 0, 5;
Notice that the start line starts at 0, the first line! 2. Query 10 rows, starting from 3
SELECT* FROM emp LIMIT 3, 10;
If there are 10 records on one page, how do I check the records on page 3?
L The first page records the initial behavior 0, and a total of 10 rows are queried. Limit of 0, 10
L The second page records the initial behavior 10, a total of 10 lines; Limit of 10, 10
L The third page records the initial behavior 20, with a total of 10 lines; Limit of 20, 10
PageIndex Page number pageSize Number of pages displayed on a page
limit (pageindex-1)*pagesize,pagesize;
The order in which the query statements are written is as follows: select – from-where-groupby-having – order by-limit The order in which the query statements are executed: from – where -group by -having – select – order by-limit
Well, this is the end of today’s article, I hope to help you confused in front of the screen