mysql常用sql与命令之从入门到删库跑路_Mysql_

 create table 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n );

 insert into user2 values (1,'李四','123'); // 增 delete from pet where ower = 'disn'; //删 update pet set name = '后裔' where ower = 'dfn'; //改

 ①> 、< 、<= 、>= 、= 、<> ②BETWEEN...AND ③ IN( 集合) ④LIKE 模糊查询 ⑤_单个任意字符 ⑥%多个任意字符 ⑦IS NULL ⑧and 或 && ⑨or 或 || ⑩not 或 ! 查询条件应用举例: SELECT * FROM user WHERE age >= 18; SELECT * FROM user WHERE age >= 18 AND age <=36; SELECT * FROM user WHERE age BETWEEN 40 AND 70; SELECT * FROM user WHERE age IN (6,18,37); // 关于NULL SELECT * FROM user WHERE height = NULL; 错误,因为null值不能使用=或（!=) 判断 SELECT * FROM user WHERE height IS NULL;(正确) SELECT * FROM user WHERE height IS NOT NULL;(正确) // 查询姓陈的有哪些？< like> SELECT * FROM user WHERE NAME LIKE '陈%'; // 查询姓名第二个字是新的人 SELECT * FROM user WHERE NAME LIKE "_新%"; // 查询姓名是三个字的人 SELECT * FROM user WHERE NAME LIKE '___'; // 查询姓名中包含狗的人 SELECT * FROM user WHERE NAME LIKE '%狗%'; 

 create table user ( id int primary key auto_increment, // 在创建表时，添加主键约束，并且完成主键自增 name varchar(20) ); -- 联合主键: 由多个字段联合组成的主键, 只要联合的主键加起来不重复就可以.联合主键中的任何一个字段都不能为空. create table user2 ( id int, name varchar(20), password varchar(20), primary key(id, name) ); 

 create table user1 ( id int primary key auto_increment, phone_num varchar(20) unique ); create table user2 ( id int primary key auto_increment, name varchar(20), unique(id, name) // 表示两个字段在一起不重复就可以 ); 

 create table user3 ( id int primary key auto_increment, name varchar(20) not null ); 

 create table user4( id int primary key auto_increment, age int default 18, name varchar(20) not null ); 

 create table 表名( .... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) ); // 班级 create table classes( id int primary key, name varchar(20) ); // 学生表 create table student ( id int primary key, name varchar(20), class_id int, foreign key(class_id) references classes(id) ); 

 查询所有记录 例如:查询student表中的所有记录. select * from student; 查询指定字段 例如:查询student中的sname,ssex,class. select sname,ssex,class from student; 查询教师表中所有的单位即不重复的depart列. <排除重复distinct> select distinct depart from teacher; 查询score表中成绩在60到80之间的所有记录 <查询区间 between…and…> select * from score where degree between 60 and 80; select * from score where degree > 60 and degree < 80; 查询score表中成绩为85,86或88的记录 select * from score where degree in(85, 86, 88); 查询student表中'95031'班或性别为'女'的同学记录.  select *from student where class = '95031' or sex = '女'; 以class降序查询student表的所有记录 <降序:desc, 升序asc,默认升序(省略)>. select * from student order by class desc; 以cno升序,degree降序查询score表的所有记录 select * from score order by cno asc,degree desc; 查询"95031'班的学生人数 <统计 count> select count(*) from student where class = '95031'; 查询score表中最高分的学生学号和课程号(子查询) select sno, cno from score where degree = (select max(degree) from score );其中:select max(degree) from score 先查出最高分. select sno,cno degree from score order by degree desc limit 0,1;其中:limit第一个数字表示从多少开始,第二个表示多少条.当有多个相同最高分时,容易出bug,不推荐使用这种方式查询. 查询每门课的平均成绩 select cno, avg(degree) from score group by cno; 查询score表中至少有2名学生选修的并以3开头的课程的平均分数. select cno, avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%'; 查询分数大于70, 小于90的sno列. select sno, degree from score where degree between 70 and 90; 查询所有学生的sname, cno和degree列. select sname, cno, degree from student, score where student.sno = score.sno; 查询所有学生的sno,cname和degree列 select sno,cname,degree from course ,score where course.cno = score.cno; 查询"95031"班学生每门课的平均分. select cno, avg(degree) from score where sno in (select sno from student where class = '95031') group by cno; 查询选修"3-105"课程的成绩高于"109"号同学"3-105"成绩的所有同学的记录. select * from score where cno = '3-105' and degree > (select degree from score where sno = '109' and cno = '3-105'); 查询成绩高于学号为"109", 课程号为"3-105"的成绩的所有记录 select * from score where degree > (select degree from score where sno = '109' and cno = '3-105'); 查询和学号为108,101的同学同年出生的所有的sno, sname, sbirthday select *from student where year(sbirthday) in (select year(sbirthday) from student where sno in(108, 101)); 查询"张旭"教师任课的学生成绩 select * from score where cno = ( select cno from course where tno = (select tno from teacher where tname = "张旭")); 查询选修某课程的同学人数多于5人的教师姓名. select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*) > 5)); 查询存在有85分以上的成绩的课程的cno select cno, degree from score where degree > 85; 查询出"计算机系"教师所教课程的成绩表 select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = "计算机系")); 查询选修编号为"3-105"课程且成绩至少高于选休息编号为"3-245"的同学的cno,sno和degree,并按degree从高到低次序排序. any 至少一个. 

 select * from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;

 select * from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245') order by degree desc;