Mysql study notes day1 -- learn all the knowledge points of mysql in three days

Mysql
Super full MySQL study notes, you can learn to use SQL statements skillfully in three days, full of dry goods, there are corresponding exercises at the end of the article, you can practice more to deepen your impression, I hope my article will be helpful to you

1. Server

1.What is a server?
	The computer used to serve shared resource files.
	Usually by agreement + IP + port access.

2.Classification of servers
	a)software
		Provides a runtime environment for the application. Expose some normative interfaces
		Used to implement or connect. Get the app's services.
	b)hardware
		A computer with great performance. There is no GUI and no mouse.

2. Data server

1.What is a database?
	Used to persist data generated by the application.
	A data warehouse that conducts, categorizes, manages, and manipulates data.
2.what is mysql？
	Mysql The predecessor was made by the Swedish Mysql AB A server developed by the company.
	then by Oracle Takeovers.
	Divided into community edition, and enterprise edition [paid].
	
	Features: Small size, fast running, free, a lightweight database server that supports transactions.
		  Alibaba uses Mysql.

3. Basic use

1.root account
	Super admin user.
	This user can mysql do any operation.
	For example: assigning accounts, assigning permissions....
2.connect Mysql
	a)Double-click the shortcut.
	b)connect using command mysql
		Open DOS window.
		mysql -account -p password -hIp
		E.g: mysql -uroot -p123456 -h127.0.0.1
		
3.View databases and tables
	Mysql The database server provides some existing databases by default.
	enter: show databases;
	
4.database
	A database is essentially a folder on disk.
	Table files are stored in this folder.

5.table file
	A table file is a file that stores data.
	The file starts with.frm is a suffix and can only be mysql Parse.

4. SQL

1.what is SQL statement?
	Structured Query Language.[Structured query statement]
	A language used for database operations.
	It is a structured operational language.
	The operations of adding, deleting, modifying and checking the data in the database.
2.SQL Classification of sentences
	DDL:data definition statement
		Data Definition Language
		Define the structure of the data. For example: library, table, column, constraint...
		Keywords:
			create  alter  drop   show
			  create modify delete    Inquire
	DML: data manipulation statement
		Data Manipulation Language
		Manipulate the data itself:
		Keywords:
			insert  delete  update
			 increase		 delete		change
	DQL: Data query statement
		Data Query Language
		Used to query and filter data.
		Keywords:
			select where....
	DCL: data control statement
		Data Control Language
		Used to control permissions, use root Accounts assign other accounts and set permissions for access.
		Keywords:
			grant	revoke
			set up		Recycle
		generally give DBA use. DBA is a database administrator
		Generally, large and medium-sized companies will set up DBA，dedicated to
		Permission to manage all company databases.

1.DDL

1.Create library
 grammar:
	method one
	create database gz2205 charset=utf8;
	Method 2
	create database if not exists gz2205 charset=utf8;

2.delete database
 grammar:
	method one
	drop database gz2205;
	Method 2
	drop database if exists gz2205;

3.View database
 grammar:
	show databases;
	
4.Select the database to use
 grammar:
	use Name database;

5.create table
 grammar:
	method one
	create table aaa(
    	k1 char(10),
        k2 int,
        k3 double,
        k4 datetime
    );
	Method 2
	create table if not exists aaa(
    	k1 char(10),
        k2 int,
        k3 double,
        k4 datetime
    );
What is a table?
	is a dataset with rows and columns.
	
6.delete table
 grammar:
	method one
	drop table aaa;
	Method 2
	drop table if exists aaa;

7.Check which tables the database has
 grammar:
	show tables;

8.View table structure
 grammar:
	desc Table Name;

9.Modify table structure

add a new column

grammar:
	alter table Table Name add column name data type;
example:
	alter table aaa add k5 text;

Modify the data type of a column

grammar:
	alter table Table Name modify column name data type;
example:
	alter table aaa modify k1 enum('1','2');

rename the column

grammar:
	alter table Table Name change old column name new column name data type
 example:
	alter table aaa change k1 dataStatus char(1);

Change the name of the table

grammar:
	alter table Table Name rename new table name;
example:
	alter table aaa rename bbb;

delete table

grammar:
	drop table Table Name;
example:
	drop table bbb;

2.DML

insert new data

grammar:
	Method 1 Select the specified row
	insert into Table Name(column name 1,column name 2....) values(value 1,value 2....);
	Method 2 full column insert
	insert into Table Name values(value 1,value 2....);
	way three	Insert multiple lines
	insert into Table Name(column name 1,column name 2....) values(value 1,value 2....),(value 1,value 2....)....;
	
example:
	method one	select the specified column
	insert into aaa(k1,k4) values("value 1",now());
	Method 2 full column insert
	insert into aaa values("value 1",2,3,now());
	way three	Insert multiple lines
	insert into aaa(k2,k3) values(10,11),(12,13),(14,15);

delete data

grammar:
	delete from Table Name where filter;

example:
	delete from aaa where k3 = 15;

Notice:
	Deleting data is a dangerous operation and needs to be filtered by adding conditions.

change the data

grammar:
	update Table Name set column name 1=value 1,column name 2=value 2... where filter;
example:
	update aaa set k1='value 1',k4='1998-07-17 01:02:00' where k3=15;

5. Data types

Mysql Divide data types into 3 categories:
1.Numerical
2.time date type
3.String type

1. Numerical type

Integer

tinyint		mini		a byte
smallint	small type		two bytes
mediumint	medium type		three bytes
int			Standard		four bytes		[remember]
bigint		large type		eight bytes		[remember]

create table my_number(
	k1 tinyint,
    k2 smallint,
    k3 mediumint,
    k4 int,
    k5 bigint
);

---normal insertion
insert into my_number values(127,32678,1000000,214765210,100000000000);

---mistake: Out of range value for column 'k1' at row 1   k1 Column value is out of range
insert into my_number values(128,32678,1000000,214765210,100000000000);

floating point

1.float			single precision		7 decimal range		four bytes
2.double		double precision		11 decimal range		eight bytes
3.decimal		Fixed accuracy Absolutely guaranteed	  eight bytes

create table my_decimal(
    f1 float(25,20),
    d1 double(25,20),
    d2 decimal(25,20)
);

--insert flost type of data
insert into my_decimal(f1) values(99999.9147482285978796512);
--insert double type of data
insert into my_decimal(d1) values(99999.9147482285978796512);
--insert decimal type of data
insert into my_decimal(d2) values(99999.9147482285978796512);

2. Date type

datetime	Year, month, day, hour, minute, second		The first year of the park begins		 lunar time
date		year month day
time		Minutes and seconds
year		years
timestamp	Year, month, day, hour, minute, second		Greenwich time starts	1970-01-01 08:00:00

create table my_date_time(
	d1 datetime,	
    d2 date,
    d3 time,
    d4 year,
    d5 timestamp
);
---normal insertion
insert into my_date_time values(now(),now(),now(),'1998',now());

---Abnormal Insertion: Because of the time out of Greenwich.
insert into my_date_time values('1949-10-01 10:00:00',now(),now(),'1998','1970-01-01 07:59:59');

---normal insertion
insert into my_date_time values('1949-10-01 10:00:00',now(),now(),'1998','1970-01-01 08:01:00');

3. String type

mysql Classify string types into 6 categories:
1.char
	Fixed length type:
		When allocating space, the allocated space is the specified length.
		Regardless of the value of the space, the occupied disk space is fixed.
        for example:
        	A Chinese occupies 8 bytes
        	char(20) occupies 40 bytes
2.varchar
	Variable length type:
		When allocating space, the allocated space is the specified length.
		But the space finally used depends on the length of the data inserted by the database.
		for example:
			a Chinese  		Occupies 8 bytes
			varchar(20)	   Occupies 8 bytes
3.text
	Text type:
		Generally, the number of characters exceeds 255, and it is generally used text type.
		
4.blob
	Binary text type:
		It has been rarely used, generally storing pictures and multimedia.
		A type that can store binary.
5.enum
	enum type:
		List one or more specific data.
		The user cannot insert more than the enumerated data.
	for example:
		sex enum('male','Female','unknown','keep secret');
6.set
	Collection type:
		Similar to enumeration.
		List some specified data, when the user inserts data
		The data in the collection cannot be violated.
	for example:
		hobbys set('fitness','money','Work','play games');	
	
All of the above types are string types.

create table my_string(
	s1 char(20),
    s2 varchar(20),
    s3 text,
    s4 blob,
    s5 enum('male','Female','unknown','keep secret'),
    s6 set('fitness','money','Work','play games')
);

---normal insertion
insert into my_string values('I am a Chinese',"I am a good person","news..",'write something','male','fitness,money,play games');

Solve the problem that Mysql cannot insert Chinese:

exist Mysql In the installation directory, find my.ini configuration file.
Revise my.ini file, if you do not have permission, copy it to the desktop for modification,
Then overwrite the modified file to Mysql under the installation directory.

Revise:
	57  default-character-set=GBK
	81	character-set-server=GBK

6. Query operation

1. Initialize data

Will init.sql The file is placed somewhere on the disk.

exist DOS Window access:
	source file address;

2. Introduction to table structure

emp table [employee table]

column name	type	describe
EMPNO	int(4)	staff code
ENAME	varchar(10)	employee's name
JOB	varchar(9)	position, job
MGR	int(4)	leader's number
HIREDATE	date	Entry Time
SAL	double	salary
COMM	int(7)	bonus
DEPTNO	int(2)	Department Number

dept [department table]

column name	type	describe
DEPTNO	int(2)	Department Number
DNAME	varchar(14)	Department name
LOC	varchar(13)	Department address

salgrade [salary grade table]

column name	type	describe
GRADE	int(11)	grade
LOSAL	int(11)	salary floor
HISAL	int(11)	salary cap

3. Basic query

query all data
```
select * from emp; 
```

Qualified query

Keywords:
	where	This keyword is used to filter query conditions.

Query the information of employees whose salary is greater than 1500 yuan
select * from emp where sal > 1500;

Arithmetic

+ - * /

query name is smith of employees' gross monthly income?
select sal + comm from emp where ename = 'smith';

exist Mysql , any value with null Participate in the operation to obtain null. 

Solve the process with null The value participates in the operation:
select sal + ifnull(comm,0) from emp where ename = 'smith';

ifnull(k1,v1) if k1 is empty is replaced with v1;

operator
```
> < >= <=  =   

!=   <>
```

keywords

is null

--Look up information on employees without bonuses.
select * from emp where comm is null;

SQL Execution of the statement:
1.from		Determine the location of the data source.
2.where		Filter the data loaded into memory.
3.select	Will from The data pointing to the file is loaded into memory.

is not null

--Look up information on employees with bonuses.
select * from emp where comm is not null;

and

--Check salary in 1500 - 3000 information between employees
select * from emp where sal >=1500 and sal <= 3000;

--Inquiry engaged in sales[ SALESMAN]Work, or employees with a salary greater than or equal to 2,000 yuan
---work information.
select * from emp where job = 'SALESMAN' or sal >= 2000;

not

---Query the number of employees who are engaged in non-sales work and whose salary is not less than 1500,
---Name, position, salary, entry time.
select
	empno,
	ename,
	job,
	sal,
	hiredate
from
	emp
where
	(not job = 'SALESMAN')
and
	(not sal <= 1500);

between and

--Check salary in 1500 - 3000 information between employees
select 
	* 
from 
	emp 
where 
	sal between 1500 and 3000;

---Query information for employee number 7499 7902 7788.
select
	*
from
	emp
where
	empno in(7499,7902,7788,8888);
	
Equivalent to
select
	*
from
	emp
where
	empno = 7499
or
	empno = 7902
or
	empno = 7788
or
	empno = 8888;

not in

---Query information for employees with numbers other than 7499 7902 7788.
select
	*
from
	emp
where
	empno not in(7499,7902,7788,8888);

Fuzzy queries generally need to be used in combination with two wildcards.
a) _ represents an arbitrary character
b) % represents any number of characters

---query name contains S employee information.
select
	*
from
	emp
where
	ename
like
	'%s%';
	
---The second letter of the query is M employee information.
select
	*
from
	emp
where
	ename
like
	'__M%';

or
empno = 7902
or
empno = 7788
or
empno = 8888;

not in

---Query information for employees with numbers other than 7499 7902 7788.
select
	*
from
	emp
where
	empno not in(7499,7902,7788,8888);

Fuzzy queries generally need to be used in combination with two wildcards.
a) _ represents an arbitrary character
b) % represents any number of characters

---query name contains S employee information.
select
	*
from
	emp
where
	ename
like
	'%s%';
	
---The second letter of the query is M employee information.
select
	*
from
	emp
where
	ename
like
	'__M%';

7. Practice (practice can make you more impressed)

Copy the following sql statement into your mysql editor, I use mysql workbench here
Click Run to create the database and tables.

View the name, position, working hours, salary of the employee with department number 10 in the EMP table.
To calculate the annual salary of each employee, it is required to output the employee's name and annual salary.
Query the total amount received by each employee every month (sal is salary, comm is subsidy).
Displays the name and salary of the employee whose job title is manager.
Displays the names and salaries of all employees whose third character is an uppercase R.
Displays the name, salary, and position of the employee whose job title is SALESMAN or MANAGER.
Displays the names of all employees without benefits.
Displays the name, salary, and stipend of employees who are subsidized.
Query all employees with department number 30
10. The names, numbers and department numbers of all salespersons.
11. Identify employees whose bonuses are higher than their salary.
12. Identify employees with bonuses that are 60% higher than their salary.
13. Find out the details of all managers in department number 10 and all salesmen in department number 20.
14. Find the details of all managers in department number 10, all salesmen in department number 20, and all employees who are neither managers nor salesmen but whose salary is greater than or equal to 2000.
15. Employees with no bonus or bonus below 1000.
16. Query employees whose names consist of three characters.
17. Check the employees who joined in 2000.
18. Query all employee details, sort by number in ascending order
19. Query the details of all employees, sort by salary in descending order, if the salary is the same, use the entry date to sort in ascending order
20. Query the number, name, salary, and annual salary of employees whose name does not contain M, and whose salary is greater than 1500, or whose annual salary is not less than 30,000, in descending order by employee name

drop database if exists GZ2205;
create database GZ2205 charset utf8;
use GZ2205;

create table BONUS
(
  ENAME VARCHAR(10),
  JOB   VARCHAR(9),
  SAL   int,
  COMM  int
) engine=InnoDB default charset=utf8;

create table DEPT
(
  DEPTNO int(2) not null,
  DNAME  VARCHAR(14),
  LOC    VARCHAR(13)
) engine=InnoDB default charset=utf8;

create table EMP
(
  EMPNO    int(4) not null,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      int(4),
  HIREDATE DATE,
  SAL      double,
  COMM     int(7),
  DEPTNO   int(2)
) engine=InnoDB default charset=utf8;

create table SALGRADE
(
  GRADE int,
  LOSAL int,
  HISAL int
) engine=InnoDB default charset=utf8;



insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '19800608', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '19810605',1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '19830608',  1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '19810608',  2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '19860608',  1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '19880608',  2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '19890603', 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '19870602',  3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, '19810618',  5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '19820628', 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, '19870628', 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '19810608', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '19820308', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '19820316',  1300, null, 10);
commit;
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);
commit;

Tags: MySQL Database

Posted by LuciBKen on Sun, 10 Jul 2022 03:57:02 +0930

Programs Wiki

Mysql study notes day1 -- learn all the knowledge points of mysql in three days

Mysql
Super full MySQL study notes, you can learn to use SQL statements skillfully in three days, full of dry goods, there are corresponding exercises at the end of the article, you can practice more to deepen your impression, I hope my article will be helpful to you

1. Server

2. Data server

3. Basic use

4. SQL

1.DDL

2.DML

5. Data types

1. Numerical type

2. Date type

3. String type

6. Query operation

1. Initialize data

2. Introduction to table structure

3. Basic query

7. Practice (practice can make you more impressed)

Popular Tags

Programs Wiki

Mysql study notes day1 -- learn all the knowledge points of mysql in three days

Mysql Super full MySQL study notes, you can learn to use SQL statements skillfully in three days, full of dry goods, there are corresponding exercises at the end of the article, you can practice more to deepen your impression, I hope my article will be helpful to you

1. Server

2. Data server

3. Basic use

4. SQL

1.DDL

2.DML

5. Data types

1. Numerical type

2. Date type

3. String type

6. Query operation

1. Initialize data

2. Introduction to table structure

3. Basic query

7. Practice (practice can make you more impressed)

Popular Tags

Mysql
Super full MySQL study notes, you can learn to use SQL statements skillfully in three days, full of dry goods, there are corresponding exercises at the end of the article, you can practice more to deepen your impression, I hope my article will be helpful to you