Some different customs about the database

Database, a very simple subject, at least that's what our teacher said! Hello everyone, Meng Meng Meng main welcome objective to support!

database (DATEBASE,DB) Technology is a technology that program developers want to master, using a database to efficiently and coherently store data, which enables people to manage data more quickly and easily, mainly in:

> can be structured to store large amounts of data information to facilitate effective user retrieval and access.

> can effectively maintain the consistency and integrity of data information and reduce data redundancy.

> can meet the sharing and security requirements of the application.

> Database technology can be easily and intelligently analyzed to generate new application information.

Today's Common database

SQL Serve

Oracle

DB2

Mysql

Basic concepts of the database

(1) data

Data is a symbolic record that describes things.
Data: In the computer system, a variety of letters, digital symbols combination, voice, graphics, images, etc. collectively referred to as data, the data after processing become information.
In computer science, data refers to all the media that can be entered into a computer and processed by a computer program, which is a generic term used for the processing of electronic computers, with certain meanings, such as numbers, letters, symbols, and analog quantities.

(2) database

A database, DB, is a collection of organized, shareable data that is stored in a computer for a long period of time.
A database is a unit or a general-purpose data processing system in a field of application that stores a collection of relevant data belonging to the enterprise and business units, groups, and individuals.
The data in the database is based on a global view, which is organized, described and stored according to a certain data model. Its structure is based on the natural relationship between the data, thus providing all the necessary access path, and the data no longer for an application, but for the whole organization, with the overall structural characteristics.
The data in the database is created for the information shared by many users, which has been removed from the limitation and restriction of the specific program. Different users can use the data in the database in their own usage, and multiple users can share data resources in the database at the same time, that is, different users can access the same data in the database at the same time. Data sharing not only satisfies the requirements of each user for information content, but also satisfies the requirement of information communication between each user.

(3) Database management system
Database Management system, DBMS is a database organization, it is a system software, responsible for the data in the database, data manipulation, data maintenance, control and protection and data services.
The main types of database management system are 4 kinds: file management system, hierarchical database system, mesh database system and relational database system, in which the relational database system is the most widely used.
Database management system is a large-scale software for manipulating and managing databases, which is used to establish, use and maintain databases. It has unified management and control of the database to ensure the security and integrity of the database. The user accesses the data in the database and the database administrator makes the database maintenance work through it. It allows multiple applications and users to create, modify, and interrogate databases in different ways, at the same time or at different times.
The DBMS provides data definition language DDL (Language) and data manipulation Language, which allows users to define the schema structure and permissions constraints of the database, and to append, delete, and manipulate the data.

(4) Database system
Database system, DBS, refers to the whole computer system after the introduction of database technology, which can realize the organization and dynamic storage of large amounts of relevant data, provide data processing and information resources sharing convenient means.
The database system consists of database (data), database management system (software), computer hardware, operating system and database administrator.
Tips:
In database system, database management system and database, database management system is the part of database system, database is the management object of database management system, so it can be said that database system includes database management system, database management system including database.

Language of the database: SQL language (current)

Composition of sql: DML: Data used to insert, modify, and delete databases

DDL: Used to build databases, database objects, and define their columns, most of which start with the Create command

DQL: Used to query data in a database

DCL: Used to control access permissions, access rights, etc. of database components

Operators in the database: +-*/%

Assignment operators in the database: =

Comparison operators in the database: = > < <> (not equal to) >= <=! = (Not equal)

Logical operators in the database: And when and only if two Boolean expressions are true, returns true

Returns False if and only if two Boolean expressions are false

Not negate the value of a Boolean expression, with the highest precedence

Anyway, inserting data using SQL

Grammar:

　　

1 INSERT [into] table name [(column list)]values(Value list)　　　　

　

Insert multiple rows of data at one time:

(1) Adding data to a table by using the Insert SELECT statement

(2) Adding data from an existing table to a new table by using the SELECT INTO statement

Grammar:

SELECT IDENTITY　　(data type, representing seed, identifying growth amount)  as set forth  into List  from original table　　

(3) Insert with Unio keyword merge data

Updating data Using SQL

Grammar:

 Update table name set column name = Update value [WHERE Update value ]

Use SQL to delete data (try not to use it)

Grammar:

DELETE [from] table name [WHERE < Delete condition >]　　

The above is just to give you a simple introduction of SQL, as well as some common syntax. Of course there are parts of the content that are ignored because there is a small part of the programmer's path that will not be used. Of course, if you do not understand can leave a problem, small series is willing to solve for everyone ~ at the end of this chapter, the small series will leave contact information.

With a database, we use it to store a lot of information. Some are visible to the naked eye, and others to the head. In this way, we have to invent a query method for querying. So, the following small series will show you the query method about the database

The basis for querying a database:

SELECT    < list >from    < table name >[WHERE    < query conditional expression >][order    by < sorted >[ASC or desc]]

NOTE: DESC is descending sort asc is ascending sort

(1) Querying all data rows and columns

SELECT *　　 from STUDENT　　　　

　　

' * ' denotes all columns

(2) querying some rows or columns

SELECT scode,sname,sadress  from      Student WHERE   sadress  =' Hebei '

　　

(3) using the alias of a column in a query

(4) Query null value

(5) Using constant columns in queries

(6) The number of rows that the query returns limits

Query sort using ORDER BY

Use the function in the query (it is not difficult to look at the difficult, the small part will give you the most simple explanation to let you get the biggest and biggest harvest)

SQL Server provides intrinsic functions, each of which implements different functions, and the different categories of functions can be used in conjunction with SELECT, UPDATE, and INSERT statements.

(1) String function

Name of function	Describe	Example
CHARINDEX	Used to find the starting position of a specified string in another string	SELECT CHARINDEX (' name ', ' My NAME is Tom ', 1) Returns: 4
Len	Returns the length of the string passed to it	SELECT LEN (' SQL SERVER course ') Returns: 12
UPPER	Converts the string passed to it to uppercase	SELECT UPPER (' SQL Server Course ') Back to: SQL SERVER Course
LTRIM	Clear space to the left of the character	SELECT LTRIM (' Zhou Zhiyu ') return: Zhou Zhiyu (trailing spaces reserved)
Ririm	Clear the space to the right of the character	SELECT LTRIM (' Zhou Zhiyu ') return: Zhou Zhiyu (front space reserved)
Right	Returns the character of the specified bibliography from the right side of the string	SELECT right (' Room loan ', 2) Back to: Loans
REPLACE	Replace a character with a string	SELECT REPLACE (' Struggle on both sides ', double, single) Return: The struggle of the unilateral
STUFF	In a string, delete the specified length of character and insert a new string at that location	SELECT STUFF (' 123 My 456 ', 4, 5, ' his ') Returns: 123 of his 456

　

(2) Date function:

GETDATE	Get the current system date	SELECT GETDATE () Return: Today's date
DATEADD	The date after the specified date part is added to the established value	SELECT DATEADD (mm, ' 01/01/2009 ') Returns: Returns 05/01/2009 in the current date format
DATEDIFF	Interval between two dates for a specified date part	SELECT DATEDIFF (mm, ' 01/01/2009 ', ' 05/01/2009 ') Returns: 4
Datename	The string form of the date part specified in the date	SELECT datename (DW, ' 01/01/2000 ') Back: Saturday or Saturday
DATEPART	The integer form of the date part specified in the date	SELECT DATEPART (Day, ' 01/15/2000 ') Returns: 15

Comments:

Year	Yy,yyy
Quarter	Qq,q
Month	Mm,m
Day	Dd,d
DayOfYear	Dy,y
Week	Wk,ww
Weekday	Dw,w
Hour	hh
Minute	Mi,m
Second	Ss,s
Millisecond	Ms

(3) Mathematical functions:

RAND	Returns the number of random floats from 0 to 1	SELECT RAND () Returns: 0.79288062146374
Abs	Take the absolute value of a numeric expression	SELECT ABS (-43) Returns: 43
Celing	Rounding up	SELECT celing (43.5) Returns: 44
Floor	Want to go down the whole	SELECT Floor (39.8) Returns: 39
POWER	Take the power value of a numeric expression	SELECT POWER (5.2) Returns: 25
ROUND	Rounding a numeric expression	Selecct ROUND (43.543,1) Returns: 43.500
Sign	Returns +1 for positive, negative but 1 for 0, 0 for	SELECT sign (-43) Return:-1
SQRT	Takes the square root of a floating-point expression	SELECT SQRT (9) Returns: 3

(4) System functions:

CONVERT

Used to transform data types

SELECT Conbert (VARCHAR (5), 12345) Return: String 12345

Due to the system functions are not often applied, again do not explain more, if in doubt, you can leave your problem Oh!

Fuzzy Query and Aggregation function (also a small point oh)

(1) wildcard character: It is a class of characters that can replace one or more real characters and appear as alternate characters when looking for information.

_	A character
%	string of any length
[]	One character in the range specified in parentheses
[^]	Any character that is not in the range specified in parentheses

(2) Use like for fuzzy query (: Like is in a string to find there is no similar to the condition of the keyword, that is, as long as we have to find the keyword in the line)

(3) Use between in a range of queries (: between means that between the---, as the name implies is given a range, in the scope of search)

(4) Use in to query within the example value: (: In is the enumeration value must have the same data type as the matching column)

Aggregate function: SUM () function AVG () function MAX () function and min () function COUNT () function

SUM () function: Calculates the sum of the columns

AVG () Function: averages are calculated

Max () Function: Calculates the maximum value

Min () function: calculates the minimum value

Count () function: Calculates the sum

Group query: implemented by the GROUP BY clause. Use group queries frequently for statistics!

SUM ()

Count ()

Max () min ()

AVG ()

Once the group by group is used, we can only get information about the groups, not the members in the group.

The select can only follow the aggregate function and the column following the group by, and the other columns do not allow

Cannot have aggregate function after where

Some different customs about the database