Shulou(Shulou.com)06/01 Report--

This article mainly introduces the basic handouts for the introduction to MySQL, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

1. Database introduction 1.1, introduction

Save the data to [memory]:

Advantages: read and write very fast

Cons: data loss due to program closure

Save the data to [File]:

Advantages: data can be saved permanently

Disadvantages: 1) frequent IO operations, inefficient! 2) data management is not convenient. For example, querying a certain data needs to be read out and then matched.

Save the data to [Database Software]:

Advantages: 1) the data is preserved forever. 2) data management is very convenient. (for example, query is very fast and convenient)

Data can be said to be the soul of the enterprise!

1.2. What is database software

Database, commonly known as the warehouse of data. Software (or program) that facilitates the management of data.

1.3, database software on the market

Oracle, a product of Oracle. At present, the most popular and widely used database software. It is very compatible with java language. Suitable for medium, large and medium-sized applications.

SQL Server: it's a product of Microsoft. Window platform is widely used. It is very compatible with the cantilever.net platform.

DB2: a product of IBM. IBM CVM-- > UNIX-- > DB2- > Websphere

MySQL: products of open source organizations. Oracle products. Free! Very good compatibility with java language! Suitable for small and medium-sized enterprises, medium and small applications

The above are all relational databases.

MongoDB: non-relational database.

1.4.getting started with MySQL

1) download it from mysql's official website.

2) install mysql software

3) use

Verify whether it is successful: open cmd-> enter mysql-u root-p enter-> enter password

1.5.The database storage structure of MySQL

First [database], then [table], then [data]

2. Database management

Query all databases: show databases

Create database: create database database name default character set utf8

Create database testdb default character set utf8

View the default character set of the database: show create database database name

Create database testdb default character set utf8

Modify database: alter database database name default character set character encoding

Alter database testdb default character set gbk

Delete database: drop database database name

Drop database testdb

Use database: use database name

Use testdb

3. Table management

Before managing tables, be sure to use a database: the use database name

View all tables: show tables

Create a table:

CREATE TABLE T_Students (

Id INT PRIMARY KEY AUTO_INCREMENT

NAME VARCHAR (20) NOT NULL

Gender BIT NOT NULL

)

View table structure: desc table name

View the SQL statement that created the table: show create table table name

Delete table: drop table table name

Modify the table:

Add field: alter table table name add column field name field type

Delete field: alter table table name drop column field name

Modify field type: alter table table name modify column field name field type

Modify field name: alter table table name change column old field name new field name field type

Modify table name: alter table table name rename to new table name

4. Add, delete and modify data

Increase data

Insert all fields. Must be inserted sequentially. Note that you cannot have fewer or more field values.

INSERT INTO student VALUES (1) 'Zhang San', 'male', 20)

Insert part of the field

INSERT INTO student (id,NAME) VALUES (2m'Li Si')

Modify data

Modify all data (recommended less)

UPDATE student SET gender=' girl'

Conditional modifications (recommended)

UPDATE student SET gender=' male 'WHERE id=1

Modify multiple fields. Note: SET field name = value, field name = value,....

UPDATE student SET gender=' male', age=30 WHERE id=2

Delete data

Delete all data (recommended to use less)

DELETE FROM student

Conditional deletion (recommended)

DELETE FROM student WHERE id=2

Another way

TRUNCATE TABLE student

Comparison between delete from and truncate table

Delete from: the whole table can be deleted, 1) it can be deleted conditionally; 2) only the data of the table can be deleted, not the constraints of the table; 3) the data deleted using delete from can be rolled back (transaction)

Truncate table: the whole table can be deleted 1) cannot be deleted conditionally 2) the data of the table can be deleted or the constraints of the table can be deleted 3) the data deleted using truncate table cannot be rolled back

5. Query data (key points)

5.1. Query all columns

SELECT * FROM student

5.2. Query the specified column

SELECT id,name,gender FROM student

5.3. Add constant columns when querying

SELECT id,name,gender,age,'hello world' AS 'msg' FROM student

5.4. Juxtaposition when querying

SELECT id, (math+english) FROM scores;-- Note: merge columns can only merge fields of numeric type

5.5. Remove duplicate records when querying

SELECT DISTINCT gender FROM student

5.6. Conditional query

Logical condition: and (and) or (or)

SELECT * FROM student WHERE id=2 AND NAME=' Li Si';-- intersection

SELECT * FROM student WHERE id=2 OR NAME=' Zhang San';-- Union

Comparison conditions: >

= and 70

Null condition (null empty string): is null / is not null / =''/''

SELECT * FROM student WHERE address IS NULL OR address='';-(including null and empty string)

Fuzzy condition: like

The following replacement marks are usually used:% for any character and _ for one character.

SELECT * FROM student WHERE name LIKE'Li%'

SELECT * FROM student WHERE name LIKE'Li _'

5.7. Aggregate query

SELECT SUM (math) AS 'math Total score' FROM scores

Average score of SELECT AVG (math) AS 'math' FROM scores

SELECT MAX (math) AS 'highest score' FROM scores

SELECT MIN (math) AS 'lowest score' FROM scores

SELECT COUNT (*) FROM student

SELECT COUNT (id) FROM student

-- Note: the number counted by the count () function does not include null data. Therefore, for the number of records that use the count statistics, use fields that do not contain null values.

5.8, paging query

Start line of limit, query a few lines

The starting line starts at 0

Paging: how many pages are displayed on the current page

Paging query the sql of the data on the current page:

SELECT * FROM student LIMIT (current page-1) * how many entries are displayed per page and how many entries are displayed per page

5.9. Query sorting

Syntax: order by field asc/desc

Asc: order, positive order. Value: increment, alphabet: natural order (amurz)

Desc: reverse order, reverse order. Values: decreasing, letters: natural reverse order (zmura)

5.10. Group query

SELECT gender,COUNT (*) FROM student GROUP BY gender

5.11. Filter after grouping query

Note: the condition before grouping uses the where keyword, and the condition before grouping uses the having keyword.

SELECT gender,COUNT (*) FROM student WHERE GROUP BY gender HAVING COUNT (*) 2

After reading the above basic handouts on the introduction to MySQL, I hope it can bring some help to you in practice. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.