1. DDL operates databases and tables

Overview of DDL:

DDL manages and defines objects and properties that operate on objects, including the database itself, as well as database objects, such as tables, views, and so on, as represented by Create, Drop, and Alter.

1.1. Create databases and tables

== Operation database ==

Create a database, check that it does not exist, create it again, and specify a character set

  • Create DATABASE if NOT exists Character set name of the database;
  • Example: create database if not exists db4 character set GBK; == Operation table ==

Create a table

Create table table name (column name 1 data type 1, column name 2 Data type 2,...) ;Copy the code

1.2, Query database and table

== Operation database ==

Query all databases:

  • show databases;

Query a database creation statement:

  • Show create database Database name;

== Operation table ==

Query all table names in a database:

  • show tables;

Query table structure:

  • Desc table name;

1.3. Modify databases and tables

== Operation database ==

Alter database character set

  • Alter DATABASE alter database name character set name;

== Operation table ==

Alter table name:

  • Alter table name rename to new table name;

Alter table character set;

  • Alter table table_name character set character set name;

Add a column:

  • Alter table name add table data type;

Modify the column name and type

  • Alter table table_name change column name new column name new data type;
  • Alter table table name modify column name New data type;

Delete the column

  • Alter table table_name drop table_name;

1.4. Delete databases and tables

== Operation database ==

Check whether the database exists, then delete:

  • Drop database if EXISTS Indicates the database name.

== Operation table ==

Select * from table where table exists;

  • Drop table if exists Specifies the name of the table.

2, query the records in the table

2.1. Basic Grammar

Select columns from tables WHERE condition list group by having group columns ORDER by sort LIMIT Paging limitCopy the code

2.2 basic query

Multiple field query

  • Select field 1, field 2,.. From the name of the table;

To remove repetition:

  • Select distinct column name;

Computed columns

  • In general, you can use four operations to compute the values of some columns

  • Ifnull (expression 1, expression 2): the calculation that null participates in is null

  • Expression 1: Which field needs to be checked for null

  • Expression 2: This field is the replacement value after null

names

  • As: As can also be omitted (Spaces will do)

2.3. Conditional query

The WHERE clause is followed by a condition

The operator

  • ==>, <, <=, >=, =, <>=

  • Between… and

  • In (set)

  • Like: fuzzy query

  • A placeholder

  • _: a single arbitrary character

  • %: multiple arbitrary characters

  • is null

  • And or &&

  • The or or | |

  • Or not!

2.4 group query

  • Syntax: group by;

  • Note:

  • 1. Bullets queried after grouping: grouping fields and aggregation functions

  • 2. Where and having?

  • Where is qualified before grouping and does not participate in grouping if the conditions are not met. Having is qualified after the group and will not be queried if the condition is not met

  • Where can not be followed by an aggregation function, having can be used to determine the aggregation function

2.5, paging query

  • Syntax: limit Start index, number of items per page

  • Formula: == Start index =(current page number -1) * number of items displayed per page ==

    Select * from student limit 0,3; Select * from student limit 3,3; — Limit is a mysql “dialect”

3. DML: Add, delete and modify the data in the table

Overview of DML:

Used to manipulate data contained in a database object, that is, the unit of operation is a record.

3.1 Adding data:

  • Insert into table name (1, 2… N) values(1, 2,… The value of n);

    Insert into student values(1,’ 三’,1); INSERT INTO student(id,username,tid) VALUES — INSERT INTO student(id,username,tid) VALUES — INSERT INTO student(id,username,tid) VALUES — INSERT INTO student(id,username,tid) VALUES — INSERT INTO student(id,username,tid) VALUES —

3.2. Delete data

  • Delete from table name [where condition]

    delete from student where id=3;

3.3. Modify data

  • Update table_name set table_name = ‘data’ WHERE table_name = ‘data’;

    Update student set username = “update student set username =”;

4. Internal and external join & self-join & sub-query & aggregate function

SELECT p. 'id', P. 'productName',pr. 'dirName' FROM product p,productdir pr WHERE p. 'dir_id' =pr. 'id' -- productdir =pr SELECT p.`id`,p.`productName`,pr.`dirName` FROM product p JOIN productdir pr ON p.dir_id=pr.id; SELECT p. 'id', P. 'productName',pr. 'dirName' FROM product p LEFT OUTER JOIN productdir pr ON p.dir_id=pr. SELECT p. 'id', P. 'productName',pr. 'dirName' FROM product p RIGHT OUTER JOIN productdir pr ON p.dir_id=pr.id; DirName,pr2.dirName FROM productdir pr1,productdir pr2 WHERE pr1. id =pr2. parent_id; SELECT AVG(salePrice) FROM product; SELECT COUNT(id) FROM product; SELECT COUNT(id) FROM product; SELECT COUNT(id) FROM product WHERE dir_id=2; SELECT MIN(salePrice),MAX(salePrice),SUM(salePrice) FROM product; SELECT dir_id,AVG(salePrice) FROM product GROUP BY dir_id; Query the number of each commodity category and the total number of each commodity category. SELECT dir_id,COUNT(id) FROM product GROUP BY dir_id; SELECT dir_id,COUNT(id) FROM product WHERE salePrice>100 GROUP BY dir_id; SELECT * FROM product WHERE salePrice>100 GROUP BY dir_id; SELECT dir_id,SUM(salePrice) sp FROM product GROUP BY dir_id HAVING sp>1500; -- Demand: Query information of all products with higher retail price than Logitech MX1100. SELECT * FROM product WHERE salePrice>(SELECT salePrice FROM product WHERE productName =" MX1100"); -- Requirement: Query the information of all products with the same category number and discount as logitech M100. SELECT * FROM product WHERE (dir_id,cutoff) =(SELECT dir_id,cutoff FROM product WHERE productName =" M100");Copy the code

Bottom line: Database technology is a must-have skill for every developer, no matter what language they are learning

That’s all for today’s share, please remember to like + follow