Shulou(Shulou.com)06/01 Report--

The following mainly brings you the most complete introduction of Mysql database basic commands, I hope these contents can bring you practical use, this is also the main purpose of this article that I edit the most complete Mysql database basic commands to introduce this article. All right, don't talk too much nonsense, let's just read the following.

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 http://douer.blog.51cto.com/6107588/1933366

3. Create a database

Mysql > create database database_name default character set utf8

4. Selected database

Mysql > use database_name

5. Create a table

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. Create an 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); # create unique index

7. Modify the table

1) change the table name

Mysql > alter table table_name rename new_table_name

2) add 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) add 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) change the column definition

You can change the name or properties of a column 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 the column must be redefined!

Mysql > alter table table_name modify col_1 clo_2 varchar

8. Insert the table

Mysql > insert into table_name (column_1,column_2, … .) Values (value1,value2, …)

If you want to deposit a string, you need to enclose the string in single quotation marks, but you need to pay attention to the change of meaning of the character

Such as:

Mysql > insert into table_name (text_col,int_col) value (\ 'hello world\', 1)

The characters that need to be escaped are: single quotation mark 'double quotation mark' backslash\% underscore _

You can escape single quotation marks using two consecutive single quotation marks

9. Update the 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 the column

Mysql > show columns from table_name;mysql > show fields from table_name

13. Find statement

Mysql > select column_1,column_2,column_3 from table_name

14. Change the 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 * sign represents all tables, and the second * represents all tables under the 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 the columns that store strings (char, varchar, etc.), you need to enclose the string to be compared in single quotation marks in the where clause

Such as:

Mysql > select * from users where city = 'San Francisco'

You can compare several operators at a time by adding and or or to the where clause

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 to any operators in the table. For null values, you need to use is null or is not null predicates

Mysql > select * from users where zippers called 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

Use between to select values within a range, and between can be used for numbers, dates, and text strings.

Such as:

Mysql > select * from users where lastchanged between 20000614000000 and 20000614235959X MySQL > select * from users where lname between'a 'and'm'

18. Use in/not in

If a column may return several possible values, you can use the in predicate

Mysql > select * from users where state='RI' or state='NH' or state='VT' or state='MA' or state='ME'

It can be rewritten as:

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 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%'; #% matches zero characters mysql > select * from users where fname like' Jacuzzi characters; # matches any three-letter word that begins with J # like in mysql is case-insensitive

20 、 order by

The order by statement can specify the order of the rows returned in the query, and can sort any column type by placing asc or desc at the end to set the order in ascending or descending order. If not, asc is used by default.

Mysql > select * from users order by lname,fname

You can sort as many columns as you want, 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. You can specify the number of rows to start with and the number of rows you want to return.

Get the first five rows in the table:

Mysql > select * from users limit 0Pert MySQL > select * from users order by lname,fname limit 0Jol 5

Get the second five rows of the table:

Mysql > select * from users limit 5

22. Group by and aggregate function

After using group by, Mysql can create a temporary table to record all the information about rows and columns that meet the criteria.

Count () counts the number of rows in each collection

Mysql > select state,count (*) from users group by state

The * sign indicates that all rows in the collection should be calculated

Mysql > select count (*) from users

Calculate all the 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 you need more than one word for a column, enclose the text string in single quotation marks

Sum () returns the number of given columns

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

Restricts the rows displayed through group by, the where clause displays the rows used in group by, and the having clause restricts only the rows displayed.

23. Connection table

All the tables to be joined must be listed in the from section of the select statement, and the fields used for the join 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 ambiguous, you need to use table_name.column_name syntax to specify which table the field comes from

For the above about the most complete Mysql database basic command introduction, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.