时间：2021-07-01 10:21:17 帮助过：6人阅读
约束：
1、主键约束 唯一、非空 primary key 2、外键约束 foreign key 3、唯一约束 unique 4、非空约束 not null 5、默认值约束 default 6、自增长 auto_increment DDL、DCL、DML语句： DDL： 数据定义语句 create、drop、alter DML： 数据操纵语句 select、insert、update、delete DCL： 数据控制语句 grant 建表 create table student ( id int primary key auto_increment, name varchar(20) not null , phone varchar(11) unique not null, sex tinyint default 0, addr varchar(50), brith datetime default current_timestamp, index(name) ); create table score ( id int primary key auto_increment, score float not null, sid int not null ); create table student_new like student; -- 快速创建一个和student表结构一样的表 修改表： alter table student add class2 int not null; -- 增加字段 alter table student drop addr; -- 删除字段 alter table student change name new_name varchar(20) not null; -- 修改字段 alter table student modify name varchar(30) ; 删除表： drop table student； 清空表： truncate table student; -- 自增长id会重新开始 其他操作： show tables；-- 查看当前所有表 show create table student; -- 查看建表语句 desc student; -- 查看表结构 数据操作： 增： insert into student values (‘‘,‘python‘,‘11111111111‘,0,‘北京‘,‘2019-01-03 18:39:23‘); --写全 insert into student (name,phone) values (‘mysql‘,‘12345678901‘); -- 指定字段 insert into student (name,phone) values (‘mysql1‘,‘12345678902‘),(‘mysql2‘,‘22345678901‘); --多条 insert into student_new select * from student; -- 把一个表的数据快速导出到另外一个表 修改： update student set name=‘mysql3‘ ; --修改全表数据 update student set name‘mysql2‘,sex=1; --修改多个字段 update student set name=‘mysql3‘ where id = 1; #指定修改某条数据 删除： delete from student; --整表数据删除 delete from student where id = 3; --指定数据删除 查询： 基本查询 select * from student; select id,name,addr from student; --指定字段 select id as 编号, addr 地址 , name 姓名 from student; --字段加别名 where条件 select * from student where id=1; --where条件 >,<,>=,<=,!=,<> select * from student where id in (1,2,3) and id != 5; -- in和and条件 select * from student where id between 1 and 5; -- 范围 select * from student where id between 1 and 5 or id > 10; -- or 排序 select * from student where id between 1 and 5 order by id desc; -- 一起用的话，order by必须写在where条件后面 select * from student order by id desc ; -- 降序排序，按照id，升序的话是asc select * from student order by id,name asc ; -- 升序，按照id和name排序，asc可以省略不写 分组 select * from student group by sex; -- 按照某个字段分组，可以写多个字段 select * from student group by sex having addr !=‘北京‘; select * from student where id >5 group by sex having addr !=‘北京‘; -- 如果有where条件，必须写在group by前面，group by后面不能再写where条件，如果有条件必须用having子句|
limit
select id as 编号, addr 地址 , name 姓名 from student limit 2; -- 前N条数据
select id as 编号, addr 地址 , name 姓名 from student limit 1,5; -- 从第一行开始，向后取5行，不包含第一行的数据
select * from student where id >0 group by sex having addr !=‘北京‘ limit 5; -- limit必须写在最后面
select * from student where id >0 group by sex having addr !=‘北京‘ order by id desc limit 5; -- limit必须写在最后面
#如果一个sql里面有where、group by、排序、limit，顺序一定是1、where 2、group by 3、order by 4、limit
聚合函数
select count(*) from student; -- 多少条数据
select count(addr) from student; -- 某个字段不为空的有多少条
select count(*) 人数 ,sex 性别 from student group by sex; -- 多少条数据
select avg(age) from student; -- 平均值
select sum(score) from score; -- 和
select min(score) from score;
select max(score) from score;
子查询
select * from student where id in (select sid from score where score >= 60);
多表查询
select * from student a ,score b where a.id = b.sid and a.score>90;
select a.name,b.score,a.class2 from student a ,score b where a.id = b.sid and a.score>90;
select a.name ,b.score,a.class2 from student a inner join score b on a.id = b.sid where a.score > 90;
select a.name ,b.score,a.class2 from student a left join score b on a.id = b.sid where a.score > 90;
授权
GRANT ALL privileges ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
GRANT ALL privileges ON byz.* TO ‘byz‘@‘%‘ IDENTIFIED BY ‘123456‘;
flush privileges;
|
数据库相关
标签：foreign phone sql max add 浮点 odi def cat