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

This article mainly introduces some common sense related to MySQL, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand some common sense related to MySQL.

I. download and install MySQL

(1) you can choose Linux General to download the compiler-free tar.gz package. After decompression, the specific operation can be done according to

(1), mv mysql-5.1.40-linux-i686-icc-glibc23 / usr/local/mysql

(2) establish mysql users: useradd-s / sbin/nologin mysql

(3) initialize the database

[root@localhost src] # cd / usr/local/mysql [root@localhost mysql] # mkdir-p / data/mysql; chown-R mysql:mysql / data/mysql [root@localhost mysql] #. / scripts/mysql_install_db-- user=mysql-- datadir=/data/mysql

(4), copy configuration file

[root@localhost mysql] # cp support-files/my-large.cnf / etc/my.cnf [root@localhost mysql] # cp support-files/mysql.server / etc/init.d/mysqld [root@localhost mysql] # chmod 755 / etc/init.d/mysqld (6), [root@localhost mysql] # vim / etc/init.d/mysqld

The place that needs to be modified is "datadir=/data/mysql" (the directory defined earlier when initializing the database)

(7) add the startup script to the system service item, and set the boot to start mysql.

[root@localhost mysql] # chkconfig-- add mysqld [root@localhost mysql] # chkconfig mysqld on [root@localhost mysql] # service mysqld start

If you can't start, check the error log under / data/mysql/, which is usually the hostname .err. The command to check whether mysql is started is: [root@localhost mysql] # ps aux | grep mysqld

(2) you can also download the rpm package and install it directly with rpm-ivh filename

Rpm-ivh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

Yum install mysql mysql-community-server-y

Second, common operations

Flushprivileges; / / after the permissions are modified, refreshing the system permissions table of MySQL will take effect.

(1) backup and restore of MySQL under Linux

1. Backup

[root@localhost ~] # cd / var/lib/mysql (go to the MySQL library directory and adjust the directory according to your MySQL installation)

[root@localhost mysql] # mysqldump-u root-p voice > voice.sql, enter the password.

two。 Reduction

Law one:

[root@localhost ~] # mysql-u root-p enter the password and enter "mysql >" on the console of MySQL.

Law II:

[root@localhost ~] # cd / var/lib/mysql (go to the MySQL library directory and adjust the directory according to your MySQL installation)

[root@localhost mysql] # mysql-u root-p voiceCREATEUSER name IDENTIFIED BY 'ssapdrow'

2. Change the password:

> SETPASSWORD FOR name=PASSWORD ('fdddfd')

3. Rights management

> SHOWGRANTS FOR name; / / View name user permissions

GRANTSELECT ON db_name.* TO name;// gives name users all permissions on the db_name database

> reverse operation of REVOKESELECT ON db_name.* TO name;//GRANT to remove permissions

1. Database operation:

1. View the database:

> SHOWDATABASES

2. Create a database:

> CREATEDATABASE db_name;//db_name is the database name

3. Use the database:

> USEdb_name

4. Delete the database:

> DROPDATABASE db_name

Second, create a table:

1. Create tables:

> CREATETABLE table_name

> idTINYINT UNSIGNED NOT NULL AUTO_INCREMENT,// id value, unsigned, non-empty, incremental-unique, can be used as a primary key.

> nameVARCHAR (60) NOT NULL

> scoreTINYINT UNSIGNED NOT NULL DEFAULT 0Grady / set the default column value

> PRIMARYKEY (id)

> ENGINE=InnoDB// sets the storage engine of tables. InnoDB and MyISAM;InnoDB are commonly used to be reliable and support transactions. MyISAM does not support full-text retrieval.

> DEFAULTcharset=utf8;// sets the default encoding to prevent garbled codes in the database

If you can conditionally create a data table, you can also use > CREATE TABLE IF NOT EXISTS tb_name (.

2. Copy the table:

> CREATETABLE tb_name2 SELECT * FROM tb_name

Or partial copy:

> CREATETABLE tb_name2 SELECT id,name FROM tb_name

3. Create a temporary table:

> CREATETEMPORARY TABLE tb_name (this is the same as creating a normal table)

4. View the available tables in the database:

> SHOWTABLES

5. View the structure of the table:

> DESCRIBEtb_name

You can also use:

> SHOWCOLUMNS in tb_name; / / from is also fine

6. Delete the table:

> DROP [TEMPORARY] TABLE [IF EXISTS] tb_name [, tb_name2.]

Example:

> DROPTABLE IF EXISTS tb_name

7. Rename the table:

> RENAMETABLE name_old TO name_new

You can also use:

> ALTERTABLE name_old RENAME name_new

3. Modify the table:

1. Change the table structure:

> ALTERTABLE tb_name ADD [CHANGE,RENAME,DROP]... What you want to change.

Example:

> ALTERTABLE tb_name ADD COLUMN address varchar (80) NOT NULL

> ALTERTABLE tb_name DROP address

> ALTERTABLE tb_name CHANGE score score SMALLINT (4) NOT NULL

4. Insert data:

1. Insert data:

> INSERTINTO tb_name (id,name,score) VALUES (NULL,' Zhang San', 140), (NULL,' Zhang Si', 178), (NULL,' Zhang Wu', 134)

Here, insert multiple pieces of data directly after the comma, directly write to the inserted data; the primary key id is a self-increasing column, you don't have to write.

2. Insert the retrieved data:

INSERTINTO tb_name (name,score) SELECT name,score FROM tb_name2

5. Update data:

1. Specify update data:

> UPDATEtb_name SET score=189 WHERE id=2

> UPDATEtablename SET columnName=NewValue [WHERE condition]

6. Delete data:

1. Delete data:

> DELETEFROM tb_name WHERE id=3

7. Condition control:

1. WHERE statement:

> SELECT* FROM tb_name WHERE id=3

2. HAVING statement:

> SELECT* FROM tb_name GROUP BY score HAVING count (*) > 2

3. Related condition control characters:

=, >, SELECT* FROM tb_name WHERE name REGEXP'^ [Amurd]'/ / find the name that starts with Amurd

2. special characters need to be escaped.

9. Some functions of MySQL:

1. String link-CONCAT ()

> SELECTCONCAT (name,'= >', score) FROM tb_name

2. Mathematical function:

AVG 、 SUM 、 MAX 、 MIN 、 COUNT

3. Text processing function:

TRIM 、 LOCATE 、 UPPER 、 LOWER 、 SUBSTRING

4. Operator:

+, -, *,\

5. Time function:

DATE (), CURTIME (), DAY (), YEAR (), NOW ().

10. Group query:

1. Grouping queries can be grouped according to the specified columns:

> SELECTCOUNT (*) FROM tb_name GROUP BY score HAVING COUNT (*) > 1

2. Conditional use of Having

3. ORDER BY sorting:

ORDERBY DESC | ASC= > sort by data in descending and ascending order

11. UNION rules-two statements can be executed (duplicate lines can be removed)

Full-text search-- MATCH and AGAINST

1. SELECTMATCH (note_text) AGAINST ('PICASO') FROM tb_name

2. InnoDB engine does not support full-text search, MyISAM can

XIII. View

1. Create a view

> CREATEVIEW name AS SELECT * FROM tb_name WHERE ~ ~ ORDER BY ~ ~

2. The special function of view:

A. Simplify joins between tables (write joins in select)

B, reformat output retrieved data (TRIM,CONCAT and other functions)

C. Filter unwanted data (select section)

D. use the view to calculate field values, such as summarization.

Flip-flop:

A trigger is a trigger that triggers a specified action within a trigger when a specified operation is performed

1. The statements that support triggers include DELETE, INSERT and UPDATE, but none of them are supported.

2. Create a trigger:

> CREATETRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name

The INSERT statement, which triggers the statement and returns a value

3. Delete trigger

> DROPTRIGGER trig

III. Several ways of copying tables by MySQL

CREATE TABLE IF NOT EXISTS `admin` (

`id`int (6) unsigned NOT NULL auto_increment

`username`varchar (50) NOT NULL default''

`password` varchar (100) default NULL

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4

1. The following statement copies the table structure to the new table newadmin. (data in the table will not be copied)

CREATE TABLE newadmin LIKE admin

two。 The following statement copies the data to the new table. Note: this statement is really just a table of the results of the select statement. So newadmin this table will not have a primary key, index.

CREATE TABLE newadmin AS

(SELECT * FROM admin)

3. If you want to actually copy a table. You can use the following statement.

CREATE TABLE newadmin LIKE admin

INSERT INTO newadmin SELECT * FROM admin

4. We can operate different databases.

CREATE TABLE newadmin LIKE shop.admin

CREATE TABLE newshop.newadmin LIKE shop.admin

5. We can also copy some of the fields in a table.

CREATE TABLE newadmin AS

(

SELECT username, password FROM admin

)

6. We can also rename the fields of the newly created table.

CREATE TABLE newadmin AS

(

SELECT id, username AS uname, password AS pass FROM admin

)

7. We can also copy some of the data.

CREATE TABLE newadmin AS

(SELECT * FROM admin WHERE LEFT (username,1) ='s')

8. We can also define the field information in the table while creating the table.

CREATE TABLE newadmin (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) AS (SELECT * FROM admin)

After reading some common sense about MySQL, many readers must have some understanding. If you need more industry knowledge and information, you can continue to pay attention to our industry information column.

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.