MySQL Data management 1

One pack

[[email protected]] #tar-XVF Mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar

[[email protected]] #rm-RF mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm

[[email protected]] #rpm-qa | Grep-i mariadb

[[email protected]] #rpm-e--nodeps mariadb-libs

[[email protected]] #rm-rf/etc/my.cnf

[[email protected]] #rm-rf/var/lib/mysql/*

[[email protected]] #yum-y install perl-data-dumper Perl-json

[[email protected]] #rpm-UVH mysql-community-*.rpm

[[email protected]] #rpm-qa | Grep-i MySQL

Second start-up service

[[email protected]] #netstat-utnalp | grep:3306

[[email protected]] #systemctl status mysqld

[[email protected]] #systemctl start mysqld

[[email protected]] #systemctl status mysqld

[[email protected]] #netstat-utnalp | grep:3306

Three set administrator native login password

[[email protected]] #grep-i ' Password '/var/log/mysqld.log

[[email protected]] #mysql-uroot-p "E7kdrg_&wa>t"

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

# mysql-uroot-p123456

mysql> show databases;

Mysql> quit

Four databases stored on the database server process?

1 Connecting to the database server

2 Selecting Libraries (folders in the Library directory)

3 Building a table (files in a folder)

4 inserting Table records

5 Breaking links

Build table

show databases;

Create Database Bbsdb;

Use Bbsdb

Select Database ();

CREATE TABLE Stuinfo (

Name Char (10),

Age int (2),

Sex char (4),

Class char (7)

);

View table Structure

mysql> desc Stuinfo;

Insert Table record

INSERT into Stuinfo values ("Jim", +, "Boy", "nsd1702"), ("Tom", "The Boy", "nsd1702");

View table Records

SELECT * from Stuinfo;

Break link

Mysql>quit

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

CREATE TABLE Stuinfo (

Name Char (10),

Age int (2),

Sex char (4),

Class char (7)

);

View table Structure

mysql> desc Stuinfo;

Insert Table record

INSERT INTO stuinfo values

("Jim", "Boy", "nsd1702"),

("Tom", "Boy", "nsd1702");

View table Records

SELECT * from Stuinfo;

Break link

Mysql>quit

SQL command usage rules?

Common SQL commands for managing libraries?

show databases;

Use library name;

Create DATABSE library name;

Select Database ();

Drop DATABSE library name;

Naming rules for library names?

Common SQL commands for managing tables?

Show tables;

DESC table name;

SELECT * from table name;

CREATE TABLE Table name (

Field Name Type (width), field name type (width), field name type

Width

);

Insert into table name values (List of field values), (field value list);;

drop table name;

MySQL data type?

Numerical: Weight, height, score, salary

Integral type tinyint smallint mediumint int bigint

CREATE table T1 (level tinyint);

CREATE TABLE t2 (level tinyint unsigned);

Crceate table t3 (level int unsigned);

Floating point Type

Float (m,n)

Double (m,n)

CREATE table t4 (chengj float (5,2));

Character type: name, work unit, correspondence address

Char varchar blob text

CREATE table T6 (name char (ten), level tinyint unsigned);

CREATE TABLE T7 (name char), address varchar (50),

level tinyint unsigned);

CREATE TABLE T8 (name blob,address text, level tinyint

unsigned);

CREATE TABLE T9 (name char,address varchar);

Date and Time type: birthday, registration time, year of birth class time

Year YYYY 2015

Time HHMMSS 153529

Date YYYYMMDD 20150522

Datetime/timestamp YYYYMMDDHHMMSS

20170522153658

CREATE TABLE T10 (

Name Char (10),

Age tinyint unsigned,

Pay float (7,2),

Up_class time,

Birthday date,

meetting datetime,

Syear year);

INSERT INTO T10 values

("Bob", 21,20000,083000,20171120,20170522183000,1

997);

Use the time function to get time to assign a value to a Date Time Type field

Select Now ();

Select year (20190909);

Select Month (20190909);

Select Day (20190909);

Select Date (now ());

Select Time (now ());

INSERT into T10 values ("Tom", 21,20000,now (), now

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

INSERT into T10 values ("Alic", 21,20000,time (now

()), now (), now (), year (20190328));

Enumeration types: Hobbies, gender

Field values are selected within the enumerated range

Set ("Value 1", "Value 2", "value 1") Multiple selection

Enum ("Value 1", "Value 2", "Value 1") Radio

CREATE TABLE T11 (

Name Char (10),

Age tinyint unsigned,

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

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

);

INSERT into T11 values ("Bob", and "Boy", "book,it");

INSERT into T11 values ("Alic", "%", "man", "book,it");

INSERT into T11 values ("Jerry", 29,1, "Book,it,music");

Desc Mysql.user;

Field constraints: How the action limit assigns a value to a field

Allow Nulls (null) to allow null values to be assigned by default

Not NULL does not allow null values to be assigned

Index key

Default value "Value"

Additional Settings Extra

CREATE TABLE T12 (

Name Char (10),

Age tinyint unsigned NOT null default 25,

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

Likes set ("book", "It", "film", "Music") Default "it"

);

Desc T12;

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

INSERT into T12 values ("Tom", "Boy", "It,book");

INSERT into T12 values (null,null, "Girl", "Music,book");

INSERT into T12 values ("null", "Girl", "Music,book");

INSERT into T12 values (",", "Girl", "Music,book");

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

Modify Table Structure

ALTER TABLE name execution action;

Add New Field

The Add field name Type (width) constraint condition;

Add field Name Type (width) constraint first;

Add field Name Type (width) constraint after field name;

ALTER TABLE t12 add mail varchar (+) NOT NULL

Default "[email protected]";

ALTER TABLE T12

Add stu_id char (7) First,

Add Tel char (one) after name;

Delete an existing field

Drop field name, drop field name;

mysql> ALTER TABLE t12 drop sex;

Modify an existing field type

Modify field Name type (width) constraints;

ALTER TABLE T12 Modify TEL varchar (15);

Modify field names

Change Source field name new field name Type (width) constraint condition;

ALTER TABLE T12 Change stu_id stu_num char (7);

Modify Table Name

ALTER TABLE source table name rename new table name;

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

After-school practice

Create a student information sheet

Storage System Account Information table

MySQL Data management 1