MySQL operation statement

Operational database

Statement classification

  • DDL definition library and table

    • create ,drop,alter

  • DML update data

    • insert ,update,delete

  • DQL query

    • select

  • DCL control

    • grant,deny,revoke

  • TCL transaction

    • commit,rollback

DDL example

Create database: create database [if not exists] database name;

Delete database: drop database [if exists] database name;

View databases: show databases;

Use database: use database name;

Create table

create table [if not exists] `Table name`(
  'Field name 1' Column type [attribute][index][annotation],
  'Field name 2' Column type [attribute][index][annotation],
 #...
  'Field name n' Column type [attribute][index][annotation]
)[Table type][Table character set][annotation];

Note: backquotes are used to distinguish MySQL reserved words from ordinary characters (the key under esc on the keyboard)

DML example

Manage database data through DML statements

Add data

INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1',' value 2',' value 3',...)

Note:

  • Fields or values are separated by commas
  • 'field 1, field 2...' This part can be omitted, but the added value must correspond to the table structure, data column and order, and the quantity must be consistent
  • Multiple pieces of data can be inserted at the same time. values are separated by English commas

Modify data

UPDATE table name SET column_name=value [,column_name2=value2,...] [WHERE condition];

Note:

  • column_name is the data column to be changed
  • value is the modified data, which can be variables, specifically expressions or nested SELECT results
  • Condition is the filter condition. If it is not specified, all column data of the table will be modified

Delete data

DELETE FROM table name [WHERE condition];

The above method does not reset the auto increment counter

TRUNCATE [TABLE] table name;

truncate truncates, which is equivalent to deleting a table and rebuilding it. The auto increment counter is reset.


DQL query

Syntax:

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
 [left | right | inner join table_name2]  -- Joint query
 [WHERE ...]  -- Specify the conditions to be met for the results
 [GROUP BY ...]  -- Specify which fields the results are grouped by
 [HAVING]  -- Secondary conditions that must be met to filter grouped records
 [ORDER BY ...]  -- Specifies that query records are sorted by one or more criteria
 [LIMIT {[offset,]row_count | row_countOFFSET offset}];
  -- Specify which records to query from

Description:

  • distinct de duplication

  • As statement can be used as alias

    • Used for data column, table name and calculation result

Specify query fields

-- Query all student information
SELECT * FROM student;
-- Query specified column(Student ID , name)
SELECT studentno,studentname FROM student;

where conditional statement

Function: used to retrieve qualified records in a data table
Search criteria can be composed of one or more logical expressions, and the results are generally true or false

-- Query the test score at 95-100 Between
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND It can also be written as &&

Fuzzy query

Queries containing between and, like, in, null

-- Query the student ID and name of students surnamed Liu
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu%';

-- Query for names containing Jiazi
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%Jia%';

-- The inquiry student number is 1000,1001,1002 Student name of
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- Query students whose birth date is not filled in
-- Cannot write directly=NULL , This is a mistake , use is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- Note empty string'' and null Inequivalence

join query

Operator name describe
INNER JOIN If there is at least one match in the table, the row is returned
LEFT JOIN Returns all rows from the left table even if there is no match in the right table
RIGHT JOIN Returns all rows from the right table even if there is no match in the left table

Left connection, all data in the left table are reserved; Right join to keep all data in the right table.

3-mode extended 7-mode

-- Internal connection
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- Equivalent connection
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- Check the absent students(Left connection scenario)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- The left link retains all student information. If the screening result is blank, you will be the absent student

-- Self connection, data table is connected with itself
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','information technology'),
('3','1','software development'),
('4','3','database'),
('5','1','Art design'),
('6','3','web develop'),
('7','5','ps technology'),
('8','2','Office information');
SELECT a.categoryName AS 'Parent column',b.categoryName AS 'Sub column'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

Subquery

Dolly: another query statement is nested in the WHERE condition clause of the query statement,

The results returned by subqueries are generally collections, so it is recommended to use the IN keyword;

-- Inquiry course is advanced mathematics-2 Student number and name of students whose scores are not less than 80
SELECT studentno,studentname FROM student WHERE studentno IN(
  SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
    SELECT subjectno FROM `subject` WHERE subjectname = 'higher mathematics-2'
 )
)

The most difficult is the related sub query! Not explained at this stage

Sort paging

After the query results set, you can also perform some operations.

ORDER BY statement

  • Used to sort the result set by the specified column
  • Sort records in ASC ascending order by default
  • DESC keyword descending
-- Query database structure-1 All test results for(Student No. student name subject name grade)
-- Sort by grade in descending order
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1'
ORDER BY StudentResult DESC

LIMIT statement

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

  • Used to force the SELECT statement to return the specified number of records

  • Offset specifies the offset of the first returned record line. If it is not entered, the default value is 0

    • LIMIT n is equivalent to LIMIT 0,n.

  • Rows specifies the maximum number of returned record rows

-- query JAVA Information of top 10 students in the first academic year(Student ID,name,Course name,fraction)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA First academic year'
ORDER BY StudentResult DESC
LIMIT 0,10 -- LIMIT 10

DCL instance

Permission control

authorization

GRANT permission list ON table name TO user name

revoke

REVOKE permission list ON table name FROM user name

user management 

/* User and rights management */ ------------------
User information table: mysql.user
-- Refresh permissions
FLUSH PRIVILEGES
-- Add user CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER user name IDENTIFIED BY [PASSWORD] password(character string)
 - Must have mysql Global of database CREATE USER Permission, or ownership INSERT Permissions.
 - Users can only be created and cannot be granted permissions.
 - User name. Note the quotation marks: for example 'user_name'@'192.168.1.1'
 - Passwords also need quotation marks, and pure digital passwords also need quotation marks
 - To specify a password in plain text, ignore PASSWORD Keywords. To specify a password as PASSWORD()Function returned
 Mixed value, including keywords PASSWORD
-- Rename User  RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- Set password
SET PASSWORD = PASSWORD('password')   -- Set password for current user
SET PASSWORD FOR user name = PASSWORD('password')   -- Set password for specified user
-- delete user DROP USER kuangshen2
DROP USER user name
-- Assign permissions/Add user
GRANT Permission list ON Table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
 - all privileges Indicates all permissions
 - *.* All tables representing all libraries
 - Library name.The table name represents a table under a library
-- View permissions  SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR user name
  -- View current user permissions
 SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR
CURRENT_USER();
-- revoking permission
REVOKE Permission list ON Table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name   -- Revoke all permissions

TCL transaction

What is a transaction

  • Transaction is to execute a group of SQL statements in the same batch
  • If an SQL statement fails, all SQL statements in the batch will be canceled
  • MySQL transaction processing only supports InnoDB and BDB data table types

Follow ACID principles

  1. Atomicity

    • Either all or none

  2. Consistency

    • Final consistency, total conservation

  3. Persistent

    • Uncommitted, rollback
    • Submitted, saved to database
    • Once submitted, it is irreversible!

  4. Isolated

    • When multiple users access the database concurrently, start transactions for each user. Transactions that cannot be disturbed by other transactions should be isolated from each other

    • Isolation level

      • Dirty read: one transaction reads uncommitted data from another transaction
      • Non repeatable reading: in a transaction, the same data is read repeatedly, and the results are inconsistent
      • Virtual read: read the data inserted by another transaction in one transaction

Transaction implementation

-- apply set Statement to change the auto commit mode
SET autocommit = 0;  /*close*/
SET autocommit = 1;  /*open*/
-- take care:
--- 1.MySQL Default is auto submit in
--- 2.Auto commit should be turned off before using transactions
-- Start a transaction,Mark the starting point of the transaction
START TRANSACTION 
-- Commit a transaction to the database
COMMIT
-- Rollback transaction,Data returns to the initial state of this transaction
ROLLBACK
-- reduction MySQL Automatic submission of database
SET autocommit =1;
-- Save point
SAVEPOINT Save point name -- Set a transaction savepoint
ROLLBACK TO SAVEPOINT Save point name -- Rollback to savepoint
RELEASE SAVEPOINT Save point name -- Delete savepoint

Tags: Database

Posted by baitubai on Wed, 01 Jun 2022 03:24:51 +0530

Hot Tags

©2023 Programs Team