MySQL基础知识笔记

mysql里有三种注释
1.多行注释/**/
2.单行注释#
3.单行注释--

乱码问题：
字符编码不统一造成的，解决方案：保证数据库字符编号与编程工具字符编码与浏览器字符编码相一致

#建表语句
-- create table 表名(
--     属性名 类型(长度),
--     属性名 类型(长度),
--    属性名 类型(长度),
--    属性名 类型(长度),
--     属性名 类型(长度),
-- );

例如：

-- create table food(
--     id int(6),
--     name varchar(20),
--     pic varchar(50)
-- );

建表语句注意事项：
1.关键字不区分大小写
2.表名，字段名，不区分大小写
3.建表语句名字后面跟着小括号，小括号后边跟着分号
4.括号内存放字段信息（字段=列）
5.字段之间用逗号分隔，最后一个靠近右括号的字段不需要逗号
6.缩进是老师人为添加的，用于美化语言结构
7.缩进用的是Tab键

字段类型
int存放的是整数，有长度，可默认不填，默认最大长度为11
范围，-2^31到2^31-1, 2147483648
varchar存放的是字符串，有长度，必填
datetime存放的是yyyy-MM-dd HH:II:SS格式的时间，无长度，不能设置
data存放的是yyyy-MM-dd格式的时间，无长度，不能设置
text大型文本信息
decimal存放金额类型数据，
长度必填，参数有两个，第一个，长度，第二个，小数点
第二个参数默认不填时，表示没有小数点


-- emp员工表
-- dept表示部门表
#主键约束
# 一个表至多有一个主键
# 一个表主键的数据具有唯一且非空的特点
-- create table dept(
--     deptno int(4) primary key（主键）,
--     dname varchar(20) not null（不为空）,
--     loc varchar(50)
-- ); 
#唯一约束，unique，设置为唯一的字段，内容唯一，有且只有一个，可以为空
-- 创建雇员表，表中要有雇员编号empno，雇员名称ename，工资sal,
-- 岗位job，出生日期birth，手机号唯一tel，性别sex，部门编号deptno
-- drop table emp;
-- create table emp(
--     empno int(6) ,
--     ename varchar(20),
--     sal DECIMAL(7,2),
--     job varchar(10),
--     birth date,
--     tel varchar(11) unique,
--     sex varchar(1) default '男',
--     deptno int(6)
-- );
#修改表结构
#新增字段，修改字段，删除字段，对字段重命名
#给员工表加入字段，身份证号
-- alter table emp add shenfenzhenghao int(11);
#修改身份证号名称，改名为Idcard
-- alter table emp change shenfenzhenghao IDcard int(6);
#修改身份证号字段类型及长度
-- alter table emp modify IDcard varchar(18);
#删除身份证字段
-- alter table emp drop idcard;
-- alter table emp modify empno int(6) PRIMARY key auto_increment;
-- alter table emp add IDcard varchar(18) unique not null ;

-- sid    int    学号，主键，自增
-- sname    varchar(20)    姓名
-- birth    date    出生日期
-- sex    char(2)    性别
-- telephone    int    电话
-- address    varchar(50)    地址

-- create table student(
--     sid int(6) primary key auto_increment,
--     sname varchar(20),
--     birth date,
--     sex char(2),
--     telephone INT    ,
--     address varchar(50)
-- ); 
-- tid    int    老师编号，主键，自增
-- tname    varchar(20)    姓名
-- sex    varchar(2)    性别
-- profession    varchar(20)    职称，如教授、讲师、副教授等

-- create table teacher(
--     tid int primary key auto_increment,
--     tname varchar(20),
--     sex varchar(2),
--     porfession varchar(20)
-- ); 

-- sid    int    学号，非空
-- cid    int    课程号 
-- score    double    成绩
-- create table sc(
--     sid int not null,
--     cid int ,
--     score double
-- );

-- cid    int    课程号，主键
-- cname    varchar(20)    课程名称
-- tid    int    老师编号，是teacher表外键
-- credit    double    学分
-- create table corse(
--     cid int PRIMARY key,
--     cname varchar(20),
--     tid int ,
--     credit double,
--     constraint fk_tid foreign key(tid) references teacher(tid)
-- );
-- alter table corse rename  course;
-- 1.将学生表的address字段的字段长度改为100
-- alter table student modify address varchar(100);
-- 2.将学生表的telephone字段名字改为phone，同时字段类型改为varchar(20)
-- alter table student change telephone phone varchar(20);
-- 3.将教师表中添加education字段，字段类型为varchar（10）
-- alter table teacher add education varchar(10);
-- 4.将教师表中sex字段类型改为varchar(3)
-- alter table teacher modify sex varchar(3);
-- 5.将课程表中添加描述字段desc，字段类型varchar，长度100
-- alter TABLE course add `desc` varchar(100);
-- 6.将课程表中的cid字段，设置成自增长
-- alter table course modify cid int auto_increment;
-- 7.将成绩表中的cid字段，设置成非空约束
-- alter table sc modify cid int not null; 
-- 8.将成绩表中的score字段的字段类型改为int
-- alter table sc modify score int; 
#插入语句
-- insert into 表名 values('属性值','属性值','属性值');
-- insert into dept 
-- values
-- ('20','保安部','山西太原'),
-- (30,'销售部','北京市'),
-- (40,'宣传部','上海');
-- insert into emp values
-- ('1','张三','3500','员工','2010-01-01','15511112222','女','10','370211111111111111');
-- insert into emp values
-- ('2','李四','3800','员工','2005-01-01','15511112223',default,'10','370211111111111112');

-- insert into emp(ename,sal,job,deptno) 
-- values('王五','6600','经理','20');
-- insert into emp(ename,sal,job,deptno) 
-- values('王武','3600','员工','30');
-- insert into emp(job,deptno,ename,sal) 
-- values('员工','30','刘备','3600');

-- insert into sc values(1,1,90),
-- (1,2,80),
-- (1,3,90),
-- (2,1,70),
-- (2,2,60),
-- (2,3,80),
-- (3,1,80),
-- (3,2,80),
-- (3,3,80),
-- (4,1,50),
-- (4,2,30),
-- (4,3,20),
-- (5,1,76),
-- (5,2,87),
-- (6,1,31),
-- (6,3,34),
-- (7,2,89),
-- (7,3,98);

-- drop table emp;

#修改
-- update 表名 set 属性=属性值 ;
-- update emp set ename = '王5' where empno=107;
-- update emp set deptno=10 where ename="刘备" and sal>5000;
-- update emp set sex="女" where ename="王武"; 
-- update emp set sex="男" ,job="经理",sal="7500" where empno=108;
-- update emp set sal=sal*0.8 where job="员工";
-- 1.将姓名为孙风的学生的地址改为“黑龙江省牡丹江市”
-- UPDATE student set address="黑龙江省牡丹江市" where sname="孙风";
-- 2.将出生日期在1990-01-01到1990-04-30的学生的性别改为男
-- UPDATE student set sex="女" where birth>="1990-01-01" and birth <= "1990-04-30";
-- UPDATE student set sex="男" where birth between "1990-01-01" and "1990-04-30";
-- 3.将姓名为张三的老师名字改为张三一
-- UPDATE teacher set tname="张三一"  where tname="张三";
-- 4.将张姓的老师职称改为助教
-- UPDATE teacher set porfession="助教" where tname like '张%';
-- 5.将JAVA课程的学分在原来基础上加+1
-- update course set credit=credit+1 where cname="JAVA基础";
-- 6.将高等数学课程的学分减为原来的一半，假设不知道原来学分是多少
-- update course set credit=credit*0.5 where cname="高等数学";
-- update course set credit=credit/2 where cname="高等数学";
-- 7.将成绩小于10的学生的成绩加10分
-- update sc set score=score+10 where score<30;
#delete 
-- delete  from food where name="西红柿";
-- delete from food where  id>3;
-- delete from food ;
-- 查询
#全匹配查询
-- SELECT * from emp ;
 #部分字段查询
-- select empno,ename,sal,deptno from emp;
-- #where条件查询
-- select * from emp where deptno=10;
-- select * from emp where deptno!=10;
-- select * from emp where deptno<>10;
-- select * from emp where sex="男" and sal > 5000;  
-- select * from emp where sal>5000 or job="经理";
#算数计算
-- select empno,ename,sal,sal*12,deptno from emp;
-- select empno,ename,sal,sal*15+1000+1000+1200+1200+1200+500*12,deptno from emp;
#别名employee emp e
#别名作用，1简化长度，2，说明用
-- select empno,ename,sal,sal*15*0.01 as 个税,sal*15+500*12 年薪,deptno from emp;
#去重查询
-- SELECT distinct deptno from emp;
#模糊查询
#两种匹配符，%和_ %表示任意位数的占位符  _表示一位占位符   
-- select * from emp where ename like '%五%';
-- select * from emp where ename like '_五';
-- select * from emp where ename like '_五_';
#联合查询
-- select empno,ename from emp UNION
-- select sid,sname from student;
-- #排序查询
-- #进行排序时，默认按升序排列
-- select * from emp order by sal asc;asc升序
-- select * from emp order by deptno desc;desc降序
-- select * from emp order by deptno;
-- select * from emp order by sal desc,deptno ;

-- #in
-- select * from emp where deptno in(10,20);
-- select * from emp where deptno =10 or deptno=20;
-- #空值查询
-- select * from emp where job is null;
-- select * from emp where sal is null;
-- select * from emp where sal is not null;
-- select * from emp where job is not null;
-- -- 以下两个查询，都不是空值查询
-- select * from emp where job='(Null)';
-- -- 空字符串，不是NULL
-- select * from emp where job='';

#limit limt a,b  a表示从第n+1条数据开始查询，b表示查询几条数据 
-- 第一页显示 1,2 两条信息
-- 第二页显示 3，4两条信息
-- 第三页显示 5,6 两条信息
-- 第四页显示 7   一条信息
-- 分页要必备几个信息，总条数，每页显示的条数 传递给后台当前是第几页
-- 当前页=1，查询0,1两条数据  n表示当前页  y表示每页显示几条信息，当前案例y=2
-- 当前页=2，查询2,3两条数据
-- （n-1）*2,2       4,2
-- select * from emp limit 3,2   从第四条数据开始查询，查询两条信息
-- select * from student limit 7,2;
#分组语句
#max,min,avg,sum,count ,group by , having
-- select max(sal) from emp;
-- select min(sal) from emp;
-- select avg(sal) from emp;
-- select round(avg(sal),2) from emp;
-- select sum(sal) from emp;
-- select count(*) from emp;

-- 如果表中某个column字段是非空的， count（column）== count(*) == count(1)
-- 如果表中某个字段column可以为空，count(column)不一定等于count(*)
-- select count(job) from emp;
-- select count(*) from emp where job is not null;
-- select deptno,count(*) from emp group by deptno;
-- select deptno,avg(sal) from emp group by deptno;
-- -- select 语句中出现的列(字段)一定要出现字group by语句中
-- select deptno,job,count(*) from emp group by deptno,job;
-- HAVING 有group by不一定有having，有having一定有group by
-- select deptno,avg(sal) from emp where deptno!=10 group by deptno having avg(sal)>4000 ;
-- select deptno,avg(sal) from emp where sex='男' group by deptno having avg(sal)>5000;
-- 练习
-- distinct（去重）
-- 1.查找学生所在的地址有哪些，无重复
-- select distinct address from student;
-- -- 2.查找课程表都有哪些学分
-- select distinct credit from course;
-- -- > >= < <= = <>
-- -- 1.查找女性学生的信息
-- select * from student where sex!='男';
-- 2.查找成绩在85以上的学生学号（去重）
-- select distinct sid from sc where score>85;
-- -- 3.查找1990年9月1号以后出生的学生信息
-- select * from student where birth>'1990-09-01';
-- -- 4.查找学号除2号外的学生信息
-- select * from student where sid!=2;
-- AND    OR
-- 1.查找1990年9月1号起出生的女学生信息
-- select * from student where sex='女' and birth>='1990-09-01';
-- 2.查找女性或1990年9月1号以后出生的学生信息
-- select * from student where sex='女' or birth>='1990-09-01';

-- Like
-- 1.查找名字中含有“菊”的学生的信息
-- select * from student where sname like '%菊%';
-- 2.查找地址在山东的学生的信息
-- select * from student where address like '%山东%';
-- -- 3.查找王姓老师的信息
-- select * from teacher where tname like '王%';
-- BETWEEN…AND…
-- 1.查找出生日期在1990-01-01到1990-12-31之间的学生信息
-- select * from student where birth between '1990-01-01' and '1990-12-31';
-- 2.查找课程编号3成绩在60到90之间的学生学号和信息
-- select sid from sc where cid=3 and score BETWEEN 60 and 90; 
-- -- 3.查找学号在2-5号之间的学生信息
-- select * from student where sid BETWEEN 2 and 5;
-- 1.查找学生信息，按出生日期升序排序
-- select * from student order by birth asc;
-- 2.查找课程信息，按学分降序排序
-- select * from course order by credit desc;
-- 1.查找学号为4，5，6的学生的信息（IN或or两种写法）
--     select * from student where sid in(4,5,6);
-- 2.查找课程编号1和2的课程信息
--     select * from course where cid in(1,2);
-- -- 3.教师编号Tid=1、Tid=2的老师授课的课程名称和学分
--     select * from course where tid in(1,2);
-- 1.查询学生表中前5名学生的信息
-- select * from student limit 0,5;
-- -- 2.查询学生表按学号降序排序后的第3条到第6条的学生信息
-- select * from student order by sid desc limit 2,4;
-- 查询课程编号2成绩在前三名的成绩信息
--     select * from sc where cid=2 order by score desc limit 0,3;
-- 1.统计各学生的总成绩
-- select sid,sum(score) from sc group by sid;
-- 2.查询学分不超过3分的课程的个数
-- select count(*) from course where credit<=3;
-- 3.查询各个学生的学号、总成绩
-- select sid,sum(score) from sc group by sid;
-- 4.查询已有成绩的平均成绩大于等于85的学生的学号和总成绩和平均成绩
-- select sid,sum(score),avg(score) from sc group by sid having avg(score)>=85;
-- 5.查询各个学生参加的考试门数，输出学号、考试门数
-- select sid,count(sid) from sc group by sid;
-- 6.查询至少2门课程成绩都不及格的学生人数
-- select count(sid) from 
-- (select sid,count(*) from sc where score<60 group by sid having count(*)>=2) t;

-- 子查询
-- 单行子查询  ，子查询结果为一行的信息
# 查询工资比王五武高的人的工资
-- select sal from emp WHERE ename="王五武";
-- select * from emp where sal>3600;
-- 括号内的叫子查询语句，括号外的叫主查询语句
-- select * from emp where sal>(select sal from emp WHERE ename="王五武");
-- 多行子查询 ，子查询结果为多行的信息 any all in
#查询工资比20部门所有员工工资高的员工信息;
-- select * from emp where sal>all(select sal from emp where deptno=20);
-- -- 比所有员工工资高等效于 大于最大值
-- select * from emp where sal> (select max(sal) from emp where deptno=20);
#查询工资比20部门任意员工工资高的员工信息
-- select * from emp where sal>any(select sal from emp where deptno=20);
-- -- 比任意工资高等效于 大于最小值
-- select * from emp where sal>(select min(sal) from emp where deptno=20);
-- -- #查询工资比20部门所有员工工资低的员工信息;
-- select * from emp where sal<all(select sal from emp where deptno=20);
-- -- 比所有员工工资低等效于 小于最小值;
-- select * from emp where sal<(select min(sal) from emp where deptno=20);
-- -- #查询工资比20部门任意员工工资低的员工信息
-- select * from emp where sal<any(select sal from emp where deptno=20);
-- -- 比任意员工工资低等效于 小于最大值
-- select * from emp where sal<(select max(sal) from emp where deptno=20);
-- #查询工资和20部门任意员工工资一样高的员工信息
-- select * from emp where sal in (select sal from emp where deptno=20);
-- #in 的效果和=any 一样
-- select * from emp where sal=any(select sal from emp where deptno=20);
-- 1.查询比学号是5的学生选修的编号1课程成绩还低的学生信息
-- select * from student where sid in (
--     select sid from sc where cid=1 and score<(
--             select score from sc where sid=5 and cid=1
--     )
-- )
-- 2.查询编号1课程的成绩中，高于平均成绩的学生信息
-- select * from student where sid in(
--     select sid from sc where cid=1 and score>(
--         select avg(score) from sc where cid=1
--     )
-- );
-- 3.查询比李云总成绩要高的学生信息
-- select * from student where sid in (
--     select sid from sc group by sid having sum(score) > (
--         select sum(score) from sc where sid=(
--             select sid from student where sname="李云"
--         )
--     )
-- );
-- 4.查询和学号1选修的课程2考相同成绩的学生信息（不包含学号1的学生信息）
-- select * from student where  sid in(
--     select sid from sc WHERE sid!=1 and cid=2 and score=(
--         select score from sc where sid=1 and cid=2
--     )
-- );
-- 1.查找王姓的老师信息及其教授的课程信息
-- select t.*,cid,cname,credit FROM teacher t
-- inner join course c on c.tid=t.tid
-- where tname like '王%';    
-- 2.查找手机号是131开头的学生的学号、姓名和课程和成绩
-- select s.*,cname,score from student s,course c,sc
-- where s.sid=sc.sid and c.cid=sc.cid and phone like "131%";
-- 3.查找MySQL课程的授课老师信息。
-- select t.* from teacher t,course c
-- where t.tid=c.tid and cname ='MySQL数据库';
-- 4.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-- select sname,cname,score from sc
-- INNER JOIN course c ON c.cid=sc.cid
-- inner join student s on sc.sid=s.sid
-- where score>70;
-- 5.查询学生的所有课程，输出学号、姓名、课程名称、成绩
-- select s.sid,sname,cname,score from sc
-- INNER JOIN course c ON c.cid=sc.cid
-- inner join student s on sc.sid=s.sid
-- 6.查询课程编号为1且课程成绩在80分以上的学生的学号和姓名
-- select s.sid,sname from student s,sc
-- where s.sid=sc.sid and cid=1 and score>80; 
-- 7.查询学过"张"姓老师授课的同学的信息
-- select s.* from student s ,course c,sc,teacher t
-- where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid
-- and tname like '张%';
-- 8.查询各个学生的学号、姓名、总成绩
-- select s.sid,sname,sum(score) from student s 
-- inner join sc on sc.sid=s.sid
-- group by sc.sid;
-- 9.查询已有成绩的平均成绩大于等于85的学生的学号、姓名和总成绩和平均成绩
-- select s.sid,sname,sum(score),avg(score)from student s 
-- inner join sc on sc.sid=s.sid
-- group by sc.sid
-- HAVING avg(score)>85;
-- 10.查询各个学生参加的考试门数，输出学号，姓名、考试门数
-- select s.sid,sname,count(*) from sc,student s
-- where sc.sid=s.sid 
-- GROUP BY s.sid;
-- 11.查询JAVA课程成绩在前三名的成绩信息
-- select s.* from student s 
-- inner join sc on sc.sid=s.sid
-- inner join course c on sc.cid=c.cid
-- where cname="JAVA基础"
-- order by score DESC
-- limit 0,3;
-- 12.查找课程编号3成绩在60到90之间的学生信息
-- select s.* from student s
-- inner join sc on sc.sid=s.sid
-- where cid=3 and score BETWEEN 60 and 90;
/*
连接查询分为内连接和外连接
内连接   查询表中符合条件的部分信息
    显式内连接 
    隐式内连接
外连接
    左外连接    查询左表所有数据和右表部分数据
    右外连接        查询右表所有数据和左表部分数据
    完整外连接(仅oracle提供此方法)
    显示内连接和外连接句式为 
    select  xxx from 表1  
    xxx join 表2 on  表1.xxxx=表2.xxx  
    xxx join 表3 on  表1.xxx=表3.xxx……
    以下规则适用于内连接，左外连接
    左表：from后边第一个表
    右表，join右表的表
*/
-- 内连接
-- 显式内连接
-- select emp.*,dept.deptno dno,dept.loc,dept.dname from emp 
-- inner join dept on emp.deptno = dept.deptno;
-- 
-- select emp.*,dept.deptno dno,loc,dname from emp 
-- inner join dept on emp.deptno = dept.deptno;
-- 
-- select e.*,d.deptno dno,loc,dname from emp e
-- inner join dept d on e.deptno = d.deptno;
-- 
-- select e.*,loc,dname from emp e
-- inner join dept d on e.deptno = d.deptno
-- where d.deptno !=10;
-- 
-- -- 隐式内连接
-- select e.*,loc,dname from emp e,dept d
-- where e.deptno = d.deptno and d.deptno !=10;
-- 
-- -- 左外连接
-- select e.*,loc,dname from emp e
-- left join dept d on e.deptno = d.deptno;
-- -- 右外连接
-- select e.*,loc,dname from emp e
-- right join dept d on e.deptno = d.deptno;