创建数据表
命令
create database mydb charset utf8； 创建数据库，使用字符编码为utf-8 （create database 数据库名称 charset utf-8；）
show databases； //展示database里面内容
use sys； //使用数据库（use + 数据库的名称）
use mydb；
show tables； //显示所有数据表
desc 数据表名； //显示表的结构
实例1
-----------------------------------------------
/*创建数据表*/
create table student
(
id int primary key auto_increment，
name varchar(20)，
age int,
tel varchar(20)
);
----------------------------------------------------------
/*插入数据 ，字符串加单引号*/
insert into student(name,age,tel) values('小明',20,'166666666')
/*查找数据*/
select * from student;
------------------------------------------------------------
（navicat使用）
create database student; // 创建数据库
use student; //使用数据库
create table user //创建数据列表
(id int primary key auto_increment, //自动增加
username varchar(50),
password varchar(50)
);
insert into user(username,password) values('小明','12346');
//插入内容insert into 数据列表（，） 值（，）
insert into user(username,password) values('小红','13464')
利用可视化创建表
creat database if not EXISTS user； //如果不存在数据表创建数据表
use user；使用数据库
（id int primary key auto_increment, //自动增加auto_increment主要用于整数
username varchar(50),
password varchar(50)
);
drop table if exists user; //删除存在数据表
drop database if exists mydata; //删除存在数据表
show engines; //展示存在引擎
create database gradem;
use gradem;
CREATE TABLE student
(sno char(10) PRIMARY KEY,
sname varchar(8),
ssex char(2) DEFAULT '男',
sbirthday date DEFAULT '2004-01-01',
sid varchar(18),
saddress varchar(30),
spostcode char(6),
sphone char(18) DEFAULT '不详',
spstatus varchar(20),
sfloor char(10),
sroomno char(5),
sbedno char(2),
tuixue tinyint(1) NOT NULL DEFAULT 0,
xiuxue tinyint(1) NOT NULL DEFAULT 0,
smemo text,
classno char(8)
);
CREATE TABLE course
(cno char(3) NOT NULL,
cname varchar(20) NOT NULL,
cterm tinyint NOT NULL,
CONSTRAINT C1 PRIMARY KEY(cno,cterm)
);
CREATE TABLE sc
(sno char(10) NOT NULL,
cno char(3) NOT NULL,
degree decimal(4,1),
cterm tinyint NOT NULL,
CONSTRAINT A1 PRIMARY KEY(sno,cno,cterm),
CONSTRAINT A2 CHECK(degree>=0 and degree<=100),
CONSTRAINT A3 FOREIGN KEY(sno) REFERENCES student(sno),
CONSTRAINT A4 FOREIGN KEY(cno,cterm) REFERENCES course(cno,cterm)
);
插入语句格式（接上一节数据库)
Insert into student(sno,sname,ssex,sid,saddress,spostcode,spstatus,sfloor,sroomno,sbedno,classno)
values('19dsj0101','小明','男','10000000000','广东广州','51050','群众','1','101','01','19数据1');
insert into student(sno,sname,ssex,sid,saddress,spostcode,spstatus,sfloor,sroomno,sbedno,classno)
values('19dsj0102','小董','女','10000000001','广东广州','51050','群众','2','201','02','19数据1');
insert into student(sno,sname,ssex,sid,saddress,spostcode,spstatus,sfloor,sroomno,sbedno,classno)
values('19dsj0203','小李','男','10000000002','广东广州','51050','群众','3','301','03','19数据2');
insert into course(cno,cname,cterm) values('kc1','python','1');
insert into course(cno,cname,cterm) values('kc2','java','2');
insert into course(cno,cname,cterm) values('kc3','hadoop','3');
insert into sc(sno,cno,cterm) values('19dsj0101','kc1',1);
insert into sc(sno,cno,cterm) values('19dsj0101','kc2',2);
insert into sc(sno,cno,cterm) values('19dsj0101','kc3',3);
insert into sc(sno,cno,cterm) values('19dsj0102','kc1',1);
insert into sc(sno,cno,cterm) values('19dsj0102','kc3',3);
insert into sc(sno,cno,cterm) values('19dsj0203','kc1',1);
insert into sc(sno,cno,cterm) values('19dsj0203','kc2',2);
insert into sc(sno,cno,cterm) values('19dsj0203','kc3',3);
维护数据表以及数据表alter语句修改，查找语句
1.使用describe/desc查看表结构
命令语句：describe 表名 / desc 表名
2.使用alter table 语句修改表结构
Alter table student add province varchar(10) after sname;
//命令结构: alter table 表名 add 新字段名 数据类型 【若没有用（first/after）加字段名，字段将加在最后】
alter table student modify sbirthday varchar(10);
//命令结构alter table 表名 modify 字段名 新数据类型 完整的约束条件
alter table student change sbirthday sbirth date;
//命令结构 alter table 表名 change 旧字段名 新字段名 约束条件
alter table student drop smemo;
//命令结构 alter table 表名 drop 删除的字段名
alter table sc rename student_course;
//命令alter table student
SELECT * from student;
SELECT * from student limit 4;
select sno,sname,ssex,sbirth,sid,classno from student limit 4;
SELECT distinct classno as 班级 from student;
查询表
SELECT * from student;
//查询student表所有内容
SELECT * from student LIMIT 2;
//查询student表前两行的内容
SELECT sno,sname,sid,classno FROM student
//查询student表sno sname sid classno 的内容;
select * from student where classno='22数据2' ;
//查询student表内有关22数据2的内容
select * from student where ssex='男'and classno='22数据2';
//查询student表内数据内容为ssex和classno的内容
select * from student order by sid desc;
//让student表内数据根据sid，来降序（desc）。[默认为升序（asc）]
SELECT count(*) as 总人数 from student;
//统计总人数有几个人.
SELECT * from student where sid like '%2';
//查找身份证以二结尾
select classno,count(sno) as 班级人数 from student group by classno;
//查找以组分类的人数数据
select * from student_course where degree>=70 and degree <=90;
//查找成绩在70-90之间的人
聚集函数统计数据、交叉连接、内连接
select * from student_course where degree between 80 and 90;
select * from student where sid is null
select * from student where saddress regexp '^广'
select * from student where sid regexp '10'
select * from student where classno in ('2020数据1','2020数据2')
select sum(degree) as 总成绩 from student_course
select avg(degree) as 平均成绩 from student_course
select max(degree) as 最高成绩 from student_course
select student_course.* from student_course, course
select student.sno,sname,classno,cno,degree from student inner join student_course on student.sno=student_course.sno
select student.sno,sname,classno,cno,degree from student, student_course where student.sno=student_course.sno
select student.sno,sname,classno,cname,degree from student, student_course, course where student.sno=student_course.sno and student_course.cno=course.cno
内连接：查询学生学号、姓名、班级、选课的课程名、分数：学生表student、选课表sc、课程表course
SELECT student.sno,sname,classno,cname,degree from student,sc,course
where student.sno = sc.sno and sc.cno=course.cno
等价与下面语句
SELECT student.sno,sname,classno,cname,degree from student inner join sc on student.sno=sc.sno
inner join course on sc.cno =course.cno
自连接：查询同时选了编号为kc1 kc2的学号
SELECT sc1.sno from sc as sc1,sc as sc2 where sc1.sno=sc2.sno and sc1.cno ='kc1' and sc2.cno='kc2'
左外连接
select student.sno,sname,classno,cno,degree from student left join sc on student.sno=sc.sno
右外连接
select sno,degree,cname from sc right join course on sc.cno =course.cno
//全连接
SELECT * from student full join sc
//嵌套查询
//查询学生分数大于80的学生：学号 姓名 班级 选课的课程名 分数：学生表student 选课表sc 课程表sc
select * from
(select student.sno,sname,classno,cname,degree from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno)as t
where degree>80
//嵌套查询：查找选择了课程号为kc2的学生姓名
select sname from student where sno in (select sno from sc where cno='kc2')
//查询大于平均年龄的学生
select * from student
where(year(CURRENT_DATE)-year(sbirth)) < (select AVG(year(CURRENT_DATE)-year(sbirth))from student);
//查询没有选python的学生信息
select * from student
where sno not in
(select sno from sc
where cno not in (SELECT cno from course where cname='python')
)
where sno not in
(select sno from sc
where cno not in (SELECT cno from course where cname='python')
)
/*查找所有选修了课程号为kc1的学生姓名*/
select sname from student where sno in (select sno from sc where cno='kc1')
select sname from student where EXISTS (select * from sc where sno=student.sno and cno='kc1')
/*查询选修了全部课程的学生姓名*/
SELECT * from student where not EXISTS
(SELECT * from course where not EXISTS
(SELECT * from sc where student.sno =sc.sno and sc.cno=course.cno) )
/*查询成绩比该课程平均成绩高的学生学号及成绩*/
SELECT sno,cno,degree from sc as a where degree > (SELECT avg(degree) from sc as b where a.cno=b.cno)
/*插入语句：课程表插入数据*/
INSERT into course(cno,cname,cterm) values('kc9','history',4);
/*插入多条记录*/
INSERT into course(cno,cname,cterm) values('kc7','hbase',3),('kc6','kafka',3);
/*按照course表重新创建一个新的表*/
create table course2 like course
/*插入子查询的结果*/
insert into course2 select * from course
创建视图
create view stu_view(sno,name,sex,class)
as select sno,sname,ssex,classno from student
/*通过视图修改数据*/
insert into stu_view(sno,name,sex,class) values('23sj0101','小五','男','23数据1');
update stu_view set name = '小小五' where name='小五'
DELETE from stu_view where name='小小五'
/*修改视图*/
alter view stu_view
as select sno,sname,ssex,classno from student
/*删除视图*/
drop view stu_view
/*创建视图：从学生表，课程表，选课表，对选择了python课程的所有学生信息*/
create view sc_view as
select student.sno,sname,ssex,classno,cname from student,sc,course
where student.sno=sc.sno and course.cno and cname='python'
/*给课程表course的cno字段创建索引*/
create index idx_cno on course(cno);
/*给选课表sc的cno字段创建索引*/
alter table sc add index idx_sc(sno,cno);
/*删除索引*/
drop index idx_cno on course;
