Oracle Database language-Structured Query Language SQL

Oracle Database language-Structured Query Language SQL

I. Data Definition Language DDL

1. Create a TABLESPACE: creat tablespace lyy DATAFILE 'C:/app/lyy. dbf' SIZE 10 M; (create a 10 m tablespace and store it in the C drive app folder)

Delete TABLESPACE: drop tablespace lyy;

2. create USER and assign permissions: creat user lyy profile default identified by 123456 default tablespace lyy temporary tablespace temp account unlock; (create USER lyy, DEFAULT password is 123456, default tablespace is lyy, the temporary tablespace is temp and the account is enabled)

Grant connect to lyy;
Grant resource to lyy; (GRANT lyy connect and resource permissions TO users)

3. Create a table

Syntax format: creat table name (attribute name 1 data type (length), attribute name 2 data type... attribute name n data type );

For example, create the following three tables:
-- Student table student: student ID sid, name sname, Gender ssex, age sage, phone number sphone
-- Curriculum course: course No. cid, course name cname, teacher name tname, lesson chour
-- Score table score: score number scid, student ID sid, course number cid, score grade

In Oracle, the full name of the table is: solution name. Table Name; solution name is the user name, solution name is not written, indicating that the solution name is the current user;

-- Create a student table

Creat table student (
Sid CHAR (10 ),
Sname VARCHAR2 (50) not null,
Ssex CHAR (1 ),
Sage NUMBER,
Sphone INTEGER
);

-- Create a course schedule

Creat table course (
Cid CHAR (10 ),
Cname VARCHAR2 (50 ),
Tname VARCHAR2 (50 ),
Chour NUMBER
);

-- Create an external table

Creat table score (
Scid CHAR (10 ),
Sid CHAR (10 ),
Cid CHAR (10 ),
Grade NUMBER
);

Drop table student;

4. Constraints

Constraints are added by users to ensure the entity integrity and reference integrity of the data stored in the database;

There are generally five types of constraints:

-- Primary key constraint: Primary key, which requires that the values of fields defined as Primary keys are unique and non-empty;
Constraint p1_sid primary key (sid) -- defines sid as the primary key.
-- Foreign key constraint: Foreign key, which requires that the value of the field defined as Foreign key must come from the value of the referenced field. The Foreign key field and the referenced field can have different names, however, the data type and length must be consistent;
Constraint f1_sid foreign key (sid) references student (sid) -- defines sid as a foreign key and the value comes from the sid of student table
-- Unique constraint: Unique, which must be Unique;
Unique (sid, cid) -- defines the uniqueness of the combination of sid and cid.
-- Non-null constraint: not null. A value is required;
Sname varchar2 (50) not null -- defines that the sname field is not empty
-- Check constraints: check. You can customize the field values based on your business needs.
Constraint c1_ssex check (ssex in ('M', 'F') -- Define check constraints. The value of the ssex field must be M or F.

5. Modify the ALTER TABLE

Alter table scott. test rename to TEST1 -- modify the TABLE name
Alter table scott. test rename column name to NAME1 -- modify the TABLE COLUMN NAME
Alter table scott. test modify NAME1 NUMBER (20) -- MODIFY the field type
Alter table scott. test add address VARCHAR2 (40) -- ADD a TABLE column
Alter table scott. test drop name cascadeconstraints -- delete a TABLE column

2. Data Operation Language DML

DML is a data operation language. It can perform insert, update, and delete operations. DML statements are transaction operation statements, commit confirmation and rollback are required for final completion. If you do not confirm or roll back the table, the current table will be locked, resulting in DDL and other operations on the table will fail.

1. insert data

Syntax format 1: insert into table name values (value 1, value 2 ......, value n );

Note: In SQL statements, except for the value (data), the other parts are case-insensitive. For data, single quotation marks are required for character types and no single quotation marks are required for other types; in this format, ensure that the number, type, length, and order of values must be consistent with the table fields.

Example: SELECT * FROM student;
Insert into student VALUES ('s001', 'zhang san', 'M', 30,13089247856 );

Syntax Format 2: insert into indicates (field name 1, field name 2 ,......, field name n) values (value 1, value 2 ,......, value n );

Note: The relationship between values and fields (in sequence) is doomed. The advantage is that you can insert values selectively based on the relationship between values and fields.

Example: insert into student (sname, ssex, sid, sage) VALUES ('lily', 'F', 's0002', 20 );

2. update Data

Syntax format: update table name set value expression [where condition]

Note: update itself is a column operation statement, that is, without conditions, it operates on the entire column of data in the table. If conditions are added, because conditions are limited to rows, it indicates that the column corresponding to the selected row is operated.

Example: SELECT * FROM student;
UPDATE student SET sage = 40;
UPDATE student SET sage = (sage + sphone)-sage, sphone = (sage + sphone)-sphone;
UPDATE student SET sage = sage + 1;
UPDATE student SET sage = sage + 1 where ssec = 'F ';
UPDATE student SET sname = replace (sname, 'zhang ', 'chen ');

3. delete data

Syntax format: delete from table [where condition]

Note: delete is a row operation. The minimum operation unit is a record. Remember that delete does not have the * sign. If the where condition is not added, all records in the table are deleted.

Example: delete from student WHERE sid = 's0002 ';
Insert into course VALUES ('c0001 ', 'oracle', 'Teacher 1', 32 );
Insert into score VALUES ('sc0001 ', 's0001', 'c0001 ', 100 );
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM score;

4. truncate: it is a DDL statement, but it can also delete the data of the entire table.

Syntax format: truncate table name

Example: truncate table score;

Iii. Data Query Language DQL

1. select is used to query data and obtain the desired information.

Syntax format:

Select: the content to be queried. It is a required keyword. It is followed by the content to be queried. It is generally based on fields, constants, and expressions (including fields ).
-- From: the source of the query content, which is a required keyword. The source can be a table, multiple tables, or other query statements;
-- Where: condition, which is an optional keyword. It is generally used to specify the query condition, that is, to filter data;
-- Group by: field, an optional keyword, used for grouping query;
-- Having: condition, which is an optional keyword used to filter Results After grouping;
-- Order by: field, an optional keyword, used for sorting;

Note: A Query without conditions is a column query. The query content can be a field, multiple fields, constants, or expressions in the table.

Example:

-- Query all contents of the student table
SELECT * FROM student;
-- Query the names and ages of all students
SELECT sname, sage FROM student;
-- Query Constants
SELECT sname, sage, 'Teacher 1' FROM student;
-- The query content is an expression.
SELECT sname, sage, sage + 1 FROM student;

2. | used to concatenate strings and variables

Example: SELECT sname, sage, sage | 'Year' FROM student;
SELECT sname, ssex FROM student;
SELECT sname, ssex, case WHEN ssex = 'M' then' male 'else' female 'end FROM student;

3. Conditions

The query condition is used to filter data. The basic unit of data filtering is row. Common keywords include: (>/</=/> =/<= /! =/<>/Between... and.../like/in/all/any/exists/not exists and so on; The connectors for multiple conditions are: and \ or \!

Example:

-- Query Information of all students older than 28
SELECT * FROM student WHERE sage> 28;
-- Query Information of all students older than or equal to 28 years old;
SELECT * FROM student WHERE sage> = 28;
-- Query all boys' Information
SELECT * FROM student WHERE ssex = 'M ';
SELECT * FROM student WHERE ssex! = 'F ';
SELECT * FROM student WHERE ssex <> 'F ';
-- Query the information of students whose student ID is higher than s0010.
SELECT * FROM student WHERE sid <'s0010 ';

1) between... and ..

Syntax format: field between value 1 and value 2 is equivalent to field> = value 1 and field <= value 2; it is an independent and complete field and cannot be split.

Example: -- Query student information between the ages of 23 and 28.
SELECT * FROM student WHERE sage> = 23 AND sage <= 28;
SELECT * FROM student WHERE sage BETWEEN 23 AND 28;
-- Query Information about girls aged between 23 and 28.
SELECT * FROM student WHERE sage BETWEEN 23 AND 28 AND ssex = 'F ';
SELECT * FROM student WHERE sage> = 23 AND ssex = 'F' AND sage <= 28;

2) like: fuzzy query is implemented. Generally, two special characters are used in combination with the % wildcard to match any number of characters. _ indicates matching one character.

Example: -- Query Information of all students surnamed Zhang.
SELECT * FROM student WHERE sname LIKE 'sheet % ';
-- Query the information of all the students with a name of 2 characters.
SELECT * FROM student WHERE sname LIKE 'zhang _';
-- Query the information of all students whose names are 3 Characters in total.
SELECT * FROM student WHERE sname LIKE 'zhang __';
SELECT * FROM student WHERE sname LIKE '% Zhang ';

3) in: it is an enumeration usage. The field in (value 1, value 2 ,......, value n) is equivalent to field = value 1 or field = value 2 or... or field = value n.

Example: -- query the information of a student of the age of 27 or 28.
SELECT * FROM student WHERE sage = 27 OR sage = 28;
SELECT * FROM student WHERE sage IN (27,28 );

4) distinct: used to modify fields, indicating unique queries and deduplication.

Example: -- query the student ID of all selected students.
SELECT sid FROM score;
Select distinct sid FROM score;

5) alias: it can be applied to the query content and source.

Syntax format: formerly known as alias. Generally, as is omitted, formerly known as Alias.

Example: SELECT sname, sage FROM student;
SELECT sname as name, sage age FROM student;
SELECT sname, sage + 1 sage FROM student;
SELECT sname sage FROM student; -- the syntax is correct and there is a problem at the application level. The Student name is queried, but the column name is changed to sage.

6) nesting

In SQL statements, query statements can be nested. Nesting is implemented by parentheses. The result of each query statement is a table and a set of values, which can be nested in the value or source.

-- Query the names of all students taking oracle courses.
SELECT sname FROM student WHERE sid IN (SELECT sid FROM score WHERE cid = (SELECTcid FROM course WHERE cname = 'oracle '));

-- Query the name of the course selected by a student. (Student name already exists)
SELECT cname FROM course WHERE cid IN (SELECT cid FROM score WHERE sid in (SELECT sid FROM student WHERE sname = 'zhang san '));

7) all: use and to break down the expression

-- Field> all (value 1, value 2,..., value N) is equivalent to: field> value 1 and field> value 2 and... and field> value N.
-- Field <all (value 1, value 2,..., value N) is equivalent to field <value 1 and field <value 2 and... and field <value N.

Example: Query Information about boys older than all girls.
SELECT * FROM student WHERE ssex = 'M' AND sage> 39;
SELECT sage FROM student WHERE ssex = 'F'; -- 21 \ 25 \ 39
SELECT * FROM student WHERE ssex = 'M' AND sage> ALL (SELECT sage FROM student WHERE ssex = 'F ');
-- Equivalent to SELECT * FROM student WHERE ssex = 'M' AND sage> 21 AND sage> 25 AND sage> 39;

8) any: use or to break down the expression.

-- Field> any (value 1, value 2,..., value N) is equivalent to: field> value 1 or field> value 2 or... or field> value N.

Example: -- Query Information about boys older than any girl.
SELECT * FROM student WHERE ssex = 'M' AND sage> ANY (SELECT sage FROM student WHERE ssex = 'F ');

4. Aggregate functions (grouping functions/group functions)

Common Aggregate functions include count () \ avg () \ min () \ max () \ sum (), and adding fields or field expressions to brackets.

Example: -- query the number of boys
SELECT count (*) FROM student WHERE ssex = 'M ';
-- Query the number of students with scores
SELECT count (distinct sid) FROM score;
-- Query the minimum age of a girl
SELECT min (sage) FROM student WHERE ssex = 'F ';
-- Query the average score of a student
SELECT avg (grade) FROM score WHERE sid = 's0001 ';
-- Query the name of the Oldest Student
SELECT * FROM student WHERE sage = (SELECT max (sage) FROM student );

5. query groups

Syntax format: group by field

Purpose: groups all data in the table according to the grouping field. Rows with the same grouping field values are merged into a record, that is, a group; fields other than grouping fields cannot be independently queried. They can only be queried through aggregation functions. Grouping fields can be multiple and separated by commas.

Example: -- query the number of boys and girls
SELECT ssex, COUNT (*) FROM student group by ssex;
-- Query the average age, maximum age, and minimum age of boys and girls
SELECT ssex, AVG (sage), MAX (sage), min (sage) FROM student group by ssex;
-- Query the number of courses selected
SELECT cid, COUNT (DISTINCT sid) FROM score group by cid;

NOTE: where occurs before a group and having occurs after a group. This means that the group function cannot appear after where as the condition value, but can appear after having.
Example: -- query the sid of students who pass all subjects.
SELECT sid FROM score WHERE grade> = 60 group by sid.
SELECT sid FROM score group by sid having min (grade)> = 60; -- groups students first, and sets the score of each student to be greater than or equal to 60.