数据库基础 MYSQL实现

最新推荐文章于 2023-05-18 13:48:56 发布

Allen-1

最新推荐文章于 2023-05-18 13:48:56 发布

阅读量242

点赞数

分类专栏： Mysql 数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44548432/article/details/116243944

版权

Mysql 数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

DDL：

使用数据库

use allen;

查看数据库的表

show tables;
select  * from  users;

创建表user1

create table user1(
id int primary key,
name varchar(12) not null,
age int ,
sex varchar(5),
birthday date,
password varchar(12));

select  * from  user1 ;

DDL：更改表的字段名

alter table users change usersname name varchar(12);

user1 插入数据

insert into user1(id,name,age,sex,birthday,password)
values(1,'甄士隐',23,'ma','1562-03-04','123456'),
(2,'贾雨村',25,'ma','1559-03-04','123456'),
(3,'贾宝玉',17,'ma','1567-03-04','123456'),
(4,'林黛玉',16,'fe','1568-03-04','123456'),
(5,'贾母',83,'fe','1502-05-04','123456')
;

创建emp表

create  table emp(
   id int primary key,
   name varchar(100) not null,
   gender varchar(10) not null,
   birthday date,
   salary float(10,2),
   entry_date date,
   resume text
);

select * from emp;

emp表中插入数据

insert  into emp(id,name,gender,birthday,salary,entry_date,resume)
values (1,'GT','female','1995-05-13',12000,'2010-09-01','good girl');

insert  into emp(id,name,gender,birthday,salary,entry_date,resume)
values (2,'BS','male','1994-05-12',13000,'2012-09-01','ambitious boy');

insert  into emp(id,name,gender,birthday,salary,entry_date,resume)
values (3,'BF','female','1993-05-11',14000,'2015-09-01','entrepreneurial girl');

insert  into emp(id,name,gender,birthday,salary,entry_date,resume)
values (4,'aaa','male','1991-04-11',11000,'2011-09-01','gentle man');

insert  into emp(id,name,gender,birthday,salary,entry_date,resume)
values (5,'Wu','male','1991-04-11',11000,'2011-09-01','a man who has sense of mission');

emp表数据完成更新操作

update emp set salary = 3000 where name='BS';

update emp set salary = 4000 where name='aaa'; 

update emp set salary= salary+1000 where name='Wu';

删除记录

delete from emp where name='aaa';

创建Manager表

create table Manager(
  mid int primary key,
  mname varchar(20),
  age int,
  sex char(2),
  password varchar(20),
  address varchar(20),
  phone  varchar(20)
);

插入数据到Manager

insert into Manager (mid,mname,age,sex,password,address,phone)
values(1,'大司马',23,'男','123','Shanghai','12580')
;

select  * from  Manager;

insert into Manager (mid,mname,age,sex,password,address,phone)
values(2,'大理寺 ',24,'女','134','Shanghai','17639'),
(3,'御史台',25,'男','256','Beijing','17638');

更新，删除操作

update Manager set age = 24  where mid =3;

update Manager set address ='TianJing'  where mid =3;

update manager set password='888888' where sex='女' and age >30; 

update manager set password='111111'; 

update  manager set phone=7654321 where phone<>110;

update manager set sex='女' ,address ='New York' ,age =18 where mname ='御史台';

delete from manager where mname='御史台';

delete from manager where address ='Nanjing' and age >60;

delete from manager where address <>'Beijing';

delete from manager where address ='Beijing' or address ='Shanghai';

delete from manager where phone ='' and phone is null;

show  tables;

– DCL C(CONTROL) 注意巧记变得只是三个单词中的一个 DDL D(DEFINITION)

创建用户，赋予增删改查权限

create user allen77@localhost identified by '123456';

grant select,insert,update,delete,create on allen.* to 'allen77'@'localhost';

show grants for 'allen77'@'localhost';

show databases;

grant all on *.* to 'allen77'@'127.0.0.1';

收回权限

revoke select on allen.* from allen77@localhost;

删除用户

drop user allen77@localhost;

select  * from stu s ;

#创建表stu

CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
#添加数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

#创建雇员表

CREATE TABLE emp2(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
#添加数据
INSERT INTO emp2 values
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20)     ,
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)  ,
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)   ,
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)  ,
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)  ,
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)  ,
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20)  ,
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10) ,
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)   ,
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);

创建dept表

CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);

#添加数据

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

– DQL Q(QUREY)

查询操作

select * from dept;

select dname,loc from dept;

select * from stu s  where s.gender ='female' and age <50;

select * from  stu s where s.sid ='S_1001' or sname ='liSi';

select * from stu s where s.sid in ('S_1001','S_1002','S_1003');

select * from stu s where s.sid not in ('S_1001','S_1002','S_1003');

select  * from  stu where age is null;

select * from stu s where age>=20 and age<=40;

select * from stu s where age   between 20 and 40;

select  *  from stu s where gender !='male';

select  *  from stu s where gender <>'male';

select  *  from stu s where  not gender ='male';

select  *  from stu s where not sname is null;

select  *  from stu s where sname is not null;

– 实现模糊查询 _代表的是任意的字符，z%代表的是开头以z字打头的 %a%代表的是包含a字母的

select * from stu s where s.sname like '___';

select * from stu s where s.sname like '____i';

select * from stu s where s.sname like 'z%';

select * from stu s where s.sname like '_i%';

select * from stu s where s.sname like '%a%';

– 字段控制查询 distinct 的使用,半角or全角，自己使用的时候注意

select * from emp2;

select distinct  gender from emp;

– 避免null值，给列取别名

select sal+ifnull(comm,0)  as sum from emp2 e ;

– 排序默认是升序 asc 降序是desc

select * from  stu s order by age  asc;

select  * from  stu s order by age  desc;

– 多列排序的情况，取了别名之后使用别名

select  * from emp2  e order by sal desc,empno asc ;

show tables;

– 聚合函数 count指定列名非null

select * from emp2 e ;

select count(*) as cnt from emp; 

select count(comm)  cnt from emp2;

select count(*) from emp where salary >2500;

select count(*) cnt from emp2 e where sal+ifnull(comm,0)>2500;

select count(comm),count(mgr) from emp2;

– emp2所有雇员的月薪和所有雇员的佣金和

select sum(sal),sum(comm)  from emp2 e ;

– emp2所有雇员的月薪＋佣金和

 select sum(sal+ifnull(comm,0)) usersall from emp2 e ;
  
   --select sum(sal+comm) usersall from emp2 e ;

– 所有雇员的平均工资

  select avg(sal) from emp2;

  select max(sal),min(sal) from emp2 e ;

– 分组查询
– 1 查询每个部门的部门编号和每个部门的工资和：

select deptno,sum(sal) from emp2 group by deptno;

– 2查询每个部门的部门编号以及每个部门的人数： sum(deptno) 是累加

select deptno,count(*) from emp2 e group by deptno ;

– 3查询每个部门的部门编号以及每个部门工资大于1500的人数：

select deptno,count(*) from emp2 e where sal >1500 group by deptno ;

– having 子句 having 和where的区别：1 having 是在分组后对数据进行过滤,where 是在分组前对数据进行过滤
– having 后面可以使用分组函数 where后不可用分组函数

– 查询工资总和大于9000的部门编号以及工资和：
– 原始写法： select deptno,sum(sal) from emp2 a where sum(sal)>9000;

 select  deptno,sum(sal) from emp2 group by deptno  having  sum(sal) > 3000;

– group by deptno

– 统计出stu表中每个班级的男女生各多少人多列分组

select * from  stu;

select gender,count(*) from stu s group by gender ;

– limit 用来限定查询结果的起始行，总行数

– 查询5行记录，起始行从0开始

select * from emp2 limit 0,5;

– 查询10行基础起始行从3开始

select * from emp2 limit 3,10;

– 分页查询

– 查询语句书写顺序：select – from- where- groupby- having- order by-limit
– 查询语句执行顺序：from - where -group by -having - select - order by-limit–

课后练习强化 – 练习1

insert into manager values 
(2,'公主',20,'女','456','上海','220'),
(3,'太子',23,'男','789','南京','330');

select  *  from  manager m  where MNAME ='公主';

select  MNAME from manager m where AGE between 18 and 30;

select Mname from manager m  where age>=18 and age<=30;

select MNAME,PHONE from manager m ; 

select * from manager m where SEX ='男' and  MNAME ='王子';

select * from manager m where ADDRESS = '上海' or ADDRESS ='北京';

– 练习2 scores 注意点：limit 0,3 取代 limit 3

create table scores(stuid int ,java int ,mysql int,stuname varchar(20));

select * from scores ;

insert into scores values 
(1,67,78,'张三'),
(2,87,55,'李四'),
(3,66,90,'王五'),
(4,98,78,'赵六'),
(5,80,88,'田七');

select * from scores order by java desc ; 

select * from scores order by mysql desc limit 0,3;

select * from  scores  order by java asc limit 0,1;

select * from scores where java>=80 and mysql>=80;

select * from scores where java>=90 or mysql>=90;

select java,mysql,java+mysql sum from scores;  

select java+mysql sum,stuname   from scores;

– 练习3

create table Student2 (stuname varchar(20),telephone varchar(20),
address varchar(20),subject varchar(20),stuNo varchar(20),score int,birthday date);

insert into student2 values
('郭敬明','1371234567','北京','java','S1101',89,'1979-04-05'),
('张三丰','1372839201','上海','数据库','S1102',67,'1967-09-07'),
('赵敏','1387839201','山东','mysql','S1103',99,'1987-09-07');

select stuname,telephone,address from student2 where address='山东'; 

select subject from student2 where subject like '%数据库%';

select  * from student2 where telephone like '1387%';
--  d.查询姓姜的，三个字的学生信息
select * from student2 where stuname like '姜__';  （双下划线代表字段）

--  e.查询学号为S1101的指定java,mysql科目考试成绩     ***读题之道：语文阅读理解能力***
select score from student2 where stuNo='S1101' and (subject='java' or subject ='mysql');

-- select * from student2 where birthday <= 1980-01-01;  -- 后的query有问题，参见正确写法
select * from student2 s where s.birthday between 1980-1-1 and 1989-12-31;

select stuname from student2 where address in ('北京','上海','广州');
-- h.显示成绩在第5-10名的学生名字和电话   此处注意limit的使用方法, 4,6  第四位的后六位
select stuname,telephone from student2 s order by score desc limit 4,6;


select * from student2 where (score between 80 and 90) and address='北京';

– 练习四聚合函数练习

create table scores2 (grade varchar(10),stuno varchar(20),examDate date,subject varchar(20),score int,xueqi int);

insert into scores2 values('S1','S1101','2015-02-03','C',89,1);
insert into scores2 values('S2','S1103','2015-03-03','JAVA',90,2);
insert into scores2 values('S3','S1102','2015-07-03','C',100,1);

select  * from scores2;

select count(*) from  scores2;

-- 2.学号为S1101的学生第一学期考试总成绩,平均分     **不遗漏条件** 

select  sum(score),avg(score) from  scores2  where stuno='S1101' and xueqi=1  ;

--  3.查询2013年3月22日科目“C”的最高分、最低分、平均分  
select max(score),min(score),avg(score)   from scores2 where subject='C' and examDate='2013-03-22';

select avg(score)   from scores2 where examDate='2013-03-22' and score>60 and subject='C';

select avg(score)   from scores2 where  subject='C';

select count(*) from scores2 where subject ='JAVA';

– 练习五分组练习

show tables;

create table student (grade varchar(10),name varchar(10),class_hours int,isexam char(1),subject varchar(10),score int);

insert into student values
('1','张三',10,'是','java',99),
('1','李四',10,'否','java',0),
('2','王五',20,'是','mysql',88),
('2','赵六',20,'是','mysql',77),
('2','王五',20,'是','java',99),
('2','赵六',20,'否','java',0),
('1','张三',10,'是','mysql',88);

– a:查询每个年级的总学时数，并按照升序排列 这里的order by 不再使用class_hours，因为我们全加起来了

select sum(class_hours) sumall from student group by grade order by sumall asc;

– b:查询每个参加考试的学员的平均分 自己用的score is not null,题目中有提及isexam的.审题！

select name,avg(score) from student where  isexam='是' group by name ;

select subject,avg(score) from student group by subject ;

– 练习六综合练习

create table Student1(
subjectName varchar(20),
stuname varchar(20),
address varchar(20), 
sex char(2),
email varchar(30),
grade varchar(10),
birthday date,
examDate date,
scores int
);

insert into Student1 values
('JAVA','张三','北京','男','123@qq.com','S1','1990-03-04','2013-5-6',89),
('html','李四','上海','男',null,'S2','1993-08-04','2014-5-6',87),
('html','王五','北京','男','123@qq.com','S2','1990-03-04','2015-5-6',90)
;

select distinct  subjectName from student1 where grade ='S2';

select stuname,address from student1 where grade='S2' and sex='男';

-- 3.查询无电子邮件的学生姓名和年级信息   **空值需注意有两种情况  is null 或者是  =''**
select stuname,grade  from student1 where email is null or email ='';


--  4.查询出生日期在1993年之后的S2的学生姓名和年级信息

select stuname,grade  from student1 where substring(birthday,0,4) >1993 and 
grade='S2'; 

select stuname,grade  from student1 where birthday>'1993-12-31' and 
grade='S2'; 


select  scores  from  student1  where examDate ='2014-5-6' and subjectName ='html';

Mysql总结：

学习指南：

1.直接对照题目要比较直观
2.将高难度题目分步骤解答成小题，最后完成挑战

学习注意点

                1.上机检验拼写是否有错
                2.审题很重要，需求是否理解正确。不要写反年龄区域，比如：age>=18 and age<=30 
                3. 英文环境都是用英文别名，不用中文拼音 
                4.写需求的时候，遗忘语句的使用方法，回顾加强
                5. subject='C'  注意这里面的C是大写的，VARCHAR(20)里是严格区分大小写的。

Allen-1

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库基础 MYSQL实现

DDL：使用数据库use allen;查看数据库的表show tables;select * from users;创建表user1create table user1(id int primary key,name varchar(12) not null,age int ,sex varchar(5),birthday date,password varchar(12));select * from user1 ;DDL：更改表的字段名alter tabl
复制链接

扫一扫