A database is a warehouse for storing data. In the development of application, it is always inseparable from data query, processing and storage. For example, library management system needs to manipulate and store a large amount of data. Before there was no database, we used files to store data, but file storage has many disadvantages, such as low query efficiency, troublesome reading, difficult management, etc. so many troubles can be handed over to the database for management. The database is a software specially developed for data storage management, which can be usedHigher efficiency and security。
Classification of database
Different databases manage data in different ways. It can be divided into two categories based on whether it is a relational database or not.
Relational type：MySQL、ORACAL、DATEBASE、PostgreSQL、SQL server
The main purpose of this article is to master the basic application of MySQL.
What is a relational database?The tables stored in a relational database are two-dimensional tablesFor example, a student information table contains many items: student number, name, gender and age. 60 students have 60 different student numbers, up to 60 names, gender or male or female. Each item (each column in the table) is a set of values of the same type, and the sets are not randomly combined, not randomly combined with a student number or a name, but meaningful specific combinations, which are used to represent student information one by one, This is the so-called “relationship”. If you don’t want to understand this, just think it’s excel.
Windows installation MySQL 5.7.17
On MySQL official websitehttp://dev.mysql.com/downloads/mysql/Download the zip installation package (windows (x86, 64 bit), zip archive) above.
After downloading, unzip it and put it in the directory you want to install. For example: D: \ MySQL 5.7 \ mysql-5.7.17-winx64
Create a new my.ini configuration file. The original my-default.ini configuration file is just a template and should not be changed in it. The contents of my.ini are as follows:
[mysql] default-character-set=utf8 [mysqld] port = 3306 basedir=D:\MySQL5.7\mysql-5.7.17-winx64 datadir=D:\MySQL5.7\mysql-5.7.17- winx64\data max_connections=200 character-set-server=utf8 default-storage-engine=INNODB explicit_defaults_for_timestamp=true
Create an empty data folder under the installation path.
Run CMD as an administrator, enter the bin directory, and execute
mysqld --initialize-insecure --user=mysqlCommand. Without this step, the service cannot be started after the installation is completed.
Still in the bin directory of the administrator CMD window, execute
mysqld installCommand installation. After completion, you will be prompted that the installation is successful.
Still in the bin directory of the administrator CMD window, execute
net start mysqlCommand to start MySQL service.
Modify the environment variable and add “D: \ mysql-5.7.17-winx64 \ bin”.
In the normal CMD window, enter the bin directory and execute
mysql -u root -pCommand, no password by default, enter.
Installing MySQL on MAC system
Enable homebrew in MAC to install MySQL
Download and install MySQL
brew install [email protected]
If you see the following boundary ⾯ it indicates that the download and installation have been successful
Login to MySQL
mysql -u root -p
Basic logical structure of database
The whole MySQL library contains multiple libraries, four of which are built-in core libraries. There are several tables in each library, and each table is similar to an excel table - MySQL -Several libraries -Several tables
Get to know MySQL
- First, input the command from the terminal
mysql -u root -pLog in to MySQL
- View library input
show databases;Statement to view all libraries (all SQL statements end with a semicolon). give the result as follows
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ --You can see four preset libraries, all of which are core libraries. Do not delete them.
- Select one of the libraries (for example, MySQL Library)
- View all tables in the library
+---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | ... ... | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ --The number of lines is relatively large, and the middle part is indicated by an ellipsis
select host,user from user;View the host and user field columns for all data in the user table
+-----------+---------------+ | host | user | +-----------+---------------+ | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+
select * from user;View all fields of all data in the user table
Create a new library
create database mydb default charset=utf8; --After execution, you can see the mydb library by viewing all the libraries. Where default charset = utf8, the character set used by the selected library is UTF-8
Create a new table
--To create a table in which library, you must first select that library, and then use the following statement to create a simple table create table user( name varchar(20), age int, sex char(1) )engine=innodb default charset=utf8; --Note that each field is separated by a comma. There is no comma after the last field!
Adding data to a table
--Add name, age and sex data to the user table Insert into user (name, age, sex) values ('admin ', 26,' male '); --The field assignment order can be arranged freely or assigned selectively. If no assignment order is provided, all assignments will be made in the defined order by default Insert into user values ('zhang San ', 22,' female ');
Data manipulation language
--Data manipulation is the manipulation of data content, including insert, delete and update. --Insert: in addition to inserting one row of data in the above statement, you can also insert multiple rows of data at one time: Insert into user values ('zhang San ', 22,' female '), ('admin', 26, 'male'), ('alian ', 16,' male '); --Delete: carefully control the filtering conditions. If the filtering is not properly controlled, all data will be deleted Delete from user where name = 'Zhang San'; --Update: also pay attention to the filter conditions when updating. The following statement directly changes the age and gender of the user admin Update user set age = 23, sex = 'female' where name = 'admin';
Data definition language
--Data definition refers to the operation of table structure, including table definition and table structure change. --There are instances above the table creation statement, and the table deletion statement is as follows: drop table user; --Add field (column), delete field (column) alter table user add birthday date; alter table user drop age; --Change field. The field name must be changed when using change, but cannot be changed when using modify alter table user modify name char(50); alter table user change name u_name char(30); --Change table engine Alter table user engine = 'engine'; --For other operations, please refer to the official documents
Data query language
--In database applications, the most frequent operation is query --The basic format of single table query. Select which fields, from which table, and where filter criteria select ... from ... where ...; --Example: view the names of all users older than 20 select name from user where age > 20; --Multiple filter conditions can be connected by and and or. Pay attention to the priority of condition combination, and use () to control their combination. --Nested queries. The result of the previous single table query is also a table, which can be used as the queried table, thus forming a nested query. select ... from (select ... from ... where ...) where ...;
Statistics, grouping and sorting
Statistics:Many times, we need to further process the data to find out the problemsMaximum、minimum value、the sum、average valueandcount, use the following function
select max(age),min(age),sum(age),avg(age),count(age) from user; --The statistical function will count multiple rows of data into one row of data. For example, 100 ages will be counted into one data with the max function, which is the largest data in 100 data.
grouping:In addition to the simple usage above — changing all data statistical operations into a row of data, statistical functions are usually used with grouping statements.
For example, we want to count the average age of men and women respectively.
select sex,avg(age) from user group by sex; --The semantics of group by sex is to group according to different values of sex. Sex has only two values, 'male' and 'female', and is divided into two groups accordingly. The scope of the statistical function is within groups, and there is no influence between groups.
Sort:When we encounter a need to query transcripts, usually the result we want is a result table in descending order from high to low.
select name,age from user order by age desc; --Where desc represents descending order. ASC can be arranged in ascending order. The default value is ASC.
When the query result has many rows, we can choose to view one of them
select name,age from user limit 3;-- Just look at three select name,age from user limit 4,3;-- Skip the first 4 and view 3
- Install MySQL
- Open MySQL
- View & operate MySQL
Be familiar with the following keywords
group by ,
order by ,
insert into ,
alter table ,
What do these codes mean
show databases; use ...; select ... from ... where ...; select max(...),avg(...),... from ... where ... group by ...; select ... from ... group by ... desc; select ... from ... limit ...; create database ... default charset=...; create table ...( ... ..., ... ..., ... ... )engine=innodb default charset=utf8; insert into ... values(...),(...),(...); delete from ... where ...; update ... set ... where ...;