Mysql basics: database creation, addition, deletion, modification, and query

Mysql basics: database creation, add, delete, modify, and query ============== view the database ============================ ==== use the SHOW statement to find out the current database on the server: [SQL] mysql> SHOW DATABASES; + ---------- + | Database | + ---------- + | mysql | test | tmp | + ---------- + mysql is required, because it describes the user's access permissions, the test database is often used as a workspace for users to try their best. Note: If you do not have the show databases permission, you cannot see that all DATABASES have multiple DATABASES. You must select one database before operating the database. use the mysql Command> USE testDatabase command to note that USE, similar to QUIT, a semicolon is not required. ================ Create a database, table ====================================== = the create command is used to create a database [SQL] mysql> create database tmp; query OK, 1 row affected (0.03 sec) mysql> use tmp // use this Database changed the Database you need to re-use the use command to select the Database for each login, if you do not want to be so troublesome, you can specify the # mysql-uroot-p Password Database Name When logging on # after creating a database, you can create a table and view the current table first. Use show tables; [SQL] mysql> show tables; Empty set (0.00 sec) // currently Empty table mysql> create table person (name VARCHAR (20), sex char (1 ), score int (3 ));/ /Table name "person". The name type is varchar. The length is 20, sex is 1-bit char, and score is 3. The integer type indicates the information and data type of the table, use the describe table name ============================== to fill in the table content ============ ============================== after creating a table, enter the content. You can use the load data and INSERT statements to complete the task. Because you started from an empty table, one simple way to fill it is to create a text file, each row of each person, and then load the file content into the table with a statement. You can CREATE an external person.txt file. Each line contains a record, which is separated by a tab and given in the order of the columns listed in the create table statement. You can use NULL for missing values (such as unknown gender, or dead dates of living animals. To represent the content in your text file, use \ N (backslash, letter N ). For example, the record of the Whistler bird should be (the blank space between values here is a locator ): load one or more [SQL] mysql> insert into person values ('sin', 'M', '90') by using the insert statement; Query OK, 1 row affected (0.00 sec) There are two ways to modify the table content: 1. Clear the table content and load the modified file content from the file again, then load [SQL] mysql> delete from person; Query OK, 3 rows affected (0.00 sec) with the load data command again) mysql> load data local infile '/root/person.txt' into table person; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Ski Pped: 0 Warnings: 0 2. Use the update command [SQL] mysql> update person set sex = "m" where name = "hexizhuo"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from person; + -------------- + ------ + ------- + | name | sex | score | + -------------- + ------ + ------- + | chen <span style = "white-space: pre "> </span> | m | 100 | yang | m | 60 | hex | m | 80 | xin | m | 90 | + ---- ---------- + ------ + ------- + 4 rows in set (0.00 sec) mysql ============================================= query table content ============== ========================== use the select command to retrieve information from the data table. The general statement format is: mysql> SELECT what_to_select FROM which_table WHERE conditions_to_satisfy; what_to_select indicates the content you want to see. It can be a table of a column, or * indicates "all columns ". Which_table indicates the table from which you want to retrieve data. The WHERE clause is optional. If this option is selected, conditions_to_satisfy specifies the search criteria that the row must meet. [SQL] mysql> select * from person; // retrieve all information + -------------- + ------ + ------- + | name | sex | score | + -------------- + ------ + ------- + | chen | m | 100 | yang | m | 60 | hex | NULL | 80 | xin | m | 90 | + -------------- + ------ + ------- + 4 rows in set (0.00 sec) mysql> select * from person where name = "chenjiankang"; + -------------- + ------ + ------- + | name | sex | score | + -------------- + ----- -+ ------- + | Chen | m | 100 | + -------------- + ------ + ------- + 1 row in set (0.00 sec) mysql> // The logical operators and, another one is or mysql> select * from person where sex = "m" and score> 80; + -------------- + ------ + ------- + | name | sex | score | + -------------- + ------ + ------- + | chen | m | 100 | xin | m | 90 | + ------------ + ------ + ------- + 2 rows in set (0.00 sec) mysql> select name, sex from person; // You can select certain columns. Add where to filter data + -------------- + ------ + | name | sex | + -------------- + ------ + | chen | m | yang | m | hex | m | xin | m | + -------------- + ------ + 4 rows in set (0.00 sec) mysql> select name, sex from person where sex = "f"; Empty set (0.00 sec) If two of your data are the same, for example, two names = qq, mysql> select name from person; + ---------- + | name | + ---------- + | siasjack | qq | hehe | xin | qq | + ------- --- + 5 rows in set (0.00 sec) do not want him to appear here multiple times, but only once. You can add the distinct option mysql> select distinct name from person; + ---------- + | name | + ---------- + | siasjack | qq | hehe | xin | + ---------- + 4 rows in set (0.00 sec) sorting printing function use the order option, to print the score in reverse order, add the desc parameter mysql> select name and score from person order by score; + ---------- + ------- + | name | score | + ---------- + ------- + | qq | 60 | hehe | 80 | qq | 88 | xin | 9 0 | siasjack | 100 | + ---------- + ------- + 5 rows in set (0.00 sec) mode matching MySQL provides standard SQL mode matching, and an extended regular expression pattern matching format based on Unix utilities such as vi, grep, and sed. SQL mode matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. The following are some examples. Note that = or! =; Instead, use the LIKE or not like comparison operator. [SQL] mysql> select * from person where name like '% sias %'; // The name starts with sias, or + ---------- + ------ + ------- + | name | sex | score | + ---------- + ------ + ------- + | siasjack | m | 100 | + ---------- + ------ + ------- + 1 row in set (0.00 sec) mysql> select * from person where name like '____'; // four characters + ------ + ------- + | name | sex | score | + ------ + ------- + | hehe | f | 80 | + ------ + ------- + 1 row in set (0.00 sec)