1.库操作======================
1.创建
CREATE DATABASE DB2 charset utf8;
2.删除
DROP DATABASE db2;
3.使用(进入)
use db1;
4.查看库
show databases ; -- 查看当前用户下所有的库名
select DATABASE(); -- 查看当前使用的
show CREATE DATABASE db1; -- 查看创建库的信息
2.表操作 SQL语句===============
1.创建表
create table info1(
id int not null auto_increment PRIMARY key,
name VARCHAR(50) not null,
sex CHAR(2) NULL
)
2.查看表
select * from info1; -- 查看表数据
DESC info1; -- 查看表结构
show create table info1; -- 查看表的创建信息
3.删除表
drop table info;
4.修改表
-- ALTER table info1 add age int not null ; -- 添加表字段
-- alter table info1 drop age; -- 删除表字段
-- alter table info1 modify name char(100) null ; -- 修改表字段属性
-- alter table info1 CHANGE names name varchar(50) not null; -- 修改表名称
补充信息
alter table info1 add PRIMARY key(ID,names); -- 添加主键
alter table info1 drop PRIMARY key ;-- 删除主键
alter table info1 alter sex set default '男'; -- 设置默认值
alter table info1 alter sex drop default; -- 删除默认值
5.修改表名称
rename table info1 to info; -- 修改表名
6.复制表
1.CREATE table info3 select * from info where id =1;
ps:主键自增/索引/触发器/外键 不会 被复制
2.复制表结构
CREATE table info5 SELECT * FROM info where 1!3=1
CREATE table info4 LIKE info;
ps: 数据/触发器/外键 不会被复制
3.数据操作=========================
1.增
-- INSERT into info values(1,'韩涉','女');
-- INSERT into info(id,NAMES) values(2,'韩涉');
-- INSERT into info(id,NAMES) values(3,'韩涉'),(4,'韩涉'),(5,'韩涉');
-- INSERT into info(names,sex) select nameS,sex from info ;
2.删
-- DELETE from info where id = '11'; -- 删除指定数据
-- delete from info; -- 删除整张表中所有的数据
TRUNCATE info; -- 清空整张表
3.改
update info set sex = '人妖', names = '韩小强' where id=12;
4.查
4.1 简单查询
select * from person; -- 查询所有
select name,SEX from person; -- 按指定字段查询
select name,SEX as'性别' from person; -- as 表示为字段起别名
select salary+200 from person; -- 可以进行数据列运算
select DISTINCT age,name FROM person; -- 去重复查询
4.2 条件查询=======================
1.运算符
select * FROM person WHERE age >20;
select * FROM person WHERE age <=20;
select * FROM person WHERE age <>20;
select * FROM person WHERE age !=20;
2.null 关键字
select * FROM person where dept_id is null;
select * FROM person where dept_id is not null;
select * FROM person where name ='';
3.逻辑运算符 and or
select * from person where age = 28 and salary =53000;
select * from person where age = 23 or salary =2000;
select * from person where not(age = 28 and salary =53000);
` 4.3 区间查询
select * from person where age BETWEEN 18 and 20;
ps: between...and 前后包含所指定的值
等价于 select * from person where salary >= 4000 and salary <= 8000;
4.4集合查询
select * from person where id = 1 or id = 3 or id = 5;
select * from person where id not in(1,3,5);
4.5 模糊查询
select * from person where name like '%e%'; -- 包含指定参数
select * from person where name like '%e'; -- 以什么结尾
select * from person where name like 'e%'; -- 以什么开头
select * from person where name like '__e%'; -- _表示单个字符站位符
select * from person where name like '__';
4.6 排序查询
select * from person where age >30 ORDER BY salary desc; -- ASC正序 DESC倒序
select * from person ORDER BY CONVERT(name USING gbk);-- 中文排序
=====================================================================================
1.用户权限(了解)===============
1.创建用户------------------------------------------
create user 'hanshe'@'127.0.0.1' IDENTIFIED by '123'; -- 创建用户
2.移除用户------------------------------------------
drop user 'hanshe'@'127.0.0.1' ; -- 移除用户
3.修改用户--------------------------------------------
RENAME user 'hanshe'@'127.0.0.1' to 'hanxiaoqiang'@'192.168.0.1' -- 修改用户
4.查看授权--------------------------------------------
show GRANTS for 'hanshe'@'127.0.0.1';-- 查看用户 权限
5.授权--------------------------------------------------
GRANT select,update ON db1.info to 'hanshe'@'127.0.0.1';-- 授权
GRANT all PRIVILEGES on *.* to 'hanshe'@'127.0.0.1'; -- 授权所有权限
6.移除授权------------------------------------------------
REVOKE all PRIVILEGES on *.* FROM 'hanshe'@'127.0.0.1'; -- 移除权限
7.开放外部访问权限-------------------------------------
create user 'test'@'%' identified by '123';
GRANT all PRIVILEGES on *.* to 'test'@'%';
FLUSH PRIVILEGES; -- 刷新权限
2.修改密码--------------------------------------------
1.方式一:使用 mysqladmin 命令-------
mysqladmin -u用户名 -p原密码 password 新密码;
2.方式二:直接设置密码----------
set password for 'hanshe'@'%' = password('166')
3.方式三: 直接修改------
update mysql.user set password = password('123') where user ='hanshe' and host ='%'
flush PRIVILEGES;
5.7 版本-----
update mysql.user set authentication_string = password('123') where user ='hanshe' and host ='%';
flush PRIVILEGES;
3.忘记密码-----
1.关闭mysql服务-
2.重新启动mysql服务并跳过权限表-
3.直接通过mysql登录-
4.修改密码
5.刷新
4.单表查询------------------------------
1.聚合函数----------------------------
select sum(name),avg(age),max(age),min(age),count(name) FROM person;
2.分组------------------------------------
select sum(salary),dept_id from person GROUP BY dept_id
select sum(salary) as w ,dept_id from person GROUP BY dept_id HAVING w >20000
-- 查询每个部门的平均薪资 并且看看这个部门的员工都有谁?----------
select avg(salary),dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id
#查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?---------------
select avg(salary),dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id HAVING
avg(salary) >10000
3.分页---------
select * from person LIMIT 8,4
ps: limit (起始条数),(查询多少条数);
4.SQL 语句关键字的执行顺序 ----------------
执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->limit
练习题:-------------
http://www.cnblogs.com/wangfengming/articles/7944029.html
http://www.cnblogs.com/wangfengming/articles/7889786.html
5. 多表联合查询-----------
select * from person p,dept d where p.dept_id = d.did -- 笛卡尔乘积
-- 多表联合查询--------
-- select * from person p,dept d where -- 笛卡尔乘积
--
--
-- -- 左连接查询-----------
-- select * from person LEFT JOIN dept on person.dept_id = dept.did;
--
-- -- 右连接查询-----------
-- select * from person RIGHT JOIN dept on person.dept_id = dept.did;
--
-- -- 内连接查询--------------
-- select * from person INNER JOIN dept on person.dept_id = dept.did;
-- 全连接--------------------
select * from person LEFT JOIN dept on person.dept_id = dept.did
UNION
select * from person RIGHT JOIN dept on person.dept_id = dept.did;
select * from person LEFT JOIN dept on person.dept_id = dept.did
UNION all
select * from person RIGHT JOIN dept on person.dept_id = dept.did;
6. 复杂条件查询-------------------
-- 1. 查询出 教学部 年龄大于20岁,并且工资小于4000的员工,按工资倒序排列.
-- (要求:分别使用多表联合查询和内连接查询)
select did from dept where dname ='教学部';
select * from person where age>20 and
dept_id =(select did from dept where dname ='教学部') and salary <10000 ORDER by salary DESC
-- 2.查询每个部门中最高工资和最低工资是多少,显示部门名称------------------------
select MAX(salary),min(salary),dname from person
LEFT JOIN dept ON person.dept_id = dept.did GROUP BY dept_id
7.子语句查询-------------------
1.使用结果集作为表名查询
select * from (SELECT * from person) as aaa
-- 2.求最大工资那个人的姓名和薪水--------------
select max(salary) from person;
select* from person where salary = (select max(salary) from person);
-- 3. 求工资高于所有人员平均工资的人员---------------------
select avg(salary) from person;
select * from person where salary >(select avg(salary) from person)
=====================================================================================
1. 其他查询重点题
-- 查询高于本部门平均工资的人员----------------------
select dept_id,avg(salary) from person GROUP BY dept_id;
-- select * FROM person GROUP BY dept_id HAVING salary >
-- (select avg(salary) from person GROUP BY dept_id)
select * from person p1,
(select dept_id,avg(salary) as '平均工资' from person GROUP BY dept_id) as p2
where p1.dept_id = p2.dept_id AND p2.`平均工资` < p1.salary;
2.-- 根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。---------------------
-- 显示效果:姓名,年龄,性别,工资,级别
select p.name,p.age,p.sex,p.salary ,
IF(salary>10000,'高端人群','低端人群') as '级别'
from person p;
3. -- 根据工资高低,统计每个部门人员收入情况,划分为 富人,小资,平民,吊丝 四个级别, -----------------
-- 要求统计四个级别分别有多少人
select dname ,
sum(case when person.salary > 10000 THEN 1 else 0 END) as '富人',
sum(case when person.salary BETWEEN 5000 and 10000 THEN 1 else 0 END) as '小资',
sum(case when person.salary BETWEEN 3000 and 5000 THEN 1 else 0 END) as '平民',
sum(case when person.salary < 3000 THEN 1 else 0 END) as '屌丝'
from dept,person where dept.did = person.dept_id GROUP BY dept.did
2.sql语句逻辑执行顺序------------------------
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
3.外键约束 --------------------------
添加外键:------
alter table person add CONSTRAINT fk_id FOREIGN key(dept_id) REFERENCES dept(did);
删除外键------
alter table person DROP FOREIGN key fk_id
注：插入数据时，先插入主表中的数据，再插入从表中的数据。
删除数据时，先删除从表中的数据，再删除主表中的数据。
4唯一约束--------------------------------------
create table t4(
id int(10) not null,
name varchar(255) ,
unique id_name(id,name)
);
ALTER table t4 add UNIQUE id_name(id,name)
alter table t4 DROP index id_name
5.默认值约束------------------------------------
create table t5(
id int(10) not null primary key,
name varchar(255) default '张三'
);
INSERT into t5 VALUES(3,DEFAULT),(4,DEFAULT);
6.表与表之间关系---------------------------------
1.一对多 ,一对一 ,多对多-
7.数据库设计三范式-------------------------------------
1．第一范式(确保每列保持原子性)
2．第二范式(确保表中的每列都和主键相关)
练习题:========================================
#课程表 CREATE TABLE `course` ( `c_id` int(11) NOT NULL, `c_name` varchar(50) DEFAULT NULL, `t_id` int(11) DEFAULT NULL, PRIMARY KEY (`c_id`), KEY `t_id` (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `course` VALUES ('1', 'python', '1'); INSERT INTO `course` VALUES ('2', 'java', '2'); INSERT INTO `course` VALUES ('3', 'linux', '3'); INSERT INTO `course` VALUES ('4', 'web', '2'); #成绩表 CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s_id` int(11) DEFAULT NULL, `c_id` int(11) DEFAULT NULL, `num` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; INSERT INTO `score` VALUES ('1', '1', '1', '79'); INSERT INTO `score` VALUES ('2', '1', '2', '78'); INSERT INTO `score` VALUES ('3', '1', '3', '35'); INSERT INTO `score` VALUES ('4', '2', '2', '32'); INSERT INTO `score` VALUES ('5', '3', '1', '66'); INSERT INTO `score` VALUES ('6', '4', '2', '77'); INSERT INTO `score` VALUES ('7', '4', '1', '68'); INSERT INTO `score` VALUES ('8', '5', '1', '66'); INSERT INTO `score` VALUES ('9', '2', '1', '69'); INSERT INTO `score` VALUES ('10', '4', '4', '75'); INSERT INTO `score` VALUES ('11', '5', '4', '66.7'); #学生表 CREATE TABLE `student` ( `s_id` varchar(20) NOT NULL, `s_name` varchar(50) DEFAULT NULL, `s_age` int(10) DEFAULT NULL, `s_sex` char(1) DEFAULT NULL, PRIMARY KEY (`s_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `student` VALUES ('1', '鲁班', '12', '男'); INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女'); INSERT INTO `student` VALUES ('3', '刘备', '35', '男'); INSERT INTO `student` VALUES ('4', '关羽', '34', '男'); INSERT INTO `student` VALUES ('5', '张飞', '33', '女'); #老师表 CREATE TABLE `teacher` ( `t_id` int(10) NOT NULL, `t_name` varchar(50) DEFAULT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `teacher` VALUES ('1', '大王'); INSERT INTO `teacher` VALUES ('2', 'alex'); INSERT INTO `teacher` VALUES ('3', 'egon'); INSERT INTO `teacher` VALUES ('4', 'peiqi'); 数据脚本
-- 1. 查询学习课程"python"比课程 "java" 成绩高的学生的学号;--------------------------------------------
-- select num from course,score where score.c_id = course.c_id and course.c_name = 'python';
-- select num from course,score where score.c_id = course.c_id and course.c_name = 'java';
--
select python.s_id,student.s_name from
(select score.num,score.s_id from course,score where score.c_id = course.c_id and course.c_name = 'python'
) as python,
(select score.num,score.s_id from course,score where score.c_id = course.c_id and course.c_name = 'java') as java,
student
where python.s_id = java.s_id and python.num > java.num and python.s_id = student.s_id
-- 2. 查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数); ---------------------------
-- select student.s_name,ROUND(avg(score.num),2) from student,score
-- where student.s_id = score.s_id GROUP BY score.s_id HAVING avg(num) >65;
--
--
--
-- 3. 查询所有同学的姓名、选课数、总成绩；-------------------------------------
select student.s_name,COUNT(score.s_id),sum(num) from student,score where student.s_id = score.s_id GROUP BY score.s_id
-- 4. 查询所有的课程的名称以及对应的任课老师姓名;-------------------------
-- select course.c_name,teacher.t_name from course,teacher where course.t_id = teacher.t_id
-- 5. 查询没学过“alex”老师课的同学的姓名;--------------------------------
-- select * from score where score.c_id != 2 or score.c_id !=4; -- in 集合实现方式--------------------------
-- select course.c_id FROM teacher,course where teacher.t_id = course.t_id and teacher.t_name = 'alex';
--
select * from student where student.s_id not in (select score.s_id from score where score.c_id in (2,4))
-- 6. 查询学过'python'并且也学过编号'java'课程的同学的姓名;----------------------------------
SELECT student.s_name from course,score,student where
score.s_id = student.s_id and course.c_id = score.c_id and course.c_name in('python','java')
GROUP BY score.s_id HAVING count(*) >=2
-- 7. 查询学过“alex”老师所教的全部课程的同学的姓名；----------------------------------------
select c_id from course,teacher where course.t_id = teacher.t_id and teacher.t_name='alex'
select count(*) from course,teacher where course.t_id = teacher.t_id and teacher.t_name='alex'
select * from score where score.c_id in(select c_id from course,teacher where course.t_id = teacher.t_id and teacher.t_name='alex') GROUP BY score.s_id HAVING count(*) =
(select count(*) from course,teacher where course.t_id = teacher.t_id and teacher.t_name='alex')
-- 8. 查询挂科超过两门(包括两门)的学生姓名;------------------------------
select student.s_name from score,student where score.s_id = student.s_id and score.num <70 GROUP BY score.s_id HAVING count(*) >=2
-- 9. 查询有课程成绩小于60分的同学的姓名;-------------------------
SELECT DISTINCT student.s_name from score,student where score.s_id = student.s_id and score.num <60
-- 10. 查询选修了全部课程的学生姓名;-----------------------------
select count(*) from course;
select s_name from score,student where score.s_id = student.s_id GROUP BY score.s_id HAVING count(*) = (select count(*)-1 from course)
-- 11.查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；----------------------------
select score.c_id from student,score where student.s_id = score.s_id and student.s_name ='貂蝉';
select DISTINCT student.s_name FROM student,score where
student.s_id = score.s_id and score.c_id in(1,2) and student.s_name !='貂蝉'
-- 12 查询学过'貂蝉'同学全部课程 的其他同学姓名；----------------------------------
select student.s_name FROM student,score where
student.s_id = score.s_id and score.c_id in(1,2) and student.s_name !='貂蝉'
GROUP BY score.s_id HAVING count(*) = (select count(*) from student,score where student.s_id = score.s_id and student.s_name ='貂蝉')
-- 13.查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；------------------------------------
-- select C_ID from score,student where score.s_id = student.s_id and student.s_name='貂蝉'
select student.s_name from score,student where score.s_id = student.s_id and
score.s_id in(select score.s_id FROM score GROUP BY score.s_id HAVING count(*) = 2)
and score.c_id in(1,2) GROUP BY score.s_id HAVING count(*) = 2 AND student.s_name != '貂蝉'
-- 14. 按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示：
-- 学生ID,python,java,linux,课程数,平均分--------------------------
select num from score,course where score.c_id = course.c_id and course.c_name = 'python' and score.s_id =1;
select num from score,course where score.c_id = course.c_id and course.c_name = 'java' and score.s_id =1;
select num from score,course where score.c_id = course.c_id and course.c_name = 'linux' and score.s_id =1;
select s.s_id as '学生ID' ,
(select num from score,course where score.c_id = course.c_id and course.c_name = 'python' and score.s_id =s.s_id ) as 'python',
(select num from score,course where score.c_id = course.c_id and course.c_name = 'java' and score.s_id =s.s_id ) as 'java',
(select num from score,course where score.c_id = course.c_id and course.c_name = 'linux' and score.s_id =s.s_id ) as 'linux',
count(s.s_id) as '课程数',
avg(s.num)
FROM score s GROUP BY s.s_id;
-- 15. 统计各科各分数段人数.-------------------------------------------------------
-- 显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select course.c_id as '课程ID', course.c_name ,
sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 end ) as '[100-85]',
sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 end ) as '[85-70]',
sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 end ) as '[70-60]',
sum(CASE WHEN num <60 THEN 1 ELSE 0 end ) as '[ <60]'
from score,course
where score.c_id = course.c_id GROUP BY score.c_id
-- 16 查询每门课程被选修的次数-------------------------------------------------
-- select course.c_name, count(*) FROM score,course where score.c_id=course.c_id GROUP BY score.c_id;
-- 17.查询出只选修了一门课程的学生的学号和姓名-------------------------------
-- select student.s_id,student.s_name from score,student where score.s_id = student.s_id GROUP BY s_id HAVING count(*) =1
-- 18.查询学生表中男生、女生各有多少人----------------------------------------
-- select s_sex,count(*) FROM student GROUP BY s_sex;
-- 19.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列-----------------------
-- SELECT avg(num),c_id from score GROUP BY c_id ORDER BY AVG(num) asc,c_id DESC
--
-- 20.查询课程名称为“python”，且分数低于60的学生姓名和分数
select student.s_name,score.num FROM course,score,student where course.c_id = score.c_id and score.s_id = student.s_id
and course.c_name ='python' and score.num <70
=====================================================================================. 视图 触发器 存储过程=======================
1. 视图--------------------------------------
创建视图-----------------------------
create view 视图名字 as 查询sql语句;
drop view 视图名字;
alter view 视图名字 as 查询sql语句;
2. 触发器-----------------------------------
1. 插入事件触发器--------
INSERT INTO order_table(gid,much) VALUES(1,3);
-- update goods set num = num -3 where id =1;
CREATE TRIGGER tg1 AFTER INSERT on order_table
for EACH row -- 固定写法
BEGIN
update goods set num = num -new.much where id =new.gid;
END
-- 删除触发器-----------------------------
drop TRIGGER TG1;
2.更新事件触发器----------
update order_table set much = much +2 where oid = 6;
update goods set num = num+2 where id = 1;
create TRIGGER tg2 AFTER UPDATE ON order_table
for EACH ROW
BEGIN
update goods set num = num+old.much - new.much where id = old.gid;
END
3.删除事件触发器------------------
DELETE FROM order_table where oid =6;
update goods set num = num + 3 where id = 1;
create TRIGGER tg3 AFTER DELETE on order_table
for EACH ROW
BEGIN
update goods set num = num + old.much where id = old.gid;
END
4.查看触发器--------------
show tiggers;
3.存储过程-------------------------------------
1.封装----------
-- CREATE PROCEDURE p1()
-- BEGIN
-- INSERT into goods VALUES (null,'韩涉',50);
-- select * from goods;
-- END
--
call p1();
2.参数------------
-- in out inout
CREATE PROCEDURE p3(in i int,inout names varchar(50))
BEGIN
update goods set name = names where id = i;
END
set @names = '大鹅';
call p2(4,@names);
select @names;
into 使用
set @i = 0;
set @n = '';
select num into @i from goods where id = 1;
select @i;
3.判断-----------
CREATE PROCEDURE p3(in flag char(5), in nums int)
BEGIN
if flag = 'true' then
SELECT * from goods where num < nums;
ELSEIF flag ='false' THEN
SELECT * FROM goods where num > nums;
ELSE
SELECT * FROM goods;
END if;
END
call p3('false',20);
4.循环--------
-- 做 1-100 累加的和
create PROCEDURE p4(in n int,out he int)
BEGIN
declare i int DEFAULT 0;
DECLARE sum int;
set sum = 0;
while i <= n DO
set sum = sum +i;
set i = i + 1;
end WHILE;
set he = sum;
end
set @he = 0;
call p4(100,@he);
select @he;
查看存储过程----------------------
show PROCEDURE status;
删除存储过程---------------
drop PROCEDURE p1;
4.函数----------
create FUNCTION f1(x int,y int)
RETURNS INT
BEGIN
declare sum int DEFAULT 0;
set sum = x +y;
RETURN(sum);
END
select f1(100,2);
select g.*,f1(100,num) FROM goods g;
DROP FUNCTION f1;
5.事物-------------
什么是事物---
一组sql语句批量执行,要么全部执行成功,要么全部执行失败
事物的四个特点:------------
原子性----
一致性---
隔离性---
持久性---
start TRANSACTION; -- 开启事物,关闭mysql自己的自动提交方式
SAVEPOINT sa1;
update account set money = money -1000 where id = 4;
SAVEPOINT sa1;
update account set money = money +1000 where id = 3;
-- COMMIT; -- 提交当前事物
select * from account;
ROLLBACK to sa1;-- 回滚当前事物
6.锁------------------------------
当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。
7.数据库的备份---------------------
备份:
mysqldump -uroot -p123456 数据库名 表 > 保存位置.
导入:
mysql> USE 数据库名;
mysql> source 备份文件.sql;