Chapter 1 SQL statements
1.1 SQL overview
1.1.1 introduction to SQL statement
The database does not know the JAVA language, but we also need to interact with the database. At this time, we need to use the SQL statement, which is the code of the database.
Structured query language (SQL for short) is a standard that relational database management systems need to follow. Different database manufacturers support SQL statements, but they all have unique contents.
Mandarin: SQL99 standard, the ISO standard followed by all database manufacturers.
Dialect: database specific syntax.
1.1.2SQL statement classification
SQL classification:
Data definition language: for short, DDL(Data Definition Language) is used to define database objects, that is, create, modify and delete databases, tables, columns, etc. Keywords: create, alter, drop, etc
Data manipulation language: DML(Data Manipulation Language) for short, which is used to update the records of tables in the database. Keywords: insert, delete, update, etc
Data control language: referred to as DCL(Data Control Language), it is used to define the access rights and security levels of the database and create users.
Data query language: DQL(Data Query Language) for short, which is used to query the records of tables in the database. Keywords: select, from, where, etc
1.1.3 overview of SQL general syntax
For our study, we can take a brief look at the general syntax of SQL
SQL statements can be written in one or more lines, ending with semicolons
Spaces and indents can be used to enhance the readability of statements
The SQL statements of MySQL database are not case sensitive. It is recommended to use uppercase for keywords
For example: SELECT * FROM user.
You can also use / * * / to complete the annotation –#
The data types we often use in MySQL are as follows
The detailed data types are as follows (detailed reading is not recommended!)
Classification type name Description
Integer type tinyInt is a small integer
smallint small integer
mediumint medium size integer
int(integer) an integer of normal size
Decimal type float single precision floating point number
Double double precision floating point number
Decimal (m,d) compresses the strict fixed-point number decimal(10,2)
Date type year YYYY 1901~2155
time HH:MM:SS -838:59:59~838:59:59
date YYYY-MM-DD 1000-01-01~9999-12-3
datetime YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestamp YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
Text, binary type CHAR(M) M is an integer between 0 and 255, fixed length char(10) tom
VARCHAR(M) M is an integer between 0 and 65535, variable length varchar(10) tom
The allowable length of TINYBLOB is 0 ~ 255 bytes
BLOB allowable length: 0 ~ 65535 bytes
The allowable length of mediablob is 0 ~ 167772150 bytes
The allowable length of LONGBLOB is 0 ~ 4294967295 bytes
The allowable length of TINYTEXT is 0 ~ 255 bytes
The allowable length of TEXT is 0 ~ 65535 bytes
The allowable length of MEDIUMTEXT is 0 ~ 167772150 bytes
The allowable length of LONGTEXT is 0 ~ 4294967295 bytes
VARBINARY(M) allows variable length byte strings with a length of 0~M bytes
BINARY(M) allows a fixed length byte string with a length of 0~M bytes
1.2 database operation of DDL: Database
DDL(Data Definition Language) this summary describes the creation, viewing, deletion and use of databases.
1.2.1 create database
format:
* create database Database name;
* create database Database name character set character set;
For example:
#The code of the data in the database is the default code specified when installing the database utf8 CREATE DATABASE webdb_1; #Create a database and specify the encoding of the data in the database CREATE DATABASE webdb_2 CHARACTER SET utf8;
1.2.2 viewing database
To view all databases in the MySQL server:
show databases;
To view the definition information of a database:
show create database database name;
For example:
show create database webdb_1;
1.2.3 delete database
drop database database name;
For example:
drop database webdb_2;
1.2.4 using database
View databases in use:
select database();
Other database operation commands
Switch database:
use database name;
For example:
use webdb_1;
1.3 DDL table operation: table
DDL(Data Definition Language) this summary describes the creation, viewing, deletion and use of data tables.
1.3.1 create table
Basic format:
CREATE TABLE Table name ( Field name 1 data type(length), Field name 2 data type(length), Field name 3 data type(length), .... );
The field name parameter specifies the name of the column in the table.
The data type parameter specifies the data type of the column (for example, varchar, integer, decimal, date, and so on).
The length parameter specifies the maximum length of the column in the table.
See Appendix for more data types:
Example:
Now we want to create a table named "Persons" with five columns: PersonID, LastName, FirstName, Address and City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons ( PersonID INT, LastName VARCHAR(255), FirstName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255) );
The data type of the PersonID column is int and contains integers.
The data type of LastName, FirstName, Address and City columns is varchar, which contains characters, and the maximum length of these fields is 255 characters.
Enhanced format:
CREATE TABLE Table name ( Field name 1 data type(length) [constraint], Field name 2 data type(length) [constraint], Field name 3 data type(length) [constraint], .... );
SQL constraints are used to specify data rules in tables.
If there is a data behavior that violates the constraint, the behavior will be terminated by the constraint.
Constraints can be specified when the table is created (through the CREATE TABLE statement) or after the table is created (through the ALTER TABLE statement).
In SQL, we have the following constraints:
NOT NULL - indicates that a column cannot store NULL values.
UNIQUE - ensure that each row of a column must have a UNIQUE value.
Primary key - a combination of not null and UNIQUE. Ensuring that a column (or a combination of two columns and multiple columns) has a UNIQUE identification helps to find a specific record in the table more easily and quickly.
FOREIGN KEY - ensures referential integrity that data in one table matches values in another table.
CHECK - ensures that the values in the column meet the specified criteria.
DEFAULT - specifies the DEFAULT value when no value is assigned to the column.
In later chapters, we will explain the relevant constraints.
1.3.2 view table
View all tables in the database:
Format: show tables;
View table structure:
Format: desc Table name; For example: desc sort;
1.3.3 delete table
Format: drop table name;
For example: drop table category;
1.3.4 modify table structure format:
alter table name add column name type (length) [constraint];
Function: modify the table and add columns
For example:
#1. Add a new field for the classification table. desc is the classification description, and the type is varchar(20) ALTER TABLE category ADD `desc` VARCHAR(20);
alter table name modify column name type (length) [constraint];
Function: modify the type, length and constraints of table columns
For example:
#2. Modify the description field desc of the classification table and add a constraint not null to the type varchar(50) ALTER TABLE category MODIFY desc VARCHAR(50) NOT NULL;
alter table name change old column name new column name type (length) [constraint];
Function: modify table and column names
For example:
#3. Replace the category name field of the category table with snamesname varchar(30) ALTER TABLE category CHANGE desc description VARCHAR(30);
alter table name drop column name;
Function: modify tables and delete columns
For example:
#4. Delete the snamename column in the classification table ALTER TABLE category DROP description;
rename table name to new table name;
Function: modify table name
For example:
#5. Rename the classification table category to category 2 RENAME TABLE category TO category2;
alter table name character set character set;
Function: modify the character set of the table
For example:
#6. Modify the coding table of the classification table category to gbk ALTER TABLE category CHARACTER SET gbk;
1.4 DML data operation language
Referred to as DML (data management language), it is used to update the records of tables in the database
1.4.1 insert table record: Insert
Syntax:
-- Insert some fields into the table insert into surface (Field 1,Field 2,Field 3..) values (Value 1,Value 2,Value 3..); --Insert all fields into the table,The order of the fields is the order in which the table was created insert into surface values (Value 1,Value 2,Value 3..);
be careful:
Values and fields must correspond, with the same number and type
The data size of the value must be within the length of the field
Except for the numeric type, the values of other field types must be enclosed in quotation marks. (single quotation mark recommended)
If you want to insert a null value, you can not write the field or insert null.
For example:
INSERT INTO category(cid,cname) VALUES('c001','an electric appliance');
INSERT INTO category(cid,cname) VALUES('c002','Clothes & Accessories');
INSERT INTO category(cid,cname) VALUES('c003','Cosmetics');
INSERT INTO category(cid,cname) VALUES('c004','book');
INSERT INTO category(cid) VALUES('c002');
INSERT INTO category(cname) VALUES('Consumables');
1.4.2 update table record: update
It is used to modify the data of specified conditions, and modify the specified column of records that meet the conditions to the specified value
Syntax:
--Updates the specified fields for all records update Table name set Field name=value,Field name=value,...; --Updates the specified field of the symbol condition record update Table name set Field name=value,Field name=value,... where condition;
be careful:
The type of column name should be consistent with the modified value
The maximum length cannot be exceeded when modifying the value
In addition to the numeric type, the values of other field types must be enclosed in quotation marks
1.4.3 delete record: delete
Syntax:
delete from Table name [where condition]; perhaps truncate table Table name;
Interview questions:
Delete all records in the table delete from Table name; Still use truncate table Table name; Delete method: delete Delete one by one without emptying auto_increment Number of records. truncate Directly delete the table and re create the table, auto_increment Will reset, starting again from 1.
1.5DQL data query language
DQL(Data Query Language) is used to query the records of tables in the database.
1.5.1 preparation
#Create item table: CREATE TABLE product( pid INT PRIMARY KEY, pname VARCHAR(20), price DOUBLE ); INSERT INTO product(pid,pname,price) VALUES(1,'association',5000); INSERT INTO product(pid,pname,price ) VALUES(2,'Haier',3000); INSERT INTO product(pid,pname,price ) VALUES(3,'Thor',5000); INSERT INTO product(pid,pname,price ) VALUES(4,'JACK JONES',800); INSERT INTO product(pid,pname,price ) VALUES(5,'jeanswest ',200); INSERT INTO product(pid,pname,price ) VALUES(6,'dandy',440); INSERT INTO product(pid,pname,price ) VALUES(7,'Jin Ba',2000); INSERT INTO product(pid,pname,price ) VALUES(8,'Chanel',800); INSERT INTO product(pid,pname,price ) VALUES(9,'Affordable materia medica',200); INSERT INTO product(pid,pname,price ) VALUES(10,'Face bully',5); INSERT INTO product(pid,pname,price ) VALUES(11,'Miss you so much',56); INSERT INTO product(pid,pname,price ) VALUES(12,'Hong fluttering milk tea',1); INSERT INTO product(pid,pname,price ) VALUES(13,'Fruit 9',1);
1.5.2 syntax:
select [distinct] *| Listing,Listing from Table name where condition
1.5.3 simple query
1.Query all products.
select * from Table name;
2.Query commodity name and price.
select Column name 1,Column name 2 from Table name;
3.Remove duplicate values.
select distinct Listing from Table name;
4.The query result is an expression (arithmetic query):
select Column name operator operand from product;
For example:
Add 10 yuan when querying the price display of all commodities.
5.The keyword used in alias query is as(as (can be omitted)
Column alias: select Listing as alias from Table name;
Table alias: select * from product as p;
1.Query all products. select * from product; 2.Query commodity name and price. select pname,price from product; 3.Alias query.The keywords used are as(as (can be omitted). 3.1 Table alias: select * from product as p; 3.2 Column alias: select pname as pn from product; 4.Remove duplicate values. select distinct price from product; 5.The query result is an expression (operation query): the price of all commodities+10 Display element. select pname,price+10 from product;
1.5.4 query criteria
Comparison operator > < = > = < >
!= Greater than, less than, greater than (less than) equal to, not equal to
BETWEEN... AND... Display the value in a certain range (including head AND tail)
IN(set) the value displayed in the in list, for example: in (100200)
Like 'Zhang pattern' fuzzy query, in the Like statement,
%Represents zero or more arbitrary characters,
_ Represents a character,
For example: first_name like ‘_ a%’;
IS NULL determines whether it is empty
Logical operator and multiple conditions hold at the same time
or any of multiple conditions holds
Not tenable, for example: where not (salary > 100);
The wildcard% represents 0 and more characters and is used with like
_ Represents 1 character, used with like
Title stem:
#To query all information about a product named "playboy":
#Query all information of goods with a price of 800
#Query the names and prices of all goods whose price is not 800
#Query all information of all commodities with commodity price greater than 60 yuan
#Query all information of all commodities with commodity prices between 200 and 1000
#Query all information of all commodities with commodity price of 200 or 800
#Query all the information of all products whose names contain the word "want"
#Query the commodity names and prices of all commodities whose commodity names begin with 'incense'
#Query all the information of all products whose second word of name is "want"
#Query all information of commodities with blank commodity price
#Query all information of goods whose price is not empty
#To query all information about a product named "playboy": select * from product where pname = 'dandy'; #Query all information of goods with a price of 800 select * from product where price = 800; #Query the names and prices of all goods whose price is not 800 select pname, price from product where price != 800; select pname, price from product where price <> 800; #Query all information of all commodities with commodity price greater than 60 yuan select * from product where price > 60; #Query all information of all commodities with commodity prices between 200 and 1000 select * from product where price>= 200 and price <= 1000; #select * from product where price>= 200 && price <= 1000; select * from product where price between 200 and 1000; #Query all information of all commodities with commodity price of 200 or 800 select * from product where price = 200 or price=800; select * from product where price in (200,800); #Query all the information of all products whose names contain the word 'want' select * from product where pname like '%Think%'; #Query the commodity names and prices of all commodities whose commodity names begin with 'fragrance' select * from product where pname like 'fragrant%'; #Query all the information of all products whose second word of name is' want ' select * from product where pname like '_Think%'; -- Query all the information of the product ending with the desired name in the product name select * from product where pname like '%Think'; #Query all information of commodities with blank commodity price select * from product where price is null ; #Query all information of goods whose price is not empty select * from product where price is not null ;