Database summary

Time:2020-10-27

Introduction of database service concept

Database: mainly used for effective analysis and processing of a large number of data information
Database server: MySQL maridb
Database: storing data, classifying user information — user information base, commodity information — commodity transaction information — payment business
Datasheet: Datasheet information
Data field: store specific content information, store information specification
Data line: real every legal data information


Database service environment installation and deployment

To deploy database in Windows system:https://dev.mysql.com/downloads/installer/

Start the PHP study program — > start mysql5.7.26

Mode 1: command line operation

Switch to the MySQL path in the command line

C: Users: switch to the disk where the file is located
			D\>cd d:\use\phpstudy_pro\Extensions\MySQL5.7.26\bin>mysql.exe -uroot -proot
			#CD to the specific path after input mysql.exe  -uroot -proot
	mysql>

Note: you can also use the other two methods to enter the command line

1. Enter the local folder into the path you want to go to, and then select it with the left mouse button. Select all by default, and then enter CMD --- > Enter to enter
			2. As above, in the folder, shift + right-click to have an "open command window here" option. Click it to enter.

Method 2: connect to database (graphical interface operation)
Installation and connection database program software: Navicat installation and deployment

Realize remote connection to database:

Local connection: localhost = = 127.0.0.1

Note: mode 2 should not be commonly used, so it has not tried to deploy, and the specific operation steps are not familiar.

To deploy database in Linux system:https://dev.mysql.com/downloads/mysql/

[ [email protected] ~]#Yum install - y MariaDB (command package) MariaDB server (package)
	[ [email protected] ~]#Systemctl start MariaDB

Method 1: connect to database (command line)

[ [email protected] ~]#MySQL - uroot ා enter database command
			Welcome to the MariaDB monitor.  Commands end with ; or \g.
			Your MariaDB connection id is 4
			Server version: 5.5.65-MariaDB MariaDB Server
			Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
			Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
			MariaDB [(none)] >, successfully entered
	[ [email protected] ]#Mysqladmin - uroot password "password" ා change the default password, can't change the password?

Method 2: connect to database (graphical interface)
How to establish a connection with the virtual host data path service???
Note: Linux hardly deploys graphical interfaces, does it? So there is no in-depth study here.


Database command and operation

;: semicolon, the end of command identifier in the database. Only the semicolon system can recognize you. This command is completed.

Create database information:
MariaDB [(none)] > create database PHP; ා creates a library named PHP
				MariaDB [(none)] > show databases; ා view what libraries you have
				+--------------------+
				| Database           |
				+--------------------+
				| information_schema |
				| mysql              |
				| performance_schema |
				|PHP is here
				| test               |
				+--------------------+
				5 rows in set (0.00 sec)
		MariaDB [(none)] > drop database PHP; ා delete the specified database
Note: after MySQL is installed, only two databases are found after logging in: show databases;
Database
information_schema
test

,mysql> use mysql
ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’
Access is denied because the database (RPM – e MySQL *) is not deleted completely when the database is deleted. You need to delete all the directories of / var / lib / MySQL, and then install it again.

We need to switch to the library

MariaDB [(none)] > use PHP; # use selects the name of the specified library
				Reading table information for completion of table and column names
				You can turn off this feature to get a quicker startup with -A
				Database changed
				MariaDB [PHP] >, into the PHP library

Check what tables are in the library

MariaDB [php]> show tables;
				+---------------+
				| Tables_in_php |
				+---------------+
				|Xyb | # has a form called xyb, which was created earlier
				+---------------+
		1 row in set (0.00 sec)

How to create a form?
Statement format: create table table name (field name 1, field type 2… Field name n, field type n);
Operation command: create table Xueyuan (name varchar (15), sex char (5), age int (5), Xueli char (10), Jingyan bool, Xinzi float (10,2));

MariaDB [php]> create table test(name varchar(15),sex char(5),age int(5),xueli char(10),jingyan bool,xinzi float(10,2));
		Query OK, 0 rows affected (0.00 sec) ා creates a form called test.
integer
MySQL data type Bytes occupied Value range
tinyint 1 byte -128~127
smallint 2 bytes -32768~32767
mediumint 3 bytes -8388608~8388607
int 4 bytes Range – 2147483648 ~ 2147483647
bigint 8 bytes +-The 18th power of 9.22 * 10

The length of the integer is different, so it is different in the actual use process.

Floating point type
MySQL data type Bytes occupied Value range
float(m, d) 4 bytes Single precision floating point, m total number, D decimal place
double(m, d) 8 bytes Double precision floating point, m total number, D decimal place
decimal(m, d) Decimal is a floating-point number stored as a string
Character type
MySQL data type Bytes occupied Value range
char 0-255 bytes Fixed length string
varchar 0-255 bytes Variable length string
tiniyblob 0-255 bytes A binary string of 255 characters or less
tinytext 0-255 bytes Short text string
blob 0-65535 bytes Long text data in binary form

View the specific information of the form

MariaDB [php]> desc test;
				Domain (field) type (field data) invalid (empty) key default extra
				+---------+-------------+------+-----+---------+-------+
				| Field   | Type        | Null | Key | Default | Extra |
				+---------+-------------+------+-----+---------+-------+
				| name    | varchar(15) | YES  |     | NULL    |       |
				| sex     | char(5)     | YES  |     | NULL    |       |
				| age     | int(5)      | YES  |     | NULL    |       |
				| xueli   | char(10)    | YES  |     | NULL    |       |
				| jingyan | tinyint(1)  | YES  |     | NULL    |       |
				| xinzi   | float(10,2) | YES  |     | NULL    |       |
				+---------+-------------+------+-----+---------+-------+
		6 rows in set (0.00 sec)

To see if there is content in the form:
Grammar format:select * from table;
Syntax command:select * from xyb;

To modify the name of a table in the database:
Statement format: alter table old table name rename new table name;
Operation command: alter table test rename test01;

**Delete table information
Statement format: drop table name;
Operation command: drop table Oldboy**

MariaDB [php]> drop table test;
				Query OK, 0 rows affected (0.00 sec)
		The query is normal

**The fields and field data of the form can be added, deleted, modified and checked, and the fields can be added, inserted and deleted.
Its command format is almost the same as the first three, what operation to do after, just change words. * *

MariaDB [PHP] > desc test; ා creates a form
				Domain (field) type (field data) invalid (empty) key default extra
				+---------+-------------+------+-----+---------+-------+
				| Field   | Type        | Null | Key | Default | Extra |
				+---------+-------------+------+-----+---------+-------+
				| name    | varchar(15) | YES  |     | NULL    |       |
				| sex     | char(5)     | YES  |     | NULL    |       |
				| age     | int(5)      | YES  |     | NULL    |       |
				| xueli   | char(10)    | YES  |     | NULL    |       |
				| jingyan | tinyint(1)  | YES  |     | NULL    |       |
				| xinzi   | float(10,2) | YES  |     | NULL    |       |
				+---------+-------------+------+-----+---------+-------+
		6 rows in set (0.00 sec)
Modify field data type information
				Statement format: alter table name modify field name data type after modification;
				Operation command: alter table test modify sex varchar (10); add field information in the table:
				Statement format: alter table name add column field name segment type;
				Operation command: alter table test add column Oldboy date;
    
				·Add field information in the table: you can specify the position where the control field is added
				Statement format: alter table name add field name bool after which field is added;
				Operation command: alter table test add oldgirl bool after age; 
      
				Add field information in table: insert the specified field into the first column
				Statement format: alter table name add field type first;
				Operation command: alter table test add oldtable char first;
      
				·Delete field information in the table:
				Statement format: alter table name DROP column field name;
				Operation command: alter table test drop column oldtable;
      
				·Modify field name information:
				Statement format: alter table name change the field name segment type after the original field name is modified;
				Operation command: alter table test change Xueli edu varchar (15);
      
				·Modify the existing field order:
				Statement format: 
				Alter table name modify field name type first; --- directly move the specified field to the first column
		Alter table test modify field name type after field name; --- move the field after the specified column

**Database index
Index concept introduction: it can improve the efficiency of searching specified data, similar to dictionary.
Index classification: general index, unique index (no duplicate field information), primary key index (field information cannot be empty).
Index view, create and delete:
To view the form index:**

Syntax format: Show index from table name
		Syntax command: Show index from xyb

Index creation: normal, mul.

Syntax format: alter table add index (field);
		Operation command: alter table Xueyuan add index (name);

Index creation: unique, uni.

Syntax format: alter table add unique (field)
		Operation command: alter table Xueyuan add unique (Xueli);

Index creation: primary key, pri.

Syntax format: alter table add primary key (field)
		Operation command: alter table Xueyuan add primary key (Jingyan);

Delete the index information. This command can delete the common and unique index. The primary key index needs to be deleted in other command formats.

Syntax format: drop index index name on table name
		Syntax command: drop index Jingyan on xyb

Note: this command is full and cannot drop the unique index.

Delete primary key index information

Syntax format: alter table form drop primary key;
		Syntax command: alter table xyb drop primary key;

Note: sometimes this command cannot be deleted? I don’t know what’s going on.

Search the matching data information in the form by criteria:

Grammar format: 
				select * from table where condiction
				Operation command: 
		select  name,sex,age  from    xueyuan  where    age=23 and sex="female" or sex="male";

Match condition symbol information >, <, > =, < ==
Matching condition logical symbol and or
Cancel duplicate message:
Grammar format:select distinct field from table;

Add data information to the form:
Example 1:

Syntax format: insert into table name value (field 01 data, field 02 data, field 03 data,..., field n data);
		Operation command: insert into Xueyuan value ("Zhang San", "male", "23", "undergraduate", 09000);

Example 2:

Syntax format: insert into table (field 01, field 02, field 03) value (field data 01, field data 02, field data 03);
		Operation command: insert into Xueyuan (name, sex, Jingyan) value ("Li Si", "female", 1);

In the MySQL installation process, it is easy to ignore the character set parameter (source code installation parameter ddefault)_ CHARSET=utf8 -DDEFAULT_ COLLATION=utf8_ general_ ci)。 Or maybe MySQL was not built by itself at the beginning. Others didn’t notice this problem when they installed it. Now the character set is inconsistent with the requirements. For example: now the character set is not UTF-8, resulting in the display of Chinese garbled code, the solution is as follows.
First, log in to MySQL as root and execute the command: Show variables like ‘character_ Set% ‘; check the default character set of MySQL. If there is a problem, exit MySQL first.
Edit MySQL configuration file my.cnf 。 By default, the file is in / etc/ my.cnf
The contents are as follows:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysqld]
port = 3306
basedir = /usr/local/mysqldatadir = /dataserver_id = 1
character-set-server=utf8
socket = /var/lib/mysql/mysql.sock
Save and exit, and then restart MariaDB. Just enter mysql. The form created before changing the character set will not take effect.


Database permissions

To view the total permissions of the database:select * from mysql.userG;
Check the permissions of user table in MySQL database

MariaDB [(none)]> select user,host from mysql.user;
					+------+-----------+
					| user | host      |
					+------+-----------+
					| root | 127.0.0.1 |
					| root | ::1       |
					|      | localhost |
					|Root | localhost | # this permission means that root can only use local connection to manage database 
					|      | oldboy72  |
					| root | oldboy72  |
					+------+-----------+
					#User represents which users in the database can operate and manage the database. 
			#Host represents which hosts can connect to the database