数据库分类
- DDL 数据库定义语言 建库,建表,视图,索引
- DML 数据库操作语言 对表中数据 增删改
- DQL 数据库查询语言 对表中的记录查询
- DCL 数据库控制语言 对用户权限的设置,控制事务
一、DDL操作数据库和表
1.1 创建数据库
-- 创建数据库 CREATE
-- CREATE DATABASE 数据库名;
-- 直接创建数据库db1
create database db1;
-- 判断数据库是否已经存在,不存在则创建数据库
--CREATE DATABASE IF NOT EXISTS 数据库名;
-- 判断是否存在,如果不存在则创建数据库db2
create database if not exists db2;
-- 创建数据库并指定字符集
--CREATE DATABASE 数据库名 CHARACTER SET 字符集;
-- 创建数据库并指定字符集为gbk
create database db3 default character set gbk;
1.2 查看数据库
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db3;
show create database db1;
1.3修改数据库
-- 修改数据库默认的字符集
--ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
--将 db3 数据库的字符集改成 utf8
alter database db3 default character set utf8;
1.4删除数据库
--DROP DATABASE 数据库名;
-- 删除 db2 数据库
drop database db2;
1.5使用数据库
--查看正在使用的数据库
--SELECT DATABASE(); 使用的一个 mysql 中的全局函数
--使用/切换数据库 USE 数据库名;
-- 查看正在使用的数据库
select database();
-- 改变要使用的数据库
use db4;
1.6创建表
/*CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);*/
--创建 student 表包含 id,name,birthday 字段
create table student (
id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 生日,最后没有逗号
);
1.7查看表
--查看某个数据库中的所有表
--SHOW TABLES;
--查看表结构 DESC 表名;
--查看创建表的SQL语句 SHOW CREATE TABLE 表名;
--查看 day21 数据库中的所有表
use day21;
show tables;
--查看 student 表的结构
desc student;
--查看 student 的创建表 SQL 语句
show create table student;
1.8快速创建一个表结构相同的表
--快速创建一个表结构相同的表
-- CREATE TABLE 新表名 LIKE 旧表名
--创建 s1 表,s1 表结构和 student 表结构相同
-- 创建一个s1的表与student结构相同
create table s1 like student;
desc s1;
1.9删除表
--直接删除表
--DROP TABLE 表名;
--判断表是否存在,如果存在则删除表
--DROP TABLE IF EXISTS 表名;
-- 直接删除表s1表
drop table s1;
-- 判断表是否存在并删除s1表
drop table if exists `create`;
1.10修改表结构
--添加表列ADD
--ALTER TABLE 表名 ADD 列名 类型;
--为学生表添加一个新的字段 remark,类型为 varchar(20)
alter table student add remark varchar(20);
--修改列类型MODIFY
--ALTER TABLE 表名 MODIFY 列名 新的类型;
--将 student 表中的 remark 字段的改成 varchar(100)
alter table student modify remark varchar(100);
--修改列名 CHANGE
--ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
--将 student 表中的 remark 字段名改成 intro,类型 varchar(30)
alter table student change remark intro varchar(30);
--删除列 DROP
--ALTER TABLE 表名 DROP 列名;
--将 student 表中的 remark 字段名改成 intro,类型 varchar(30)
alter table student change remark intro varchar(30);
--修改表名
--RENAME TABLE 表名 TO 新表名;
--将学生表 student 改名成 student2
rename table student to student2;
--修改字符集character set
--ALTER TABLE 表名 character set 字符集;
--将 student2 表的编码修改成 gbk
alter table student2 character set gbk;
二、DML操作表中的数据*****
2.1插入记录
--所有的字段名都写出来
--INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);
--不写字段名
--INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);
--插入部分数据 INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
insert into student values (2, '孙悟天', 16, '男');
乱码问题:
现象:有时候我们使用DOS命名窗口查询数据库有中文的部分是乱码,而使用的工具内部是正常的中文。
原因:DOS窗口和工具的编码设置不统一
解决:
1.查看包含 character 开头的全局变量
show variables like ‘character%’;
2.修改 client、connection、results 的编码为 GBK,保证和 DOS 命令行编码保持一致
set names gbk;
2.2蠕虫复值(将一张表中的数据复制到另一张表)
--将表名 2 中的所有的列复制到表名 1 中
--INSERT INTO 表名 1 SELECT * FROM 表名 2;
--只复制部分列
--INSERT INTO 表名 1(列 1, 列 2) SELECT 列 1, 列 2 FROM student;
-- 创建student2表,student2结构和student表结构一样
drop table student2;
create table student2 like student;
-- 将student表中的数据添加到student2表中
insert into student2 select * from student;
-- 如果只想复制student表中name,age字段数据到student2表中,两张表都写出相应的列名
insert into student2 (name,age) select name,age from student;
2.3更新表记录
--不带条件修改数据
--UPDATE 表名 SET 字段名=值; -- 修改所有的行
--带条件修改数据
--UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
-- 不带条件修改数据,将所有的性别改成女
update student set sex = '女';
-- 带条件修改数据,将id号为2的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列,把id为3的学生,年龄改成26岁,address改成北京
update student set age=26, address='北京' where id=3;
2.4删除表记录
--不带条件删除数据
--DELETE FROM 表名;
--带条件删除数据
--DELETE FROM 表名 WHERE 字段名=值;
--使用truncate删除表中所有记录
--TRUNCATE TABLE 表名;
--truncate和delete的区别: truncate 相当于删除表的结构,再创建一张表。
-- 带条件删除数据,删除id为1的记录
delete from student where id=1;
-- 不带条件删除数据,删除表中的所有数据
delete from student;
三、DQL查询表中的数据
3.1 查询数据
--使用*表示所有列
--SELECT * FROM 表名;
--查询所有的学生:
select * from student;
--查询指定列的数据,多个列之间以逗号分隔
--SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
--查询 student 表中的 name 和 age 列
select name,age from student;
--对列指定别名
--SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
--对列和表同时指定别名
--SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
-- 使用别名
select name as 姓名,age as 年龄 from student;
-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st
3.2清除重复值
--查询指定列并且结果不出现重复数据
--SELECT DISTINCT 字段名 FROM 表名;
-- 查询学生来至于哪些地方
select address from student;
-- 去掉重复的记录
select distinct address from student;
3.3查询结果参与运算
--某列数据和固定值运算
--SELECT 列名 1 + 固定值 FROM 表名;
--某列数据和其他列数据参与运算
--SELECT 列名 1 + 列名 2 FROM 表名;
-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student; -- as 可以省略
select *,(math+english) 总成绩 from student;
3.4条件查询
运算符
逻辑运算符
-- 查询math分数大于80分的学生
select * from student3 where math>80;
-- 查询english分数小于或等于80分的学生
select * from student3 where english <=80;
-- 查询age等于20岁的学生
select * from student3 where age = 20;
-- 查询age不等于20岁的学生,注:不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;
in关键字
-- 查询id是1或3或5的学生
select * from student3 where id in(1,3,5);
-- 查询id不是1或3或5的学生
select * from student3 where id not in(1,3,5);
范围查询
--查询 english 成绩大于等于 75,且小于等于 90 的学生
select * from student3 where english between 75 and 90;
select * from student3 where english age>=75 && age<=90;
like 关键字
-- 查询姓马的学生
select * from student3 where name like '马%';
select * from student3 where name like '马';
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马,且姓名有两个字的学生
select * from student3 where name like '马_';
3.5 排序
-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;
3.6聚合函数
-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;
-- 查询id字段,如果为null,则使用0代替
select ifnull(id,0) from student;
--我们可以利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏
select count(ifnull(id,0)) from student;
-- 查询年龄大于20的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;
3.7分组
-- 按性别进行分组,求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
--查询男女各多少人
--1) 查询所有数据,按性别分组。
--2) 统计每组人数
select sex, count(*) from student3 group by sex;
--查询年龄大于 25 岁的人,按性别分组,统计每组的人数
--1) 先过滤掉年龄小于 25 岁的人。
--2) 再分组。
--3) 最后统计每组的人数
select sex, count(*) from student3 where age > 25 group by sex ;
--查询年龄大于 25 岁的人,按性别分组,统计每组的人数,并只显示性别人数大于 2 的数据
-- 对分组查询的结果再进行过滤 只有分组后人数大于 2 的`男`这组数据显示出来
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2
having与where的区别
3.8limit语句
-- 查询学生表中数据,从第3条开始显示,显示6条。
select * from student3 limit 2,6;
-- 如果第一个参数是0可以省略写:
select * from student3 limit 5;
-- 最后如果不够5条,有多少显示多少
select * from student3 limit 10,5;
数据库表的约束
--
--创建好以后修改起始值 ALTER TABLE 表名 AUTO_INCREMENT=起始值;
alter table st4 auto_increment = 2000;
insert into st4 values (null, '刘备');
--什么是默认值: 字段名 字段类型 DEFAULT 默认值
-- 创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州
create table st9 (
id int,
name varchar(20),
address varchar(20) default '广州'
)
-- 添加一条记录,使用默认地址
insert into st9 values (1, '李四', default);
select * from st9;
insert into st9 (id,name) values (2, '李白');
-- 添加一条记录,不使用默认地址
insert into st9 values (3, '李四光', '深圳');
外键约束
1.添加外键
--已有表增加外键: ALTER
--ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
--创建表是的外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
2.删除外键
--ALTER TABLE 从表 drop foreign key 外键名称;
-- 删除employee表的emp_depid_fk外键
alter table employee drop foreign key emp_depid_fk;
-- 在employee表情存在的情况下添加外键 a
lter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);
3.外键级联
-- 删除employee表,重新创建employee表,添加级联更新和级联删除
drop table employee;
create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade )
-- 再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); I
-- 删除部门表?能不能直接删除?
drop table department;
-- 把部门表中id等于1的部门改成id等于10
update department set id=10 where id=1;
select * from employee; select * from department;
-- 删除部门号是2的部门
delete from department where id=2;
三、1.多表查询*****
准备数据
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键)
)
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男 ',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男 ',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'200808-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女 ',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女 ',4500,'2011-03-14',1);
出现笛卡尔积
-- 需求:查询所有的员工和所有的部门
select * from emp,dept
-- 设置过滤条件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;
内连接
隐式内连接
显示内连接
确定表连接条件,员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.`dept_id` = d.`id`;
确定查询条件,我们查询的是唐僧的信息,员工表.name='唐僧'
select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧 ';
确定查询字段,查询唐僧的信息,显示员工 id,姓名,性别,工资和所在的部门名称
select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧'
我们发现写表名有点长,可以给表取别名,显示的字段名也使用别名
select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧'
左外连接
左外连接:使用 LEFT OUTER JOIN … ON,OUTER 可以省略
==SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件 ==
-- 在部门表中增加一个销售部
insert into dept (name) values ('销售部'); select * from dept;
-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;
右外连接
右外连接:使用 RIGHT OUTER JOIN … ON,OUTER 可以省略
==SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件 ==
用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL 可以理解为:在内连接的基础上保证右表的数据全部显示
-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;
子查询
-- 需求:查询开发部中有哪些员工
select * from emp;
-- 通过两条语句查询
select id from dept where name='开发部' ;
select * from emp where dept_id = 1;
-- 使用子查询
select * from emp where dept_id = (select id from dept where name='市场部')
子查询的结果是一个值的时候
子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、 = 等
==SELECT 查询字段 FROM 表 WHERE 字段=(子查询); ==
--案例:查询工资最高的员工是谁?
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
查询工资小于平均工资的员工有哪些?
-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);
子查询结果是多行单列的时候
子查询结果是单例多行,结果集类似于一个数组,父查询使用 IN 运算符
==SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询); ==
--查询工资大于5000的员工,来自于哪些部门的名字
-- 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门id中部门的名字 Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);
--查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门id中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务 部'));
子查询的结果是多行多列
子查询结果只要是多列,肯定在 FROM 后面作为表
==SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件; ==
子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段
--查询出2011年以后入职的员工信息,包括部门名称
-- 查询出2011年以后入职的员工信息,包括部门名称
-- 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
--也可以使用表连接:
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';
子查询总结
- 子查询结果只要是单列,则在 WHERE 后面作为条件
- 子查询结果只要是多列,则在 FROM 后面作为表进行二次查询
四、DCL修改用户权限
我们现在默认使用的都是 root 用户,超级管理员,拥有全部的权限。但是,一个公司里面的数据库服务器上面 可能同时运行着很多个项目的数据库。所以,我们应该可以根据不同的项目建立不同的用户,分配不同的权限来管 理和维护数据库。
4.1创建用户
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
创建 user1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123
create user ‘user1’@‘localhost’ identified by ‘123’;
给用户授权
用户创建之后,没什么权限!需要给用户授权
GRANT 权限 1, 权限 2… ON 数据库名.表名 TO ‘用户名’@‘主机名’;
给 user1 用户分配对 test 这个数据库操作的权限:创建表,修改表,插入记录,更新记录,查询
grant create,alter,insert,update,select on test.* to ‘user1’@‘localhost’;
给 user2 用户分配所有权限,对所有数据库的所有表
grant all on . to ‘user2’@’%’;
4.3撤销授权
REVOKE 权限 1, 权限 2… ON 数据库.表名 revoke all on test. from ‘user1’@‘localhost’; ‘用户名’@‘主机 名’;*
撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from ‘user1’@‘localhost’;
4.4查看权限
SHOW GRANTS FOR ‘用户名’@‘主机名’;
4.5删除用户
DROP USER ‘用户名’@‘主机名’;
删除 user2
drop user ‘user2’@’%’;
4.6修改管理员密码
mysqladmin -uroot -p password 新密码
4.7修改普通用户密码
set password for ‘用户名’@‘主机名’ = password(‘新密码’);