Shulou(Shulou.com)06/01 Report--

DB2

Oracle

Infomix

MySQL

Sybase

SQLServer

1 what is a database

Data needs to be persisted.

(disk file)

Database (database management system) is a software

Safe, reliable and efficient

Database server, data center

Relational database

A database that uses tables to store data

SQL General Operation language of Relational Database

There are some differences in SQL between different databases.

NoSQL Not only SQL

Mango DB.

T-SQL

PLSQL

Classification in SQL

DDL (Data Definition Language) data definition language

Create;drop...

DML (Data Maniplation Language) data manipulation language

Insert;delete;update...

TCL (Transaction Control Language) object control language

Begin transaction;commit;rollback....

DQL (Data Query Language) data query language

Select...

DCL (Data Control Language) data control language

Grant;revoke...

Install Oracle (DBA/OP)

Create a library (DBA/OP)

Create user (DBA/OP)

Log in to database (RD)

Access database (RD)

Document

Table name

Field name, type, description

The create statement creates a table that specifies how many columns the table has, the column name, and the type of each column

Type:

Digital number (m) cannot exceed m

A total of m places in number (m), with decimal places in n places

The string varchar2 (m) cannot exceed m

(variable length string version 2)

Date date

There is a default format to modify

Must conform to this format when inserting a date

You can use the to_date function for dates (but the data saved in the table is still formatted by the system)

The format defined by the first parameter itself

To_date ('2016-10-10 mm Murdd`)

The second parameter indicates the custom format

CREATE TABLE table_name (column_name column_type); / / create a table create table emp (id number (10), name varchar2 (50), hire_date date, salary number (8Power2)); / / delete the table DROP TABLE tablename; drop table emp;// to view the structure of the table (can only look at the structure of the table, not data) DESC tablename Desc emp// add row inserts to the table can be specified as null columns that are not inserted are null unless there is a default value INSERT INTO tablename (column_name,column_name,column_name) values (xxx,xxx,xxx,xxx); insert into emp (id,name,hire_date,salary) values ('2016-10-10 grammmerry MMUDT'), 123123.12) ORinsert into emp values ('2016-10-10') (123123.12); / / some rows in the table are modified to UPDATE table_name SET field1=new-value1;UPDATE table_name SET field1=new-value1 according to specific conditions, field2=new-value2 [WHERE Clause] update emp set salary=200 where id=101; deletes rows in the table [WHERE Clause]; delete from emp where id=101;SELECT * FROM tablename;SELECT * FROM tablename [WHERE Clause] Select * from emp;select * from emp where salary > 100th select name,salary from emp where salary > 100th

Oracle character operation:

There are differences between the lower version of varchar and varchar2, the same as the higher version.

The difference between char and varchar2:

Char is fixed length char (20), no matter how much is used, it is 20 space.

= "refers to storage space.

Varchar2 is a variable length varchar2 (20) according to the actual given space

The default is to store 20 English characters.

Length ()

Varchar2 Chinese:

How many characters a Chinese occupies depends on the encoding you use.

Oracle doesn't use Unicode.

When designing tables, we should pay attention to the general length of Chinese * 3

View oracle's character encoding userenv ('language')

Select userenv ('language') from dual

The dual table has no practical meaning, it is a pseudo table for us to use with some functions.

The nvarchar2 type is just how many characters have nothing to do with encoding.

Maximum length of char and varchar2

2000 char

4000 varchar2

String concatenation | | or use the conncat (mforce n) function

'aa' | | 'bb'

Create table class (xing nvarchar2 (50), ming nvarchar2 (50)); insert into class values ('Zhang','3'); insert into class vlaues ('steve','steve') select xing | | ming from class;select xing | |'. | | ming from class;select concat (xing,ming) from class;select concat (concat (xing,'.'), ming) from class

Trim removes the space before and after trim (String)

Ltrim left space

Rtrim right space

Lpad padding characters on the left to the specified length lpad (String,50) defaults to spaces

Rpad fills characters on the right to the specified length rpad (String,50,'*')

Lower lower case lower (String)

Upper capital

Initcap initials are capitalized

Length to find the length

Substr intercepts the string substr (String, start position) to the end

Substr (String, starting position, how many to take)

Substr (String,-20) from back to top 20

Starting with 1.

Instr looks for a string in a string

Return to the location found for the first time instr (String1,String2) find String2 in String1

Starting at 1, instr (String1,String2,5) looks for String2 in String1, starts with the fifth

Instr (String1,String,5,2) looks in String1 for the second String2 found from the fifth.

Create table emp (id number (10), name varchar2 (50), hire_date date,salary number (8Magazine 2)); desc emp;insert into emp (id,name,hire_date,salary) values (101 select from emp;select * from emp where id=102;select name,salary from emp where salary > 1000 update update date) Update emp set salary=99999 where id > 2000 update emp set salary=99999999999 where id=101;delete from emp where id= 010: delete from emp where id=101; drop table emp;create table INFO_COST (ID number (11) primary key not null, COST_DESC VARCHAR2 (200), BASE_DURATION number (11), BASE_COSR number (11je 2), UNIT_COST number (11je 2)); insert into INFO_COST (ID,COST_DESC,base_duration,base_cosr,unit_cost) values (1m 'bag 20hrs', 20J 2.45) 0.30) Insert into INFO_COST (ID,COST_DESC,base_duration,base_cosr,unit_cost) values (2dint 'Bao 40hrs', 40JEI 3.45pl 0.30); insert into INFO_COST (ID,COST_DESC,base_duration,base_cosr,unit_cost) values (3Jing 'bao 100hrs', 100Ji 4.45g 0.30); insert into INFO_COST (ID,COST_DESC,base_duration,base_cosr,unit_cost) values (4Jing 'bao 200hrs', 200Jing 5.45jue 0.30) Insert into INFO_COST (ID,COST_DESC,base_duration,base_cosr,unit_cost) values (5 minus' normal rates', null,null,0.20); insert into INFO_COST (ID,COST_DESC,base_duration,base_cosr,unit_cost) values (6 mini 'prepaid', null,10,null); update INFO_COST set unit_cost= unit_cost+ (unit_cost*10) where idsysdate

The last day of the month where last_day calculates

Months_between the difference between one date and another is several months.

Least a date and a given date which time is earlier

Return to the small

Greatest A date is nearer to a given date.

Return to the large

Create table foo_6 (birthday date); insert into foo_6 values (to_date ('1990 Universe 09 / 09)); insert into foo_6 values (to_date (' 1890 / 09 / 09); insert into foo_6 values (to_date (2016 / 10 / 2016)) Select to_char (last_day (to_date ('2016 pedigree)),' dd') from dual;select months_between (sysdate, birthday) / 12 from foo_6;select to_char (least (birthday,to_date ('2016 pedigree')), 'yyyy-mm-dd') from foo_6 Select least (1,3) from dual select to_char (greatest (birthday,to_date ('2016 Univers08PUBG 08ppm),' yyyy-mm-dd') from foo_6;select greatest (1,3) from dual

Round rounds up hours and seconds for 24 hours, counting tomorrow after 12:00.

Trunc directly removes the time, minute and second

Select to_char (round (sysdate), 'yyyy/mm/dd') from dual;select to_char (trunc (sysdate),' yyyy/mm/dd') from dual

Used to intercept a specific part from a date or interval type

Extract (year from date)

Extract (MONTH from date)

Extract (DAY from date)

Extract (HOUR from date)

Extract (MINUTE from date)

Extract (SECOND from date)

Select extract (year from sysdate) from dual;select extract (month from sysdate) from dual;select extract (day from sysdate) from dual;select extract (hour from systimestamp) from dual;select extract (minute from systimestamp) from dual;select extract (second from systimestamp) from dual

Create

Drop (rarely used, must be used less)

Alter (rarely used, must be used less, and should be considered when create)

Create table foo_11 (N1 number (20); C1 varchar (50);); insert into foo_11 values (1 recordabc'); insert into foo_11 values (null,'bcd'); insert into foo_11 values (3 from foo_11 where null); select * from foo_11 where C1 is null

Null related:

Nvl

Nvl (arg1,arg2)

Returns arg2 if arg1 is null

If arg1 does not return arg1 for null

Nvl2 (arg1,arg2,arg3)

Returns arg3 if arg1 is null

If arg1 does not return arg2 for null

Select nvl (C1 Magi 'empty') from foo_11;select nvl2 (C1 Magi 'not empty', 'empty') from foo_11

Not null constraint

Specify that some columns cannot be null

Create table foo_12 (N1 number (20) not null; C1 varchar (20) not null;)

Rounding of number (rounding first, depending on the length)

Create table foo_13 (N1 number (5), N2 number (5jue 2), / / one-to-many decimal places are discarded directly, integer places with multiple errors n3 number, / / reciprocal variable 0 erases the odds. Round it off first, the last 0 of the length does not count)

Primary key

Uniqueness of data

1 create a primary key

The primary key is also a column (multiple columns, federated primary key)

Generally there is no business meaning.

Uniquely identify a row in the data table

There must be a primary key

Type grouping is number

Primary key is not null

Primary key cannot be repeated

Constraint primary key constraint name primary key (primary key column)

Create table stu (stu_id number (11), stu_no number (8), stu_name varchar2 (50), constraint stu_pk primary key (stu_id)); insert into stu values (123 100001)

Modify the table

Delete:

Drop delete table

Drop table stu

Delete deleted data can be recovered, but the speed is slow

Delete table stu;delete table str where stu_id=123

Truncate delete table content cannot be recovered, so it is fast.

Truncate table stu

Alter

Add column

How to add not null columns

Add a column that can be empty

Update setting valu

Change the column to not null

Set as primary key

More troublesome

Modify the properties of a column

Delete column

Create table foo_22 (name varchar2 (50)); insert into foo_22 values ('Zhang San'); insert into foo_22 values ('Li Si'); insert into foo_22 values ('Wang Wu'); insert into foo_22 values ('Zhao Liu'); insert into foo_22 values ('Qi Qi'); alter table foo_22 add (salary number (8) 2); desc foo_22;select * from foo_22;alter table foo_22 modify (name varchar2 (55) not null); desc foo_22 Alter table foo_22 add (s_id number (8)); desc foo_22;update foo_22 set s_id=3 where name=' Zhang San'; update foo_22 set s_id=4 where name=' Li Si'; update foo_22 set s_id=5 where name=' Wang Wu'; update foo_22 set s_id=6 where name=' Zhao Liu'; update foo_22 set s_id=7 where name=' Qi Qi'; select * from foo_22;alter table foo_22 drop column Sidney foo_22;select * from foo_22

Copy data

But constraints cannot be copied

Create table emp2 as select empno, ename, job,salfrom emp;create table emp2 as select empno, ename, job,salfrom emp where ename='lmdtx'

Copy the table structure (add an untenable condition to the where clause) but cannot copy the constraint

Create table emp3as select * from empwhere 1: 2

>

=

1200 orderby salary;select ename,salary from emp2 where salary > 1500 orderby salary desc;select ename,salary from emp2 where salary > 1500 orderby length (ename); select ename,salary from emp2 orderby salary,length (ename)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.