[原创]mysql基本语句_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2510 | 回复: 1

主题： [原创]mysql基本语句

rui.yuan

注册用户

等级：中校
经验：1637
发帖：29
精华：0
注册：1970-1-1
状态：离线
发送短消息息给rui.yuan

加好友发送短消息息给rui.yuan

发消息

发表于：

2014-11-17 16:59:24 | [全部帖] [楼主帖]

楼主

第一次玩mysql，其实大多语句和Oracle相同不过有少数的语句是有区别的

#创建数据库
create database stums;
#创建学生信息表
create table tb_students(
stu_num char(12) primary key,
stu_name varchar(20),
stu_sex char(2),
stu_age int
)
#删除学生表
dorp table tb_students;
select * from tb_students;
#添加字段、删除字段
alter table tb_students add stu_des varchar(20) after stu_name;
alter table tb_students drop column studes;
#插入数据
insert into tb_students(stu_num,stu_name,stu_des,stu_sex,stu_age) values('1005','小钱','ii','女',21);
#修改数据
update tb_students set stu_des='oo';

update tb_studnets set stu_sex='女',stu_age=21 where stu_num='1002';

#查找数据
select * from tb_students where stu_name like '小_';
select * from tb_students where stu_name like '_王';
select * from tb_students where stu_name like '小%';
select * from tb_students where stu_name like '%王';
select * from tb_students where stu_name like '%王%';

#字段别名
select stu_num 学号,stu_name 姓名 from tb_students;

#group by having分组操作 order by desc/asc排序
select count(*) 人数,avg(stu_age) 平均年龄 from tb_students group by stu_age having avg(stu_age)>23;

select * from tb_students where stu_age>22 order by stu_age desc;
select avg(stu_age) from tb_students;
select max(stu_age) from tb_students;
#主键外键
create table tb_sc(
sc_stu_num char(12),
sc_course_name char(8),
sc_grade int,
constraint PK_SC primary key (sc_stu_num),
foreign key(sc_stu_num) references tb_students(stu_num)
);
insert into tb_sc(sc_stu_num,sc_course_name,sc_grade) values('1003','韩语',90);
select * from tb_sc;
#多表查询
select * from tb_students s,tb_sc sc where s.stu_num=sc.sc_stu_num;
select * from tb_students s join tb_sc sc on s.stu_num=sc.sc_stu_num;
select * from tb_students s left join tb_sc sc on s.stu_num=sc.sc_stu_num;
select stu_name from tb_students where stu_num in(select distinct sc_stu_num from tb_sc);
#创建索引
create index index_students_stu_age on tb_students(stu_age);
show index from tb_students;
#创建视图
create view V_STU as select * from tb_students where stu_age>22;
select * from v_stu;