mysql基础

-- *********对整个数据库的操作************
-- cmd字符集调整方法，避免乱码
找到mysql的配置文件作以下修改（my.ini）

[client]
default-charset-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
修改后重启，然后进入mysql输入 \s即可查看字符集
命令行上下键，即可快速返回前几个命令
-- 创建数据库
create database `stuM` charset=utf8;
-- 选择数据库
use `stuM`;
-- 删除数据库
drop database `stuM`;
-- 显示所有的数据库
show databases;
-- 查看创建数据库的sql
show create database `stum`;

-- 反引号目的是为了防止表名或者字段为保留关键字，起规避作用，一般不要起这种名字，也就是说一般不要用反引号，如果不加反引号不能执行成功的就不要起这个名字
-- 规范起见，表名和字段名不允许使用保留关键字，那样反引号就可以放弃了

-- *********对整个表的操作************
-- 创建学生表
create table `Students`(
`id` int primary key not null auto_increment comment '编号',
`sId` char(8) not null comment '学号',
`sName` varchar(8) not null unique comment '姓名',
`sMajor` char(12) not null comment '专业',
`sSex` enum('男','女') default '女' comment '性别',
`sBirth` timestamp not null comment '生日',
`sBNum` int not null comment '借书数量',
`sHeadPic` blob null comment '头像' ,
`sMoney` float(8) not null comment '奖学金',
)engine=myisam;
-- 修改表的名字
alter table `borrow` rename to `bo`;
-- 修改表的类型
alter table `bo` type = innodb;
-- 删除表
drop table `test`;
drop table t1,t2,t3,t3;
-- 显示所有的表
show tables;
-- 查看表的结构
desc `students`;
-- 查看建表SQL
show create table `students`;
-- 如果是在命令行(\G后没有分号)
show create table `students` \G


-- *********对表中字段的操作************
-- 给表末尾添加一个新字段
alter table `students` add `test` tinyint null;
-- 给表开头添加一个新字段
alter table `students` add `test` tinyint null first;
-- 给指定字段后面添加一个新字段
alter table `students` add `test` tinyint null after `sBirth`;
-- 删除表中一个字段
alter table `students` drop column `test`;
-- 修改字段的名称和类型
alter table `students` change `test` `teee` int;
-- 修改字段的类型，但是不能改字段的名字
alter table `students` modify `teee` tinyint;
-- 修改是否空值
alter table `students` modify `teee` char(10) not null;
-- 修改默认值
alter table `students` alter `teee` set default '女';


-- *********对表中记录的操作************
-- 添加记录
insert into `students` values
('', '字段2', '字段3'),
('', '字段2', '字段3'),
('', '字段2', '字段3'),
('', '字段2', '字段3');
-- 修改记录
update `student` set `name`='小明',`age`=18 where id = 1;
-- 将所有学生的年龄更新为最新年龄（当前年份-出生年份）。
update Students
set Sage = YEAR(now()) - YEAR(Birthday)
-- 将计算机网络成绩不及格的都加分
update SC
set Grade = Grade + 5
where Grade < 60 and Cno = (select Cno from Courses where Cname = '计算机网络')
-- 删除记录
delete from `students` where `name`='小明';
	--	将所有没有选课的同学删除
	delete from Students
	where Sno not in (select Sno from SC)

-- 清空表中记录
truncate `students`;

-- *********重点：查询操作************
-- 简单查询
  -- select 子句
    -- 查询所有学生的信息(选择所有列)
    select * from `students`;  # 查询数据最好不要用这个，会拖慢速度
    -- 查询所有学生的学号和姓名(指定列)
    select `sno`,`sname` from `students`;
    -- 查询每个学生的入学日期和姓名
    select left(`sno`,4) as startTime,`sname` from `students`;
  -- where条件查询
    -- 找出4个学分以上（包括4个学分）的课程信息
    select * from `courses` where Ccredit >= 4;
    -- 找出2-5个学分的课程名称，学分以及先修课(分别用between...and...和in实现)(此外还有not between ..and..和not in)
    select `Cname`,`Ccredit`,`Cpno` from `courses` where Ccredit between 2 and 5;
    select `Cname`,`Ccredit`,`Cpno` from `courses` where Ccredit in(2,3,4,5);
    -- 逻辑连接词 not and or ! && ||
    select `Cname`,`Ccredit`,`Cpno` from `courses` where Ccredit >= 2 and Ccredit <= 5;
    -- 找出姓王的学生的详细信息('%'：任意长度字符;'_':任意一个字符;[]:指定范围内的单个字符;[^]:不在指定范围的单个字符;)
    select * from `students` where `Sname` like '王%';
    -- 查询课程名称含有数据两个字的课程名称及学分
    select `Cname`,`Ccredit` from `Courses` where `Cname` like '%数据%';	
    -- 找出先修课位置的课程编号和名称(此外还有is not null)
    select `Cno`,`Cname` from `courses` where `Cpno` is null;
    -- 查询所有男性讲师的姓名和年龄
    select `Tname`,year(now())-year(`Tbirth`) Tage from `teachers` where tsex='男';	
  -- distinct
    -- 列出所有学生的专业，不包含重复专业
    select distinct `Speciality` from `students`;
  -- limit
  -- 找出年龄最大的三位同学的姓名、性别、专业
  select `Sname`,`Ssex`,`Speciality` from `students` order by `Sage` desc limit 3;
  -- order by
    -- 查询所有学生的姓名年龄，并按年龄降序排列(升序 ASC)
    select `Sname`,`Sage` from `students` order by `Sage` desc;
  -- group by  -- 分组之后附加条件用 having 不用where，且having的条件可以用聚集函数
    -- 找出平均成绩大于的学生的学号、姓名、以及平均成绩；
    select SC.Sno,Students.Sname,AVG(SC.Grade) 平均成绩
    from SC join Students on SC.Sno = Students.Sno
    group by SC.Sno,Students.Sname
    having AVG(SC.Grade) > 80
    -- 找出选修课程数目最多的学生学号和姓名
    select SC.Sno,Students.Sname 
    from SC join Students on SC.Sno = Students.Sno
    group by SC.Sno,Students.Sname
    order by COUNT(SC.Cno) desc
    limit 1

-- 多表查询
  -- 内连接(inner可以省略，根据on的条件进行连接，如果在源表中没有对应的行，则该行会被过滤掉)
    -- 找出学号为“2010001”的学生所选修的课程编号、名称及相应成绩，并按成绩降序、课程名称升序排列
      select `SC`.`Cno`,`Cname`,`Grade`
      from `SC` inner join `Courses` on `SC`.`Cno` = `Courses`.`Cno` 
      where `SC`.`Sno` = '2010001' 
      order by `Grade` desc,`Cname` asc;
    -- 查询刘亦菲所选修课程的最高分、最低分、总分、平均分；
	  select MAX(Grade) 最高分,MIN(Grade) 最低分,SUM(Grade) 总分,AVG(Grade) 平均分
	  from SC join Students on SC.Sno = Students.Sno
	  where Students.Sname = '刘亦菲'
    -- 左连接(left join，左表中不符合连接条件的行和内连接的行)
      -- 把所有班的总人数打印出来，前提是有些班没有人，人数为0的则打印出无
      select class.name,if(count(user.id),count(user.id),'无') from class left join user on class.id=user.class_id group by class.id;
    -- 右连接(right join，右表中不符合连接条件的行和内连接的行)
      --  
      select class.name,if(count(user.id),count(user.id),'无') from user right join class on class.id=user.class_id group by class.id;
    -- 外连接(左右表中不符合连接条件的行和内连接的行)mysql不支持，可以用union把左和右连接实现外连接
  -- 自身连接
    -- 找出与猪八戒同一个专业的男生的姓名
    select s1.Sname
    from Students s1 join Students s2 on s1.Speciality = s2.Speciality
    where s2.Sname = '猪八戒' and s1.Ssex = '男' and s1.Sname <> '猪八戒' ;
    -- 找出与猪八戒不在同一个专业的学生的姓名
    select s1.Sname 
    from students s1 join students s2 on s1.speciality <> s2.speciality and s2.sname = '猪八戒' 
  -- 多表连接
    -- 找出“计算机网络”成绩不及格的学生的姓名及成绩；
    select Students.Sname,SC.Grade
    from Students join SC on Students.Sno = SC.Sno join Courses on SC.Cno = Courses.Cno
	where SC.Grade < 60 and Courses.Cname = '计算机网络'
	group by Courses.Cname,Students.Sname,SC.Grade

  -- 联合查询(union默认去处重复行，如果在union后面加个all则为不去除重复行)
  select Sname from students
  union
  select Tname from teachers;
-- 子查询（相关子查询：子查询依赖父查询，不能单独执行；不相关子查询：子查询独立可单独执行）
  -- 单值子查询(子查询只返回一个值)
  -- 找出与猪八戒同一个专业的男生的姓名
    select Sname
	from Students
	where Ssex = '男' and Speciality = 
	(
		select Speciality
		from Students
		where Sname = '猪八戒'
	)and Sname <> '猪八戒'

  -- 多行子查询(子查询返回单列多行数据)
  -- 多层嵌套子查询(子查询里面套子查询)
  -- 找出选修数据库的学生的姓名
    select Sname
    from Students
	where Sno in 
	(
		select Sno
		from SC
		where Cno = 
		(
			select Cno
			from Courses
			where Cname = '数据库'
		)
	)
	-- 找出没有选修数据库的学生的姓名（not in）
	select Sname
	from Students 
	where Sno not in
	(
		select Sno
		from SC
		where Cno = 
		(
			select Cno 
			from Courses
			where Cname = '数据库'
		)
	)
	-- 找出没有选修数据库的学生的姓名（not exists）
	select Sname
	from Students 
	where not exists
	(
		select *
		from SC
		where exists
		(
			select * 
			from Courses
			where Cname = '数据库' and Students.Sno = SC.Sno and SC.Cno = Courses.Cno 
		)
	)

	-- 查出表中及格和不及格人数，用一条sql语句写出来
	1.select (select count(*) from sc where grade >= 60) 及格,(select count(*) from sc where grade < 60) 不及格;
    2.select sum(if(grade>=60,1,0)) 及格,sum(if(grade<60,1,0)) 不及格 from sc;

  -- 相关子查询




-- 表索引（加快检索速度）
desc 表名;   -- 查看表结构
	desc sql语句 \G   -- 查看这个sql语句的好坏信息，\G是行列颠倒过来，防止行过长出现折行，主要看返回值的row这个字段
	-- 查询的时候最好用有索引的字段，如id

  -- 添加普通索引
  alter table t1 add index idx_name(name);
  -- 删除普通索引
  alter table t1 drop index idx_name;

  -- 添加主键索引，一般是在创建表的时候添加
  alter table t1 add primary key(id);
  -- 删除主键索引，一般为id,先删除自增，再删除索引
  alter table t1 modify id int unsigned not null;
  alter table t1 drop primary key;
  -- 添加唯一索引
  alter table t1 add unique uni_name(name);
  -- 删除唯一索引(注意删除的语法)
  alter table t1 drop index uni_name;


-- 函数
concat('woshi', @x);  #字符串连接
   select concat(username, '-', class_id) from user;  -- 把用户名和班号通过短横线连接输出
rand();
  select * from user order by rand() limit 3;  #随机输出三条数据
in();   
year();   #从日期中取出年
now();    #获得当前的日期时间
sum();
avg();
min();
max();
count();
substring();
 select count(*) from user;  -- 统计个数用这个，这个是优化过的速度快,比count(id)好



有个点菜表（order） 字段有菜品ID（id）和点菜时间（time），用sql语句查询出来点菜次数最多的菜品ID和最后的点菜时间
select count（*），max（点菜时间）
from order
group by id 
having count（*）≥all（ 
select count（*）
from order
group by id ）

原文地址： https://blog.youkuaiyun.com/qq_42195688/article/details/80579574