Shulou(Shulou.com)06/01 Report--

II. Advanced course of SQL

1 、 SQL SELECT TOP

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is very useful for large tables with thousands of records.

Note: not all database systems support the SELECT TOP clause.

Sql server | ms access syntax

Select top number | percent column_name from table_name

Mysql | oracle syntax (equivalent to top)

Select Column_name from table_name limit number

Mysql syntax

Select * from table_name limit number

Select * from websites limit 2

Oracle syntax

Select * from table_name where rownum 10)

)

Alter table person4 add check (id > 10)

Alter table person4 drop check (...)

24 、 SQL DEFAULT

The DEFAULT constraint is used to insert default values into the column.

If no other values are specified, default values are added to all new records.

Create table Persons (

Id int primary key

Name varchar (20) default "zy"

)

Create table orders (

Id int primary key

P_id int not null

Orderdate date default getdate ()

Foreign key (p_id) references persons (id)

)

Create table persons (

Id int primary key

Name varchar (20) default 'zy'

)

Alter table persons alter name set default 'zz'

Alter table persons alter name drop default

25 、 SQL CREATE INDEX

The CREATE INDEX statement is used to create an index in a table.

Indexes enable database applications to find data faster without reading the entire table.

You can create indexes in tables to query data more quickly and efficiently.

The user cannot see the index, they can only be used to speed up the search / query.

Note: updating a table with an index takes more time than updating a table without an index, because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

Create index index_name on table_name (column_name)

Create index index_name on persons (name)

Create a unique index on the table. Duplicate values are not allowed: a unique index means that two rows cannot have the same index value. Creates a unique index

Create unique index index_name on table_name (column_name)

Create a multi-column index

Create index index_name on table_name (column_name)

26 、 SQL DROP

Indexes, tables, and databases can be easily deleted by using DROP statements.

Drop index index_name on table_name

Delete tabl

Drop table table_name

Delete database

Drop database database_name

What should we do if we only need to delete the data in the table, but not the table itself?

Truncate table table_name

27 、 SQL ALTER

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Add column

Alter table_name add column_name datattype

Modify column

Alter table table_name modify column column_name datatype

Delete column

Alter table table_name drop column column_name

28 、 SQL AUTO INCREMENT

Auto-increment generates a unique number when a new record is inserted into the table.

To start the AUTO_INCREMENT sequence with a different value, use the following SQL syntax:

To insert a new record in the "Persons" table, we do not have to specify a value for the "ID" column (a unique value is automatically added):

Self-increasing of oracle

Create sequence seq_person

Minvalue 1

Start with 1

Increment by 1

Cache 10

29. SQL view

SQL View creation

SQL CREATE VIEW

Create view view_name as select column_name (s) from table_name where...

In SQL, a view is a visual table based on the result set of a SQL statement.

The view contains rows and columns, just like a real table. The fields in the view are fields from real tables in one or more databases.

You can add SQL functions, WHERE, and JOIN statements to the view, or you can render data as if it came from a single table.

SQL view update

SQL CREATE OR REPLACE VIEW

Create or replace view view_name as select column_names from table_name where....

SQL view deletion

Drop view view_name

30. SQL date

When we deal with dates, perhaps the most difficult task is to make sure that the format of the date inserted matches the format of the date column in the database.

As long as your data contains only the date portion, there will be no problem running the query. However, if the time part is involved, the situation is a bit complicated.

Before we discuss the complexity of date queries, let's take a look at the most important built-in date processing functions.

Now () returns the current date and time

Curdate () returns the current date

31, SQL NULL value

The NULL value represents missing unknown data.

By default, the columns of the table can hold NULL values.

This chapter explains the IS NULL and IS NOT NULL operators.

If a column in the table is optional, we can insert a new record or update an existing record without adding a value to the column. This means that the field will be saved as a NULL value.

The NULL value is treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: NULL and 0 cannot be compared; they are not equivalent.

You cannot use the comparison operator to test NULL values, such as =, <, or.

We must use the IS NULL and IS NOT NULL operators.

SQL IS NULL

SQL IS NOT NULL

32. SQL NULL function

33. SQL common data types

Character (n) character / string. Fixed length n.

Varchar (n) or character varying (n) characters, strings. Variable length. Maximum length n.

Binary (n) binary string. Fixed length n.

Boolean stores TRUE or false values.

Varbinary (n) or binary varying (n) binary string. Variable length. Maximum length n.

Integer (p) an integer (without a decimal point). Precision p.

Smallint integer value (no decimal point). Precision 5.

Integer integer value (no decimal point). The precision is 10.

Bigint integer value (no decimal point). The accuracy is 19.

Decimal (pforce s) accurate value, precision p, decimal point after the number of s. Such as: decimal (5. 2) is a number with 3 digits before the decimal point and 2 digits after the decimal point.

Numeric (pforce s) exact value, precision p, number of decimal places s. (same as decimal)

Float (p) approximate value, Mantissa precision p. A floating-point number that uses an exponential counting method with a base of 10. The size parameter of this type consists of a single number that specifies the minimum precision.

REAL approximate value, Mantissa precision 7.

FLOAT approximate value, Mantissa precision 16.

DOUBLE PRECISION approximate value, Mantissa precision 16.

DATE stores the values of year, month, and day.

TIME stores the values of hours, minutes, and seconds.

TIMESTAMP stores the values of year, month, day, hour, minute, and second.

INTERVAL consists of integer fields that represent a period of time, depending on the type of interval.

A fixed-length ordered collection of ARRAY elements

A variable-length unordered collection of MULTISET elements

XML stores XML data

34. SQL DB data type

Microsoft Access data type

MySQL data type

In MySQL, there are three main types: Text (text), Number (number), and Date/Time (date / time).

SQL Server data type

Number type:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.