Mysql database learning (1): Basic Concepts of databases, relational databases, and Mysql _ MySQL

Mysql database learning (1): Basic Concepts of databases, relational databases, Mysql database installation and configuration, and DDLDCLDML statements bitsCN.com

I. basic concepts of databases

Database (DB)

Warehouses that organize and store data according to the data structure

Database management system (DBMS)

Database Management System is a set of software used to manipulate and manage databases. it is used to establish, use, and maintain databases.

Database System (DBS)

Database

Database management system (and development tools)

Application System

Database Administrator

User

II. relational databases

Relational database

The relational model is used as the data organization mode. Simply put, the logical structure of data is a two-dimensional table consisting of rows and columns. Each row of the table is a tuples, and each column is an attribute.

Integrity constraints

Entity integrity

The primary key is not empty (it cannot be repeated)

Integrity of reference (foreign key)

It can be null or equal to the master code value of another link.

User-defined integrity

Used to set the value range of an attribute

If you set the ethnic code and department code as foreign keys, the value must be 1 or 2 or NULL.

Common relational databases:

1. MySQL 2. SQL Server3.Oracle4. Sybase5.DB26 Informix7.Access

III. install and start the Mysql database
Mysqld.exe server program (if it is not started during installation, mysqld-install in cmd)
Mysql.exe client program (navicat is also recommended for database connection)
Other tool sets

Start the service

Control by service control panel (services. msc)

Use command line

Net start MySQL

Net stop MySQL

Change password:

Mysqladmin-u root-poldpass password newpass

Mysqladmin-usimba-p123456 password 123/* you must have the permission to modify the password */

When the server starts, it will read the my. ini configuration file. if not, you can directly create one, similar to the following content:
[Mysqld]

# Binding IPv4 and 3306 ports
Bind-address = 0.0.0.0
Port = 3306

# Set the installation directory of mysql
Basedir = D:/mysql-5.6.13

# Set the directory for storing mysql database data
Datadir = D:/mysql-5.6.13/data

# Maximum number of connections allowed
Max_connections = 200

4. some basic DDL/DCL/DML statements
SQL is StructuredQuery Language;
DDL (Data Definition Language)

-- Used to create databases, database objects, and define their columns

-- CREATE, DROP, ALTER, etc.

DCL (Data Control Language)

-- Used to control access permissions and access permissions;

-- GRANT and REVOKE;

DML (Data Manipulation Language)

-- Query, insert, delete, and modify data in the database;

-- SELECT, INSERT, UPDATE, DELETE, etc;

SQL Code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 Create database dbname [database Options];/* if the database name is special, such as 234, or if the database name is retained, enclose it with backticks */

Show create database dbname;/* settings used in the creation process */

Alter database dbname character set gbk/* supports Chinese characters */

Create user username identified by '123456'/* enclose the password in quotation marks */

Grant select, insert, update, delete on *. * to username @ '%' identified by '000000 '/**. * Any table '%' in any database indicates any host */

Grant all privileges on *. * to username @ '%'
Revoke all privileges on *. * from username @ '%'

Refer:

Introduction to Database Systems

Mysql 5.1 Reference Manual

BitsCN.com