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

1.1. Database model

The database consists of an ordered collection of data, which are stored in structured data tables. The data tables are related to each other, reflecting the essential relationship between objective things. Database system provides security control and integrity control of data. The development of database is roughly divided into several stages: manual management stage, file system stage, database system stage, advanced database stage. There are about three types: hierarchical database, network database and relational database.

Text database defects: data redundancy and inconsistency, data access difficulties, data isolation, integrity problems, atomicity problems, concurrent access problems, security problems.

Database system features: data management independence; effective completion of data access; data integrity and security; data centralized management; concurrent storage and fault recovery; reduction of application development time.

The database system is composed of hardware and software. the hardware is mainly used to store data in the database, including computers, storage devices and so on. The software part mainly includes DBMS, the operating system that supports DBMS, and the access technology that supports application development in multiple languages.

Common relational database architecture:

1.2, data types and constraints

Data types determine the format in which data is stored in the computer and represent different types of information. Common data types are:

Character: CHAR (n) VARCHAR (n) BINARY (n) VARBINARY (n) TEXT (n) BLOB (n)

Numerical value:

Integer: TINYINT SMALLINT MEDIUMINT INT BIGINT

Modifier: UNSIGNED NOT NULL

Floating point: FLOAT DOUBLE

Date time: DATETIME DATETIME STAMP

Boolean

NULL

Built-in: ENUM, SET

Field modifier:

NOT NULL is not empty

NULL null

UNSIGNED unsigned

DEFAULT does not apply to TEXT types

PRIMARY KEY primary key

UNIQUE KEY unique key

AUTO_INCREMENT: fields of auto-growth type must be primary key or unique key

Domain constraints: data type constraint

Foreign key constraints: referential integrity constraint

Primary key constraint: a field uniquely identifies the entity to which this field belongs and is not allowed to be empty. There can be only one primary key in a table.

Uniqueness constraint: a field in each row is not allowed to have the same value. It can be empty. There can be more than one in a table.

Checking constraints: user-defined range of valid values.

1.3introduction to MySQL

MySQL is a small relational database management system. Compared with other large database management systems (Oracle, DB2), MySQL has small scale and limited functions, but it has small size, high speed, low cost, and the functions it provides are sufficient for slightly complex applications.

The main operation flow of MySQL is as follows:

MySQL database management system provides many command-line tools, which can be used to manage MySQL servers, access control of databases, manage MySQL users, database backup and recovery tools, and so on.

Server-side tool program:

Mysqld:SQL daemon. After the program runs, the client can connect to the server to access the database.

Mysqld_safe: server startup script.

Mysqld_multi: server startup script that starts or stops multiple servers installed on the system.

Myisamchk: a utility for describing, examining, optimizing, and maintaining MyISAM tables.

Mysqlbug:MySQL defect report script. It can be used to send defect reports to the MySQL mail system.

Mysql_install_db: this script creates the MySQL authorization table with default permissions. It is usually performed only once when the system installs MySQL for the first time.

Client tool program:

Myisampack: a tool for compressing MyISAM tables to produce smaller read-only tables

Mysql: a command line tool that interactively enters SQL statements or executes them in batch mode from a file

Mysqlaccess: a script that checks permissions to access the hostname, user name, and database combination

Mysqladmin: client programs that perform administrative operations, such as creating or deleting databases, reloading authorization tables, reopening log files, etc., and can also be used to check version, process, and server status information

Mysqlbinlog: a tool for reading statements from a binary log file that contains executed statements that can be used to help the system recover from a crash

Mysqlcheck: check, fix, analyze, and optimize tables

Mysqldump: dump the mysql database to a file

Mysqlhotcopy: a tool for quickly backing up MyISAM tables while the server is running

Mysqlimport: a library program that imports text files into related tables using LOAD DATA INFILE

Mysqlshow: a client program that displays information about databases, tables, columns, and indexes

Perror: a tool that displays the meaning of a system or MySQL error code

Mysqladmin [options] command [arg] [command [arg]]...

[options]: all traffic options are available

Command:

Create DB_NAME create database drop DB_NAME delete database

Debug: open the debug log and record it in error log

Status: displays brief status information (--sleep #: interval time-- count #: displayed batches)

Extended-status: outputs the state variables of mysqld and their values, which is equivalent to executing mysql > SHOW GLOBAL STATUS

Variables: output each server variable of mysqld

Flush-hosts: clear the host-related cache: DNS parses the cache. The list of hosts that were previously denied access to mysqld due to too many connection errors

Flush-logs: log scrolling, binary log and relay log

Refresh: equivalent to using flush-logs and flush-hosts at the same time

Flush-privileges, reload: refresh authorization table

Flush-status: resets the value of the state variable

Flush-tables: closes the currently open table file handle

Flush-threads: clear thread cache

Password: change the password of the current user

Ping: test whether the server is online

Processlist: displays a list of mysql threads

Shutdown: shut down the mysqld process

Start-slave/stop-slave: starting / shutting down slave server threads

Kill: kill specified threads. You can kill more than one thread at a time, separated by commas, with no extra spaces.

1.4.The source code installs MySQL5.5

Install the cmake software and dependency packages:

[root@mylinux home] # yum install-y cmake ncurses-devel

Create users and groups

[root@mylinux home] # groupadd mysql [root@mylinux home] # useradd mysql-s / sbin/nologin-M-g mysql

Decompress and compile MySQL

[root@mylinux home] # wget [root@mylinux home] # tar xf mysql-5.5.56.tar.gz [root@mylinux home] # cd mysql-5.5.56 [root@mylinux home] # cmake. -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.56\-DMYSQL_DATADIR=/application/mysql-5.5.56/data\-DMYSQL_UNIX_ADDR=/application/mysql-5.5.56/tmp/mysql.sock\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DEXTRA_CHARSETS=gbk,gb2312,utf8 Ascii\-DENABLED_LOCAL_INFILE=ON\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_FEDERATED_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITHOUT_PARTITION_STORAGE_ENGINE=1\-DWITH_FAST_MUTEXES=1\-DWITH_ZLIB=bundled\-DENABLED_LOCAL_INFILE=1\-DWITH_READLINE=1\-DWITH_EMBEDDED_SERVER=1\-DWITH_DEBUG= 0 [root @ mylinux home] # make [root@mylinux home] # make install [root@mylinux application] # cd / application/ [root@ Mylinux application] # ln-s / application/mysql-5.5.56/ / application/mysql [root@mylinux application] # mkdir / application/mysql/data-p [root@mylinux application] # cd mysql [root@mylinux mysql] # chown-R root:mysql / application/mysql [root@mylinux mysql] # mdkir-p / application/mysql-5.5.56/tmp [root@mylinux mysql] # chmod-R 777 / application/mysql-5.5.56/tmp

Select profile and environment variables

[root@mylinux mysql] # cp support-files/my-large.cnf / etc/my.cnf [root@mylinux mysql] # vim / etc/profile.d/mysql.sh export PATH=/application/mysql/bin:$PATH

Initialize the database

[root@mylinux mysql] # / scripts/mysql_install_db-- user=mysql-- data=/application/mysql/data/Installing MySQL system tables...170711 22:09:40 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.170711 22:09:40 [Note] Ignoring-- secure-file-priv value as server is running with-- bootstrap.170711 22:09:40 [Note]. / bin/mysqld (mysqld 5.5.56-log) starting as process 28738 .. OKFilling help tables...170711 22:09:41 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.170711 22:09:41 [Note] Ignoring-- secure-file-priv value as server is running with-- bootstrap.170711 22:09:41 [Note]. / bin/mysqld (mysqld 5.5.56-log) starting as process 28745. OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER! To do so Start the server, then issue the following commands:./bin/mysqladmin-u root password' new-password'./bin/mysqladmin-u root-h mylinux password' new-password'Alternatively you can run:./bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd. ;. / bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.plcd. / mysql-test; perl mysql-test-run.plPlease report any problems at http://bugs.mysql.com/

Add startup script and set boot up

[root@mylinux mysql] # cp support-files/mysql.server / etc/init.d/mysqld [root@mylinux mysql] # chmod + x / etc/init.d/mysqld [root@mylinux mysql] # chkconfig-add mysqld [root@mylinux mysql] # chkconfig-level 2345 mysqld on [root@mylinux mysql] # service mysqld start Starting MySQL.. SUCCESS!

Configuration file for mysql:

/ etc/my.cnf or / etc/mysql/my.cnf-- > $MYSQL_BASE/my.cnf->-- defaults-extra-file=my.cnf

-- > ~ / .my.cnf

The way configuration files are used

1. It looks for each file in turn, and the result is that all files are merged.

2. If a parameter appears multiple times in multiple files, the read will eventually take effect.

1.5.The MySQL variable and scope

Server variable: @ variable name

Display: SELECT @ @ variable name

Setting: SET GLOBAL | SESSION variable name = 'value'

Set the value of the server variable: (only used to support dynamic variables)

Server variables that are supported for modification:

Dynamic variables: can be modified at MySQL runtime, can be modified immediately

Static variable: modify its value in the configuration file and restart it before it takes effect

The effective mode of dynamically adjusting parameters:

Global variable: server level, valid only for newly established sessions after modification

Session variables: session level, valid only for the current session; when the session is established, inherit the variables from the global

View server variables:

Mysql > SHOW [{GLOBAL | SESSION}] VARIABLES [LIKE']; mysql > SELECT @ @ {GLOBAL | SESSION} .VARILABLE _ NAME;mysql > SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='NAME';mysql > SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='NAME'

Modify variables: by default, only administrators have permission to modify global variables

Mysql > SET {GLOBAL | SESSION} VARIABLE_NAME='VALUE'

Note: dynamic variable modifications at both global and session levels will become invalid after restarting mysqld; to be permanently valid, you can define them in the corresponding section in the configuration file [mysqld].

1.6.Resolvent of Chinese data garbled in MySQL

1) before inserting data into the table, set the character set of the client to be consistent with the table

SELECT CREATE TABLE tb_name\ G View the character set of the table set names character set

2) specify the character set in the SQL file and then log in to MYSQL to import the data

Source tb_namq.sql

3) specify the character set in the SQL file and import the data through the mysql command

Mysql-uroot-paired password 'tb_name

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.