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

This article mainly introduces the relevant knowledge of "what are the basic knowledge points of DDL, DML and DQL of MySQL". The editor shows you the operation process through actual cases, the method of operation is simple and fast, and it is practical. I hope that this article "what are the basic knowledge points of DDL, DML and DQL of MySQL" can help you solve the problem.

Database related Concepts what is a database

A warehouse that holds data. It reflects that in our computer, it is a software or file system. Then save all the data in these special files, and need to use a fixed language (SQL language / statements) to manipulate the data in the file.

A warehouse for storing data, which is organized for storage

English name: DataBase, DB for short

Advantages of database

Database is to store data in files according to a specific format, and it is convenient to add, delete, modify and query a large number of data through SQL statements. Database is an efficient solution for managing a large amount of information.

Common databases

Common Relational Database Management system

When we develop an application, all the data in the program needs to be saved to professional software. These professional software for saving data are called databases. We study the database, not how to develop a database software, we learn how to use the database and the operation of the data records in the database. The database software is developed by a third-party company.

Oracle: it is a large relational database of Oracle. The system has good portability, easy to use and strong function, and is suitable for all kinds of large, medium, small and microcomputer environments. It is efficient, safe and reliable. But it's for a fee.

MYSQL: early developed by a Swedish company called MySQL AB, later acquired by sun, and later acquired by Oracle. Small size, high speed and low total cost of ownership, especially the open source feature, generally choose MySQL as the website database for the development of small and medium-sized websites. The MySQL6.x version also starts to charge.

DB2: IBM's database product, for a fee. Often used in the banking system.

SQLServer:MicroSoft charges for a medium-sized database. C #,. Net and other languages are often used.

From the SyBase:Sybase company. Has faded from the stage of history. Provides a very professional data modeling tool PowerDesigner.

Common databases: Java development applications mainly use databases: MySQL (5.6), Oracle, DB2. (reason: open source, free, powerful enough to handle web development)

Relational database

When developing software, there must be a certain relationship between the data in the software. For example, the relationship between goods and customers, a customer can buy a variety of goods, and a product can be purchased by multiple customers.

If you need to save the data in the database while maintaining the relationship between the data, you can use the above databases directly. All of the above databases are relational databases.

Relational data: when designing a database, you need to use the Emurr entity relationship diagram to describe it.

Emurr is the first letter of two words, and E stands for Entity entity R and represents Relationship relationship.

1. There is bound to be a certain relationship between the data in the data table, such as the relationship between goods and customers, a customer can buy a variety of goods, and a product can be purchased by multiple customers.

two。 When designing a database, you can use ER entity relationship diagrams to describe the relationships between tables, with E for Entity entities and R for Relationship relationships.

3. Entity: can be understood as an object in our Java program. For example, goods, customers and so on are all physical objects. It is represented by a rectangle (rectangle) in the Emurr diagram.

4. Attributes: entity objects contain attributes, such as trade name, price, and so on. For the attributes in an entity, we call the data of this entity, which is represented by an ellipse in the Emurr diagram.

5. Relationship: the relationship between entities and entities: a diamond is used in the Emurr diagram.

Requirements: use Emurr diagram to describe the relationship among customers, goods, and orders

Brief introduction of MySQL Database SQL

What is SQL?

Structured Query Language structured query language. The SQL statement does not depend on any platform and is common to all databases. Learned the use of SQL statements, can be used in any database, but all have unique content. SQL sentence is powerful, easy to learn and easy to use.

Characteristics of SQL

SQL statement is a non-procedural language, each SQL execution will have a specific result. There is no effect between multiple statements

SQL action

SQL statements mainly operate data records in databases, data tables, and data tables.

SQL general syntax

SQL statements can be written on a single line or multiple lines, ending with a semicolon.

You can use spaces and indentation to enhance the readability of statements.

SQL statements in MySQL databases are case-insensitive and are generally uppercase in keyword development.

Three kinds of notes

-single-line comments:-- comment content

-Multi-line comments: / * comment content * /

-# comment content: (single-line comments specific to mysql)

SQL classification

SQL is a language used to access relational database, which has four functions of defining, manipulating, controlling and querying relational database. So according to the four functions, we classify SQL.

DDL (Data Definition Language) data definition language

Used to define database objects: databases, tables, columns, etc. Keywords: create drop alter truncate (clear data record) show, etc.

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

Update, add, and delete records in database tables. Such as update (update), insert (insert), delete (delete) do not contain queries

DQL (Data Query Language) data query language ★

Query for data table records. Keyword select.

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

Is a statement used to set or change database user or role permissions, such as grant (set permissions), revoke (revoke permissions), begin transaction, etc. This is rarely used.

MySQL directory structure

MySQL database connection

MySQL is a database that requires an account name and password to log in. After login, it provides a default root account, which can be logged in using the password set during installation.

Start the database service: enter the net start MySQL command in the open dos window

Turn off the database service: enter the net stop MySQL command in the open dos window

Connection MySQL: login format 1:mysql-u username-p password

Mysql-uroot-p123456

Login format 2:mysql [- h connection host ip address-P port number 3306]-u user name-p password

Mysql-h 127.0.0.1-P 3306-u root-p 123456

If the connection is local: the-h-P host IP and port can be omitted. So you can log in to the mysql database.

Exit: exit command

Show database: show databases

SQL_DDL_ manipulates databases DDL_ creates and views databases

Create the database:

1. Create a database directly

Create database database name

two。 Determine whether the database exists and create it (if not, create it)

Create database if not exists database name

3. Create a database and specify a character set (coding table)

Create database database name character set character set

Description: character set is the name of the encoding table, utf8 in mysql

View the database:

1. View all databases

Show databases

two。 View the definition information of a database

Show create database database name

3. View the database currently in use

Select database ()

Use and switch databases:

Use database name; DDL_ modify and delete database

Modify the database:

1. Modify database character set

-- alter means to modify the alter database database name default character set new character set

Note: if the coding table specified by the modified database is utf8, remember that you cannot write utf-8.

The common codes in Java correspond to the codes in mysql database.

JavaMySQLUTF-8utf8GBKgbkGB2312gb2312ISO-8859-1latin1

Delete the database:

1. Delete directly

-- drop deletes database drop database database name

two。 Determine if it exists when you delete the database (if so, delete it)

Drop database if exists database name; SQL_DDL_ operation data table DDL_ database constraint

The concept of constraints:

Constraints are rules that act on columns in a table to limit the data added to the table

The existence of constraints ensures the correctness, validity and integrity of the data in the database.

Classification of constraints:

Constraint name keyword description non-null constraint NOT NULL guarantees that all data in the column cannot have null null unique constraint UNIQUE guarantees that all data in the column is different primary key constraint PRIMARY KEY primary key is the unique identification of a row of data, requires non-empty and unique check constraint CHECK to ensure that the values in the column meet a certain condition default constraint DEFAULT saves data If no value is specified, the default value is used to constrain the FOREIGN KEY foreign key to establish a link between the data of the two tables to ensure the consistency and integrity of the data.

MySQL5.7 does not support checking constraints, but write statements do not report errors. The MySQL8.0 version supports checking constraints.

Non-empty constraint

Non-null constraints are used to ensure that all data in the column cannot have null values.

1. Add constraints when creating a table

Add a non-empty constraint create table table name (column name data type not null,...) when creating the table.

two。 Add constraints after the table is built

-- add constraints alter table table name modify field name data type not null after the table is built

3. Delete constraint

Alter table table name modify field name data type

Unique constraint

Unique constraints are used to ensure that all data in the column is different.

1. Add unique constraints when creating a table

-- method 1create table table name (field name data type UNIQUE,...);-- method 2create table table name (field name data type,...) [CONSTRAINT] [constraint name] UNIQUE (column name)

two。 Add unique constraints after the table is built

-- add a unique constraint alter table table name modify field name data type UNIQUE after building the table

3. Delete unique constraint

Alter table table name drop index field name

Primary key constraint

The primary key is the unique identity of a row of data, which is required to be non-empty and unique.

A table can have only one primary key

1. Add a primary key constraint when creating a table

Create table table name (field name data type PRIMARY KEY [AUTO_INCREMENT],-- [AUTO_INCREMENT] increments automatically when no value is specified.); create table table name (column name data type, [CONSTRAINT] [constraint name] PRIMARY KEY (column name))

two。 Add a primary key constraint after the table is built

Alter table table name add PRIMARY KEY (field name)

3. Delete primary key constraint

Alter table table name drop PRIMARY KEY

Default constraint

When saving data, if no value is specified, the default value is used.

1. Add default constraints when creating a table

Create table table name (field name data type default default,...)

two。 Add default constraints after building the table

Alter table table name alter column name set DEFAULT default

3. Delete constraint

Alter table table name alter column name drop DEFAULT;DDL_ create and view and table

Premise: create a database db1 and use this database

-- create a database create database db1;-- using the database use db1

Create a table:

Create table table name (field name 1 field type constraint, field name 2 field type constraint,... Field name n field type constraint);-- Note: the last field does not have a comma

Create a table with the same table structure as other tables

Create table table name like other table name

Data types commonly used in MySQL

Case list requirements:

Design a student form with the following requirements:

Student number, requires a unique primary key, self-increasing

Name, cannot be empty, and unique

Gender, male and female only, the default value is null

Class, string type

The time of enrollment is year, month and day.

Math score, double type, default is 60

English score, double type, no default value

Create table students (id int primary key auto_increment, name varchar (10) not null unique, sex enum ('male', 'female') default null, class varchar (10), starttime date, math int default 60, english int)

View the table:

1. View all the tables in a database

Show tables

two。 View table structure

Desc table name

3. View the SQL statement that creates the table

Show create table table name;-- View the table-building sql statement show create table students listed above according to this statement

DDL_ deletes and modifies the structure of the table

Delete the table:

1. Delete directly

Drop table table name

two。 Determine whether the table exists when deleting the table (if so, delete it)

Drop table if exists table name

Modify the table:

1. Modify table name

Alter table old table name rename to new table name

two。 Add a field (a column) to the table

Alter table table name add field name data type

3. Modify the field data type in the table

Alter table table name modify field name new data type

4. Modify the field name (column name) and data type in the table

Alter table table name change field name new field name new data type

5. Delete fields (columns) from the table

Alter table table name drop field name; SQL_DML_ operation database DML_ inserts table data

1. Insert all fields

-- write the name of the insert into table in all fields (Field 1, Field 2.) Values (value 1, value 2.);-insert all unwritten field names insert into table name values (value 1, value 2);-- insert data insert into students (id,name,sex,class,starttime,math,english) values (1) into the table in the case (1) 'Zhang San', 'male', 'Class 1 of Senior three', '2022-03-02). Insert into students values (2Jing'Li Si','Nu', 'Class 2 of Senior three', '2022-03-01)

two。 Insert part of the data

-- insert name, class, school time, English score-- id default growth, gender default null, mathematics default 60insert into students (name,class,starttime,english) values ('Wang Wu', 'Class 3', '2022-03-02 values 78)

Description: when inserting part of the data, the column name must be written.

3. Insert data in bulk

Insert into table name values (field value 1, field value 2...), (field value 1, field value 2...), (field value 1, field value 2...)

Fields with no data added will use NULL

Note:

The value corresponds to the column one by one. You need to write as many values as there are columns. If a column has no value, you can use null to indicate that the insert is empty.

The data type of the value matches the data type defined by the column, and the length of the value cannot exceed the length of the defined column.

String: insert data of character type, it is recommended to write English single quotation marks. In mysql, use single quotation marks to indicate a string

Date time type data should also be enclosed in English single quotation marks, such as yyyy-MM-dd

DML_ updates table data

1. Modify data without conditions

Update table name set field name = new value, field name = new value,...;-- Note: unconditional modification is to modify the entire column in the data table-- change the value of math in the students table to 90update students set math=90

two。 Modify data with conditions

Update table name set field name = new value, field name = new value,... Where condition;-- modify the gender of Wang Wu in students table to male, and set the math score to 70update students set sex=' male', math=70 where name=' Wang Wu'.

3. Keyword description

UPDATE: indicates to modify the record SET: which field to change WHERE: set condition

4. Be careful

Update database records without conditions: UPDATE table name SET field name = new value; modify the modified columns in the entire table

Conditional: UPDATE table name SET field name = new value WHERE condition

DML_ deletes table records

1. Delete without condition

DELETE-deletes the record DELETE FROM table name; the table is still there and can be manipulated, just delete the data.

two。 Conditional deletion

DELETE FROM table name WHERE condition;-- delete the information of Wang Wu in the student table DELETE FROM students WHERE name=' Wang Wu'

3.truncate deletes table records (belonging to DDL)

Truncate table table name

The difference between 4.truncate and delete

Delete deletes the data in the table one by one.

Truncate destroys the whole table and re-creates a new table. The new table structure is exactly the same as the original table structure.

SQL_DQL_ simple query data

Prepare a student table and query on this table

DQL_ basic query

1. Query all data

Select * from table name;-- query all the data in the student table select * from students

two。 Query the data of the specified column

The select field name is 1, and the field name is 2. From table name;-- query the name and class fields select name,class from students

3. Set aliases for the fields queried

Select field name 1 as alias 1, field name 2 as alias 2 from table name;-- query the fields in the students table and set aliases select id as student number, name as name, sex as gender, class as class, starttime as admission time from students

4. Remove the duplicated data from the query

-- DISTINCT to repeat-- No duplicate select DISTINCT class from students appears in the result of querying the class field.

DQL_ conditional query

1. Conditional query syntax

The select field name is 1, and the field name is 2. Where condition list

two。 Conditional operator

Symbol function > greater than = 80 and sex=' male'

4. Inquire about students whose English scores are between 60 and 80

-- BETWEEN value 1 AND value 2-- indicates the range from value 1 to value 2, and the bag head includes the tail select * from students where english between 60 and 80 position select * from students where english > = 60 & & english300

The difference between 4.where and having

Having is usually used in conjunction with group by grouping. Where has nothing to do with grouping.

Having can write aggregate functions (where aggregate functions appear after having), such as aggregate functions in having (count,sum,avg,max,min), which cannot appear in where conditions.

Where is filtered before grouping, and having is filtered after grouping.

DQL_ paging query

1. Application and concept

For example, when we log on to JD.com and Taobao, there may be tens of thousands of items of commodity information returned, not all at once. It shows a fixed number of entries on one page. Suppose we page in a way that displays five records per page.

-- starting index: starting from 0, index 0 represents the first row of the data table, data select field list, from table name, limit starting index, number of query entries.

Calculation formula: starting index = (current page number-1) * number of entries displayed per page

Note:

Paging query limit is the dialect of MySQL database

Oracle paging queries using rownumber

SQLServer paging queries using top

two。 Paging query

-- query the data in the student table. Every four pieces of data are select * from students limit 0, 4 select, * from students limit 4, select 4, select, from students limit 8, select, from students limit, 12, select, 4, select, query, query, select, query, and display.

3. Return the first or middle rows of data

-- 2 represents the index of the paging query, and the corresponding data table is the third row of data, and 4 indicates that each page displays four pieces of data-- the query starts with the third row of data, and the query starts with four pieces of data select * from students limit 2.

4.SQL execution order

SELECT field name (5) FROM table name (1) WHERE condition (2) GROUP BY grouped column name (3) HAVING condition (4) ORDER BY sort column name (6) LIMIT skips the number of rows and returns the number of rows (7); execution order: 1234567

Order: 1234567

This is the end of the introduction of "what are the basic knowledge points of DDL, DML and DQL of MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.