MySQL database service overview, build MySQL server, basic database management, MySQL data type,

08:30------09:00
------12:00
FTP server: 172.40.50.117 7000
Software Softpackage/mysql
Note NSD1710/Course Name
Practice Homework

Pangli Silent Teacher
Stage four:
Database Management 7-day DBA
Security and Monitoring 6-day IDC monitoring
Cluster and storage 5 days
+++++++++++++++++++++
Database management:
A database server
Basic management of MySQL database service
Three data management
Four user authorization and permission revocation
Five data backup and recovery
Six MySQL master-slave synchronization
Seven data read/write separation
Eight MySQL service tuning
Nine deploy MySQL Cluster
++++++++++++++++++++++++++++
DAY01:
A database server: 1 build MySQL Data server 2 basic usage
Two MySQL data types *
Three Modify table structure

A database server 192.168.4.51
1.1 Buying a server DELL HP Lenovo (IBM)
CPU Memory Disk

1.2 Installing the operating system Windows Linux Unix
Rhel7
Rhel6

1.3 Installing software that provides database services
What are the database service software?
Whether the software is cross-platform
Software is open source business software
The source of the software
Package type of software rpm source code

1.4 Installing the MySQL software
1.4.1 Unloading system comes with the MARIADB
1.4.2 Installing MySQL
1.4.3 Start Service:
Service name Process name Port number Transfer Protocol process owner process owning Group database directory

1.5 which companies are using database services
Financial Institutions shopping Website Games website Forum website

1.6 Web services and database services are used together
LAMP LNMP
+++++++++++++++++++++++++++++++++++++++++++
1.7 Storing data on a database server process
1 Connecting to the database server
2 Building a library
3 Build Table
4 Inserting records
5 disconnecting

1. To connect to the database server:
   Log on to the database server with the initial password
   Modify password Authentication policy and password length, change password
   Log in with the modified password
   Modify the service's master profile to take effect permanently using the Password Authentication policy.

* Libraries and tables are stored as files in the database library directory.

2. Build Library (folder) view Create delete toggle
   Displays the library that is currently located
   View existing tables in the library

• The table must be in the library.
  3. Build table (file) view Create delete
     Field
     Column
     Name Sex class
     Jim Boy nsd1710 Line record
     Tom Boy nsd1710

Product name price manufacturer Number Size

CREATE table library name. Table Name (
Field Name Type (width) constraints,
Field Name Type (width) constraints,
.....
);

Create Database studb;
CREATE TABLE Studb.stuinfo (
Name Char (15),
Sex char (4),
Class char (7)
);

View table Structure desc studb.stuinfo;

4. Insert Record: View insert Delete Modify record select * from library. Table;

INSERT into Studb.stuinfo values ("Jim", "Boy", "nsd1710");

Update Library. Table SET field name = value where condition;

Delete from library. Table;

5. Mysql> quit

+++++++++++++++++++++++++++++++++++++++++++++
SQL syntax rules?
Types of SQL commands: DDL DML DCL DTL
Library name naming rules?
+++++++++++++++++++++++++++++++++++++++++++++
Two MySQL data types *
Numeric type: Height, weight, age, salary unsigned
Integer type: Small integer integral type large integer type maximum integer type

Floating-point type: single-precision double precision

Create Database db1;
CREATE table Db1.t1 (level tinyint);
CREATE table db1.t2 (level tinyint unsigned);
CREATE table DB1.T3 (level int unsigned);

Integer. Total number of decimal places
Field Name Type (n,m)
n Total number of digits
M-Decimal Place number
Pay double (8,2)
Pay float (8,2)
xxxxx.xx
999999.99
-999999.99
CREATE TABLE Db1.t4 (pay float (5,2), age tinyint unsigned);
INSERT into DB1.T4 values (900.23,27);

Mysql>desc Mysql.user;

Character Type: Name Home address birthplace Company Department name
Solid length char (255) *
Variable length varchar (65532)
Large text type blob text

CREATE TABLE DB1.T6 (name char (n), image blob);

CREATE TABLE Db1.t7 (
Name Char (10),
Email varchar (30),
Pay float (5,2),
Age tinyint unsigned
);

INSERT into DB1.T7 values ("Bob", "[email protected]", 999.23,27);

The difference between a char type and a varchar type

 t1     t2

Name Name
CHAR (3) varchar (3)
Jim Jim
AB AB
C C

Mysql>desc Mysql.user;

The difference between the width of a numeric type and the width of a character type. The width of a numeric type is the width of the display, which cannot be controlled by the size of the assigned value of the field, size by type.
CREATE TABLE Db1.t9 (
Name Char (3),
Pay INT (3)
);
INSERT into DB1.T9 values ("Lucy", 1088);

CREATE TABLE Db1.t10 (
ID int Zerofill,
Pay int (3) Zerofill
);
INSERT into db1.t10 values (9,9);
INSERT into db1.t10 values (27,27);

+++++++++++++++++++++++++++++++++++++++++
DateTime Type: Entry time Registration time Appointment time Class time birthday
Year YYYY 2018
Date YYYYMMDD 20180117
Time HHMMSS 173258
Date Time Datetime/timestamp
Yyyymmddhhmmss
CREATE TABLE db1.t12 (
Name Char (10),
Youstar year,
Upclass time,
Birthday date,
Party datetime
);

INSERT into db1.t12 values ("Bob", 1995,083000,20181120, 20180214183000);

Assign a value to a field using the time acquired by the time function.
Now () year () date () Time () Day () month ()

INSERT into db1.t12 values ("Lucy", Year (20191212), 083000,date (today ()), now ());

Assigns a value to a field of year type using a 2-digit number. Follow the following rules:
01~69 20XX
70~99 19XX
00 0000

CREATE TABLE db1.t13 (
Name Char (10),
Youstar year
);

INSERT into db1.t13 values ("Bob", 53);
INSERT into db1.t13 values ("Tom", 88);

DateTime differs from timestamp:
CREATE TABLE db1.t14 (
meetting datetime,
Party timestamp
)

INSERT into db1.t14 values (now (), now ());
Insert into db1.t14 (meetting) values (20171222092835);
INSERT into db1.t14 (Party) values (20161222092835);
++++++++++++++++++++++++++++++++++++++++++
Enumeration type: Sex-loving majors

• field can only be within the specified range of options
  Field name Enmu (value 1, value 2, value N) radio
  Field Name Set (value 1, value 2, value N) multi-Select

CREATE TABLE db1.t21 (
Name Char (10),
Sex enum ("Boy", "Girl"),
Likes set ("Game", "Music", "film", "book")
);

INSERT into db1.t21 values ("Bob", "Boy", "game,film");
INSERT into db1.t21 values ("Tom", "Man", "game,film,it");
INSERT into db1.t21 values ("Lucy", 2, "Game,film,book");

Field constraint conditions
Role: Limit How to assign a value to a field
Includes: whether NULL is allowed null key value default extra setting

CREATE TABLE db1.t22 (
Name Char (TEN) is not NULL,
Sex enum ("Boy", "Girl") The default "boy",
Age tinyint (2) unsigned NOT null default 18,
Likes set ("Game", "Music", "film", "book") is not null default "Game,music"
);
Desc db1.t22;
Insert into t22 (name) VALUES ("Alice");
INSERT into t22 values ("Bob", "Boy", and "book");

Insert into t22 (name) VALUES ("null", "");
Insert into t22 (name) values (NULL);
+++++++++++++++++++++++++++++++++++
Three Modify table structure
ALTER TABLE Library. Perform the action;

Add New Field
Add field Name type (width);
The Add field name Type (width) constraint condition;
Add field Name Type (width) constraint first;
Add field Name Type (width) constraint after field name;
Add field Name type (width), add field name type (width);
Delete an existing field
Drop field name;
Drop field name, drop field name;

Modify the field type (the modified type conflicts with the data stored in the field and is not allowed to be modified)
Modify field Name type (width) constraints;

Modify field names
Change Source field name new field name Type (width) constraint condition;
Modify the table name rename the name of the new table named ALTER TABLE source table;
ALTER TABLE db1.t21 Add email varchar (+) Default "[email protected]";
ALTER TABLE db1.t21 add stu_id char (9) first;
ALTER TABLE db1.t21 add age tinyint (2) unsigned NOT null default all after name;
ALTER TABLE db1.t21 drop age;
ALTER TABLE db1.t21 Modify stu_id char (9) NOT null;
ALTER TABLE DB1.T21 modify name varchar (ten) not null;
ALTER TABLE db1.t21 Modify likes set (' Game ', ' Music ', ' film ', ' book ') after name;
ALTER TABLE db1.t21 change email (varchar) default "[email protected]";
ALTER TABLE t21 rename Stuinfo;
System ls/var/lib/mysql/db1/stuinfo.*

                     varchar(15)

School Number class name age hobby sex birth year
STU_ID class name age like Sex s_year
Book No 1998
Game NOT NULL
Film

MySQL database service overview, build MySQL server, basic database management, MySQL data type,