Large set of basic SQL statements and large set of SQL statements
Every time I used to use SQL Sever to directly design the database, but did not pay enough attention to the SQL statements. I took the opportunity of restructuring the cooperative edition of the data center and used SQL statements to design the database, at the same time, we will sort out the basic usage of SQL statements.
[Category]
There are nine types of SQL statements, which can be divided into four categories by function:
1. query statement: select ---- the most commonly used statement in the database
2. Data Definition: create, drop, alter ---- operations on databases, data tables, and fields in tables
3. data manipulation: insert, update, and delete-the legendary "add, delete, modify, and query" operation on specific data
4. Data Control: grant, revoke ---- permission definition and Deletion
[Statement Summary] 1. Create a database
Create database RechargeSystemon primary -- master file group (/* specific description of the data file */name = 'rechargesystem _ data', -- Logical name of the master data file filename = 'G: \ RechargeSystem \ RechargeSystem_data.mdf ', -- physical name size of the master data file = 5 mb, -- initial size maxsize = 100 mb, -- maximum growth value filegrowth = 15% -- growth rate of the master data file) log on (/* log file Description */name = 'rechargesystem _ log', filename = 'G: \ RechargeSystem \ rechargesystem_log.ldf', size = 2 mb, filegrowth = 1 mb)
If you have learned the operating system, these definition statements are not very unfamiliar. The database group file is also a file and requires a logical name (that is, the file name), physical name (the actual storage address in the disk), file size, etc.; log files record all the logical operations in the database, advantages and disadvantages, and other details can be found in Baidu Encyclopedia: Log Files
2. basic table operation example ------------- create table <basic table name> --- create a table (<column name type not null>, primary key (column name, column name ), -- primary key constraints foreign key (<column Name>) references tableName (<column Name>), -- foreign key constraints ); tables ------------- alter table <table Name> add <column Name> <data type> -- add Column
You can set the primary and Foreign keys for the created table: alter table <table Name> add primary key (<column Name>, <column Name>) alter table <table Name> add foreign key (<column Name>) references tableName (<column Name>)
Delete a column: alter table <table Name> drop <column Name> [cascade | restrict] -- restrict also splits the view and constraints of the column into sectors -------------- drop table <table Name> [cascade | restrict] -- delete a table, when there are constraints, you must add restict 3. query statement: the general query statement is: select * from datatable. You need to note the nesting and connection query from multiple tables. This combination is usually used in M: find the corresponding link in the N link. Select TB1.column1, TB1.column2 from TB1 where TB1.column1 in (select column1 from TB2 where TB2.column2 = "specific value"); this relationship is not recommended for users with logic disorder, it is particularly prone to search failure. ------------------------------------------------------------------------------------------------------------------------------- ------------- In addition, SQL query statements also provide basic Aggregate functions for ease of query: count (*) ----------------------- calculate the number of tuples, that is, the total number of columns count (<column Name>) ---------------- calculation of the number of values in a column sum (<column Name>) ------------------- total (numeric) avg (<maximum value>) --------------- average (must be numeric) max (<column Name>) --------------- maximum min (<column Name>) -------------------- minimum value Eg: select count (distinct s #) from T_student. After distinct is added, when multiple student numbers are repeated, only one complete semantic structure can be calculated as follows: select <column name or expression sequence> from <Table name or view sequence> where <row expression> group by <column Name> ----------------------------- grouping having <group condition expression> ------------------ based on a field -------------------- order by <column name [asc | desc]> -- ---------------- Asc in ascending order and desc in descending order ----------------- special query requirements: 1. Comparison operation: <, <=, >>= ,! = 2. matching operation: % --- match with zero or multiple characters. "_" matches a single character. 3. null is Null 4. aggregate resource comparison: in, some, any, all 5. whether the set is not empty: exitsts --- if it is not empty, it is true; otherwise, it is false 6. whether duplicate tuples exist in the Set: unique -------- the repeated values are true; otherwise, false indicates ---------------- 4. insert data operation statement: insert into <Table Name> (<column Name>, <column Name> ...) values ('number', 'number') Deletion: delete from <Table Name> where condition modification: update <Table Name> set
5. embedded SQL use technology, usually use the execution view, stored procedures, and so on to add exec to execute [summary] the knowledge is always like this, each time the learning will have a different harvest, especially after use, we will try to compare the differences between different methods. We look forward to the comparison of database views, stored procedures, and security settings.
