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

MySQL: relational database (Relational Database ManagementSystem)

This so-called "relational" can be understood as the concept of "table". A relational database consists of one or more tables.

Header (header): the name of each column

Row: a collection of data with the same data type

Col: the specific information used by each line to describe a person / thing

Value (value): specific information about the row, each value must be the same as the data type of the column

Key: the method used in a table to identify a particular person / object. The value of the key is unique in the current column.

Log in to MySQL:

Mysql-h hostname-u user name-p

-h: this command is used to specify the MySQL hostname to which the client wants to log in. This parameter can be omitted from the current machine.

-u: the user name to be logged in

-p: tells the server that a password will be used to log in, and this option can be ignored if the username and password you want to log in is empty.

You can use the show databases; command to see which databases have been created.

Create a database:

Create database database name [other options]

Create database test_db character set gbk

To facilitate the display of Chinese at the command prompt, the database character encoding is specified as gbk through character set gbk at creation time. When created successfully, you will get a response of Query OK, 1 row affected (0.02 sec).

Select the database you want to operate on:

One: specify when logging in to the database, command: database name selected by mysql-D-h hostname-u user name-p

For example, when logging in, select the database you just created: mysql-D samp_db-u root-p

Second: use use statement to specify after login, command: use database name

The use statement can execute use samp_db without a semicolon to select the database you just created. After the selection is successful, it will prompt: Database changed.

Create a database table:

Create table table name (column declaration)

Take the creation of the students table as an example, the table will store contents such as student number (id), name (name), gender (sex), age (age), and contact number (tel):

Create table students

(

Id int unsigned not null auto_increment primary key

Name char (8) not null

Sex char (4) not null

Age tinyint unsigned not null

Tel char (13) null default "-

);

"id" is the name of the column

"int" specifies that the type of the column is int (values range from-8388608 to 8388607). Later, we modify it with "unsigned" to indicate that the type is unsigned. In this case, the value of the column ranges from 0 to 16777215.

"not null" indicates that the value of the column cannot be empty and must be filled in. If you do not specify this attribute, it can be empty by default.

"auto_increment" needs to be used in an integer column, and its purpose is that when inserting data, if the column NULL, MySQL will automatically produce a unique identifier value that is larger than the existing value. There can be only one such value in each table and the column must be an index column.

"primary key" indicates that the column is the primary key of the table, the value of this column must be unique, and MySQL will automatically index the column.

The following char (8) indicates that the stored character length is 8, the value of tinyint ranges from-127to128, and the default attribute specifies the default value when the column value is empty.

Insert data into the table:

Insert [into] Table name [(column name 1, column name 2, column name 3,...)] Values (value 1, value 2, value 3,...)

Insert into students values (NULL, "Wang Gang", "male", 20, "13811371377")

Sometimes we only need to insert part of the data, or do not insert in the order of the columns, we can insert in this form:

Insert into students (name, sex, age) values (Sun Lihua, female, 21)

Query the data in the table:

Select column name from table name [query criteria]

For example, to query the names and ages of all students in the students table, enter the statement select name, and the result of age from students; execution is as follows:

Mysql > select name, age from students

You can also use the wildcard character * to query all the contents of the table, statement: select * from students

Query by specific criteria:

The where keyword is used to specify query conditions in the form of select column name from table name where condition

To search for information about women of all genders, enter the query sentence: select * from students where sex= "female"

The where clause supports not only the query form of "where column name = value", but also the operators of general comparison operations, such as =, >, =, 21.

Query for information about everyone with the word "king" in their name: select * from students where name like "% king%"

Query the information of everyone whose id is less than 5 and older than 20: select * from students where id20

Update the data in the table:

The update statement can be used to modify the data in a table. The basic form of use is:

Update table name set column name = new value where update condition

Examples of use:

Change the mobile phone number with id 5 to the default "-": update students set tel=default where id=5

Increase the age of all by 1: update students set age=age+1

Change the name of the mobile phone number 13288097888 to "Zhang Weipeng", age to 19: update students set name= "Zhang Weipeng", age=19 where tel= "13288097888"

Delete data from the table:

The delete statement is used to delete data from a table. The basic usage is as follows:

Delete from table name where delete condition

Examples of use:

Delete the line with id 2: delete from students where id=2

Delete all data under the age of 21: delete from students where age

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.