创建数据库
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());
212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
