SELECT [DISTINCT] [TOP n] select_list
[INTO new_table_name]
FROM table_list
[WHERE search_conditions]
[GROUP BY [ALL] group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [ASC | DESC] ]

1. 查询student所有数据都在表中

 SELECT *FROM student; 

2. select_list
   查询student在表中确定列数据，id、name、numid列全部数据

 SELECT id,NAME,numid FROM student; 

3. order by …[ASC/DESC]
   查询student表中的数据按照一列数据的升降序列排序

 SELECT id,NAME,numid FROM student ORDER BY numid; #按numid升序排列 ASC 默认升序  SELECT id,NAME,numid FROM student ORDER BY numid DESC; #按numid降序排列 

4. union
   使用union只能对最终结果进行排序。UNION 操作符允许您将两个或两个以上 SELECT 句子的结果组合成结果集

 SELECT * FROM student WHERE numid LIKE '2019%' AND num like '44%' GROUP BY id UNION SELECT * FROM student WHERE money>40 ORDER BY money; #结合两个筛选结果，删除重复行 

5. GROUP BY 子句和 ALL 关键字
   只有在 SELECT 语句还包括 WHERE 子句时，ALL 关键词有意义。

6. DISTINCT
   DISTINCT 关键字可以从结果集中删除重复行

 SELECT DISTINCT numid FROM student; 

5. INTO new_table_name
   创建和复制新旧表

 SELECT * INTO new_student FROM student;    CREATE TABLE new1_student SELECT * FROM student; #新表不存在 既复制表结构，也复制表内容；new_table中没有了old_table中的primary key,Extra,auto_increment等属性    CREATE table new2_student LIKE student; #数据记录为空    insert into new2_student select * from student; #需要新表存在 将旧表数据复制到新表 不复制表结构，只复制表内容    create table new3_student as select * from student where 1=2; #数据记录为空 只复制表结构，不复制表内容 

6. WHERE 子句筛选
   Between …and …
   通配符:%_、[]、[^]

 SELECT * FROM student where numid BETWEEN '2019082408' and '2019082450';#  #字符串变量需要单引号，数字和布尔变量不需要单引号，日期需要#。  #替换变量：加双引号，双引号中间需要连接符& & ，注意所有连接符前后都有空间。  

 SELECT * FROM student WHERE numid LIKE '20190824%';  SELECT * FROM student WHERE numid NOT LIKE '20190824__';  SELECT * FROM student WHERE num LIKE '44%';#  #SELECT * FROM PERSON WHERE Name LIKE '[TJL]%' #从 "Person" 在表中找到名字的首字母T，J，L”的人  #SELECT * FROM PERSON WHERE Address LIKE '[^H]%' #从 "Person" 在表中找到地址的开头字母不是H”的人 

7. HAVING 子句选择行

 SELECT * FROM student WHERE numid LIKE '2019%' OR num like '4%' GROUP BY id HAVING money>40; 

DELETE table_or_view
[FROM table_sources ]
[WHERE search_condition ]

1. 删除全部行
   删除new1_student所有数据，

 DELETE FROM new1_student； 

2. 删除某一行

 #删除num=447行的所有数据  DELETE FROM new1_student where num=447; 

update table_name set 列=表达式
[FROM table_sources]
[WHERE search_condition]

 #修改name=那一行丽丽num为440  UPDATE new1_student SET num=440 WHERE NAME='丽丽'; 

使用 INSERT…Values 增加一行或多行数据

 #新增两行new1_student数据  INSERT INTO new1_student(id,name,numid,num,money,DATE) VALUES(0,'lili2019082425，442，58，2021-05-08)，linda',2019082457,448，58，2021-07-08)；