Content review of MySql database learning (1)

-Database creation: 
      Name of create database character set character set collate collation

--Database deletion: 
      drop database database name

-- Amendment: 
      alter database character set character set (utf8)

- enquiry: 
      show databases;

	  show create database name

	  select database();

--Switch database:
	Name of use database
	
--Table structure operation:
		Establish:  
		create table table name(	
				Column name column type column constraint,
				Column name column type column constraint
			);

		--Column type: char / varchar   

		--Column constraints: 
				Primary key primary key constraint
				Unique: unique constraints
				not null non empty constraint

		--Automatic growth:
		  auto_increment
	    - delete:  
	     drop table table name
	    -- Amendment:   
	    alter table name (add, modify, change, drop)

		rename table old table name to new table name

		alter table table name character set character set

	   --Query table structure:

			show tables; query all tables

			show create table name: table creation statement, table definition

			desc table name: table structure


	    --Operation of data in table

		--Insert: 
		      insert into table name (column name, column name) values (value 1, value 2);

		- delete:        
		delete from table name [where condition]

		-- Amendment:       
		update table name set column name = 'value', column name = 'value' [where condition];

		- enquiry:    
		select [distinct] * [column name 1, column name 2] from table name [where condition]

		as keyword: alias

	    After where condition:
		
			--Relational operators: > = < ==========

			--Judge whether a column is empty: is null is not null

			in is within a certain range

			between...and

			--Logical operators: and or not

			--Fuzzy query: like    
					_: represents a single character
					%: represents more than one character

			- grouping: 
			group by 
			--Filter conditions after grouping: 
			 having

			--Aggregate function: 
			sum()  ,avg() , count()  ,max(), min()

			Sorting: 
			Order by (ASC ascending, desc descending)

SQL will create multi table and multi table relationships
Is there a relationship between the classification table and the commodity table? If so, how to express this relationship in the database?

create table category(
  cid int primary key auto_increment,
  cname varchar(10),
  cdesc varchar(31)
);

insert into category values(null,'Mobile phone digital','kk electronic product, kk production');
insert into category values(null,'Shoes schoolbag','kk Leather shoes factory, kk production');
insert into category values(null,'Cigarette drinks','kk Wine, kk production');
insert into category values(null,'Snack biscuits','kk Whoahaha, kk production');
insert into category values(null,'Spicy spicy bar','kk Weilong, kk production');

--All goods
1.commodity ID
2.Trade name
3.prices for goods
4.Date of manufacture
5.Commodity classification ID

//Commodity and commodity classification: relationship
create table product(
	pid int primary key auto_increment
	pname varchar(10),
	price double,
	pdate timestamp,
	cno int
);

insert into product values(null,'Millet 9',998,null,1);
insert into product values(null,'1+',2888,null,1);
insert into product values(null,'Adidas',99,null,2);
insert into product values(null,'Old village head',88,null,3);
insert into product values(null,'Strong wine',35,null,3);
insert into product values(null,'Jenny Bakery',1,null,4);
insert into product values(null,'Weilong spicy strips',1,null,5);
insert into product values(null,'Spicy strips',1,null,5);

//Inserting data will fail because there is a primary foreign key relationship
insert into product values(null,'Baked gluten',1,null,12);

How to maintain the relationship between multiple tables
Foreign key constraint: foreign key

• Add a foreign key constraint to this cno in product
  alter table product add foreign key(cno) references category(cid);

• From the classification table, delete the information classified as 5.

  • delete from category where cid =5; / / deletion failed
  • First of all, you need to go to the product table and delete all classified ID5 products because there is a master foreign key relationship.

• Database building principles:

  • Usually, a project / application builds a database

• The principle of building tables between multiple tables

  • One to many: goods and classification

    • Table creation principle: add a foreign key to one side, and point to the primary key of one side

  • Many to many: teachers and students, students and courses
    Table creation principle: create an intermediate table, split the many to many relationship into one to many relationship. The intermediate table must have at least two foreign keys, pointing to the original two tables respectively.

  • One on one: class and monitor, citizen and ID card,

    • Table building principle:
      • Treat the one-to-one situation as one to many situations. Add a foreign key to any table. The foreign key must be unique and point to another table.
      • Merge two tables directly into one table
      • Establish a connection between the primary keys of the two tables so that the primary keys in the two tables are equal
    • Practical use: not much is used.
      • Dating website:
        • Personal information: name, gender, age, height, weight, circumference, hobbies, (annual income, specialty, education background, occupation, spouse selection objectives, requirements)
        • Table splitting operation: reduce the overstaffing of personal common information and uncommon information.

Analysis of database table of online shopping mall

User table (user ID, user name, password, mobile phone)

create table user(
	uid int primary key auto_increment,
  	username varchar(31),
  	password varchar(31),
  	phone  varchar(11)
);

insert into user values(1,'xiekk','123','13888888888');

Order form (order number, total price, order time, address, ID of foreign key user)

  create table orders(
  	   oid int primary key auto_increment,
       sum int not null,
       otime timestamp,
       address varchar(100),
       uno int,--Add foreign key and user Table correlation
      foreign key(uno) references user(uid)
  );
  insert into orders values(1,100,null,'k city z 132 towns',1);
  insert into orders values(2,200,null,'X city r City 1075',1);

Commodity list (commodity ID, commodity name, commodity price, foreign key cno)

create table product(
	pid int primary key auto_increment,
  	pname varchar(10),
  	price double,
  	cno int,
  	foreign key(cno) references category(cid)
);

insert into product values(null,'Millet 9',998,null,1);
insert into product values(null,'1+',2888,null,1);
insert into product values(null,'Adidas',99,null,2);
insert into product values(null,'Old village head',88,null,3);
insert into product values(null,'Strong wine',35,null,3);
insert into product values(null,'Jenny Bakery',1,null,4);
insert into product values(null,'Weilong spicy strips',1,null,5);
insert into product values(null,'Spicy strips',1,null,5);

Order item: intermediate table (order ID, commodity ID, commodity quantity, total price of order item)

create table orderitem(
	ono int,
  	pno int,
  	foreign key(ono) references orders(oid),
  	foreign key(pno) references product(pid),
  	ocount int,
  	subsum double
);

--Add $100 to order 1
insert into orderitem values(1,7,50,50);
insert into orderitem values(1,8,50,50);


--Add 200 yuan to order 2 ()
insert into orderitem values(2,3,2,198);
insert into orderitem values(2,7,2,2);

Commodity classification table (classification ID, classification name, classification description)

create table category(
  cid int primary key auto_increment,
  cname varchar(10),
  cdesc varchar(31)
);

insert into category values(null,'Mobile phone digital','kk electronic product, kk production');
insert into category values(null,'Shoes schoolbag','kk Leather shoes factory, kk production');
insert into category values(null,'Cigarette drinks','kk Wine, kk production');
insert into category values(null,'Snack biscuits','kk Whoahaha, kk production');
insert into category values(null,'Spicy spicy bar','kk Weilong, kk production');

How to maintain the relationship between multiple tables

• : foreign key constraint: foreign key
• Add a foreign key: alter table product add foreign key (CNO) references category (CID);

  • 	foreign key(cno) references category(cid)
    

  • When deleting, first delete all the data associated with the foreign key, and then delete the classified data.
• Table building principle:
  • One to many:
    • Table building principle: add a foreign key to one side and point to one side
  • Many to many:
    • Table building principle: turn many to many into one to many relationship, and create an intermediate table
  • One to one: not commonly used, table splitting operation
    • Table building principle: merge two tables into one table
      • Establish a relationship between the primary keys of two tables
      • Treat the one-to-one relationship as one to many

Primary key constraint: cannot be empty by default, unique

• Foreign keys are all primary keys that point to another table
• A primary key can only have one table

Unique constraint: the content of the column face must be unique, cannot be duplicate, and is empty.

• Unique constraint cannot be used as foreign key of other tables
• There can be multiple unique constraints

One to many: table building principle:
Add a foreign key to the multiple side, pointing to the one side

Many to many: table building principle:
1. Split into one to many
2. Create an intermediate table with at least two foreign keys pointing to the original table

One to one: principle of table building:
Merge a table, establish a relationship with the primary key, and treat it as a one to many situation

Using mall table to complete multi table query of commodity information

Demand analysis:
In our mall case, my order contains a lot of information. To open my order, you need to query the table.
multi-table query

• Cross connect query Cartesian product

SELECT * FROM product;
SELECT * FROM category;

Cartesian product. It's the product of two tables. The result is meaningless.
SELECT * FROM product,category;

Filter out meaningful data
SELECT * FROM product,category WHERE cno=cid;

SELECT * FROM product AS p,category AS c WHERE p.cno=c.cid;
SELECT * FROM product p,category c WHERE p.cno=c.cid;

-Data preparation
INSERT INTO product VALUES(NULL,'Nike Emperor',10,NULL);

• Internal connection query

 -Implicit link
  SELECT * FROM product p,category c WHERE p.cno=c.cid;
 -Show inner links
  SELECT * FROM product p INNER JOIN category c ON p.cno=c.cid;	
- distinction:
	Implicit link: filter WHERE condition based on the result of query
	Display inner link: query results with conditions, high efficiency

• Left outer join

If there is no corresponding data in the right table, use NULL instead
  SELECT * FROM product p LEFT OUTER JOIN category c ON p.cno=c.cid;

Preparation
  INSERT INTO category VALUES(100, 'computer office', 'computer fork difference');

• Right outer join

- Right outer join: All data in the right table will be queried, If there is no corresponding data in the left table, useNULLreplace
 
  SELECT * FROM product p RIGHT OUTER JOIN category c ON p.cno=c.cid;

Paging query

• Data per Page3
• Start index from 0
• First pages: 0
• Second pages: 3
  Start index: index represents the number of pages displayed starting from 1
  3 data per page
  startIndex = (index-1)*3

The first parameter is the index

Number of the second parameter display

select * from product limit 0,3;

select * from product limit 3,3;

Subquery (what you know, very important) (nesting of sql statements)

Find out (commodity name, commodity classification name) information

-Query out(Trade name,Commodity classification name)information
   -Left join
   SELECT p.pname,c.cname FROM product p LEFT OUTER JOIN category c ON p.cno = c.cid;
   
   -Subquery
   SELECT pname ,(SELECT cname FROM category c WHERE  p.cno=c.cid ) AS Commodity classification name  FROM product p;

Query all products with classification name as mobile digital

-Query all products with classification name as mobile digital
   1.Search for mobile digital ID
   SELECT cid FROM category WHERE cname='Mobile phone digital';
   2.Obtain ID by1Result
   SELECT * FROM product WHERE cno = (SELECT cid FROM category WHERE cname='Mobile phone digital');