MySql database is a relational database
MySQL database is not case sensitive

Some grammars in this article, such as goudan, dachui and Cuihua, are not fixed grammars and can be named arbitrarily

data type
integer	int(m) m indicates the length
Long integer	bigint(m)
Floating point number	double(m,d) m represents the total length d and the decimal point length
Ultra high precision floating point number	decimal(m,d)
character string	char(m) maximum length 255
character string	varchar(m) 65535 max. text for more than 255
character string	text(m) max length 65535
specific date	date
Hour, minute and second	time
Mm / DD / yyyy H / min / S	datetime
time stamp	timestamp

increase

Add database

create database goudan; //goudan is the name of the database

create database if not exists goudan;//Creating a database does not show any errors

View all databases

show database;

Create and use gbk character set

create database dachui character set gbk;//Database using gbk character set

With verification rules

create database cuihua character set gbk
collate gbk_chinese_ci;

Add database object

Select database

use goudan;//Enter goudan database

View current database

select database();

Create table

You must select a database use dachui;

create table goudan(//Create goudan table
    sno char(6) not null,//Create sno column. The type is char, the length is 6, and the value is not empty (that is, it must exist)
    sgender char(2) default 'male',//The sgender column type is char and the length is 2. The default value is male
    sday datetime);//sday column type is datetime

Create unique value

create table test(
    id int primary key auto_increment,//The id column grows automatically. You can only have one primary key column
    name char(10));

Set primary key

create table goudan(
    sno char(6) primary key,//Set the sno column as the primary key
	sdata datetime);

Primary key cannot be duplicate
And cannot be empty

Create combined primary key

create table dachui(
	sno char(6),
    cno char(6),
    primary key(sno,cno)//How to set a combined primary key
);

When the primary key constraint definition is not in the same column, the values in a column can be repeated

Create foreign key constraint table

Some tables need a parent-child relationship
Constrain with foreign keys

create table goudan(
    sno char(6),
    cno char(6),
    score decimal(4,1),
    foreign key(sno) references dachui(sno)//Reference your sno column to dachui's sno column
);

Addition of table data

Add data insert to all lists

insert into goudan(sno,sname,sgender,sbirthday,sdept)//All fields must be listed in different order
values('18110','Sledgehammer','female','2002-02-14 00:00:00','d12001');//Value one-to-one correspondence

Check whether it is added successfully

select * from goudan;

Add by omitting field names

insert into goduan
values('18111','Pan an','male','2001-03-05 00:00:00','d12003');//Because there are no field names, they must be added in the defined order

Adds a value to the specified field of the data

insert into goudan(sno,sname,sgender)
values('18112','son of a bitch','male');//Value one-to-one correspondence
#It should be noted that when a field is not empty and no default value is set, you must add a value to this field when adding a value, otherwise an error will be reported

Adding multiple records to data

insert into goudan(sno,sname,sgender)
values('18113','High fire','male'),('18114','Zhang San','male'),('18115','Li Si','male');

lookup

Wrap output \ G
For example:

select * from Database name\G;

View a database

show create database goudan;

View data sheet

First, select the database use goudan;

show tables;

View data table structure

show create table Data table name;

Display data table in two-dimensional table structure

describe Data table name;//Short desc data table name

Data lookup select

Query all values

select * from goudan;//It is not recommended to consume too much performance when there is too much data

Queries all values of the specified field

selsct sname,sage from goudan;

Condition query

selsct sno,sname from goudan where sno='19102';//sno is the student number and sname is the name

in query multiple

select sno,sname from goudan where sno in('18102','18103','18105');

The query student number is not a specific value

select sno,sname from goudan where not in('18102','18103');//Just add not

Query values within the range

select sno,sname from goudan where sno between 'Value one' and 'Value two';
//The deconstruction of the query contains values one and two

The query student number is not the value of a specific interval

select sno,sname from goudan where sno not between 'Value one' and 'Value two';

Null query

select sno,sname,sgender from goudan where sgender is null;
//Query whether sgender is null

Non null query

select sno,sname,sgender from goudan where sgender is not null;
//sgender is not an empty value

No duplicate query record

select distinct sgender from goudan;//distinct keyword is not duplicate
//Query sgender query record is not duplicate

String fuzzy query

select sno,sname from goudan where sname like 'dog%';
//%Wildcards can match strings of any length, including empty strings, which can represent any character

_ Wildcards can match strings of any length, including empty strings, which can only represent one character

select * from goudan where sname like 'king_hammer';

and multi condition query

select * from goudan where sno='18101' and sname='son of a bitch';
//The query student number is 18101 and the name is the value of dog egg

or multi condition query

select * from goudan where sno<'18101' or sdept='d12001';
//Or means or

Relational operator	explain
=	be equal to
<>	Not equal to
!=	Not equal to
<	less than
<=	Less than or equal to
>	greater than
>=	Greater than or equal to

Subtotal query

Aggregate function

Function name	effect
count()	Returns the number of rows in a column
sum()	Returns the sum of a bad value
avg()	Returns the average value of a column
max()	Returns the maximum value of a column
min()	Returns the minimum value of a column

To uppercase, lowercase

upper('asd');//Capitalize
lower('NGMD')//a lowercase letter

Query the total number of records

select count(*) from goudan;//How many records are there in the goudan data table

Query average

select sname,avg(scorge) from goudan GROUP BY sname;
//Query the average value of scorge in the goudan table

Category query group by

sno values of all students whose mean score in goudan table is greater than 80

select sno,avg(scorge) from goudan group by sno HAVING avg(scorge)>80;

Write function after having

Query result sorting

asc ascending, default ascending
desc descending order

select sno,sname,sdept from goudan ORDER BY sno;
//Query the goudan table and sort by sno

Sorting with asc

select sno,sname,sdept from goudan order by sno asc;

Sort with desc

select sno,sname,sdept from goudan order by sno desc;

Gender in descending order and student number in ascending order

select sno,sname,sgender from goudan order by sgender desc, sno acs;
//Start sorting with sgender and sort with sno when sgender is the same

limit query

select sno,sname,sgender from goudan LIMIT 4;//Display the first four records in the table

Fifth to eighth

select sno,sname,sgender from goudan limit 4,4//Fifth to eighth

Joint query

The union does not contain duplicate rows
union all contains duplicate lines

select cno,cname,sgender,sdept from goudan where sgender='female' 
UNION 
select cno,cname,sgender,sdept from goudan where sdept='d12001';

Equivalent connection and inner connection:

1. Equivalent connection

   select * from A Table name A,B Table name B where A.field=B.field and condition;
   

2. Inner connection

   select * from A join B on A.field=B.field where condition
   

multi-table query

Cross query
There is a lot of duplicate data

select * from dachui CROSS JOIN goudan;//Show all data of dachui table and goudan table

Inner connection

select * from dachui INNER JOIN goudan
ON dachui.snum=goudan.snum;//The snum of the dachui table is equal to the data of goudan's snum
//Only data with two tables snum equal appears in the result

Table alias

select s.sno,sname from goudan s INNER JOIN dachui d ON s.sno=d.sno;
//Simplify the name of the data table, but once the alias is used, the original name cannot be used, otherwise an error will be reported

External connection

select sname,deptname from dachui d LEFT JOIN goudan g
ON d.deptname=g.deptname;//Left outer connection

Whoever is the standard is on the left
The right link query results include all records in the right table and the records matching the link conditions in the left table
If a record in the right table does not exist in the left table, null is displayed in the left table

select sname,deptname from goudan g RIGHT JOIN dachui d
ON g.deptname=d.deptname;

Self connection

Link yourself
Be sure to use the table alias

select work.name,work.num,student.name,student.num from goudan work INNER JOIN goudan student ON work.num=student.name;

Subquery

One query statement is nested inside another query statement

select * from goudan
where sdate>(select sdate from goudan where name='Sledgehammer');
//First query the date named sledgehammer, and then compare the size

Parameters of subquery

Operator	meaning
in	Equal to a value in the list
any	Compare with any value in the list
all	Compare with all values in the list

select name from goudan
where num in (select num from dachui where num='210203');

Related sub query

select * from goudan
where EXISTS (select num from goudan where year(date)=2000);//Check whether there are people born in 2000

The results of EXISTS query return true and false
If true, continue
If it is plus, the outer layer is not executed

Write location of subquery

1. Write after where or having

2. When creating a table

   create table emp_10 as (select * from emp where deptno=10);
   

3. Alias is required after from
   select ename from (select * from emp where deptno=20) newtable;

Order of keywords

select...from Table name where ...group by ... having ... order by ... limit...

modify

Change character set encoding to utf-8

alter database dachui character set utf8;

Add data table fields

First enter the database use goudan;

alter table Table name add Field name varchar(20);//Automatically add to last

Add to fixed location

alter table Table name add Field name char(20) after goudan;//Put it behind goudan
//Writing first is the first field

Modify field name

alter table Table name change Field name new name old rule;

You can change the location

alter table Table name change Field name new name old rule after Field name;//After the required fields

Modify field rules

alter table Table name modify Field name new type;

rename table

alter table Table name rename New name;

Set a column in the table as the primary key

alter table goudan//enter
add primary key(con);//Change con column to primary key

It will automatically check whether the conditions are met
Failed to set if the conditions are not met

Delete primary key constraint

alter table dachui
drop primary key;

Only the primary key constraint is deleted, but the field or field group is not deleted

Uniqueness constraint

Uniqueness in this table

alter table goudan
add unique(cname);//Set unique as the unique field in the table

That is, there can be no duplicate in the cname column

Modify create foreign key constraints

Add a foreign key constraint to the added table

alter table goudan
add constraint sc_con foreign key(cno) references dachui(cno);
//Create a foreign key named sc_con constrains its cno column by referring to the cno column in the dachui table

Delete foreign key constraint

Only the foreign key constraint is deleted
Structure was not deleted
To delete a foreign key, you only need to delete the foreign key name

alter table goudan drop foreign key sc_cno;//Delete the foreign key SC in the goudan table_ cno

Modification of data in table update

Modify all data

update dachui
set sname='son of a bitch';//Change the sname column to dog egg

Modify some data

update goudan
set sname='Sledgehammer'
where sno='18101';//where determines whether sno is equal to 18102

If multiple values in the table meet the judgment conditions, they will be changed together

delete

Delete database

drop database goudan;

Deleting a nonexistent database will result in an error

drop database if exists gouda;//if exists is added, the error message will not be displayed

Delete data table in database

drop table goudan;//goudan table

Delete fields in data table

alter table Table name drop Field name;

delete of data in table

Delete all data

delete from goudan//Delete all data in goudan

Delete some data

delete from goudan
where sno='18101' and con='110013';
//Delete the data with cno of 18101 and con of 110013 in the table

Indexes

Advantages of indexing

1. Ensure the uniqueness of data in the data table
2. Speed up data table retrieval
3. Connection between accelerometer and
4. Reduce retrieval time when using grouping and sorting clauses for data retrieval
5. The performance of the system is improved by using the optimization Concealer in the query process

Disadvantages of indexing

1. Creating an index takes time and increases as the amount of data increases
2. Physical space occupied by index creation
3. The index needs dynamic maintenance, which reduces the speed of data maintenance

Conditions suitable for index creation

1. Columns that often need to be searched
2. Above the column as the primary key
3. Often used as a connected column
4. It is often necessary to search the columns according to the range
5. Columns that often need to be sorted
6. Columns in the where clause are often used

Primary index

The index that must be created in the primary key field

General index

The index defined by key or index is the basic index type in MySQL

unique index

Index defined by unique. The value of the field where the index value is located must be unique

Full text index

Indexes defined by fulltext are created on fields of character or text type. Note that only MySAM storage engine supports them

single column

An index created on a single field in a table

Composite index

Create a composite index of multiple fields in a table
In a composite index, the index is used only when the query condition uses the first field of multiple fields
A table can have multiple singleton indexes, but this is not a composite index because a composite index is an index created by multiple fields

Create index

Create normal index

create index dachui ON goudan(name);
//A dachui index is created for the name field in the goudan data table
//The default length does not limit the ascending index

Create unique index

create UNIQUE index Index name ON Table name(Field name[(length)] [ASC|DESC]);

create unique index goudan ON dachui(sno DESC);
//A goudan unique index is created for the sno field in the dachui data table. The index is arranged in descending order

Create full-text index

create FULLTEXT index Index name ON Table name(Field name[(length)] [ASC|DESC]);

create fulltext index goudan2 ON dachui(address);
//Create a full-text index goudan2 from the address field of the dachui data table

Create multi column index

create index dachui on goudan(sno,sname);
//Create a multi column index named dachui in the goudan data table

alter table create index

alter table Table name add [UNIQUE|FULLTEXT] index Index name (Field name[(length)] [ACS|DESC]);

alter tablr can only create multiple indexes at a time. create index can only create one index at a time
alter tablr does not specify an index name. The default is the first column name. create index must specify an index name

View and delete of index

View of index files in database

show index from Database table name

Index deletion

Grammar one

ALTER TABLE Table name DROP INDEX Index name;

Grammar II

DROP INDEX Index name ON Table name;

view

A view is a virtual data table exported from one or more data tables. The structure and data of the table depend on the source table

effect
View the data in the source table; Add, delete, modify and query the source table data

advantage

1. Simplify query statements, data WYSIWYG
2. Data security, users can only query or modify the visible data
3. Logical independence can shield the impact of changes in real table structure

shortcoming

1. Performance: slow query speed
2. Table dependencies: to change the associated table, you must change the view

Create view

create VIEW View name[(List of field names)] AS SELECT sentence;//If you specify a specific database, you need to 
//Database name. View name

Create a view_ In the view of stu, the three columns ssno, sname and sData in goudan database are imported into the view and named student number, name and date of birth respectively

create view view_stu(Student number,full name,date of birth) as select sno,sname,sdata from goudan;

see

select * from View name;

View view definition

DESC View name;//More intuitive
//perhaps
show CREATE view View name;//More detailed

modify

ALTER VIEW View name[(Field name)] AS SELECT sentence;

update the view

//insert record
INSERT INTO View name(List of field names) VALUES(Value list);
//Delete record
DELETE from View name[WHERE Conditional expression];
//Update some data
UPDATE View name SET Field name 1=Expression 1[,Field name 2=Expression 2,...][WHERE Conditional expression]

Delete view

DROP VIEW [IF EXISTS] View name 1[,View name 2,...];//If IF EXISTS is not written, ensure that the view specified by the user for deletion already exists, otherwise an error will be reported