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 求最小值

条件查询(where,in)

-- 起别名
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);

分组查询(order by,group by)

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)

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

联合查询/集合查询(union)

select 查询结果是元组的集合,可用union进行结果的集合操作,相当于把多个查询结果进行==连接起来==输出

UNION规则

• UNION必须由两条以上的SELECT语句组成，语句之间用关键字UNION分割。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。
• 如果取出来的数据不需要去重，使用UNION ALL。

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

假设我们有两个表：employees 和 contractors，它们都有 name 和 email 字段。我们希望获取所有人的名字和电子邮件。

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

如果我们希望包括所有的电子邮件（即当它们重复时也要显示），我们可以使用 UNION ALL：

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

模糊查询(like)

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 <起始偏移量,行数>