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

Learn the basics of myslq database

Basic Linux Usage

LINUX system common services use

++++++++++++++++++++++++

Database management:

Building a database server

management database server

++++++++++++++++++++++

I. Building database server

1.1 Deploy database services on hosts with IP address 192.168.4.11.

Determine the server used (factors to consider): cpu memory hard disk (storage)

DELL HP Lenovo Huawei

Operating System: Windows Unix Linux

Redhat7

Install the software package that provides database services:

Relational Database Services (RDBMS):

Oracle MySQL DB2 SQL Server

Data is stored according to specified results, and correlation operations can be made between data

Non-relational row database software (NoSQL)

redis mongodb

key values

The source of the bag? Download system installation CD

Packet type? rpm source package

Cross-platform?

Database application in production environment: LAMP LNMP

Shopping website Game website Forum website Financial website

Pack MySQL

rpm -qa | grep -i mysql

modify the configuration file

#ls /etc/my.cnf

start the service

#systemctl start mysqld

View service health status

#systemctl status mysqld

#ps -C mysqld

#netstat -utnalp | grep :3306

To view the initial password when the database is initially installed:

grep password /var/log/mysqld.log

Login with initial password and reset login password

mysql -hlocalhost -uroot -p Password

Use password authentication policies to take effect permanently.

set global validate_password_policy=0;

#Change password Default level is 0

#0 is length, 1 is letter length symbol, 2 is letter length symbol, dictionary file

set global validate_password_length=6;

#Change password default length is 6

alter user root@"localhost" identified by '123456';

#Change password

After that quit; you can log in with a new password Command line instructions are temporarily valid, write commands to the configuration file/etc/my.cnf permanently valid

Restart the service after writing is complete

Add it under [mysql] in/etc/my.sql

set global validate_password_length=6;

alter user root@"localhost" identified by '123456';

####################################################

noun explanation

DB，DataBase

A collection of data organized according to a data model and stored in storage.

DBMS，DataBase Management System

Database management system, large server software used to operate and manage databases

DBS， DataBase System

Server with database, database management system

####################################################

Classification of sql commands

DDL Data Definition Language

(create alter drop)

DML data manipulation language

(insert update delete)

DCL data control language

(grant revoke)

DTL Data Thing Language

(commit rollback savepoint)

####################################################

Use SQL command to manage database:

The process of storing data on a database server?

1 Connect to database server

The client provides its own connectivity tools (graphical command line)

mysql>

2 Creating libraries (folders)

What is the naming convention for database names?

create database name;

View existing libraries show databases;

drop database name;

toggles library use library name;

View existing tables in the library show tables;

select database();

3 Establishment of tables (documents)

create table name. Name of table (

Field name type (width) constraint,

Field name type (width) constraint,

...

....

）;

create table gamedb.stu(

name char(10),

age int

);

View table structure desc library name. Table name;

insert table record

insert into library name. table name values(list of values);

insert into gamedb.stu values("jim",21),("tom",29);

view table record

select * from Library Name. Table name;

delete table record

delete from library name. Table name;

delete

Drop table library name. Table name;

+++++++++++++++++++++++++++++++++

mysql data type

What are the supported data types?

Numeric type: integer, floating point

Integer: According to the range of stored values, integer types are divided into:

Type tinyint smallmediumintintbigint

Uses Small Medium Large Large

Size 1 byte 2 bytes 3 bytes 4 bytes 8 bytes

It is divided into signed and unsigned, and the ranges are respectively:

Signed-128 ° 27-32768 ° 2767-2^23 ° 2^23 -1 -2^31 ° 2^31 -1 -2^63 ° 2^63 -1

unsigned 0~255 0~65535 0~2^24-1 0~2^32-1 0~2^64-1

When created with unsigned modifier, the corresponding field holds only positive numbers

Width is display width only, size of stored value is determined by type

When zerofill is used, fill in 0 instead of space

#e.g.: age int(3)

insert in to a values(3)

Database shows 003.

When the value exceeds the range, an error is reported.

Floating-point type: divided into single precision and double precision according to the range of storage:

single-precision float(n,m)4 bytes

double(n,m)8 bytes

#n represents the total number of digits,m represents the number of decimal places #For example:float(5,2) Maximum 999.99 Minimum-999.99

The width of a numeric type is the display width that cannot limit the size of the assignment to the field. The size is determined by the field type.

pay float(5,2)

Range of xxx.xx

999.99

0.00

-999.99

++++++++++++++++++++++++++++++

Character type:

Fixed length: char(number of characters)

#Maximum length 255 characters, not enough to specify the number of characters on the right with spaces, character number exceeds the break, can not write data.

Length:varchar

#Allocate storage space according to the actual size of the data. When the number of characters exceeds, data cannot be written.

Large text type:text/blob

#Used when the number of characters is greater than 65535.

Char is used relatively frequently in production environments because varchar consumes a portion of CPU resources to calculate storage size.

Date Time Type:

year YYYY 2017

Date date YMMDD 20171220

time HHMMSS 155145

Date and time:

datetime YYYYMMDDHHMMSS

timestamp YYYYMMDDHHMMSS

datetime is empty when it is not assigned

timestamp uses the current system time when it is not assigned

now()

#Time of the current system

year()

#Get year in data

day()

#Get the days in the data

date()

#Get dates in data

time()

#Get Time in Data

Enumeration type: The value of the field can only be selected within the enumerated range

field name enum(list of values)

field name set(list of values) multiple-choice

Set field constraints: Actions restrict how field values are assigned.

Null is allowed or not, type followed by NOT Null

Default Set default value, default is NULL type followed by default

+++++++++++++++++++++++++

Modify table structure

mysql> alter table name execution action;

Perform actions:

add field name type (width) constraint,

#Add new field, default to last.

add field name type (width) constraint first;

#Before all fields

add field name type (width) constraint after field name;

#Add after what field

alter table drop field name;

#Delete fields

alter table modify field type (width) constraint

#Modify field type

alter table change Original field name New field name Type (width) Constraints

#Change field name

alter table name rename new table name;

#Change table name

remove fields

drop field name;

Modify field type

modify field type (width) constraint;

Modify field name

change Source field New field type (width) constraint;

modify the table name

alter table source table name rename new table name;

Modify table structure

+++++++++++++++++++++++++++++

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.