修改表alter table tb_name;
1.添加字段alter table tb_name add 字段名字 字段类型 约束条件 [first/after 字段名称]
1>添加用户名字段
alter table test add username varchar(20);
2>将字段test添加到表中第一个字段
alter table test add test varchar(20) first;
3>在id之后添加年龄字段
alter table test add age tinyint unsigned after id;
4>一次添加多个字段
alter table test add test1 int unsigned not null,
add test2 tinyint unsigned not null default 12,
add test3 int;
2.删除指定字段alter table tb_name drop [column] 字段名称;
将test1字段删除
alter table test drop column test1;
将test字段删除
alter table test drop test;
将test2,test3,字段删除,添加test4
alter table test drop test2,drop test3,add test4 int;
3.修改列定义modifyalter table tb_name modify 字段名称 字段类型 约束条件 [first|after 字段名称];
将test4字段变为varchar(20) not null default 'this is a test' first
alter table test modify test4 varchar(20) not null default 'this is a test' first;
4.change修改字段名称alter table tb_name change 原字段名称 新字段名称 字段类型 约束条件 [first|after 字段名称]
将test4修改为test2
alter table test change test4 test2 varchar(32);(必须给新名称指定类型）
将test2修改为test2 数据类型 int not null default 100
alter table test change test2 test2 int not null default 100;
将id字段修改为主键并自增长
alter table test change id id int unsigned not null auto_increment key;
将test2的字段位置改在username 之后
alter table test change test2 test2 int unsigned not null default 100 after username;
5.添加主键add primary keyalter table tb_name add primary key(字段名称）
create table test1(
id tinyint);
将id字段添加为主键
alter table test1 add primary key(id);
alter table test1 add constraint symbol primary key(id);
5.删除主键drop primary keyalter table tb_name drop primary key;
删除test1的主键
alter table test1 drop primary key;
create table test2(
id tinyint unsigned auto_increment key
);
删除test2主键
alter table test2 modify id tinyint unsigned
alter table test2 drop primary key;
6.添加唯一alter table tb_name add unique [key|index][index_name](字段名称)
age字段唯一
alter table test add unique (age);
将username字段添加成唯一，同时指定索引名称
alter table test add unique key un_username (username);
8删除索引drop index index_namealter table tb_name drop index index_name;
age索引删除
alter table test drop index age;
username字段唯一索引删除
alter table test drop index un_username;
9.添加默认约束alter table tb_name alter 字段名称 set default 值；
username字段添加
alter table test alter username set default 'king';
10.删除默认约束alter table tb_name alter 字段名称 drop default;
username删除
alter table test alter username drop default;
11.修改表名alter table tb_name rename [to|as] new_name;
rename table tb_name to new_name;
test表命名kaikeba
alter table test rename to kaikeba;
kaikeba表命名test
alter table kaikeba rename test;
test表命名ran
rename table test to ran;
12修改表的存储引擎alter table tb_name engine=存储引擎名称；
alter table test engine=myisam;
13.修改自增长的值alter table tb_name auto_increment=值；
ran表auto_increment=100
alter table ran auto_increment=100;
记录的操作dml（增删改）1>插入记录的操作insertinsert [into] tb_name [(字段名称……)]values(值|exp|)
default……）
create table if not exists user(
id smallint unsigned auto_increment key,
username varchar(20) not null unique,
age tinyint unsigned
)engine=innodb charset='utf8'
--插入记录
insert into user values(1,'2e',2);
指定字段名称
insert user(username,age) values('dsa',9);
insert user(id,username,age) values(null,'dsesa',9);
insert user(id,username,age) values(default,'sda',4+3);
一次插入多听记录
insert [into] tb_name [(字段名称……)]values(值……)，(值……)
insert into user(username,age) values ('a',2),('w',9),('21',8);
--通过set插入记录
insert [into] tb_name set 字段名称=值，……；
insert user set username='sadfl',age=9;
2>更新记录updateupdate tb_name set 字段名称=值|exp|default [where 条件]
不加条件会更新表中所有记录
--user表中第一用户年龄改为45
update user set age=45 where id=1;
update user set age=45,username='iopo' where id=1;
添加默认值
alter table user alter age set default 90;
--将user表中第三个用户的年龄改为默认值
update user set age=default where id=3;
3>删除记录deletedelete from tb_name [where 条件]
--删除id为1
delete from user where id=1;
--删除所有记录
delete from user;
alter table user auto_increment=1;
重置auto_increment
5>彻底清空一个表truncate,清空表时不用加条件会重置auto_increment,
truncate table tb_name;
truncate table user;
查询数据selectselect exp[,..] from tb_name
[where 条件]
[group by 分组[having 子句对分组结果进行二次筛选]]
[order by 排序]
[limit 限制显示条数]
select * from user;
*代表所有字段
select 字段名称，字段名称 from tb_name;
select username from user;
select username,age from user;
select 表名.字段名称，...from tb_name;
select user.id,user.age,user.username from user;
给字段起别名，给表起别名
select 字段名称 [as] 别名，...from tb_name [as] 别名
select id as '编号',username as '用户名',age as '年龄'from user;
别名时尽量不要省略as
select u.id,u.username,u.age from user as u;
1》where表达式比较运算符
select 字段名称...from tb_name
where 条件；
比较运算符
> >= !=
--user表插入记录
insert user values(11,'ewrr',15);
insert user values(6,'ewr44r',14);
insert user values(7,'ew56rr'，45);
insert user values(8,'euyuyr',55);
insert user values(9,'ewrr',4);
insert user values(12,'ewxr4r',5);
insert user values(4,'ewsrr',9);
--用户名为'sad'
select * from user where username='ewrr';
select * from user where id=6;
--年龄大于18
select * from user where age>=18;
不等于
select * from user where age!=123;
select * from user where age123;
等于
select * from user where age123;
//可以检测某个值是否为空
insert user values(36,'test1',null);
select * from user where agenull;
select * from user where age=null;
--通过is null 或者is not null检测是否为空
select * from user where age is null;
select * from user where age is not null;
--年龄18-28
between...and或者not between...and
select * from user where age between 18 and 28;
select * from user where age not between 18 and 28;
in在集合中的形式
select * from user where age in(18 ,12,23,35,28);
select * from user where age not in(18 ,12,23,35,28);
通过like实现模糊查询
%：代表0个1个或多个任意字符；
—：1个任意字符
--包含张/w的用户
select * from user where username like '%张%';
select * from user where username like '%w%';
--以张开头
select * from user where username like '张%';
查询用户名长度为3位的用户
select * from user where username like '___';
select * from user where username like '_e%';
逻辑运算符
and or
用户名为king并且年龄为28的信息
select * from user where username='king' and age=28;
用户名包含k或者年龄小于50的用户
select * from user where username like '%k%' or age
2》分组查询create table if not exists department(
id tinyint unsigned auto_increment key,
depname varchar(20) not null unique);
insert department(depname) values('摄影部');
insert department(depname) values('视频部');
insert department(depname) values('教学部');
insert department(depname) values('督导部');
create table if not exists employee(
id int unsigned auto_increment key,
username varchar(20) not null,
age tinyint unsigned default 18,
addr varchar(50) not null default '北京',
salary float(6,2) not null default 0,
sex enum('男','女','保密'),
depid tinyint unsigned
);
insert employee(username,age,addr,salary,sex,depid)
values('张三',12,'上海',6234.56,'男',1);
insert employee(username,age,addr,salary,sex,depid)
values('张三风',22,'北京',1563.51,'男',2);
insert employee(username,age,addr,salary,sex,depid)
values('章子怡',14,'云南',6454.14,'女',1);
insert employee(username,age,addr,salary,sex,depid)
values('丽丽',41,'广州',1201.45,'男',2);
insert employee(username,age,addr,salary,sex,depid)
values('李成',52,'深圳',6914.86,'男',2);
insert employee(username,age,addr,salary,sex,depid)
values('程程',25,'成都',5631.12,'女',3);
insert employee(username,age,addr,salary,sex,depid)
values('白静',65,'南阳',4823.45,'男',4);
insert employee(username,age,addr,salary,sex,depid)
values('程秀碧',48,'广汉',5523.36,'女',4);
按性别分组
select * from employee group by sex;
按地址分组
select * from employee group by addr;
按部门编号
select * from employee group by depid;
根据性别和地址分组
select * from employee group by sex,addr;
select * from employee group by addr,sex;
查询结果跟顺序有关
--分组查询配合group_concat()
--按性别分组，得到每个组中人员的名称
select * ,group_concat(username) from employee group by sex;
select * ,group_concat(username) as usernames from employee group by sex;
--配合聚合函数使用
count():统计记录数目
sum():求字段和
avg():求字段平均值
max():
min():
--统计员工表中员工数目，以及薪水总和、最大最小值
select id as '编号',username as '用户名',count(*) as 'totalnums',sum(salary) as '总薪水'
,avg(salary) as '平均薪水',max(salary) as '最高薪水',min(salary) as '最低薪水'from employee;
select id as '编号',username as '用户名',count(*) as 'totalnums',sum(salary) as '总薪水'
,avg(salary) as '平均薪水',max(salary) as '最高薪水',min(salary) as '最低薪水'from employee\g;
count(*),count(字段名称):
*无论字段值是否为null都统计
字段名称：不统计null记录。
insert employee(username,age,addr,salary,sex,depid)
values('程秀碧',null,'广汉',5523.36,'女',4);
select count(*) from employee;
select count(age) from employee;
--按性别分组，统计出每个组找那个年龄最大最小，最高薪水，每个组人数，以及平均薪水
select id,sex,max(age) as max_age,min(age) as min_age,max(salary) as max_salary,count(*) as total,avg(salary) as avg_salary from employee group by sex;
select id,sex,max(age) as max_age,min(age) as min_age,max(salary) as max_salary,count(*) as total,avg(salary) as avg_salary,group_concat(username) as users from employee group by sex;
3》having子句二次筛选--按照性别分组
select id,sex,count(*) as total from employee group by sex;
分组后组中人数大于3的有
select id,sex,count(*) as total from employee group by sex having count(*)>6;
--按地址分组
select id,addr,min(age) as min_age from employee group by addr;
分组后要求组中最小人员年龄大于18；
select id,addr,min(age) as min_age from employee group by addr having min(age)>25;
select exp from tb_name
[where 条件]
[group by 分组[having对分组结果进行二次筛选]]
[order by 字段名称]
[limit 限制显示条数 ]
4》对分组结果排序order by 字段名称|位置 asc|desc;
--按照id由小到大排序
select * from department order by id asc;
select * from department order by id ;
默认升序，所以查询结果一样
select * from department order by id desc;
--按位置
select * from department order by 1 desc;
select * from employee order by age desc;
select * from employee order by 3 desc;
当有相同年龄时，按出现顺序进行排序
--按多个字段排序
order by 字段名称1，字段名称2
select * from employee order by age desc,id desc;
select * from employee order by id desc,age desc;
查询结果与字段名称的顺序有关
5》limitlimit 值：
代表显示前几条记录
limit 偏移量，显示记录条数
实现分页的核心是通过limit语句
--显示前5条
select * from employee limit 5;
select * from employee order by id desc limit 5;
第一条记录偏移量为0
select * from employee limit 0, 5;
--下一页，显示后五条
select * from employee limit 5, 5;
select * from employee limit 10, 5;
--所有条件
select *,group_concat(username) from employee where id>2 group by sex having count(*)>6 order by age desc,id desc limit 0,5;
子查询1》通过in引发的子查询select * from employee where depid in(select id from department);
select * from employee where depid not in(select id from department);
2》由于比较运算符引发的子查询select * from employee where depid=(select id from department where depname='摄影部');
--
select depname from department where id in(select depid from employee where age>25);
--摄影部年龄最小/大值
select min(age) from employee where depid=1;12
select max(age) from employee where depid=1;14
--年龄大于摄影部年龄最大的员工
select * from employee where age>(select max(age) from employee where depid=1);
3》exist(内层有结果才执行外层）select id from department where depname='国防部';空
select * from employee where exists(select id from department where depname='国防部');
select * from employee where exists(select id from department where depname='督导部');
4》由any|some|all修饰的比较运算符>=any|some:大于子查询中的最小值
>=all:大于子查询中的最大值
select * from employee where depid>any(select id from department);depid>2
select * from employee where depid>some(select id from department);depid>2
select * from employee where id>all(select id from department);id>5
=any|some:相当于in,不能写不等于
select * from employee where depid=any(select id from department);全部成员
all:相当于not in
select * from employee where depidall(select id from department);空
等于：只能any|some;不等于：all只能。
开发中很少用子查询，效率不高，必须现执行子查询，再执行外层查询。
连接查询连接查询：内连接+外连接
外连接：左连接+右连接
1》两表查询--员工表中员工姓名、编号、性别、年龄、所属部门名称
select e.id,e.username,e.sex,e.age,d.depname from employee as e,department as d;得到两边笛卡尔积
由于哪个桥梁连接起来的
员工表中的depid等于部门表的id
select e.id,e.username,e.sex,e.age,d.depname from employee as e,department as d where e.depid=d.id;
1>内连接
[inner|cross] join
通过on连接条件
查询出两个或多个表都满足的结果
--员工表所属部门名称
select e.id,e.username,e.sex,e.age,d.depname from department as d inner join employee as e on e.depid=d.id;
where通常用于条件塞选
--员工年龄大于25
select e.id,e.username,e.sex,e.age,d.depname from department as d inner join employee as e on e.depid=d.id where e.age>25;
2》多表查询：create table if not exists provinces(
id tinyint unsigned auto_increment key,
pname varchar(10) not null unique
);
insert provinces(pname) values('四川'),('云南'),('陕西'),('广东');
alter table employee add addr1 tinyint unsigned default 1;
update employee set addr1=2 where id in(2,4,6,8);
alter table employee change addr1 pid tinyint unsigned default 1;
update employee set pid=2 where id in(2,4,6,8);
--查询员工所属省份
select e.id,e.username,p.pname from employee as e join provinces as p on p.id=e.pid;
--部门名称、省份名称、用户的信息
select e.id,e.username,p.pname,d.depname from employee as e join provinces as p on p.id=e.pid join department as d on e.depid=d.id;
不断在后面加join跟上on条件。
select e.id,e.username,p.pname,d.depname from employee as e join provinces as p on p.id=e.pid join department as d on e.depid=d.id limit 0,3;
insert employee(username,age,addr,salary,sex,depid,pid) values('test5',25,'北京',6542,'女',6,9);
插入成功了，但这条数据部合法。省份只有4个，部门只有4个，没有保证记录完整性与一致性，那么就应当通过外键来实现。
外键依赖已存在表的主键来设置外键。
作用：保证记录完整性与一致性
创建外键注意事项：
父表与子表必须使用相同的存储引擎，禁止使用临时表；
存储引擎只能为innodb
子表外键必须关联父表主键
外键列与参照列应具有相似的数据类型。
1》创建外键
创建表时
create table if not exists user(
id tinyint unsigned auto_increment primary key,
username varchar(20) not null unique,
pid tinyint unsigned ,
foreign key(pid) references provinces(id)
);
constraint 约束名称 foreign key(外键列) references 主表（字段）；
create table if not exists user1(
id tinyint unsigned auto_increment primary key,
username varchar(20) not null unique,
pid tinyint unsigned ,
constraint user1_fk_provinces foreign key(pid) references provinces(id)
);
insert user(username,pid) values('dsfsa',1);
insert user(username,pid) values('zdx',2);
insert user(username,pid) values('ssa',3);
insert user(username,pid) values('hga',4);
insert user(username,pid) values('hgre',5);省份没有id=5,插入失败
delete from provinces where id=4;不成功，id=4的省份还有用户，应当先删除用户，在删除省份。
select * from user;
delete from user where pid=4;
delete from provinces where id=4;
delete from provinces where id=3;一样不成功，原因同id=4，有外键约束存在：
下面删除外键：
alter table tb_name drop foreign key 约束名称；
--查看user约束名称
show create table user;
--删除外键约束
alter table user drop foreign key user_ibfk_1;
--现在做删除操作，成功
delete from provinces where id=3;
添加外键
alter table tb_name add constraint 约束名称 foreign key(字段） references 主表（字段）；
alter table user add constraint user_fk_provinces foreign key(pid) references provinces(id);不成功
delete from user where pid=3;
alter table user add constraint user_fk_provinces foreign key(pid) references provinces(id);删除pid=3的记录后添加成功
先删除子表记录，再删除父表记录
创建外键的时候可以指定一下，当我们删除父表记录时，子表应当进行什么样的操作。
cascade,级联
set null(保证列没有非空约束)
restrict
no action效果同restrict
on update|on delete cascade|set null|restrict|no action
--测试cascade
alter table user drop foreign key user_fk_provinces;
show create table user;
alter table user add constraint user_fk_provinces foreign key(pid) references provinces(id) on delete cascade;
delete from provinces where id=2;实际上先删除user中的pid=2的记录，再删除provinces表中的记录。
实际开发中采用外键思想进行操作，而不是纯的物理外键
外连接left|right [outer] join on 条件
left:显示左表中的全部记录和右表中符合条件的记录
right:显示右表中的全部记录和左表中符合条件的记录
--内连接
select e.id,e.username,d.depname from employee as e join department as d on e.depid=d.id;
--外连接
select e.id,e.username,d.depname from employee as e left join department as d on e.depid=d.id;
当右表中没有满足条件时，null填充
select e.id,e.username,d.depname from employee as e right join department as d on e.depid=d.id;
内连接查询两个或多个表中都符合条件的记录
insert department(depname) values('测试部门');
select e.id,e.username,d.depname from employee as e right join department as d on e.depid=d.id;
联合查询多个表中的记录和在一起
union all，简单地将查询结果合并到一起
union会去掉重复记录
select * from employee;9
select * from user;1
select username from user union all select username from employee;
联合查询，字段顺序、字段数目一定要相同
select id,username,age from employee union all select id as uid from user;不成功
select id,username,age from employee union all select id as uid,username as uname,1 from user;
多表更新和删除--将employee表中的addr存为省份表中的id
update employee as e join provinces as p on e.pid=p.id set e.addr=p.id;
select * from employee;
--删除所有所在省份在省份表中省份的人
delete e from employee as e join provinces as p on e.pid=p.id;
多表操作，注意表和表间的连接条件
删除数据表
一张表
drop table tb_name;
多张表
drop table tb_name1,tb_name;