Oracle database language-Structured Query language SQL

I. Data definition language DDL

1. Create tablespace : creat tablespace lyy datafile ' c:/app/lyy.dbf ' SIZE 10M; (Create a 10M tablespace and store it in the C-drive app folder)

Delete Table space: Drop tablespace lyy;

2. Create User and empower: creat user Lyy profile default identified by 123456 default tablespace lyy temporary tablespace temp AC COUNT UNLOCK; (Create user lyy, the default password is 123456, the default tablespace is lyy, temp table space is temporary, the account is open)

GRANT CONNECT to Lyy;
Grant RESOURCE to Lyy; (Give users lyy Connect and RESOURCE permissions)

3. Create a table

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

Example: Create the following three tables:
--Student table student: SID, Name Sname, gender ssex, age sage, telephone sphone
--Curriculum Course: Course Number CID, course name cname, teacher name Tname, class Chour
--Results Table score: Score number SCID, number SID, course number CID, fraction grade

In Oracle, the full name of the table is the schema name. Table name; The scheme name is the user name, the scheme name is not written, indicating that the scheme name is the current user itself;

--Create student tables

creat TABLE Student (
Sid CHAR (10),
Sname VARCHAR2 () not NULL,
Ssex CHAR (1),
Sage number,
Sphone INTEGER
);

--Create a curriculum

creat TABLE Course (
CID CHAR (10),
CNAME VARCHAR2 (50),
Tname VARCHAR2 (50),
Chour number
);

--Create a score table

creat TABLE Score (
SCID CHAR (10),
Sid CHAR (10),
CID CHAR (10),
Grade number
);

Delete tables: drop table student;

4. Constraint conditions

Constraints are added by the user to guarantee the entity integrity and referential integrity of the data stored in the database;

There are generally five types of constraints:

--PRIMARY KEY constraint: Primary key, which requires the value of the field defined as the primary key to be unique and non-nullable;
constraint P1_sid primary KEY (SID)--defines the SID as the primary key.
--FOREIGN KEY constraint: Foreign key, requires that the value of the field defined as a foreign key must be derived from the value of the referenced field, the Foreign key field and the referenced field, the name can be different, but the data type and length must be consistent;
constraint f1_sid foreign key (SID) references student (SID)--defines the SID as a foreign key and the value from the SID of the student table
--Unique constraint: Unique, the requirement has uniqueness;
Unique (SID,CID)-Defines the uniqueness of a combination of SIDS and CID.
--nonempty constraint: NOT NULL, required to have value;
sname varchar2 () NOT null--Define SNAME field non-empty
--Check constraint: Check, the user can customize the value of the field according to the needs of the business.
constraint c1_ssex Check (ssex in (' m ', ' F '))--Defines the check constraint, which requires that the value of the Ssex field must be M or F

5. Modify the table alter

ALTER TABLE SCOTT. TEST RENAME to test1--Modify table name
ALTER TABLE SCOTT. TEST RENAME column name to NAME1--Modify Table list name
ALTER TABLE SCOTT. TEST MODIFY NAME1 Number (20)--Modify field type
ALTER TABLE SCOTT. TEST Add ADDRESS VARCHAR2 (40)--Add Table column
ALTER TABLE SCOTT. TEST Drop NAME cascadeconstraints--Delete table column

Second, data manipulation language DML

DML is a data manipulation language that can be used to perform three operations: Insert inserts, update updates, delete deletes, and DML statements, which are transactional operation statements that require commit confirmation and rollback rollback operations to be finalized. If you do not confirm or roll back, the current table will be locked, resulting in operations such as DDL for that table will fail.

1.insert Inserting data

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

NOTE: In SQL statements, in general, in addition to the worthy part (data), the other parts are case-insensitive, the data part, the character type needs single quotation marks, the other types do not need single quotation marks, in this format, to ensure that the value of the number, type, length, order and table fields are consistent.

Example:SELECT * from student;
INSERT into student VALUES (' S001 ', ' Zhang San ', ' M ', 30,13089247856);

The syntax format 2:insert into indicates (field name 1, field Name 2, ..., field name N) VALUES (value 1, value 2,......, value n);

Note: The corresponding relationship between the destined value and the field (in order), the advantage is that it can be selectively inserted according to its own value and field correspondence.

Example: INSERT into student (sname,ssex,sid,sage) VALUES (' John Doe ', ' F ', ' s0002 ', +);

2.update Update Data

Syntax format: Update table name set-assignment expression [where condition]

Note: Update itself is a column-action statement that, without conditions, operates on the entire column of data in the table, and if a condition is added because the condition is a row, the column that corresponds to the selected row is the action.

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 Delete Data

Syntax format: delete from table [Where condition]

Note: Delete is a row operation, the minimum unit of action is a record, remember that Delete does not have an * number, and if you do not add a where condition, all records in the entire 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: is a DDL statement, but can also achieve the effect of deleting data from an entire table

Syntax format: TRUNCATE TABLE name

Example: truncate TABLE score;

Third, the data query language DQL

1. Select is used to query the data to get the information that the user wants.

Syntax format:

Select: the content of the query, is the required keyword, followed by the content to be queried, usually field-based, can be a constant, an expression (including fields).
--from: The source of the query content is a required keyword, the source can be a table, multiple tables, other query statements, etc.
--where: Conditions, optional keywords, generally used to specify the conditions of the query, that is, to filter the data;
--group by: field, optional keywords, used to implement the group query;
--having: Conditions, optional keywords, are used to filter the results after grouping;
--order by: field, optional keyword, used for sorting operations;

Note: Queries without conditions, that is, column queries, can be a field, multiple fields, constants, or expressions in a table.

Example:

--Query all contents of student table
SELECT * from student;
--Check the names and ages of all students
SELECT sname,sage from student;
--Query constants
SELECT sname,sage, ' teacher 1 ' from student;
--Query content is an expression
SELECT sname,sage,sage+1 from student;

2. | | used to implement concatenation of strings and variables.

Example:SELECT sname,sage,sage| | ' Years old ' from student;
SELECT sname,ssex from student;
SELECT sname,ssex,case when ssex= ' M ' Then ' Men ' ELSE ' female ' END from student;

3. Conditions

Plus query conditions are used to filter the data, the basic unit of filtering is the line, the common keywords are: (>/</=/>=/<=/!=/<>/between. and.. /like/in/all/any/exists/not exists and so on; connectors with multiple criteria are: and\or\!

Example:

--Query information for all students older than 28 years of age
SELECT * FROM student WHERE sage>28;
--Query information for all students aged 28 or older;
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 who are s0010 than before.
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; is a separate, complete field that cannot be split.

example:--Query for student information between the ages of 23-28 years (inclusive).
              select * from student where sage>=23 and sage<=28;
              select * from student where sage between 23 and;
       -Query For information about girls aged between 23-28 years old (inclusive).
              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: the implementation of the fuzzy query, in general will be combined with two special symbols used,% wildcard character, matching any number of characters; _ means match one character.

Example:------query all students with surname Zhang's information.
SELECT * FROM student WHERE sname like ' Zhang% ';
--Query all surname Zhang, the name of a total of 2 characters of the classmate information.
SELECT * FROM student WHERE sname like ' Zhang _ ';
--Query all surname Zhang, the name of a total of 3 characters of the classmate information.
SELECT * FROM student WHERE sname like ' Zhang __ ';
SELECT * FROM student WHERE sname like '% Zhang ';

3)in: Is an enumeration usage, field in (value 1, value 2, ...), value N) equivalent to field = value 1 or field = value 2 or ... or field = value N.

Example:-Query For information about students aged 27 or 28 years old.
SELECT * FROM student WHERE sage=27 OR sage=28;
SELECT * FROM Student WHERE Sage in (27,28);

4) Distinct: Used to modify a field, to represent a unique query, to remove duplicate values.

Example:--Query The student's number for all the classes that have been selected.
SELECT Sid from score;
SELECT DISTINCT Sid from score;

5) Aliases : Can be applied to the content and source of the query.

Syntax Format: Formerly an as Alias, in general, 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; --there is no problem with the grammar, it is problematic from the application level. The student's name was queried, but the result was that the column name was changed to Sage.

6) Nesting

In an SQL statement, a query statement can be used in a nested set. Nesting is implemented by parentheses (). The result of each query statement is itself a table, a collection of values that can be nested in a value or source part.

--Check the names of all students who have enrolled in the Oracle course.
Select sname from student where SID in (select SID from Score where cid= (selectcid from course where cname= ' Oracl E '));

--Query the name of the course selected by a student. (already student name)
Select CNAME from course where CID in (select CID from score where SID in (select SIDs from student where Sname= ' Zhang Three '));

7)all: use and to decompose an expression

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

Example: query for 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 decompose an 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 For information about boys older than any other girl.
SELECT * FROM student where ssex= ' M ' and Sage>any (select Sage from student where ssex= ' F ');

4. Aggregation functions (grouping functions/group functions)

Common aggregation functions are: count () \avg () \min () \max () \sum (), parentheses in a field or field expression.

Example:--Query the number of boys
SELECT Count (*) from student WHERE ssex= ' M ';
--Check the number of students with grades
SELECT count (distinct SID) from score;
--Check the minimum age of girls
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. Group queries

Syntax format: GROUP by Group Field

Function: means that all the data in the table will be grouped into grouped fields, the rows with the same values of the grouped fields will be merged into one record, or one group, and the fields other than the group fields can no longer be queried independently, only through the aggregation function to implement the query, the grouping fields may be multiple, 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, minimum age for boys and girls
            select ssex,avg (Sage), MAX (Sage), min (Sage)  from Student group by Ssex;
        --Query the number of selected courses for each course
            select Cid,count (DISTINCT sid)  from score  Group by CID;

Note: Where is occurring before grouping, having is occurring after grouping, meaning that the grouping function is not directly the value of the condition appears after the where, but can appear after having.
Example:--Query the SID of a student who has passed all subjects.
SELECT SID from Score WHERE grade>=60 the GROUP by Sid;--the wrong wording, the realization of any subject passing students.
SELECT SID from Score GROUP by Sid have MIN (grade) >=60; --Group BY student first, set conditions each student's minimum score is greater than or equal to 60.

Oracle database language-Structured Query language SQL