Common MySQL DDL, DML, and DCL languages (example)

Before introducing these SQL languages, list the common mysql Data Types and Data Type modifications for query reference. The length of a field of this type is indicated by a number.

Numeric type:

TINYINT 1, SMALLINT 2, MEDIUMINT 3, INT 4, BIGINT 8, DECIMAL, FLOAT 4, DOUBLE 8, BIT

String type

CHAR, VARCHAR, BINARY, VBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOG, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, EMUM, SET

Datetime type

Date, time, datetime, timestamp

Data restriction modification:

Not null, NULL, DEFAULT, AUTO_INCREMENT, UNSIGNED, primary key, unique key, FOREIGN KEY

Character set # ps: show character set displays all CHARACTER sets supported by the current database

COLLATION # ps: show collation show all supported sorting rules

The following are SQL DDL, DCL, and DML languages.

DDL

---- Data Definition Language database Definition Language
Such as create procedure.

Create a database

Create database [if not exists] DBNAME [character set 'Char _ name'] [COLLATE 'coll _ name']

Modify: ALTER Delete: DROP

Create a new table

Crtate table [if not exists] TBNAME (col_name col_definition ,...)

EXAMPLE:

mysql>CREATE TABLE students(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT UNSIGNED,Name CHAR(20) UNIQUE KEY NOT NULL,Age TINYINT UNSIGNED INDEX,Gender CHAR(1) NOT NULL) [ENGINE={MyISAM | InnoDB }];

You can also write it like this (the difference is that the primary key, unique key, and index are defined separately ):

mysql>CREATE TABLE students(Id INT NOT NULL AUTO_INCREMENT UNSIGNED,Name CHAR(20) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL,PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))

Create a new table after querying the data of a table (the Field definition will be lost and the data will be retained)

Create table tbname select...

EXAMPLE:

mysql>CREATE TABLE test SELECT * FROM students WHERE Id>5;

Create a new empty table as defined in the format of a table

Create table TBNAME1 LIKE TBNAME2

Modify Table:

Alter table tb_name MODIFY # MODIFY the field Definition CHANGE # You can modify the field name and field definition. ADD DROP

EXAMPLE:

Add fields to a table

mysql>ALTER TABLE students ADD (course VARCHAR(100),teacher CHAR(20));

Add unique key

mysql>ALTER TABLE students ADD UNIQUE KEY Name;

Modify Field:

MODIFY the course field to the Course field, and put it after the Name field. MODIFY the field to include the new field definition.) ps: MODIFY can only MODIFY the field definition.

mysql>ALTER TABLE students CHANGE course Course VARCHAR(100) [AFTER Name];

Rename table name

mysql>ALTER TABLE students RENAME TO stu;mysql>RENAME TABLE stu TO students;

Add a foreign key constraint

ALTER TABLE students ADD FOREIGN KEY foreign _cid (CID) REFERENCES course (CID);

Create an index

Create index index_name on table (col_name [(length)] [ASC | DESC]) [USING {BTREE | HASH}];

Delete Index

Drop index index_name on tbname;

View the table STATUS: show status like 'tbname ';

View the table index: show indexes from tbname;

DML

---- Data Manipulation Language
Such as insert, delete, update, select (insert, delete, modify, search)
Insert and modify data

# If each field has a value, you do not need to enter a segment name. Each group of values is separated by commas (,).

Mysql> insert into tb_name (col1, col2) VALUES ('string', NUM), ('string', NUM ); Mysql> insert into tb_name SET col1 = 'string', col2 = 'string '; Mysql> insert into tb_name (col1, col2, col3) SELECT ...;

EXAMPLE:

mysql>INSERT INTO students (Name,Gender,teacher) VALUE ('lujunyi','M','mage'),('wusong','M','zhuima');mysql>INSERT INTO students SET Name='lujunyi',Gender='M',tearcher='zhuima';

Update Data

mysql>UPDATE tb_name SET column=value WHERE column=value;mysql>UPDATE students SET Course='mysql' WHERE Name='lujunyi';

Replace data:

As with UPDATE, you only need to replace update with REPLACE.

Delete data

Mysql> delete from tb_name WHERE conditions;

mysql>DELETE FROM students WHERE Course='mysql';

Clear table: the counter is reset.

Mysql> TRUNCATE tb_name

Query data

Single Table query:

Mysql> SELECT [DISTINCT] column FROM tb_name where condition;

EXAMPLE:

# Basic projection Query

mysql>SELECT Name,teacher FROM students WHERE Name='wusong';

# Duplicate results are displayed only once

mysql>SELECT DISTINCT Gender FROM students;

# Combination conditions. You can use AND, OR, NOT, XOR to combine multiple conditions.

mysql>SELECT * FROM students WHERE Age>20 AND Gender='M';

# Use BETWEEN... AND... to filter data BETWEEN the ages of 20-25.

mysql>SELECT * FROM students WHERE Age BETWEEN 20 AND 25;

# Query data whose Name starts with Y. % indicates any character of any length, and _ indicates any single character.

mysql>SELECT * FROM student WHERE Name LIKE 'Y%';

# Use a regular expression to match a query. the keyword is RLINK or REGEXP.

mysql> SELECT * FROM students WHERE Name RLINK '^[MNY].*$';

# Use the IN keyword to restrict conditions to a list. Use the IS keyword to indicate whether the condition is null or is not null)

mysql>SELECT * FROM students WHERE Age IN (20,22,24);

# Sort the query results

mysql>SELECT * FROM students ORDER BY Name {ASC|DESC};

# Query result alias display

mysql>SELECT Name AS Stu_Name FROM students;

# LIMIT limits the number of query results. LIMIT 2 and 3 indicates that three data records are obtained after two data records are offset.

mysql>SELECT * FROM students LIMIT 2;

# Average: AVG (), maximum: MAX () MIN () Quantity: COUNT () SUM: SUM ()

mysql>SELECT AVG(age) FROM students;

# GROUP

mysql>SELECT Age, Gender FROM students GROUP BY Gender;

# Alias:

mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age;

# Filtering: HAVING

mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age HAVING Num>2;

Multi-Table query:

# Specify which field is connected to two tables

mysql>SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;

# Specifying aliases during connection

mysql>SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;

# LEFT Outer JOIN... left join... ON...

mysql>SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;

# RIGHT Outer JOIN... ON...

mysql>SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;

Subquery

# Query data older than the average age

mysql>SELECT * FROM students WHERE Age > (SELECT AVG(Age) FROM students);

# Use subquery in FROM

mysql>SELECT Name,Age FROM (SELECT * FROM students WHERE CID IN (2,3)) AS t WHERE Age>20;

# Joint Query

mysql>(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);

Create View

Create view VIEW_NAME as select ....

DCL

---- Data Control Language Database Control Language
For example, grant, deny, and revoke. Only the Administrator has such permissions.

Create user

Mysql> create user 'username' @ 'host' identified by 'Password'

Delete a user

Mysql> drop user 'username' @ 'hoshost supports wildcards _: Any single character %: Any number of characters

Authorization

Mysql> GRANT pri1, pri2. .. ON DB_NAME.TB_NAME TO 'username' @ 'host' [identified by 'Password']

Cancel authorization

Mysql> REVOKE pri1, pri2. .. ON DB_NAME.TB_NAME FROM 'username' @ 'host ';

View authorization

Mysql> show grants for 'username' @ 'host ';

EXAMPLE:

mysql>CREATE USER 'lujunyi'@'%' IDENTIFIED BY '123456';mysql>SHOW GRANTS FOR 'lujunyi'@'%';mysql>GRANT ALL PRIVILEGES ON testdb.* TO 'lujunyi'@'%';

This article from the "lustlost-lost in desire" blog, please be sure to keep this source http://lustlost.blog.51cto.com/2600869/1224775