mta database fundamentals

Label:I. Overview1. Concept of transactionsIn a database, a transaction is a series of actions performed by a set of logical units of work, either executed or not executed. 2. Transaction acid characteristicsAtomicity (Atomic) atomicity means that multiple database operations that make up a transaction are an indivisible atomic unit, and the operations in the transaction either occur or do not occur. The co

statement, create a new user before performing the import, and assign the user table space and permissions. The steps to Tablespace and grant separately are very important. The first time the data was imported, because the user after create was not assigned the DBA and connect permissions, many migration failures were caused.When creating a user, pay attention to specifying tablespace, the tablespace can ask the DBA, or create one yourself, but you create a time to specify the parameters of Tab

row in the constrained column contains a null value.A unique constraint is used for the field, which can be used when the main surviving database4. Foreign keyIf the public keyword is the primary key in a relationship, then this common keyword is called the foreign key of the other relationship. Thus, the foreign key represents the correlation between the two relationships. A table with a foreign key of another relationship as the primary key is called the primary table, and a table with a key

Copyright NOTICE: This article is the original article of Bo Master, reprint annotated source http://blog.csdn.net/u0131427811. Super key, candidate key, primary key, foreign keyHyper-Key: A property set that uniquely identifies a tuple in a relationship is called a hyper-key of a relational pattern. A property can be used as a super-key, and multiple properties can be combined together as a super-key. The super-key contains the candidate and primary keys.candidate Key: is the least-key, that is

on multiple columns Illegal query using the GROUPING function--e Illegal statements when using grouping functions HAVING clause Nested group functions Summary Seventh Zhang Ziyi (query Using subqueries to resolve issues Sub-query Working with sub-queries Rules that use sub-queries 1. Sub-queries are enclosed in parentheses 2. Only the query is placed on the right side of the comparison operator 3. Do not add an ORDER BY clause in a subquery 4. Use single-line operators f

Label:When it comes to the fundamentals of database storage technology, it is necessary to understand the data characteristics and bottlenecks faced by enterprise applications. 1. Data characteristics of enterprise applications Event Generation Data As an example of courier tracking, the information read process includes the location of the current read operation, the timestamp, the current

or negative currencies. In Microsoft SQL Server, the data type of the currency data isMoney and SmallMoney The Money data type requires 8 storage bytes, and the SmallMoney data type requires 4 bytes of storage. 　　 (7) Special data types Special data types include data types that were not previously mentioned. There are 3 unique types of data, namelyTimestamp, Bit and uniqueidentifier。The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. Timesta

1. Experimental Content1) Create a database with Management Studio University_mis2) Create a relational database base table using SQL statements:Student table Students (Sno,sname, semail,scredit,sroom);Teacher Table Teachers (tno,tname,temail,tsalary);Curriculum courses (CNO,CNAME,CCREDIT);Score Table reports (Sno,tno,cno,score);Where: Sno, Tno, CNO are table students, table teachers, table courses The pri

Label:2.1MySQL Getting Started 1) Download to mysql website. 2) Install mysql software 3) Use Verify success turn on cmd-- input mysql-u root-p Enter password return C:\users\apple>mysql-u root-p Enter Password: * * * Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is 2 Server version:5.5.40 MySQL Community Server (GPL) Copyright (c), the Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of the Oracle Corporation and/or i

. Updating data in a tablemysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';.12. To load data into a data table in text modemysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;.13. Import. sql File commandmysql> use database name;.Mysql> SOURCE d:/mysql.sql;.14. command line to modify root passwordmysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';.mysql> FLUSH

cannot have duplicate properties. the first paradigm is a column with no duplicates. second normal form (2NF): the second paradigm is established on the basis of the first paradigm, that is, satisfying the second paradigm must first satisfy the first paradigm. The second paradigm requires that each instance or row in a database table must be divided by a unique region. It is often necessary to add a column to the table to store the unique

95031 107Wang Liju1976/1/ at 95033 101Li June Man1976/2/ - 95033 109Wang Fang Men1975/2/Ten 95031 103Contacts male1974/6/3 95031 6rows selected SQL> SetServeroutput on; SQL> ExecuteHanqi (95031); Kuanming Wang Fang contacts PL/SqlprocedureSuccessfully completed v. Functions (function) Functions are constructed in a similar way to stored procedures CREATE OR REPLACE FUNCTION inch Number inch Number RETURN Number as number ; BEGIN C:=+ B; RETUR

that you are calling to change the value of a parameter, you must pass it by reference using the ref or out keyword.Note: Passing parameters by reference also uses a ref-decorated parameter when calling a method.Comparison value passing and reference passingIn summary, a value pass is a copy of the value of a variable that is passed to the method so that the parameter of the method is the same as the value of the argument. Modifying a parameter in a called method is also an update to the data o

in this number. "K%" matches a string that begins with K.Select * from Student where FNAme like '%n% '* * NULL in the database is ' not known ' and is not interpreted in C #. There is no value.Grouping of dataThe number of people at all ages is grouped according to age:Select Fage, Count (*) from StudentGROUP BY Fage* * the GROUP BY clause must be placed after the where statement.* * the column in the GROUP BY clause does not appear in the list of co

permissions Because app users have very low privileges, they can only connect, and permissions are configured in the property configuration of the corresponding database Iv. Description of the error 1. If the Enforce password policy is checked in the General tab of Step 3, a 15128 error will occurAt this point, you need to log on as Windows Authentication mode and execute the following statement [SQL]View Plaincopy AlTER LOGIN testUser wit

t_users MODIFY uname NOT NULL means that the uname field in the T_users table is set to non-empty.2. PRIMARY KEY constraintsTo ensure uniqueness of the data in a table, there can be only one primary key in a table, but the primary key may consist of multiple fields (federated primary key, composite primary key), but this is not recommended.You can set the key in the key option when you are building a table: primary key; For example, setting the name as the primary key can be written like this:

Users'(name,fullname,password) VALUES (%s,%s,%s) ' cur.execute (sql, ("aaaaa","asd666" ,"6666"))#获取插入的最后一条数据的自增ID Print (CUR.LASTROWID) #只对一次增加一个可以直接看出, for adding multiple quantities at a time execute multiple SQL at one time='INSERT into Users (Name,fullname,password) VALUES (%s,%s,%s)'Cur.executemany (sql,[("aaadsaaa","Asd6dasd66","6666"),("adsaaaaa","asd6awf66","6666"),("awaaaaa","asd666","6666")]) Note that execute and Executemany will return the number of execution impact bars after exec

Objects Table View Stored Procedures Trigger Constraints: PRIMARY KEY constraint, FOREIGN KEY constraint, non-null constraint, UNIQUE constraint, check constraint 2.4 Database Integrity Entity integrity: The primary key field cannot be empty; Regional integrity: All data within the scope of validity; Referential integrity: Data correctness in interrelated tables, with foreign key constraints can be guara

function, aggregate function, analytic function)6.1 Single-line function6.1.1 character functions6.1.2 Date functionSysdate-System Current dateExample: select Sysdate from dual;--->dual: Virtual table6.1.3 Numeric functions6.1.4 conversion function6.1.1.1 To_char ()Convert to String typeExample: SELECT to_char (sysdate, ' YYYY ' year "fmmm" month "FMDD" Day "HH24:MI:SS") from dual;--Output system Current date6.1.1.2 to_date ()Convert to date typeExample: SELECT to_date (' 2012-01-12 ', ' YYYY-M

follows.DELETE from StudentsWHERE SName = ' Zhang Qing cut 'In another case, if the primary key value of the row to be deleted is referenced by another table, for example, the StudentID in the score table refers to the SCODE column in the Student information table, then the referenced row is deleted:DELETE from StudentsWHERE SCode = 22SQL Server will report error messages that conflict with constraintsTip: The DELETE statement deletes the entire record and does not delete only a single column,