• define

MySQL is a small open source relational database management system (RDBMS) that uses SQL (Structured Query Language) for database management.

  • advantages

1. Open source

MySQL is an open source database, and anyone can get the source code and fix defects. Anyone can use the MySQL database for any purpose

2. The cross-platform

MySQL can achieve cross-platform operation, supporting various mainstream operating systems in the market, such as Windows, UNIX,Linux, MacOS and other operating systems.

3. Low development cost

Anyone can download the software from the MySQL website, and some community versions are available as a free trial. Even with some paid features, MySQL has an absolute price advantage over commercial software such as Oracle, DB2 and SQL Server.

4. Powerful and easy to use

MySQL is a real multi-user, multi-threaded SQL database server.

  • Use SQL language to manage database

1. Database management statement

Note: MySQL Database is not case sensitive, so I use the camel name. 1.Show DataBases 2 Engines (Check the types of Engines supported by the current version of the database)Copy the code

2. Basic operations of data tables:

Create table constraints such as primary key, foreign key, unique, cannot be null, etc., such as department table and employee table:CREATE TABLE dept
(
  id INT(11) PRIMARY KEY// Create primary key constraintname VARCHAR(22)   UNIQUE// create a unique constraint.CREATE TABLE employee
(
  id INT(11) PRIMARY KEYAUTO_INCREMENT, // Set automatic incrementname VARCHAR(25) Not Null// Create the non-null constraint deptIdINT(11),
  sex char DEFAULT 'male'// Specify that the default value is maleCONSTRAINT employee FOREIGN KEY(deptId) REFERENCES dept(id// Create a foreign key constraint.DESCRIBEEmployee // View the basic structure of the tableDESCThe employee / / same as aboveSHOW CREATE TABLEEmployee // View details of the tableCopy the code

3. Modify data table:

Take the above table for example

ALTER TABLE dept RENAMEDeptment // Change the dept table to deptmentALTER TABLE employee MODIFY sex VARCHAR(30// Change the sex field type of the EMPLOYEE table toVARCHAR

ALTER TABLE employee ADD salary FLOAT NOT NULLSQL > alter TABLE EMPLOYEE select * from employeefloatCan't be emptyCopy the code
ALTER TABLE employee ADD salary FLOAT FIRST// Add a new field to the first column of the Employee tableALTER TABLE employee ADD salary FLOAT AFTER name// Add a new field after the NEme field in the Employee tableALTER TABLE employee DROPSalary // Drop the fields in the EMPLOYEE tableALTER TABLE employee DROP FOREIGN KEYEmployee // Drop the foreign key constraint on the EMPLOYEE tableCopy the code

Mysql > alter TABLE dataflow;

Note: to remove the primary TABLE from the TABLE tomorrows, the foreign key associationmust be removed because the data integrity would be compromised if the primary TABLE were to be removedCopy the code

  • MySQL data types and operators

1. The plastic

Data Type Storage Requirements Valid Value Range Unsigned Value range TINYIN 1 byte - 128-127 0-255 SMALLINT 2 byte - 32768-32767 0-65535 MEDIUMINT 3 byte -8388608 - 8388607 0-16777215 INT(INTEGER) 4 bytes -2147483648 -2147483647 0-4294967295 BIGINT 8 bytes -9223372036854775808 - A scale of 0-18446744073709551615 to 9223372036854775807Copy the code

2. Floating point

Type Name Description Storage Requirements FLOAT Single-precision floating point number 4 bytes DOUBLE high precision floating point number 8 bytes DECIMAL(M,D),DEC compressed strict fixed-point number M+2 bytesCopy the code

3. Date from time type

Type Name DATE Format DATE Range Storage Requirement YEAR YYYY 1901-2155 1 byte TIME HH:MM: SS-838:59:59-838:59:59 3 bytes DATE YYYY-MM-DD 1000-01-01 - 9999-12-31 3 bytes DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00-8 bytes 9999-12-31 23:59:59 TIMESTAMB YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01-4 bytes 2038-01-19 03:14:07Copy the code

4. String type

Type Name Description Storage Requirements CHAR(M) Fixed-length non-binary character string M bytes, 1<=M<=255 VARCHAR(M) Variable length non-binary character string L+1 bytes Again L<=M and 1<=M<=255Copy the code

  • Common operators

1. Arithmetic operators

Operator + addition - subtraction * multiplication/division, returns the quotient %, returns the remainderCopy the code

2. Compare operators

Operator = equal <=> Safe equal (can compare NULL) <>(! =) NOT equal to <= Less than or equal to >= Greater than or equal to < less than or equal to >= Greater than or equal to < less than or equal to > IS NULL Check whether the value IS NULL IS NOT NULL Check whether the value IS NOT NULL LEAST Return the minimum value when there are multiple parameters GREATEST When there are multiple parameters, Returns the maximum value BWTWEEN AND determines whether a value is between two values. ISNULL determines whether it ISNULL. IN determines whether a value is any value IN the IN listCopy the code

3. Logical operators

The operator does NOT or! Logic is not AND OR && logic AND OR OR | | OR logic OR XOR logicCopy the code

4. Aggregate function

Mainly used for the sum of the values of a column, average, number of records, etc., need to use aggregate functions

AVG() Returns the average value COUNT() Returns the number of rows in a record COUNG() Returns the number of values in a column MAX() Returns the maximum value of a column MIN() Returns the minimum value of a column SUM() Returns the SUM of a columnCopy the code


  • SELECT(query) statement details

The SELECT attribute list FROM the table name [expression] WHERE clause for [GROUP BY the attribute name [HAVING conditional expression]] ORDER BY the attribute name [ASC | DESC]

Existing table

(1) the student table: (student id, name, gender, age, department), student (sno, sname, ssex, sage, sdept)

(2) course schedule :(course number, course name, name of pre-basic course), course(cno,cname,cpno)

(3) students’ course selection table :(student number, course number,grade), sc(sno,cno,grade)

Sno sname Ssex sage SDept 1 Liu Min female 19 Computer 2 Zhou Song male 21 Computer 3 Zhang Ming male 20 Economic and Trade 4 Meng Xin female 21 Credit management CNO Cname CPno 1 Database 4 2 Operating system 3 3 Information system Null 4 Data structure 2 SNO Cno grade 1 1 89 1 2 97 1 3 67 2 1 78 2 2 90 1 Single table queryselect cno, cname fromCourse // Query some fields in the tableselect * fromSutdent // Query all entries in the table (if the table has a large number of fields, the data transfer efficiency is too large)select sno, sname, year(now() -sage // query the calculated value (student year of birth),year(now()) is nested for functionsselect distinct sdept fromStudent // query and repeat the keyworddistinct 

select * from student where ssxe = 'woman'// conditionalwhereThe query wordsselect * from student where sage between 20 and 25; // Use between 0 and 9 to query the number between two valuesselect * from student where sage in (18.20.23// Query the values contained in the data setinOn the contrarynot in

select * from student where sname like 'a %'/ / query name"Zhang"The students ('a %'It begins with zhang.select * from student where sname like '_ sensitive %'// Query student confidence where the second field is' min '(_ represents a wildcard with a placeholder)select * from course where cpno is  null// query the first class asnullOn the contrarynot null

select * from sc where cno =1 and grade >= 80// Multi-condition queryandMake andorUsed as conditional joins and logical operatorsselect * from sthdent where sdept='Computer' or sdept = 'Department of Economics and Trade'/ / same as aboveselect * from student order by sage desc    //order bySorting wordsdescDescending orderselect * from student order by sage desc ,sno desc// Sort by age first, or by student id if they are the sameCopy the code

2. Aggregate functionselect count(*) from student where sdept = 'Computer'Select * from computer department where student number = 1select count(distinctSno) // Select * from snodistinctDuplicates have been removedselect avg(sags) from student where sdept = 'Computer'// Query the average age of a computer departmentselect max(grade) from sc where cno =1// Query optional1The highest score in the classCopy the code

3. Query GROUP BYselect sdept,count(*) from student group bySdept // Select * from dept where student = 1;select ssex, sage, count(*) from student group bySelect * from ssex, sage, select * from sage, select * from sageselect sno,avg(grade) from sc group by sno having avg(grade) >80; Select * from student where average score > 80 and average score > 80select sno, max(grade), min(grade) from sc group by sno with rollup// Query the maximum and minimum scores of each student (grouped by student id,with rollupIs the final result summary)Copy the code

4.LIMIT the number of query results.select * from student limit 2// There is only one argument2The query2rowsselect * from sc limit 1.3// select * from sc table2The line3Entry, the first parameter1Can be understood as from >1The second parameter is the number of queries to be queriedCopy the code

5. Connect query // query the student id information and the result of the student, the course number (two tables in the connection query) (internal connection will ignore the records that do not meet the connection conditions)select student.*, cno, grade from student, sc whereStudent.sno =sc.sno // select * from student where sno=sc.snoselect c1.cname, c2.cname from course c1, course c2 whereC1. cpno = c2.cno // The inner join ignores the records that do not meet the connection conditions. If the records that do not meet the connection conditions appear in the query results, the outer join is needed to display themleft join(left link) and Righejoin(right join), using the left join if the right table does not meet the join criteria values will be displayed asNULL
select student.*, cno, grade from student s left jion sc on s.sno = sc.sno  Copy the code

Select * from ANY, SOME, and ALL; select * from ALL; select * from ALL;select sno, grade from sc where grade <=all(select grade from sc)
select sno, grade from sc where grade <=(select min(grade) from sc)
select sno, min(grade) from sc order bySno query information about a student who is younger than a student in a non-computer science departmentANY(random)select * from student where sage < any(select sage from student where sdept = 'Computer')
 andsdept ! ='Computer'

//in 和 not inThe inner layer returns a set, and the outer layer compares the operation // selected by the query1Student number and name of course NOselect sno, sname from student where sno in(select sno from sc where cno = 1) // Select no option1Student number and name of course NOselect sno, sname from student where sno not in(select sno from sc where cno = 1) / /UNIONMerge query result set (accumulated rows of two query results) // Query female information and age greater than20Student information (used if you want duplicate lines to appearUNIONALL)select * frem student where ssex = 'woman' union select * from student where sage > '20'

Copy the code

7. Use regular expression to express query // attribute name REGEXP 'matching mode' // pattern character ^ ^ A to match records starting with a // for example, query students in the department starting with 'ji'select * from student where sdept regexp '^ plan'$$a matches records ending with a or pending character. $$A matches records ending with aselect * from student where sname regexp 'sensitive $'// The meaning of the pattern character.. matches any character. Query a record that begins with a, has two characters in the middle, and ends with dselect * from test where name regexp('^a.. d$'// The pattern character "[character set]" matches any character in the parentheses as opposed to [^ character set] // Query for records containing the letters B and Sselect * from test where name regexp '[bs]'/ / /14 -]14 -All digits [a-g] between a-g indicate all letters [^ character set] between A-g are exactly the opposite // pattern characters'S1|S2|S3'Matches any string S1,S2,S3select * from test where name regexp 'S1|S2|S3'// The pattern character * a* indicates that a match occurs1Time or0Select * from column A where b is followed or not followed by bselect * from test where name regexp 'ab*'// The pattern character + a+ indicates that a match must have a record beginning with b1B's on the recordselect * from test where name regexp 'ab+'// Pattern character string {N} a{5} said5At least they showed up.5Once a {1.5} indicates that a is present at least1Times, at most5timeselect * from test where name regexp 'a{5}'Copy the code