3.1 SQL overview
Structured Query Language
mysql installation
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