Summary of Common SQL statements

Common SQL statements Summarize insert common statements

insert 语法insert into <表名> ( <字段名1>,..,<字段名n > ) values ( 值1 ), ……，( 值n );--注意：1、后面的值1对应的是字段名1的值，依此类推。2、没有指定字段时，表示对应每个字段都会插入数据3、如果值是字符串的话要用 ‘ 单引号引起了 仅在name下插入一个名字mysql> insert into xixi(name) values(‘xiaoer‘);插入两行数据mysql> insert into xixi values(2,‘zhangsan‘),(3,‘lisi‘);--表示在xixi表中对应的2字段插入zhangsan 和3字段中插入了lisi在表中的多个字段插入多条数据mysql> insert into xixi(name,age) values(‘zhangsan‘,28),(‘lisi‘,29);--在xixi表中插入name值为zhangsan，age字段值为28和name值为lisi，age字段值为29查看表内容mysql> select * from xixi;使用 insert 复制表结构及内容，产生附表。mysql> create xixi2 test2 like xixi;    --创建新表xixi2复制xixi表的结构mysql> insert into xixi2 select * from xixi;    --把xixi表的内容插入新的xixi2表中

Update the frequently Used statements

update更改数据库为安全一定要加上where条件更新表内容将字段中的lisi改为hahamysql> update xixi set name=‘haha‘ where name=‘lisi‘;将xixi表中字段id=5的行把name值改为maliuupdate xixi set name=maliu where id=5;一次修改多个值mysql>update xxi set name-maliu,age=27 where id=5更改后查看select * from  xixi;

Delete a common statement

delete from xixi;     #逻辑删除，一行一行删。truncate table xixi;  #物理删除，效率高。根据指定的条件删除语句mysql> delete from xixi where name=‘zhangsan‘;如：从表中找出age大于50的进行降序排列，删除第一个。delete from xixi where age>50 order by age desc limit 1;删除之后查看下mysql> select * from  test2;在mysql命令加上选项-U后使用update与delete命令的时候不加where 条件不会执行。

Select Common statements

command syntax Select field 1, Field 2, ... from table name [where expression and expression]-where select, from, where is not arbitrarily changed, is a keyword, supports case. View all information in a table select * from Xixi; View the ID in the table and NameSelect id,name from Xixi; View the information with ID equal to 2 select Id,name from Xixi where id=2; view name is Zhangsan record select Id,name from XI Xi where name= ' Zhangsan '; View records with IDs greater than 2 select Id,name from Xixi where id>2; view records with IDs greater than 2 and less than 4 select Id,name from Xixi where Id>2 and id<4; See all data in the table with a value greater than or equal to 5 and less than or equal to 10 select * from Xixi where ID >= 5 and <= 10; View data for all IDs equal to 5 or equal to 10 in the table select * From Xixi where id = 5 or id = 1 View ID is not equal to data between 5 and 10 select * from Xixi where ID not between 5 and 10select * from Xixi where I    D < 5 or ID > 10;like with wildcards using a query% to denote any character of any length, _ represents any single character, and also supports regular expression select * from Xixi where name like ' a% '; --View all data starting with a in the name field using the In keyword to specify a list such as: Find all data for any row with ID equal to 5, 6, 7 select * from Xixi where ID in (5,6,7), select * from Xixi where ID Not in (5,6,7);   --not in that is to take the meaning of the limit specified line select Id,name from Xixi limit 2; --show the first two lines select Id,name from Xixi limit 2, 3; --Displays three rows after the 2nd line ASC forward sort view (no default is forwardSort) Select Id,name from Xixi order by ID ASC;  --View ID and Name field to sort by ID field desc Reverse sort View Select Id,name from Xixi order by id DESC;    --View the ID and Name fields and reverse Sort by ID field select * from Xixi ORDER by id DESC; --View all and reverse Sort by ID field display distinct remove duplicate row display in the Age field of the--xixi table, select distinct age from Xixi;

Select Group Query

一些聚合函数的常用示例group by 分组     count()统计指定列的数量avg() 求指定列平均值sum（）求指定列的和min（）显示指定列的最小值max（）显示指定列的最大值count（）显示指定列中非null值的个数group_concat()分组显示指定列的值查询students表中的以性别为分组，求出分组后的年龄之和select gender，sum（age）from student group gender         --gender性别  --age年龄查询student表中以classid分组，显示平均值大于22的classidselect?classid,avg(age)?as?avgage?from?student?group?by?classid?having?avgage?>?22;查询student表中以性别字段gender分组，显示各组中的年龄大于18的学员的年龄总和select?sum(age)?from?students?where?age?>?19?group?by?gender;

Summary of Common SQL statements