Database-Basics (Database operations, table Operations)--Add advanced queries

Notes

Lamp:linx (operating system)
A (Apache)--Web application
M (Mysql): Small size, simple application
P (PHP)

The first step: setting up the Web environment--a\m\p

WAMP: Build the environment with WAMP

DW: Better display

Basic operations of the database:

Database--Table structure--Fields (columns)

Each row of data becomes a single piece of data (record)

Features: relational database, with strict specifications

1. Must have a primary key: A field that uniquely identifies a single piece of data

2

T-sql: Common database operation statements

Self-growing column code (primary key column); The last field of the Connection key table is not added, #注释

To create a table:
CREATE TABLE Family
(
Code varchar (primary key),
Name varchar (NOT NULL),
Sex bit

);
CREATE TABLE Nation
(
Code varchar (primary key),
Name varchar (50)
);

CREATE TABLE Info
(
Code int Auto_increment primary KEY,
Name varchar (50),
Sex bit,
Birthday date,
Height float,
Nation varchar (references) Nation (Code)//generally not written
)
Primary key: Primary key
NOT null: Non-empty
Auto_increment: Self-growing columns, shaping, auto-grow without adding
References: referencing foreign key relationships

Delete a table
drop table Family

Creating databases: Create DATABASE MyDB

Add a column to the database: ALTER TABLE t1 add column addr varchar (a) not null;
This statement adds a column of addr to the existing table T1, which is the last column in the table.

The above command means adding addr to the column after User1:
ALTER TABLE t1 add column addr varchar (NO) null after User1;

Delete database: Drop DB mydb

CRUD: Adding and deleting changes
c:create Add data (must write, can be empty)
INSERT into nation values (' n001 ', ' Han ')-General
INSERT into Name values (' ', ') self-growing columns are not required, empty, without filling in the blanks
insert INTO Nation (Code, Name) VALUES (' n002 ', ' Huizu ')-add data for a specific column to the table

r:read Querying Data
I. Querying all data
SELECT * from info check all
Select Name from info check specific column
second, according to the conditions of inquiry
SELECT * from info where code= ' p001 '
SELECT * from info where code= ' p001 ' and nation= ' n003 ' multi-condition and relational query
SELECT * from info where name= ' hu June ' or nation= ' n001 ' multi-condition or relational query
SELECT * from car where price>=50 and price<=60 range query
SELECT * from car where price between 60 and range query common
third, fuzzy query
SELECT * from car where name '% audi% ' wildcard symbol for any number of characters
SELECT * from car where name like ' _ Horse% ' _ wildcard symbol for any one character
Iv. Sort
SELECT * FROM Car ORDER BY Price (ASC) in ascending order of prices
SELECT * FROM car ORDER BY price desc in descending order of prices
SELECT * FROM Car ORDER by price Desc,oil desc sorted in two columns-first in descending order of prices and then in descending order of fuel consumption
v. Statistical functions (Aggregation functions)
Select COUNT (code) from car query table how many data
select Max from car fetch price Max
Select min (price) from car fetch minimum value
Select SUM (Price) The sum of prices from car fetch
Select avg from car fetch price average
Six, group query
Select brand from Car GROUP by Brand has count (*) >2 query for all series with a quantity greater than 2
Seven, paging query
select * FROM Car limit 5, 5 skip a few data to fetch several data
eight, go to re-query
SELECT distinct brand from car can go to which column of repetition

U:update Modifying data
Update Nation set Name= ' Huizu '-all modifications
Update Nation set name= ' Huizu ' where code= ' n002 '-Modify a piece of data
Update Nation set name= ' Huizu ' where code= ' n003 ' and code= ' n004 '-Modify a piece of data

D:delete Deleting data
Delete from nation-deletes data from the entire table
Delete from nation where code= ' n001 '-delete a piece of data

Advanced Query

one, multi-table connection
1.select * from info,nation where Info.nation=nation.code
Select Info.code,info.name,nation.name from info,nation where Info.nation=nation.code
2. Join Connection
SELECT * from Info join Nation on Info.nation=nation.code
two, Multi-table Union (extension of the column)
SELECT * from Info where code= ' P001 '
Union
SELECT * from Info where nation= ' n001 '
Third, sub-query (unrelated subquery)
SELECT * from Info where Nation (! ) = (select Code from Nation where Name= ' Han ')
SELECT * from Info where Nation (not) in (select Code from Nation where Name= ' han ' or name= ' Miao ')
Sub-query (related subquery)
SELECT * from Car a where a.oil< (select Avg. from car b where B.brand=a.brand)
first query inner query becomes select AVG (oil) from car where brand= ' b001 '

Database-Basics (Database operations, table Operations)--Add advanced queries