-- Build table 
-- keyword : create
-- usage :
/*
create table table_name

lie1_name data type Is it empty ,
lie2_name data type Is it empty ,
lie3_name data type Is it empty ,
……………………………………
); */
create table infos
(
stuid varchar2(7) not null , stuname varchar2(10) not null , gender varchar2(2) not null , age number(2) not null , seat number(2) not null , enrolldate date , studress varchar2(50) , classno varchar2(4) not null
) ; -- Adding constraints -- Add primary key
alter table infos add constraint pk_infos primary key(stuid) ; --check constraint
-- The gender can only be male or female
-- effect : Conditionality
alter table infos add constraint ck_infos_gendertest check(gender=' male ' or gender=' Woman ') ; -- Unique constraint
-- effect : So that the column does not repeat
alter table infos add constraint un_stuname unique(stuname) ; -- Date format conversion
select to_char(sbirthday , 'yyyy-mm-dd hh24:mi:ss') from student ; -- Query system time
select sysdate from dual ; -- dual Pseudo column , There's only one row and one column of data -- Absolute value function
--abs() In brackets are numeric or can be implicitly converted to numeric
select abs(122),abs(-123),abs(0) from dual ; -- Data addition -- insert into Table name valus
insert into student(sno,sname,ssex) values ('',' Xiao Ming ',' male ') ; -- Add date
-- Type conversion required to_date
insert into student values(to_date('1990/2/3','yyyy/mm/dd')) ; -- Commit transaction commit ; -- Roll back the transaction rollback ; -- Replication of data -- insert into New table name select * from Table name to copy ; -- Query data to remove duplication
--distinct
--select distinct Name from Table name ;
-- * Represents all field information
-- != or <> Means not equal to
--|| Indicates splicing -- Modify table
--update Table name set Conditions or additions
update student set sclass = '' where sname=' Xiao Ming ' ; update student set sclass = '' where sclass is null ; -- Delete table
--delete
delete student where sname = ' Xiao Ming ' ; -- Quickly clear a table
--truncate
--truncate table Table name ; -- Sort --order by asc/desc positive sequence / In reverse order
-- Place on where After the condition -- grouping
--group by Name -- Query records between two values
-- The two methods select degree from score where degree >70 and degree < 85 ;
--between It must be followed by a small number
select degree from score where degree between 70 and 85 ; -- Inquire about be located 66,77,88 The results in select degree from score where degree=66 or degree=77 or degree=88 ; select degree from score where degree in (66,77,88) ; /*
Sort usage order by asc positive sequence , Default , Not to add desc Descending ; grammar : order by Field name 1 asc/desc , Field name 2 asc/desc ... The first field has a higher priority , Press fields first 1 Sort , Then press field 2 Sort If there are conditionals , Put it in where Statement behind */ -- Remove duplicate records from query data
--distinct
-- usage
--select distinct lie_name from biao_name ;
-- example
select distinct depart from teacher ; -- Query between two data -- Comparison operations example :
select * from score where degree > 60 and degree < 80 ; -- keyword :between Small value and Great value . example :
select * from score where degree between 60 and 80 ; -- Built in functions -- Aggregate functions Returns a single value
--- Number of records ( Column name content is not empty , General statistics primary key column ) select count(*) from student where sclass = ''; -- The results are summed up select sum(degree) Total score from score ; -- Average score select avg(degree) Average from score ; -- The highest select max(degree) The highest from score ; -- Lowest score select min(degree) Lowest score from score ; -- Pseudo column
-- rownum Built in columns
select * from score where rownum = 1 ; -- The average score of each course is greater than 80 Of
-- First cno grouping , stay Built in functions
select cno , avg(degree),count(cno) from score group by cno having avg(degree) > 80 ; select * from (select cno , avg(degree) a,count(cno) from score group by cno ) where a> 80 ; -- Vertical query
-- The number of columns queried between two tables should be the same , The data type of the corresponding column number should be the same select sname,ssex,sbirthday from student
union
select tname,tsex,tbirthday from teacher ; -- Fuzzy query -- Check the information of students surnamed Wang
select * from student where sname like ' king %' ;
--% Any number of characters
-- An underline represents a character _ -- Built in functions -- Lower limit select floor(123.12) from dual ; -- Upper limit value select ceil(123.12) from dual ; -- rounding select round(123.89) from dual ; -- The number of reserved digits is rounded select round(125.456,2)from dual ; -- Just give up
-- effect : Format data , Unified format select trunc(123.456) from dual ;
-- It doesn't round
select trunc(123.456,2) from dual ; -- The absolute value select ABS(-23) from dual ; -- Mod , modulus select mod(78,69) from dual ; -- Calculate string length select tname,length(tname) from teacher ; -- Go to space
-- Space before and after
select trim(' a b cf ') from dual ;
-- Go to the front space ltrim
select ltrim(' sdf ') from dual ;
-- Go after the space rtrim
select rtrim(' sdf ') from dual ; -- Search and replace select replace(' s df g ',' ') from dual ; select sname,replace(sname,' king ',' Li ') from student ; -- Find string ( Space is also a ), from 1 Start , No return found 0 select instr('sd wef','w') from dual ; -- Intercepting string
--3 Number one starts to intercept
--5 Interception length
select substr('sdfga fgh',3,5) from dual ; select sname,substr(sname,1,1) || ' classmate ' from student ; --null Value processing select nvl(degree,0) from score ; select nvl2(degree,55,0) from score ; select t.*,decode(ssex,' male ','',' Woman ','') from student t ; -- Returns the login name of the current user select user from dual ; --- Gets the current value of the sequence
select sq_test.nextval from dual ; select sq_test.currval from dual ; insert into weadafa values(sq_test.nextval,' Wang Wu ') ;
commit ;
select * from weadafa ; -- View create or replace view v_score as
select t.*,s.sname,c.cname
from score t,student s,course c where t.sno=s.sno and t.cno=c.cno ;

Oracle —————— Build table 、select、 More articles on view

  1. 5.oracle Create a primary key when creating a table , Foreign keys , notes , constraint , Indexes

    5.oracle Create a primary key when creating a table , Foreign keys , notes , constraint , Indexes 1 -- Primary key )); ) ,constraint aba_pr primary key(id,name1)); -- Foreign keys )); -- complex ...

  2. oracle Build table Primary key self increasing sequence /////

    oracle  Build table   Primary key self increasing sequence (2011-10-12 11:59:22) Reprint ▼ label : gossip classification : oracle SQL> create table sms_activity(  2   ...

  3. Oracle Tips for setting up a table SQL error : ORA-00904: : Invalid identifier

    Oracle Tips for setting up a table : Error reporting :SQL error : ORA-00904: : Invalid identifier 00904. 00000 -  "%s: invalid identifier"*Cause: ...

  4. PowerDesigner Generated ORACLE Remove the double quotation marks from the object in the table creation script , Set big 、 A lowercase letter

    original text :PowerDesigner Generated ORACLE Remove the double quotation marks from the object in the table creation script , Set big . A lowercase letter If you want to CDM Lieutenant general Entity All identifiers of are set to the specified case , You can set it like this : open cdm Under the circumstances , Get into T ...

  5. oracle Display when creating the table ORA-00984: You are not allowed to

      oracle Display when creating the table ORA-00984: You are not allowed to CreationTime--2018 year 7 month 19 Japan 16 spot 10 branch Author:Marydon 1. Scene display Use plsql Build table , Report errors ...

  6. oracle Display when creating the table ORA-00904 Invalid identifier

      oracle Display when creating the table ORA-00904 Invalid identifier CreationTime--2018 year 7 month 19 Japan 16 spot 03 branch Author:Marydon 1. Scene display Use plsql Build table , Report errors Field show ...

  7. Oracle Build table

    1.oracle A variety of data structures in a database : 1. Table structure             Store the data 2. View Bytes of data in one or more tables 3.sequence It is mainly used to generate primary key values 4.index Improve retrieval performance We ...

  8. SQL SERVER Generate ORACLE Create table script

    /****** Object: StoredProcedure [dbo].[GET_TableScript_ORACLE] Script Date: 06/15/2012 13:07:16 **** ...

  9. Oracle Creating tables, inserting data, etc

    Oracle The management of the table of : Naming rules for table names and columns , See Database naming rules . Must start with a letter . The length cannot exceed 30 Characters . Out of commission Oracle Reserved words . Only the following characters can be used column_name-Z, ...

Random recommendation

  1. turn 10 The best Node.js Of MVC frame

    10 The best Node.js Of MVC frame oschina  Published on : 2014 year 02 month 24 Japan (33 review ) Share the :    Collection +322 Node.js It's based on Chrome JavaScri ...

  2. JQueryMobile + PhoneGap Summary of experience

    1. pageinit & pageshow JQM The official Manual of the company highlights the use of $(document).bind(‘pageinit’) Instead of $(document).ready(). But when you need to know something ...

  3. cojs Knight Problem solving report

    not have understood until then knight Ninet , Instead of Cornett This problem is obviously a data structure problem , I carried it CF Last question CF It's very long , And it's online , But I can't understand So I thought of a way A It fell off , The only drawback is that it's a little cumbersome ...

  4. Don't stop MySQL There are two ways to add a slave library to a service 【 Reprint 】

    Now the production environment MySQL Database is a master and a slave , Due to the increasing traffic , So add another slave . The premise is not to affect the use of online business , That is to say, it can't be restarted MySQL service , To avoid other situations , Choose to operate during the peak period of low website traffic . ...

  5. One a day JavaScript example - Prevent repeated form submission

    <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content ...

  6. 1.cocos2dx 3.2 Environment building

    1         Software required jdk-7u25-windows-i586.exe python-2.7.8.amd64.msi cocos2d-x-3.2.zip apache-ant-1.9.4.zi ...

  7. TensorFlow- Google deep learning library Image processing module

    Module: tf.image This article mainly introduces TensorFlow Deal with the picture , This module is the same as the file mentioned before I/O The treatment is also mainly from python From here . Through official documents , We learned that this module mainly includes the following ...

  8. ITSA(IT Strategy and Architecture) Methods to introduce

    Architecture Capability – At a Glance Architectural coherence part1 Architectural coherence part2 SA ...

  9. Talking about linux Thread switching problem

    http://www.jb51.net/article/102059.htm The processor is always in one of the following states : 1. Kernel mode , Run in process context , The kernel represents the process running in kernel space 2. Kernel mode , Run in interrupt context , Inside ...

  10. BZOJ2001 HNOI2010 urban construction ( Segment tree divide and conquer +LCT)

    A very obvious idea is to divide the edge into line segments according to the time period , use lct maintain MST. The theoretical complexity is O((M+Q)logNlogQ), The real constant explodes T Adult dog . I can't write any more . #include<iostream> ...