数据库慨念
数据是事实或观察的结果,它是对客观事物的逻辑归纳,是信息的表现形式和载体,可以是符号、文字、数字、语音、图像、视频等。
1、创建数据库
create database mydb;
create database `test` Character Set utf8 Collate utf8_general_ci;
2、查看数据库
show databases;
3、使用mydb数据库
use mydb;
4、创建表
create table myinfo(
id int(10) not null auto_increment primary key,
name varchar(20),
sex int(2),
phone int(11)
);
5、查看数据库里面的表
show tables;
6、删除数据库
drop database mydb;
7、添加字段sex,类型为VARCHAR(1)
ALTER TABLE myinfo ADD sex VARCHAR(1);
8、修改字段sex的类型为tinyint
ALTER TABLE myinfo MODIFY sex tinyint;
9、删除字段sex
ALTER TABLE myinfo DROP COLUMN sex;
10、删除myinfo表
DROP TABLE myinfo;
11、添加单个数据
insert into myinfo(name,sex,phone) values("zhangsan",1,13765287861);
12、添加多个数据
insert into myinfo(name,sex,phone) values("zhangqin",1,13765287863)
,("zhangrui",1,13765287862);
13、修改数据
update myinfo SET name="lisi", sex=2 where id=1;(如果不带where就修改全部数据)
14、删除数据
delete from myinfo where id = 1;(如果不带where则删除数据表中的所有记录)
15、约束
唯一性约束:unique
#在创建表时添加唯一性约束
create table person(
id int not null auto_increment primary key comment '主键id',
name varchar(30) comment '姓名',
id_number varchar(18) unique comment '身份证号'
);
外键约束:foreign key(stu_no) references stu(stu_no)
#学生表(主表)
create table stu(
stu_no int not null primary key comment '学号',
stu_name varchar(30) comment '姓名'
);
#成绩表(从表)
create table sc(
id int not null auto_increment primary key comment '主键id',
stu_no int not null comment '学号',
course varchar(30) comment '课程',
grade int comment '成绩',
foreign key(stu_no) references stu(stu_no)
);
# 注意:在插入数据时,必须先向主表插入,再向从表插入。删除数据时正好相反。
16、查询表
#全部信息
select * from myinfo;
#根据需要的数据查询
select name,phone from myinfo;
#带条件的查询(and表示前后都要有,or选择其中一个)
select name,phone from myinfo where id=4 and name = "zhangrui";
select name,phone from myinfo where id=1 or name = "zhangrui";
17、模糊查询
#like(匹配姓名带张的)
select * from myinfo where name like '%zhang%';
#in(相当于多个or)
select * from myinfo where id in(2,3,4);
18、数据库常用函数
#count()查询数据库的总条数
select count(*) from myinfo;
#sum()统计
select sum(工资) from myinfo;
#avg()平均数
select avg(工资) from myinfo;
#所属用户
select user();
#min()/max() 最小值和最大值
select min(工资)/max(工资) from myinfo;
#now()当前时间
select now();
#date_format 序列化时间格式
select name, date_format(birthday, '%Y/%m/%d') from myinfo;
#case where用法
select id,name,case sex
when 1 then '男'
else '女'
end as sex,phone
from myinfo;
19、数据库的排序
#order by
#默认升序排:
select * from employee order by salary;
#设置升序排
select * from employee order by salary asc;
#设置降序排:
select * from employee order by salary desc;
#多条件的排序
select * from employee order by sex,salary asc;
select * from employee order by sex,salary desc;
#limit
#显示5条数据
select * from employee limit 5;
#显示分页数据(从哪里开始-到哪里结束)
select * from employee limit 0,5;
20、group by和having的使用
#测试数据:
create table employee(
id int not null auto_increment primary key,
name varchar(30) comment '姓名',
sex varchar(1) comment '性别',
salary int comment '薪资(元)',
dept varchar(30) comment '部门'
);
insert into employee(name, sex, salary, dept) values('张三', '男', 5500, '部门A');
insert into employee(name, sex, salary, dept) values('李洁', '女', 4500, '部门C');
insert into employee(name, sex, salary, dept) values('李小梅', '女', 4200, '部门A');
insert into employee(name, sex, salary, dept) values('欧阳辉', '男', 7500, '部门C');
insert into employee(name, sex, salary, dept) values('李芳', '女', 8500, '部门A');
insert into employee(name, sex, salary, dept) values('张江', '男', 6800, '部门A');
insert into employee(name, sex, salary, dept) values('李四', '男', 12000, '部门B');
insert into employee(name, sex, salary, dept) values('王五', '男', 3500, '部门B');
insert into employee(name, sex, salary, dept) values('马小龙', '男', 6000, '部门A');
insert into employee(name, sex, salary, dept) values('龙五', '男', 8000, '部门B');
insert into employee(name, sex, salary, dept) values('冯小芳', '女', 10000, '部门C');
insert into employee(name, sex, salary, dept) values('马小花', '女', 4000, '部门B');
insert into employee(name, sex, salary, dept) values('张勇', '男', 8800, '部门A');
#group by
#查看公司男员工和女员工的数量
select sex,count(*) from employee group by sex;
#查看公司每个部门人数
select dept,count(*) from employee group by dept;
#查看公司每个部门人数的总工资
select dept,sum(salary) from employee group by dept;
#查看公司每个部门的最高工资
select dept,max(salary) from employee group by dept;
#查看公司每个部门的最低工资
select dept,min(salary) from employee group by dept;
#having
#查看公司人数小于5的部门
select dept,count(*)
from employee
group by dept
having count(*)<5;
#查看公司工资过万的部门
select dept,max(salary)
from employee
group by dept
having max(salary)>=10000;
21、group_concat的使用(在后面显示数据)
#查看公司每个部门人数并且查看姓名
select dept,count(*),group_concat(name) from employee group by dept;
#查看公司每个部门人数并且查看姓名(用分号隔开)
select dept,count(*),group_concat(name separator ';') from employee group by dept;
22、distinct的使用(去重)
#去重员工性别
select distinct sex from employee;
23、内连接:
#准备两张表
create table student(
stu_no varchar(20) not null primary key comment '学号',
name varchar(30) comment '姓名',
address varchar(150) comment '地址'
);
insert into student(stu_no, name, address) values('2016001', '张三', '贵州贵阳');
insert into student(stu_no, name, address) values('2016002', '李芳', '陕西兴平');
insert into student(stu_no, name, address) values('2016003', '张晓燕', '江西南昌');
create table score(
id int not null auto_increment primary key,
course varchar(50) comment '科目',
stu_no varchar(20) comment '学号',
score int comment '分数',
foreign key(stu_no) references student(stu_no)
);
insert into score(course, stu_no, score) values('计算机', '2016001', 99);
insert into score(course, stu_no, score) values('离散数学', '2016001', 85);
insert into score(course, stu_no, score) values('计算机', '2016002', 78);
#join用法(表连接(JOIN)是在多个表之间通过一定的连接条件,使表之间发生关联,进而能从多个表之间获
取数据)
语法:select A.c1, B.c2
from A
join B on (A.c3 = B.c3)
#内连接查询表里面学生的成绩(学号,姓名,课程,成绩)
select A.stu_no,A.name,B.course,B.score
from student A
join score B on(A.stu_no=B.stu_no);
#常用where表示(等价于join)
select A.stu_no,A.name,B.course,B.score
from student A,score B
where(A.stu_no=B.stu_no);
#左连接查询表里面学生的成绩(学号,姓名,课程,成绩)
select A.stu_no,A.name,B.course,B.score
from student A
left join score B on(A.stu_no=B.stu_no);
24、自连接:(有层次结构的表)
#准备测试数据:
create table area(
id int not null auto_increment primary key comment '区域id',
pid int not null comment '父id(0-省份)',
name varchar(30) comment '区域名称'
);
insert into area(id, pid, name) values(1, 0, '贵州省');
insert into area(id, pid, name) values(2, 1, '贵阳');
insert into area(id, pid, name) values(3, 1, '遵义');
insert into area(id, pid, name) values(4, 0, '广东省');
insert into area(id, pid, name) values(5, 4, '广州');
insert into area(id, pid, name) values(6, 4, '深圳');
#查询市属于哪个省份
select A.id,A.name,B.name as proName
from area A,area B
where A.pid=B.id and A.pid<>0;
25、子查询EXISTS和IN的使用
#in用法
#查询所有选修课程的学生
select A.*
from student A
where A.stu_no in(select B.stu_no from score B);
#查询所有选修课程的学生并且选择了计算机的学生
select A.*
from student A
where A.stu_no in(select B.stu_no from score B where B.course="计算机");
#exists用法
#查询所有选修课程的学生
select A.*
from student A
where exists(select * from score B where A.stu_no=B.stu_no);
#查询所有未选修课程的学生
select A.*
from student A
where not exists(select * from score B where A.stu_no=B.stu_no);
26、用户管理
#创建用户
create user 'zhangyong'@'localhost' identified by '521521';
#删除用户
drop user 'zhangyong'@'localhost';
#修改密码
alter user 'zhangyong'@'localhost' identified by '新密码';
#授权
grant all privileges on databaseName.tableName to 'zhangyong'@'localhost';
#撤销授权
revoke all privileges on databaseName.tableName from 'zhangyong'@'localhost';
#刷新权限
flush privileges;
#查看权限
show grants for 'zhangyong'@'localhost';
27、禁止root远程登录
#把root设置为只能本机登录(默认只能本机登录)
UPDATE user SET Host="localhost" where user="root";
#查看表里所有用户的权限
SELECT user,Host from user;
28、忘记root密码处理
#关闭MySQL服务-->重启MySQL时关闭权限验证-->修改root密码-->启动mysql
#关闭MySQL服务
net stop MySQL57
#关闭权限验证
mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 5.7\my.ini" --console --skip-granttables --shared-memory
#连接数据库
mysql
#刷新权限
FLUSH PRIVILEGES;
#修改root用户的密码
alter user 'root'@'localhost' IDENTIFIED BY '521521';