1)查询数据库下表:show tables;2)创建表:create table student(id int(4) primary key,name char(20));注释:id为表的第一列; int数字类型; primary key主键的意思,列不能重复。Name为表的第二列名字。 char:类型; 创建表:create table score(id int(4) not null,class int(2));注释:not null字段不能为空。创建表:create table student1(id int(4) not null,name char(20));Field (列名),Type(字段类型),null(是否为空),key(主键)3)查看表结构:describe student; 或desc student;4)修改表名:alter table <表名> rename <表名>;5)删除表:drop table <表名>;6)修改表字段信息:alter table student change id id int(20);7)增加表字段信息:alter table student1 add class int(4) not null after id;8)删除一个表字段:alter table student1 drop number;
表数据的增删查改
提示:在数据库导入表时,要修改列的字段类型并设置主键;主键:表中经常有一个列或多列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束,而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据,所以经常用来定义标识列。1) 表数据新增格式:insert into 表格名(列名) values(值)先导入student和score表,表为Excel,可以自己编写。例子:mysql> insert into student(id,class,number,name) values(81,4,19,'stu81');mysql> insert into student(id,class,number) values(82,4,20);mysql> insert into student values(83,4,21,'stu83');mysql> alter table student change id id int(2) auto_increment;注释:auto_increment以1为单位自增长的意思;mysql> insert into student(class,number,name) values(4,22,'stu84');mysql> alter table score change id id int(4) auto_increment;注释:auto_increment自增长的意思。+1。输入该命令,表格会在新输入自动新增长新的一行,id也会成自增。mysql> insert into score(class,number,maths,chinese,english) values(4,19,80,78,98);mysql> insert into score(class,number,maths,chinese,english) values(4,20,98,88,68);mysql> insert into score(class,number,maths,chinese,english) values(4,21,91,83,78);mysql> insert into score(class,number,maths,chinese,english) values(4,22,67,83,88);2) 查询表数据格式:select * from <表名> where注释:语句以逗号做分隔,*通配符,select是展示的意思,where是条件;例子: 查询学生信息表中所有信息:select * from student;查询成绩表中,列id,class,chinese的信息:select id,class,chinese from score;3)表数据排序操作:升序:order by降序:升序语句末尾加desc例子:查询成绩表中,列id,chinese的信息并且以列chinese排序select id,chinese from score order by chinese;(升序)select id,chinese from score order by chinese desc;(降序)4)表数据查询操作:(1)查询1班与2班的成绩信息:mysql> select * from score where or;(2)查询语文为77并且数学为88的成绩信息:mysql> select * from score where chinese=77 and maths=88; (3)查询1,2,3班的成绩信息:mysql> select * from score where class in (1,2,3);查询不为4班的成绩信息: mysql> select * from score where class not in (4);(4)查询不为4班的成绩信息: mysql> select * from score where class !=4; 注释: !在数据库里面为否定的意思:(5) 查询1班到3班的成绩信息: mysql>select * from score where class between 1 and 3;注释:between:在```之间,中间的意思:(6) 查询不为3班与4班的成绩信息:mysql> select * from score where class not in (3,4);(7)查询语文成绩大于等于80小于等于90的成绩信息mysql> select * from score where chinese>=80 and chinese<=90;(8) 统计成绩表的总数:mysql> select count(*) from score;(9) 按照英语去重,显示英语成绩信息:mysql> select distinct English from score; 注释:distinct 去除重复的意思;(10) 显示4到7行的数据:mysql> select * from score limit 3,4;注释:数据库数据排列:0,1,2,3; 3显示第4行;4,5,6,7共有4行; 3,4 ; 3表示第4行,4表示从第3行开始到第7行,共有4行;(11) 按chinese排序,显示4,5行数据: mysql> select * from score order by chinese limit 3,2;(12) 查询出学生姓名为stu10的学生信息:mysql> select * from student where name='stu10';注释:只要不是数字,有汉字数字字母多种组成的形式都要加单引号,表示字符串。(13) 查询出学生姓名为stu10或者stu15的学生信息:mysql> select * from student where name in ('stu10','stu15');(14) 分组查询每个班的人数:mysql> select class,count(*) from student group by class;
分组与函数查询
温馨提示:分组之后查询其他函数结果是不正确的;分组函数:group by按班级分组,查询出每班数学最高分:select class,max(maths) from score group by class;不分班级查询总人数最高分: select max(maths) from score;注释: max:最大值;按班级分组,查询出每班数学最低分:select class,min(maths) from score group by class;注释:最小值min;按班级分组,查询出每班数学总分:select class,sum(maths) from score group by class;注释:sum:总分;按班级分组,查询出每班数学平均分:select class,avg(maths) from score group by class;注释:avg:平均值:按班级分组,查询出每班学生总数:select class,count(*) from score group by class;注释:count:有价值的;语句执行顺序: from先执行,后执行where, 再接着执行having,limit等。例句:select class,max(maths) from score where group by(分组) class having(所有) order by(排序) limitfrom后面可以加兹查询,语句先执行后面再执行前面
运算符:数学运算符
mysql> select class,number,maths,maths+5 from score;mysql>select class,number,chinese+maths+english from score;mysql> select *,maths+chinese+english as total from score;mysql> select *,maths+chinese+english as total from score order by total desc;mysql> select class*2,number,maths+chinese+english as total from score order by total desc;
连接查询
左连接查询:mysql> select stu.*,sc.*,maths+sc.chinese+sc.english from student stu left join score sc on stu.id=sc.id;注释:stu:为别名。student stu left join score:student:为主表,score为副表显示。left join:为左连接。两表关联:其ID必须一一对应(stu.id=sc.id);右连接查询:mysql> select stu.*,sc.*,maths+sc.chinese+sc.english from student stu right join score sc on stu.id=sc.id;内连接查询:两个表同时都有的内容才会显示。mysql> select stu.*,sc.*,maths+sc.chinese+sc.english from student stu join score sc on stu.id=sc.id;显示查询数据连接:把后表与前排合起来在一个表显示。select id,name,class from student union select class,number,maths from score;
多表查询
select name,student.class,student.number,maths,chinese,english from student,score where student.id=score.id;题目练习显示总分大于200的学生信息:select stu.name,sc.maths,sc.chinese,sc.english,sc.maths+sc.chinese+sc.english from student stu,score sc where stu.id=sc.id and sc.maths+sc.english+sc.chinese>200;select * from student stu,score sc where stu.id=sc.id and sc.maths+sc.english+sc.chinese>200; 显示班级总数大于等于20的班级:select class,count(*) as total from student group by class having total>=20;显示人总数大于等于20的班级的成绩信息:mysql> select sc.class,sc.number,sc.maths from score sc,(select class,count(*) as total from student group by class having total>=20) s where sc.class=s.class;注释:commit:保存提交的意思,一般文件删除修改都要做保存;Rollback:撤回的意思,命令执行后;可以撤回为修改删除前的数据;truncate table score:永久删除的意思,尽量少用,删除则无记录找回;select now():查询现在的时间;(MySQL数据库使用)select sysdate from cshtord :查询当前的日期(oracel 数据库中使用,select sysdate() :mysql数据库使用)
修改语句
update 表名 set where 条件mysql> update student set birth=1988,department='中文系' where id=901 and name='张老大';把张老大的出生日期修改为1988,院系修改成中文系mysql> update student set birth=birth-5;把所有学生的年纪增加5岁;
删除语句
格式:delete from 表名 where 条件 like 条件里面包含的信息;mysql> delete from student where id=901;删除字段信息:删除901同学的,学生信息mysql> delete from student where address like "湖南%";删除含有特定内容的字段信息:删除湖南籍学生的信息mysql> delete from student;删除表:清空学生表信息
字符查询like
%代表省略的字符,在前面表示前面还有内容;在后面表示后面还有内容;前后都有则表示前面都还有省略的内容。格式:select 需要查询的字段 from 表 where 条件 like 条件里面包含的信息;mysql> select * from student where address like '北京%';查询地址为北京的学生信息mysql> select * from student where address like '%北京%平%';查询地址为北京市昌平区的学生信息mysql> select * from score where stu_id in (select id from student where address like '湖南%');查询湖南籍学生的成绩信息;