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

This article mainly introduces the basic basic knowledge of MySQL, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the basic basic knowledge of MySQL.

I. installation:

Sudo apt-get install mysql-server # download (sudo apt-get install php5-mysql (install php5-mysql connects php and mysql) sudo service mysql start # start service sudo service mysql restart # restart service sudo apt-get autoremove-- purge mysql-server-5.0sudo apt-get remove mysql-serversudo apt-get autoremove mysql-serversudo apt-get remove mysql-common # # uninstall service dpkg-l | grep ^ rc | awk'{print $2}'| sudo xargs dpkg-P # clean up thoroughly

Offline package installation

Group add mysql

Mkdir / home/mysql

Useradd-g mysql-d / home/mysql mysql # add user

Copy mysql-5.0.45-linux-i686-icc-glibc23.tar.gz to / usr/local directory

Tar zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz

Ln-s mysql-5.0.45-linux-i686-icc-glibc23 mysql

Cd / usr/local/mysql

Chown-R mysql.

Chgrp-R mysql.

Scripts/mysql_install_db-- user=mysql (be sure to execute in the mysql directory, pay attention to the output text, which contains commands to change the root password and start mysql)

. / bin/mysqladmin-u root password 'passw0rd' # modify root password

Configuration

The directory is / etc/mysql/mysql.cnf

/ etc/mysql/conf.d/

/ etc/mysql/mysql.conf.d/

Main configuration:

Bind-address 127.0.0.1 (default)

Port 3306 (default)

Datadir / var/lib/mysql (default)

General_log_file / var/log/mysql/mysql.log

Log_error / var/log/mysql/error.log

Optional client navicat (graphical interface)

Tar zxvf navicat....tar.gz

. / start_navicat

2. Operation of command mode

Mysql-u root-p # login

Quit exit

Show databases

Use databasename1

Select database (); # View the database currently in use

Create database databasename1 charset=utf8

Drop database databasename1

III. Data

Common data types

Int bit integer

Decimal decimal

Varchar char string

Datetime datetime date time

Decimal (5Bing 2) means five digits, with two digits after the decimal point.

The difference between varchar (3) and char (3): the latter automatically fill in the blanks when filling in 2 bits.

two。 Constraint

Primary key: primary key, physically stored in order that satisfies the second normal form

Not null: null values are not allowed in this field

Unique: the value of the field cannot be duplicated

Default: the default value will be used when this value is left empty. If so, the default value will prevail.

Foreign key: constrains the relationship field. When you fill in a value for the relationship field, you will query whether the value exists in the associated table. If it exists, it succeeds. If it does not exist, it fails and an exception is thrown.

Description: although foreign key constraints can ensure the validity of the data, but in the data crud (add, modify, delete, query), will reduce the performance of the database, so it is not recommended to use, then how to ensure the validity of the data? A: it can be controlled at the logic level.

IV. Database design

Three paradigms

After studying and summarizing the problems in use, this paper puts forward some specifications for designing the database, which are called paradigms.

There are six paradigms in database design, which generally need to follow three paradigms.

The first normal form (1NF): each column of a database table is an indivisible atomic data item, that is, the column is inseparable

The second normal form (2NF): based on the first normal form, it requires that each instance or record in the database table must be uniquely distinguished, that is, uniquely identified.

The third normal form (3NF): based on the second normal form, any non-primary attribute does not depend on other non-primary attributes, that is, referencing the primary key

Emurr model

E stands for entry, entity, design entity, just like defining a class, specifying in which ways the object is described, and an entity is converted into a table in the database.

R stands for relationship, relationship describes the corresponding rules between two entities, the types of relationships include one-to-one, one-to-many, many-to-many

A relationship is also a kind of data that needs to be stored in a table through a field.

If entity A versus entity B is 1 to 1, a field is created in Table An or Table B to store the primary key value of another table.

Logical deletion

For important data, you do not want to be physically deleted. Once deleted, the data cannot be recovered.

Delete scheme: set the column of isDelete. The type is bit, which means logical deletion. The default value is 0.

For non-important data, you can physically delete it.

The importance of data should be decided according to the actual development.

After reading the above basic knowledge about MySQL, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

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.