MySQL series (i)---basic knowledge summary
Preface: This article is mainly for MySQL basic knowledge Summary, the basic knowledge of MySQL many, here for a simple general introduction, specific details or need to search by themselves. Of course, there are many missing places in this article, the follow-up will be gradually supplemented.
Directory
- MySQL series (i): basic knowledge Summary
- MySQL series (ii): MySQL transaction
- MySQL Series (iii): Index
Database and database software
- A database is a container that holds organized data
- DBMS is a software management system designed for managing databases, MYSQL, ORACLE, etc. are database management systems
Mysql
- MySQL is a database management software
- Open source, free to use
MySQL command
- Create DATABASE NAME
- Use database to select databases
- Drop database NAME deletes databases directly
- Show database Displays the list of available databases
- Show table displays a list of tables in the database
- Show COLUMNS from table is equivalent to DESCRIBE table, showing field information for the table
- Show GRANTS Displays the security permissions granted to the user
- Show ERRORS show WARNINGS display server error and warning messages
Sql
- Structured Query LANGUAGE Structured Query language, a language dedicated to communicating with the database
- Not a DBMS-specific language, many DBMS support SQL, but different DBMS implementations of SQL are different
- DBMS supported SQL syntax is not fully applicable to other DBMS
- SQL statements are case insensitive
- Multiple statements require semicolons to be separated, and a single statement can be used without semicolons
- Typically, SQL statements are capitalized, and identifiers (such as the name of the table name database) are lowercase
Create a table
- The CREATE TABLE user (id INT not NULL auto_increment, username varchar (1) is not null,password varchar (a) NOT null DEFAULT, PRIMARY KEY (ID)) ENGINE =innodb
Retrieving columns
- SELECT username from user
- SELECT Username,password from user
- SELECT * from user does not recommend all columns, do not ' * ', otherwise this operation is very performance-consuming
- SELECT DISTINCT username from user only returns a different username, such as two rows of user names that are identical, only one row of records is displayed
Limit results
- SELECT * from TABLE LIMIT 5 to retrieve the first five elements
- SELECT * FROM TABLE LIMIT 5,5 retrieve 6 to 10
- SELECT * FROM TABLE LIMIT 5,-1 retrieves 6 to last row
Result sort
- SELECT * from the user order by username, in alphabetical order by username
- SELECT * from the user ORDER by Username,password, if username have the same, then username the same again by password arrangement, if username are unique, Password doesn't work this time.
- SELECT * from the user ORDER by username DESC, descending order, by default ascending ASC
- SELECT * from the user e order by username Desc,password at this time in descending order of username, if username the same row in ascending order of password
- SELECT * from the user order by username limit 1 mixed with ORDER and limit
- SELECT * FROM user where username = ' Jiajun ' ORDER by password here order to be in where
Filtering data
- SELECT * FROM user WHERE username <> ' Jiajun ' is not equal to and! = Same effect
- SELECT * FROM user WHERE ID between 1 and 10 to retrieve 1 to 10 records, including 1 and 10
- SELECT * from user where username is NULL lookup column no value row, here No value is equal to value 0 and empty string
- SELECT * from user where id=1 or username= ' Jiajun ' and password= ' 666 ' execution order is WHERE id=1 OR (username= ' Jiajun ' and password= ' 666 ') and has a higher priority, but it is recommended to add parentheses
- SELECT * from user where ID in (both) effect and select * from user where id=1 or id = 2 If this effect is to be achieved, it is recommended to use the first, the syntax is clearer, and the general execution is faster
- SELECT * FROM user WHERE ID not in (UP)
Wildcard filtering
- SELECT * from the user WHERE username like ' jia% ', matching the username at the beginning of the Jia, no matter how many characters are followed, Jia and Jiajun are matched, can be configured to MySQL if the region is case-sensitive, That is, Jiajun does not match. Note that the like '% ' is not matched to a value of NULL
- SELECT * FROM user WHERE username like ' Jia_ ', _ can match only one character, that is, match Jiaj mismatch Jiajun
- Wildcard matching is inefficient, if other ways can have the same effect, it is recommended to use other effects, and it is best not to put the wildcard character at the beginning, because it is very slow
Regular-expression filtering
- SELECT * FROM user WHERE username REGEXP '. ia ', regular expression,. Match any one character
- SELECT * FROM user WHERE username REGEXP ' June ' matches the username containing June, ' Jiajun ' and ' June ' match
- SELECT * FROM user WHERE username REGEXP ' jiajun|jiaj ', ' | ' Same as the ' or ' effect
- SELECT * FROM user WHERE username REGEXP ' [123]jun ', Match contains ' 1jun ' or ' 2jun ' or ' 3jun '
- [Except ^123]123
- [1-9],[a-z], using '-' to denote range
- Match Special characters (e.g. '. ') and ' _ ') can be escaped with ' \ ', such as matching there '. ' can be represented as ' \. '
Stitching Fields
- SELECT CONCAT (username, ' (', Password, ') ') from user, stitching two fields together, the query result is a column, the row value Jiajun (666)
- SELECT CONCAT (username, ') ', password, ') ') as up from user, set the field name for the stitched column
Arithmetic operations
- SELECT Price*count as all from TABLE multiplies the unit price and quantity by the column named All
Function
- The function portability is not high, that is, one function supports a DBMS, but does not necessarily support another DBMS, such as support for MySQL and not Oracle. So use the function to write a good comment.
Text Processing functions
- Length () returns string lengths
- LOWER () Converts a string to lowercase
- UPPER () Converts a string to uppercase
- LTRIM () Remove the left space of the string
- RTRIM () Remove the space to the right of the string
Date and time processing functions
Date function Note points
- SELECT * from TABLE WHERE date= ' 2017-7-22 ', if the type of date is datatime type, then the table one record should be stored in the format of 2017-07-22 16:20:19, when this record is not filtered out, The filter was 2017-07-22 00:00:00
- can take the date function SELECT * from TABLE WHERE date (date) = ' 2017-7-22 '
- The time range can take the date function SELECT * from TABLE WHERE Date (date) between ' 2017-7-1 ' and ' 2017-7-31
Array processing functions
- ABS () returns the absolute value of a number
- COS () returns the cosine of an angle
- EXP () returns the exponential value of a number
- MOD () returns the remainder of the operation
- Pi () returns PI
- RAND () returns a random number
- Sin () returns the sine of an angle
- SQRT () returns the square root of a number
- TAN () returns the tangent of an angle
Aggregation functions
- SELECT AVG (Student_score) as Avg_ Prive from score averaging
- SELECT COUNT (*) Number of records in table table
- SELECT column (name) the number of records with values in the From table table, with a value of NULL not counted
- SELECT Max (score) from TABLE find maximum value
- SELECT min (score) from TABLE find minimum value
- SELECT sum (count) from TABLE returns the sum of the specified columns (price*count) as well
Data grouping
- SELECT avg (score) from score GROUP by class_id retrieves the average score for different classes
- SELECT AVG (score) from score Group by class_id, DEP_ID, and group by can be followed by multiple fields
- Select AVG (score) from score Group by class_id, the field of select must be a field followed by a group by, or an aggregate function, where the class_id is merged into one line according to the class_id. But the merging of these score is different, how can it be merged into one line
- If the grouping column has a null value, the column value is divided into a set of NULL
- Group BY IS to be filtered and then sorted before the order by.
- SELECT Count (*) from the TABLE group by ID has the COUNT (*) >3, grouped and then filtered.
Sub-query
- SELECT * FROM student where class_id in (SELECT class_id from teacher where teacher_id=6), subquery filter, where nested subqueries
- Select Name, (select score from score WHERE studen.student_id=score.student_id) as score from Student calculated Field field use subquery
- It is not recommended to use too many subqueries, which can affect performance
Primary key
- A column or set of columns that uniquely identifies itself, uniquely distinguishing one row in a table
- Primary key cannot be empty
- The primary key values for any two rows must be different
- The primary key is not required, but it is recommended that you have a primary key in each table, which makes operation management more convenient
- Primary key values are best not updated
- A table's primary key can have only one
FOREIGN key
- A table's primary key points to the foreign key of another table, such as the primary key student_id of the student table, which is also in the score table, and is the foreign key of the score table.
- Foreign keys maintain data integrity and consistency. , such as when you modify the student_id in the student table, the score student_id will also be linked to update. And the student_id inserted in the score table must be in the student.
Join
- SELECT Name,score from score,student Two tables for the Cartesian product, which means the number of rows in the result score table rows *student table rows
- SELECT Name,score from Score,student where student.student_id=score.student_id, based on above, plus where to filter
- SELECT Name,score from score INNER joins student on student.student_id=socre.student_id Internal junction
- External Junction, left OUTER join and right OUTER join, sometimes the join condition does not match when the inner join, and the Ieft JOIN guarantees that all rows of the table on the other side are guaranteed
- The full join effect is the same as the left join and right join, and the table mismatch in the Open table does not match the list
- Do not join too many tables, which can degrade performance
- The connection may be because two tables have the same column, because there is no good limit to cause the same column to appear two times, so this is where it is needed, which is also called natural coupling
Alias
- SELECT CONCAT (username, ' (', ' Password, ') ') as MES from user, which uses CONCAT to stitch together two fields, and an alias for the stitched field MES
- The same table can also be aliased select Name,score from score as a,student as B WHERE a.student_id=b.student_id
Combination Query
- Using union to combine the results of multiple SELECT statements can be understood as a table with the same header vertically stitched together
- Each query must contain the same number of columns, and the field type is compatible.
- Select score from score where name= ' Jiajun ' or score between-100 and select score from score where name= ' Jiajun ' Unio N SELECT score from score WHERE score between and 100 function equivalent.
- The above is a single-table query, with the union feeling complex, but if the query for the different tables of the connection is simpler.
- If a query found 5 rows, b query to 4 rows, because there are duplicates, will remove the same line, and finally left 8 lines, if necessary, you can use UNION ALL
Inserting data
- Insert into user values (' Jiajun ', ' 666 ') and insert into user (Username,password) VALUES (' Jiajun ', ' 666 '), in the table only username and pas Sword Two fields are equivalent, the first way must be the number and order of values must be the same as the number of fields, the latter, because to the column and value, as long as the one by one correspondence is good
- Insert INTO User VALUES (' Jiajun ', ' 666 '), (' Jiajia ', ' 666 ') inserts multiple lines
- Insert INTO user VALUES (SELECT username, password from olduser), retrieve the rows of the olduser and insert them into the user table, with the attention to column issues, The field name after the SELECT statement is not important and does not need to correspond to the user table because it simply inserts the retrieved column values sequentially into the user table and does not care about the field names of the Olduser. The number and order of the columns are also important to note.
Update data
- Update user SET password = ' 666 ', money= ' 6666 ' WHERE username= ' Jiajun ', it is important to note that the where must not be missed, or update all rows in the table
- If an error occurs halfway through updating multiple lines, the update reverts back to the original value, and if you want to continue updating even if an error occurs halfway through, update the IGNORE user with the IGNORE keyword.
Delete data
- If you want to delete the value of a column, SET username=null is OK.
- If you want to delete a row, delete from user WHERE username= ' Jiajun '
- If you want to delete the entire table row Detelte from user, note that this table is not deleted, only all records are emptied. TRUNCATE user, also has the same effect, the difference is that he is first to delete the table, and then re-establish a table
Update table
- ALTER TABLE User Add phone CHAR (20) Add a column
- ALTER TABLE user Drop column phone deletes a row
- ALTER TABLE user CONSTRAINT Wai_jian foregin key (class_id) REFERENCES class (CLASS_ID) define foreign keys
- ALTER TABLE User Add PRIMARY key (ID) Add primary key
- Make a backup before modifying, the table changes cannot be undone
Delete a table
Renaming a table
- RENAME TABLE user to users
View
- When we query a result, we can wrap it into a virtual table, which is the view, and we can use it as a table.
- The view itself does not contain data, and the data is retrieved from other tables
- Use views to reuse SQL, and to protect data, and to grant users partial data permissions rather than all data
- Cannot update operations on views such as grouping (group by), joins, subqueries, and (Unoin), aggregate functions (Sum/count, etc.), calculated fields, DISTINCT, etc.
- Create View My_view as SELECT Name,score from student, creating views
- Drop View My_view Delete views
Stored Procedures
- Sometimes SQL needs to have if else, and we can encapsulate multiple SQL statements together to form a stored procedure, which is not only simple and secure, but also performance is higher.
- The stored procedure does not display the result, but returns the result to the variable you specified.
- The process is to create a stored procedure, use stored procedures, pass parameters in, select parameter output results
- The parameter type in is passed to the stored procedure, out of the stored procedure, and inout to the stored procedure. The result is returned to the out variable
- CREATE PROCEDURE Pro (in PARAM int,out PARAM2 int.) BEGIN SELECT COUNT (*) from the user WHERE id=param into PARAM2 END; Create a stored procedure
- Call PRO (666, @PARAM2), SELECT @PARAM2, invoke and output PARAM2
- Show Create PROCEDURE Pro displays the CREATE statement for the stored procedure
- SHOW PROCEDURE Status Lists all stored procedures
Trigger
- Some statements are executed automatically when an event occurs, and you need to do something after the insert UPDATE Delete, you can use the trigger
- A table up to 6 triggers, inserted before and after the delete update.
- Create TRIGGER My_trigger after inserts on the user for each row BEGIN SELECT new.id END, creating a trigger named My_trigger, in the user table, each insert row, the ID is displayed Come out
- You can refer to the new virtual table in the trigger to access the inserted row. You can refer to an old virtual table to access the deleted row.
- Drop TRIGGER My_trigger Delete trigger
- Only tables support triggers, views do not support
- Stored procedures cannot be called in triggers
I think sharing is a kind of spirit, sharing is my pleasure, not to say I think I said must be right, I said may be a lot of is wrong, but I hope I said something is my life experience and thinking, is to give a lot of people reflect, maybe give you a second, half a second, even if a word a little bit of truth, It is my greatest value to raise my inner feelings. (This is my favorite word, but also I write the original intention of the blog)
MySQL Series (i)
