常用sql知识

WINDOWS SERVER
net start  MYSQL
net stop MYSQL
CREAT USER 'ALEX'@'%' IDENTIFIED BY  '123456';
授权：
www.cnblogs.com/wupeiqi/articles/5713315.html

grant select,insert,update  on db1.*    to  'root'@'%';
grant all privileges on db.* to 'root'@'%';
revoke   取消授权
show databases；
use pyhton;
show tables;
select * from table1;
desc table1;
----------
sql 规则
--------
create database db1 default charset utf8;
show databases;
create database db2;
create table t1(
    id int unsigned null auto_increment primary_key,#自增必须是一个key
    num decimal(总位数，小数位数)，
    name char(10)  default 1,
    ) engine=innnodb default charset=utf8;
insert into t1 (id,name) vlues (1,'song');
innodb  支持事务，原子性 支持回滚     myisam  速度快 不支持事务
primary_key  表示约束 不能重复 并不能为空  加速查找
auto_increment  中有一个   必须搭配 primary_key    else: error  must a key
删除表
delete from t3;  清空数据   id 从自增开始
truncate  table t3;清空数据  快   id  start with 1；
int
tinyint
drop  table t3;直接删除表
delete from t3  where id<6;
update t3  set age =18  where age = 17;
float    double  不精准
decimal   always  精准  按字符串存储
char                                            255
varchar   可变的 节省空间  速度没有char的快      255
text                         65535
date
time
datetime   年月日时分秒
timestamp   时间戳
存文件时  存路径
枚举：
size enum['a','b','c']  限定范围
集合：
cap  set（'a','b','c'）    插入时任意组合
外键：
用户表      不固定的加一张表
一个中文三个字节
constraint   创建约束
create table userinfo(
uid biggint autto_increment primary_key,
name varchar(32),
department_id int,
#创建外键约束
constraint fk_user_depart foregin key ("department_id") references  department('id')

)engine=innodb default charset=utf8;

create table department(
id bigint auto_increment primary_key,
title char(15)
);
enigine=innodb default charset=utf8
------------------------------上节回顾-----------
补充：外键
create table t1(
nid int(11) not null auto_increment primary key,
pid int(11) default null,
num int(11) default null
)engine=innodb default charset=utf8;
---------
一张表只有一个主键 一个主键可以是两列
不常用
create table t1(
nid int(11) not null auto_increment ,
pid int(11) not null,
num int(11) default null，
primary key （nid,pid）  #主键不为空
)engine=innodb default charset=utf8;
------
create table t2（
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
constraint fk_t1_t2  foreign key(id1,id2) references t1(nid,pid)
）auto_increment=5;  #下个id
------------------------------数据行--------------------
增删改查
insert into t1(name,age) values('song',18);
delete from t1;  id  zeng
truncate table t1;
update t1 set name='root';
select  id,name  from t1;
show create t1;
show create t1\G;
------------
alter table t1  auto_increment=5;  新增的数据id
------------而sql server 可以基于table
设置自增的步长（只能基于会话级别）
登陆一次 就是会话
show sessions  variables like 'auto_inc%'; 查看全局变量
set sessions auto_increment_increment=2; 基于会话 关闭后没有了
set sessions auto_increment_off_set=2;设置起始值
基于全局的  改变后  设置会话时  都会按照  全局
set global auto_increment_increment=2;
--------------------------------------
今日访谈
唯一索引
create table  t1(
id int auto_incremen primarykey,  不可以为空
num int,
xx int,
unique uq1  (num,xx)       #联合索引  唯一索引可以有一个值为空
);
外间的变种
用户 1 alex   1
     2 root   3
     3 egon   2
     4 laoyao 4
部分 1 服务
     2 保安
     3 公关
     ======>一对多
b:用户表和博客表
用户 1 alex
     2 root
     3 egon
     4 laoyao
博客表：                fk + 为一索引（user id and  unique） administered
  1     /yuanchenqi/    4
  2     /alex/          1
  3     /xxxxxxxx/      3
  4     /xxxxxxxx/      2

======>一对一
用户表 （百合网）   相亲记录表
用户表  id     name        gender
        1      fangshao       man
        2      qinbing        man
        3      alex           woman
        4      xioabai         man
        6      chengtao        woman
        7       gangnian        man

                fk          fk
相亲表     id  user1       user2
            1   1           2
            2   1           4
            3   2           6
            4   3           4
            5   7           2
            6   4           7
 ========》多对多  双向的一对多
        id     name        gender
        1      fangshao       man
        2      qinbing        man
        3      alex           woman
        4      xioabai         man
        6      chengtao        woman
        7       gangnian        ma

  主机表
  id  主机名
    1   c1
    2   c2
    3   c3
    4   c4
    5   c5


    用户主机关系表    联合唯一  （不用再出现一次）     user_id       host_id
                                                             1           1
     id     user_id       host_id
    1       1           1
    2       2           3
    3       1           2
    4       4           3
    5       1           4
    =============================
    一对一
    create table userinfo(
    id int auto_inncrement primary key,
    name char(10),
    gender  char(10),
    email   varchar(64)
    )engine=innodb default charset=utf8;

    create table admin(
    id int not null auto_increment primary_key,
    username varchar(64) not null,
    password  varcahr(64) not null,
    user_id int  not_null,
    uniques uq_ul   (user_id),
    constraint  fk_admin_ul foreien key  (user_id) preferens  userinfo(id)
    )engine= innodb default charset=utf8;
    ------------------------
    多对多
      create table userinfo(
    id int auto_inncrement primary key,
    name char(10),
    gender  char(10),
    email   varchar(64)
    )engine=innodb default charset=utf8;


    create table  host(
    id int auto_increment primary_key,
    hostname char(64)
    )engine=innodb default charset=utf8;

    create table user2host(
    id int auto_increment primary_key,
    userid  int not null,
    hostid  int not null,
    unique uq_user_host (userid,hostid)    #创建联合唯一索引
    constraint fk_u2h_user foreign key (userid) preferences userinfo2(id),
    constraint fk_u2h_user foreign key (hostid) preferences host(id),
   )engine=innodb default charset=utf8;

   -----------------------------------------------------------------------------
   SQL语句的补充
   数据行的操作
   insert into tb（name,age） values('alex',12),('root',18)
   create table userinfo(
    id int auto_inncrement primary key,
    name char(10),
    age int
    )engine=innodb default charset=utf8;

    create table userinfo2(
    id int auto_inncrement primary key,
    name char(10),
    age int
    )engine=innodb default charset=utf8;

     insert into tb（name,age） select   name ,id from  userinfo1;

     ---------
     delete from userinfo1 where id>2  or name="song";
     update userinfo1  set name='alex',age=18  where id>12 and  name='xx'
     select id ,name  as cname from userinfo1 where id > 10 or   id<20
     select name.age,11 from userinfo1;
     where id <>  1========where id !=1
     select id ,name  as cname from userinfo1 where id in  (1,5,12);
     select id ,name  as cname from userinfo1 where id  between 5 and  12  *双开
     select id  from userinfo1 where id in(selecct  id from tb1)
     a%  后面任意多个
     a_  后面一个位置
     %a% 包含a
     select * from  userinfo where name like  "a%"
      select * from  userinfo  limit 10;前几条
       select * from  userinfo  limit （1，2）;  start 1   取 2条
       select * from  userinfo  limit 10 offset 20;
               page=int("输入要查看的页码")
               page=int(page)
               (page-1)*10
排序：
select  * from userinfo  order by  id desc limit 5;  asc（小到大）
拍序时可以按多列排序  age相同的
分组：
   create table department(
    id int auto_increment primary key,
    title varchar(32)
    )engine=innodb default charset=utf8;

    insert into department(title) values('公关'),（'关公'）,('大大')
       create table userinfo(
    id int auto_inncrement primary key,
    name char(10),
    part_id int
    )engine=innodb default charset=utf8;
     constraint fk_part_user foreign key (part_id) preferences department(id),
         insert into department(name,) values('公关'，1),（'关公'，2）,('大大'，3)，（'共公'，1）

select part_id,max(id) from userinfo group by part_id;   (谁大取谁)    count（id）显示count 值
如果将聚合后的结果进行操作 必须使用having
select count(id) as count_res,part_id from userinfo group by part_id having count(id)>1;
连表操作
select * from userinfo,department; #笛卡尔积
select * from userinfo,department  where userinfo.part_id=department.id;          以前有性能的差别
select* from userinfo left join    department on  userinfo.part_id=department.id;  recommend
select* from userinfo right join    department on  userinfo.part_id=department.id;   右边的表全部显示
select score,sid,
        student.aid
from
userinfo left join    department on  userinfo.part_id=department.id
         left join    department on  userinfo.part_id=department.id
         left join    department on  userinfo.part_id=department.id;
 将显示null的行删除
 select * from userinfo inder join    department on  userinfo.part_id=department.id;
----------------------------------------------------------------------------------
mysqldump  -u root  (-d)  db1   > db1.sql  -p                                #备份文件
-d  表示只有结构
mysqldump  -u root  (-d)  db1    <    db1.sql  -p           导入文件
-------------------------------------------
www.cnblogs.com/wupeiqi/articles/572934.html
select * from score  where num>60;
select teacher_id  count(cname) from  course group by teacher_id
这是一个临时表
select sid from( select * from score  where num>60 )as B;
----------------------------------------------------------------------------
学生平均成绩大于60
select student_id,avg(num) from  score   group by student_id having  avg(num)  > 60;
select B.studnet_id,student.name,B.cccc from （select student_id,avg(num) as ccc from  score   group by student_id having  avg(num)  > 60）
as B left join student on B.studnet_id =student.sid;





--------------------------------------------------------------
pip install pymysqll -i https://pypi.douban.com/simple
---------------------------处女作-----------------------------------
import  pymysql
user=input("username:")
pwd=input("password:")
conn=pymysql.connect(host="localhost",user="root",password="",database="mysql_text")
cursor=conn.cursor() #拿数据
sql="select * from student where username='%s' and password='%s'"%(user,pwd,)
print(sql)
cursor.execute(sql)  #容易被sql注入
result=cursor.fetchone() # 只拿第一条
cursor.close()
conn.close()
if result:
    print("登陆成功")
else:
    print("登陆失败")
-------------------------------

import  pymysql
# user=input("username:")
# pwd=input("password:")
conn=pymysql.connect(host="localhost",user="root",password="",database="mysql_text")
cursor=conn.cursor()
# sql="select * from student where username=%s and password=%s"
# sql="select * from student where username=%(u)s and password=%(p)s"
sql="insert into student (username,password) values('rootsss',123)"
# sql="insert into student (username,password) values(%s,%s)"
print(sql)
cursor.execute(sql)
print(cursor.lastrowid)   #获取id
# cursor.execute(sql,(user,pwd,))
# cursor.executemany(sql,[('egon','465'),('laoyao','4561')])   #多行插入
# cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
    # result=cursor.fetchone()   insert  不需要
    #增加 删除 修改  需要commit 这个是 事务
conn.commit()
cursor.close()
conn.close()
                # if result:
                #     print("登陆成功")
                # else:
                #     print("登陆失败")
                #     #uu" or 1=1 --  sql注入
#新插入数据的自增id

            #cursor
                #fetchemany()
                #fetcheall()

---------------------------------------
day61 mysql 补充  杂七杂八的东西
1.视图
2.触发器
3.函数
4.存储过程
5.索引
6.orm操作
--------------回顾--------------
数据行 临时表：（select * from tb where id>10）
指定映射：select id,name,1,sum(x)/count()
条件：case when id>8 then xx else xx end
if(isnull(xx),0,1)
左右连表  join
上下连表 union 有一样的自动去除(union all)
select id,name from tb1
union
select num,sname from tb2;
---------------------------------------------------------------
权限管理
基于用户的权限管理
权限表：
1  订单管理
2  用户管理
3  菜单管理
4  权限分配
5  bug管理
用户表
1   alex
2   egon
用户权限关系表
1   2
1   1
2   1
程序：
    用户登录
基于角色的权限管理
用户信息
id username  pwd
1   alex    124
权限
1   it部门
2   用户卷
3  it主管
角色权限管理
1   1
1   2
3   1
3   2
3   3
-------------------------------------------------------
视图  (临时表娶个别名)virtual   临时 临时表
select asd  from (select * from tb1 where id>10)
create view 视图名称 as sql;
alter view
drop  view
触发器
修改终止符delimiter //
create trigger  before insert on     t1     for  each row
begin
inisert into teacher (tname) values("yijuuxiewn");
end  //
delimiter ；
------------------------------------------------------------------------
函数：
f()
bin()
内置函数  自定义函数
select currentdata();
--------------------
存储过程：
保存在mysql的别名==》sql语句的集合  替代程序员写sql语句  解耦

DELIMITER //
create procedure  P1()
BEGIN
    SELECT * FROM STUDNET;
    INSERT INTO TEACHER(TNAME)  VALUES("ETC");
  END //
    DELIMITER ;
CALL P1();
------------------------
慢日志 dba
-----------
DELIMITER //
create procedure  P1(  #可传参（in.out.inout)   out 传一个变量
in n1 int,
in n2 int
)
BEGIN
    SELECT * FROM STUDNET where id>n1;
    INSERT INTO TEACHER(TNAME)  VALUES("ETC");
  END //
    DELIMITER ;
CALL P1();
---------------------------------------
游标  对单独的一行数据进行操作时使用 cursor
delimitee //
create procedure p6()
bengin
declare row_id int ;
declare row_num  int;
declare done int default false;
declare temp int;

declare my_cursor cursor for select id,num from A;
declare coutinue handler for not found set done = true;

open my_cursor;
    xxoo:LOOP
    fetch my_cursor into row_id,row_num;
    if done then
    leave xxoo;
    end if;

     insert into B(num) values(temp);
    end loop xxoo;

close my_cursor;

end //
delimiter ;
---------------------存储过程------------
动态的执行sql   数据库里防止sql注入
delimiter //
create procedure p7(
in tp1 varchar(255),
in arg  int
)
begin
                        sql合法性
                        sql=格式化 tpl+arg
                        execute
                    set @xo=arg;  #预编译 防止sql 注入
                    prepare xxx from 'select * from student where sid > 2';
                    execute xxx using @arg;
                    deallocate prepare prod;
end //
delimter;
call p7("select * from tb where id >  ?",9)
------------------------------------------------
 in 参数 就是结果集合 事务 游标 存储过程
 -------------------------------------------------
 索引里有函数是 就不会加速查找了
 orm sqlalchmy
 -------------------
 索引  （加速查找，约束）
 唯一索引
 -------回顾------------
 视图 临时表 虚拟的
 触发器  sql  添加是 设置
 函数 select  functionname() from student;
 存储过程   游标 事务 结果集 和返回值
 cursor  default  tupple
 cursor.dict()
 callproc('p1',canshu)
 select  @_cunchuguocheng_0
 close cursor
 close  conn
 --------------------------------------------
 前端:  存储 使用  处理
1.基本选择器
id #
class .
2.背景
-------------------bootstrap---------------------