본문 바로가기
ICIA 수업일지

2021.08.02 수업일지(HTML, CSS, JAVA SCRIPT,MySQL)

by 주성씨 2021. 8. 7.

- HTML(Hyper Text Markup Language) + CSS(Cascading Style Sheet) + JAVA SCRIPT(+ jQuery)

- 반응형 웹 페이지 레이아웃 : 웹사이트의 에이아웃을 만들 때 사용하는 모니터의 화면 해상도를 고려하여 레이아웃이 pc인 경우와 모바일인 경우 적절한 레이아웃으로 변경되어야 한다. 이러한 문제를 해결하는 방법 중의 하나가 반응형 웹디자인이다.

 

ex07.html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Rayout ex07</title>
    <!-- <script src="http://code.jquery.com/jquery-1.9.1.js"></script> -->
    <style>
        #container {
            width: 940px;
            border: 1px solid tomato;
            margin: 0px auto;
            margin-top: 50px;
            padding: 20px;
        }

        #header {
            border: 1px solid tomato;
            padding: 20px;
            margin-bottom: 20px;
            color: red;
        }

        #content {
            border: 1px solid tomato;
            margin-bottom: 20px;
            padding: 20px;
            width: 580px;
            float: left;
        }

        #content h2 {
            color: red;
        }

        #content p {
            color: salmon;
        }

        #sidebar,
        #sidebar2 {
            border: 1px solid tomato;
            margin-bottom: 20px;
            padding: 20px;
            width: 260px;
            float: right;
        }

        #sidebar h2,
        #sidebar2 h2 {
            color: red;
        }

        #sidebar li,
        #sidebar2 li {
            color: salmon;
        }

        #footer {
            border: 1px solid tomato;
            padding: 20px;
            clear: both;
            color: red;
        }
    </style>
</head>

<body>
    <div id="container">
        <div id="header">
            <h1>Responsive Layout</h1>
        </div>
        <div id="content">
            <h2>Content</h2>
            <p>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the
                industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and
                scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap
                into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the
                release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing
                software like Aldus PageMaker including versions of Lorem Ipsum.</p>
            <p>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the
                industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and
                scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap
                into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the
                release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing
                software like Aldus PageMaker including versions of Lorem Ipsum.</p>
            <p>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the
                industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and
                scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap
                into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the
                release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing
                software like Aldus PageMaker including versions of Lorem Ipsum.</p>
            <p>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the
                industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and
                scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap
                into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the
                release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing
                software like Aldus PageMaker including versions of Lorem Ipsum.</p>
        </div>
        <div id="sidebar">
            <h2>Sidebar</h2>
            <ul>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
            </ul>
        </div>
        <div id="sidebar2">
            <h2>Sidebar2</h2>
            <ul>
                <li>Lorem2</li>
                <li>Ipsum2</li>
                <li>Dolor2</li>
                <li>Lorem2</li>
                <li>Ipsum2</li>
                <li>Dolor2</li>
                <li>Lorem2</li>
                <li>Ipsum2</li>
                <li>Dolor2</li>
            </ul>
        </div>
        <div id="footer">
            <p>Copyright</p>
        </div>
    </div>
</body>

</html>

 

ex07 출력물

 

ex07.html -2 -> prev, next 버튼을 이용한 페이지 넘김과 first, last page에서 더이상 버튼이 활성화 되지 않기 위한 방법.

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Rayout ex07</title>
    <script src="http://code.jquery.com/jquery-1.10.2.js"></script>
    <link rel="stylesheet" href='./ex07.css' />
</head>

<body>
    <div id="container">
        <div id="header">
            <h1>Responsive Layout</h1>
        </div>
        <div id="content">
            <h2>Content</h2>
            <div id="posts"></div>
            <div id="pagination" style="text-align: center;">
                <button id="prev">◀</button>
                <span id="page">1</span>
                <button id="next">▶</button>
            </div>
        </div>
        <div id="sidebar">
            <h2>Sidebar</h2>
            <ul>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
                <li>Lorem</li>
                <li>Ipsum</li>
                <li>Dolor</li>
            </ul>
        </div>
        <div id="footer">
            <p>Copyright</p>
        </div>
    </div>
</body>
<script>
    let page = 1;
    getList();

    let lastpage = Math.ceil(100/5);
    // prev 버튼을 눌렀을때
    $('#prev').on('click', function () {
        page = page - 1;
        $('#page').html(page);
        getList();
    });

    // next 버튼을 눌렀을때
    $('#next').on('click', function () {
        page = page + 1;
        $("#page").html(page);
        getList();
    });

    function getList() {
        $.ajax({
            type: 'get',
            url: 'https://jsonplaceholder.typicode.com/posts',
            dataType: 'json',
            success: function (data) {
                let str = '';
                $(data).each(function () {
                    let id = this.id;
                    let title = this.title;
                    let body = this.body;
                    if (id >= (page - 1) * 5 + 1 && id <= page * 5) {
                        str += `<h4>${id} : ${title}</h4>`;
                        str += `<p>${body}</p>`;
                    }
                });
                $('#posts').html(str);
                if(page==1){
                    $('#prev').attr('disabled',true);
                }else{
                    $('#prev').attr('disabled',false);
                };
                if(page==lastpage){
                    $('#next').attr('disabled',true);
                }else{
                    $('#next').attr('disabled',false);
                };
            }
        });
    };
</script>

</html>

 

ex07-2 출력물

 

ex08.html - 외부 DB를 통해서 이미지를 받고 10개씩 페이지에 출력해보도록 한다.

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Rayout ex08</title>
    <script src="http://code.jquery.com/jquery-1.10.2.js"></script>
    <link rel="stylesheet" href='./ex08.css' />
</head>

<body>
    <div id="container">
        <div id="header">
            <h1>Responsive Layout</h1>
        </div>
        <div id="content">
            <h2>Content</h2>
            <div id="photos"></div>
            <div id="pagination" style="text-align: center; margin-top: 5px;">
                <button id="prev">◀</button>
                <span id="page">1</span>
                <button id="next">▶</button>
            </div>
        </div>
        <div id="footer">
            <p>Copyright</p>
        </div>
    </div>
</body>
<script>
    let page = 1;
    getList();

    let lastpage = Math.ceil(100 / 5);
    // prev 버튼을 눌렀을때
    $('#prev').on('click', function () {
        page = page - 1;
        $('#page').html(page);
        getList();
    });

    // next 버튼을 눌렀을때
    $('#next').on('click', function () {
        page = page + 1;
        $("#page").html(page);
        getList();
    });

    function getList() {
        $.ajax({
            type: 'get',
            url: 'https://jsonplaceholder.typicode.com/photos',
            dataType: 'json',
            success: function (data) {
                let str = '';
                $(data).each(function () {
                    let id = this.id;
                    let title = this.title;
                    let img = this.thumbnailUrl;
                    if (id >= (page - 1) * 10 + 1 && id <= page * 10) {
                        str += '<div class="box">';
                        str += `<h4>${id} : ${title}</h4>`;
                        str += `<img src="${img}"/>`;
                        str += `</div>`;
                    }
                });
                $('#photos').html(str);
                if (page == 1) {
                    $('#prev').attr('disabled', true);
                } else {
                    $('#prev').attr('disabled', false);
                };
                if (page == lastpage) {
                    $('#next').attr('disabled', true);
                } else {
                    $('#next').attr('disabled', false);
                };
            }
        });
    };
</script>

</html>

ex08 출력물

 

- MySQL(DataBase)

 

- 학사 데이터베이스를 만든다.

create database haksadb;

 

- 학사라는 이름의 유저를 만든다. 비밀번호를 지정해준다.

create user 'haksa'@'localhost' identified by 'pass';

 

- 학사에게 권한을 준다.

grant all privileges on haksadb.* to 'haksa'@'localhost';

 

- 테이블을 생성할때는 기본키랑 외래키, 참조키를 생각해 잘 참조해야한다.

- 교수테이블 생성

CREATE TABLE professors (
    pcode CHAR(3) NOT NULL PRIMARY KEY,
    pname NVARCHAR(15) NOT NULL,
    dept NVARCHAR(30),
    hiredate DATE,
    title NVARCHAR(15),
    salary INT
);

desc professors;

 

- 학생 테이블 생성

CREATE TABLE students (
    scode CHAR(8) NOT NULL PRIMARY KEY,
    sname NVARCHAR(15) NOT NULL,
    dept NVARCHAR(30),
    year int default 1,
    birthday DATE,
    advisor CHAR(3),
    foreign key(advisor) references professors(pcode)
);

desc students;

 

- 강좌 테이블 생성

CREATE TABLE courses (
    lcode CHAR(4) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    hours INT,
    room CHAR(3),
    instructor CHAR(3),
    capacity INT DEFAULT 0,
    persons INT DEFAULT 0,
    PRIMARY KEY (lcode),
    FOREIGN KEY (instructor)
        REFERENCES professors (pcode)
);

desc courses;

 

- 수강신청 테이블 생성

CREATE TABLE enrollments (
    lcode CHAR(4) NOT NULL,
    scode CHAR(8) NOT NULL,
    edate DATE,
    grade INT DEFAULT 0,
    PRIMARY KEY (lcode , scode),
    FOREIGN KEY (lcode)
        REFERENCES courses (lcode),
    FOREIGN KEY (scode)
        REFERENCES students (scode)
);

 

- E-R 다이어그램(Entity-Relationship diagram) 만들기

database -> reverse engineer -> stored connection : haksa(여기서는) -> next -> 비밀번호 입력 -> 이후에는 순서대로 하면 된다.

 

- 교수 정보 입력

insert into professors(pcode,pname,dept,hiredate,title,salary) values('221','이병렬','전산','75/04/03','정교수',3000000);
insert into professors(pcode,pname,dept,hiredate,title,salary) values('228','이재광','전산','91/09/19','부교수',2500000);
insert into professors(pcode,pname,dept,hiredate,title,salary) values('311','강승일','전자','94/06/09','부교수',2300000);
insert into professors(pcode,pname,dept,hiredate,title,salary) values('509','오문환','건축','92/10/14','조교수',2000000);

 

- 학생 정보 입력

insert into students(scode,sname,dept,year,birthday,advisor) values('92414029','서연우','전산',3,'73/10/06','228');
insert into students(scode,sname,dept,year,birthday,advisor) values('92414033','김창덕','전산',4,'73/10/26','221');
insert into students(scode,sname,dept,year,birthday,advisor) values('92514009','이지행','전자',4,'73/11/16','311');
insert into students(scode,sname,dept,year,birthday,advisor) values('92514023','김형명','전자',4,'73/08/29','311');
insert into students(scode,sname,dept,year,birthday,advisor) values('92454018','이원구','건축',3,'74/09/30','509');
insert into students(scode,sname,dept,year,birthday,advisor) values('95454003','이재영','건축',4,'76/02/06','509');
insert into students(scode,sname,dept,year,birthday,advisor) values('95414058','박혜경','전산',4,'76/03/12','221');
insert into students(scode,sname,dept,year,birthday,advisor) values('96414404','김수정','전산',3,'77/12/22','228');

 

- 강좌 정보 입력

insert into courses(lcode,lname,hours,room,instructor,capacity,persons) values('C301','파일처리론', 3 ,'506','221',100,80);
insert into courses(lcode,lname,hours,room,instructor,capacity,persons) values('C401','데이터베이스',3,'414','221',80,80);
insert into courses(lcode,lname,hours,room,instructor,capacity,persons) values('C421','알고리즘',3,'510','228',80,72);
insert into courses(lcode,lname,hours,room,instructor,capacity,persons) values('C312','자료구조',2,'510','228',100,60);
insert into courses(lcode,lname,hours,room,instructor,capacity,persons) values('E221','논리회로',3,'304','311',100,80);
insert into courses(lcode,lname,hours,room,instructor,capacity,persons) values('A109','한국의건축문화',2,'101','509',120,36);

 

- 수강신청 정보 입력

insert into enrollments(lcode, scode, edate, grade) values('C401','92414033','98/03/02',85);
insert into enrollments(lcode, scode, edate, grade) values('C301','92414033','98/03/02',80);
insert into enrollments(lcode, scode, edate, grade) values('C421','92414033','98/03/02', 0);
insert into enrollments(lcode, scode, edate, grade) values('C401','95414058','98/03/03',90);
insert into enrollments(lcode, scode, edate, grade) values('C301','95414058','98/03/03',80);
insert into enrollments(lcode, scode, edate, grade) values('C312','95414058','98/03/03',80);
insert into enrollments(lcode, scode, edate, grade) values('C401','92514023','98/03/03',70);
insert into enrollments(lcode, scode, edate, grade) values('C301','92514023','98/03/03',70);
insert into enrollments(lcode, scode, edate, grade) values('C421','92514023','98/03/03',70);
insert into enrollments(lcode, scode, edate, grade) values('C301','92414029','98/03/03',90);
insert into enrollments(lcode, scode, edate, grade) values('C421','92414029','98/03/03',0);
insert into enrollments(lcode, scode, edate, grade) values('C312','92414029','98/03/03',70);
insert into enrollments(lcode, scode, edate, grade) values('E221','92414029','98/03/03',75);
insert into enrollments(lcode, scode, edate, grade) values('A109','92414029','98/03/03',90);
insert into enrollments(lcode, scode, edate, grade) values('C301','92514009','98/03/03',70);
insert into enrollments(lcode, scode, edate, grade) values('C401','92514009','98/03/03',85);
insert into enrollments(lcode, scode, edate, grade) values('E221','92514009','98/03/03',85);
insert into enrollments(lcode, scode, edate, grade) values('C301','96414404','98/03/04',75);
insert into enrollments(lcode, scode, edate, grade) values('C401','96414404','98/03/04',75);
insert into enrollments(lcode, scode, edate, grade) values('C421','96414404','98/03/04',75);
insert into enrollments(lcode, scode, edate, grade) values('C312','92454018','98/03/04',90);
insert into enrollments(lcode, scode, edate, grade) values('E221','92454018','98/03/04',90);
insert into enrollments(lcode, scode, edate, grade) values('A109','95454003','98/03/05',85);
insert into enrollments(lcode, scode, edate, grade) values('E221','95454003','98/03/05',85);

 

※ 유저 및 db 생성을 제외하고는 오라클과 똑같다.

 

SELECT 
    *
FROM
    enrollments;
SELECT 
    COUNT(*)
FROM
    enrollments;
    
commit;


-- select문 -- 
# 교수 테이블 검색
select * from professors;
# 교수 테이블에서 교수이름과 학과명을 검색
select pname, dept from professors;
# 교수 테이블에서 교수들이 속학 학과를 중복제거하여 검색
select distinct(dept) from professors;
# 학생 테이블에서 학과가 전산이면서 3학년인 학생 검색
select * from students where dept='전산' and year=3;
# 학생 테이블에서 1학년과 3학년 사이에 있는 학생 검색
select * from students where year between 1 and 3;
# 학생 테이블에서 성이 '이'인 학생 검색
select * from students where sname like '이%';

-- Join문 --
select * from students;

select s.*, p.dept
from students s, professors p
where pcode=advisor;

select c.*, pname
from courses c, professors p
where instructor = pcode;

select e.*, lname, sname
from enrollments e, courses c, students s
where e.lcode=c.lcode and e.scode=s.scode
order by s.sname;

# 강좌별 평균점수
select avg(grade), lcode, lname
from enrollments e, courses c
where e.lcode=c.lcode
group by e.lcode, lname;

# 학생별 평균점수
select avg(grade), e.scode, sname
from enrollments e, students s
where e.scode = s.scode
group by e.scode, sname;
저작자표시

'ICIA 수업일지' 카테고리의 다른 글

2021.08.04 수업일지(node.js, MySQL, VSC)  (0) 2021.08.07
2021.08.03 수업일지(MySQL, jQuery, node.js)  (0) 2021.08.07
2021.07.30 수업일지  (0) 2021.07.31
2021.07.29 수업일지  (0) 2021.07.31
2021.07.28 수업일지  (0) 2021.07.31

관련글

티스토리툴바