Oracle learning notes 4 SQL commands (2): SQL operation language category, oracle learning notes

Oracle learning notes 4 SQL commands (2): SQL operation language category, oracle learning notes

SQL is classified into the following language categories
1. Data Definition Language (DDL)
Create, Alter, Drop

2. Data manipulation language (DML)
Insert, Select, Delete, Update

3. Transaction Control Language (TCL)
Commit, Savepoint, and Rollback

4. Data Control Language (DCL)
Grant, Revoke

 

Data Definition Language (DDL) the data definition language is used to change the database structure, including creating, changing, and deleting database objects. The data definition language commands used to manipulate the table structure include: 1. create table 2. alter table 3. truncate table 4. DROP TABLE

-- Create a Weibo web program to create a Weibo user table

-- User table information account, password, nickname, and gender

Create table blog_user (User_name varchar2 (20) not null, -- useruser_pwd varchar (20) not null, -- User_age int default 15, -- Age User_sex char (2) default 'male', -- Gender User_alias varchar (1) -- nickname)

 

Create table blog_article(Art_id int not null,Art_author varchar2(20) not null,Art_content varchar2(100),Art_time date)

Add a primary key or foreign key to the table

1. Create a primary key constraint when creating a table

(1) No Name:

create table student (studentId int primary key not null, studentName varchar(8), age int)；

(2) There are naming:

create table student(studentId int, studentName varchar(8), age int, constraint yy primary key(studendId))；

2. Delete the existing primary key constraints in the table
(1) No Name is available

select * from user_cons_columns;

Find the primary key name in the student table. The primary key name is SYS_C002715.

alter table student drop constraint SYS_C002715;

(2) named

alter table students drop constraint yy;

3. Add a primary key constraint to the table

alter table student add constraint pk_student primary key(studentid);

 

4. Add a foreign key constraint to the table

alter table table_A add constraint FK_name foreign key(id) references table_B(id);

 

Modify Table

-- Alter table blog_user Modify user_alias varchar (10) -- Add an Alter table blog_user Add birth date column -- Add a new column -- delete an alter table Name drop column; -- set username as primary Key: add constraint name constraint type Alter table blog_user Add constraint Pk_user_name primary Key (user_name) -- check constraints, the age cannot be negative. Alter table blog_user Add constraint ck_age check (User_age> 0 and User_age <150) -- delete a constraint; Alter table blog_user_age Drop constraint name; -- Add a foreign Key Alter table blog_article Add constraint fk_user_name_art_name foreign Key (Art_author) References blog_user (user_name) -- modify the table column name alter table name rename column old COLUMN name TO new COLUMN name -- modify the TABLE name alter table name rename to new TABLE name;

 

Delete table

-- Normal deletion (available in the database recycle bin) drop table name -- the command to permanently delete a table is as follows: drop table name purge

 

 

Data manipulation language (DML) the data manipulation language is used to retrieve, INSERT, and modify data manipulation languages. It is the most common SQL command. The data manipulation language Commands include: SELECT INSERT UPDATE DELETE pseudo columns.

• The pseudo column in Oracle is like a table column, but it is not stored in the table
• Pseudo columns can be queried from tables, but their values cannot be inserted, updated, or deleted.
• Common pseudo columns include ROWID and ROWNUM.
• ROWID is the address of the row stored in the table. This address can uniquely identify a row in the database. You can use the ROWID pseudo column to quickly locate a row in the table.
• ROWNUM is the number of rows in the returned result set. You can use it to limit the number of rows returned by the query.

Use of pseudo Columns

Select rownum, rowid,tablename.* from tablename;

 

 

DML: data manipulation language, data addition, deletion, modification, and query operations

Insert delect select update

-- Create a table with data from the Create table emp2 as select empon, ename, job, salfrom emp -- Select: query the Select column name from table name, other queries [where condition group by column name having clause order by column name] -- queries all information of the entire blog_user table Select * from blog_user; -- queries only the name and password, nickname: Select user_name, user_pwd, user_alia from blog_user; -- name the column Select user_name as uanem, user_pwd as upwd from blog_uesr-calculation column: columns are calculated (and do not exist) Select sa1, sa1 * 2 as double salary from emp; -- double salary is calculated -- remove duplicate data: remove Select distinct job from emp from identical duplicate data;

 

Conditional query: where Condition

Select * from emp where job = 'salesman'-all sales personnel -- Obtain the system's date format Select sysdate from dual; -- June-15 Select * from emp where hiredate <'1-June 22-81 '>;-hired from January 1

 

Multiple query conditions: and or combination

-- How to determine if the user entered the correct username and password Select * from blog_user where user_name = 'admin' and user_pwd = '123' Select * from all records with scores between 80 and 123 table name where column name between 80 an 100; the results are 80.90/100 records respectively, and are sorted by the scores. Select * from table name where score in (80, 90,100) score = 80 or score = 90 or score = 100;

 

 

Fuzzy search

Like condition;
% _ ('%' Indicates 0, 1 or more characters, '_' indicates one character) indicates a wildcard, representing any content, representing any content

-- Find all the persons surnamed Chen Select * from blog_user where user_name like 'chen % '-- Select * from blog_user where user_name like' _ A % 'For the third letter select * from blog_user where user_age in (20, 25 );

 

Insert data

-- Specify the column name: insert into Table Name (column name 1, column name 2 ,......) Values (value 1, value 2 ,......); Insert into blog_user (user_name, user_pwd, user_sexy) values ('old _ 2', '2013', default) -- unspecified column name: insert into table name values (value, value, ......); Several values are required for several columns -- Insert multiple statements into blog_user (user_name, user_pwd) select ename, empno from emp at the same time -- data comes from other tables. Pay attention to the data type and number.

 

 

Modify data

 

Udpate table name set column name = new value, column name 2 = new value ...... Where condition

 

Update blog_user set user_sexy = 'female 'where user_name = 'Jones'; -- modify multiple columns Update blog_user set user_age = 20, user_alias = 'boss 'where user_name = 'King '; -- fuzzy match modify data Update blog_user set user_age = 20 where user_name like 'J %'

 

 

Delete data

-- Delete table name where condition Delete blog_user where user_name = 'old _ 2' -- Delete multiple rows Delete blog_user where user_age <18; Delete bolg_user where user_age is null; -- Delete all data whose age column is null: delete blog_user where user_age in not null; -- Delete non-empty Truncate table names; -- efficiency is higher than delete

 

 

The transaction control language (TCL) is the smallest unit of work. As a whole, the transaction is successfully or fails. transaction control statements used for transaction control include: COMMIT-COMMIT and end transaction processing ROLLBACK-Undo the finished work in the transaction SAVEPOINT-mark the point where the transaction can be rolled back

Commit: Submit and save the transaction.
Rollback: rolls back the transaction and returns the status before the start of the transaction.
Savepoint: stores the transaction point (similar to the archive), and the transaction can be rolled back to this point.

Delete blog_user where user_age <22; -- Delete Savepoint del22 for all users younger than 22 years old; -- save the transaction and name it del22Delete blog_user; -- Delete all data Rollback to savepoint del22; -- returns all transactions. Only the data after the last point of storage is returned.

 

Data Control Language (DCL) the data control language provides users with permission control commands. commands used for permission control include GRANT permissions, REVOKE, and REVOKE granted permissions.

 

The GRANT command can be used to assign permissions or roles to users.

Grant connect to martin; -- the CONNECT role allows users to connect to the database and create database objects.

Grant resource to martin; -- The RESOURCE role allows users TO use the bucket in the database.

Grant create sequence to martin; -- this system permission allows users to create sequences in the current mode. This permission is included in the CONNECT role.

  Grant MARTIN the permission to operate the TEST table object.

Grant select on test to martin; -- allows the user TO query records in the TEST table

Grant update on test to martin; -- allows users to update records in the TEST table.

Grant all on test to martin; -- allows users TO insert, delete, update, and query records in the TEST table.

 

The alter user command can be used to change the password.

Alter user martin identified by martinpass; -- modify the password of a MARTIN USER

 

The drop user command is used to delete a USER.

Drop user martin cascade; -- delete the martin user mode

 

Revoke and revoke permissions

Revoke permission name [on table name] from role name;