day12 数据库结尾 前端基础

l 多表链接

1 笛卡尔积
select * from dep,emp;

select * from dep,emp where dep.id = emp.dep_id;


2 内连接inner join
select * from dep inner join emp
    on dep.id = emp.dep_id;

3 左连接left join
select * from dep left join emp
    on dep.id = emp.dep_id;


4 右连接right join
select * from dep right join emp
    on dep.id = emp.dep_id;

5 全连接full join
select * from dep left join emp
    on dep.id = emp.dep_id
union
select * from dep right join emp
    on dep.id = emp.dep_id;

 

l 子查询

#查询平均年龄在25岁以上的部门名
select name from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from emp where dep_id=(
select id from dep where name='技术'
);

#查看<=1人的部门名
select name from dep where id in (
select dep_id from emp group by dep_id having count(id) <= 1
);


#查询大于所有人平均年龄的员工名与年龄
#select name,age from emp where age > 所有人的平均年龄;


select name,age from emp where age > (
select avg(age) from emp
);

#查询大于部门内平均年龄的员工名、年龄
select * from emp as t1 inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) as t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age
;


select * from dep where exists
(select * from dep where name='技术');


#查询每个部门最新入职的员工
select t1.name,t1.hire_date,t2.* from emp as t1 inner join
(
select post,max(hire_date) max_date from emp group by post
) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;







#sql关键字的优先级
select distinct 字段1，字段2,... from 左表 left join 右表
    on 链表条件
    where 约束条件
    group by 字段
    having 过滤条件
    order by 排序字段
    limit n;

egon@5565487

 

l Pymysql模块

# import pymysql
#
# conn=pymysql.connect(
#     host='localhost',
#     port=3306,
#     user='root',
#     password='123',
#     database='db8',
#     charset='utf8'
# )
#
# cur=conn.cursor(cursor=pymysql.cursors.DictCursor)

# sql='''
# create table userinfo(
#   id int primary key auto_increment,
#   user char(16),
#   password char(20)
# );
# '''
# cur.execute(sql)


# sql='insert into userinfo(user,password) values("alex","123");'
# cur.execute(sql)
# sql='insert into userinfo(user,password) values("egon","123456");'
# cur.execute(sql)
# sql='insert into userinfo(user,password) values("wxx","123456");'
# cur.execute(sql)
# print(cur.lastrowid)


# sql='select * from userinfo;'
# rows=cur.execute(sql)
# print(rows)

#取查询结果
# print(cur.fetchone())
# print(cur.fetchmany(2))
# print(cur.fetchall())

# print(cur.fetchone())
# cur.scroll(0,'absolute')
# cur.scroll(1,'relative')
# print(cur.fetchone())


# cur.close()
# conn.commit()
# conn.close()





# import pymysql
#
# username=input('username>>: ').strip()
# password=input('password>>: ').strip()
#
# conn=pymysql.connect(
#     host='localhost',
#     port=3306,
#     user='root',
#     password='123',
#     database='db8',
#     charset='utf8'
# )
#
# cur=conn.cursor(cursor=pymysql.cursors.DictCursor)
# # sql='select * from userinfo where user="%s" and password ="%s"' %(username,password)
# # sql='select * from userinfo where user="xxx" or 1=1 -- aa" -- aa" and password ="";'
# #
# # sql='select * from userinfo where user=%s and password =%s'
# #
# # rows=cur.execute(sql,(username,password))
#
#
# sql='insert into userinfo(user,password) values(%s,%s)'
# cur.execute(sql,('yxx','123'))
# cur.executemany(sql,[('axx','123'),('bxx','123')])
#
#
# # if rows:
# #     print('登录成功')
# # else:
# #     print('账号或密码错误')
#
#
# cur.close()
# conn.commit()
# conn.close()
#

 

l MySQL其他

#1、视图：虚拟表，只有表结构，没有数据（来自于其他表）

create view course_to_teacher_view as
select course.*,teacher.tname from course inner join teacher
    on course.teacher_id=teacher.tid;


alter view course_to_teacher_view as
select course.*,teacher.tname from course inner join teacher
    on course.teacher_id=teacher.tid;


drop view course_to_teacher_view;


#2:触发器
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if (NEW.success = "no")then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;

end $$
delimiter ;


#3:事务

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);



start transaction;
update user set balance=900 where id=1;
update user set balance=1010 where id=2;
update user set balance=1090 where id=3;

rollback;
commit;




#4:存储过程
delimiter $$
create procedure auto_insert1()
begin
    declare n int default 1;
    while (n<=10)do
        insert into user(name,balance) values(concat('egon',n),n);
        set n=n+1;
    end while;

end $$
delimiter ;

#mysql
call auto_insert1();




delimiter $$
create procedure proc1(
    in n int,
    out res int
)
begin
    select * from user where id > n;
    set res=0;
end $$
delimiter ;

#mysql
set @n=3;
set @res=111;
call proc1(@n,@res);



#pymysql







select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

 

l 执行存储过程

import pymysql

conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db9',
    charset='utf8'
)

cur=conn.cursor(cursor=pymysql.cursors.DictCursor)

cur.callproc('proc1',(3,10)) #set @_proc1_0=3;set @_proc1_1=10
# print(cur.fetchone())


cur.execute('select @_proc1_1;')
print(cur.fetchone())

cur.close()
conn.close()

 

l 前端 jdserver：

import socket


sock=socket.socket()
sock.bind(("127.0.0.1",8080))
sock.listen(5)


while 1:

    conn,addr=sock.accept()

    data=conn.recv(1024)
    print(data)

    with open("index.html") as  f:
        data=f.read()

    conn.send(("http/1.1 200 Ok\r\n\r\n %s"%data).encode("utf8"))

    conn.close()

 

l 基本标签

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>

</head>
<body>

     <h1 style="background-color: green">egon</h1>
     <h2>egon</h2>
     <h3>egon</h3>
     <h4>egon</h4>
     <h5>egon</h5>
     <h6>egon</h6>

AAAAAAAA,<br>
BBBBBBBB,<br>
CCCCCCCC
<p style="background-color: darkgreen">AAAAAAAA</p>
<p>BBBBBBBB</p>
<p>CCCCCCCC</p>
<hr>
alex<b>alex</b>
<b>alex</b>
<b>alex</b> <strong>egon</strong>
<em>oldboy</em>
<!--========  嵌套关系          -->

<b><p>yuan</p></b>
<p>
    <h1>yuan</h1>
</p>
 <hr>
<!--<div></div> <span></span>-->
<div style="background-color: darkgreen">yuan</div><div>yuan</div>
<span style="background-color: goldenrod">spannnnnnnnnnnnnnnnnn</span><span>span</span>
</body>
</html>

 

l 常用标签

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>

</head>
<body>





<a href="http://www.baidu.com" target="_blank">click</a>
<a href="http://www.baidu.com" target="_blank">
    <img src="egg.jpg" title="蛋" alt="这是个蛋" width="188" height="280">
</a>



</body>
</html>

 

l 列表标签

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>

</head>
<body>

<ul>
    <li>111</li>
    <li>222</li>
    <li>333</li>
</ul>

<ol>
    <li>111</li>
    <li>222</li>
    <li>333</li>
</ol>

<dl>
    <dt>河北省</dt>
    <dd>廊坊市</dd>
    <dd>保定</dd>
    <dd>雄安</dd>
</dl>



</body>
</html>

 

l 锚功能

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        /*css代码*/
        #chapter1{
            width: 100%;
            height: 800px;
            background-color: wheat;
        }
        #chapter2{
            width: 100%;
            height: 800px;
            background-color: darkgrey;
        }
        #chapter3{
            width: 100%;
            height: 800px;
            background-color: lightblue;
        }

    </style>
</head>
<body>
<p><a href="#chapter1">第一章</a></p>
<p><a href="#chapter2">第二章</a></p>
<p><a href="#chapter3">第三章</a></p>

<div id="chapter1">1111</div>
<div id="chapter2">2222</div>
<div id="chapter3">3333</div>


</body>
</html>

 

l 表单标签

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>

</head>
<body>
<h3>注册页面</h3>

<form action="" method="post">
    <p>
        <label for="user">姓名：</label>
        <input type="text" name="user" value="yuan" id="user">
    </p>
    <p>密码：<input type="password" name="pwd" value="123"></p>

    <p>爱好：<input type="checkbox" name="hobby" value="lanqiu" checked>篮球
            <input type="checkbox" name="hobby" value="zuqiu">足球
            <input type="checkbox" name="hobby" value="shuangsqiu">双色球
    </p>
    <p>
        性别：<input type="radio" name="gender" value="male">男
             <input type="radio" name="gender" value="female">女
             <input type="radio" name="gender" value="egon">其他
           
    </p>
    <p>
        <input type="button" value="按钮" onclick="alert(123)">
    </p>
    <p>
        <input type="reset">
    </p>
    <p>
        头像：<input type="file" name="avatar">
    </p>
    <p>
        <input type="hidden" name="egon" value="is_dog">
    </p>

    <!--===============================select-->

    <select name="pro" size="3" multiple>
        <option value="1">河北省</option>
        <option value="2">河南省</option>
        <option value="3" selected="selected">山东省</option>
    </select>


    <p>个人简介：
        <textarea name="person" id="" cols="80" rows="10"></textarea>
    </p>



    <p><input type="submit" value="submit"></p>


</form>


{"user":"yuan","pwd":"123","hobby":[lanqiu,zuqiu]，"gender":"male"，"avatar":"","egon":"is_dog，"pro":"2"}

</body>
</html>

 

l 作业

Table1

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <table border="1" cellspacing="5" cellpadding="10" style="border: 1px solid brown">
        <tr>
            <th colspan="6" style="text-align: center">星期一菜谱</th>
        </tr>
        <tr>
            <th rowspan="2">素菜</th>
            <th colspan="3">清炒茄子</th>>
            <th>花椒扁豆</th>>
        </tr>
        <tr>
            <th colspan="3">小葱豆腐</th>>
            <th>炒白菜</th>>
        </tr>
        <tr>
            <th rowspan="5">荤菜</th>
            <th colspan="3">油焖大虾</th>>
            <th>海参鱼翅</th>>
        </tr>
        <tr>
            <th rowspan="4"  colspan="3">红烧肉
                <a href="table2.html" target="_blank">
                    <img src="110.png">
                </a>>
            </th>>
            <th rowspan="4">烤全羊</th>>
        </tr>
    </table> >

</body>
</html>

 

Table1

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <table border="1" cellspacing="5" cellpadding="10" style="border: 1px solid sandybrown">
        <tr>
            <th>项目</th>
            <th colspan="5">上课</th>
            <th colspan="2">休息</th>
        </tr>
        <tr>
            <th>星期</th>
            <th>星期一</th>
            <th>星期二</th>
            <th>星期三</th>
            <th>星期四</th>
            <th>星期五</th>
            <th>星期六</th>
            <th>星期日</th>
        </tr>
        <tr>
            <td rowspan="4">上午</td>
            <td>语文</td>
            <td>数学</td>
            <td>英语</td>
            <td>英语</td>
            <td>物理</td>
            <td>计算机</td>
            <td rowspan="4">休息</td>
        </tr>
        <tr>
            <td>数学</td>
            <td>数学</td>
            <td>地理</td>
            <td>历史</td>
            <td>化学</td>
            <td>计算机</td>
        </tr>
        <tr>
            <td>化学</td>
            <td>语文</td>
            <td>体育</td>
            <td>计算机</td>
            <td>英语</td>
            <td>计算机</td>
        </tr>
        <tr>
            <td>政治</td>
            <td>英语</td>
            <td>体育</td>
            <td>历史</td>
            <td>地理</td>
            <td>计算机</td>
        </tr>
        <tr>
            <td rowspan="2">下午</td>
            <td>语文</td>
            <td>数学</td>
            <td>英语</td>
            <td>英语</td>
            <td>物理</td>
            <td>计算机</td>
            <td rowspan="2">休息</td>
        </tr>
         <tr>
            <td>数学</td>
            <td>数学</td>
            <td>地理</td>
            <td>历史</td>
            <td>化学</td>
            <td>计算机</td>
        </tr>
    </table>
</body>
</html>

页面注册

!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <h4>注册信息</h4>
    <form action="https://www.jd.com" method="post">
        <p>
            <label for="姓名">姓名：</label>
            <input type="测试" name="姓名" value="高宏杰" id="姓名">
        </p>

        <p>
            <label for="密码">密码：</label>
            <input type="password" name="密码" value="999" id="密码">
        </p>

        <select  >
            <option>北京市</option>
            <option selected>山西省</option>
            <option>河北省</option>
            <option>山东省</option>
            <option>河南省</option>
            <option>东北三省</option>

        </select>

        <p>
            <label for="性别">性别：</label>
            <input type="radio" name="性别" value="男" id="性别">男
            <input type="radio" name="性别" value="女" >女
            <input type="radio" name="性别" value="其他">其他
        </p>

        <p>
            <label for="爱好">爱好：</label>
            <input type="checkbox" name="爱好" value="抽烟" id="爱好">抽烟
            <input type="checkbox" name="爱好" value="抽喝酒" >抽烟
            <input type="checkbox" name="爱好" value="烫头发" >烫头发
        </p>

        <p>
            <label for="头像">头像：</label>
            <input type="file" name="上传头像" id="头像">
        </p>

        <p>
            <label for="个人简介">个人简介：</label>
            <textarea name="人物" cols="50" rows="20" id="个人简介">
            </textarea>
        </p>

        <p>
        <input type="reset" name="重置">
        </p>

        <p>
            <input type="submit" name="提交到京东" value="提交到京东">
        </p>
    </form>
</body>
</html>

 

 

l 老师博客地址

lhttp://www.cnblogs.com/yuanchenqi/articles/6835654.html