SQL Learning Note 1

* The textbook is a database system concept (6th edition) compiled by a number of foreigners from Yale University, Lehigh University and Indian Polytechnic Institute.

1. SQL query Language Overview: Data definition Language (DDL), Data Manipulation language (DML), integrity, view definition, transaction control, Embedded SQL, and dynamic SQL, authorization.

2. Data Definition 2.1 Basic type: char (n) fixed-length string varchar (n) variable-length string, user-specified maximum length n. The difference between int smallint bigint and tinyint is as follows

bigint

Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807). The storage size is 8 bytes.

Int

Integer data (all numbers) from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The storage size is 4 bytes. The SQL-92 synonymous word for int is integer.

smallint

Integer data from -2^15 (-32,768) to 2^15-1 (32,767). The storage size is 2 bytes.

tinyint

Integer data from 0 to 255. The storage size is 1 bytes.

numeric (p,d) fixed-point number that represents the P-bit number (plus a sign bit) and D for precision. For example, numeric (3,1) can accurately store 44.5 and cannot accurately store 444.5real,double precision floating-point and double-precision numbers, and precision is machine-related. Float (n) Precision at leastis an n-bit floating-point number. 2.2 Basic Mode definition

• Build table:

  CREATE TABLE Instructor (ID varchar (7), name varchar () NOT NULL,/* Do not allow null values */dept_name varchar (), salary numeric　　(8,2), primary key (ID),/* main code (or primary key), its properties must be non-empty and unique, i.e. entity integrity */FOREIGN KEY (dept_name) references department); /* External code, the value must correspond to the value of the main code attribute in the external relationship department, i.e. referential integrity) */
• Insert data:

INSERT INTO Instructor

VALUES (10211, ' Mike ', ' biology ', 66000);

• Add attribute: ALTER TABLE R add AD;
• Delete attribute: ALTER TABLE R drop AD;
• Delete a tuple: delete from instructor; /* Delete all tuples in the instructor relationship */
• Delete Relationship: drop table instructor;

Two deletions: For the above deletions, the drop is stronger than delete, which retains the relationship instructor, but removes all tuples. The latter not only deletes the tuple, but also deletes the pattern of the relationship instructor.

* There are many other features about insertions and deletions, which are explained in more detail later.

This note takes the example of a university database model. There are a total of 11 tables, including: Classroom,department,course,instructor,section,teaches,student,takes,advisor,time_slot and Prereq.

SQL Learning Note 1