MySQL 简单得建表、插入、查询代码

复制代码
 1 create table student(
 2 studentno int(4) not null primary key comment'学号',
 3 loginpwd varchar(20) not null comment'密码',
 4 studentname varchar(50) not null comment'学生姓名',
 5 sex char(2) not null DEFAULT'' comment'性别',
 6 gradeid int(4) UNSIGNED comment'年级编号',
 7 phone varchar(50) comment'联系电话',
 8 address varchar(255) default'地址不详' comment'地址',
 9 birthday datetime comment'出生日期',
10 email varchar(50) comment'邮件账号',
11 identityCard varchar(18) comment'身份证号' UNIQUE key 
12 )comment="学生表";
13 alter table student change gradeid gradeID int(4) UNSIGNED comment'年级编号';
14 alter table student change birthday birthday date comment'出生日期';##更改birthday字段数据类型
15 insert into student(studentno,loginpwd,studentname,gradeID,phone,birthday) values(01,123,'黄小平',1,13956799999,'1996-5-8');
16 insert into student(studentno,loginpwd,studentname,sex,gradeID,phone,address,birthday)values
17 (10000,'123','郭靖','',1,1365667783,'天津市河西区','1990-09-08'),
18 (10001,'123','李文才','',1,13645667890,'地址不详','1994-04-12'),
19 (10002,'123','李斯文','',1,13645556793,'河南洛阳','1993-07-23'),
20 (10003,'123','张萍','',1,13642345112,'地址不详','1995-06-10'),
21 (10004,'123','韩秋洁','',1,13812344566,'北京市海淀区','1995-07-15'),
22 (10005,'123','张秋丽','',1,13567893246,'北京市东城区','1994-01-07'),
23 (10006,'123','肖梅','',1,13563456721,'河北省石家庄市','1991-02-17'),
24 (10007,'123','秦洋','',1,13056434411,'上海市卢湾区','1992-04-18'),
25 (10008,'123','何晴晴','',1,13053445221,'广州市天河区','1997-07-23'),
26 (20000,'123','王宝宝','',2,15076552323,'地址不详','1996-06-05'),
27 (20010,'123','何小华','',2,13318877954,'地址不详','1995-09-10'),
28 (30011,'123','陈志强','',3,13689965430,'地址不详','1994-09-27'),
29 (30012,'123','李露露','',3,13685678854,'地址不详','1992-09-27');##添加数据
30 delete from student where studentname='黄小平';##删除名字为黄小平得这一行数据
31 update student set address='西直门东大街215号',loginpwd='000' where studentno=20000;##更新表中数据
32 update student set address=null where address='地址不详';##将地址不详得地址改为空值
33 select studentname as 姓名,address as 地址,'北京新兴桥' as 学校名称 from student;
34 select * from student where gradeID = 1;##查询年级编号为1得全部学生得信息
35 select studentname,phone from student where gradeID = 2;##查询年级编号为2得全部学生得电话号码和姓名
36 select * from student where gradeID = 1 and sex = '';##查询年级编号为1得全部女同学得信息
37 select studentname, address from student where gradeID = 2 and sex = '';##查询年级编号为2得所有男同学得姓名和地址
38 select studentname, gradeID from student where email is null;##查询无电子邮件得学生姓名和年级信息
39 select studentname from student where gradeID = 2 and birthday > '1990-0-0';##查询年级编号为2且1990年后出生得学生姓名
40 select * from student where gradeID = 1 order by birthday;##查询年级编号为1得学生信息并按照出生日期升序排序
41 ##查询所有女学生按年龄从大到小排序从第2条记录开始显示6名女学生得姓名、年龄、出生日期、手机号码信息
42 select studentname, floor(datediff(now(),birthday)/365)as 年龄, birthday, phone from student where sex = '' order by birthday asc limit 1,6; 
43 ##查询按出生年份分组统计学生人数,将各组中人数达到两人得年份和人数显示出来
44 select year(birthday)as 出生年份, count(studentno)as 人数 from student group by year(birthday) having 人数>=2;
45 ##查询年龄比李斯文小得学生
46 select * from student where birthday > (select birthday from student where studentname = '李斯文');
复制代码

 

posted @   是小杰哦  阅读(309)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂
· 凌晨三点救火实录:Java内存泄漏的七个神坑,你至少踩过三个!
点击右上角即可分享
微信分享提示