Basic syntax of MySQL DDL and DML and DQL

preface

SQL statement, namely structured query language, is a programming language for special purposes. It is a database query and programming language, which is used to access data, query, update and manage relational database systems. It is also an extension of database files.

The SQL statements specified in the SQL standard are divided into: DDL (data definition language), DML (Data Manipulation Language), DQL (Data Query Language) and DCL (Data Control Language). This article will introduce them in detail.

First of all, let's learn some notes about SQL syntax:

1. SQL statements can be written in one or more lines, ending with semicolons.

2. Spaces and indents can be used to enhance the readability of statements.

3. The SQL statements of MySQL database are not case sensitive, and it is recommended to use uppercase keywords.

4. 3 Notes

① Single line comment: – comment content or # comment content (unique to mysql)

② Multiline comment: / * comment*/

1, DDL (data definition language)

DDL language: data definition language is used to define and manage data objects, such as databases, data tables, etc. DDL commands include CREATE, DROP, and ALTER.

Here is an example with code:

-- SQL Syntax is not case sensitive
-- Use a semicolon at the end of each sentence;
# Library operation
-- Show all libraries
show databases;

-- Create a library
-- create database Library name;
create database ku;

-- Delete a library
-- drop database Library name;
drop database ku;

-- Use library
-- use Library name;
use ku;


# Table operation
-- View all tables in the library
show tables;

-- Build table
create table Table name(
	Field name type attribute,
	Field name type attribute,
	....
	Field name type attribute
);

create table tab_teacher(
	tea_name varchar(10),
	tea_sex char(1),
	tea_birthday datetime,
	tea_money decimal(20,1)
);

show tables;

-- View table structure
desc tab_teacher;

show create table tab_teacher;

-- ` Backquotes invalidate keywords
CREATE TABLE `tab_teacher` (
  `tea_name` varchar(10) DEFAULT NULL,
  `tea_sex` char(1) DEFAULT NULL,
  `tea_birthday` datetime DEFAULT NULL,
  `tea_money` decimal(20,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
    COLLATE=utf8mb4_0900_ai_ci

2, DML (data operation language)

DML language: Data Manipulation Language, which is used to manipulate the data contained in database objects. DML commands include INSERT, DELETE, and UPDATE.

Here is an example with code:

# DML statement

-- newly added
-- grammar
-- insert into Table name(Field name,Field name...Field name) values(value,value...value);

-- The date is expressed as a string
insert into student(sid,sname,birthday,ssex,classid) values(9,'Zhang San','1999-1-1','male',3);

insert into student(sid,ssex,classid) values(11,'male',2);

-- Let the primary key increase automatically
insert into student(sname) values("Wang sang");
insert into student values(default,'Lao Wang','1970-6-1','male',2);
insert into student values(null,'Lao Wang','1970-6-1','male',2);

-- Insert multiple pieces of data at one time
insert into student(sname,ssex) values('Wang shuaishuai','male'),('Wang Liangliang','male'),('Sister Wang','female');

-- Unusual addition method
-- All tables must exist
create table stu1(
	xingming varchar(10),
	ssex varchar(2)
)
-- insert into select 
insert into stu1 select sname,ssex from student;

-- Insert data when creating a new table
-- New table cannot exist
create table newstu select sname,birthday,ssex from student;


-- modify
-- grammar
-- update Table name set Field name=value,Field name=value... where clause

update stu1 set xingming = 'Zhao leilei';

update newstu set ssex= 'female' where sname='Lao Wang';

-- Range
update student set ssex = 'female',classid = 10 where sid >= 10 and sid <= 15;

-- between Small data and big data
update student set ssex='ha-ha',classid = 20 where sid between 10 and 15;


-- delete
-- delete from Table name  where clause
delete from stu1;
delete from student  where sname = 'Lao Wang';

-- Empty table
truncate Table name
truncate student;

3, DQL (data query language)

DQL language: Data Query Language, which is used to query database data. DQL command has SELECT (query).

Here is an example with code:

# DQL

-- query
-- Query the data of all rows and columns of the table(What you get is a virtual table)
-- select * from Table name;
select * from student;

-- Query specified fields
-- select Field name 1, field name 2... from Table name;
select sid,sname,birthday,ssex,classid from student;


-- Field alias
-- select Old field name as 'new field name';
select sname as 'full name', birthday 'birthday',ssex Gender from student;


-- Remove duplication distinct
-- select distinct Field name... from Table name; 
select distinct ssex,classid,sid from student;


-- Conditional query  WHERE clause
select * from student where ssex = 'male' and classid = 1;

-- Birthday greater than 1990-1-1 Students of
select * from student where birthday < '1990-1-1';

-- Fuzzy query like
insert into student(sname) 
values('Sanfeng Zhang'),('Zhang San'),('Zhang Sansan');

-- Data related to Zhang Zi
-- Fuzzy symbol % Any number of any characters
select * from student where sname like '%Zhang%';

-- A person surnamed Zhang
select * from student where sname like 'Zhang%';

-- Fuzzy symbol_ An arbitrary character
select * from student where sname like 'Zhang__';

-- Student number is 2,5,6,8,9,20,300,4000
-- in Find in a specific range
select * from student  where sid in (2,5,6,8,9,20,300,4000);


-- Students without birthdays  is Yes, yes null Judgment of
select * from student where birthday is null;
select * from student where birthday is not null;



# grouping
-- group by field
select count(1) from student where ssex = 'male';
select count(1) from student where ssex = 'female';
select ssex,count(sid) from student group by ssex;

-- How many students are there in each class
select classid,count(sid) from student group by classid;

-- sc  Average score of each student
select sid,avg(score) average, sum(score) Total score,max(score) Highest score, min(score) Lowest score, count(*) frequency from sc group by sid;

4, Aggregate function

Syntax: the previous queries we made are horizontal queries, which are judged according to the conditions line by line, while the query using aggregate function is vertical query, which calculates the value of a column and then returns a result value. The aggregate function ignores NULL values.

– count the number count (field) / field can be written *, constant, any field name /count does not count the number of null data.

– statistical mean AVG (field)

– statistics max (field)

– statistical minimum min (field)

– statistical sum (field)

eg: select count(*) total number, sum(score) total score, avg(score) average score, max(score) highest score, min(score) lowest score from sc;

# Aggregate function
count(field) -- Count the number
-- number
avg(field) -- average value
sum(field) -- the sum
max(field) -- Maximum value
min(field) -- minimum value

-- count Count the number
select count(*) from student where ssex = 'male';
select count(sname) from student where ssex = 'male';
select count(1) from student where ssex = 'male';
select count('a') from student where ssex = 'male';
-- count() No statistics null
select count(birthday) from student;


-- avg average value
-- Average score of all students
select avg(score) from sc;

-- sum Total score
select sum(score) from sc;


select count(*) frequency, sum(score) Total score, avg(score) average, max(score) Highest score,min(score)Lowest score from sc;

First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials

Tags: Front-end Android Back-end Interview

Posted by Noctagon on Sat, 30 Jul 2022 09:58:45 +0300