大数据课堂笔记——lianxi数据库

创建数据库

create database 库名；

create database lianxi;

打开

use 库名；

use lianxi;

use table 表名；

创建表（主键：primary key ）

create table 表名 (列名1 数据类型1，……);

create table course

(couid char(4),

couname char(10),

teachername char(3)

);

删除表

drop database 数据表名;

显示命令运行结果
show tables;

add 列名 类型 [];

修改表
alter database 数据库名 character set 字符集名;

数据类型：
int
tinyint
smallint
mediumint
bigint
float(m,d)
char
varchar
date
time
datetime
blob

约束表达式：
primary key 主键
auto_increment 从1开始自增
check(term=1 or sex='1' or depid='1') 在内容里选择
default '男' 默认值
unique 无重复
not null 空值

增加记录【不用自己填写的或没有的一律填 null】
insert into 表名 values(表内容);

建立关系级联更新级联删除

foreign key(字段) references 表名(字段) on update cascade on delete cascade;

复制表

结构：create table 新表 like 旧表;

create table stu1 like stu;

表：create table 新表 select * form 旧表;

create table stu2 select * from stu;

插入数据：(必须赋值一一对应，空值填null)

insert into 表名[(字段列表)] values(值列表);

insert into stu(stuname,sex,birthday,phone) VALUES('李二','男','1995-1-1','11111111111');

insert into 表名 values(值列表);

insert into stu VALUES(null,'李二','男','1995-1-1',null,'11111111111',null,null,null);

为主键添加重复记录时insert出错,replace替换原记录

replace into 表名[(字段列表)] values(值列表);

replace into 表名 values(值列表);

插入多条记录

insert into 表名[(字段列表)] values(值列表1),(值列表2),(……);

insert into stu(stuname,sex) VALUES('郑霞','女'),('张瑶','女'),('陈成','男');

replace into 表名[(字段列表)] values(值列表),(值列表2),(……);

插入其他表数据

insert into 目标数据表（字段列表1）select 字段列表2 from 数据表 where 条件；

insert into scores1 insert into stu11(stuid,stuname)

select * select stuid,stuname

from scores from stu

where score<60; where sex='男';

修改数据

update 表名 set 字段名1=值1,字段名2=值2,……[where 条件];

update scores update stu

set score=score+5 set phone='未知'

where stuid='20070102'; where phone is null;

修改多个表数据

update 表名 set 列名1=表达式1[,列名2=表达式2，……] where 表.列=表.列 and 条件；

update scores,stu update scores,course

set score=score+5 set score=score*1.1

where scores.stuid=stu.stuid and sex='女'; where scores.couid=course.couid and couname='高等数学';

删除记录

delete from 表名 [where 条件];

delete from stu update course

where depid='1005' and birthday>'1989-1-1'; set teachername=''

                                                                                    where couid='A001';

删除多个表的记录

delete from 表名1[,表名2……] using 表名列表 [where 条件];

delete from scores delete from stu,scores

using stu,scores using stu,scores

where stu.stuid=scores.stuid and sex='女'; where stu.stuid=scores.stuid and sex='男';

只能删除所有记录（默认值从一开始，不会在日志中记录）

truncate[table] 表名；

lianxi数据库：

create database lianxi;

use lianxi;

create table course
(couid char(4),
couname char(10),
teachername char(3)
);

INSERT INTO course VALUES ('A001', '英语', '袁圆');
INSERT INTO course VALUES ('A002', '高等数学', '谭鹃鹃');
INSERT INTO course VALUES ('B001', 'C语言程序设计', '王大强');
INSERT INTO course VALUES ('B002', '软件技术基础', '成华');
INSERT INTO course VALUES ('C001', 'SQL SERVER', '钟恬');
INSERT INTO course VALUES ('C002', 'VB程序设计', '张海');
INSERT INTO course VALUES ('C003', 'XML', '殷惠');

create table dep
(depid char(4),
depname char(5),
director char(3),
phone char(11)
);

INSERT INTO dep VALUES ('1001', '电子信息系', '吴天露', '08302568968');
INSERT INTO dep VALUES ('1002', '畜牧兽医系', '张长辉', '08302569541');
INSERT INTO dep VALUES ('1003', '农学园艺系', '陈齐', '08301254896');
INSERT INTO dep VALUES ('1004', '建筑工程系', '周洲', '08301478547');
INSERT INTO dep VALUES ('1005', '经济管理系', '刘伟', '08302569854');

create table scores
(id int primary key auto_increment,
stuid int,
term char(1),
couid char(4),
score tinyint
);

INSERT INTO scores VALUES (0,20070101, '1', 'A001', 78);
INSERT INTO scores VALUES (0,20070101, '6', 'A002', 89);
INSERT INTO scores VALUES (0,20070101, '3', 'B001', 85);
INSERT INTO scores VALUES (0,20070101, '4', 'B002', 45);
INSERT INTO scores VALUES (0,20070101, '5', 'C001', 78);
INSERT INTO scores VALUES (0,20070101, '2', 'C002', 65);
INSERT INTO scores VALUES (0,20070101, '1', 'C003', 25);
INSERT INTO scores VALUES (0,20070102, '4', 'A001', 89);
INSERT INTO scores VALUES (0,20070102, '5', 'A002', 63);
INSERT INTO scores VALUES (0,20070102, '2', 'B001', 65);
INSERT INTO scores VALUES (0,20070103, '1', 'B002', 87);
INSERT INTO scores VALUES (0,20070103, '3', 'C001', 89);
INSERT INTO scores VALUES (0,20070103, '1', 'C002', 98);
INSERT INTO scores VALUES (0,20070104, '1', 'C003', 98);
INSERT INTO scores VALUES (0,20070104, '2', 'A001', 87);
INSERT INTO scores VALUES (0,20070104, '3', 'A002', 47);
INSERT INTO scores VALUES (0,20070105, '4', 'A001', 85);
INSERT INTO scores VALUES (0,20070105, '5', 'A002', 98);
INSERT INTO scores VALUES (0,20070105, '6', 'B001', 96);
INSERT INTO scores VALUES (0,20070105, '1', 'B002', 87);
INSERT INTO scores VALUES (0,20070106, '1', 'A001', 98);
INSERT INTO scores VALUES (0,20070106, '3', 'A002', 78);
INSERT INTO scores VALUES (0,20070107, '1', 'A001', 47);
INSERT INTO scores VALUES (0,20070107, '1', 'A002', 75);
INSERT INTO scores VALUES (0,20070108, '4', 'A001', 99);
INSERT INTO scores VALUES (0,20070108, '1', 'A002', 87);
INSERT INTO scores VALUES (0,20070109, '1', 'C003', 78);
INSERT INTO scores VALUES (0,20070109, '2', 'C002', 58);
INSERT INTO scores VALUES (0,20070110, '1', 'C001', 68);
INSERT INTO scores VALUES (0,20070110, '1', 'C003', 98);

create table stu
(stuid int,
stuname char(3),
sex enum('男','女'),
birthday date,
rx_score smallint,
phone char(12),
class char(8),
depid char(4),
remark varchar(200)
);

INSERT INTO stu VALUES (20070101, '张华生', '男', '1989-01-07',400, '13312569854', '07软件技术1', '1001', NULL);
INSERT INTO stu VALUES (20070102, '刘雪梅', '女', '1989-01-17',500, '13369586963', '06软件技术1', '1001', NULL);
INSERT INTO stu VALUES (20070103, '何东', '男', '1988-07-24',450, '18936589635', '07计算机技术1', '1001', NULL);
INSERT INTO stu VALUES (20070104, '康勇', '男', '1987-04-17',430, '18936585745', '07计算机技术2', '1001', NULL);
INSERT INTO stu VALUES (20070105, '赵琳', '女', '1990-02-02',470, '18812569635', '06畜牧兽医1', '1002', NULL);
INSERT INTO stu VALUES (20070106, '王春', '女', '1990-04-04',480, '134569852635', '06园林技术1', '1003', NULL);
INSERT INTO stu VALUES (20070107, '吴苇', '女', '1989-12-23',390, '133698541254', '07建筑工程1', '1004', NULL);
INSERT INTO stu VALUES (20070108, '陈飞', '男', '1990-05-06',444, '135698541254', '06文秘', '1005', NULL);
INSERT INTO stu VALUES (20070109, '董成山', '男', '1988-02-05',495, '135485214526', '05市场营销', '1005', NULL);
INSERT INTO stu VALUES (20070110, '刘小梅', '女', '1990-01-07',505, null, '06市场营销', '1005', NULL);

create table orders
(orderid int primary key auto_increment,
productid int,
productname varchar(20),
unitprice decimal(7,2),
nums smallint,
customerid int,
orderdate date);

insert into orders values(null,10,'格力变频柜机',10000,100,1111,now());
insert into orders values(null,15,'格力定频柜机',7000,50,1111,now());
insert into orders values(null,18,'格力变频挂机',5000,80,1111,now());
insert into orders values(null,10,'格力变频柜机',10000,50,1112,now());
insert into orders values(null,15,'格力定频柜机',7000,20,1112,now());
insert into orders values(null,18,'格力变频挂机',5000,30,1112,now());
insert into orders values(null,10,'格力变频柜机',10000,20,1113,now());
insert into orders values(null,15,'格力定频柜机',7000,50,1113,now());
insert into orders values(null,18,'格力变频挂机',5000,80,1113,now());