修改表名
alter table 旧表名 rename as 新表名 alter table teacher rename as teacher1
增加字段
alter table 表名 add 字段名称 属性 alter table teacher add account int(8) not null default'0000000' comment'账号'
修改约束
alter table 表名 modify 字段名 属性 alter table teacher modify account varchar(10)
字段重命名
alter table 表名 change 旧字段名 新字段名 属性（可省略） alter table teacher change account account1 int(10)
删除字段
alter table 表名 drop 字段名 alter table teacher drop account1
2.修改数据
添加数据
注意：数据和字段要一一匹配
insert into [数据库名.] 表名 [(字段1,字段2)] values ('数据1','数据2')
insert into grade(年级,姓名) values ('初二','小橙')
insert into grade values ('初一','李明')
INSERT INTO `school`.`grade`(`年级`, `姓名`) VALUES ('初一', '小红')
插入多个数据
INSERT INTO `school`.`grade`(`年级`, `姓名`)
VALUES ('初一', '公主'),
('初一', '王子')
更改单个数据
update [数据库名.]表名 set 字段=新数据[，...] where 条件; 条件：等于，大于，小于，区间 update school.student set age=13 where id=4;
3.修改数据
修改所有数据，不指定条件
删除数据
delete from 表名 where 条件;
若没有条件，会删除所有数据
delete from student where id=4;
清空所有数据
truncate 表名
4.查询数据
查询所有信息
select * from 表名; select * from student;
查询某个字段
select 字段[AS 新名字][,...] from 表名[AS 新表名]; select subjectname as 科目 from subject;
去重
select distinct 字段 from 表名; select distinct studentno from result;
where
联表查询
right
left
inner
5.分页和排序
排序
order by 字段名 asc(升序)/desc(降序) select s.studentno as `学号`,studentname as `姓名`,subjectname as `科目名称`,studentresult as `成绩` from student as s left join result as r on s.studentno=r.studentno left join subject as sub on r.subjectno=sub.subjectno order by studentresult asc;
分页
limit 第几条开始,显示几个; select s.studentno as `学号`,studentname as `姓名`,subjectname as `科目名称`,studentresult as `成绩` from student as s left join result as r on s.studentno=r.studentno left join subject as sub on r.subjectno=sub.subjectno order by studentresult desc limit 0,3;
先选择（where）,在排序（order by）
5.函数
聚合函数
统计数量
select COUNT(字段名) FROM 表名;--会忽略null * 是所有
其他
- 求和
select sum (字段名) FROM 表名;
- 平均
avg
- 最高，最低
max/min
分组
select subjectname ,avg(studentresult),max(studentresult) from result r INNER JOIN subject sub on r.subjectno=sub.subjectno group by== r.subjectno
过滤
select subjectname ,avg(studentresult) as 平均分,max(studentresult) from result r INNER JOIN subject sub on r.subjectno=sub.subjectno group by r.subjectno HAVINg 平均分>70
6.事务 7.错误解决方案
1205 - Lock wait timeout exceeded; try restarting transaction
https://blog.csdn.net/u012248802/article/details/77866019?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162926990516780269816425%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=162926990516780269816425&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduend~default-1-77866019.first_rank_v2_pc_rank_v29&utm_term=1205±+Lock+wait+timeout+exceeded%3B+try+restarting+transaction&spm=1018.2226.3001.4187
