MySql database basic notes (1), mysql database basic notes

MySql database basic notes (1), mysql database basic notes

I. Concepts of tables and databases

What forms does a database manage data?

Tables in life ----> tables
The traditional databases use multiple rows and multiple columns. The header is declared. After a table is created, the rest is to add data to the table.

Store multiple tables in files ---> database
The student information table, the student information table, and the recorded table are all information about this class. Put these tables in a folder.> this folder, the document is the library.
Mysql server can help us manage multiple databases

Xampp \ mysql \ data
Put the data in the database in this file. MYD is the data file

Archive administrator ---> server
Databases store our data as files and manage the data in different categories.

View the database show databases;
Select the database use test;
View the table show tables in the database;
Select * from cat;

Ii. Understanding SQL

SQL statements can be roughly divided into three types:

DML is Data Manipulation Language statements. Some examples: Data operation Language, number of processes in SQL
Data and other operations are collectively referred to as adding, deleting, modifying, and querying data manipulation languages.
For us, DML is our focus

DDL is Data Definition Language statements. Some examples: Data Definition Language for Definition and Management
Language of all objects in the SQL database (builder's perspective, table creation, database creation, view creation, etc., 15%)
Generally, a database is rarely changed after it is designed.

DCL is Data Control Language statements. Some examples: Data Control Language, used to grant or revoke access
Ask a database privilege, control the time and Effect of Database Transaction operations, and monitor the database.
(Administrator: DBA [Database Administrator], 5%)
Does this user have permission to create tables?

3. add, delete, modify, and query operations

1. insert operation

DML, add, delete, modify, and query-> Add

User table, uid name age three columns

(1). Add all columns and add all columns

insert into user (uid,name,age) values (1,'lisi',23);

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

insert into user (uid,name) values (1,'lucy');

(3). Primary Key auto-incrementing insert primary key auto-incrementing insert desc user;

insert into user (name) values ('yumi');

(4) Insert all columns. Insert all columns.

Insert into user values ('kimi', 25); // error: column count unmatched value count insert into user values (3, 'kimi', 25 );

A number can be enclosed in single quotes and converted to an int.
However, the string must be enclosed in single quotes. If it is not added, it is understood as a column name or variable and an error is returned.

insert into user values ('4','zhangsan','25');insert into user values (5,zhangsan,25);

(5). Add multiple rows of data at a time and add multiple rows of data at a time

insert into user values (5,'test1',44),(6,'test2',23),(7,'test3',18);

Note:
Column and value, strictly corresponding (id auto-incrementing column must also correspond)
Notes on numbers and strings
Numbers do not have to be enclosed in single quotes, and strings must be enclosed in single quotes.
Add multiple rows of data once, separated by commas

2. update operation

Add, delete, modify, query, and modify
Which table is changed?
Which columns of Values do you need to change?
Why are values changed separately?
In which lines does it take effect?

(1). update to change Columns

Update user set age = 99 where name = 'umi'; update table name set column 1 = new value 1, column 2 = new value 2 where expr

update user set age=23,name='nobody' where uid=2;

(2) update all rows
Note: the consequences of not adding a where clause

update usersetname='mingming',age=55;

Data is very valuable
If we do not add the where condition for update, the consequences will be terrible.
Mysql allows you to set the new mode. In the new mode, the where condition is not added for deletion and modification, and execution is denied.

3. delete operation

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

For traditional databases, one row is its atomic unit.
Add is a row, delete is also a row
Which table of data do you want to delete?
What lines do you want to delete?

Delete from table name where expr

SQL :*

delete from user where uid=7;

Note that the where condition is not added.

delete from user;

4. select Operation

Add, delete, modify, and query
Which table is queried?
Which columns do you Want to query?
Which Rows Do You Want To select?

Select column 1, column 2,... column n
From table name
Where expr

(1). query all columns in all rows of the table.

select * from user;

It is rarely written in this way during development, because there are thousands of data in the table, so queries will increase the burden on the database;
You can query the data that we need.
(2) query a row

select * from user where uid=2;

(3). query multiple rows

select * from user where uid>=2;

(4) query certain columns of certain rows. * indicates all columns.

select uid,name from user where uid>=2;select name from user where uid=2;

Dml accounts for 80% of SQL and 80% of dml;
What we have learned is the most basic query statement;
To write complex queries with high difficulty, we also need to discuss the query model issues;
Check whether we can really understand select and write powerful query statements,
It is very important to rely on the select query model.

(5) In the goods table, query how much our products are cheaper than the market price

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

(6) concepts of projection

select name,age from user;

The user table has three columns. We only retrieve two columns (some columns), which are calledProjection operation
Like a flashlight, only two columns are shown, and the shadow is displayed.
Goods Table query market_price-shop_price
Two columns for calculation, calledGeneralized projection

Iv. Exercises
1. Integer

select num/10 from mian;

2. mysql manual functions
Floor rounded down

select floor(num/10)*10 from mian;

3. Change the mian table

update mian set num=floor(num/10)*10 where num between 20 and 39;select * from mian;

Tip: Think of columns as variables, participate in operations, and even call functions for processing.
Substring (), concat ()
4. substring truncation string

Select goods_id, substring (goods_name, 4) from goods where goods_name like 'nokia % ';

5. concat concatenated strings

Select goods_id, concat ('htc ', substring (goods_name, 4) from goods where goods_name like 'nokia % ';

6. Change the table
In order not to break the data of the bad table, it will not be modified here

Update goods set goods_name = concat ('htc ', substring (goods_name, 4) where goods_name like 'nokia % ';

Boldly regard columns as variables and participate in Calculation

Note: This note is for learning"MySQL enhanced _ Boolean Education"