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