Content of this article: Describes the operation (CRUD) of data in Mysql database in detail, including inserting, modifying, deleting data, and querying data, including where, in, like, ifnull, and or non, order by, aggregation functions, etc.
1. Insert data (insert)
1. Syntax for inserting data
- insert into table name (field 1, field 2, field 3) values (value 1, value 2, value 3);
- insert into table name values (values 1, value 2, value 3);
2. Things to note
- The inserted data must be the same as the field type
- The size range of data is within the field range
- Values correspond to fields one by one
- String or date type data requires single quotes
Example:
insert into user values (1,'meimei','1956‐1‐1','1957‐1‐1','HR',5000,'meimeimei','xx'); insert into user values (2,'Xiaofeng','1996‐1‐1','2013‐1‐1','BOSS',15000,'mei','xx'); insert into user values (3,'Congcong','1993‐11‐11','2015‐09‐10','WORKER',500.0,'chou','yy'); insert into user values (4,'flowery','1994‐1‐1','2013‐1‐1','BOSS',25000,'mei','xx'); insert into user values (5,'Xiao Cang','1991‐1‐1','2014‐1‐1','BOSS',15000,'mei','xx'); insert into user values (6,'Ozawa','1986‐1‐1','2013‐1‐1','BOSS',15000,'mei','xx');
Problem: If MySQL inserts Chinese data garbled
solve:
1.Put it firstMySQLService stops。 2.turn upMySQLConfiguration file for installation file [client] port=3306 [mysql] default‐character‐set=gbk 3.RestartMySQLServe
2. Modify data (update)
1. Syntax
update table name set field 1=value, field 2=value where condition; where username = 'meimei';
2. Whether there is or not
If there is no where conditional statement, all data is updated by default.
If there is a where condition, the records that meet the conditions are updated by default.
Example:
Modify all employees' salaries to5000Yuan。 update user set salary = 5000; Put the name as'Congcong'The employee salary is modified to3000Yuan。 update user set salary = 3000 where username = 'Congcong'; Put the name as'Xiaofeng'The employee salary is modified to4000Yuan,jobChange toccc。 update user set salary = 4000,job = 'ccc' where username = 'Xiaofeng'; Increase your salary on the original basis1000Yuan。 update user set salary = salary+1000 where username = 'flowery';
3. Delete data (delete)
1. Syntax
delete from table name where condition;
2. Whether there is or not
If there is no where condition, all data is deleted by default.
3. truncate table name; delete all data in the table.
delete from table name; can also delete all data.
- Difference: truncate first deletes your entire table and silently creates an empty table (the same as the original table structure).
- delete from Table name Delete line by line. (Use it)
- Concept of things: Submission of things and rollback of things.
Example:
Delete the name in the table'Congcong'Records of。 delete from user where username = 'Congcong'; Delete all records in the table。 delete from user; drop table user；Delete data
4. Query data (select) (key point)
1. Basic select statement
grammar:
select * from table name; �
select Field 1, Field 2, Field 3 from table name; ‐-Query record of field 123
DISTINCT �
Example: select distinct english from stu;
practise:
create database day15;
use day15;
create table stu(
id int,
name varchar(30),
math int,
english int,
chinese int
);insert into stu values (1,'Mimei',78,93,56); insert into stu values (2,'Congcong',18,13,16); insert into stu values (3,'Xiaofeng',98,96,89); insert into stu values (4,'flowery',90,100,46); insert into stu values (5,'Ouyang Feng',74,93,56); insert into stu values (6,'Danzu Wu',37,11,89); insert into stu values (7,'Shengda',88,77,66); insert into stu values (8,'Saoji',55,44,33);
2. Use operations and alias in query statements (data in the database remains unchanged)
Add on all student scores10Specialties。 select name,(math+10) m,(english+10) e,(chinese+10) c from stu; Statistics of the total score of each student。 select name,(math+english+chinese) total point from stu; 使用别名表示学生point数 select name,(math+english+chinese) total point from stu;
3. Use where conditional filtering
Check the grades of students named Congcong select name,math,chinese from stu where name = 'Congcong'; Search English scores are greater than90Classmates select name,english from stu where english > 20; The total query score is greater than200All students select name,math+english+chinese from stu where (math+english+chinese) > 200;
Operations that appear in clauses (3)
4.1 > < <= >= = <> Greater than, less than, greater than (less than) equals, not equal to
4.2 in represents the range.
select * from stu where math = 18; Query a data select * from stu where math in (78,18,99);
4.3 like fuzzy query ‐‐ The fuzzy conditions meet
select * from stu where name like 'open_'; Name of Zhang（There are only two）Records of select * from stu where name like 'open%'; Name of Zhang（Zhang Fei Zhang Yide Zhang Ye Ye Ye Ye Guan Xi）Records of。 select * from stu where name like '%open'; At the end is Zhang（Congcong Zhang XSDFopen） select * from stu where name like '%open%'; 只要名称中包含open
4.4 isnull ifnull nullif determines whether a field record is empty
ifnull: If xxx is null, it can be replaced with the default value;
4.5 and or not
Query English scores in 80－90The classmates between。 select * from stu where english >= 10 and english < 19; Query math scores as89,90,91Classmates。 select * from stu where math in (89,90,91); Check the grades of all students with the surname Xiao。 select * from stu where name like 'Small%'; Query math scores>80，Chinese Points>80Classmates。 select * from stu where math > 80 or chinese > 80;
Summarize：select List name（Operation） from Table name（Alias） where condition（Operation的符号）;
by sort the results of the query
5.1 Sorting syntax
select * from Table name where condition order by List name Ascending order/descending order;
5.2 Ascending and descending order
- order by column name asc; (asc order, default value)
- order by column name desc; (desc order)
5.3 The order by clause must appear at the end of the select statement. Example:
Output after sorting math scores。 select name,math from stu order by math desc; Sorting total scores is output from high to low select name,(math+english+chinese) as total from stu order by total desc; The grades of students named Cong are sorted in descending order according to English，Students with the same English are descending order in mathematics select name,english,math from stu order by english desc,math desc; Sorting the grades of students with Cong surname select name,(math+english+chinese) as total from stu where name like 'Sao%' order by total desc;
6. Aggregation function where, group by, having, order by
6.1 Aggregation function: To sum up the data sum of a certain column. Number of columns. The average of a column. Maximum and minimum values in a column.
6.2 Aggregate functions to operate columns.
count ---count sum ---Ask for sum ifnull --Determine whether it is empty：grammar：ifnul(xxx,0) ifxxxfornull，Replace with0 avg ‐‐ average value max ‐‐ Maximum value min ‐‐ Minimum value
practise:
Statistics how many students there are in a class？ select count(name) from stu; Statistical mathematics scores are greater than90How many students are there？ select count(math) from stu where math >= 90; The total statistics score is greater than220How many people are there？ select count(*) from stu where math + english+chinese > 200; Statistics of total math scores in a class？ select sum(math) from stu; Statistics of a class Chinese、English、Total scores of each subject in mathematics select sum(math),sum(english),sum(chinese) from stu; Statistics of a class Chinese、English、Total math scores select sum(ifnull(math,0)+english+chinese) from stu; select sum(math) + sum(english) + sum(chinese)from stu; Write an update statement：update stu set math = null where id = 2;
This is the end of this article about data operations (CRUD) in Mysql database. For more related content on mysql data crud, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!