Database concept

A database is where data is stored. In the computer, in the memory and in the hard disk, everything is stored in the database. The place where these data are stored is called the database. It can also be called electronic "filing cabinet".

The database in computer science and application means that the data will be more and more huge in the future, and it also means that more data should be used in more important science and applications in the future development.

Type of database

Relational type
Feature 1: there are relationships or constraints between data

  Feature 2 the expression form of stored data is usually stored in tables	
	
    Each field will also have storage type restrictions
    For example, names can only be saved as strings...

Non relational
Stored data is usually in the form of k,v key value pairs

MySQL

Including the client and server, the bottom layer of any application based on network communication uses socket

Introduction to important concepts

A library is a folder
Table is file
Records are lines of data in a file

First row field of header table
Fields: name, password, hobby

MySQL server and client

"""
Server
mysqld.exe

client
mysql.exe
"""

be careful

When configuring MySQL in the early stage, the cmd terminal should run as an administrator as far as possible

cmd directly enters the ordinary user terminal, and some commands cannot be executed
It is recommended to right-click to run as administrator

start-up

• First switch to the bin directory where mysqld is located, and then enter mysqld

• Keep the original cmd window and reopen one

• If you enter MySQL as an administrator for the first time, you can enter directly without a password

Default port number for common software
MySQL 3306
redis 6379
mongodb 27017
django 8000
flask 5000

Client connection server complete command

  	mysql -h 127.0.0.1 -P 3306 -uroot -p

Brief introduction to sql statement

sql statements in MySQL end with a semicolon

View all library names

	show databases;

Command to connect to the server

	mysql -uroot -p

When you enter the wrong command and do not want the server to execute and return the error message, you can use \ c to cancel

	Error instruction\c

The client exit command can be executed with or without a semicolon

	quit
	exit

Environment variable configuration and system service production

View the current specific process

	tasklist
	tasklist |findstr mysqld

Kill specific processes (admin cmd)

	taskkill /F /PID PID number

View the number of running processes on the current computer

	services.msc

Making mysql into system service

	mysqld --install

Remove mysql system service

	mysqld --remove

Set password

mysqladmin -uroot -p Original password password New password

The change command can be input directly at the terminal to enter the client unordered

mysqladmin -uroot -p123 password 123456

Crack the password

principle

The function of obtaining user name and password verification by mysql is regarded as a decorator, which is decorated on the function of client requesting access
If the decorator is removed, the mysql server will not verify the user name and password

Close the current mysql server

Start from the command line (let mysql skip the user name and password authentication function)

 mysqld --skip-grant-tables  

Connect directly without a password

	mysql -uroot -p   Direct enter

Modify the password of the current user

	update mysql.user set password=password(123456) where 		user='root' and host='localhost';

Brush the modified data to the hard disk immediately

	flush privileges;

Close the current server and start it in the form of normal verification authorization table

Unified coding

***mysql default configuration file**

my-default.ini
ini usually ends with a configuration file

The program will be started after loading the configuration in the configuration file

[mysqld] once the server starts, load the following configuration immediately
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql] once the client starts, load the following configuration immediately

[client] other clients

You need to create a new my.ini configuration file

Verify that the configuration is really loaded automatically
[mysql]
print('hello world')

After modifying the configuration file, you must restart the service to take effect

The user name and password of the administrator are also added to the configuration file
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
user="root"
password=123456
default-character-set=utf8

Basic sql statement

Addition, deletion, modification and query of Library (folder)

# increase
create database db1;
create database db2 charset='gbk';
# check
show databases;  # Check all
show create database db1;  # Check single
# change
alter database db2 charset='utf8';
# Delete
drop database db2;

Addition, deletion, modification and query of tables (documents)

#When operating tables (files), you need to specify the library (folder) where they are located

# View the name of the current library
select database();
# Switch Library
use db1; 

# increase
create table t1(id int,name char(4));
# check
show tables;  # View all table names under the current library
show create table t1;
describe t1;  # Support the abbreviation desc t1;
# change
alter table t1 modify name char(16);
# Delete
drop table t1;


#create table db2.t1(id int);   You can also manipulate different libraries in the form of absolute paths

Addition, deletion, modification and query of data (one line of data)

#You must have a library and a table before you can operate records

# increase
insert into t1 values(1,'jason');
insert into t1 values(1,'jason'),(2,'egon'),(3,'tank');
# check
select * from t1;  # This command is not recommended when the amount of data is very large
select name from t1;
# change
update t1 set name='DSB' where id > 1;
# Delete
delete from t1 where id > 1;
delete from t1 where name='jason';
# Clear all data in the table
delete from t1;