Database (i): overview

Learn about MySQL

• Learn about MySQL
  • Tables table
  • Primary key
  • SQL Structured Query Language structured queries language
• MySQL installation and start-up
  • Install MySQL in Ubuntu
  • View all databases on the system
  • Link Database
  • View the contents of a specific database
• Retrieving databases
  • SELECT statement retrieves one or more data columns
  • To sort the results of a search
  • Retrieving filtering through the where

Database

A database is a collection of data that is stored in an organized way.

A structured file such as a table in a database stores a particular type of data. Like what:

StudentID	Studentname	Studentage
20101101	Li Hua	18
20101102	Andy	19
20101103	John	20

Above is a statistical class student information table structure, generally in a specific database. There are a variety of table structures stored (the table name should be uniquely labeled). Each table structure can maintain some kind of connection relationship.

1. Tables (table)

A structured list of data of a particular type

Records in the table :
There are rows and columns in the table, each row represents a record in the table, and each column stores a specific piece of information, and the first column in the Class Student information table above represents the student ID information.

2. Primary key:

• A primary key is identified by a column or set of columns in a table whose value uniquely identifies each row in the table, is unique, cannot be null, and the primary key in the table needs to satisfy the condition:
• Arbitrarily two lines do not have the same primary key value;
• Each row must have a primary key value ( the primary key value does not agree to be null)
• The value in the primary key column does not agree to change or update

3. SQL (structured query Language) Structured Query Language

SQL is a language specifically designed to work with databases, and many database management system vendors have a corresponding extension of the SQL language, such as Mysql,oracle,sql server and so on, but most database vendors support SQL.

MySQL is installed and turned on 1. Install MySQL in ubuntu:

• simply install on-line to:
  

• You may have to enter password for MySQL root during the installation process, and you need to remember password. Open MySQL in the following ways:

  1) Check that the MySQL service is started:
  

  2) Open service:
  Indicates that the service has started, assuming it is not started. The MySQL service can be started using the following methods, such as:
  

  3) Log in using the root account:
  
  Middle-P Indicates input password, if no password is entered for root, then-p

2. View all databases on the system:

3. Link database:

4. View the contents of a specific database:

Retrieve database 1. The SELECT statement retrieves one or more data columns:

SELECTFROM 表名。

This is the simplest database statement to retrieve, such as:

Ability to enumerate multiple column names in a table. If you want to retrieve all the columns in a table, you can use the * wildcard character. Like what:

Suppose you want to not show the same value. Just want to show a different value. Ability to use keyword DISTINCT, such as:

2. Sort the results of the search

Way One:

SELECT 列名FROM 表名ORDER BY 一个列或者多个列名

Way two:

SELECT 列名FROM 表名ORDERBY2。3//表示以列名表中的第2列和第3列排序（优先第2列，第2列同样的情况下依据第3列排序）

Specifies whether the sort is descending or ascending, by default in ascending order by DESC (Descending) and ASC (ascending) , such as:

Note that the ORDER by statement should be the last sentence of the SELECT statement

3. Search through the WHERE filter * *

• Basic form:

  SELECT 列名FROM 表名WHERE 子句ORDER BY 子句；

• operator in the WHERE clause:

  operator	Description		operator	description
  =	Equals		>	Greater than
  <>	Not equal to		>=	Greater than or equal
  !=	Not equal to		！ >	No greater than
  <	Less than		Between	Between a specified two values
  <=	Less than or equal		Is NULL	is a null value
  ！ <	Not less than

• Example of using is NULL :
  

• Uses advanced data filtering in the where clause, such as and , or , in , not :

  select  pro_id, Prod_price, Prod_namefrom  Products< Span class= "Hljs-keyword" >where  vend_id =  ' DLL01 '  and  prod_price <= 4 ;   

  SELECT  pro_id, Prod_price, Prod_namefrom  productsWHERE  vend_id =  ' DLL01 '  or  vend_id =  ' BRS01 ' ;   

• The

• in operator is followed by a comma-delimited set of valid values that must be in parentheses, and the advantage of in is that it can include other SELECT statements to create a more dynamic where clause.

   Select  pro_id, Prod_price, Prod_namefrom  Products where  vend_id in  (,  ' BRS01 ' ) order  by  prod_name;   

• Uses wildcard characters for filtering. In order to use wildcards, you must use the like keyword in the where clause, such as the following wildcard characters:

  wildcard characters /th>	meaning
  %	No matter what characters appear no matter what the number of times
  _	matches only a single character
  []	to specify a character set

  -

  Here are a few examples of using wildcards:

  Note:
  1) do not overuse wildcards, and can be used in a different way than possible.
  2) try not to use wildcards at the beginning

• To create a calculated field:

  SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)‘)FROM VendorsORDER BY vend_name;--功能：SELECT完毕以下的拼接：--存储在vend_name中的名字；--包括一个空格和（的字符串；--存储在vend_country中的国家；--包括一个）的字符串。

• as Take aliases:

Database (i): overview