MySQLデータベース-Day 1ノート
16147 ワード
1.学生表の作成
create table student(
id int primary key auto_increment,
name varchar(20) not null,
sex char(5),
age int,
score double
);
2.学生表に記録を挿入する
方法1:
insert into student(name,age,sex,score)values(' ',22,' ',85);
insert into student(name)values(' ');
方式2:
insert into student values(2,' ',' ',25,88.5);
方式3:
insert into student(name,age,sex,score)values(' ',26,' ',91.5),
(' ',21,' ',60),(' ',20,' ',65);
3.学生記録の修正
1. 60
update student set score=60;
2. id 10 100
update student set score=100 where id=10;
3. id 10 , 2 , 5
update student set age=age+2,score=score+5 where id>10;
4.レコードの削除
1.
delete from student where score<60;
5.照会記録
1.
select * from student;
2.
select name,score from student;
3. ,
select name as ,score as from student;
4. 80
select name,score from student where score>80;
6.クエリーの制限:
1.studentテーブルの最初の3つのレコードをクエリー
select * from student limit 3;
2.クエリーstudentテーブルの3~5番目のレコード(オフセット量2から調べる、最大3つまで調べる)
select * from student limit 2,3;
7.ソートクエリー:
select * from student order by score;
select * from student order by score desc;
select * from student order by score desc,age;
select * from student order by score desc limit 3;
8.集約関数:
select max(score) from student;
select avg(score) from student;
select sum(score) from student;
select count(score) from student;
select count(*) from student;
select sum(score)/count(score) from student;
9.グループクエリー:
create table product(
id int primary key auto_increment,
name varchar(20) not null,
price double not null,
address varchar(20),
type varchar(20)
);
insert into product(name,price,address,type)values
(' ',5,' ',' '),(' ',12,' ',' '),
(' ',2,' ',' '),('Mac ',2000,' ',' '),
(' ',35,' ',' '),(' ',8,' ',' ');
select type,avg(price) from product group by type;
select type,avg(price) from product group by type having type=' ';
select type,address,avg(price) from product group by type,address;
10.ファジイクエリ:
select * from student where name like '% %';
select * from student where name like ' %';
select * from student where name like '_ _';