Oracle ultra full SQL, details crazy

hello! Hello, everyone, I am [IT Bond], known as Jeames007. I have 10 years of DBA working experience and am a member of China DBA Union (ACDU). I am currently engaged in DBA and program programming . Thank you all! ️ ️ ️

preface

SQL is used by everyone, but it’s not SQL itself that is used to measure output, you need to use this tool to create other value.

1. Basic commands

1. Log in to SQL*Plus (as the system user) conn system/jeames@orcl conn sys/jeames@orcl as sysdba to disconnect — disc 2 Set linesize 100 set linesize 18 select * from global_name select * from global_name; 6, select * from dba_roles; SQL>select TABLespACE_NAME from dBA_TABLespaces; 8. Query all system permissions in Oracle. SQL>select * from system_privilege_map order by name; 9. Query all object permissions in the Oracle database SQL>select distinct privilege from dba_tab_privs; 10, SQL>select table_name from user_tables; 11, display all data dictionary views accessible to the current database SQL>select * from dict where comments like’%grant%’; 12. What role does a user have? SQL>select * from dba_role_privs where grantee=’SCOTT’; 13. What system rights does a user (role) have? SQL>select * from dba_sys_privs where grantee=’SCOTT’; 14. View the object permissions of a user (role). SQL>select * from dba_tab_privs where grantee=’SCOTT’; 15, Query information about all users in Oracle database SQL> select * from all_users; 16. Close the database SQL>shutdown 17. Start the database SQL>startup 18. 20. Open SQL> Set ServerOutput on

2. User management

1, create user watchdog SQL> create user watchdog identified by watchdog;

2. Change the password for user watchdog. SQL>password watchdog

3. Delete user watchdog. If the user to be deleted has created a table, cascade SQL>drop user watchdog cascade is required

4, run the Sql script (f:\wdd.sql) Sql > @d :\wdd.sql Sql >start d:\wdd.sql

5. Output the content to the specified file SQL>spool d:\bb. SQL SQL>select * from emp SQL>spool off

SQL>grant grant to ‘watchdog’; grant to ‘watchdog’;

grant create session to watchdog; grant create session to watchdog;

2, grant select on scott.emp to watchdog;

9, grant all on scott.emp to watchdog SQL>grant all on scott.emp to watchdog;

SQL>revoke select on scott.emp from watchdog;

SQL>grant select on scott.emp to watchdog with grant option; For system permission, add with admin Option

3. Manage user passwords

1. Account lock The specified cfmaster user can log in for a maximum of 3 times. The lockout duration is 2 days SQL>create profile lock_account limit failed_login_attempts 3 >password_lock_time 2; SQL>alter user cfmaster profile lock_account;

2. Unlock an account SQL>alter user cfmaster account unlock;

Create a profile file for xiaoming. The user is required to change its password every 10 days. SQL> Create profile myprofile limit password_life_time 10 password_grace_time 2; SQL>alter user xiaoming profile myprofile;

4, Delete profile file (lock_account) SQL>drop profile lock_account;

4. Table management

4.1 Creating a table

SQL>create table student( 2 xuehao number(4), 3 xingming varchar2(20), 4 sex char(2), 5 birthday date, 6 sal number (7, 2) 8 / SQL > 7) create table mytable (id, name, sal, job, deptno) as the select empno, ename, sal, job, deptno the from >scott.emp;

4.2 Modifying a table

1, alter table student add (CLASSID number(2));

2, alter table student modify (xingming varchar2(30)) alter table student modify (xingming varchar2(30));

3, alter table student drop column SAL; \

4, rename student to STU SQL> rename student to STU;

5, drop table stu SQL>drop table stu

4.3 Adding Data

1, insert into sc values(1,’ 01 ‘,’ 01 ‘,’03-8月-99’);

2, alter date default format SQL> ALTER session set NLS_date_format =’ YYYY-MM-DD ‘;

3, insert (xingming,sex) SQL>insert into student (xingming,sex) values(‘ xingming ‘,’ female ‘);

4, update student set birthday=’2001-05-22′ SQL>update student set birthday=’2001-05-22′;

5, modify multiple fields — modify sex to female’s birthday as “2014-05-21”, SQL> update student set birthday=’2014-05-21′,xingming=’ 男 ‘where sex=’ 男 ‘;

6, delete from student;

7, delete from stu where fullname=’ wangping ‘;

8. Set the rollback point SQL> savepoint a; SQL> rollback to a;

5. Query the table

5.1 Simple Query Commands

SQL> clear — clear screen SQL> desc dba_users — query table structure SQL> set timing on — SQL> select count () from student; SQL> select sal12 from imp; SQL> select sal*12+ NVL (comm,0)*12 “from imp; \

Null-value processing uses NVL function \

SQL> select distinct deptno,job from IMP; SQL> select ename, sal*12 from imp; – use the column alias SQL > select ename | | ‘is a’ | | job from imp; – how to connection string (| |)

5.2 where clause

1, SQL> select * from imp where hiredate>’1-1月-1982′;

2, SQL> select * from imp where sal>=2000 and sal<=2500;

3, SQL>select * from imp where empno IN (7369,7499,7876);

5.3 Like operator

1, SQL>select * from imp where ename like’ %’;

Select * from imp where ename = ‘__O%’; 2, select * from imp where ename = ‘__O%’;

5.4 Logical Operation symbol

query for employees whose salary is above 500 or whose position is MANAGER and whose initials are J? SQL> select * from imp where (sal >500 or job = ‘manager’)\ and ename like ‘J%’;

5.5 Order by sentence

1. How to display employees’ information in order of salary from lowest to highest? SQL> select * from imp order by sal;

2, select * from imp order by deptno, sal desc;

6. Complex query for tables

1. Data grouping

Show the highest and lowest salaries of all employees? . SQL> select max(sal),min(sal) from imp; Who’s the highest paid guy? SQL> select ename from imp where sal=(select max(sal) from imp); SQL> select * from imp where sal > (select avg(sal) from IMP); Show the average salary and highest salary for each department? SQL> select avg(sal), max(sal), deptno from imp group by deptno; The average salary of the department whose average salary is less than 2000 will be displayed in error. SQL> select avg(sal), deptno from imp group by deptno having avg(sal) < 2000;

2. Query multiple tables

SQL> select e.name, e.sal, d.name from imp e, dept d where e.deptno = d.deptno; Show the name of each employee, salary and salary level? SQL> select e.ename, e.sal, s.grade from imp e, salgrade s where e.sal between s.losal and s.hisal; Display employee’s name, employee’s salary, and the name of the department, sorted by department? SQL> select e.ename, e.sal, d.dname from imp e, dept d where e.deptno = d.deptno order by e.deptno;

3. Merge query

1) union This operator is used to get the union of two result sets. When used, duplicate rows are automatically removed from the result set. SQL> select ename, sal, job from imp where sal >2500 union select ename, sal, job from imp where job = ‘MANAGER’; 2) union all This operator is similar to union, but it does not cancel duplicate lines and does not sort. SQL> select ename, sal, job from imp where sal >2500 UNION ALL select ename, sal, job from imp where job = ‘MANAGER’; 3) Intersect uses this operator to get the intersection of two result sets. SQL> select ename, sal, job from imp where sal >2500 Intersect select ename, sal, job from imp where job = ‘MANAGER’; Minus uses the change operator to get the difference between two result sets. It only shows data in the first set and does not show data in the second set. SQL> select ename, sal, job from imp where sal >2500 Minus select ename, sal, job from imp where job = ‘MANAGER’;

How to become a SQL master PostgreSQL from start to start

You can like, collect, pay attention to, comment on me, there are database related questions to contact me or exchange yo ~!