MySQL Database Basics (ii)

21. Querying data

Format:  Select [Field List]|* from table name  [where search condition]  [GROUP by Group Field [having sub condition]]  [ORDER by sort Asc|desc] 
   
    [limit Paging parameters]
   

Mysql>select * from Stu;

+----+----------+-----+-----+---------+| ID | Name     | age | sex | classid |+----+----------+-----+-----+---------+|  1 | Zhangsan |  20 | M   | lamp138  | |  2 | Lisi     |  20 | M   | lamp138  | |  3 | Wangwu   |  21 | W   | lamp138  | |  4 | Zhaoliu  |  25 | W   | lamp94  | |  5 | Uu01     |  26 | M   | lamp94  | |  6 | uu02     |  28 | W   | lamp92  | |  7 | qq02     |  24 | M   | lamp92  | |  8 | uu03     |  32 | M   | lamp138  | |  9 | qq03     |  23 | W   | lamp94  | | | AA       |  19 | M   | lamp138  | | one | sad      |  35 | M   | lamp94  | | | | TT       |  25 | M   | lamp92  | | | | wer      |  25 | W   | lamp94  | | | xx |  25 | M   | lamp92  | | | | KK       |   0 | W   | lamp94  |+----+----------+-----+-----+---------+

# #where条件查询1. Query the class for the lamp138 period of the student information mysql> select * from Stu where classid= ' lamp138 '; 2. Query male information for lamp138 period (sex is m) mysql> select * from Stu where classid= ' lamp138 ' and sex= ' m '; 3. The query ID number value is above 10 for student information mysql> select * from Stu where id>10;4. Find information about students aged 20-25 mysql> select * from Stu where age>=20 and age<=25;mysql> select * from Stu where age between and 25;5. Query information for students who are not at the age of 20-25 mysql> select * from Stu where ages not between and 25;mysql> select * from Stu where age<20 O R age>25;6.  Student information for Query ID value 1,8,4,10,14 SELECT * from Stu where ID in (1,8,4,10,14);mysql> select * from Stu where id=1 or id=8 or id=4 or id=10 or Id=14;7.  Query lamp138 and lamp94 girls information mysql> select * from Stu where ClassID in (' lamp138 ', ' lamp94 ') and sex= ' W ';mysql> select * FROM Stu where (classid= ' lamp138 ' or classid= ' lamp94 ') and sex= ' w# #LIKE clause then we need to use the SQL like clause in the WHERE clause. The percent% character is used in the LIKE clause to denote any character, similar to the asterisk * in UNIX or regular expressions. If percent% is not used, the LIKE clause has the same effect as equals sign =. Like is usually used in conjunction with%, similar to a meta-character search. You can use and or or to specify one orMultiple conditions. You can use the where ... in the DELETE or UPDATE command. The LIKE clause to specify the condition. You can use RegExp regular instead of like1. Query the Name field value is all information that begins with zh mysql> select * from Stu where name like "zh%";mysql> select * from Stu WH ere name regexp "^zh"; --Regular wording +----+----------+------+-----+---------+| ID | name | Age | sex | ClassID |+----+----------+------+-----+---------+| 14 |   Zhangle | 29 |       m |  5 | | 1 |   Zhangsan | 20 |       W |  1 | | 4 |   Zhaoliu | 21 |       m | 4 |+----+----------+------+-----+---------+2. Query name All information containing the ANG substring in name mysql> select * from Stu where name is like "%ang%"; Mysql> select * from Stu where name RegExp "Ang", +----+-----------+------+-----+---------+| ID | name | Age | sex |  ClassID |+----+-----------+------+-----+---------+| 1 |   Zhangsan | 20 |       W |  1 | | 3 |   Wangwu | 22 |       W | 5 | | 10 |   Xiaozhang | 19 |       W | 1 | | 13 |   Wangwen | 27 |       W | 2 | | 14 |   Zhangle | 29 |       m | 5 |+----+-----------+------+-----+---------+3. Query name is any four-character information。 Mysql> select * from Stu where name like "___";mysql> select * from Stu where name regexp "^[a-z0-9]{4}$"; +----+--- ---+------+-----+---------+| ID | name | Age | sex |  ClassID |+----+------+------+-----+---------+| 2 |   Lisi | 25 |       m |  2 | | 5 |   uu01 | 27 |       W |  1 | | 6 |   uu02 | 25 |       m |  2 | | 7 |   uu03 | 28 |       W |  2 | | 8 |   uu05 | 22 |       m | 4 |+----+------+------+-----+---------+

Create a table structure with the same structure as another table

CREATE table t3 like T1; Now the structure of the T3 table is the same as the structure of the T1 table, but be aware that the data is not

# #MySQL的统计函数 (aggregate function): Max () min () count () sum () AVG ()

1. What is the maximum, minimum and average age of the student table? Mysql> select Max (age), min (age), AVG (age) from stu;2. Gets the number of male m in the student table Mysql> Select COUNT (*) from Stu where sex= ' m ';

# #GROUP by Statement grouping

The GROUP BY statement groups The result set based on one or more columns.

 Statistics class information, grouped by sex, and statistics for each group of people;mysql> Select Sex,count (*) from Stu Group by sex; 1. Count the number of each class nysql> select Classid,count (*) from Stu Group by CLASSID;2. Count the number of boys and girls in each class.  Mysql> Select Classid,sex,count (*) from Stu Group by classid,sex;# #ORDER by sort--ASC default Ascending desc Descending we know that SQL is used from the MySQL table SELECT statement to read the data. If we need to sort the read data, we can use the MySQL ORDER BY clause to set which field you want to sort by, and then return the search results. You can use any of the fields as criteria for sorting, returning the sorted query results. You can set multiple fields to sort. You can use the ASC or DESC keyword to set the query result to be sorted in ascending or descending order. By default, it is sorted in ascending order. You can add WHERE ... The LIKE clause to set the condition.  1. Search for student information in ascending chronological order mysql> select * from Stu ORDER by age;mysql> SELECT * from Stu order by age ASC; --default ASC ascending can omit 2. Age Descending Sort mysql> select * from Stu ORDER by age desc;3. Query student information, sorted by class in ascending order, same class sorted in descending order mysql> select * FROM St U ORDER by classid asc,age desc;# #LIMIT keyword to query part of the data-for example: .... LIMIT m; The query data displays only the first m--for example: .... limit m,n; Exclude the former m, and then query out the top N 1. Query the first 5 messages mysql> select * from Stu limit 5;2. After excluding the first 2, obtain 4 messages mysql> select * from Stu limit 2, 4; 
 
 

22. Modify the data

    --change age of ID 11 to 35,sex to M value    mysql> update stu set age=35,sex= ' m ' where id=11;       ---Change the data value for ID values 12 and 14 to M,classid to lamp92    mysql> update stu set sex= ' m ', classid= ' lamp92 ' where id=12 or id=14-- Equivalent to the following    mysql> update stu set sex= ' m ', classid= ' lamp92 ' where ID in (12,14);

23. Delete operation

Format: Delete from table name [where condition]

     --Delete the data in the Stu table with ID value 100    mysql> delete from Stu where id=100;

    --delete data from the STU table with ID values from 20 to 30    mysql> Delete from Stu where id>=20 and id<=30;

    --delete data from the STU table with ID values from 20 to 30 (rank above)    mysql> Delete from Stu where ID between and 30;

    --delete data in Stu table with ID value greater than 200    mysql> delete from Stu where id>200;

24. Import and Export

Note: Do not log in to the MySQL service when you import and export data, to exit the MySQL service to operate

--Export all tables in the lamp138 database c:\>mysqldump-u root-p lamp138 > C:\lamp138.sqlEnter Password:----export lamp138 table in Stu database C  : \>mysqldump-u root-p lamp138 stu > C:\lamp138_stu.sqlEnter password:--Import All tables in lamp138 library to   c:\>mysql-u root -P lamp138 < C:\lamp138.sqlEnter password:--import stu table in lamp138 library c:\>mysql-u root-p lamp138 < C:\lamp138_stu.sqlEn ter password:*********************--Export all databases mysqldump-uroot-p password  --all-databases  --events >/tmp/ Bak.sql       All database backups

25.MySQL of Rights Management

Format: Grant permissions on the database. Data table to ' username ' @ ' login host '  identified by  ' password ' refresh privileges flush privileges;

Example: Grant Select,insert,update,delete on * * to  ' wjs ' @ ' percent ' identified by ' 12345 ';       

    Safe Practice   Grant Select,insert,update,delete on pass.* to ' root ' @ ' localhost '  identified by ' 12345 ';       Only in the localhost host inside the  pass database inside the data table to delete and    remove the user drop users ' xxoo ' @ '% '  

26. Role of the index:

Indexes are used in the database to improve search performance. We usually do the database optimization when the index is usually optimized, when the amount of data is not effective, the more data more obvious effect.

See which indexes are in a table

Show index from table name \g

Classification of indexes:

Regular Indexes (index)

The most basic index, without any restrictions

Add a regular index to a table field    CREATE index name on table name (field name)    ALTER TABLE name add index name (field name)    You can also add create table    T2 ( C4/>id Int (Ten) unsigned auto_increment primary key,            name varchar (255), index  index name (field name),        Delete table field General Index * *    Drop index name on table name    ALTER TABLE table name DROP INDEX name

Unique index (unique)

Unique indexes can be added to each field, the value of the field after the addition can not be repeated, the primary key index and a unique index is similar, but the primary key index in the table can only be added to a single word Cheri (usually added to the ID), the ID is self-increment, the index will not be duplicated when the occurrence

Add a unique index to a table field    create unique index name on table name (field name) ALTER TABLE name    add unique index name (field name) can also be added when creating a table    Tabl E T2 (            ID int () unsigned auto_increment primary key,            name varchar (255), unique  index name (field name),        Delete unique index C18/>drop index name on table name

primary key index (primary key)

A primary key index is the most common type of index in a relational database, and the primary role is to determine the location of a particular data record in the data table. We can add primary key after the field to set the primary key index for the field. Note: 1. It is a good idea to specify a primary key for each table, but not one that must be specified. 2. A table can only specify one primary key, and the value of the primary key cannot be null 3. Primary key can have multiple candidate indexes (for example, not null,auto_increment) to add a table field's primary key index    ALTER TABLE name add  primary KEY ( Field name) add self    -increment ALTER TABLE name modify ID int (4) auto_increment Delete primary key index    if the field has auto_increment and primary key, you need to delete the primary key, c5/> Delete the self-increment before deleting the primary key        delete the self-increment    ALTER TABLE name change field Name Field name Class name    Delete primary key    

Full-text index (fulltext)

A full-text index is an fulltext type index in MySQL, but the fulltext index can only be used on MyISAM tables and is created on columns of char, varchar, or text type, or on one or more data columns. Add a full-text index to a table field    ALTER TABLE name ADD Fulltext index name (field name) Delete Full-text index    DROP Index (index name) on table name    ALTER TABLE table name DROP INDEX name

Note: If you do not add an index name when creating an index, the field name will be used as the index name by default.

MySQL Database Basics (ii)