--Create table/*create table test_table (STUDENT VARCHAR2 ($), SUBJECT VARCHAR2 (+), GRADE number) Tablespace Syste M pctfree pctused Initrans 1 Maxtrans 255 storage (initial 64K minextents 1 Maxextents Unlimited); *//*insert into test_table (student, Subject, Grade) values (' Zhang San ', ' languages ', ') insert into test_table (student, Subject, G Rade) VALUES (' Zhang San ', ' Math ', ' Max ') insert into test_table (student, Subject, Grade) values (' Zhang San ', ' English ', ') insert into test_t Able (student, Subject, Grade) VALUES (' John Doe ', ' language ', ' + ') insert into test_table (student, Subject, Grade) VALUES (' John Doe ', ' Math ', "INSERT into test_table (student, Subject, Grade) VALUES (' John Doe ', ' English ', ' go*/--oracle ') the Decode () function uses and then sums the accumulated sum (sum section) Select T.student as name, sum (decode (t.subject, ' language ', grade,null)) language, sum (decode (t.subject, ' mathematics ', grade,null)) Math, sum ( Decode (T.subject, ' English ', Grade,null)) English from the test_table T Group by student--static SQL, refers to the subject only Chinese, maths, English this course. Select Student name, max (case subject when ' language ' thEn grade else 0 end) language, Max (case subject when ' math ' then grade else 0 end) Math, Max (case subject when ' English ' then grade Els e 0 end) English from test_table Group by student/* name Chinese maths English-------------------------------- -----------Lee 4,100 80 90 380 70 60*/--Dynamic SQL, refers to subject more than Chinese, maths, English, this course. Under SQL Server Test declare @sql varchar (8000) Set @sql = ' Select student as ' + ' name ' Select @sql = @sql + ', max (case Subject When "+ Subject +" then grade else 0 end) [' + Subject + '] ' from (select distinct Subject from test_table) as Aset @ sql = @sql + ' from test_table GROUP by student ' EXEC (@sql) A HAVING clause can filter the result of a group function or a grouped message, and write a fuzzy query and sort the select username after the GROUP BY clause , Userno,userpassword from UserInfo t where username like '%a% ' GROUP by Username,userno,userpassword have (Userno) ; The order by username desc Quantity Relationship: Many-to-one relationship-a-to-many relationship a one-to-many relationship first paradigm, all attributes must be single-valued, that is, attributes represent only a single meaning. (Records can be duplicated, without any restrictions) the second paradigm, where attributes are required to be unique and non-null (records are not repeatable, but data can be redundant). The third paradigm,A non-primary property can only depend on the primary property and cannot depend on other non-primary properties. (Solve data redundancy problem) only one long type CLOB storage large text object BLOB storing large binary objects Oralce supports cascading deletions and does not support cascading updates
Oracle row-to-column SQL