安装命令 --initialize-insecure
bin目录下的mysqld
E:wupeiqimysql-5.7.16-winx64mysql-5.7.16-winx64inmysqld --initialize-insecure # 命令参考
show databases; # 查看库
create database db1; # 创建文件夹
use db1; # 进入db文件夹
create user '用户名'@'192.168.1.%'indentfied by '密码'; # 创建用户
grant select,insert,update on db1.t1 to 'alex'@'%'; # 给用户授权 db1.t1 是某个表
create table t1(列，列) engine=innodb 原子性操作 default charset=utf8 设置默认编码为utf8;
一般 primary key 都是识别id添加not null 非空属性。
create table t1( id int not null auto_increment primary key ,student_id int , constraint foreign (sutdent_id) references student(sid) );
enum ('内容','内容','content') # 荐举属性，使列只能填括号内的内容
insert into t1(student_id) values() # 插入数据行
desc t1; # 查看表的信息，
show create table t1 G; # 查看创建信息，可看自增属性
alter table t10 AUTO_INCREMENT=20; # 把自增数修改。 比如表里由自增到3，那只能修改3以后的数。
unique uq_u1 (user_id) # 索引，加速查找唯一值，可以为空
delimiter // # 修改终止符，修改后例如： select * from t1// 结束
set global log_bin_trust_function_creators=1; # 创建函数时，出错输入这个。
关于自增的设置
基于会话级别：
show session variables like 'auto_inc%'; 查看全局变量
set session auto_increment_increment=2; 设置会话步长
# set session auto_increment_offset=10;
基于全局级别：
show global variables like 'auto_inc%'; 查看全局变量
set global auto_increment_increment=2; 设置会话步长
# set global auto_increment_offset=10;
一对一：
create table department(id int auto_increment primary key , title char(20) not null , unique uq1_dep (title))engine=innodb default charset=utf8;
create table userinfo(id int auto_increment primary key , username char(10) not null , usertype int ,constraint foreign key (usertype) references department(id),unique uq2 (username) )engine=innodb default charset=utf8;
create table admin(id int auto_increment primary key , user_id int , passwrod char(16) not null , unique uq_u1 (user_id) ,constraint foreign key (user_id) references userinfo(id) )engine=innodb default charset=utf8;
多对多：
create table userinfo(id int auto_increment primary key , name char(64) not null, gender enum('男','女') )engine=innodb default charset=utf8;
create table host_table(id int auto_increment primary key , hostname char(64) not null ) engine=innodb default charset=utf8;
create table user_host_table(id int auto_increment primary key , user_id int not null,host_id int not null,constraint foreign key (user_id) references userinfo(id) , constraint foreign key (host_id) references host_table(id),unique uq1 (user_id,host_id) )engine=innodb default charset=utf8;
增
insert into tb11(name,age) values('alex',12);
insert into tb11(name,age) values('alex',12),('root',18);
insert into tb12(name,age) select name,age from tb11;
删
delete from tb12;
delete from tb12 where id !=2
delete from tb12 where id =2
delete from tb12 where id > 2
delete from tb12 where id >=2
delete from tb12 where id >=2 or name='alex'
truncate table t1;
改
update tb12 set name='alex' where id>12 and name='xx'
update tb12 set name='alex',age=19 where id>12 and name='xx'
查
select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name ='xxx';
select id,name as cname from tb12 where id > 10 or name ='xxx';
select name,age,11 from tb12;
a、条件
select * from 表 where id > 1 and name != 'alex' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)
b、通配符
select * from 表 where name like 'ale%' - ale开头的所有（多个字符串）
select * from 表 where name like 'ale_' - ale开头的所有（一个字符）
c、限制
select * from 表 limit 5; - 前5行
select * from 表 limit 4,5; - 从第4行开始的5行
select * from 表 limit 5 offset 4 - 从第4行开始的5行
d、排序
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
e、分组
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
如果对于聚合函数结果进行二次筛选时，必须用having
select num from 表 group by num having max(id) > 10
特别的：group by 必须在where之后，order by之前
f、连表
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid
无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid
A表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid
B表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid
f2、上下连表： union
# 自动去重
select id,name from tb1
union
select num,sname from tb2
# 不去重
select id,name from tb1
union all
select num,sname from tb2
g、组合
组合，自动处理重合
select nickname
from A
union
select name
from B
组合，不处理重合
select nickname
from A
union all
select name
from B
h、条件：判断显示
select course_id,max(number)as 最高分,min(number) as 最低分 , case when min(number) <60 then 0 else min(number) end from score group by course_id;
case when min(number) <60 then 0 else min(number) end
i、三元运算
if(isnull( 判断条件),0,1) # 成立则为0 不成立则1
临时表:
select cid,cname from (select cid,cname from course where number > 60 ) as B;
计算命令：
sum
avg
count
表格查询操作:
select * from t1 as s1 ,t1 as s2; # 生成 t1 乘 t1 的表格
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
# 思路，直接not in 老师的课程的话，会导致数据错误，先选出学过，然后排除学过的学生
select score.student_id,student.sname from score
left join student on student.sid = score.student_id
where score.student_id not in
(select student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '波多')) );
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
# 把course as s1 可以每次循环的时候s1.cid 再次在 表No.1 2 3 里面循环判断，达到自己需求的目的
select
s1.cid,
(select number from score as s2 where s2.course_id = s1.cid group by s2.number order by s2.number desc limit 0,1) as No1,
(select number from score as s2 where s2.course_id = s1.cid group by s2.number order by s2.number desc limit 1,1) as No2 ,
(select number from score as s2 where s2.course_id = s1.cid group by s2.number order by s2.number desc limit 2,1) as No3
from course as s1;
视图:
- 创建
create view 视图名称 as SQL
PS: 虚拟
- 修改
alter view 视图名称 as SQL
- 删除
drop view 视图名称;