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;
-
or
--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;
-
in
---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);
-
like
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);
-
like
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;