Error message here!

Hide Error message here!


Error message here!


Hide Error message here!


Error message here!



Tsinghua senior students stayed up for 20 days to sort out the basic chapter of "database mysql" [Xiaobai must see!]

Programmer two black 2022-01-15 04:45:16 阅读数:9 评论数:0 点赞数:0 收藏数:0

  • I'm erhei , A tester who's going bald

  • Welcome to thumb up Collection Leaving a message. Please correct any mistakes !

  • I collected some software testing data , Follow my official account : Programmer two black , Free collection !

  • The most difficult time , That's when we're not far from success !

Write at the top

You may know that database is a very important technology . Database is also required for software testing . Of course , The level of the database also directly determines the upper limit of our salary . therefore , I decided to systematically organize a set of MySQL The article . I hope I can sum up and sort it out at the same time , It can also help everyone . Here's the picture , yes MySQL The overall content outline of the series .

One 、 Why study databases

Usually, most of our data is stored in Excel In the table , Or have learned Python, Data can be stored in a list 、 aggregate 、 In the dictionary . Then they have many defects , For example, data is easy to lose ; When the amount of data is large , Difficult to find . that , Now We can use the database . Database is software specially used to manage data . It has two advantages :① Data persistence ② Complete and unified data management , Easy to query .

Two 、 Database related concepts

1、 DB
database (Database), A container for holding a set of organized data .

Database management system (Database Management System), Also known as database software ( product ), Used to manage DB Data in . such as :MySQL、Oracle、DB2、SqlServer etc. . Most companies use MySQL, Because it's open source , Free of charge .

Structured query language (Structure Query Language), Used for and DBMS The language of communication , A set of operation commands specially established for the database .
SQL advantage : ① Almost all DBMS All support SQL Language ② Easy to learn ③ Can be very complex and
Advanced database operation .

Take the library for example , The collaborative relationship between the three is shown in the figure below :

3、 ... and 、 Characteristics of data storage in database

1. Put the data in the table , Put the watch in the library ;
2. There can be multiple tables in a database , Every watch has a name , To identify yourself . Table names are unique ;
3. Table has some characteristics , These properties define how data is stored in tables , similar Python in “ class " The design of the ;
4. Table consists of columns , We also call it fields . All tables are made up of one or more columns , Each column is similar to Python Medium " attribute ”;
5. The data in the table is stored by row , Each line is similar to Python Medium " object ".

Four 、 Design specifications for tables and Libraries

1、 Naming specification

1) The library name can only use English letters , Numbers , Underline , And start with an English letter , Capital
2) Table names can only use letters 、 Numbers and underscores , All in lowercase
3) The naming is concise and clear ( The length cannot exceed 32 Characters )
4) Try not to use keywords for field names ( Such as type,order etc. )
5) Use as little storage space as possible to store the data of a field

2、 Grammatical norms

1) Case insensitive , But it is recommended that keywords be capitalized , Table name 、 Column names in lowercase
2) It's better to end each command with a semicolon
3) Every command is as needed , You can indent Or a new line
4) notes

  • Single-line comments :# Note text
  • Single-line comments :-- Note text
  • Multiline comment :/* Note text */

3、 Three paradigms of database

1) First normal form : Columns cannot be subdivided
2) Second normal form : There is a primary key , Non primary key fields depend on the primary key ( Properties are completely dependent on the primary key ).
3) Third normal form : The data table cannot have redundant fields ( Non primary key fields cannot depend on each other )

The three paradigms are the basic concepts of general database design , Less redundancy can be established 、 A well structured database . If there are special circumstances , Special treatment, of course , The most important thing in database design is to see the requirements and performance , demand > performance > Table structure . So we can't blindly pursue the paradigm to build a database .

4、SQL Language classification of

1)DQL(Data Query Language): Data query language select
2)DML(Data Manipulate Language): Data operation language insert 、update、delete
3).DDL(Data Define Languge): Data definition language create、drop、alter
4)TCL(Transaction Control Language): Transaction control language commit、rollback

5、 ... and 、 First time to know MySQL

1、MySQL Installation and introduction

MySQL Can be installed locally , It can also be installed on the server . For example, we need to build a test library locally , Or you want to operate the database on the server through the client , That can be operated locally ; If the development environment is on the ECS , Then you can operate directly on the server . that , How do the client and server work together , Here's the picture :

  • Local (Windows or Mac) | Cloud server (Linux)
    - Insert picture description here
  • notes : Here, the client uses Navicat, If you need to crack and install MySQL Documentation of services , Official account : Programmer two black , Add little sister wechat , Can get . It's not detailed here .

2、MySQL Start and stop of service

Start command :net start mysql
Stop the order :net stop mysql

  • notes : open cmd window , Be sure to use the administrator identity !

3、MySQL Service login and logout

Mode one : Terminal window login

#mysql 【-h Host name -P Port number 】-u user name -p password
Login command :mysql -hlocalhost -uroot -p
Exit command :exit or Ctrl + c
 Insert picture description here
notes :Windows The default password for installation is empty .

Mode two : client (Navicat) Sign in

 Insert picture description here
4、 MySQL Common commands for

  • show databases; View all databases

  • use Library name ; Open the specified The library of

  • show tables ; Show all tables in the library

  • show tables from Library name ; Show all tables in the specified library

  • create table Table name (

  • Field name Field type ,

  • Field name Field type

  • ); Create table

  • desc Table name ; View the structure of the specified table

  • select * from Table name ; Show all the data in the table

Check the version of the server

Mode one : Log in to mysql Server side
select version();
Mode two : No sign in to mysql Server side
mysql --version
mysql --V

good , That's all for today , The biggest insight is that no matter what language you learn , We must lay a good foundation , There will be fewer detours in the later stage . While realizing the function , Consider performance . This article is mainly for the first time MySQL, We learn MySQL Take the first step . Continue to study tomorrow SQL Of DOL、DML、DDL、DCL. Come on together !

Finally, for the convenience of everyone's study , I specially prepared a 13G Super practical dry goods learning resources , The content involved is very comprehensive .

Include , Software learning Roadmap ,50 Video of class for many days 、16 It's a real assault project ,80 More than one software for testing ,37 A test document ,70 A software testing related problem ,40 Test experience level article , Thousands of test questions to share , also 2021 Software testing interview Dictionary , There's also a selection of resumes for software testing jobs , I hope that's helpful ……

Follow my official account :【 Programmer two black 】 You can get this information !

If you don't want to experience it again, you can't find information when you study on your own , No one answers the question , If you insist on giving up after a few days , You can join our group :785128166 Let's talk about it , There are also various software testing materials and technical exchanges .

Recommended reading

High paid programmers can't escape 35 Year old … When ability is out of step with age , How can we help ourselves

College graduates start selling … Not reconciled to the status quo , The road to self-help in the transition test

From crown sales to unemployment , Finally, choose software testing , Looking back at this section of the road , I was lucky !

Copyright statement
In this paper,the author:[Programmer two black],Reprint please bring the original link, thank you