A relational database:
Oracle: by Oracle (Oracle) company is a relational database, in which the company has a lot of
Servers and software, but the main or the Oracle database,
MySql: is a small database, acquisition by Sun company before, and now it belongs to the Oracle
Service: Sql is a relational database of Microsoft
DB2: a relational database is IBM, which is the most powerful rival Oralce database, security, and
Usage and Oracle database rival,
Access: is a relational database, Microsoft
Sybase: is the relational database of a company in the United States,
No: is not only the SQL, such as: HBase
Key/value pair: {" name ":" zhangsan ", "age" : 28, "sex", "nan"}
Open the database:
1. In the CMD window to enter a command (up sqlplus)
2. The installation of Sql command line opened in the
3. Open in the visualization tools,
SQL statements (data structured language)
DDL statements: the database defined Language: database definition Language), is mainly used to create and
Changes in the database object,
Create: create
The alter: modify the
Delete drop:
How to create tables:
The create table table_name (
Field name (name) data type (data_type) (integrity constraints),
Field name (other_name) data types (data_type) (integrity constraints)
);
Data type:
NUMBER: numeric type, format NUMBER (6) NUMBER (5, 2)
Number (6) number (5, 2)
When the figure is only a said the length of the digital type before the decimal point for so many
When two Numbers, a digital representation in front of the total figures, said a number of decimal places behind,
CHAR: it means the character type
Char (n) : n bytes of the characters, the biggest char types can store 2000 bytes
Long VARCHAR: character type
Varchar (n) : n bytes of the characters, the varchar type can store 4000 bytes biggest
DATE: DATE type, the length is only 7 bytes
The default format for: DD - MON - YY
Modify the table name: rename oldname to newname. commit;
Modify the table add fields: the alter table myemp add (hiredate DATE);
Modify delete table fields: the alter table myemp drop (hiredate);
Modify table to modify fields: the alter table myemp modify (gender char (5));
Delete table, drop table myemp;
DML statements: (database maptipu.. Language: the database manipulation Language), it is used for database
The language of operated object,
Delete: delete the
Update: update
Insert: insert
Insert data: insert into myemp (id, gender, name, birth, salary, job, dept)
Values (6, 'W', 'Flacs, TO_DATE (' 1997-05-03', '- DD YYYY - MM), 4000,' UI development engineers, 123458);
Update the data: the update myemp set GENDER='G' where GENDER='M'.
Delete the data: delete from MYEMP where job='CEO;
DCL language: (database control language: database control language), it is to the operation of the database access control,
Grant: authorization
Connect:
Char or varchar data type function:
1. The concat (c1, c2) : returns the results of two strings, the two parameters c1, c2 is to connect two strings,
Its role and "| |"; If and null joining together, is to connect a space,
Select id, CONCAT (name, salary) from MYEMP;
Select id, concat (concat (name, ':'), salary) as the show from MYEMP;
Select id, name | | ':' | | salary) as the show from MYEMP;
2. Length (char) : used to return to the length of the string
The select length (name), name the from myemp;
Select the name from myemp where length (name)=4;
3. The upper, lower, initcap
Upper: converts a string to uppercase
The lower: gen converted to lowercase characters
Initcap: capitalize the first letter of each word in the string
Select the name, upper (name) from myemp;
Select the name, the lower (name) from myemp;
Select the name, initcap (name) from myemp;
4. Trim (c1 from c2), ltrim (c1, c2 []), rtrim (c1 [, c2])
Trim (c1 from c2) : returns the c1 from c2 about the result of interception
Ltrim (c1, c2 []) : return to c2 c1 from the result of the centre-left interception
Rtrim (c1, c2 []) : return to c2 c1 from the result of the right to intercept
Select the name, the trim (' S 'the from name) from MYEMP;
Select the name, LTRIM (name, 'J') from MYEMP;
Select the name, the RTRIM (name, 'K') from myEmp;
5. Substr (char, m [n])
Substr: said interception string; Return from m, intercepting n character string,
Select the name, SUBSTR (name, 3, 3) the from MYEMP;
Select the name, SUBSTR (name, 3) the from MYEMP;
6. Instr (c1 and c2/n, m)
Instr (c1 and c2 [, n, m]) : returns the substring of c2 in the source string c1 position;
N: start with c1 n subscript
M: for p1 of c1, (if you cannot find returns 0)
The select INSTR (' HelloWorldWorldWorldabc! ', 'World', 10, 2) from dual;
Numerical function:
1. Round (m, n) : rounded; The number m need to be rounded,
N a few decimal places,
Select round (45.621) from dual;
Select round (45.621, 1) from dual;
2. Trunc: capture digital
And round about the same, but not round,
The select trunc (45.621, 0) from dual;
More than 3. Mod: said to
The mod (m, n)
Select the mod (13, 5) from dual;
4. The ceil (m), floor (m)
Ceil (m) : it means the upward integer
Floor (m) : rounded down
The select ceil (14.9) from dual;
The select floor (13.1) from dual;
To date some of the operation function
To_Date: date type in accordance with the specific date format string parsing for date type,
TO_CHAR: will a Date Date in accordance with the specified Date format string
The select TO_CHAR (sysdate, 'YYYY - MM - DD HH24 - mi - ss') from dual;
Last_Day (Date) : it means the current Date on the last day of the month
Select Last_Day (sysdate) from dual;
ADD_months (Date, n) : it means the current Date increase n months
The select Add_Months (sysdate, 1) from dual;
Months_bettwen (Date1 and Date2) : indicates the interval between two dates months
The select MONTHS_BETWEEN (sysdate, sysdate + 1000) from dual;
Date type to addition and subtraction;
Select (sysdate - birth) as the age from myemp;
Which day is 100 days later?
Select (sysdate + 100) as ten from dual;
DQL statement: (databse Query Language, database Query Language), it is used to Query the database object,
Select: query
Query: select id, name, job from myemp;
Alias: when the select query expression or function of the results of the query attribute will be an expression or a function,
Therefore look very intuitive or convenient, all give it an alias, to facilitate the intuitive understanding of the data,
The select trunc (45.621, 0) as num from dual;
Or, and used to connect multiple conditions
Or priority is less than and, therefore, when both use can add () to improve priority,
Select the name from myemp where
Salary> 4000 and (job='UI designers' or job=' Java development engineers');
Like: used for fuzzy query
The wildcard: _ : said a
% : 0 - multiple
Select * from myemp where name like '_A %';
Select * from MYEMP where name like '% m %' and salary>=3000;
In, not in
In (the list) : meet the list any one of a set of
Not in (the list) : does not meet the list, any one of a set of
Select * from MYEMP where job (in 'UI designers',' Java development engineers');
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
