- Note that each command must be added with ";"
- Note: Add single quotes to date '’, eg:'2019-01 01 10:30:00’
- Query empty: is null; —> cannot be used=
- Determining that it is not empty: is not null; —>Not available! =
1. Basic database operations
1. Display database information
show databases;
2. Create a database:
create databaseDatabase name;3. Select (using) the database:
useDatabase name;4. Delete the database (it is best not to delete):
drop databaseDatabase name;2. Data table operation
1. Create a table (note that you need toSelect the database first）
create tableTable name (the structure of the table);//The structure of the table is the attribute of the table and the attribute type
**Note: **Table names should not be duplicated with database keywords
2. Check the data table name
show tables; //This command can only view table names
3. Check the specific structure of the table
descTable name; //This command can view the specific information in the table
4. Delete the data table (it is best not to use)
drop tableTable name;
3. Add, delete, modify and check MySQL table—“Beginner”
1. Added
- Basic Insert (insert all columns of a record)
insert intoTable namevalues(Record); //A record contains several columns, that is, specific information
- Insert multiple records at once (the brackets should be separated by commas)
insert intoTable namevalues
(Record),
(Record),
(Record),
...;
- Insert several columns of a record at a time (displays which columns to insert)
insert intoTable name (displays the column name specified to insert)values(Specific information of the specified attribute)2. View the information in the table
- View all column information
select * fromTable name;
- View information for the specified column
selectList namefromTable name;
- Put the search result into an expression (that is, modify the contents of the table through the expression)
selectList name+10 fromTable name; //For example, the content of a column +10
selectList name1+List name2+... fromTable name; // Calculate the sum of certain attributes
selectList name1+List name2+... asAliasfromTable name; //Give the sum an alias
- Go to the heavy
select distinct column name from table name
When specifying multiple columns to deduplicate (all added after distinct), you must ensure that both columns are repeated before deduplicate. - Sort
order by (default ascending order)—> Use asc to asc is also ascending order
order by column name desc display plus desc—> descending order
select column name from table name order by column name
order by column name 1desc, column name 1desc Sort by multiple columns
null is regarded as the minimum value in order by. If order by is not added, there cannot be any expectations for the order between records of the query result ~~~
Note: null does not participate in ±*/ operation
Multi-column sorting is when the first column cannot be divided into size, then it is arranged according to the second column. If the size cannot be divided into size, regardless of the second column.
- Conditional query (= equal sign in SQL, not assignment)
select column name from table name where condition (NuLL will be treated as false and will not be displayed)
Where multiple conditions are linked with a linker
Note: Where comparison cannot be made for column alias
and and or priority (without brackets): and>or
The difference between = and <=>: The first type cannot be filtered out, the second type can - Range query
a) between and----->equivalent to and
b)in—>Filter—>Equivalent to or - Use like to fuzzy search ---->Find all according to some conditions->Applicable to characters, strings, and numbers
eg:where list like ’ ’
Wildcard characters: % (can represent multiple characters), _ (each underscore represents only one character)
a) Start with t: ‘t%’
a) Ending with t: ‘%t’
a) contains t: ‘%t%’
Note: Fuzzy matching efficiency is low and is not used frequently - Pagination query (pagination of multiple records)
limit
select * from table name limit Display data row count offset N
N means to start searching from the Nth data
3. Modify update
Specify modifying certain rows and columns
update table name set modify data where conditions (for which row is modified)
For example: Change Tom's Chinese score to 99
update set chinese=90 where name=‘tom’
Note: Without adding where, all data in the column is modified
When multiple columns are set, separate them with commas
decimal(3, 1)->A total of three significant digits, one after the decimal point
4. Delete
Just specify the row to delete the compound condition
delete from table name where delete condition
If there is no where condition, deleting all data will become an empty table, which is different from directly deleting the table
4. Database backup:
1. MySQL supports bin log
2. Backup mysqldump tool
3. Restore based on disk files
V. Database Constraints
1. Constraint type (for columns)
1) *not null: *Specify that a certain column cannot store null values
2) *unique: *Specifies that a row in a certain column must have a unique value
3) *default: *Specifies the default value when no value is assigned to the column
The default specified by default will take effect when inserting by column (columns are inserted by columns but not specified, that is, inserting according to the default value)
If you manually set a column to null, the default value effect will not be triggered
4）primary key ：—> "Primary Key" not a combination of null and unique to ensure that a column (or a combination of two columns or multiple columns) has a unique identifier, that is, it cannot be empty or repeated
When designing a table, you usually need to specify itonePrimary key** (only one can be specified)**, the primary key is the only standard of a record
Auto-increase primary key:primary key auto_increment-– The value of the primary key is automatically added to 1 every time (apply to generate a unique id for a set of data), delete a record and then add it, and the primary key will be incremented once
5）foreign key ：—> "Foreign Key" Ensure that data in one table matches the reference integrity of values in another table
Syntax: foreign key (field name) references main table (column)
Note: Foreign keys must constrain the primary key of the primary table.
create table class(id int primary key ,name varchar(50));
create table student(id int primary key auto_increment,name varchar(50),classid int, foreign key (classid) references class(id));
Foreign key constraints require that the value of the classid field in the current table must appear in the id in the class table before it can be
**Note: **If the id in the class table has been used by the classid in the student, then the corresponding records in the class table cannot be deleted, and the entire class table cannot be deleted.
6. Database design
1. Definition: "Database design" is actually a design table. According to the current problem scenario, analyze what problem scenarios need to be created, which tables need to be created, which fields should be included in the table, and what constraints are in these fields.
2. Basic idea: From the specific problem scenario, first extract the "entity" (i.e., object), and then find out the association relationship between multiple "entities" (one to one/one to many/many to many/irrelevant)
3、For example:
Question Scenario: Describe the student’s test scores
analyze:
1) Entity: Students, Courses, Grades
2) The relationship between the three
Students and Courses: Many-to-many
Students and grades: one-to-many (not considering the same score)
Course and grades: It doesn’t matter (There is no direct relationship between the two, but is linked by students as media)
3) Design Table
Student table: (id, name)
Course Schedule: (id, name)
Grade Table: (student_id,course_id, score)—> Link student tables and course tables through grade tables
6. Add, delete, modify and check MySQL table—“Advanced”
1. Added -> Use query results as new content (combination of insertion and search)
insert intoTable 2 NameselectTable one column namefromTable 1;2. Aggregation query—> "Aggregation" refers to the aggregation between **" and has nothing to do with "columns".
1) Aggregation function
count()，sum(),avg(),max(),min()
Note: Except for count, the other four have no meaning for those that are not numbers.
a) count—Find the number of rows
select count(*) fromTable name;//First execute select * from table name, and then count how many rows are in the result
**Note: **When select count (a certain column), if the value in the current column is null, it will not be counted into it.
b)sum—Sum
Example: Check the total scores of all students in Chinese
select sum(chinese) from student;
Extension: View warning information
show warnings;
c)avg—Find the average value
Example: Check the average Chinese scores of all students
select avg(chinese) from student;
d)max—Find the maximum value
For example:
select max(chinese) from student;
e)min—Find the minimum value
For example:
select min(chinese) from student;
2) group by clause
For a certain column, follow the contents of the column to enter these records.The same valueThe group is divided into
For example:
selectThe column to be grouped, the evaluated valuefromTable namegroup byColumns to be grouped;
//For example: Find the average salary of different positions in the company
select role,avg(salary) fromTable namegroup by role;
**Note: **group by is often used with aggregate functions (Group first, then aggregate), if you are not using an aggregate function, and try to query some non-repetitive classes at this time, the result may be less scientific
3) Conditional filtering—having—>To use it with group by
- Before using group by group, filter records by condition, use where
- After using group by group, filter by condition, use having
For example:
selectThe column to be grouped, the evaluated valuefromTable namegroup byColumns to be groupedhavingcondition;
//Example: Find the average salary of different positions in the company and select positions with an average salary of less than 1,500.
select role,avg(salary) fromTable namegroup by role having avg(salary)<1500;