mysql操作篇二

SELECT sname,age,scope from stu_info where sname='cc';

update stu_info set scope=60 where sno=3;

select * from stu_info order by scope DESC;
----ASC默认的，从小到大 DESC从大到小

select sname 姓名,scope 成绩 from stu_info s;

select * from stu_info where birthday is null;

select * from stu_info where birthday='2017-6-1';

select * from stu_info order by scope desc
limit 0,1; ---0是起始行，1是截取的行数 ，限制查询行数

select * from person;
drop table person;
create table person
(
id varchar(18) primary key,
sname varchar(20) not null,
age int,
address varchar(30)
)
----第一范式 数据都是最小单位，列不可分割 第二范式 主键 第三范式 外键必须建在主键上
---不一定单独一个字段为主键，两个字段组合也可为主键，执行语句没讲
---时间戳 timesplit？ 精确到小时分钟秒 date是日期类型
----表于表之间的关系 一对一 ，，，有关联的字段 数据类型必须一致 外键就是别的表中拿来的
---外键约束了 这个数据必须是存在的 主键约束了不能重复
----一对多，被用的表是主表，，即一方是主表，多方是外表，关系建在多方
create table card
(
id varchar(18) primary key,
caddress varchar(20) not null,
cdate date not null
)

create table person
(
cid varchar(18) primary key,
pname varchar(20) not null,
sex char(1),
birthday date,
foreign key(cid) REFERENCES card(id)
)

insert into card values('1111','秦皇岛市海港区公安局',now());

insert into person values('1111','aa','男',now());

drop table dept;
create table dept
(
deptno int primary key,
dname varchar(20),
loc varchar(20)
)
insert into dept values(10,'CEO','----');
insert into dept values(20,'人事部','----');
insert into dept values(30,'研发部','----');
drop table emp;
create table emp
(
empno int primary key,
ename varchar(20) not null,
manager int,
sal float,
comm float,
birthday date,
address varchar(20),
deptno int,
foreign key (deptno) REFERENCES dept(deptno)
)
insert into emp
values(1001,'rose',null,10000,2000,'1983-9-7','天津',10);
insert into emp
values(1002,'jack',1001,10000,2000,'1989-9-7','北京',20);
insert into emp
values(1003,'tom',1001,10000,2000,'1990-9-7','上海',20);
insert into emp
values(1004,'king',1002,10000,2000,'1992-9-7','南京',20);

select * from emp;

select ename,dname from emp e inner join dept d
on e.deptno=d.deptno;

create table student
(
sno int primary key,
sname varchar(20) not null,
sex char(1),
address varchar(20)
)
insert into student values(1001,'aa','男','天津');
insert into student values(1002,'bb','男','天津');
insert into student values(1003,'cc','女','秦皇岛');

create table sub_ject
(
subNo int primary key,
sname varchar(20),
shi varchar(20)
)
insert into sub_ject values(1,'html','12课时');
insert into sub_ject values(2,'java','200课时');
insert into sub_ject values(3,'js','56课时');

create table stu_sub
(
stuNo int,
subNo int,
foreign key (stuNo) REFERENCES student(sno),
foreign key (subNo) REFERENCES sub_ject(subNo)
)

insert into stu_sub values(1001,1);
insert into stu_sub values(1002,1);
insert into stu_sub values(1001,2);
insert into stu_sub values(1002,2);

select * from stu_sub;
select stu.sname,sub.sname from student stu inner join
stu_sub ss on stu.sno=ss.stuNo inner join sub_ject sub
on ss.subNo=sub.subNo;