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

MySQL Database Administration Day01

Related conceptual issues

Database introduction? A repository for storing data

Which companies are using database services?

Shopping website Gaming website Financial website

What data does the data service store?

Data information corresponding to account information

What software provides database services?

Open source software mysql, mongodb, redis

Oracle, db2, SQL SERVER

Is the software cross-platform? Linux Unix Windows

Source of software package: download from official website Use operating system installation CD comes with software package

Introduction to MySQL Software? mysql mariadb

Relational data software: data should be stored according to a certain organizational structure, and data and data can be associated with each other.

Cross-platform Linux Unix Windows

strong portability

Python/Java/Perl/PHP

In a production environment, data services and website services are used together to build a website runtime platform

LNMP LAMP WNMP WAMP

mysql package type: rpm package source package can customize installation information

Non-relational database software (NoSQL) mongodb,

redis 、 memcached

1 Build mysql database server

2 Basic use of mysql services

3 mysql data types

I. Build mysql database server 192.168.4.51

bagging

modify the configuration file

start the service

View service processes and port numbers

Service name mysqld

The main configuration file for the service/etc/my.cnf

Data directory/var/lib/mysql

Log file/var/log/mysqld.log

Basic use of database services

2.1 Connect to database service locally using initial password

mysql [-h database server ip address-u username-p'password'

2.2 Reset local connection password mysql> alter user

root@"localhost" identified by "password";

2.3 The process of storing data on a database server?

2.4 SQL command classification? DDL DML DTL DCL

2.5 SQL command using rules?

2.6 SQL command for managing database and naming rules for library names

2.7 SQL command for managing tables

What is the syntax of the table?

create table name. Name of table (

Field name type (width) constraint,

Field name type (width) constraint,

.....

）;

2.8 sql command for managing records

Views

inserted

update

delete

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

III. MySQL data types

3.1 Value Type (Grade Age Wage)

The scope of each type of stored data is fixed

Integer type (only integers can be stored)

Small integer Small integer Medium integer Large integer Large integer

tinyint smallint MEDIUMINT INT bigint

****unsigned Use numeric types for signed ranges.

Floating-point type (storing decimals)

float(M,N)

double(M,N)

M Set total digits

N Sets the number of decimal places

Positive. Total decimal places Whole number decimal places

18088.88 7 5 2

3.2 Character type (commodity name, origin name, manufacturer)

char (255) Fixed length character type

varchar (65532) Variable length character type

Large text type (audio file video file picture file)

blob

text

3.3 datetime type

(Registration Date Appointment Date Meeting Date Entry Date Birthday)

year YYYY 2018

Date date YMMDD 20180423

time HHMMSS 161958

datetime/timestamp

YYYYMMDDHHMMSS 20180423161958

Get date and time to assign values to fields of the corresponding date and time type

Get date-time function

now() Gets the current system time

year(datetime) Gets the year in the specified time

month(datetime) Gets the month in the specified time

date Gets the date in the specified time

day(datetime) Gets the sign (day) in the specified time

time(datetime) Gets the time in the specified time

You can use 2 digits to assign values to fields of type year, as follows:

01-69 20XX

70-99 19XX

3.4 Enumeration type (values recorded when inserting records are selected within the enumerated range)

Sex, hobbies, majors.

enum(list of values)

set(list of values) Multiple-choice

Keyword storage range assignment method of data type Reasonable use of data type

** Focus

The width of a numeric type is the display width and cannot be assigned a size to a field. The value of a field is determined by the type

Set.

mysql-day02

I. Field constraints

1.1 Role: Restrict how field assignments are assigned

1.2 Key Default Extra NULL

NULL Allow fields to be assigned null values

null By default, null values are allowed

not null is not allowed

key Type: Ordinary index Unique index Full-text index Primary key Foreign key

Default Default Value Role: Use default value to assign value to field when it is not assigned by field

Not set default value is system-defined is null

default value

Extra setting, whether the field is set to auto-increase, default no auto-increase function

II. Modify the table structure

2.1 Command to modify table structure

mysql> alter table library. table execution action;

add new field add field name type (width) [constraint]

Delete an existing field drop field name

Modify the type width and constraints of existing fields

* Key points ** Modification is not allowed if it cannot contradict the stored data.

modify Field Name Type (Width) [constraint]

Modify field name

change source field name new field name type (width) [constraint]

modify the table name

alter table source table name rename [to] new table name;

III. mysql key value

Set on a field in a table to restrict how values are assigned to the field. It also indexes fields.

Index introduction: tree directory structure similar to the book "directory"

Advantages: Speed up lookup table records

Disadvantages: slows down editing table records and takes up physical storage space on disk

(delete insert update)

Total number of pages 1000 pages

directory information

1------100 page record directory information

101---1000 page body

Ways to look up dictionaries

strokes

radical

pinyin

When correcting content, modify content Add content Delete content

stuinfo /var/lib/mysql/db2/stuinfo.*

name age home class

DBA

3.1 Key value: Ordinary index Unique index Full-text index Primary key Foreign key

3.1.1 Use of ordinary indexes (index)

Using rules?

Views

desc table name;

show index from table name;

Table: t2

Key_name: aaa

Column_name: age

Index_type: btw (btw)

create

Create an existing table

create index name on table name (field name);

Create table when it is created

create table name (

field list,

index(field name),

index(field name),

）;

delete

drop index name on table name;

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

3.1.2 Primary key

(Normal PK Composite PK +auto_increment)

Using rules?

View desc table; key ----> PRI

create

Create an alter table in an existing table add primary key(field name);

Create when creating tables

create table name (

Field list,

primary key(field name)

）;

Use of composite primary key: multiple fields are used as primary keys together. When inserting records, records can be inserted as long as the values of the primary key fields are not repeated at the same time. (Must be created and deleted together, values of multiple fields cannot be the same at the same time)

desc mysql.db;

desc mysql.user;

Primary key is usually used with auto_increment.

Let the value of the field automatically increase by i++

Value type i=i+1

mysql> alter table drop primary key;

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

3.1.3 outer key (one that restricts how a field is assigned)

When assigning a value to a field in the current table, the value can only be selected from a range of values for the specified field in the specified (other) table.

Using rules?

Command format for creating foreign key:

create table (

list of field names,

foreign key references table name (field name)

on update cascade on delete cascade

）engine=innodb;

payment schedule

use studb;

create table jfb(

jfb_id int(2) zerofill primary key auto_increment,

name char(15),

pay float(7,2)

)engine=innodb;

insert into jfb (name,pay)values("bob",26800);

insert into jfb (name,pay)values("tom",26000);

select * from jfb;

class table

create table bjb(

bjb_id int(2) zerofill,

name char(15),

foreign key(bjb_id) references jfb(jfb_id)

on update cascade on delete cascade

)engine=innodb;

insert into bjb values(3,"lucy");

insert into bjb values(1,"bzengjiaob");

insert into bjb values(2,"tom");

select * from bjb;

mysql> update jfb set jfb_id=9 where name="bob";

mysql> delete from jfb where jfb_id=2;

select from jfb;

select from bjb;

Check mysql> show create table name;

Delete foreign key

alter table name drop foreign key name;

alter table bjb drop foreign key bjb_ibfk_1;

mysql> delete from jfb where jfb_id=02; it has the effect of synchronization class table also does not have this entry

mysql> update jfb set name="xixi"where jfb_id=05; modify the payment table ID is 05 change his name to xixi

Create an existing table

mysql> delete from bjb;

mysql> alter table bjb add foreign key(bjb_id) references jfb

(jfb_id) on update cascade on delete cascade;

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.