MySQL数据库的增删改查

 create table if not exists student(
     id int primary key auto_increment comment 'id',//只有主键才可以用自增,主键唯一
     age int check(age>=0 and age<=100) not null comment '年龄',//check用来限制
     name varchar(10) unique not null comment '名字',
     gender char(1) not null comment '性别',
     Snumber int not null unique
 );
 ​

 insert into [表名] (字段1, 字段2,....) value (value1, value2, ...);
 ​
 insert into [表名] (字段1, 字段2, ....) values
 (value1, ...),
 (value2, ...),
 (value3, ...);
 - 插入两条记录，value_list 数量必须和指定列数量及顺序一致
 INSERT INTO exam_result (id,name, chinese, math, english) VALUES
  (1,'tom', 67, 98, 56),
  (2,'jum', 87.5, 78, 77),
  (3,'lim', 88, 98.5, 90),
  (4,'tim', 82, 84, 67),
  (5,'huy', 55.5, 85, 45),
  (6,'sun', 70, 73, 78.5),
  (7,'ming', 75, 65, 30);

 alter table [table_name] add [column_name] [数据类型] [约束];
 alter TABLE employee add nickname varchar(20) comment '昵称';-- 添加表头

 delete from [tabel_name] where[];
 delete from user where uname='tom';
 delete from user;//删除整张表

 alter table [table_name] drop [column_name];
 alter table user drop uid;

 drop database [database_name];
 drop database web;
 truncate table employer;-- 删除表和数据,并重新创建表

 update [table_name] set [修改内容1,修改内容2,...] where []
 update employer set name='Lucy',age=20 where id = 1 limit 0,1;
 update employer set age=10 where id=10;

 ALTER TABLE tuser CHANGE name user_name varchar(32) DEFAULT NULL COMMENT '姓名';
 #  ALTER TABLE 表名  CHANGE 旧字段名 新字段名 新数据类型;
 ​
 alter table employee change nickname username varchar(30) comment '昵称';-- 重命名字段
 alter table employee rename to employer;-- 重命名表名
 ALTER TABLE tuser MODIFY name varchar(32) DEFAULT NULL COMMENT '姓名';
 # ALTER TABLE 表名 MODIFY  字段名  数据类型;
 ​

 alter table [table_name]
     add [新列名][数据类型][完整约束];
     drop [列名];
     rename column [列名] to [新列名];
     
     
 ALTER TABLE user
 MODIFY COLUMN name VARCHAR(20);-- 改变类型

 以下函数都是对'列'进行操作
 count 对元组计数
 select count(distinct teacher='Joe') from stu;-- 计算所有老师为joe的学生数量
 select count(*) from user where age>10;-- 对所有满足年龄打印10岁进行计数 
 SUM 求和
 select sum(dollar) from user;-- 将所有dollar列中的元素进行相加
 AVG 求平均值
 select avg(math) from student;-- 对整个班级的数学分进行求平均值
 MAX 求最大值 -- 求这列的最大值
 MIN 求最小值 

 -- 起别名
 select * from user u where name="Tom";
 -- 去重
 select distinct name from user where age between 10 and 90;
 -- 多个
 select distinct name from user where age between in (10,11,12);
 select name,age from user where extract(year from birthdate);-- extract()用于对日期进行year提取
 select name,major from student where extract(year from current_date)-extract(year from birthdate) between 20 and 30;
 ​
 select name from student where major not in (math,english);

 select id,name from user order by id,name desc-- 先安id顺序排列,如果id相同则安照name字典序逆序排列
 select * from user group by gender,age;-- 按照gender分组,再按age分组,如果有重复的列组值,则将其合并为一行输出
 select * from user group by teacher having avg(grade)>=90 limit 10;-- having 一般与order by同时使用,目的是筛选参与分组的元组

 limit<起始偏移量(可不写),行数>
 select * from user where age >15 and age<20 limit 0,10;-- 从第0行开始查询10行,剩下的分给后续页 

 SELECT column1, column2 FROM table1  
 UNION  
 SELECT column1, column2 FROM table2;

 SELECT name, email FROM employees  
 UNION  
 SELECT name, email FROM contractors;  

 SELECT name, email FROM employees  
 UNION ALL  
 SELECT name, email FROM contractors;  

 select * from user where name like 'Sun%'-- %替换多个字
 select * FROM USER where name like 'S__'-- _替换一个字
 select * from user where teacher not like 'Dr\_Smith' escape '\';-- 用\_来匹配_ <escape '\'>表示换码符

 select count(*),name-- 父查询
 from user where age in
 (select age from user where -- 子查询teacher='joe');--  父查询的限制对象应与子查询的查询对象一致都是age
  select * from student where major=(select major from student where name='Tom');
  select Sno from student x where grade>=(select avg(grade) from student y where y.Sno=x.Sno); 
  
  -- 带有any/all的子查询
 select name from student where birthdate 
  > any(select birthdate from student where major='math')
 and grade>90;
  -- 带有exists的子查询,exists代表'存在'返回true或false true就执行前面语句

 （8）Select
 （9）distinct 字段名1,字段名2，
 （6）[fun(字段名)]  
 （1）from 表1
 （3）<join类型>join 表2 
 （2）on <join条件> 
 （4）where <where条件> 
 （5）group by <字段> 
 （7）having <having条件> 
 （10）order by <排序字段> 
 （11）limit <起始偏移量,行数>