MySQL database management

1.3 Build the MySQL database server and set the database administrator's native login

Password is 123456

A Buy server (storage CPU memory) DELL HP Lenovo

b Installing the operating system RHEL7.2

C Install the package that provides the database service (MySQL)

# Rpm-qa | Grep-i mariadb

# rpm-e--nodeps mariadb-libs

# Rpm-qa | Grep-i mariadb

#rm-rf/etc/my.cnf

#yum-y Install perl-data-dumper Perl-json

# TAR-XVF Xxxx.tar

# RM-RF mysql-community-server-minimal-5.7.17-

1.el7.x86_64.rpm

#rpm-UVH mysql-community-*.rpm

#rpm-qa | Grep-i MySQL

#systemctl Start mysqld

#systemctl Status Mysqld

#systemctl Enable Mysqld

Service Name Mysqld

Process name Mysqld

Owner/Group of the process Mysql/mysql

Data Transfer Protocol TCP

Port number 3306

Master configuration file/etc/my.cnf

Database Directory/var/lib/mysql

Log file/var/log/mysqld.log

Client Access database server (command line graphical tools)

* When not authorized, only the database administrator root user is allowed to access from the local computer

#mysql-hlocalhost-uroot-p Password

[Email protected] ~]# grep-i "Password"

/var/log/mysqld.log

2017-06-19T02:07:11.746572Z 1 [Note] A Temporary

Password is generated for [email protected]: *? F (SFA; M3jy

[Email protected] ~]#

[Email protected] ~]# mysql-hlocalhost-uroot-p "*? F

(SFA; M3jy "

mysql> set global validate_password_policy=0;

mysql> set global validate_password_length=6;

mysql> alter user [email protected] "localhost" identified by

"123456";

Mysql>quit

[Email protected] ~]# Mysql-hlocalhost-uroot-

p123456

mysql> show databases;

Mysql>quit

Vim/etc/my.cnf

[Mysqld]

Validate_password_policy=0

Validate_password_length=6

: Wq

# systemctl Restart Mysqld

#mysql-hlocalhost-uroot-p123456

Mysql>

Database services are typically used with Web services. LAMP LNMP

Shopping website Games website Forum website Financial website php java HTML CSS

What data does the database store? Register your account and password

Shopping information

Savings information

Post Content

1.4 Basic use of database services

The process of storing data on a database server?

1 Connecting to the database server

mysql-hlocalhost-uroot-p123456

2 Creating a new library (folder)

3 Creating a table (file)

4 inserting records into a table

5 Viewing Records

6 disconnecting

SQL command usage rules?

Each command must end with;

command does not distinguish between uppercase and lowercase letters

\c End Command

Related commands for managing libraries

show databases;

Create database name;

Select Database ();

Use library name;

Show tables;

drop database name;

Commands related to managing tables

CREATE TABLE table name (field name type (width), field name type (wide

degree));

Mysql> CREATE TABLE Regtab (name char), password

CHAR (6));

DESC table name;

Desc Regtab;

Account name Password

PLJ 123456 Records

Jim 654331

Tom 111199

INSERT into Regtab values ("Plj", "123456"),

("Jim", "654321"), ("Tom", "111199");

SELECT * FROM table name

SELECT * from Regtab;

Delete from table name;

Delete from Regtab;

drop table name;

drop table Regtab;

Naming rules for table names and library names?

Has a unique

Distinguish letter Case

Use a numeric letter _ name, not allowed to be a pure number

Use of special symbols and command keywords is not allowed

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

MySQL data type

Character type name Home address hometown

Char 255 fixed length

VARCHAR 65532 variable length

Large text type

Text

Blob

Create Database studb;

Use STUDB;

CREATE TABLE T1 (

Name Char (10),

HOMEADDR varchar (50)

);

INSERT into T1 values ("Lucy", "Beijing");

SELECT * from T1;

CREATE TABLE t2 (image blob,name text);

T1 T2

Name Name

CHAR (TEN) varchar (10)

A ABC

Numeric type: Age, height, weight, salary.

11 12.23

+11-19.23

-12

Integral type (divided into the following types according to the range of stored numbers)

Signed and unsigned

tinyint-128~127 0-255

smallint

Mediumint

Int

bigint

CREATE TABLE T5 (name char), age tinyint

unsigned);

CREATE table T6 (name Char), age tinyint, level

tinyint);

CREATE TABLE T7 (name Char), age tinyint

Unsigned,level tinyint);

INSERT into T7 values ("Jim",-11,101);

The width of a numeric type is the width of the display, which cannot be controlled to assign a value to the field, the value of the field

The size is determined by the type.

CREATE TABLE T8 (name char (3), level int (3));

INSERT into T8 values ("Lucy", 10224);

CREATE TABLE T12 (ID int (2) zerofill,level int zerofill);

INSERT into T12 values (9,7);

Floating point type (number of decimal points can be stored) 19.23 21.75

Float

Double

Integer. Decimal

1023.88

xxx.xx

999.99

-999.99

Float (5,2)

CREATE TABLE T13 (name char), age tinyint (2)

Unsigned,pay float (7,2));

INSERT into t13 values ("Bob", 21,18800.23);

INSERT into t13 values ("Tom", 29,118800.23);

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

Date Time type Registration time class time meeting time birthday entry

Year YYYY 2017

Date YYYYMMDD 20170619

Time HHMMSS 160258

Date Time Type Yyyymmddhhmmss 20170619160258

Datetime

Timestamp

CREATE TABLE T14 (

Name Char (10),

Age tinyint (2) unsigned,

Pay float (7,2),

S_year year,

Birthday date,

Up_class time,

meetting datetime

);

INSERT INTO t14 values

("Bob", 21,18800,1990,20170818,083000,20170707204

523);

Use the time function to assign a value to a DateTime type field

Now ()

Year ()

Date ()

Month ()

Day ()

Time ()

INSERT into t14 values ("Lilei", 21,18800,year (now

()), Date (now ()), Time (20150718231458), now ());

INSERT into t14 values ("Hanmm", 21,18800,now (), now

(), now (), now ());

To assign a value to the year field using a 2-digit number, follow the rules below.

01-69 20XX

70-99 19XX

00 0000

100 error

INSERT into t14 values ("Lee", 21,18800,69,now (), now

(), now ());

What is the difference between datetime and timestamp?

CREATE TABLE T15 (

meetting datetime,

Partty Timestamp

);

INSERT into t15 values (now (), now ());

Insert into t15 (meetting) values (20171020091828);

Insert into t15 (Partty) values (20191020091828);

SELECT * from T15;

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

Enumeration type gender specialty subjects Hobbies

(Field values can only be selected within the enumerated range)

Enum (value list) radio

Set (value list) multiple selection

CREATE TABLE T16 (

Name Char (10),

Age tinyint (2) unsigned,

Sex enum ("Boy", "Girl", "no"),

Likes set ("It", "book", "film", "Game")

);

INSERT into t16 values ("Bob", +, "Boy", "It,game");

INSERT into t16 values ("Jim", "No", "music,game");

INSERT into t16 values ("Jerry", 21,2, "It,game")

mysql> desc Mysql.user;

mysql> desc Mysql.tables_priv;

Field constraints: How functional limits assign values to a field

Null field allows null value to be null by default allows nulls to be assigned

Key Index

Default value of Default field, value is null

Assign a value to a field without assigning a value to the field using the default value

Default value

Extra Additional settings (auto-Grow)

CREATE TABLE t17 (

Name Char (TEN) is not NULL,

Age tinyint (2) unsigned default 22,

The Sex enum ("Boy", "Girl", "no") is not NULL, the default "boy",

Likes set ("It", "book", "film", "Game") default "It,book"

);

Insert into t17 (name) VALUES ("Bob");

INSERT into t17 values ("Jim", "No", "film,game");

INSERT into t17 values ("", NULL, "Boy", null);

INSERT into t17 values ("null", NULL, "Boy", null);

INSERT into t17 values (null,null, "boy", null);

Deploying the database service on the IP address is 192.168.4.101 and setting the database administrator's password is abc123 creating the STUDB library to create a table that stores student information Stuinfo

MySQL database management