Mysql database learning notes-common Operation Commands-MySQL

Mysql database learning notes-common operation commands bitsCN.com

Mysql database learning notes-common Operation Commands

1. create a database

Mysql> create database user;

Query OK, 1 row affected (0.00 sec)

2. use this database

Mysql> use user;

Database changed

3. create a table in this database

Mysql> create table person (

-> Id int unsigned not null auto_increment primary key,

-> Name varchar (30)

-> );

Query OK, 0 rows affected (0.00 sec)

4. view the table structure of the person table

Mysql> desc person;

+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +

| Id | int (10) unsigned | NO | PRI | NULL | auto_increment |

| Name | varchar (30) | YES | NULL |

+ ------- + ------------------ + ------ + ----- + --------- + ---------------- +

2 rows in set (0.00 sec)

5. create a person_bak with the same table structure as person, that is, copy the table structure of person.

Mysql> create table person_bak like person;

Query OK, 0 rows affected (0.01 sec)

6. Insert data to the person table

Mysql> insert into person (name) values ("user1 ");

Query OK, 1 row affected (0.00 sec)

7. copy the data in the person table to the person_bak table.

Mysql> insert into person_bak select * from person;

Query OK, 10 rows affected (0.01 sec)

Records: 10 Duplicates: 0 Warnings: 0

8. create an index for the name column in the person table.

Method 1:

Mysql> create index in_name on person (name );

Query OK, 10 rows affected (0.00 sec)

Records: 10 Duplicates: 0 Warnings: 0

Method 2:

Mysql> alter table person add index in_name (name );

Query OK, 10 rows affected (0.01 sec)

Records: 10 Duplicates: 0 Warnings: 0

9. View indexes

Mysql> show index from person;

+ -------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+ -------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +

| Person | 0 | PRIMARY | 1 | id | A | 10 | NULL | BTREE |

| Person | 1 | in_name | 1 | name | A | NULL | YES | BTREE |

+ -------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +

2 rows in set (0.01 sec)

10. create a unique index in the person table

Mysql> alter table person add unique index un_name (name );

Query OK, 10 rows affected (0.01 sec)

Records: 10 Duplicates: 0 Warnings: 0

11. modify column attributes

Mysql> alter table person modify name varchar (20 );

Query OK, 10 rows affected (0.01 sec)

Records: 10 Duplicates: 0 Warnings: 0

12. data in the statistical table

Mysql> select count (*) from person;

+ ---------- +

| Count (*) |

+ ---------- +

| 10 |

+ ---------- +

1 row in set (0.00 sec)

13. create a view

Mysql> create view v_person as select * from person;

Query OK, 0 rows affected (0.01 sec)

14. view the view (the same as the command used to view the table)

When you delete a record in a table, the records that meet the conditions in the corresponding view of the table will also be deleted.

Mysql> show tables;

+ ---------------- +

| Tables_in_user |

+ ---------------- +

| Person |

| Person_bak |

| V_person |

+ ---------------- +

3 rows in set (0.00 sec)

15. delete a View

Mysql> drop view v_person;

Query OK, 0 rows affected (0.00 sec)

16. concat ("string1", "string2") alias

Mysql> select concat ("li", "haichao") myname;

+ ----------- +

| Myname |

+ ----------- +

| Lihaichao |

+ ----------- +

1 row in set (0.00 sec)

17. Convert data in uppercase to lowercase-lcase (string1)

Mysql> select lcase ("LHC ");

+ -------------- +

| Lcase ("LHC") |

+ -------------- +

| Lhc |

+ -------------- +

1 row in set (0.00 sec)

18. convert the string to a function of big writing ---- ucase (string1 );

Mysql> select ucase ("lhc ");

+ -------------- +

| Ucase ("lhc") |

+ -------------- +

| LHC |

+ -------------- +

1 row in set (0.00 sec)

19. the length (string1) of the string );

Mysql> select length ("lhc ");

+ --------------- +

| Length ("lhc") |

+ --------------- +

| 3 |

+ --------------- +

1 row in set (0.02 sec)

20. remove the frontend and backend space functions ltrim () and rtrim ()

21. repeat (string, count) the specified string for n times)

Mysql> select repeat ("linux", 3 );

+ ------------------- +

| Repeat ("linux", 3) |

+ ------------------- +

| Linuxlinuxlinux |

+ ------------------- +

1 row in set (0.02 sec)

22. string replacement function

Find linux in "linux is very good" and replace it with php

Mysql> select replace ("linux is very good", "linux", "php ");

+ --------------------------------------------- +

| Replace ("linux is very good", "linux", "php") |

+ --------------------------------------------- +

| Php is very good |

+ --------------------------------------------- +

1 row in set (0.01 sec)

23. string truncation function substring ("str", int 1, int 2)

In the str string, the field is obtained from int1 (counted from 1) to int2 (inclusive ).

Mysql> select substring ("linux is very good", 1, 5 );

+ ------------------------------------- +

| Substring ("linux is very good", 1, 5) |

+ ------------------------------------- +

| Linux |

+ ------------------------------------- +

1 row in set (0.00 sec)

24. space () function: space generation function, usually used with the concat function

Mysql> select concat (space (50), "linux ");

+ ----------------------------------------------------------- +

| Concat (space (50), "linux") |

+ ----------------------------------------------------------- +

| Linux |

+ ----------------------------------------------------------- +

1 row in set (0.02 sec)

25. Convert decimal to binary function BIN ()

Mysql & gt; select BIN (255 );

+ ---------- +

| Bind (255) |

+ ---------- +

| 1, 11111111 |

+ ---------- +

1 row in set (0.00 sec)

26. The entire function CEILING () is obtained upwards. for example, if 5.6 is set to 6, the entire floor () is obtained downwards. if 5.6 is set to 5

Mysql> select ceiling (5.6 );

+ -------------- +

| Ceiling (5.6) |

+ -------------- +

| 6 |

+ -------------- +

1 row in set (0.01 sec)

**************************************** ********************************

Mysql & gt; select floor (5.6 );

+ ------------ +

| Floor (10, 5.6) |

+ ------------ +

| 5 |

+ ------------ +

1 row in set (0.00 sec)

27. take the maximum and minimum values.

Select sudent_name, MIN (test_score), MAX (test_score) from student group by student_name;

28. return random number: RAND ()

Mysql> select ceiling (10 * RAND ());

+ --------------------- +

| Ceiling (10 * RAND () |

+ --------------------- +

| 4 |

+ --------------------- +

1 row in set (0.00 sec)

BitsCN.com