mysql建表语句

建库：
create database db_name character set =utf8;
/*建立库db_name，默认字符集为utf8*/
建表：
create table tb_1 (id int auto_increment primary key, remark varchar(20)) engine=innodb default character set=utf8 auto_increment =100;
/*建立表tb_1，存储引擎为innodb,默认字符集为utf8，自增列开始值为100*/ create table tb_2 (id2 int auto_increment primary key, remark varchar(20)) engine=innodb default character set=utf8 auto_increment =100;
内联接：
select * from t1 inner join t2 where t1.id=t2.id2;
左联接：
select * from t1 left join t2 on t1.id=t2.id2;
右联接：
select * from t1 right join t2 on t1.id=t2.id2;
全联接：
select * from t1,t2
create table tb_student (
Student_ID int auto_increment primary key,
Student_Name varchar(20),
Sex char(1),
Birthday varchar(10)
) engine=innodb default character set =utf8;
create table tb_subject (
Subject_ID int auto_increment primary key,
Subject_Name varchar(50)
) engine=innodb default character set =utf8;
create table tb_score (
ID int auto_increment primary key,
Student_ID int,
Subject_ID int,
Score decimal(10,2),
foreign key (Student_ID) references tb_student(Student_ID),
foreign key (Subject_ID) references tb_subject(Subject_ID)
) engine=innodb default character set =utf8
Data 数据Database 数据库RDBMS 关系数据库管理系统GRANT 授权
REVOKE 取消权限DENY 拒绝权限DECLARE 定义变量PROCEDURE存储过程事务Transaction 触发器TRIGGER 继续continue 唯一unqiue
主键primary key 标识列identity 外键foreign key 检查check
约束constraint
--------------------------------------------------------------------
1) 创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话create table student
(
学号int,
姓名varchar(10),
年龄int,
性别varchar(4),
家庭住址varchar(50),
联系电话varchar(11)
);
--------------------------------------------------------------------
2) 修改学生表的结构，添加一列信息，学历
alter table student add column 学历varchar(6);
--------------------------------------------------------------------
3) 修改学生表的结构，删除一列信息，家庭住址
alter table student drop column 家庭住址;//注意此处用drop而非delete
--------------------------------------------------------------------
4) 向学生表添加如下信息：
学号姓名年龄性别联系电话学历
1A22男123456小学
2B21男119中学
3C23男110高中
4D18女114大学
insert into student (学号,姓名,年龄,性别,联系电话,学历) values(1,"A",22,"男","123456","小学");
insert into student (学号,姓名,年龄,性别,联系电话,学历) values(1,"B",21,"男","119","中学");
insert into student (学号,姓名,年龄,性别,联系电话,学历) values(1,"C",23,"男","123456","高中");
insert into student (学号,姓名,年龄,性别,联系电话,学历) values(1,"D",23,"女","114","大学");
--------------------------------------------------------------------
5) 修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”
update student set 学历="大专" where 联系电话like "11%";
--------------------------------------------------------------------
6) 删除学生表的数据，姓名以C开头，性别为‘男'的记录删除
delete from student where 姓名like "C" and 性别="男";
--------------------------------------------------------------------
7) 查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号示出来
select 姓名,学号from student where 年龄<22 and 学历="大专";
--------------------------------------------------------------------
8) 查询学生表的数据，查询所有信息，列出前25%的记录
select top 25 percent * from student ; ????
select * from student limit 25%;????
这条有问题，在sql 2000中应该是select top 25 percent * from student ;
--------------------------------------------------------------------
9) 查询出所有学生的姓名，性别，年龄降序排列
select 姓名,性别,年龄from student order by 年龄desc;
--------------------------------------------------------------------
10) 按照性别分组查询所有的平均年龄
select avg(年龄) as 平均年龄from student group by 性别;
select avg(年龄) from student group by 性别;
select avg(年龄) 平均年龄from student group by 性别;
--------------------------------------------------------------------
3) 说出以下聚合数的含义：avg ,sum ,max ,min , count ,count(*)
AVG:求平均值
SUM:求和
MAX:求最大值
MIN:求最小值
COUNT(*):返回所有行数
COUNT返回满足指定条件的记录值。