Chapter 1 Introduction to MySQL
What is Mysql
MySQL is a relational database management system developed by MySQL AB and currently owned by Oracle.
Mysql is open source and customizable. Under the GPL, you can modify the source code to develop your own Mysql system.
MySQL uses the standard SQL data language format.
Mysql is available on multiple systems and supports multiple languages. These languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.
MySQL supports large databases and data warehouses with 50 million records. The maximum size of 32-bit system table files is 4GB, and the maximum size of 64-bit system table files is 8TB.
2. Install Mysql on Linux
2.1 Preparations
2.1.1 Checking whether Mysql has been installed in the current system
(1) CentOS6 environment
Command: RPM – qa | grep mysql
The default Linux installation comes with mysql related components.
Run the RPM -e –nodeps mysql-libs command to uninstall mysql
(2) CentOS7 environment
Command: RPM – qa | grep mariadb
The default Linux (CentOS7) comes with mariadb(the full open source version of mysql) related components when installed.
Run the RPM -e –nodeps mariadb-libs command to uninstall the mariadb
2.1.2 Checking the permission of the/TMP folder
/ TMP folder permissions:
Grant the maximum permission to chmod -r 777 / TMP
2.2 installing Mysql
The installed version is mysql 5.5, official website to download address: dev.mysql.com/downloads/m…
2.2.1 Copying the RPM installation package to the opt directory
2.2.2 RPM Installation in the Installation directory
(1) the RPM – the ivh MySQL – the client – 5.5.54-1. There. X86_64. RPM
(2) the RPM — the ivh MySQL server – 5.5.54-1. There. X86_64. RPM
After the installation is complete, the following message is displayed, and you need to set the password of user root for the software.
③ Run mysqladmin –version to check whether the installation is successful
④ Set the user name and password to mysqladmin -u root password XXXXXX
2.3.1 Starting and Stopping the Mysql Service
Check the service mysql status
Parameter Path Description Remarks
–datadir /var/lib/mysql/ directory where the mysql database file is stored
–basedir /usr/bin Related command directory mysqladmin mysqldump commands
–plugin-dir /usr/lib64/mysql/plugin Specifies the directory where the mysql plug-in is stored
– log – error/var/lib/mysql/jack atguigu. Err mysql error log path
– pid – the file/var/lib/mysql/jack atguigu. Pid process pid file
– the socket/var/lib/mysql/mysql. The sock local connection with Unix socket file/usr/share/mysql configuration file directory mysql and configuration file/etc/init script. D/mysql service start-stop scripts
2.3.3 Automatic startup of Mysql Service
Mysql service starts automatically upon startup!
2.3.4 Mysql Repeated Startup Problem symptom
2.4 Modifying a Character Set
2.4.1 Common Commands
SQL statement Description Remarks
Show databases lists all databases
Create Database Database name Creates a database
Create database character set UTF8 create database character set UTF-8
Show create database Database name Query the character set of the database
Show variables like ‘%char%
Set [character set attribute]=utf8 Set the corresponding attribute to UTF8 is a temporary change, currently valid. After the service is restarted, it becomes invalid.
Alter DATABASE database name character set ‘utf8’ Changes the character set of the database
Alter table name convert to character set ‘utf8’ Alter table name convert to character set ‘utf8’ alter table name convert to character set ‘utF8
2.4.2 Cause of garbled Characters in character Set
If the character set is not specified during database construction, the default character set latin1 is used, which does not contain Chinese characters. To view the implied coded character set:
2.4.3 Permanent Change
(1) Modify the configuration file
Go to /usr/share/mysql.find the configuration file named my.cnf and copy it to /etc/my.cnf and name it my.cnf. Add the following information and restart the service. Note: You must copy the file to the specified path and name it my.cnf
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
Look again:
Note: the Settings of existing databases do not change, and parameter changes only apply to new databases!
(2) Modify the character set of the created database and table
Modify the character set of the database
mysql> alter database mydb character set ‘utf8’;
Modify the character set of the data table
mysql> alter table mytbl convert to character set ‘utf8’;
(3) Modify the garbled data
No modification of the mysql configuration file or modification of the library or table character set can change the data that has become garbled. It can only be completely resolved by deleting data and re-inserting or updating data.
2.5 Setting Case Insensitive
2.5.1 Checking case sensitivity
show variables like ‘%lower_case_table_names%’
Windows systems are case insensitive by default, but Linux systems are case sensitive
Add lower_case_table_names = 1 to the my.cnf configuration file [mysqld] and restart the server
Property Settings are described
0 Case sensitive
1 case insensitive. The created tables and databases are stored in lowercase on the disk. SQL statements are converted to lowercase to search for tables and DB
2 create tables and DB according to the statement format stored, all lookup is converted to lowercase for
Note: If you want to make the properties case-insensitive, you will need to convert the original database and tables to lowercase before restarting the database instance, otherwise the database name will not be found. Before setting database parameters, you must understand the impact of the parameters.
2.6 sql_mode
Sql_mode specifies the syntax of the SQL statement in Mysql.
Sql_mode is a variable that is easily ignored. The default value is null, allowing illegal operations such as the insertion of illegal data. This value must be set to strict mode in the production environment, so the databases in the development and test environments must also be set so that problems can be found during development testing.
2.6.1 SQL_mode Common value
ONLY_FULL_GROUP_BY
For GROUP BY aggregations, if the column in SELECT does not appear in GROUP BY, then the SQL is illegal because the column is not in the GROUP BY clause
NO_AUTO_VALUE_ON_ZERO
This value affects the insertion of self-growing columns. By default, inserting 0 or NULL generates the next self-increment value. This option is useful if the user wants to insert a value of 0 and the column is self-growing
STRICT_TRANS_TABLES
In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, with no restrictions on non-transaction tables
NO_ZERO_IN_DATE
In strict mode, zero dates and months are not allowed
NO_ZERO_DATE
If set to this value, the mysql database does not allow the insertion of zero dates, and inserting zero dates will raise an error rather than a warning
ERROR_FOR_DIVISION_BY_ZERO
If data is divided by zero during an INSERT or UPDATE, an error is generated rather than a warning. If the schema is not given, MySQL returns NULL when the data is divided by zero
NO_AUTO_CREATE_USER
GRANT is prohibited from creating a user with an empty password
NO_ENGINE_SUBSTITUTION
If the desired storage engine is disabled or not compiled, an error is thrown. When this value is not set, the default storage engine is used instead and an exception is thrown
PIPES_AS_CONCAT
Will “| |” as a connection string operator rather than the or operator, it is the same and the Oracle database, and string concatenation function Concat also similar
ANSI_QUOTES
With ANSI_QUOTES enabled, strings cannot be quoted in double quotes because they are interpreted as identifiers
ORACLE
Set to be the same as PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
2.6.2 Viewing and Modifying information
① Check the current sql_mode: select @@sql_mode;
Sql_mode: group by query syntax error!
CREATE TABLE tbl (id INT,NAME VARCHAR(200),age INT,dept INT); INSERT INTO TBL VALUES (1, 'zhang3, 33101); INSERT INTO TBL VALUES (2, 'li4, 34101); INSERT INTO TBL VALUES (3, 'wang5, 34102); INSERT INTO TBL VALUES (4, 'zhao6, 34102); INSERT INTO TBL VALUES (5, 'tian7, 36102);Copy the codeSELECT NAME,dept,MAX(age) FROM TBL GROUP BY DEPT;
Correct way to write:
SELECT id,name,ab.dept,ab.maxage
FROM tbl m
INNER JOIN
(SELECT dept,MAX(age)maxage FROM tbl GROUP BY dept) ab
ON ab.dept = m.dept AND m.age=ab.maxage;
Set @@sql_mode= ‘ ‘;
(4) You need to modify it permanently in configuration file my.cnf
[mysqld] add sql_mode=” and restart mysql
Chapter 2 MySql user and permission management
1. Mysql user management
1.1 Related Commands
Command Description Remarks Create user zhang3 identified by ‘123123’; Create user name zhang3 and password 123123. select host,user,password,select_priv,insert_priv,drop_priv from mysql.user; Update mysql.user set password=password(‘123456’) where update mysql.user set password=password(‘123456’) where update mysql.user set password=password(‘123456’) where update mysql.user set password=password(‘123456′) where user=’li4′; Modify the passwords of other users. All changes made through the user table must be flush PRIVILEGES. Update mysql.user set user=’li4′ where user=’wang5′; Modify user name All changes made through the user table must be flush PRIVILEGES. Run the drop user li4 command to drop a user. Do not run the delete from user u where user=’li4’ command to delete a user. Residual system information will be reserved.