MySQL Database basic notes (i)

I. The concept of tables and libraries

Database management data, what form does it consist of?

Table----> table in life
Multiple rows and columns, the traditional database is like this; Declares a table header, a table is created, and all that is left is adding data to the table

Multiple tables are placed in the file---> Library
Student Information Form, score table, demerit list, are the information of this class, put these tables in a folder, this folder, the document is the library
MySQL server can help us manage multiple libraries

Xampp\mysql\data
The data in the database is placed in this file. MyD is the data file.

File Bag Administrator---> Servers server
Database is to store our data as a file, the management of a penny class

View Gallery show databases;
Select library use test;
View the table under the Library show tables;
Tabular select * from Cat;

Ii. Knowledge of SQL

SQL statements can be broadly divided into three types:

DML is Data manipulation Language statements. Some Examples: Data manipulation language, number of processing in SQL
Data manipulation language additions and deletions
For us, DML is our focus.

DDL is Data Definition Language statements. Some Examples: Data definition language for defining and managing
The language of all objects in the SQL database (builder's perspective, build table, build library, build view, etc, 15%)
In general, a database is rarely changed after the design is complete.

DCL is Data Control Language statements. Some Examples: A Data Control language used to grant or recycle visits
Ask the database for some kind of privilege, and control the time and effect of database manipulation transaction, monitor the database, etc.
(Administrator angle, dba[database administrator],5%)
Does this user have the right to build a table, etc.

Third, the increase and deletion check operation

1. Insert operation

DML, add/delete change

User table, UID name, age three columns

(1). Add all columns Add all columns

Insert  into User Values (1,'Lisi');

(2). There are multiple columns in a row, we can insert all the columns, or you can insert some columns
However: columns and values must correspond strictly

Insert  into User Values (1,'Lucy');

(3). Primary key self-increment insert primary key self-increment insert desc user;

Insert  into User Values ('Yumi');

(4). Insert shorthand for all columns to insert all columns

Insert  into User Values ('Kimi',+// Error: Column count mismatch value count insert into User Values (3,'Kimi',+);

The number can be enclosed in single quotes, and it will be converted to int to understand
However, the string must be enclosed in single quotation marks and will not be interpreted as a column name or a variable.

Insert  into User Values ('4','zhangsan',' ); Insert  into User Values (5, Zhangsan,+);

(5). Add more than one row of data at a time add more than one row of data at a time

 insert  into  user  values  (5 ,  " test1  " , 44 ), (6 ,  " test2   ' , 

Note:
Column and value, strict correspondence (ID self-increment column must also correspond)
Note points for numbers and strings
Numbers do not have to be single quotes, and strings must be enclosed in single quotes.
1 times add multiple rows of data, separated by commas

2. Update operation

Changes and additions
Which table is changed?
What columns of values do you need to change?
What value should I change to?
Which lines are in effect?

(1). Update Change Column

 update  user  set  age=  99  where  name=  Span style= "COLOR: #ff0000" > " yumi    ;  update  table name set   column 1  =   New value 1, column 2  =   new value 2  where  expr 

Update User Set Age=23°c, name='nobody'where uid=  2;

(2). Update All Rows
Note that the consequences of not adding where

Update User Set name = ' mingming ' , Age = ;

The data is very valuable.
If we update without the where condition, the consequences are horrible.
MySQL can set the novice mode, in novice mode, delete and change without the Where condition, it is refused to execute.

3. Delete operation

Delete and amend
Can I delete a column in a row? Delete ...
Actually, this is an update operation.

For a traditional database, a row is its atomic unit.
Add is a row, delete also if a row
Which table data do you want to delete?
Which lines do you want to delete?

Delete  from Table name where expr

SQL: *

Delete  from User where uid=7;

Be careful not to add a where condition

Delete  from user;

4. Select operation

Adding and deleting and checking
Which table data do you look for?
Which columns do you want to select for queries?
Which rows do you want to select?

Select column 1, column 2,... Column n
From table name
where expr

(1). Querying all rows of the table for all columns

Select *  from user;

This is rarely done in development because there are thousands of data in the table, so queries increase the burden on the database;
What data do we need and what data can we query?
(2). Query a row

Select *  from User where uid=2;

(3). Querying multiple rows

Select *  from User where uid>=2;

(4). Query a few columns of a few rows, * represents all columns

Select  from User where uid>=2; Select  from User where uid=2;

DML accounts for 80% of SQL, accounting for 80% of DML;
What we are learning is the most basic query statement;
To write complex queries that are difficult, we also need to focus on the query model.
See if we can really understand select, write powerful query statements,
It is very important to rely on the query model of SELECT.

(5). Goods table, check the store's goods than the market price of how much cheaper

Select goods_id,goods_name,shop_price,market_price,market_price- from goods;

(6). Concept of projection

Select  from user;

The user table has three columns, we only take out 2 columns (some columns), called projection operations
Just like a flashlight, just two columns, cast a shadow and show it.
Goods table query out Market_price-shop_price
Two columns for operations called generalized projections

Iv. Practice
1. Rounding

Select num/ten from Mian;

2.mysql Manual function
Floor Down rounding

Select  Floor (Num/ten) * Ten  from Mian;

3. Change the Mian table

Update set num=floor (num/ten)*where  Between; Select *  from Mian;

Tip: Boldly treat columns as variables, participate in operations, and even call functions to handle them.
SUBSTRING (), concat ()
4.substring Intercept string

Select goods_id,substring(goods_name,4fromwhereto like'  nokia%';

5.concat Stitching String

Select goods_id,concat ('HTC',substring(goods_name,4  fromwherelike' nokia%';

6. Change the table
In order not to destroy the table data, this is not modified here

Update set goods_name=concat ('HTC',substring(goods_name,4  wherelike' nokia%';

Bold to consider the column as a variable, participate in the operation

Note: The notes are recorded when learning "MySQL Reinforcement _ Boolean education "

MySQL Database basic notes (i)