Chapter XVI. Primary Knowledge Database
I. Database
1. What is a database management data system - Installing a system for managing data - The object of management is essentially a file.
2. Location of Data Storage: Memory, Hard Disk
3. What is a system: Running on the basis of hardware, managing other software
II. Composition of database
Storage: Store multiple tables - folders
Table: Record-file containing multiple identical structures
Record: A data-binary data containing multiple key-value pairs
Field: Description information - information itself = key-value - binary data
"""
stu
id name age gender
1 Bob 18 Males
2 Tom 17 Female
"""
Classification of databases
# 1) Relations and Non-Relations
# Relationships: Relationships between tables in the database - mysql
# Non-relational: No table concepts - redis, mongodb (between relational and non-relational)
# 2) Memory and Hard Disk
# Hard disk: data can be stored permanently - mysql, mongodb
# Memory: data access efficiency is very high - redis, memcache
# 3) sql and nosql
# sql: database operations through sql statements
# nosql: A database operation is a key-value form (value is a record)
# stu - {'name': 'Bob', 'age': 18}
# stus - [{'name': 'Bob', 'age': 18}, {'name': 'Tom', 'age': 18}]
# name - 'Jerry'
IV. Unloading database
Prerequisite) Start the terminal, enter mysql, if not prompted, not internal or external commands, representing the installed database
1) Direct Connection
i Search: Service, mysql Search Service, there are
Stop Services and Remove Services - Start Administrator Terminal: mysqld --remove
ii search: service, retrieve MySQL service, no (mysql starts with command)
Stop Services and Start Administrator Terminal
tasklist | findstr mysql
Taskkill/pid process pid/f
2) Connection timeout
Search: Service, search mysql service, remove service
3) Remove environmental variables
Summary: If you can connect, stop the service first | If you add the service, remove the service | Cancel the configuration of environment variables5. Installation of databases
"""
"""
1. Unzip-free installation version (5.6 - No initial password)
2. Configuring environment variables: bin under absolute path of database
3. Configuration services:
Start Administrator Terminal: mysqld --install [nmysql]
"""6. Connecting database
1) Tourist landing (not necessarily able to login, login can do nothing)
>: mysql
2) Account password login
> Mysql-u root-p return
Enter the password again, without any prompt, no password to return directly
3) mysql to connect to the specified server
> Mysql-h IP address-P port number-u account-p
Enter the password after return
eg:
>: mysql -hlocalhost -P3306 -uroot -p
4) exit the database
>: quit
>: exit
7. Viewing User Information
1)View the currently logged-in user
mysql>: select user();
2)root All user information can be viewed under permissions
mysql>: select * from mysql.user;
mysql>: select * from mysql.user \G
mysql>: select user,password,host from mysql.user;
3)root Log in and delete visitors (restart after operation) mysql Service)
mysql>: delete from mysql.user where user='';
4)root Log in, change password (restart after operation) mysql Service)
mysql>: update mysql.user set password=password('12345678') where host='localhost';
5)not logged on
>: mysqladmin -u User name -p Old password -h domain name password "New password"
eg>: mysqladmin -uroot -p12345678 -hlocalhost password "root"
6)root Log in and create users
mysql>: grant Permissions on Database name.Table name to User name@host name identified by 'Password';
8. Basic operation of database
1)View existing databases
mysql>: show databases;
2)Select a database
mysql>: use Database name
3)View the current database
mysql>: select database();
4)Create a database
mysql>: create database Database name [charset=Encoding format];
eg>: create database owen;
eg>: create database zero charset=utf8;
eg>: create database tank;
5)View the details of creating a database
mysql>: show create database Database name;
eg>: show create database owen;
6)Delete the database
mysql>: drop database Database name;
eg>: drop database tank;
9. Basic operation of tables
Prerequisite: Select the database to be operated on first
1)View existing tables
mysql>: show tables;
2)Create table
mysql>: create table Table name(Fields);
eg>: create table student(name varchar(16), age int);
eg>: create table teacher(name varchar(16), age int);
3)View the Created Table sql
mysql>: show create table Table name;
eg>: show create table student;
4)View the structure of the created table
mysql>: desc Table name;
5)Delete table
mysql>: drop table Table name;
eg>: drop table teacher;
6)Modify table fields
mysql> alter table orders modify column shipaddress int(20);
X. Basic Operation of Recording
1) Look at all the records of a table in a database. If you have a corresponding database, you can look up the table directly.
MySQL >: select * from [database name.] table name;
Note: * Represents querying all fields
2) Insert data into all fields of a table
MySQL >: insert [in] [database name.] table name values (value 1,..., value n);
eg: If you insert data into the student table with name and age fields
1 >: insert into student values ('Bob', 18);
Multiple >: insert into student values ('Zhang San', 18), ('Li Si', 20);
Specified library >: insert owen.student values ('Zhang San', 18), ('Li Si', 20);
3) Modify the specified content according to the conditions
MySQL >: update [database name.] table name set field 1 = new value 1, field n = new value n where field = old value;
Eg:> update student set name ='Wang Wu', age ='100'where name ='Zhang San';
Note: i) You can modify only part of field ii) Without conditions, all records will be updated.
Eg:> update student set name='ha';
4) Delete records according to conditions
MySQL >: delete from [database name.] table name where condition;
eg:> delete from student where age<30;