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

Database MYSQL Learning Series 1

I. MySQL Database Foundation

1.1-get to know MYSQL

What is a database?

All information processed and stored by a computer is data.

A program used to access data in a computer system

One kind:

There are many programs in a computer system that can access data.

They each have their own characteristics and advantages, and have their own scope of use.

Access:

Be able to save data to avoid loss

Be able to find qualified data according to the demand

Why use a database?

The database helps us solve the data access problem:

Large amount of data

Network control

Concurrent access

High performance requirements

Transaction control

Persistence and data security

Logical replication of query data requirements

Database classification

Relational database

MYSQL

Oracle

SQL Server

PostgreSQL

Non-relational database

Hadoop: store big data

MongoDB: document database

Redis: key-type database

Cassandra: distributed database

The most significant difference: whether or not to use structured query statements (SQL)

Why study MYSQL?

MYSQL:The world's most popular open source database

The most popular

Open source

Not the most advanced.

The only open source database in the top three

Occupy an absolute mainstream position in Internet enterprises

Open source code based on GPL protocol

The community version is completely open and free.

Code is allowed to be modified freely

Easy to learn:

MYSQL has the core functions of relational database, but there are not many features.

The architecture design tends to be simplified.

It is very suitable for beginners to learn relational database, and can develop to other databases after entry.

Who needs to learn MYSQL?

Application developer

DBA

Learning goals-Application developers

It is helpful to develop the due program with excellent performance by using MYSQL.

Learning goal-DBA

Provide reliable database technical support for enterprises

1.2-easy installation of MySQL

Easy deployment of MySQL

Install MySQL under Windows

O graphical tool installation, MySQL Installer

Install MySQL under Linux (Ubuntu)

O package management installation, apt-get

The wrong solution to the starting server Times error during Windows installation is to manually enter the service management to change the login mode of the MySQL service to a local account and allow interaction with the desktop.

You can use apt-cache search mysql-server to view available software packages under Ubuntu

Install MySQL5.6 using sudo apt-get install mysql-server-5.6

Start and stop the MySQL service:

Start

Sudo / etc/init.d/mysql start# or

Sudo service mysql start

Stop it

Sudo / etc/init.d/mysql stop# or

Sudo service mysql stop

Restart

Sudo service mysql restart

View statu

Sudo / etc/init.d/mysql status

1.3-MySQL database connection

Three connection modes commonly used in work

Java App + JDBC client (also available in other languages, such as Python's MySQLdb)

MySQL client

"MySQL" utility

Use an application to connect to MySQL

applications use driver (connector/driver) clients to connect to MySQL

MySQL drivers cover a variety of mainstream languages

Use the command line to connect to MySQL

installs the MySQL client package

set environment variable (Linux)

How to install MySQL-client

installs sudo apt-get install mysql-client from the software source

Verify the installation of MySQL

Mysql-V

Two ways to connect MySQL on the command line

Socket connection (local connection)

TCP/IP connection (remote connection)

Connect using Socket

Need to specify socket file and user name, password

Mysql-S/tmp/mysql.sock-uroot-p

Remote connection

IP and port need to be specified

Mysql-h227.0.0.1-P3306-uroot-p

Local connection VS remote connection

local connections can only be created on MySQL servers, often as MySQL status checks, or programs and MySQL are deployed on the same machine.

remote connection works both inside and outside the MySQL server, which is suitable for scenarios where application servers and MySQL are deployed on different machines.

Connect MySQL with command line under Windows

Mysql-hlocalhost-P3306-uroot-p

What can be done after the connection is entered

Database status

Status

Show current connection

Show processlist

Considerations for using the command line to connect to MySQL

The general storage path for socket is: / tmp/mysql.sock

If you can't find the file, you can connect to it through tcp and find it with the following command

Show global variables like 'socket'

Permissions for socket files must be 777

do not enter the password directly on the command line, there is a security risk!

Characteristics of command line connection MySQL

There are plenty of extension parameters in the MySQL command line

Most of the DBA operation and maintenance management tools use command line mode.

can operate multiple machines at the same time, which is very efficient for DBA.

Connect to MySQL using a graphical client

Graphical client tools commonly used in 

ONavicat

OMySQLWorkBench

Advantages of graphical GUI tools

is easy to operate and easy to use.

supports graphical import and export

visual interface output, output visualization

Summary

applications need to use the API interface to connect to MySQL

development engineers can use graphical tools to connect to MySQL

The command line client is DBA's favorite.

Introduction to 1.4-SQL language

Relational database

data is stored in a table

Each row of the table is called a record

All records in the table have the same field (column)

What is SQL?

Structured Query Language

is a special purpose programming language for standard data access operations in relational databases.

The key for to communicate with the database

SQL language and Database

creates a table with SQL to define the fields in the table

uses SQL to add, delete, and modify records to the table

uses SQL to query the desired record from the table.

manipulates everything in the database with SQL

Classification of SQL statements

The classification and general use of SQL statements

DDL (Data Definition Language) creates tables, deletes tables, modifies tables...

DML (Data Manipulation Language) inserts records into the table, modifies or deletes records in the table...

Select queries the desired records from the table according to the conditions.

DCL (Data Control Language) controls database access and other settings

TCL (Transaction Control Language) controls transaction progress

DDL

OCREATE TABLE

ODROP TABLE

OALTER TABLE

DML

OSELECT FROM TABLE

OINSERT INTO TABLE

OUPDATE TABLE SET

ODELETE FROM TABLE

DCL

OGRANT

OREVOKE

TCL

OCOMMIT

OROLLBACK

Example:

Check what databases are currently available

Show databases;# uses a database named test

Use test;# creates a student table create table stu (

Id int (10)

Name varchar (20)

Age int (10)

Primary key (id)); # each table needs to contain a primary key, which uniquely identifies a record, a unique field that cannot be repeated and cannot be empty, and is defined by the primary key keyword.

View the created table

Show create table stu;# adds a new field alter table stu add column gender varchar (20); # modifies a field alter table stu modify column gender varchar (40); # deletes a field alter table stu drop column gender;# deletes the table drop table stu;# to view the table in the current database

Show tables

Insert data insert into stu (id,name,age) values into the table; # you can write only the table name insert into stu values when inserting all fields; # View the data you just added. "" represents query all fields select from stu;#. If you only want to query two fields, write only the field name select name,age from stu. # you can also query according to certain conditions, such as querying only the record select name age from stu where id=1; update statement with id 1, update stu set age=29 where id=1; deleting the data delete from stu where id=1 in the table.

1.5-get to know DBA

What is DBA?

DBA is the database administrator, like the goalkeeper of the football team, the last barrier of the business, the cornerstone of the stable operation of the business, and can provide a more enjoyable user experience.

Why do you need DBA

small company

O there is no full-time DBA

O but there must be someone who knows the database.

big company

O full-time DBA

O expert in data storage technology

Internet business has the following strict requirements:

high performance (database is an important part)

highly available (requires uninterrupted operation of the business)

extensible (support massive data and business, database expansion)

security (core business data sensitivity)

Without DBA, there would be no stable database, slow requests, data loss, security issues, user complaints, endless

What does DBA have to do?

basic operation and maintenance work

O installation and deployment

O Monitoring

O Fault handling

security operation and maintenance work

O data backup and recovery

O secure access, security vulnerabilities

O Audit

performance tuning

O Database optimization

O capacity assessment, software and hardware upgrades

development support work

O Storage plan development

O Database design

O Database changes, SQL Review

process and training

O Database development specification

O Operation and maintenance process standardization

O Business training

What skills are required for DBA

professional skills

O Database principle

OLinux and Shell

O computer architecture

O Network principle

O Database system and operation

O Server hardware

O Business architecture design

soft skills

O sense of responsibility, execution

O tenacity, pressure resistance

O Learning and communication skills

O Integrity and integrity

O patience and attention to detail

O Analytical ability

O teamwork

How to develop DBA

small company or big company

development channel:

ODBA-> Business architect

ODBA-> Operation and maintenance expert

ODBA-> database research and development

Summary

knows DBA.

O manage the database well

ODBA work is very important.

O pay equal attention to professional skills and soft skills

O good prospects for development

O Internet companies are short of good DBA.

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.