Orcale (1)

Oracle Database basic statement Query

Oracle-12560: TNS protocol configurator Error 1. service: 1. the Listener service is not enabled. 2. server not enabled 3. environment variable: oracle_sid = orcl 4. regedit registers oracle_sid = orcl 5. CMD --> set oracle_sid = orcloracle network configuration file path: D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_3 \ Network \ admin \ sampleclient configuration file path: D: \ app \ Administrator \ product \ 11.2.0 \ client_3tnsping IP: Test Database Service Command listening configuration command: 1. LSNRCTL start listener 2. LSNRCTL Stop 3. LSNRCTL Status view status

Oracle basic type

String: 1. char/nchar fixed length 2. varchar2/nvarchar2 Variable Length numeric type 1. number (P, S) eg: 123.89 number (3) --> 124 rounding 123.89 number (6, 1) --> 123.9 1. p-indicates the number of digits that are valid. A maximum of 38 valid digits can be entered. s-scope of use-84 ~ 127 1. positive number: number of digits from the decimal point to the lowest valid number 123.89 number (6, 1) --> 123.9 2. when it is a negative number: the number of digits from the maximum valid number to the decimal point is 123.89 number (6,-2) --> 100 2.int Integer type date data type storage date and time information

Oracle role:

Connect role: insert into, delete, update, and select can be used to create tables, views, and sequences for tables of other users. You cannot view the username resource role (resource role): Create: trigger process of table sequence index and cluster DBA role (Database Management): unlimited space limit and authorization to other users. For example: eilin select, delete to tmp1 role permission lin1 wcq159753? Grant eilin. resource to Lin assigns the permissions of the role to the Lin user.

1. create temporary tablespace car_data <br> tmpfile 'C: \ Users \ 102466 \ Desktop \ provite \ car_data.dbf' size 50 mautoextend onnext 50 m maxsize 20480 mextent managemet local; 2. create a data table space create tablespace car_dataloggingdatefile 'C: \ Users \ 102466 \ Desktop \ provite \ car_data.dbf' size 50 mautoextend onnext 50 m maxsize 20480 mextend management loacl; 3. create user eilinge identified by 8888 default tablespace car_data; // username: eilinge password: 88884. authorize the created user to grant connect, resource, DBA to eilinge; 5. revoke User Permissions revoke create, resource from eilinge; 6. delete user drop user eilinge

7. create Table car (carid number (20) not null primary key, carname varchar2 (20), cartype varchar2 (20), carnum varchar2 (20), carage INT); 8. modify Table // Modify Table car, add check constraint alter table table name Add constraint check name check (column name constraint); alter table car add constraint car_carage check (carage between 0 and 10 ); // set the foreign key alter table table name Add constraint foreign key name foreign key (userid) References usern (userid); // modify the Data Type of a column (generally limited to the modification length, change to different classes Type) alter table table name modify (column name data type); alter table car modify (carid number (10 )); // modify the column name alter table table name rename column current column name to new column name; alter table car rename column carid to carcar; // modify the table name alter table current table name Rename to new table name; alter table car Rename to carcar; // Delete the alter table Table Name drop column name; alter table car drop column carname; // Add the alter table table name Add (column1 datatype, column2); alter table car add (car1 number) (20), car2 number (20); // Foreign key constraints // constraints are used to specify data rules in the table. If any data violation occurs, behavior will be constrained to terminate not null the column specified by the non-null constraint cannot be null the unique constraint specified by the unique column does not have duplicate value primary key constraint -- non-null and unique, A table can have only one primary key constraint. The foreign key constraint of foreign is that columns in a table reference columns in other tables, and dependency exists, you can point to the check condition constraint that references your own column C to specify whether the column meets the constraint name for a certain condition recommendation: constraint type _ TABLE name_column name NN -- not nulluk -- Unique keypk -- primary key FK -- foreign keyck -- check key foreign key constraints are used to maintain the integrity of the reference from the table and the master table, therefore, the foreign key constraint involves two tables: foreign key: indicates the column references in the child table at the table level: indicates the column on Delete cascade in the parent table: when the columns in the parent table are deleted, the corresponding columns in the child table are also deleted. On Delete set NULL: The corresponding columns in the child table are left empty.

9. create a table in the usern table and add a foreign key and a primary key create table usern (userid number (20) not null primary key, // Add a primary key licnum number (20 ), username varchar2 (10), sex varchar2 (2), userage int, usernative varchar2 (10), useraddress varchar2 (100), usernum number (13), carid number (20 ), constraint carid foreign key (carid) References car (carid); // Foreign key Association constraint 10. insert into car values ('20140901', 'bmw '); 11. update a data statement // modify a data. For example, change the income of employees numbered 11011 In the table salary to 10000; Update salary set income = 10000 where employeeid = 110001; // modify a column of data. For example, increase the income of every employee by 100 RMB Update salary set income = income + 100; 12. query the vehicle information in the car table with the license plate number 88888 select * From carwhere carnum = '000000 '; // query all information of a car whose license plate number is 88888 and whose carname is Mercedes-Benz (cross-Table query) Select * From usernwhere carid = '000000' and carname = 'merced '; // query all information of the owner whose license plate number is 88888 (cross-Table query) Select * From usernwhere carid in (select carid from car where car. carnum = '000000'); // query all the identity information of the owner and the vehicle information (external connection) Select * From usern left join car on (car. carid = usern. carid); // query all the identity information and vehicle information of the vehicle owner whose license plate number is 88888 (external connection); select * From usern left join car on (car. carid = usern. carid) Where carnum = '000000'; // query the user information of a car whose name is BMW (subquery) select * From usern where cauid in (select carid from car where carname = 'bmw '); // query the information of the Mercedes-Benz that has changed the taillight and added navigation (Multi-table join) select * from car as C, reparis as R, variation as V where R. type = 'taillight 'and V. type = 'add navigation 'and C. carname = 'bender'; // query the number of vehicles of different types (grouping and adding count) Select carname, count (*) quantity from car group by carname; 12. view and index what is View: 1. A view is a virtual table. A view is built on an existing table. The tables created by the view are called base tables. the statement that provides data content to the view is a SELECT statement, which can be understood as a stored SELECT statement 4. view provides users with another form of representation of the base table data. the view does not store real data. The real data is stored in the base table. 6. although the programmer operates on a view, the final view will still be converted into an operation base table view. restrict Data Access 2. simplify complex queries 3. providing data independence 4. the same data can be displayed in different ways. create view temp as select carid, cartype from car; what is Index 1. it is a mechanism to quickly query the content in a table, similar to the Xinhua Dictionary directory 2. it is applied to some fields in a table, but stored, independent of indexes outside the table. once an index is created, the Oracle management system automatically maintains the index, and the Oracle management system determines when to use the index. you do not need to specify which index to use in the query statement. after the primary key or unique constraint is defined, the system automatically creates an index on the corresponding column. 4. you can also add an index for a single field or multiple fields as needed. create index cartype on car (cartype) 13. order byselect column_name1, colum_name2from table_nameorder by column_name, column_name ASC | desc -- descending order; 14. likeselect * From websiteswhere name like '% s-' % -- replace 0 or multiple characters-replace one character 15. function aggresponavg () average count () number of rows Fisrt () value of the first record last () value of the last record max () Maximum min () Minimum Sum () the sum scalar function returns a single value based on the input value. ucase () converts a field to uppercase lcase () and converts a field to lowercase mid () extract the character Len () from a text field and return the length of a text field. Round () rounds a specified decimal place to a value field. Now () returns the current system Date and Time Format () format the display mode of a field. havingselect column_name, aggregate_function (column_name) from table_namewhere column_name operator valuegroup by column_namehaving aggregate_function (column_name) operator; eg: Select websites. name, sum (access_log.count) as Nums from websitesinner join access_logon websites. id = access_log.site_idwhere website. alexa <200 group by websites. namehaving sum (access_log.count)> 200;

Appendix:

Commit is submitted. After data is inserted, you must enter commit to check whether the data is successfully inserted. view the table structure Delete table table_name Delete table varchar allows you to store an empty string varchar2 and varchar, change the empty string to null storage distinct remove duplicate value revoke Grant select table_name from user_tables; view all table names under a user user_tables all tables all_tables all tables dba_tables select * From sys. TMP // sys declaration object view all users select username from dba_users; query User Permissions select * From user_sys_privs; the user's permission system role select * From user_role_privs cannot be displayed; sys has the highest permission, you can perform operations on tables created by system and manage permissions. system cannot perform operations on sys. temp

 

Orcale (1)