Mysql database learning notes-common operation commands

Mysql database learning notes common operation commands 1. create a database www.2cto.com mysql> create database user; Query OK, 1 row affected (0.00 sec) 2. use this database mysql> use user; database changed3. create a table mysql> create table person (-> id int unsigned not null auto_increment primary key,-> name varchar (30)->) on this Database; Query OK, 0 rows affected (0.00 sec) 4. view the table structure of the person table www.2cto.com 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 person_bak, the table structure of this table is the same as that of 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 into the person table mysql> insert into person (name) values ("user1"); Query OK, 1 row affected (0.00 sec) 7. Copy data from the person table to mysql> insert into person_bak select * from person; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 8. method 1 for creating an index for the name column in the person table: 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: 09. view the index www.2cto.com mysql> show index from person; + -------- + ------------ + ---------- + upper + ----------- + upper + ---------- + -------- + ------------ + --------- + | Table | Non_unique | Key_name | partition | Column_name | Collation | Cardinality | sub_part | Packed | Null | Index_type | Comment | + -------- + ------------ + ---------- + ------------ + accept + ----------- + accept + ---------- + -------- + ------ + ------------ + ----------- + | 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 mysql> alter table person add unique index un_name (name); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 011. modify the column attributes mysql> alter table person modify name varchar (20); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 012. 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 A View (same as the command used to view a table). When you delete a record in a table, the matching records in the view corresponding to this table will also be deleted from 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. String connection function --- concat ("string1", "string2") alias mysql> select concat ("li", "haichao ") myname; + ----------- + | myname | + ----------- + | lihaichao | + ----------- + 1 row in set (0.00 sec) 17. Convert uppercase to lowercase functions --- lcase (string1) mysql> select lcase ("LHC"); + -------------- + | lcase ("LHC") | + -------------- + | lhc | + -------------- + 1 row in set (0.00 sec) 18. Convert the string to a function of uppercase: ucase (string1); mysql> select ucase ("lhc"); + -------------- + | ucase ("lhc ") | + -------------- + | LHC | + -------------- + 1 row in set (0.00 sec) 19. Judge the length of the string function length (string1); mysql> select length ("lhc "); + --------------- + | length ("lhc") | + ----------------- + | 3 | + --------------- + 1 row in set (0.02 sec) 20. Remove the front-end and backend space functions ltrim () repeat (string, count) mysql> select repeat ("linux", 3 ); + ------------------- + | repeat ("linux", 3) | + ------------------- + | linuxlinuxlinux | + ------------------- + 1 row in set (0.02 sec) 22. Search for linux in "linux is very good" and replace it with phpmysql> select replace ("linux is very good", "linux ", "php"); + ------------------------------------------- + | replace ("linux is very good", "linux", "php ") | + trim + | 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 mysql> select substring ("linux is very good",) is obtained from int1 (counted from 1) to int2 (inclusive ); + response + | substring ("linux is very good", 0.00) | + response + | linux | + ----------------------------------------- + 1 row in set (sec) 24, space () functions: the space Generation function, usually used with the concat function mysql> select concat (space (50), "linux"); + ------------------------------------------------------- + | concat (space (50 ), "linux") | + bytes + | linux | + ----------------------------------------------------- + 1 row in set (0.02 sec) 25, decimal to binary function BIN () mysql> select BIN (255 ); + ---------- + | BIN (255) | + ---------- + | 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 taken down. For example, if 5.6 is set to 5, mysql> select ceiling (5.6); + -------------- + | ceiling (5.6) | + -------------- + | 6 | + -------------- + 1 row in set (0.01 sec) **************************************** * ****************************** mysql> select floor (5.6 ); + ------------ + | floor (5.6) | + ------------ + | 5 | + ------------ + 1 row in set (0.00 sec) 27. obtain 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)