Summary of Common SQL statements

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.