MySQL Database basic commands Daquan

1. When you need to install MySQL database via Yum

First, we need to update the Yum source

[Email protected] ~]# RPM-UVH http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

[email protected] ~]# 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. Select 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_n AME) 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); #建立唯一索引

7, modify the table

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 top column of the table

After the new column is placed after the column that already exists

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_c Olumn);

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 property of a column by using the ALTER 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: You must redefine the properties of the column!!!

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 quote "'", but be aware of the character's meaning

Such as:

Mysql> INSERT INTO table_name (TEXT_COL,INT_COL) value (\ ' Hello world\ ', 1);

Characters that need to be escaped are: single quote ' double quotation mark ' backslash \ percent% underline _

Single quotes can be escaped using two single quotes in a row

9. Update table

Mysql> Updata table_name set col__1=vaule_1 where Col=vaule;

10. Delete the table/library

mysql> drop table table_name;mysql> drop database database_name;

11. View the table/library

Mysql> show tables;mysql> show databases;

12. View the properties and types of a column

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

13. Find Statements

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 [email protected] 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 the mysql.* to [e-mail protected]% identified by ' 123456 ';

The first * number represents all tables, and the second * represents all tables under the change database.

16. Use where

Limit the rows of records returned from a query (select)

Mysql> SELECT * FROM table_name where user_id = 2;

If you want to compare columns that store strings (char, varchar, and so on), you need to enclose the strings to be compared in single quotes in the WHERE clause.

Such as:

Mysql> SELECT * from the 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 the users where userid=1 or city= ' San Francisco ';mysql> Select 8 from users where state= ' CA ' and CI ty= ' San Francisco ';

NOTE: null values cannot be compared with any operators in the table, and for null values you need to use the is null or is NOT NULL predicate

Mysql> SELECT * from the users where zip!= ' 1111′or zip= ' 1111′or zip is null;

If you want to find all of the records that contain any value (other than null values), you can

Mysql> SELECT * FROM table_name where ZIP is not null;

17. Using between

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

Such as:

Mysql> SELECT * from the users where lastchanged between 20000614000000 and 20000614235959;mysql> select * from users WH ere lname between ' a ' and ' m ';

18. Using 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 ';

Can be rewritten as:

Mysql> SELECT * from the users where state in (' RI ', ' NH ', ' VY ', ' MA ', ' ME ');

If you want to achieve the same result, but the result set is reversed, you can use the not in predicate

Mysql> SELECT * from user where the state is not in (' RI ', ' NH ', ' VT ', ' MA ', ' ME ');

19. Use Like

If you need to use wildcards, you want to use a like

Mysql> SELECT * from the users where fname like ' dan% ';    #% matches 0 Characters mysql> select * from the users where fname like ' j___ '; #匹配以J开头的任意三字母词 #mysql中like不区分字母大小写

20. ORDER BY

An ORDER BY statement can specify the order of the rows returned in the query, sort any column type by placing ASC or DESC at the end to set in ascending or descending order, or, if not set, by default using ASC

Mysql> SELECT * from the users order by Lname,fname;

You can sort by as many columns as you want, or you can mix ASC and DESC

Mysql> SELECT * from the 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 and the number of rows you want to return

Get the first 5 rows in the table:

Mysql> SELECT * from the users limit 0,5;mysql> select * from the users order by Lname,fname limit 0, 5;

Get the second 5 rows of a table:

Mysql> SELECT * from Users limit 5, 5;

22. Group BY and aggregation functions

After using group BY, MySQL can create a temporary table that records all the information about the rows and columns that meet the criteria

Count () calculates the number of rows in each collection

Mysql> Select State,count (*) from the Users group by State;

* number indicates that all rows in the collection should be evaluated

Mysql> Select COUNT (*) from users;

Count all rows in a table

You can use the Word as after any function or column name, and then specify a name for the alias. If you need more than one word for a column name, enclose the text string in single quotation marks.

SUM () returns the number of the given column

Min () Gets the minimum value in each set

Max () Gets the maximum value in each collection

AVG () returns the product mean value of the collection

Having

Limits the rows displayed through group BY, where clauses display the rows used in group by, and the HAVING clause restricts only the rows that are displayed.

23. Connection Table

All tables to be joined 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 a reference to a field name is ambiguous, you need to use the Table_name.column_name syntax to specify which table the field is from

Reference: http://sun510.blog.51cto.com/9640486/1926622

http://907832555.blog.51cto.com/4033334/1926563

This article is from the "Mq_douer" blog, make sure to keep this source http://douer.blog.51cto.com/6107588/1934762

MySQL Database basic commands Daquan