MySQL knowledge summary _ MySQL

MySQL knowledge summary bitsCN.com

Database Development history:
Hierarchical Model -->
Mesh Model (high coupling degree) -->
Relational model (object-relational model)

DBMS database management system
RDBMS relational database management system
Manage storage
Management security management
Manage metadata
Managing trasactions transaction management
Support expansion of supporting connectivity
Optimizing performance optimization
Providing back-up and recovery mechanisms backup and recovery
Processing requests for data retreval an modifaction data retrieval and modification

DataBase development direction:
Development DBA
Database Design
Stored Procedures, stored functions, triggers (SQL)
Manage DBA
Install, uninstall, and upgrade software
Data import and export
Data backup and recovery
User management and permission management
Security management
Architecture Design

Structured query langue
Built-in statements:
Select, update, drop, update...
Query statement: select, update, delate

Common Database management systems
Large:
Oracle
Sybase
Informix
DB2

Small:
SQL server

Open-Source:
Mysql
PostgreSQL --> EnterpriseDB (except for restoring mysql after the crash, other performance is quite superior)

Mysql installation and upgrade:
Mysql is thread-based.
DML data operation language
DDL data definition language
Storage Engine: converts a logical structure into a physical structure
Mysql version:
GA (general availability) stable version
RC release
Beta
Alpha test
Mysql software package
Rpm Package:
Mysql-client *
Mysql-debuginfo debugging information
Mysql-devel development Library *
Mysql-embedded mysql
Mysql-ndb-management mysql Cluster
Mysql-server port, providing mysqld *
Mysql-shared sharing tool *
Mysql-shared-compat provides
Mysql-test component

Linux startup search configuration order
/Etc/my. cnf -->/etc/mysql/my. cnf --> $ MYSQL_HOME/my. cnf -->/path/to/file when default-extra-file =/path/to/file is specifid --> ~ /. My. cnf
Start the search order. if there is a conflict, refer to the last one found. if there is no conflict, consider it comprehensively.
Query from start to end
After the installation is complete, mysql automatically generates five accounts and the password is blank.
Three administrator accounts: root @ localhost, root@127.0.0.1, root @ hostname
Two anonymous accounts: ''@ localhhost,'' @ hostname
# Mysql
> Use mysql to use mysql
> Show tables; displays TABLES
> DESC TABLE_NAE; display table format
> SELECT Host, User, Password FROM user; displays the specified columns in the table.
> SELECT * FROM user/G; display by column
> Dorp database db_name; delete a DATABASE
The first step after installing mysql is to set a password for the root user, delete anonymous users, and improve security.
Set password:
Method 1:
# Mysqladmin-uroot-hlocalhost password 'redhat' set the password:-u user name,-h host name
Method 2:
# Mysql
> UPDATE user SET Password = PASSWORD ('redhat') WHERE User = 'root' and Host = '2017. 0.0.1 ';
As long as the original password is the same, the encrypted password is the same.
> Flush privileges; notifies mysql to load the modified content to the memory.
Method 3:
# Mysql
> Set password for 'root' @ 'host _ name' = PASSWORD ('New _ password ');
> Flush privileges;
Delete anonymous users
> Drop user ''@ localhost;
> Drop user root @ ': 1 ';
> Drop user ''@ hostname;
> Flush privileges;
Authorization:
> Grant all privileges on *. * TO root @ '%' identified by 'redhat ';
> Flush privileges;
In this case, the window client can be connected.
Mysql AB tool:
Mysql
Mysqladmin
Mysqldump
Options for user:
-- User =-u user
-- Password =-p password
Options for host:
-- Protocol
The tcp client and the server are not on the same host.
The socket client and server are on the same host and Linux or unix
Pipe mysql-server on window
Memory mysql-server on window
-- Host =-h host
-- Port
-- Socket
-D db_name
-- Database db_name
Mysql applications:
Eg; mysql-uroot-h192.168.1.101-D mysql-p
> Select database (); displays the current default DATABASE
> Select user (); displays the current default USER
# Cd ~
# Vim. my. cnf
[Client]
User = root
Password = redhat
Then
# Mysql does not need to enter the password, but you should pay attention to the permission of this file

# Vim test. SQL
Create database test;
Batch processing method:
Method 1:
# Mysql-uroot-p <test. SQL
Method 2:
# Mysql
> SOURCE/etc/test. SQL;

Add ";" to the end of the server
The client does not need to add
> STATUS
Mysql prompt:
-> The command has not been executed.
'> The corresponding "'" is missing "'"
"> The corresponding" "is missing
/*> The corresponding Annotator is missing.

Database objects:
Table, two-dimensional relationship
Index
View
Trigger
Stored Procedure
Storage functions
Event scheduler
Cursor
User
Physical
File
Storage Engine
Plug-in storage engine


Mysql records the Command History and stores the. mysql_history file in the user's home directory.
Enable command completion
> /#
> Rehash
/C cancel command
/D defines the end symbol
Eg:/d // end //
/G ignores the default Terminator and sends it directly to the server for execution.
/G is displayed as a column
-E or -- veri
Help command view COMMAND HELP
-E
Mysql-e "show database" runs mysql statements directly in shell, which is often used in scripts.
Mysql-e "SELECT User, Host, Password FROM mysql. user;" to retrieve a table

Use of mysqladmin:
Mysqladmin [option] command [arg] [command]
# Ping mysqladmin to check whether the server is running
# Mysqladmin-h 172.16.100.1 ping
# Mysqladmin create sampledb create database
# Mysqladmin drop delete
# Mysqladmin debug debugging information (in the mysql data directory)
# Mysqladmin extended-status ==> show status;
# Mysqladmin flush-hosts
# Mysqladmin flush-logs
# Mysqladmin flush-privileges
# Myaqladmin flush-status
# Mysqladmin flush-tables clear the table
# Mysqladmin flush-thread
# Mysqladmin processlist
Reload
Refresh
Shutdown
Start-slave start slave server
Status: displays the global status information.
Variables
Version: displays the version number.
Status Display
# Mysqladmin status -- sleep 3 -- count 2
# Top-d 2-n 2 refresh once in two seconds, showing two exits
> Show variables like '% datadir %'; view the Directory of the data file

Mysql graphical tool:
SQLyog
MySQLFront
PhpMyAdmin
MySQL Query Browser
MySQL Administrator
MySQL Workbench

Mysql development knowledge:
> Use sampledb;
> Create table stus (Name CHAR (18), gender char (1 ));
NO NULL

> Show character set; View supported CHARACTER sets
> Show collation; view the sorting method
SELECT CURRENT_DATE ();
SELECT CURRENT_TIME ();
SELECT CURRENT_DATETIME ();
Create table user (id unsigned AUTO_INCREMENT not null primary key, Name VARCHAR (100) no null, Gender ENUM ('M', 'F') no null default 'M ', age tinyint unsigned );
Insert into user (Name, Age) values ('luowei', 18); INSERT
Show warnings; view error WARNINGS
SET SQL _mode = 'ansi 'sets the SQL model.
Show variables like "% SQL _mode %"; View model
Show variables like '% SQL _mode % ';
Common modes:
Ansi
Ignore_space
Strict_all_tables
Strict_trans_tables
Traditional
Mysql variable type
Global variable: Valid for each new session
Session variable: valid only for the current session
SHOW VARIABLES
Show global variables; Display GLOBAL VARIABLES
Show session variables; display SESSION VARIABLES
LIKE''
% String of any length
? Match
SELECT @ global. SQL _mod; clearly know the variable name
Modify variable value:
SET SQL _mode = 'traditional'
Set global SQL _mode = 'traditional'
The set session variable takes effect immediately, but is only valid for the current session
Setting global variables does not take effect immediately. logon again is valid for all
Case sensitivity:
Keywords and function names are case-insensitive, such as select
Table name. database names are case-insensitive on Windows and Linux
Stored procedures and stored functions are case insensitive.
Trigger case sensitive
Mysql aliases are case sensitive.
Database:
Create database db_name; CREATE
Create database if not exists mydb; it is commonly used in scripts and is created IF it does NOT exist.
Create database character set 'gbk' COLLATE 'gbk _ bin' mydb;
Show create database db_name; display the statement used to CREATE the DATABASE
Drop database db_name;
Drop database if exists db_name; delete DATABASE
Show wardings; displays a warning
Alter database db_name COLLATE chartset modify DATABASE

Tables & Index
Key, index
Candidate key
Primary key
Unique key
The foreign key used between the foreign key table and the table
Paradigm

Show engines; displays the system default engine. default is the default engine.
For mysql, if a foreign key is used, a transaction-supporting engine is required.
Index can accelerate the query speed, but modifying the database will reduce the speed.

Warn: unfinished, to be continued...
Author: "IT dream-Qi-sharing"

BitsCN.com