Sql21 days self-study

Sql21 self-study
P18
WEEK 1
Day 1
1. SQL functions, joint queries and subqueries (queries embedded in queries)
Structured Query Language)
RDBMS (relational database management system)
2. Non-Procedural languages are irrelevant to specific processes.
SQL only describes how to retrieve, insert, and delete data.
It does not explain how to perform such operations, but how to operate SQL Server.
P20
1. Twelve rules
P23
Design the database structure

Day 2
P32
1. SQL statements are case-insensitive, but query conditions, that is, data items are case-sensitive.
2. Select column 1 column 2 from table -- Sort
3. Select distinct amount from table
4. Select is equivalent to select all.

Expression of the third day, Condition Statement and Operation
P42
1. Where name = 'tree' -- Return true or false Based on =
2. where wages> 2000
3. Select Price + 10 from table
You can create a virtual column or combine or modify existing columns to create a derived column.
4. Select (Price + 10) newprice from table
Alias,
5. Minus signs can be negative or subtracted.
6. Distinguish null values from blank values.
Select * from table where name = NULL;
7. Select state <'La'
Result ST: co // Il
8. <> not equal! =
9. Where name like '% tree %'
10. Where like 'zhang_qiang'
11. Select firstname | lastname full name from table
12. Select day1 day2 after merging from table where day1-day2> 10
13. or not and
Set
1. Union queries two tables and returns non-duplicate results.
Select name from Table1
Union
Select name from Table2
2. Union all includes duplicate.
3. Intersection intersect
Select name from Table1
Intersect
Select name from Table2
4. It exists in the first table, not in the second table (minus)
Select name from Table1
Minus
Select name from Table2
5. The value range is in.
Select name from table
Where
Name in ('tree ', 'crane', 'zhpch ');
6.
Select * from table
Where
Age between 20 and 30;
Day 4
Further processing of data
P62
1. Count, sum, AVG, Max, and min are column-based operations.
2. Variance variance (price)
3. Date operations
Select add_months (ddate, 6) from table
Where
Add_months (ddate, 5)> enddate;
4. last_day (enddate) specifies the last day of the month.
5. leap year
Select last_day ('2017-01-02 ') non_leap, last_day ('2017-01-02') leap from table
6.
Select name months between (startdate, enddate) from table;
7. nextday, sysdate
8. mathematical functions, sine and cosine.
Select ABS (price) absolute value from Table;
9. Character operation function, case-insensitive Conversion
Select upper (name) from table
10. Expand or cancel lpad and rpad
Select lpad (name, 20, '*') from table
11. Cut ltrim and rtrim left and right
Ltrim (name, '*') removes * on the left of name *
12. Replace
Select Replace (name, 'E', '*') replacename from table;
13. substr partial output
Select substr (name, 2, 3) from table;
14. Translate (target string, source string, destination string)
Select name, translate (name, 123abc, nnnaaa) from table;
In this way, the number is replaced with N, and the letter is replaced with
15. location where specific content is met instr
Select name, instr (name, 'E', 2, 1) from table;
16. Length (name)
Conversion functions
1. Numeric to character conversion
Select to_char (AGE) from table;
2. Convert character to number to_number
Other functions
1. Select User from table;

Clause in SQL on the fifth day
P97
1. Where, starting with, order by, group by, having
2. Starting with is similar to like
Where name starting with ('tr ');
3. Group
Select name sum (amount) from Table group by name
Group first and then summarize.
Select name sum (amount), count (payee) from Table group by payee, remarks;
4. Grouping. There is no clear concept. Let's study it again.
5. Having
The function used for calculation cannot be placed in the WHERE clause. So having
Select Team, AVG (salary) from Table group by team having salary <38000;
Operators such as logical operations can be used in having.

Day 6: Table Union
P121
1. Select * From Table1, Table2
2. It is troublesome to add fields to the table. Therefore, you can create another table and associate it.
3. Select O. orderid, O. Name, P. partnum, P. description from orders o, Part p
4. Equivalent Union
Select * from T1, T2 where t1.id = t2.id
5. If a joint query has no conditions, it is a Cartesian product, which is very large.
6. the table with the least number of rows returned Based on the given condition will be used as the driving table-that is, the base table.
Except the base table, other tables are usually joined to the base table to obtain data more effectively.
7. Non-equivalent Union
Where o. Num> P. Num
8. External and Internal Federation.
Internal: the rows in each table are joined with the data in the table.
No where statement.
Select P. Num, P. Description, O. Name, O. Num
From Part p join orders o on orders. num = 54
External: the union between tables.
Right join
From Part p right Outer Join orders o on orders. num = 54
Return all records in the right table set. If there is no value on the left, null values are added.
Usage + number
Where E. ID = ep. ID (+) and E. name like '% mith'
The EP. ID is displayed.
Internal Connection, that is, cross connection. Outer Join, equivalent to union. Approximate meaning.
Http://topic.csdn.net/t/20050514/17/4006860.html
9. Table self-Union
Select a. Num, A. Description, B. Num, B. Description
From tabel1 A, Table2 B
Where a. num = B. Num and A. Description <> B. Description

Seventh Day: subquery: Embedded SQL clause
P138
1. A subquery returns the result of one query to another as a parameter.
2. exist, any, and all
3.
Select * From Table1 where table1.somenum =
(Select someothernum from Table2
Where someothernum = somevalue)
4. Use the aggregate function in the subquery
Select O. name from orders 0 part P
Where
O. num = P. Num and
O. Quantity * P. Price>
(Select AVG (O. Quantity * P. Price) from
Orders o, Part p where O. num = P. Num)
5. subquery, nested
Select C. Name... from customer C
Where C. Name in
(Select O. name from orders o, Part p
Where o. num = P. Num
And
O. Quantity * P. Price> (Select ..)
)
6. exist
Select name from orders where
Exits
(Select * from orders where name = '')
7. Any compares each row in the subquery with the primary query, and returns a true value for each row in the subquery.
Where Name> Any ()
Summary
Select * from Table A where exists (select * from Table B where Table B. ID = Table A. ID)
This sentence is equivalent
Select * from Table A where ID in (select ID from Table B)
For each piece of data in Table A, select * from Table B where Table B. ID = Table A. ID is used to determine the existence of Table B. If table B contains Table

If the current row has the same ID, exists is true. The row is displayed. Otherwise, the row is not displayed.
Exits is suitable for queries with both inner and outer sizes, and in is suitable for queries with inner, outer, and outer sizes.

 

P157 week 2
Day (s), operation data
1. insert into Table valuse ('1', '2', '3 ')
Or insert into table (Item1, item2, item3)
Values ('1', '2', '3 ')
2. null and zero or space are not the same thing.
3. Avoid repeated Columns
If not exists (select * from collection where name = 'tree ')
Insert into collection values ('tree', 50 ,'');
3. Insert select
Insert into Table1
Select * From Table2
4. Import and export of external data
Almost all database systems can import or export ascll text files.

 

Day 9
P176 create and operate tables
1. Create Database my # DB
2. Create a data dictionary
Most sdks provide data dictionaries.
3. Primary and external keywords
The primary keyword ensures that each record is unique.
It is a keyword that can be used as a matching field in other relationships.
P179 poor database design and good database design
1. One thing is that there are too many duplicate data.
2. Create Table tablename (name char (30), amount number, account_id number );
Create Table bills (
Name char (30) not null)
3. Alert table
4. Drop table

 

Day 10
P193 create view and Index
Create View
Create Index
Both pre-sort and pre-define the data to significantly improve the performance of the table.
1. Create view envelope (Company, mailing_address)
Select name, address + ''+ city + ',' + state
From Company
2. You have created a view and can query the view application.
Select * From my_view1 where count> 500
3. view restrictions
Union operations are not allowed.
Order by cannot be used but has the same operation.
4. You can perform update or delete operations on a single table view.
5. You cannot perform the delete operation on multiple tables.
You cannot use the insert statement unless all non-empty columns of the underlying table are already in the view.
6. Create view view1
Select * From Table1 where name in
(Select * From table2)
7. Index
Data integrity is guaranteed forcibly when the unique keyword is used.
Index fields or other fields can be easily sorted.
Improves query execution speed
8.
Create index index_name
On
Table (column1, column2 );
9. Differences between indexes and order
Order by is sorted every time.
An index is a physical object created in a database.
10. Composite Index
11. Avoid duplication
Create unique index unique_id_name
On
Bills (account_id, name)
Go
Select * From bills
Go
12. Create index desc_amount
On
Bills (amount DESC );
13. Index and merge
14. Use of clusters

 

11th days
P223 event processing control

Transaction refers to the unit of a command sequence that must be completed logically.
The entire process is either terminated in full or everything is correct.

SET transaction read only -- lock the record set
Select * from MERs
Where name = 'bill ';
Commit;

Select
Lock table
Set role
Alter session
Alter System

Sybase syntax
Begin transaction new_account
Insert MERs values ('TEE ', 'Eee ')
If exist (select * from MERs where name = 'tree ')
Begin
Begin transaction
Insert balances values (123.3, 34)
End
Else
Rollback transaction
If exists (select * from balances where account_id = 8)
Begin
Begin transaction
Insert accounts values (8, 6)
End
Else
Rollback transaction
If exists (select * from account where account_id = 8 and customer_id = 6)
Commit transaction
Else
Rollback transaction
Go
The above is the nesting of transactions.
By default, the set autocommit on command runs automatically.
It tells SQL to automatically confirm all the statements you run.
If you do not want this command to run automatically, set its parameter to no
Set autocommit off
End Transaction Processing

Save point.
Save transaction save_it
...
Rollback transaction save_it
Commit transaction

P238 database security
Who should get the database administrator privilege?
How many users need to access the database system
What permissions and roles should each user obtain?
How can a user delete a database that is no longer accessed?
1. Users, roles, and permissions
Create User Username identified by password
Alter User Username identified by newpwd
Alter User Username default tablespace users
Drop User Username
2. Create a role
A role is one or a group of permissions that allow users to execute specific functions in the database.
Grant role to user [with admin option]
Revoke role from user;
Orcle three roles
Connect, resource, DBA
Grant connect to Username
Connect allows you to create tables and update data. And other normal operations.
Resource allows creation, triggering, and indexing.
3. User Permissions
Grant system_privilege to {username}
Grant create view to public
Grant select on Table1 to Username
Select * from username. Table1
Skip
P255 advanced SQL
13th days
Grant authorization, revoke abolition, identifier

P267 Stored Procedure
1. Execute the stored procedure with Parameters
Create procedure pro1
@ Pname char (40 ),
@ Psummary char (30) Output
As
Select @ psummary = summary from mytalbe where pname = @ pname
Go
Execution Method
Declare @ return_summary char (30)
Exec pro1, @ return_name = @ name output
Print @ pname
Go
Nesting of stored procedures, that is, they can be called to each other.
2. Create a virtual table

P274 trigger