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

Introduction to MySQL database

The most famous and widely used open source database software

-MySQL AB, which was originally owned by Sweden

-MySQL AB was acquired by SUN in January 2008

-SUN was acquired by Oracle in April 2009

A new open source branch, MariaDB

-born to cope with the risk that MySQL may be closed

-developed by Widenius, the original author of MySQL

-maintain maximum compatibility with MySQL

Characteristics and Application of MySQL

Main features

-suitable for small and medium-sized, relational database systems

-support multiple operating systems such as Linux/Unix and Windows

-written in C and C++ with strong portability

-support languages such as Python/JAVA/Perl/PHP through API

Typical application environment

-LAMP platform, combined with Apache HTTP Server

-LNMP platform, combined with Nginx

Mysql installation

Preparatory work

-stop the mariadb service

-Delete file / etc/my.cnf

-Delete data

-Uninstall the package

[root@proxy ~] # systemctl stop mariadb [root@proxy ~] # rm-rf / etc/my.cnf [root@proxy ~] # rm-rf / var/lib/mysql/* [root@proxy ~] # rpm-e-- nodeps mariadb-server mariadb warning: / var/log/mariadb/mariadb.log has been saved as / var/log/mariadb/mariadb.log.rpmsave

Install at least server, client, share* packages

-upgrade installation with-U to replace conflicting files

-it is recommended to install devel to support other software

[root@proxy] # yum-y install perl-Data-Dumper perl-JSON perl-Time-HiRes [root@proxy ~] # tar-xf mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar [root@proxy ~] # rm-f mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm [root@proxy ~] # rpm- Uvh mysql-community-*.rpm

Start the MySQL database service

-the service script is / usr/lib/systemd/system/mysqld.service

[root@localhost ~] # systemctl start mysqld [root@localhost ~] # systemctl enable mysqld [root@localhost ~] # systemctl status mysqld

Initial configuration of MySQL

Default database management account

-root, allowing access from localhost

-the first login password is randomly generated during installation

-stored in the error log file

[root@proxy ~] # grep 'temporary password' / var/log/mysqld.log2019-06-24T15:19:18.303935Z 1 [Note] A temporary password is generated for root@localhost: zzXdihIzU4-_ [root@proxy ~] # mysql-uroot-p'zzXdihIzU4-_'mysql > set global validate_password_policy=0; / / only verify length Query OK, 0 rows affected (0.00 sec) mysql > set global validate_password_length=6 / / modify password length. Default is 8-character Query OK, 0 rows affected (0.00 sec) mysql > alter user user () identified by "123456"; / / modify login password Query OK, 0 rows affected (0.00 sec)

Use client commands to connect to the server

[root@proxy] # mysql-uroot-p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Parameters related to MySQL service

File

Description / etc/my.cnf master profile / var/lib/mysql database directory default port number 3306 process number mysqld transfer protocol owner TCP process group mysql to which the mysql process belongs

Basic database management

Common SQL operation instructions

-DDL data definition language (create,alter,drop)

-DML data definition language (insert,update,delete)

-DCL data definition language (grant,revoke)

-DTL data definition language (commit,rollback,savepoint)

Library management command

-show databases; / / display existing libraries

-Use library name; / / switch the library

-Select database (); / / displays the current library

-Create database library name; / / create a new library

-Show tables; / / display existing libraries

-Drop database library name; / / delete the library

Table management command

-Desc table name; / / View table structure

-Select * from table name; / / View table records

-Drop table table name; / / Delete the table

Record management command

-Select * from table name; / / View table records

-Insert into table name values (values list); / / insert table record

-Update table name set field = value; / / modify table record

-Delete from table name; / / Delete table records

Modify table structure

-add a new field

ALTER TABLE table name

ADD field name type (width) constraint

AFTER field name can be added

Or FIRST.

Mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | int (3) | NO | PRI | NULL | name | varchar (5) | NO | | NULL | | age | int (3) | NO | | NULL | | +-+-+ 3 rows in set (0.01 sec) mysql > alter table tt1 add interest varchar (40) Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (3) | NO | PRI | NULL | name | varchar (5) | NO | | NULL | | age | int (3) | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+- -+ 4 rows in set (0.00 sec)

-modify field type

Alter table table name

Modify field name type (width) constraint

After field name can be added

Or first.

Mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | name | varchar (5) | NO | | NULL | | age | int (3) | NO | | NULL | | gender | enum ('boy' 'girl') | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec) mysql > alter table tt1 modify name char (6) not null Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | name | char (6) | NO | | NULL | | age | int (3) | NO | | NULL | | gender | enum ('boy' 'girl') | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec)

-modify the field name

Alter table table name

Change source field name new field name type (width) constraint

Mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | name | varchar (5) | NO | | NULL | | age | int (3) | NO | | NULL | | sex | enum ('boy' 'girl') | YES | | NULL | | interest | varchar (40) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec) mysql > alter table tt1 change sex gender enum (' boy','girl') not null Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | name | varchar (5) | NO | | NULL | | age | int (3) | NO | | NULL | | gender | enum ('boy' 'girl') | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec)

-Delete a field

Alter table table name

Drop field name

Mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | name | char (6) | NO | | NULL | | age | int (3) | NO | | NULL | | gender | enum ('boy' 'girl') | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec) mysql > alter table tt1 drop gender Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc tt1 +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (3) | NO | PRI | NULL | name | char (6) | NO | | NULL | | age | int (3) | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+- -+ 4 rows in set (0.00 sec)

-modify the table name

Alter table table name

Rename new table name

Mysql > alter table tt1 rename tt2;Query OK, 0 rows affected (0.31 sec) mysql > desc tt1;ERROR 1146 (42S02): Table 'studb.tt1' doesn't existmysql > desc tt2 +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (3) | NO | PRI | NULL | name | char (6) | NO | | NULL | | age | int (3) | NO | | NULL | | interest | varchar (40) | YES | | NULL | | +-+- -+ 4 rows in set (0.00 sec)

Time function

Type use now () get system current date and time year () dynamically get system date time sleep () hibernate Ns curdate () get current system date

Curtime () gets the current system time month ()

Gets the month in the specified time

Date () get the date in the specified time time () get the time in the specified time

There is no need for libraries and tables, but can be called directly

-use SELECT instructions to output function results

Mysql > select now (), sysdate (), curdate () +-+ | now () | sysdate () | curdate () | +-+- -+-+ | 2019-06-25 22:10:45 | 2019-06-25 22:10:45 | 2019-06-25 | +-+ 1 row in set (2019 sec) mysql > select date (now ()) Time (now ()) +-+ | date (now ()) | time (now ()) | +-+-+ | 0-06-25 | 22:11:41 | +-+-+ 1 row in set (2019 sec)

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.