TAILIEUCHUNG - SQL VISUAL QUICKSTART GUIDE- P44

SQL VISUAL QUICKSTART GUIDE- P44:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 15 Generating Sequences Listing defines the sequence shown in Figure . You can use a sequence generator in a few ways. The SQL standard provides the built-in function NEXT VALUE FOR to increment a sequence value as in INSERT INTO shipment part_num desc quantity VALUES NEXT VALUE FOR part_seq motherboard 5 If you re creating a column of unique values you can use the keyword IDENTITY to define a sequence right in the CREATE TABLE statement CREATE TABLE parts part_num INTEGER AS IDENTITY INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 START WITH 1 NO CYCLE desc AS VARCHAR 100 quantity INTEGER This table definition lets you omit NEXT VALUE FOR when you insert a row INSERT INTO shipment desc quantity VALUES motherboard 5 SQL also provides ALTER SEQUENCE and DROP SEQUENCE to change and remove sequence generators. Listing Create a sequence generator for the consecutive integers 1 to 10 000. See Figure for the result. Listing CREATE SEQUENCE part_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 START WITH 1 NO CYCLE 1 2 3 . 9998 9999 10000 Figure The sequence that Listing generates. Tip nRIUIC Oracle DB2 and PostgreSQL support CREATE SEQUENCE ALTER SEQUENCE and DROP SEQUENCE. In Oracle use NOCYCLE instead of NO CYCLE. See your DBMS documentation to see how sequences are used in your system. Most DBMSs don t support IDENTITY columns because they have other pre-SQL 2003 ways that define columns with unique values. See Table in Unique Identifiers in Chapter 3. PostgreSQL s generate_series function offers a quick way to generate numbered rows. 410 SQL Tricks Listing Create a one-column table that contains consecutive integers. See Figure for the result. Listing CREATE TABLE temp09 i CHAR l NOT NULL PRIMARY KEY INSERT INTO temp09 VALUES 0 INSERT INTO temp09 VALUES l INSERT INTO temp09 VALUES 2 INSERT INTO temp09 VALUES 3 INSERT INTO temp09 VALUES 4 INSERT INTO temp09 VALUES 5 INSERT INTO temp09 VALUES 6 INSERT INTO temp09 VALUES 7 INSERT

TỪ KHÓA LIÊN QUAN
Đã phát hiện trình chặn quảng cáo AdBlock
Trang web này phụ thuộc vào doanh thu từ số lần hiển thị quảng cáo để tồn tại. Vui lòng tắt trình chặn quảng cáo của bạn hoặc tạm dừng tính năng chặn quảng cáo cho trang web này.