3.1 SQL overview

Structured Query Language

mysql installation

install

3.2 student course database

3.3 data definition

Enter mysql

mysql;

or

mysql -uroot -p

Create database

CREATE DATABASE cyp;

View created databases
In the early days, databases and schema s were essentially the same
And sql keywords are not case sensitive, but their names are case sensitive

show databases;

Use database (enter mode)

use cyp;

View all tables in the database

show tables;

Not created, so none

establish

CREATE TABLE TAB1(COL1 SMALLINT,COL2 INT,COL3 CHAR(20),COL4 NUMERIC(10,3), COL5 DECIMAL(5,2));

Review the table again

 SHOW TABLES;

View details in table

DESC TAB1;

Show the situation when the table was created

 SHOW CREATE TABLE  TAB1;

Delete table cyp

 DROP DATABASE cyp ;

Exit mode

again
Line by line input

>mysql -uroot -p                       //Then enter the password
>CREATE DATABASE cyp;
> use cyp;
> create table tab1 (c1 smallint,c2 int,c3 char(20),c4 numeric(10,3),c5 decimal(5,2));
>  desc tab1;
> create table Student (Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) UNIQUE,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20));
> desc student;
>  create table Course(Cno CHAR(4) PRIMARY KEY,Cname CHAR(40)NOT NULL,Cpno CHAR(4),Ccredit SMALLINT,FOREIGN KEY(Cpno)REFERENCES Course(Cno));
> DESC cOURSE;
> CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY(Cno) REFERENCES Course(Cno));
> DESC SC;

Add a row to the student table

 ALTER TABLE Student ADD S_entrance DATE;

Modification type
Change the character type of age to integer type

 ALTER TABLE STUDENT MODIFY COLUMN Sage INT;

Add a constraint that the course name must take a unique value

ALTER TABLE Course ADD UNIQUE(Cname);

Delete base table

DROP TABLE <Table name> [RESTRICT|CASCADE];/*Limited | unlimited*/

 DROP TABLE STUDENT CASCADE;

Indicates that it cannot be deleted

 CREATE VIEW IS_STUDENT
    -> AS
    -> SELECT Sno,Sname,Sage
    -> FROM Student
    -> WHERE Sdept='is';

DROP TABLE Student RESTRICT;
DROP TABLE Student CASCADE;
SELECT *FROM IS_STUDENT;

Different sql has different details
See page87

Index type
Index of sequential files
B + tree index
Hash index
Bitmap index

Indexing

 CREATE UNIQUE INDEX Stusno ON Student(Sno);
 CREATE UNIQUE INDEX Coucno on Course(Cno);
 CREATE UNIQUE INDEX Scno ON SC(Sno ASC,Cno DESC);

Modify index
There's a bug

3.4 data query

tricks:

SELECT [ALL|DISTINCT] <Target list expression> [,<Target list expression>]...        /*3 In accordance with SELECT The attribute values in the tuple are selected by the target list in the clause to form the result table
FROM<Table name or view name>[,<Table name or view name>...]|(<SELECT sentence>)[AS]<alias>     /*2 from from Clause specifies the base table, view, or derived table to find a tuple that meets the condition
[WHERE<Conditional expression>]                                               /*1 according to where Conditional expression
[GROUP BY<Alias 1>[HIVING<Conditional expression>]]                              /*If yes, group according to the value of column name 1, and change the tuples with equal attribute column values into a group
[ORDER BY<Column name 2>[ASC|DESC]];                        			  /* If so, sort in ascending or descending order according to the values in list 2

Single table query

SELECT Sno,Sname
FROM student;

 SELECT Sname,Sno,Sdept
 FROM Student;

Query all students

SELECT*
FROM Student;

Query calculated values

SELECT Sname,2021-Sage               /*The year is obtained by subtracting the age from the current year
FROM Student;                        

Select column

SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)
FROM Student;

Change the column title of the query result by specifying an alias

SELECT Sname NAME,'Year of Birth:'BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept)DEPARTMENT
FROM Student;

Eliminate rows with duplicate values

SELECT (ALL) Sno
FROM SC;

duplicate removal

SELECT DISTINCT Sno
FROM SC;

Query tuples that meet the criteria

query criteria	predicate
compare	=，>，<，>=，<=，!=，<>，!>，!<； NOT + the above comparison operators
Determine scope	BETWEEN AND,NOT BETWEEN AND
Determine set	IN,NOT IN
Character matching	LIKE,NOT LIKE
Null value	IS NULL ,IS NOT NULL
Multiple conditions (logical operation)	AND,OR,NOT

Check the list of all students in the computer science department

SELECT Sname
FROM Sudent
WHERE Sdept='CS';

Query the names and ages of all students under the age of 20

SELECT Sname,Sage
FROM Student
WHERE Sage<20;

Check the student number of the student who failed the exam

SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

Query the name, department and age of students aged 20 ~ 23.

SELECT Sname,Sdept,Sage
FROM Sdudent
WHERE Sage BETWEEN 20 AND 23;

Query the name, department and age of students aged not between 20 and 23

SELECT Sname,Sdept,Sage
FROM Sdudent
WHERE Sage NOT BETWEEN 20 AND 23;

IN can be used to find tuples whose attribute values belong to a specified set

Department of computer science, Department of mathematics, and Department of information

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');

Character matching
The predicate like can be used to match strings

[NOT] LIKE'<Matching string>' [ESCAPE'<Escape character>']

SELECT*
FROM Student
WHERE Sno LIKE '20192502';

Equivalent to

SELECT*
FROM Student
WHERE Sno ='20192502';

Surnamed Liu
There is no need to look for anything below. Look from that table

SELECT 
FROM
WHERE Sname LIKE 'Liu%';

Ouyang what

SELECT 
FROM
WHERE Sname LIKE 'Ouyang_';

_Representatives must have
%The representative string can be empty
_Yang%

SELECT 
FROM
WHERE Sname LIKE '_Yang%';

No, Liu

SELECT 
FROM
WHERE Sname NOT LIKE 'Liu%';

Escape character
ESCAPE '\'

SELECT 
FROM
WHERE Cname LIKE 'DB\_Design' ESCAPE'\';

Query the details of courses starting with 'DB_' and the penultimate character is i

SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE'\';

NULL

SELECT 
FROM
WHERE Grade IS NULL;

SELECT 
FROM
WHERE Grade IS NOT NULL;

Multiple condition query

SELECT 
FROM
WHERE Sdept='CS'  AND  Sage <20;

SELECT 
FROM
WHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';

Add the fourth line
Default ascending ASC descending DESC

ORDER BY Grade DESC;

A ascending B descending

ORDER BY A,B DESC;

Focus function

COUNT(*)                          		Count the number of tuples
COUNT([DISTINCT|ALL]<Listing>)    			Count the number of values in a column
SUM([DISTINCT|ALL]<Listing>)				Calculate the sum of a column of values (integer column)
AVG([DISTINCT|ALL]<Listing>)				Calculate the average of a column of values (integer column)
MAX([DISTINCT|ALL]<Listing>)				Calculate the maximum value of a column of values
MIN([DISTINCT|ALL]<Listing>)				Calculate the minimum value of a column of values

3.5 data update

3.6 handling of null values

3.7 view

Subsection 3.8