Basic configuration of MySQL database

MySQL Basics

MySQL was developed by the Swedish MySQL AB company and currently belongs to Oracie Company
MySQL is an open-source relational database management system
MySQL is divided into Community Edition and Enterprise Edition
--------------------
Configuring the default character encoding
Open the My.ini (config file) in the MySQL installation directory
[Client] under: [MySQL] default-character-set=utf8//client display encoding method
[Mysqld] Under (server configuration):
[Mysqld]
basedir= "C:\Program files\mysql\ Mysql Server 5.5//" Program installation directory
datadir= "C:\Program files\mysql\ Mysql Server 5.5/data/"//Data storage directory
character-set-server=utf8//Server storage Mode

--------------------
Completion page, Launch the mysqlinstance configuration Wizard (MySQL configuration starts after installation is complete)
Configure MySQL
1. Enter C:\Program Files\mysql (typical mode after installation, will be in this path) \ Mysql Server 5.5 (5.5 is the version number) \ Bin
2. Locate the MySqlInstanceConfig.exe File Open (Configuration Wizard file)
3. Go to the Configuration Wizard and next
4. Select the configuration type (detailed detailed configuration) or (standard configuration)//recommended selection standard configuration
5. Whether to install as a Windows service
Install as Windows Service//whether installing Windows Services recommended check box
Service Name://Name the database
Launch the MySQL Server automatically//boot MySQL recommended check box
Include Bin directory in Windows PATH//Add bin subdirectory to System environment variable recommended check box
6. Set the root user password
--------------------
install MySQL
Install MySQL mode:
1>msi installation (Windows Installer)
Step 01. Double-click the MSI installation file
02. End User License Agreement
03. Select the Installation type
*typical: Typical installation (recommended)
*custom: Custom Installation
*complete: Fully Installed
04. Ready to install (click Back button to return to modify settings)
05. Installation Progress
06.MySQL Product Advertising
07.MySQL Product Advertising
08. Ask if the configuration operation can be configured directly
Additional configuration MySQL
01. Run the MySQL Configuration Wizard file: MySQLInstanceConfig.exe
02. Configuration Wizard Welcome Interface
03. Select a Configuration type
*detailed configuration, configuring in detail
*standard configuration, standard (recommended)
04. Whether to install as a Windows service
05. Set the root user's password. Enter the password two times OK
06. Prepare to perform setup options, click the Execute button directly
07. Configuration Complete
MySQL directory structure
*bin directory, storing executable files
*data directory, storing data files
*docs, Documentation
*include directory, storing the included header files
*lib Directory, repository file
*share, error messages and character set files
configuration options for MySQL
* Modify the Encoding method
[MySQL]
Default-character-set=utf8
[Mysqld]
Character-set-server=utf8
Modify the default encoding format under Linux:
Vim/etc/mysql/my.cnf
Add under [client]
Default-character-set=utf8
Under [mysqld], add
Character_set_server=utf8 (underline here)
Init_connect= ' Set NAMES utf8′ (or set NAMES ' utf8′)
Add under [MySQL]
Default-character-set=utf8
Configuration file under installation directory: My.ini
2>zip installation (difficult to master for beginners)

--------------------
Start, close MySQL
net start MySQL
net stop MySQL
All Windows services can be started, closed by them
Unable to start MySQL on the cmd interface:
1. You must run the CMD program with administrator status
2. If you download the MySQL5.7 version, the name of MySQL on the Windows service defaults to MySQL57, so running net start/stop mysql in cmd is invalid and must be changed to net Start/stop mysql57.
The startup error occurred:
There are two reasons for this situation
1. They say they don't have enough authority to C:\Windows\System32 find Cmd.exe and pull him to the start. Lock bar or desktop so it's easy to use
2. The root directory you installed under MySQL is similar to the C:\Program files\mysql\mysql Server 5.5
That is, depending on your Bin folder root directory name input net Stop/start MySQL Server 5.5 is available

--------------------
mysql Sign out
mysql login parameters
-u,--user name
-p,--password name password
-D,--database name open the specified database
-p,--Port # Port name
-H,--host Name server name
--delimiter name Specifies the delimiter
--prompt name set prompt
-v,--version output release information and exits (select version ();)
Mysql-v view version
Login exit:
mysql-uroot-proot-p3306-h127.0.0.1
Mysql>exit;
quit;
\q;
MySQL exit (exit quit \q)
MySQL's superuser name is: root
mysql-uroot-p://do not want others to see their password entered after the prompt to enter a password;
-p parameter is the current port number 3306; The
-h parameter is the server name, If you want to connect to the local server is 127.0.0.1 (local loopback address);
When the default port number is not modified-P can not be written, if the practical local server-H can also not add;
Issue: ERROR 1045 (28000): Access denied For user ' root ' @ ' localhost ' (using Password:yes)
FIX: 1. Add "Skip-grant-tables" to the bottom of the My.ini file in the MySQL directory and save and close the file
2. CMD in restart MySQL service
3.cmd input "Mysql-uroot-p", enter (I am prompted to enter the password, the password entered is the password originally set)
4. Remove the content that you added in step 1 to save and close the file
5. Restart the MySQL service to verify that you can successfully log in to MySQL

--------------------
Modify the MySQL prompt
To modify the MySQL prompt: 2 different ways
(1) specify by parameter when connecting to client
Shell> Mysql-uroot-proot--prompt prompt
(2) After connecting the client, modify by Prompt command
Mysql>prompt prompt
For example:
C:\users\sean>mysql-uroot-p.--prompt \h
localhost
Localhostprompt mysql>
PROMPT set to ' mysql> '
Mysql>prompt \[email protected]\h \d>
PROMPT set to ' \[email protected]\h \d> '//format
[Email protected] (none) >
To modify the MySQL prompt:
1. Pre-logon: Shell>mysql-u user name-p password--prompt prompt
2. After login: mysql>prompt prompt
\d Full Date
\d Current Database
\h Server Name
\u Current User

mysql statement specification
MySQL statement specification:
① keyword and function name all caps
② database name, table name, field name all lowercase
The ③sql statement must end with a semicolon
Common statements
Select version (), query the current server version
Select Now (), query the current datetime
SELECT USER (), query the current user
prompt meaning
Mysql> ready to accept the new order.
-Wait for the next line of multiple-line commands.
> waits for the next line, waiting for the end of the string to begin with single quotation marks ("'").
> Wait for the next line, waiting for the end of the string to start with double quotation marks ("" ").
' > waits for the next line, waiting for the end of the qualifier to start with a backslash (' ').
/*> waits for the next line, waiting for the end of the comment to start with/*.

--------------------
manipulating databases
Preliminary mysql--Operation Database
First, database creation: Create
1. Syntax: CREATE {DATABASE | SCHEMA} [IF not EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name.
2, database and schema are the same, choose one
3, if not EXISTS: if the creation of the database exists, not only reported warning, not write will report an error
4. Chrarcter Set GBK: Set the encoding method for the table, if not set then use MySQL default encoding method
Ii. viewing a list of databases: Show
1. SHOW {DATABASE | SCHEMAS} [like ' Pattern ' | WHERE Expr]
2, Show CREATE database xx: show XX databases information
Iii. modification of the database: Alter
1. Modify the database encoding method: ALTER {db | SCHEMAS} [Db_name][default] CHARACTER SET [=] Charset_name
Iv. Deleting a database: Drop
1. Delete database: drop {db | SCHEMAS} [IF EXISTS] db_name;

Mysql-uroot-proot prompt \d--Modify the MySQL operator to the current date
Show databases--Show all databases
Create database if not EXISTS T1 character set GBK; --Create a database
Show CREATE DATABASE T1; --Display the creation command and encoding form of database T1
--Modify the database encoding format
ALTER DATABASE T2 character Set =utf8;
--Delete Database
Drop database if exists T1;
--Display warning message
Show warnings;

Basic configuration of the MySQL database