MySQL: Database Knowledge points

First, terminology

1, databases (database)

2. Database management System (DBMS)

3, Columns (column)/row (row)

4, PRIMARY key (Primary key)

5. SQL (structured query Language) Structured Query Language

Features: (1), all DBMS support

(2), simple and easy to learn, flexible and powerful

Second, one of the common commands

6, MySQL options and parameters:

1: Specify User login name Ben:

2:mysql-u, Ben.

3: Give the username, host, port, and password:

4:mysql-u ben-p-H myserver-p 9999

(1), command input after mysql>; Enter help or/h for assistance

(2), order use; or/g end; Enter quit, exit Exit command line

7, clause (clause):

Clause The meaning of clauses, clauses, SQL statements constitute, optional and necessary; a clause usually consists of a keyword and the data provided. such as the FROM clause of the SELECT statement, or the ORDER BY clause

8, wildcard (*) try not to use, because the retrieval of unwanted columns will reduce the retrieval or program performance.

9, DISTINCT keyword instructs MySQL to return only a different value:

1:select DISTINCT title from new

10, the limit clause restricts the results returned

1:select Id,title from New LIMIT 3

LIMIT M,n: where m represents starting from M row, N represents the number of rows to retrieve

Retrieves the maximum row when the number of rows is not sufficient.

Note: MYSQL5 supports another LIMIT substitution syntax, LIMIT n OFFSET m: curved n rows starting from M line

1:select Id,title from New LIMIT 1 OFFSET 3

2:select id,title from New LIMIT 3,1

3: The effect is the same

11. Use fully qualified table name

1:select New.id,new.title from New

2: Of course the table can also be used fully qualified such as: Mydata.new

12. ORDER BY clause

The ORDER BY clause takes out the name of one or more columns so that the output is sorted by default ascending (ASC), or Descending (DESC), and note that DESC applies only to the name of the column directly preceding it.

If you arrange in descending order in more than one column, you must specify the DESC keyword in each column

13. Use the show statement to find out what database is currently on the server

1:show DATABASES;

The back is plural.

14. Create a database Mysqldata

1:create DATABASE Mysqldata;

15. Create a database table

1:create TABLE Product (pro_id int,pro_price float,pro_name VARCHAR (20));

17, display the structure of the table

1:describe Product

Describe: Describe, describe

The following is shown in Navicat for MySQL:

18, add the record to the table

1:insert into Product VALUES (15.00, ' pen ')

2: #插入多行

3:insert into Product (pro_price,pro_name) VALUES (12.00, ' toothpaste '),

4: (18.00, ' belt '), (15.00, ' Basin '), (52.30, ' pot ')

19, in Navcat for MySQL to establish the ID

20. Set the character set in Navcat for MySQL

Select the ' Product ' table---Right ' design table '---

You can also set the primary key

In this way, we can insert Chinese data into the table;

1:insert into Product (pro_price,pro_name) VALUES (1.00, ' Softdrink '), (2.00, ' Mineral water ')

21, delete the table

1:mysql>drop TABLE MYTABLE;

22, empty the table

1:delete from New

23, update the data

1:update product SET pro_price=50.00 WHERE pro_name= ' pot '

24. Use the combination of order by and limit to find the highest or lowest value in a column:

1:select Pro_price,pro_name from Product order by Pro_price DESC LIMIT 1

Note Order