Basic operations of the database 2

Mysql-day03
One, MySQL storage engine
1.1 Storage Engine Introduction: Is the MySQL database software comes with the function program,
Each storage engine has different functions and data storage methods
The storage engine handles the processor for the table

1.2 View what are the storage engines supported by the database service?
Mysql> show engines;
InnoDB DEFAULT

1.3 Viewing the storage engine used by existing tables
Show create table table name;

1.4 Modifying the storage engine used by the database service by default
] #vim/etc/my.cnf
[Mysqld]
Default-storage-engine=myisam
: Wq
]# systemctl Restart Mysqld

1.5 Modify the storage engine used by the table, or specify the storage engine used by the table when the table is built
ALTER TABLE Table engine= storage engine name;
Create TABLE table (field list) engine= storage engine name;

1.6 Features of common storage engines
InnoDB Features:
Support for transactions, transaction rollback, row-level locks, foreign keys
Storage mode: One table for 2 storage files
Table name. FRM table Structure
Table name. IBD Data and Index

MyISAM Features
Transaction, transaction Rollback, foreign key not supported
Support for table-level locks
Storage mode: One table for 3 storage files
Table name. FRM table Structure
The table name. MYD data
The table name. MYI Index

Transactions: Access to the database service (Connection database server operation data is disconnected)

Transaction rollback: During the execution of a transaction, any one-step operation fails and all previous operations are resumed.
A transaction-enabled table has a corresponding transaction log file record

Card (connect to database server)
Transfer: Offset card number 888888
Amount 50000
Ok
Prompt transfer Success-50000 +50000
Prompt transfer failed +50000
Return card

The MySQL database service uses transaction log files to record the SQL operations performed on the InnoDB Storage engine table.
cd/var/lib/mysql/
Ib_logfile0-|
|------> Log SQL commands
Ib_logfile1-|
INSERT into T1 values (8888);
IBDATA1----> Data source (data information generated after SQL command execution)

Size of Lock:
Table-level Lock (MyISAM) to the entire table locking (the entire table will be locked regardless of whether you visit a row or a few lines)
Row-level locks (InnoDB) lock only the rows currently being manipulated in the table

Role of the Lock: Resolves a concurrency access violation to a table.
SELECT * from t1 where ID <=20;
Insert
Delete from T1;
Update T1 set name= "Bob" where Name= "Lucy";
Update T1 set name= "Tom" where Name= "Jerry";
Depending on the access type of the client the lock is divided into read lock and write lock
Lock type
Read lock (Shared lock) Select
Write lock (Mutex/exclusive lock) Insert Update Delete

Transaction characteristics (ACID)
? Atomic: atomicity
– The entire operation of a transaction is a whole, indivisible, either wholly successful, or all fails.
? Consistency: Consistency such as bank transfer
– The records in the table have not changed before or after the transaction operation.
? Isolation: Isolation
– Transaction operations are isolated from each other and are not affected.
? Durability: Persistence
– Once the data is submitted, it cannot be changed and the table data is permanently changed

1.7 How to decide when tables are built using that storage engine
Tables that perform many write operations are suitable for use with the InnoDB storage engine, which supports row-level locks, which can have a large amount of concurrent access to the table.
Performing a multi-check table suitable for use with the MyISAM storage engine can save system resources, this engine supports table-level locks,
++++++++++++++++++++++++++++++++++++++
Ii. Data import and export (bulk operation data)
2.1 Data Import command format and data import considerations
command format for importing data:

Data import: Store the contents of the system files in the table of the database server.
Save the information of the user of the system to the Usertab table under the DB3 library.

Create a Storage data table
Create Database db3;
CREATE TABLE Db3.usertab (
Username char (50),
Password char (1),
UID Int (2),
GID Int (2),
Comment Char (100),
Homedir char (100),
Shell Char (50),
Index (username)
);
Desc Db3.usertab;
SELECT * from Db3.usertab;

Import data
]# cp/etc/passwd/var/lib/mysql-files/

Mysql>
Load data infile "/VAR/LIB/MYSQL-FILES/PASSWD"
into table Db3.usertab
Fields terminated by ":"
Lines terminated by "\ n";

Mysql> ALTER TABLE Db3.usertab
Add
ID int (2) primary key auto_increment first;

Mysql> Select from db3.usertab;
Mysql> Select from Db3.usertab where id=20;

Load data infile "/MYSQLDATA/PASSWD"
into table Db3.usertab
Fields terminated by ":"
Lines terminated by "\ n";

2.2 Data Export command format and data export considerations

Mysql>select Username,uid from Db3.usertab to outfile "/mysqldata/user1.txt";

Mysql>select * from Db3.usertab to outfile "/mysqldata/user2.txt";

Mysql>select Username,uid from Db3.usertab to OutFile "/mysqldata/user3.txt" Fields terminated by "# #";

]# Cat/mysqldata/user1.txt
]# Cat/mysqldata/user2.txt
]# Cat/mysqldata/user3.txt

Iii. Management table Records (DB3.USERTAB)
Inserting records

mysql> INSERT INTO Usertab
Values
("Yaya", "X", 1001,1001, "", "/home/yaya", "/bin/bash");

mysql> INSERT INTO Usertab
VALUES ("Yaya2", "X", 1002,1002, "", "/home/yaya2", "/sbin/nologin"), (Wuyi, "7yaya", "X", 1003,1003, "", "/home/7yaya", " /sbin/nologin ");

Insert into Usertab (Username,homedir,shell)
Values
("Lucy", "/home/lucy", "/bin/bash");

Insert into Usertab (Username,homedir,shell)
Values
("Lu8cy", "/home/lu8cy", "/bin/bash"), ("Tom", "/home/tom", "/bin/bash"), ("Lilei", "/home/lilei", "/bin/bash");

+++++++++ Viewing Records
SELECT * from Db3.usertab;

SELECT * from usertab where id = 1;

Select Id,username,password from Db3.usertab;

Select Username,uid,shell from usertab where id = 1;

------Modify Records
Update Db3.usertab set password= "A";
Update Db3.usertab set password= "x" where id=1;
SELECT * from Db3.usertab;

-----Delete Records
Delete from Db3.usertab where id=3;

Four, matching conditions (view selcet Modify update delete deleted record can be added conditions)

4.1 Numeric comparison field name symbol number
= = = < <= > >=

Select username from Usertab where uid=10;
Select Id,username,uid from Usertab where uid=1001;
SELECT * FROM Usertab where id<=10;

4.2-Character comparison field name symbol "string"
= !=
Select username from usertab where username= "Apache";
Select Username,shell from Usertab where shell= "/bin/bash";
Select Username,shell from Usertab where shell!= "/bin/bash";

4.3 In-range comparison
Field name between Number 1 and number 2 in ... Between...
Field name in (Value list) in ... In
Field name not in (value list) is not ... In
Select username from Usertab where uid between and 150;

Select Username,uid from Usertab where UID in (10,20,30,50);

Select Username,uid from Usertab where username in ("root", "rsync", "MySQL");

Select username from Usertab where username not in ("root", "bin");

4.4 Logical comparison (that is, there is a query condition)
Logic matches and multiple conditions are set at the same time
Logical or multiple conditions, a certain condition is set to match
Logical Non! Or not take the reverse

Select Username,uid from Usertab where username= "root" and uid=0 and Shell= "/bin/bash";

Select Username,uid from Usertab where username= "root" or uid=1 or shell= "/bin/bash";

Select Username,uid from Usertab where username= "root" or username= "Apache" or username= "Bob";

4.5 Match NULL field name is NULL
Match empty field name is NOT NULL

Select Username,uid,gid from Usertab
where
UID is null and GID is null;

mysql> Update usertab set uid=3000,gid=3000 where username= "Lucy";

The select ID from Usertab where name= "Yaya" and UID are NOT null;

Update Usertab set username=null where id=2;

4.6 Fuzzy Matching
Field name like ' expression ';
% represents 0 or more characters
_ Table any one character

Select username from Usertab where username like ' ;
Select username from Usertab where username like ' A_ _t ';

INSERT into Usertab (username) VALUES ("a");

Select username from Usertab where username like ' a% ';
Select username from Usertab where username like '%';

4.7 Regular Matches
Field name RegExp ' Regular expression ';
^ $ . * [ ]
Select username from Usertab where username regexp ' [0-9] ';
Select username from Usertab where username regexp ' ^[0-9] ';
Select username from Usertab where username regexp ' [0-9]$ ';

Select username from Usertab where username regexp ' a.*t ';

Select username from Usertab where username regexp ' ^a.*t$ ';

Select Username,uid from Usertab where uid regexp '. ';
Select Username,uid from Usertab where uid regexp ' ^. $ ';

4.7 Arithmetic (SELECT and update operations are available for mathematical calculations)
field type must be numeric type (integer or floating point type)

• • • / %

Select Id,username,uid from Usertab where ID <=10;
Update Usertab set uid=uid+1 where ID <=10;

Select Username, uid,gid from usertab where usernane= "MySQL";
Select Username, Uid,gid, uid+gid as en from usertab where username= "MySQL";

Select Username, Uid,gid, uid+gid as EN, (uid+gid)/2 as PJZ from Usertab where username= "MySQL";

ALTER TABLE Usertab add age tinyint (2) unsigned default, after username;

Mysql> select Username,age from Usertab;
Select Username, age, 2018-age s_year from Usertab where username= "root";

4.9 Aggregation functions (Statistics on field values, field type requirement is numeric type)
Count (field name) Number of statistic field values
sum (field name) sum
Max (field name) The maximum value of the output field value
Min (field name) The minimum value of the output field value
AVG (field name) output field value average
Select Max (UID) from Usertab;
Select SUM (UID) from Usertab;
Select min (uid) from Usertab;
Select AVG (UID) from Usertab;
Select COUNT (id) from Usertab;
Select COUNT (username) from Usertab where shell= "/bin/bash";

4.10 Query does not show field duplicate value distinct field name

Select distinct shell from Usertab;

Select distinct shell from Usertab where UID >10 and uid<=100;

4.11 Query Grouping
SQL query Group by field name;

Select shell from Usertab where UID >10 and uid<=100
Group BY Shell;

4.12 Query Sort (queued by field of numeric type)
SQL query order by field name Asc|desc;

Select Username,uid from Usertab where UID >10 and uid<=100 an order by UID;

Select Username,uid from Usertab where UID >10 and uid<=100 ORDER by uid Desc;

Query result filtering
Basic usage
–sql query having conditional expressions;
–sql query Where condition having conditional expression;
–sql Query Group By field name having conditional expression;

4.13 Limit the number of rows the query displays (the record for all queries is displayed by default)
SQL query limit number; Display first few lines of query results
SQL query Limit number 1, number 2, display query results specified range of rows

Select Username,uid from Usertab where UID >10 and uid<=100
ORDER BY uid DESC LIMIT 1;

Select Username,uid from Usertab where UID >10 and uid<=100
ORDER BY uid DESC limit 2, 3;

##########################################################################################
One, multi-table query
1.1 Copying tables
Role? Backup tables and Quick build tables
Command format? CREATE TABLE Library. Table SQL query command;

Example?
CREATE TABLE Db3.user2 select * from Db3.usertab;

CREATE TABLE Db3.user3 Select Username,uid,shell from Db3.usertab limit 5;

Create Database DB4;
CREATE TABLE DB4.T1 SELECT * from db3.usertab where 1 = 2;

CREATE TABLE Db4.t2 Select Id,username,uid,homedir from db3.usertab where 1 = 2;

1.2 Where nested queries
Select Username,uid from Db3.usertab where UID < (select AVG (UID) from Db3.usertab)

;

Mysql> Select Username,uid from Db3.usertab where UID > (select AVG (UID) from

Db3.usertab);

Select username from Db3.usertab
where username in
(select User from mysql.user where host= "localhost");

1.3 Multi-Table query
Mysql> CREATE TABLE Db4.t3
Select Username,uid,shell,homedir from Db3.usertab
, limit 3;

Mysql> CREATE TABLE Db4.t4
Select Username,uid,gid from Db3.usertab limit 5;

                     3  * 5 =  

SELECT * from T3,t4; Cartesian set

Mysql> Select T3.username,t4.username from T3,t4
, where
-T3.username = T4.username;

Mysql> Select T3.*,t4.username from t3,t4 where t3.username = T4.username;

SELECT * FROM T3,t4
where
T3.uid = T4.uid;

Select t3.*, T4.gid from T3,t4
where
T3.uid = T4.uid;

Select T3.username, t4.username from T3,t4
where
T3.uid = T4.uid;

Select T3.username,t4.username from T3,t4
where
T3.uid = T4.uid
And t3.username is not NULL
And t4.username is not null;

1.4 Connection Query
Mysql> CREATE TABLE Db4.t5
Select Username,uid,gid,shell from Db3.usertab
where uid>=100 and uid<=500;

Mysql> CREATE TABLE Db4.t6
Select Username,uid,gid,shell from Db3.usertab
where uid>=100 and uid<=500 limit 3;

SELECT * from T6 right join T5 on
T6.uid = T5.uid;

SELECT * from T6 left join T5 on t6.uid = T5.uid;

Select T5.username,t6.username from T6 right join T5 on
T6.uid = T5.uid;

Select T5.username,t6.username from T6 left join T5 on t6.uid = T5.uid;

2.2 Installing the graphical management tools on the database server phpMyAdmin
Prepare the operating environment for the software LAMP/LNMP
]# rpm-q httpd php php-mysql
]# yum-y Install httpd php php-mysql
]# systemctl Status httpd
] #systemctl Restart httpd
] #systemctl Enable httpd

Test Run environment
[Email protected] mysql]# vim/var/www/html/test.php
<?php
$x =mysql_connect ("localhost", "root", "123456");
if ($x) {echo "OK";} else{echo "No";};
?>
[Email protected] mysql]#
[Email protected] mysql]# yum-y install ELinks

]# elinks--dump http://localhost/test.php
Ok

Installing the Software phpmyadmin-2.11.11-all-languages.tar.gz

] #tar-zxf phpmyadmin-2.11.11-all-languages.tar.gz-c/var/www/html/
]# cd/var/www/html/
] #mv phpmyadmin-2.11.11-all-languages phpMyAdmin

Modify the configuration file of the software to define the managed database server
] #cd phpMyAdmin
] #cp config.sample.inc.php config.inc.php
] #vim config.inc.php
$cfg [' blowfish_secret '] = ' plj123 ';
$cfg [' Servers '] [$i] [' host '] = ' localhost ';
: Wq

On the Client Access software Management database server
Open Browser input URL address access software
Http://192.168.4.51/phpmyadmin
User name Root
Password 123456

Third, user authorization and REVOKE permission
3.0 Administrator Password Management
Restore the database administrator's native login password
] #systemctl Stop mysqld
]# vim/etc/my.cnf
[Mysqld]
Skip-grant-tables
#validate_password_policy =0
#validate_password_length =6
: Wq
]# systemctl Start mysqld
] #mysql

Mysql> select host,user,authentication_string from Mysql.user;
Mysql>
Update Mysql.user
Set Authentication_string=password ("abc123")
where
host= "localhost" and user= "root";

mysql> flush Privileges;
Mysql>quit

]# vim/etc/my.cnf
[Mysqld]
#skip-grant-tables
Validate_password_policy=0
Validate_password_length=6
: Wq

]# systemctl Restart Mysqld
]# mysql-uroot-pabc123
Mysql>

The operating system administrator modifies the password of the database administrator root native login
[Email protected] ~]# mysqladmin-hlocalhost-uroot-p password "654321"
Enter Password: Current login password

3.1 What is User authorization: Add a connection user on the database server, add the user's access rights and connect the secret

Code. By default, only the database administrator root user is allowed to log on natively. By default, only the database administrator root user is logged on to the local computer.

Authorization permissions.

3.2 Syntax format for user authorization commands
Mysql> grant permission list on number library name to User name @ "Client Address"
Identified by "password" [with GRANT option];

Example 1: Allow the client 254 host to use the root connection, the connection password is 123456, the access rights after the connection is more than all libraries

All tables have full access and have authorization permissions.
192.168.4.51mysql>
Db3.
Grant all on. * to [email protected] "192.168.4.254"
-Identified by "123456"
with GRANT option;

3.3 Using an authorized user to connect to a database server on a client
]# ping-c 2 192.168.4.51
]# which MySQL
]# yum-y Install mariadb
]# mysql-h Database Server IP address-u user name-p password

192.168.4.254]# mysql-h192.168.4.51-uroot-p123456
Mysql>

Grant Select,update (name) on Studb.t8 to [e-mail protected] "%" identified by "123456";

Grant Select,insert,update on Studb.dogperson to [email protected] ' localhost ' identified by

"123456";

Grant all on studb.* to [e-mail protected] "%" identified by "123456";

3.4 The database server uses the authorization library to store authorization information
MySQL Library
User desc Mysql.user; SELECT * FROM Mysql.user
Db
Tables_priv
Clomoun_priv

3.3 Syntax formatting of the REVOKE User Rights command
Mysql> revoke permissions list on number Library name from username @ "Client address";

Example 1: Revoke 254 When the host uses the root user to connect, authorization permissions.
Mysql> revoke grant OPTION on . from ' root ' @ ' 192.168.4.254 ';

Example 2: Revoke a user's access by modifying table records
mysql> Update Mysql.user
Set select_priv= "N"
where user= ' root ' and host= ' 192.168.4.254 ';

mysql> flush Privileges;

Example 3: Revoke 254 hosts all permissions when using the root user connection
Mysql> revoke all in . from ' root ' @ ' 192.168.4.254 ';

Example 4 Remove the authorized user ' root ' @ ' 192.168.4.254 ';
Drop user ' root ' @ ' 192.168.4.254 ';

3.5 How to authorize in the work
The manager gives full permission and has authorized permission
The user only has select and insert permissions to the library where the data is stored

Basic operations of the database 2