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

One: database explanation

Source code installation before 1 5.6

Source code format. / configure option

Make make install

2 yum installation

6 yum-y install mysql-server mysql

Service mysqld start; chkconfig mysqld on

7 yum-y groupinstall mariadb mariadb-client

Systemctl enable/restart mariadb

3 basic

Port number 3306

Process name mysqld

Process owner / Group mysql/mysql

Data transfer protocol tcp

Main configuration file / etc/my.cnf

Database directory / var/lib/mysql/

Error log file / var/log/mysqld.log

Process pid number file / var/run/mysqld/mysqld.pid

Each sql command must end with;

The sql command is case-insensitive.

\ C end the sql command

View the existing library show databases

Information_schema virtual library

Information / data that stores existing libraries and tables on the current database server is stored in physical memory

Mysql authorization library

Store user login and permission information / occupy physical storage space

Performance_schema

Stores the parameter information of the current database service operation

Take up physical storage space

Mysql Architecture (8)

Connection pool

Sql interface

Parser

Optimizer

Cache service mysql start / etc/my.cnf 8m

Storage engine

File system (hard disk / var/lib/mysql)

Management tools

4 database connection

Mysql-h database server Ip address-u user name-p password [library name]

Mysql-uroot-p123456-e "show databases;" non-interactive

5 set password

1 mysqladmin-hlocalhost-uroot password "abc123"

2 mysql > set password for root@ "localhost" = password ("999")

Change the database administrator password

1 [root@www ~] # mysqladmin-hlocalhost-uroot-p password "999"

Enter password: old password

2 recover the database administrator password

# service mysql stop

# service mysql start-skip-grant-table

# mysql

Mysql > update mysql.user

-> set

-> password=password ("123")

-> where

-> host= "localhost"

Mysql > flush privileges

# service mysql stop

# service mysql start

6 upgrade

Install and download high version of the software package to provide mysql database service

Service mysqld stop

Rpm-e-nodeps mysql-server mysql

Rm-rf / etc/my.cnf

Rm-rf / var/lib/mysql/*

Tar-xvf MySQL-5.6.rpm.tar

Rpm-Uvh MySQL-*.rpm

Rpm-qa | grep-I mysql

Service mysql start; chkconfig mysql on

Cat / root/.mysql_secret

Mysql-hlocalhost-uroot-pNlUDn9Wn

Mysql >

Mysql > set password for root@ "localhost" = password ("999")

Mysql > quit

Mysql-hlocalhost-uroot-p999

Mysql > show databases

2. Database operation

1 partial SQL command (library)

View the currently logged-in user information

Select user ()

View the name of the current library

Select database ()

Create a library

Create database library name

Delete Library

Drop database library name

Switching library

Use library name

What are the naming rules for database names?

You can use numbers / letters / underscores, but not pure numbers

Case-sensitive and unique

Do not use instruction keywords or special characters.

2 partial SQL commands (table)

Check the tables already in

Show tables

View table structure

Desc table name

View table records

Select * from table name

Select field name 1, field name 2, field name N from table name

Select field name list from library name. Table name where condition

Add a record to the table

Insert into stuinfo (name) values ("j"), ("t")

Use of tables (tables must be stored in the library)

Build a table

Create table table name (

Field name 1 Field Type (width) Field constraint

Field name 2 Field Type (width) Field constraint

Field name 3 Field Type (width) Field constraint

.

);

Copy tabl

Create table table name SQL query command

Create table user2 select * from user

Create table user3 select id,name,uid from user limit 3

Create table user4 select * from user where 1 = 2

Delete all records of the table

Delete from table name

Insert into bjb values (1, "zzz")

Insert into bjb values (4, "bob")

Update table name set field name = value where condition

Update jfb set jfb_id=8 where jfb_id=2

Delete from table name where condition

Delete from jfb where jfb_id=3

Delete from user where name regexp'[0-9]'

3 modify the table structure (limited by the records already in the table)

Alter table table name action

Add add a new field

Add field name type (width) constraint

Alter table t26

Add mail varchar (30) not null default "plj@tarena.com"

Add qq varchar (11)

Alter table T26 add stu_id int (2) first

Alter table T26 add age tinyint (2) unsigned not null default "21" after name

Drop deletes an existing field

Drop field name

Drop field name, drop field name

4 modify modifies the type of an existing field

Modify field name type (width) constraint

Alter table T26 modify sex enum ("boy", "girl") default "girl"

Change modifies the field name

Change source field name new field name type (width) constraint

Alter table T26 change email mail varchar (10)

Modify table name

Alter table source table name renam [to] New table name

Three database field types (tables)

1 character type

Char (255) fixed length

Varchar (65532) becomes longer

Blob

Text

Create table stuinfo (name char (10))

Create table stuinfo2 (name varchar (3))

2 numerical type

Micro integer small integer medium integer large integer maximum integer

Signed and unsigned

Create table T9 (name char (5), age tinyint unsigned)

Create table T10 (name char (5), age int unsigned)

Create table T11 (name char (5), age tinyint unsigned,pay int (2))

Floating point float single precision 4 bytes

Double double precision 8 bytes

Float (NMagol M)

Double (NMagol M)

Total number of digits of N value

Number of M decimal places

Create table T11 (

Name char (5)

Age tinyint unsigned

Pay float (7 dint 2)

3 enumerated type

Set (value 1, value 2, value N) Select one or more

Enum (value 1, value 2, value N) can only select one

Create table studb.t25 (

Name varchar (10)

Sex enum ("boy", "girl")

Love set ("game", "film", "music", "girl")

Class set ("network", "system", "server", "shell")

);

4 date type

Year (YYYY)

Two-digit automatic complement rule

01 / 69 / 2001 / 2069

70,99,1970,1999

Create table T23 (

Name char (10)

S_year year

);

Insert into T23 values ("jim", 01)

Insert into T23 values ("bob", 80)

Insert into T23 values ("jerry", 00)

Date date (YYYYMMDD)

Time time (HHMMSS)

Date and time

Datetime

1000-01-01 0014 00.000000

9999-12-31 2314 59.999999

Timestamp

1970-01-01 0014 00.000000

2038-01-19 03-14-14-07.999999

Create table T22 (

Time1 datetime

Time2 timestamp

);

Insert into T22 values (20151211094418 2015 1211094418)

Insert into T22 (time2) values (20160214000000)

Insert into T22 (time1) values (20170214200000)

Date-related function

Now ()

Year ()

Month ()

Day ()

Time ()

Select time (now ())

Select day (now ())

Select now ()

Four: the setting of field constraints (restrictions on how to assign values to fields)

1 whether to assign a null null/ null value to a field is allowed to be null by default

Not null

Field default value when adding a new record to the table, use the default value to assign a value to the field when no value is assigned to the field, if there is no

The default value has been set, and the default value is null

2 default value

Create table T26 (

Name varchar (10) not null

Sex enum ("boy", "girl") not null default "boy"

Love set ("game", "film", "music", "girl") default "game,film"

Class set ("network", "system", "server", "shell") default "shell"

);

Insert into T26 (name) values ("jerry")

Insert into T26 values ("lucy", "girl", "game", "network")

Insert into T26 values (null,null,null,null)

3 non-zero zerofill

Five: index

1 benefits of indexing

Advantages of indexing: speed up queries

Index disadvantage: taking up physical storage space

Slow down update insert delete

2 Index View

Show index from table name

3 index Index

There can be multiple INDEX fields in a table

Duplicates are allowed for corresponding field values.

You can assign a null value

Set the field that often makes the query condition to the INDEX field

The KEY flag of the INDEX field is MUL

Mode one

Create index name on tt26 (name)

Create index sex on tt26 (sex)

Mode two

Create table T27 (

Name varchar (10) not null

Sex enum ("boy", "girl") not null default "boy"

Love set ("game", "film", "music", "girl") default "game,film"

Class set ("network", "system", "server", "shell") default "shell"

Index (name)

Index (sex)

);

Index name table name

Drop index name on tt26

4 unique Index

There can be multiple UNIQUE fields in a table

No duplicates are allowed for the corresponding field values, but NULL values can be assigned

If the value of the UNIQUE field is allowed to be NULL and it is modified to disallow NULL, the limit for this field is the same as the primary key

The KEY flag of the UNIQUE field is UNI

Mode one

Create unique index stu_id on tt26 (stu_id)

Mode two

Create table T28 (

Stu_id char (4)

Iphone char (11)

Name varchar (10)

Unique (stu_id)

Unique (iphone)

);

Drop index stu_id on tt26

5 Primary key (primary key)

There can be only one PRIMARY field in a table

The corresponding field values are not allowed to repeat and are not allowed to be null.

The KEY flag for the primary key field is PRI

If multiple fields are used as PRIMARY KEY, called compound primary keys, they must be created together when the table is created.

Usually used with AUTO_INCREMENT

Let the value of the field increase automatically + +

Numerical type

The field must be a primay key field

Set the field in the table that can uniquely locate a record as the primary key field

Create table t211 (

Name varchar (10) primary key

Age tinyint (2)

);

Alter table t211 drop primary key

Alter table t211 add primary key (age)

Create table T222 (

Id int (2) primary key auto_increment

Name varchar (10) not null

Age tinyint (2) unsigned

Index (name)

);

Insert into T222 (name,age) values ("bob", 23)

Alter table T222 modify id int (2) not null

Alter table t222

Add

Id int (2) primary key auto_increment first

Create table test3 (

Id1 int (3) zerofill

Level int zerofill

);

The width of the numeric type is the display width, and you cannot control the size of the value assigned to the field. The size of the field value is determined by the field type.

Insert into test3 values (9pr 9)

Insert into test3 values (277,27)

-

Create table sertab (

Cip varchar (15)

Sername varchar (20)

Serport smallint (2)

Status enum ("deny", "allow") not null default "deny"

Primary key (cip,serport)

);

6 foreign key

1 the storage engine for the table must be innodb

2 the type of field should be the same.

3 the referenced field must be a key (primary key)

Create table table name (list of field names) engine= storage engine name DEFAULT CHARSET= character set

Create a foreign key

Foreign key (field name) references table name (field name) on delete cascade on update cascade

Payment table

Create table jfb (

Jfb_id tinyint (2) primary key auto_increment

Class char (7) not null default "nsd1509"

Name varchar (15)

Xf float (7 ~ (2)) default "17800"

) engine=innodb

Insert into jfb (name) values ("zzz")

Insert into jfb (name) values ("mda")

Insert into jfb (name) values ("jack")

Class table

Create table bjb (

Bjb_id tinyint (2)

Name varchar (15)

Foreign key (bjb_id) references jfb (jfb_id) on delete cascade on update cascade

) engine=innodb

Delete the foreign key attribute of the table field

Mysql > alter table bjb drop foreign key bjb_ibfk_1

Six: storage engine

1 check which storage engines are supported by the current database server

Show engines

2 Storage engine MyISAM and InnoDB

MyISAM

Transactions, transaction rollback, foreign keys are not supported

Table-level locks are supported

Exclusive tablespace bt.frm table structure

Bt.MYD record

Bt.MYI table index

Table level lock

InnoDB

Supports transactions, row-level locking, and foreign keys

Business? The process from start to finish of a SQL operation

Transaction rollback: when the operation is not completed correctly, restore the data to the state before the operation.

Transaction log file

Ib_logfile0

Ib_logfile1

Ibdata1

Shared tablespace at.frm table structure

At.ibd table record + table index

Row level lock

Read lock (shared lock)

Write lock (mutually exclusive lock

3 set transaction status

Mysql > show variables like "commit%"

Mysql > set autocommit=off; transaction auto commit off

Start transaction; starts a transaction

4 Storage engine selection

Tables with multiple editing operations use row-level locks (innodb)

Tables with many query operations use table-level locks (myisam)

5 Storage engine Settings

Modify the storage engine of the table

Alter table Table name engine= Storage engine

Modify the storage engine used by database service by default

Service mysql stop

Vim / etc/my.cnf

[mysqld]

Default-storage-engine=myisam

: wq

Service mysql start

Seven: conversion between file and database (import and export)

1 file to database (import), table structure first

Mysql > LOAD DATA INFILE "system file name"

INTO TABLE table name

FIELDS TERMINATED BY "delimiter"

LINES TERMINATED BY "\ n"

Examples

Save the current system user information to the user table of the database studb library.

/ etc/passwd studb.user

Create table studb.user (

Name varchar (25)

Password char (1)

Uid int (2)

Gid int (2)

Comments varchar (50)

Homedir varchar (50)

Shell varchar (20)

Index (name)

);

Load data infile "/ etc/passwd" into table user fields terminated by ":" lines terminated by "\ n"

Alter table user add id int (2) primary key auto_increment first

2 Database to File (Export)

All records of the user table under the library are saved to the user.txt file under the system / mydata directory.

Mkdir / mydata

Chown mysql / mydata

Select * from mysql.user into outfile "/ mydata/user.txt"

SELECT query command

INTO OUTFILE "file name" fields terminated by "#" lines terminated by "!!"

Examples

Select name,uid,gid from user into outfile "user4.txt" fields terminated by "#" lines terminated by "!

The exported content is determined by the SQL query statement

If you do not specify a path, it will be placed in the database directory corresponding to the library where the export command is executed by default.

You should ensure that the mysql user has write access to the destination folder.

Eight: query conditions

The representation of the condition when recording a query table

1. Character comparison

Equal =

Not equal! =

Select id,name from user where names = "zzz"

Select name from user where shell= "/ sbin/nologin"

2. Numerical comparison

Equality is not equal greater than or equal to less than or equal to

=! > >

< =500 group by shell; 13 给查询结果排序 asc/ desc（倒序） order by 字段名 排序的方式 select name,uid from user order by uid; select name,uid from user where uid>

= 500 order by uid desc

14 limit limits the number of entries that display query records

Limit N,M

N begins to show from which record

The number of the first record is zero

M shows several records in total

Select * from user limit 0Pol 1

Select * from user order by uid desc limit 1

Select * from user order by uid desc limit 2 and 5

15 nested queries

Take the result of the inner query as the query condition of the outer query

Select Field name list from Table name where condition (sql query)

Select name,system from user where system > (select avg (system) from user)

Select name from user where name = (select name from user2 where uid = 3)

Select name from user where name in (select name from user2 where uid > = 3)

16 multi-table query

Select field name list from Table 1, Table 2, Table N where condition

*

Table 1. Field name

Table 2. Field name

Select * from atab,btab; Dicar set

Select atab.name,btab.shell from atab,btab where condition

Select atab.name from atab,btab where atab.name = btab.name

18 left join query (mainly shown by the record in the left table)

Select field name list from table a LEFT JOIN table b ON conditional expression

Right join query (mainly shown in the record in the right table) select field name list from table a RIGHT JOIN table b ON conditional expression

Mysql > select atab.name,btab.shell from atab right join btab on atab.uid = btab.uid

Select * from atab left join btab on atab.uidroombtab.uid

Select name from atab where uid not in (select uid from btab)

IX: database authorization and revocation

1 query authorization

Show grants; users who connect to the database server view their own permission information

Check what authorized users are on the database server?

Select user,host from mysq.user

2 permission list

Command permissions

All all permissions

Usage does not have permission

SELECT query table record

INSERT insert table record

UPDATE updates table records

DELETE deletes table records

CREATE creates libraries and tables

DROP deletes libraries and tables

RELOAD has reload authorization and must have reload permission to execute flush [tables | logs | privileges]

SHUTDOWN allows the shutdown of mysql services to use mysqladmin shutdown to shut down mysql

PROCESS allows you to view the process of a user logging in to the database server (show processlist;)

FILE imports and exports data

REFERENCES creates a foreign key

INDEX creates an index

ALTER modifies the table structure

SHOW DATABASES View Library

SUPER shuts down threads that belong to any user

CREATE TEMPORARY TABLES allows the use of the TEMPORARY keyword in create table statements

LOCK TABLES allows the use of LOCK TABLES statements

EXECUTE executes existing Functions,Procedures

REPLICATION SLAVE reads binary logs from the primary server

REPLICATION CLIENT allows the use of show status commands on the master / slave database server

CREATE VIEW creates a view

SHOW VIEW View View

CREATE ROUTINE creates stored procedures

ALTER ROUTINE modify stored procedure

CREATE USER create user

EVENT has permission to manipulate events

TRIGGER, with permission to manipulate triggers

CREATE TABLESPACE has permission to create tablespaces

3 how to represent the client address?

172.40.7.213 fixed Ip address

192.168.1% network segment

% all addresses

Pc10.tarena.com Hostnam

%. Tarena.com region

4 authorized information storage and database and table

User stores authorized user rights information *. *

The permission information library name of the db library. *

The permission information base name of the tables_priv table. Table name

Permission information for the columns_priv field update (name,sex)

5 user authorization

There are two conditions for a user to have authorized rights.

1 has authorized permission with grant option

2 write access to the authorized library

3 when authorizing other users, the authority cannot be greater than one's own.

Grant permission list on database name to user name @ "client address" identified by "password"

Grant permission list on database name to user name @ "client address" identified by "password" with grant option

With grant option settings authorized users have authorized rights

Administrators can reset the password of authorized users

SET PASSWORD

FOR username @ 'client address' = PASSWORD ('new password')

6 permission revocation

* permission can be revoked only if it is authorized.

* revoke revokes permissions

Revoke permission list on database name from user name @ "client address"

Grant all on *. * to root@ "172.40.7.42" identified by "123456" with grant option

Revoke grant option on *. * from root@ "172.40.7.42"

Revoke delete on *. * from root@ "172.40.7.42"

Revoke all on studb.* from root@ "172.40.7.42"

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.