Test mysql: (1) database knowledge point _ MySQL

Test mysql: (1) database knowledge point bitsCN.com

I. Terms

1, Database)

2, Database management system (DBMS)

3. column/row)

4. Primary key)

5. Structured Query Language (Structured Query Language)

Features: (1) all DBMS support
(2) easy to learn, flexible, and powerful

II. common commands

6. MySQL options and parameters:

1: specify the user login name Ben:

   2:  mysql -u ben

3: provide the user name, host, port, and password:

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

(1) enter the command after mysql> and enter help or/h for help.

(2) run the command; or/g. enter quit and exit to exit the command line.

7. clause ):

Clause has the meanings of clauses and clauses. an SQL statement consists of clauses, which are optional and required. a clause usually consists of a keyword and provided data. For example, the FROM clause of the SELECT statement or the order by clause

8. do not use wildcard characters (*) because columns that are not required for retrieval may degrade the search or program performance.

9. the DISTINCT keyword indicates that Mysql returns only different values:

   1:  SELECT DISTINCT title FROM new

10. the LIMIT clause limits the returned results.

   1:  SELECT id,title FROM new LIMIT 3 

LIMIT m, n: Where m indicates starting from m, n indicates the number of rows to be retrieved

If the number of rows is not enough, search for the largest row.

Note: Mysql5 supports another LIMIT substitution syntax. LIMIT n OFFSET m: start from m to Qu n rows.

   1:  SELECT id,title FROM new LIMIT 1 OFFSET 3

   2:  SELECT id,title FROM new LIMIT 3,1

3: same effect

11. Use a fully qualified table name

   1:  SELECT new.id,new.title FROM new

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

12. order by clause

The order by clause extracts the names of one or more columns. Therefore, the output is sorted in ascending ORDER (ASC) BY default and can be sorted in descending ORDER (DESC ), note that DESC applies only to the column names directly located in front of DESC.

If you sort multiple columns in descending order, you must specify the DESC keyword in each column.

13. use the SHOW statement to find out the current database on the server.

   1:   SHOW DATABASES;

Followed by a plural number

14. create a database named 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 table structure

   1:  DESCRIBE product

Describe: Description and description

The following information is displayed in navicat for Mysql:

18. add records to the table

   1:  INSERT INTO product VALUES(15.00,'pen')

2: # insert multiple rows

   3:  INSERT INTO product (pro_price,pro_name) VALUES(12.00,' toothpaste'),

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

19. create an id in navcat for mysql

20. set character sets in navcat for mysql

Select 'product' table --- right-click '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 a table

   1:  mysql>drop TABLE MYTABLE;

22. clear the table

   1:  DELETE FROM new

23. update 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

Attention sequence

BitsCN.com