1. When you need to install mysql database through yum
First you need to update the yum source
[root@server ~]# rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
[root@server ~]# yum install mysql-community-server mysql-community-devel
2. Source Package Installation
Reference resources http://douer.blog.51cto.com/6107588/1933366
3. Creating a database
mysql> create database database_name default character set utf8;
4. Selected database
mysql> use database_name;
5. Create tables
mysql> create table table_name ->( ->column_1 column_type column attributes, ->column_2 column_type column attributes, ->column_3 column_type column attributes, ->primary key (column_name), ->index index_name(column_name) ->) engine=innodb default charset=utf8 auto_increment=1;
6. Creating Index
mysql> alter table table_name add index index_name(column_name); mysql> create index index_name on table_name(column_name); mysql> create unique index index_name on table_name(column_name); #Establishing Unique Index
7. Modification of tables
1) Change the table name
mysql> alter table table_name rename new_table_name;
2) Adding columns
mysql> alter table table_name add column column_name colomn attributes;
For example:
mysql> alter table my_table add column my_column text not null;
First specifies that the inserted column is in the first column of the table
After puts the new column after the existing column
For example:
mysql> alter table my_table add column my_col text not null first； mysql> alter table my_table add column my_col text not null after my_other _column;
3) Delete columns
mysql> alter table table_name drop column column_name;
4) Adding Index
mysql> alter table table_name add index index_name (column_name1,column_name2,......); mysql> alter table table_name add unique index_name (column_name); mysql> alter table table_name add primary key(my_column);
Delete index
mysql> alter table table_name drop index index_name;
Such as:
mysql> alter table test10 drop primary key;
5) Changing column definitions
The name or property of a column can be changed with the change or modify command. To change the name of a column, you must also redefine the properties of the column. For example:
mysql> alter table table_name change original_column_name new_column_name int not null;
Note: The properties of columns must be redefined!!!
mysql> alter table table_name modify col_1 clo_2 varchar(200);
8. Insert table
mysql> insert into table_name (column_1,column_2,.....) values (value1,value2,......);
If you want to save a string, you need to enclose the string with a single quotation mark "'", but you need to pay attention to the meaning of the character.
Such as:
mysql> insert into table_name (text_col,int_col) value (\'hello world\',1);
Characters that need to be escaped are: single quotation mark double quotation mark backslash percent mark underscore_
You can use two single quotation marks to escape single quotation marks in succession
9. Update table
mysql> updata table_name set col__1=vaule_1 where col=vaule;
10. Delete tables/libraries
mysql> drop table table_name; mysql> drop database database_name;
11. View tables/libraries
mysql> show tables; mysql> show databases;
12. View the properties and types of columns
mysql> show columns from table_name; mysql> show fields from table_name;
13. Search statement
mysql> select column_1,column_2,column_3 from table_name;
14. Modifying Password
mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';mysql> alter user root@localhost identified by '123456';
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';mysqladmin -uroot -p old_password password new_password
15. User Authorization
mysql> GRANT ALL PRIVILEGES ON mysql.* TO tom@% identified by '123456';
The first * number represents all tables, and the second * represents all tables under the modified database.
16. Use where
Restrict the rows of records returned from the query (select)
mysql> select * from table_name where user_id = 2;
If you want to compare columns of storage strings (char, varchar, etc.), you need to enclose the strings to be compared in single quotation marks in the where clause.
Such as:
mysql> select * from users where city ='San Francisco';
By adding an and or to the where clause, you can compare several operators at a time
mysql> select * from users where userid=1 or city='San Francisco'; mysql> select 8 from users where state='CA' and city='San Francisco';
Note: Null values cannot be compared with any operator in the table. For null values, you need to use is null or is not null predicates
mysql> select * from users where zip!='1111′ or zip='1111′ or zip is null;
If you want to find all records that contain any values (except null values), you can
mysql> select * from table_name where zip is not null;
17. Use between
Using between, you can select values within a certain range, and between can be used for numbers, dates, and text strings.
Such as:
mysql> select * from users where lastchanged between 20000614000000 and 20000614235959; mysql> select * from users where lname between 'a' and 'm';
18. Use in/not in
If a column may return several possible values, the in predicate can be used
mysql> select * from users where state='RI' or state='NH' or state='VT' or state='MA' or state='ME';
It can be rewritten as follows:
mysql> select * from users where state in ('RI','NH','VY','MA','ME');If you want to achieve the same result, but the result set is the opposite, you can use the not in predicate
mysql> select * from user where state not in ('RI','NH','VT','MA','ME');19. Use like
If you need to use wildcards, use like
mysql> select * from users where fname like 'Dan%'; #% Match zero characters mysql> select * from users where fname like 'J___'; #Match any three-letter word beginning with J #like is case-insensitive in mysql
20,order by
The order by statement can specify the order of rows returned in the query. It can sort any column type by placing ASC or desc at the end to set ascending or descending order. If not, the default is asc.
mysql> select * from users order by lname,fname;
You can sort as many columns as you need, or you can mix asc and desc
mysql> select * from users order by lname asc, fname desc;
21,limit
limit limits the number of rows returned from the query, specifying the number of rows that start and the number of rows that you want to return
Get the first five rows in the table:
mysql> select * from users limit 0,5; mysql> select * from users order by lname,fname limit 0,5;
Get the second five rows of the table:
mysql> select * from users limit 5,5;
22. group by and aggregation function
With group by, Mysql can create a temporary table that records all information about rows and columns that meet the criteria.
count() Calculates the number of rows in each collection
mysql> select state,count(*) from users group by state;
* Number indicates that all rows in the collection should be computed
mysql> select count(*) from users;
Calculate all rows in the table
You can use the word as after any function or column name and then specify a name as an alias. If the required column name exceeds one word, enclose the text string with single quotation marks
sum() returns the number of columns given
min() gets the minimum value in each set
max() gets the maximum value in each set
avg() returns the mean value of the set
having
Restrict rows displayed through group by, where clause shows rows used in group by, and having clause only limits rows displayed.
23. Connection table
All tables to be connected must be listed in the from section of the select statement, and the fields used for the connection must be displayed in the where section.
mysql> select * from companies,contacts where companies.company_ID=contacts.company_ID;
When the reference to a field name is not clear, you need to use the table_name.column_name grammar to specify which table the field comes from.
Reference resources: http://sun510.blog.51cto.com/9640486/1926622