#通过Cmd连接数据库时：mysql -u账号 -p密码
#目前主流的数据库软件有：oracle、mysql、sqlserver
#什么是库？:库是用来存放n张表
#什么是表？：在数据库中，表(table)类似于excel，可以用来存放数据
#字段：类似于Excel中的表头
#数据类型：字符串(varchar)、整数(tinyint、int、bigint)、小数(float)、日期(Date、datetime)等
#什么是数据？：文本、视频、图片、音频等
#------------------------------创建数据库-------------------------------
#创建一个数据库：库名为dt55
#语法规则：create database 库名;
CREATE DATABASE dt55;
#删除指定的数据库：drop database 库名;
DROP DATABASE dt55;
#在创建库时，希望指定编码语法：create database 库名 character set 编码名
CREATE DATABASE dt55 CHARACTER SET utf8;
#--------------------------------创建表(table)---------------------------------------
#创建表语法：
CREATE TABLE 表名(
字段1 数据类型,
字段2 数据类型,
.....
)
#由于是先有库(database)，然后在库中创建表(table)
#选中某一个数据库：use 库名;
USE dt55;
CREATE TABLE java成绩表(
姓名 VARCHAR(40),
班级 VARCHAR(20),
Java成绩 FLOAT
);
#删除指定表语法：drop table 表名;
DROP TABLE java成绩表;
#------------------------往表中添加、删除、修改、查询数据(CRUD)-----------------------------
#往指定的表中添加数据：insert into 表名(字段1,字段2....) values(值1,值2....)
INSERT INTO `java成绩表`(姓名,班级,Java成绩) VALUES('王二麻子','dt55班',90.5);
INSERT INTO `java成绩表`(姓名) VALUES('王五');
#在插入时，可以省略掉表后面的字段名，但前提是：values关键字后面的字段值数量必须与表字段数量保持一致
INSERT INTO `java成绩表` VALUES('王二麻子','dt55班',90.5);
#插入数据的第二种语法：insert into 表名 set 字段名1=字段值1,字段名2=字段值2...
#如果"="是放在set关键字后面，则是“赋值运算符”
INSERT INTO `java成绩表` SET 姓名='李四';
INSERT INTO `java成绩表` VALUES('刘攀',NULL,100);
#同时插入多条数据：
INSERT INTO `java成绩表`(姓名,班级,Java成绩)
VALUES('abc','dt55班',90.5),('def','dt55班',90.5),('ccc','dt55班',90.5)
#删除数据语法：delete from 表名 where 条件
#如果“=”是放在where关键字后，则是“关系运算符”
DELETE FROM `java成绩表` WHERE 姓名='李四'
#---------------------------关系运算符------------------------------------
#在mysql中常用的关系运算符有：=(等于)、>、>=、<、<=、!=(不等于)
#在mysql中关系运算符需要放置在where关键字之后
#查询的语法规则：select 字段1,字段2... from 表名 [where 条件]
#1、查询某一张表中的所有数据记录
SELECT 姓名,班级,Java成绩 FROM `java成绩表`;
SELECT 姓名 FROM `java成绩表`;
SELECT * FROM `java成绩表`;#“*”代表查询某一张表中的所有字段
#查看`java成绩表`中Java成绩>=60的所有学生的名字
SELECT 姓名 AS 名字 FROM `java成绩表` WHERE Java成绩>=60;
#在查询数据的时候，可以通过as来给某一个字段取别名
SELECT 姓名 username FROM `java成绩表` WHERE Java成绩>=60;
#------------------------------逻辑运算符-------------------------------------
#在mysql中常见的逻辑运算有：与(and)、或(or)、非(not)
#查询`java成绩表`中班级='dt55班'并且Java成绩>=60
SELECT * FROM `java成绩表` WHERE 班级='dt55班' OR Java成绩>=80;
#作业
CREATE DATABASE dt55_mysql CHARACTER SET utf8;
USE dt55_mysql;
CREATE TABLE students(
id INT,
stuName VARCHAR(40),
age INT,
weight FLOAT
);
#同时往students表中插入3条数据记录
INSERT INTO students
VALUES(1,'王二麻子',20,60.5),
(2,'李四',21,60.5),
(3,'王五',22,62.5)
#查询students表，为每个字段取一个别名:id(主键)、stuName(学生名字)，age（年龄），weight(体重)
SELECT id AS 主键,stuName 学生名,age AS 年龄,weight 体重
FROM students;
#给表取别名
SELECT s.id,s.stuName FROM students s;
#--------------------------------对表数据的增(insert)删(delete)改(update)查(select)-------------------------------------------
#修改语法：update 表名 set 字段名1=值1,字段名2=值2.... where 条件
UPDATE students SET id=1,stuName='王二',age=25 WHERE id=1;
UPDATE students SET age=35 WHERE 1=1 AND id=1
#删除students表中的所有数据:
DELETE FROM students WHERE 1=1
#--------------------------对sql语句进行分类---------------------------------
数据库查询语言(DQL，database QUERY LANGUAGE)：对表的查询语句，select
数据库定义语言(DDL,DATABASE defined LANGUAGE)：create database、drop database、修改库、create table、drop table、修改表等
数据库操作语言(DML,DATABASE manage LANGUAGE):update、insert、delete
#DDL之操作数据库：
#添加数据库：create database 库名 【character set utf8/gbk】
#删除指定的数据库：drop database 库名;
#查询指定库的详细信息：
（1）show CREATE DATABASE 库名:查看某一个数据库的详细信息
SHOW CREATE DATABASE dt55_mysql;
(2) SHOW DATABASES:查看mysql服务器软件下所有的库
(3) 查看：当前用户连接的是哪个数据库：select DATABASE();
(4)查看指定的数据库下有哪些表： SHOW TABLES;
#通过cmd窗口连接mysql数据库，创建一个dt55_account库，在dt55_account下创建一张表(bank)
#id(编号) int,bankNo(卡号) varcahr，username varchar、money(余额) float
#宝强给马蓉转200万(算符运算符：+、-、*)
UPDATE bank SET money = money-200 WHERE bankNo='119';
UPDATE bank SET money = money+200 WHERE bankNo='911';
#马蓉给宋喆转200万
UPDATE bank SET money = money -200 WHERE bankNo='911';
UPDATE bank SET money = money +200 WHERE bankNo='110';
#修改指定库的编码：alter database 库名 character set 新编码名;
SHOW CREATE DATABASE dt55_account;
ALTER DATABASE dt55_account CHARACTER SET utf8;
#----------------------------DDL之对表的增删改查------------------------------------------------
#创建一张表：
CREATE TABLE 表名(字段名1 数据类型,字段名2 数据类型....);
#删除表：drop table 表名;
#查询：
(1)、查询某一张表的结构：desc 表名
DESC bank;
(2)、打印某一张表sql创建信息：show CREATE TABLE 表名;
SHOW CREATE TABLE bank;
#修改表：
（1）:对已经存在的表进行重命名：rename TABLE 旧表名 TO 新表名;
RENAME TABLE aaa TO bank;
(2)：往已经存在的表中添加字段信息：alter TABLE 表名 ADD 字段名 数据类型;
ALTER TABLE bank ADD gender VARCHAR(2);
(3)：删除某一张表中的字段：alter TABLE 表名 DROP 被删除的字段名
ALTER TABLE bank DROP gender;
(4)：对表中字段进行重命名:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段名数据类型
ALTER TABLE bank CHANGE aaa username VARCHAR(40);
(5)：修改某一字段的数据类型长度：ALTER TABLE bank CHANGE bankNo bankNo VARCHAR(25);
#-----往bank表中添加一个字段birthday
ALTER TABLE bank ADD birthday DATE;#date：只有年月日
UPDATE bank SET birthday='2000-2-28' WHERE id=1;
ALTER TABLE bank CHANGE birthday birthday DATETIME;#datetime：年月日时分秒
#-----------------------------------对某一数据库的备份与还原--------------------------------------------------------
#第一种通过命令：mysqldump -uroot -p密码 需要备份的数据库名>备份后的sql脚本名;
cmd--->mysqldump -uroot -proot dt55_account>c:\dt55_account_back.sql
还原备份的文件数据： 首先需要进入到mysql环境--->创建一个库---->在库下还原数据
----->source 备份的数据库脚本
#第二种通过sqlyog工具：选中需要备份的数据库---->右键---->备份/导出--->转储到sql
#作业
DROP TABLE book;
#给表中的某一字段添加注释：使用comment属性，comment关键字跟在字段的最后面
CREATE TABLE Book(
B_ID INT COMMENT '图书编号',
B_NAME VARCHAR(20) COMMENT '图书名称',
P_ID INT(20) COMMENT '出版社编号',
B_AUTHOR VARCHAR(20) COMMENT '图书作者',
B_PRICE FLOAT COMMENT '图书价格'
)
#对B_ID、B_NAME进行重命名
ALTER TABLE Book CHANGE B_ID id INT;
ALTER TABLE Book CHANGE B_NAME bookName VARCHAR(20)
#删除指定的表字段
ALTER TABLE Book DROP P_ID;
#修改字段类型的长度
ALTER TABLE Book CHANGE bookName bookName VARCHAR(40);
#对表重命名
RENAME TABLE book TO shu;
#同时添加3条数据
INSERT INTO shu VALUES(1,'java从入门到放弃','老阮',16800),
(2,'MySQL从删库到跑路','老丁',1)
SELECT * FROM shu WHERE id=1
#-----------------------------------数据类型的属性---------------------------------------------
#mysql中常见的数据类型：varchar(n)、float、int(n)、bigint(n)、date、datetime、text
#默认值：DEFAULT ‘默认值’
#非空：NOT NULL,如果某一字段被NOT NULL修饰后，添加数据时，此字段必须填写
#自动增长：auto_increment，尽量作用在int类型字段上
#主键：primary key，不能够重复，一张表中只有一个字段可以作为主键
#唯一键：unique，被unique修饰的数据不能够重复
DROP TABLE students;
CREATE TABLE students(
id BIGINT(20) AUTO_INCREMENT PRIMARY KEY COMMENT '学生编号',
stuName VARCHAR(40) COMMENT '学生姓名',
gender VARCHAR(2) DEFAULT '男' COMMENT '性别',
className VARCHAR(20) NOT NULL COMMENT'班级',
phone VARCHAR(20) UNIQUE COMMENT '手机号码'
)
#此处的delete可以删除整张表，但是删除数据后，自增列不会从1开始
DELETE FROM students WHERE 1=1
#如果要删除一整张表中的数据，使用truncate。使用truncate删除数据后，如果字段时自增的，则重新从1开始
TRUNCATE TABLE students;
#===================================数据类型属性的练习==============================================
(1)创建一张表：users
(2)id(用户编号,BIGINT(20))、username(用户名，varchar(40))、gender(性别,VARCHAR(2))
idcard(身份证号,VARCHAR(20))、javaScore(Java成绩)
(3)给每个字段都添加一个注释
(4)id为主键，自增、不能够为空
username不能够为空
gender给个默认值为女
idcard 唯一、不能够为空
javaScore默认值为0
USE dt55;
CREATE TABLE users(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
username VARCHAR(40) NOT NULL COMMENT '用户名',
gender VARCHAR(2) DEFAULT '女' COMMENT '性别',
idcard VARCHAR(20) UNIQUE NOT NULL COMMENT '身份证号',
javaScore FLOAT DEFAULT '0' COMMENT 'java成绩'
)
INSERT INTO users SET username='关雨',gender='男',idcard='110',javaScore=90;
INSERT INTO users SET username='蔡冠鹏',gender='男',idcard='120',javaScore=80;
INSERT INTO users SET username='贺晨',gender='男',idcard='911',javaScore=70;
#----------------------------------排序(order by 字段 降序/升序)---------------------------------------------
#排序时字段类型可以是数值类型(int、float)，也可以是varchar类型
#如果varchar类型对应的字段存放的是中文，则不能够排序；但是如果字段值都是英文，可以排序
#降序(DESC:)
SELECT * FROM users ORDER BY javaScore DESC;
SELECT * FROM users ORDER BY idcard DESC;
SELECT * FROM users ORDER BY username DESC;
#升序(ASC)
SELECT * FROM users ORDER BY javaScore ASC;
#------------------------------聚合函数-----------------------------------
#在mysql中函数使用select关键字调用：select 函数名(字段) 【FROM 表名】
#找出最大值:max(字段名)
#找出users表中javaScore的最高分
SELECT MAX(javaScore) AS 最高分 FROM users;
#找出最小值:min(字段名)
#找出users表中javaScore的最低分
SELECT MIN(javaScore) AS 最低分 FROM users;
#求平均数：avg(字段名)
SELECT AVG(javaScore) AS 平均分 FROM users;
#求和：
SELECT SUM(javaScore) AS 总分数 FROM users;
#统计记录：count(字段名)
#count(字段名)：如果字段的值为NULL，则此字段对应的数据条数不再统计之内
#为了解决上述问题，在统计某一张表中的所有数据记录时，最好使用count(*)
SELECT COUNT(*) AS 总条数 FROM users;
#--------------------------------------常用函数---------------------------------------------
#-------时间函数---------------
#NOW()：获取当前系统时间，时间格式包括年月日时分秒
SELECT NOW() AS 当前系统时间;
#CURTIME():只获取系统的时分秒
SELECT CURTIME();
#CURDATE()：只获取系统的年月日
SELECT CURDATE();
#----------数学函数-------------------
#向上取舍：
SELECT CEIL(2.3)
#向下取舍:floor(数值)
SELECT FLOOR(2.3)
#随机数：rand()：不用接受参数，返回的是0-1之间的小数
SELECT RAND()
#获取一个随机的4位数字，没有小数
SELECT CEIL(RAND()*10000)
#---------------------------------同时查询多条记录---------------------------------------
#获取id=1或者id=2或者id=4
SELECT * FROM users WHERE id=1 OR id=2 OR id=4
#in(数据1,数据2...)：判断表中某一个字段是否在in后面的参数列表之中
SELECT * FROM users WHERE id IN(1,2,4)
SELECT * FROM users WHERE id NOT IN(2,3) ORDER BY javaScore ASC;
#----------------------------------分组查询(group by 分类字段)--------------------------------------
CREATE TABLE godds(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
goodName VARCHAR(40) COMMENT '商品名称',
goodCategory VARCHAR(20) COMMENT '商品种类'
)
RENAME TABLE godds TO goods
#查询goods表中商品的种类
SELECT goodCategory FROM goods GROUP BY goodCategory
#查询goods表中是否有种类为衣服的类型
#注意点：如果一个查询语句中使用了group by，则后面的条件需要使用having关键字
SELECT goodCategory FROM goods GROUP BY goodCategory HAVING goodCategory='数码'
#----------------------------------分页(limit 起始下标,每页显示的数据量)--------------------------------------------
#goods表中有7条数据记录，每页显示3条，总共可以分3页
#获取第一页数据
SELECT * FROM goods LIMIT 0,3;
#获取第二页数据
SELECT *FROM goods LIMIT 3,3;
#获取第三页的数据
SELECT * FROM goods LIMIT 6,3;
SELECT * FROM goods LIMIT (pageNo-1)*pageSize,pageSize;
#作业
CREATE TABLE publisher(
P_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '出版社编号',
P_NAME VARCHAR(20) NOT NULL UNIQUE COMMENT '出版社名称',
p_LINKMAN VARCHAR(20) NOT NULL COMMENT '出版社联系人',
P_TEL VARCHAR(20) COMMENT '电话',
P_ADDRESS VARCHAR(50)
)
#往指定表中添加一个字段
ALTER TABLE `publisher` ADD price FLOAT;
#修改字段名
ALTER TABLE `publisher` CHANGE P_ID id BIGINT(20);
ALTER TABLE `publisher` CHANGE P_NAME `name` VARCHAR(20) COMMENT '出版社名称';
#同时添加5条数据记录
INSERT INTO `publisher` SET NAME='北京大学出版社',p_LINKMAN='王二麻子',price=99
INSERT INTO `publisher` SET NAME='清华大学出版社',p_LINKMAN='李四',price=20
INSERT INTO `publisher` SET NAME='武汉大学出版社',p_LINKMAN='战三',price=40
#找出最高价
SELECT MAX(price) AS 最高价 FROM `publisher`;
SELECT MIN(price) AS 最低价 FROM `publisher`;
SELECT * FROM `publisher` ORDER BY price DESC LIMIT 2,2
#------------------------------------------------------------------------------
#赋值某一张指定的表以及表数据
CREATE TABLE aaa(
SELECT * FROM `publisher`
);
#插入数据
INSERT INTO aaa
SELECT * FROM aaa;
#---------------------------------时间格式函数--------------------------------------
CREATE TABLE persons(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
personName VARCHAR(40) COMMENT '人名',
birthday DATETIME
)
SELECT personName,DATE_FORMAT(birthday,'%Y/%m/%d/ %H:%i:%s') AS birthday
FROM `persons`;#2017年12月25号
#-----------------------#多表查询(****)-----------------------------
SELECT * FROM users WHERE id IN(1,3,4);
SELECT * FROM users WHERE id=1 OR id=3 OR id=4
#聚合函数：max()、min()、avg()、sum()、count
#最高分
SELECT MIN(javaScore) AS minScore FROM users;
SELECT username FROM users WHERE javaScore=60;
#同时查询多张表
SELECT * FROM 表1,表2...表n WHERE 条件
SELECT u.username,temp.minScore
FROM users u,(SELECT MIN(javaScore) AS minScore FROM users) temp
WHERE u.javaScore=temp.minScore
#部门表(dept)
CREATE TABLE dept(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '部门编号',
deptName VARCHAR(20) COMMENT '部门名称'
)
#员工表(emp)
CREATE TABLE emp(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '员工编号',
empName VARCHAR(40) COMMENT '员工名',
salary FLOAT COMMENT '薪水',
deptId BIGINT(20) COMMENT '部门编号'
)
#(1)查询部门编号=1的部门下的所有员工
SELECT * FROM emp WHERE deptId=1
#(2)查询所有部门中的所有员工
SELECT 字段名1...字段n FROM 表1,表2...表n WHERE 条件
SELECT d.deptName,p.empName FROM dept d,emp p WHERE d.id=p.deptId
#(3)找出"开发部"中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='开发部'
#(4)找出"开发部"和"测试部"中的所有员工名、薪水、部门名
#第一种方式
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName IN ('开发部','测试部')
#第二种方式
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND (d.deptName='开发部' OR d.deptName='测试部')
#第三种方式(*)
#union、union all：可以将两个查询语句的结果进行合并,合并的前提是两个查询语句的数据结构是一样的
#union：可以自动去重
#union all：不能够去重
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='开发部'
UNION ALL
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='测试部'
#---------------------------------内连接与外连接----------------------------------------
#多表查询语法1：select * from 表1,表2...表n where 条件
#多表查询方式2：通过连接关键字
#内连接
#外连接
#左外连接
#右外连接
#内连接: 表1 inner join 表2 on 条件(多个表之间有关联的条件)
#(1)查询所有部门中的所有员工
SELECT * FROM dept d,emp e WHERE d.id=e.deptId
SELECT d.deptName,e.empName,e.salary
FROM
dept d INNER JOIN emp e
ON d.id=e.deptId
#(2)找出"开发部"中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary
FROM
dept d INNER JOIN emp e
ON d.id=e.deptId WHERE d.deptName='开发部'
#(3)找出"开发部"和"测试部"中的所有员工名、薪水、部门名
#作业
CREATE TABLE publisher(
P_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '出版社编号',
P_NAME VARCHAR(20) NOT NULL UNIQUE COMMENT '出版社名称',
p_LINKMAN VARCHAR(20) NOT NULL COMMENT '出版社联系人',
P_TEL VARCHAR(20) COMMENT '电话',
P_ADDRESS VARCHAR(50)
)
#往指定表中添加一个字段
ALTER TABLE `publisher` ADD price FLOAT;
#修改字段名
ALTER TABLE `publisher` CHANGE P_ID id BIGINT(20);
ALTER TABLE `publisher` CHANGE P_NAME `name` VARCHAR(20) COMMENT '出版社名称';
#同时添加5条数据记录
INSERT INTO `publisher` SET NAME='北京大学出版社',p_LINKMAN='王二麻子',price=99
INSERT INTO `publisher` SET NAME='清华大学出版社',p_LINKMAN='李四',price=20
INSERT INTO `publisher` SET NAME='武汉大学出版社',p_LINKMAN='战三',price=40
#找出最高价
SELECT MAX(price) AS 最高价 FROM `publisher`;
SELECT MIN(price) AS 最低价 FROM `publisher`;
SELECT * FROM `publisher` ORDER BY price DESC LIMIT 2,2
#------------------------------------------------------------------------------
#赋值某一张指定的表以及表数据
CREATE TABLE aaa(
SELECT * FROM `publisher`
);
#插入数据
INSERT INTO aaa
SELECT * FROM aaa;
#---------------------------------时间格式函数--------------------------------------
CREATE TABLE persons(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
personName VARCHAR(40) COMMENT '人名',
birthday DATETIME
)
SELECT personName,DATE_FORMAT(birthday,'%Y/%m/%d/ %H:%i:%s') AS birthday
FROM `persons`;#2017年12月25号
#-----------------------#多表查询(****)-----------------------------
SELECT * FROM users WHERE id IN(1,3,4);
SELECT * FROM users WHERE id=1 OR id=3 OR id=4
#聚合函数：max()、min()、avg()、sum()、count
#最高分
SELECT MIN(javaScore) AS minScore FROM users;
SELECT username FROM users WHERE javaScore=60;
#同时查询多张表
SELECT * FROM 表1,表2...表n WHERE 条件
SELECT u.username,temp.minScore
FROM users u,(SELECT MIN(javaScore) AS minScore FROM users) temp
WHERE u.javaScore=temp.minScore
#部门表(dept)
CREATE TABLE dept(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '部门编号',
deptName VARCHAR(20) COMMENT '部门名称'
)
#员工表(emp)
CREATE TABLE emp(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '员工编号',
empName VARCHAR(40) COMMENT '员工名',
salary FLOAT COMMENT '薪水',
deptId BIGINT(20) COMMENT '部门编号'
)
#(1)查询部门编号=1的部门下的所有员工
SELECT * FROM emp WHERE deptId=1
#(2)查询所有部门中的所有员工
SELECT 字段名1...字段n FROM 表1,表2...表n WHERE 条件
SELECT d.deptName,p.empName FROM dept d,emp p WHERE d.id=p.deptId
#(3)找出"开发部"中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='开发部'
#(4)找出"开发部"和"测试部"中的所有员工名、薪水、部门名
#第一种方式
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName IN ('开发部','测试部')
#第二种方式
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND (d.deptName='开发部' OR d.deptName='测试部')
#第三种方式(*)
#union、union all：可以将两个查询语句的结果进行合并,合并的前提是两个查询语句的数据结构是一样的
#union：可以自动去重
#union all：不能够去重
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='开发部'
UNION ALL
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='测试部'
#---------------------------------内连接与外连接----------------------------------------
#多表查询语法1：select * from 表1,表2...表n where 条件
#多表查询方式2：通过连接关键字
#内连接
#外连接
#左外连接
#右外连接
#内连接: 表1 inner join 表2 on 条件(多个表之间有关联的条件)
#(1)查询所有部门中的所有员工
SELECT * FROM dept d,emp e WHERE d.id=e.deptId
SELECT d.deptName,e.empName,e.salary
FROM
dept d INNER JOIN emp e
ON d.id=e.deptId
#(2)找出"开发部"中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary
FROM
dept d INNER JOIN emp e
ON d.id=e.deptId WHERE d.deptName='开发部'
#(3)找出"开发部"和"测试部"中的所有员工名、薪水、部门名
#作业
CREATE TABLE publisher(
P_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '出版社编号',
P_NAME VARCHAR(20) NOT NULL UNIQUE COMMENT '出版社名称',
p_LINKMAN VARCHAR(20) NOT NULL COMMENT '出版社联系人',
P_TEL VARCHAR(20) COMMENT '电话',
P_ADDRESS VARCHAR(50)
)
#往指定表中添加一个字段
ALTER TABLE `publisher` ADD price FLOAT;
#修改字段名
ALTER TABLE `publisher` CHANGE P_ID id BIGINT(20);
ALTER TABLE `publisher` CHANGE P_NAME `name` VARCHAR(20) COMMENT '出版社名称';
#同时添加5条数据记录
INSERT INTO `publisher` SET NAME='北京大学出版社',p_LINKMAN='王二麻子',price=99
INSERT INTO `publisher` SET NAME='清华大学出版社',p_LINKMAN='李四',price=20
INSERT INTO `publisher` SET NAME='武汉大学出版社',p_LINKMAN='战三',price=40
#找出最高价
SELECT MAX(price) AS 最高价 FROM `publisher`;
SELECT MIN(price) AS 最低价 FROM `publisher`;
SELECT * FROM `publisher` ORDER BY price DESC LIMIT 2,2
#------------------------------------------------------------------------------
#赋值某一张指定的表以及表数据
CREATE TABLE aaa(
SELECT * FROM `publisher`
);
#插入数据
INSERT INTO aaa
SELECT * FROM aaa;
#---------------------------------时间格式函数--------------------------------------
CREATE TABLE persons(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
personName VARCHAR(40) COMMENT '人名',
birthday DATETIME
)
SELECT personName,DATE_FORMAT(birthday,'%Y/%m/%d/ %H:%i:%s') AS birthday
FROM `persons`;#2017年12月25号
#-----------------------#多表查询(****)-----------------------------
SELECT * FROM users WHERE id IN(1,3,4);
SELECT * FROM users WHERE id=1 OR id=3 OR id=4
#聚合函数：max()、min()、avg()、sum()、count
#最高分
SELECT MIN(javaScore) AS minScore FROM users;
SELECT username FROM users WHERE javaScore=60;
#同时查询多张表
SELECT * FROM 表1,表2...表n WHERE 条件
SELECT u.username,temp.minScore
FROM users u,(SELECT MIN(javaScore) AS minScore FROM users) temp
WHERE u.javaScore=temp.minScore
#部门表(dept)
CREATE TABLE dept(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '部门编号',
deptName VARCHAR(20) COMMENT '部门名称'
)
#员工表(emp)
CREATE TABLE emp(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '员工编号',
empName VARCHAR(40) COMMENT '员工名',
salary FLOAT COMMENT '薪水',
deptId BIGINT(20) COMMENT '部门编号'
)
#(1)查询部门编号=1的部门下的所有员工
SELECT * FROM emp WHERE deptId=1
#(2)查询所有部门中的所有员工
SELECT 字段名1...字段n FROM 表1,表2...表n WHERE 条件
SELECT d.deptName,p.empName FROM dept d,emp p WHERE d.id=p.deptId
#(3)找出"开发部"中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='开发部'
#(4)找出"开发部"和"测试部"中的所有员工名、薪水、部门名
#第一种方式
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName IN ('开发部','测试部')
#第二种方式
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND (d.deptName='开发部' OR d.deptName='测试部')
#第三种方式(*)
#union、union all：可以将两个查询语句的结果进行合并,合并的前提是两个查询语句的数据结构是一样的
#union：可以自动去重
#union all：不能够去重
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='开发部'
UNION ALL
SELECT d.deptName,e.empName,e.salary
FROM dept d,emp e
WHERE d.id=e.deptId AND d.deptName='测试部'
#---------------------------------内连接与外连接----------------------------------------
#多表查询语法1：select * from 表1,表2...表n where 条件
#多表查询方式2：通过连接关键字
#内连接
#外连接
#左外连接
#右外连接
#内连接: 表1 inner join 表2 on 条件(多个表之间有关联的条件)
#(1)查询所有部门中的所有员工
SELECT * FROM dept d,emp e WHERE d.id=e.deptId
SELECT d.deptName,e.empName,e.salary
FROM
dept d INNER JOIN emp e
ON d.id=e.deptId
#(2)找出"开发部"中的所有员工名、薪水、部门名
SELECT d.deptName,e.empName,e.salary
FROM
dept d INNER JOIN emp e
ON d.id=e.deptId WHERE d.deptName='开发部'
#(3)找出"开发部"和"测试部"中的所有员工名、薪水、部门名
#作业
CREATE DATABASE dt55_homework CHARACTER SET utf8;
USE dt55_homework;
CREATE TABLE publisher(
P_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '出版社编号',
P_NAME VARCHAR(20) UNIQUE NOT NULL COMMENT '出版社名',
p_LINKMAN VARCHAR(20) NOT NULL COMMENT '出版社联系人',
P_TEL VARCHAR(20) COMMENT '电话',
P_ADDRESS VARCHAR(50) COMMENT '地址'
)
CREATE TABLE book(
B_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '图书编号',
B_NAME VARCHAR(20) COMMENT '图书名称',
P_ID BIGINT(20) COMMENT '出版社编号',
B_AUTHOR VARCHAR(20) COMMENT '图书作者',
B_PRICE FLOAT COMMENT '价格'
)
#往表中添加数据
#查询出版社的所有信息
SELECT * FROM `publisher`;
#查询所有的图书信息
SELECT * FROM book;
#查询B_NAME="计算机基础"的所有信息，包括出版社的信息
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM book b INNER JOIN `publisher` p ON b.`P_ID`=p.P_ID
WHERE b.`B_NAME`='计算机基础';
#查询所有武汉大学出版社出版的所有书籍
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM book b INNER JOIN `publisher` p ON b.`P_ID`=p.P_ID
WHERE p.p_NAME='武汉大学出版社'
#将出版社p_LINKMAN='张小明'的手机号改为xxxx
UPDATE `publisher` SET p_tel='15902738715' WHERE p_LINKMAN='张小明';
#查询book表中b_AUTHOR以"王"开头的所有书籍
SELECT * FROM `book` WHERE B_AUTHOR LIKE '王%'
#查询book表中b_AUTHOR以"a"结尾的所有书籍
SELECT * FROM `book` WHERE B_AUTHOR LIKE '%a'
#查询book表中b_AUTHOR中包含"a"的所有书籍
SELECT * FROM `book` WHERE B_AUTHOR LIKE '%a%'
#内连接：inner join
SELECT 字段名1,字段名2....字段n FROM 表1 别名1 INNER JOIN 表2 别名2 ON 条件 WHERE 条件
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM `publisher` p INNER JOIN book b ON b.`P_ID`=p.P_ID
#-----------------外连接-------------------
#左外连接:left join
#左外链接当条件不满足时，以左边的表为主
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM `publisher` p LEFT JOIN book b ON b.`P_ID`=p.P_ID
#右外链接:right join
#右外链接当条件不满足时，以右边的表为主
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM `publisher` p RIGHT JOIN book b ON p.P_ID=b.`P_ID`
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM book b LEFT JOIN `publisher` p ON p.P_ID=b.`P_ID`
#---------------------------3表查询（*****）----------------------------------
#求出teacherId=4的平均得分
#得到总分数
SELECT SUM(score) AS total FROM teacher_question WHERE teacherId=4
SELECT COUNT(*) AS num FROM `teacher_question` WHERE questionId=4 AND teacherId=4
SELECT temp1.total/temp2.num AS 平均分 FROM
(SELECT SUM(score) AS total FROM teacher_question WHERE teacherId=4) temp1,
(SELECT COUNT(*) AS num FROM `teacher_question` WHERE questionId=4 AND teacherId=4) temp2
#需求：想知道王二麻子具体买了哪些商品(商品名，价格，客户名，客户手机号)
SELECT g.goodName,g.price,c.customerName,c.phone
FROM goods g INNER JOIN goods_customer gc INNER JOIN customer c
ON g.id=gc.goodId AND gc.customerId=c.id WHERE c.customerName='王二麻子';
#视图：在真实表上面构建的一张虚表
#有dept、emp，要求查看所有的员工信息，还包括部门信息
SELECT * FROM dept d INNER JOIN emp e ON d.`id`=e.`deptId`
#创建视图语法:create view 视图名 as 查询语句;
CREATE VIEW view_all
AS SELECT e.id AS empId, e.`empName`,e.`salary`,e.`phone`,d.`deptName`
FROM dept d INNER JOIN emp e ON d.`id`=e.`deptId`
#删除视图:drop view 视图名
DROP VIEW `view_all`;
#查询某一视图中的数据
SELECT * FROM view_all WHERE empId=1;
#删除视图中的某一条数据
CREATE VIEW view_emp AS SELECT * FROM emp;
DELETE FROM view_emp WHERE id=1;
#往视图中添加数据
INSERT INTO view_emp SET empName='王二麻子'
SELECT * FROM view_emp;
#修改视图
UPDATE `view_emp` SET empName='李四' WHERE id=7
#视图的应用场景：在金融行业、保险行业、财务行业等
#-----------------------------------------------------------------------
#数据库建模(powerDesinger)：file---->new Model--->physical data model
#------------------------------------事务-------------------------------------------
#宝强给马蓉转200万
UPDATE `accounts` SET money= money-200 WHERE bankNo='119';
UPDATE `accounts` SET money = money+200 WHERE bankNo='911';
#事务:
#什么是事务？：多组操作要么全部成功要么全部失败
开启事务：start TRANSACTION;
回滚事务(如果事务提交后，不能够回滚)：rollback;
提交事务：commit;
#事务的4大特性：
#原子性(automic)：同一个事务中多组操作不能够分割，必须是一个整体
#一致性(consistent)：事务操作前与事务操作后总量保持一致
#隔离性(isolation)：多个事务之间互不干扰
#持久性(durable)：数据一旦进入到库中、表中，就永久存在
UPDATE `accounts` SET money = money-300 WHERE bankNo='120';
UPDATE `accounts`SET money = money+300 WHERE bankNo='911';
#--------------------------------------mysql数据库高级部分之存储过程-----------------------------------------------------
#创建存储过程的基本语法规则：
DELIMITER //
CREATE PROCEDURE 存储过程名(IN 输入参数名 参数类型,OUT 输出参数名 参数类型)
BEGIN
代码块;
END//
DELIMITER ;
#删除存储过程:
DROP PROCEDURE 【if exists】 存储过程名;
DROP PROCEDURE IF EXISTS test1;
#调用存储过程
CALL 存储过程名(参数1...参数n);
#带if语句的存储过程
#加薪的存储过程，传递两个参数：id、m(只能够传递整数，不能够传递负数)
SELECT '亲，您输入的金额不能够为负数!!!' AS '友情提示';
DELIMITER //
CREATE PROCEDURE pro_addSalary(idd BIGINT,m FLOAT)
BEGIN
IF m>0 THEN
UPDATE users SET money=money+m WHERE id=idd;
END IF;
END//
DELIMITER ;
CALL `pro_addSalary`(1,-500);
#带if..else的存储过程
DROP PROCEDURE IF EXISTS pro_salaryAdd;
DELIMITER //
CREATE PROCEDURE pro_salaryAdd(idd BIGINT,m FLOAT)
BEGIN
IF m>0 THEN
UPDATE users SET money=money+m WHERE id=idd;
ELSE
SELECT '亲，您输入的金额不能够为负数!!!' AS '友情提示';
END IF;
END//
DELIMITER ;
CALL `pro_salaryAdd`(1,-2000);
#带if...else if...else语句的存储过程
#存储过程名:pro_buyCar(money float)，如果money>500万则买保时捷；否则如果money>300万，买宝马；否则如果money>10万买奥拓；否则骑摩拜
DELIMITER //
CREATE PROCEDURE pro_buyCar(money FLOAT)
BEGIN
IF money>500 THEN
SELECT '买保时捷' AS '买啥';
ELSEIF money>300 THEN
SELECT '宝马' AS '买啥';
ELSEIF money>10 THEN
SELECT '奥拓' AS '买啥';
ELSE
SELECT '骑摩拜' AS '骑啥';
END IF;
END//
DELIMITER ;
CALL `pro_buyCar`(5);
#练习题：存储过程:pro_score(score float)，如果成绩>90分则是A等；
#否则score>80B等；否则如果score>=60，C等；否则score<60，不及格
#case选择分支结构
#存储过程名:pro_case(i int)，如果i=1则打印星期一，i=2则打印星期二....
DELIMITER //
CREATE PROCEDURE pro_case(i INT)
BEGIN
CASE i
WHEN 1 THEN
SELECT '星期一' AS '日期';
WHEN 2 THEN
SELECT '星期二' AS '日期';
ELSE
SELECT '今天不是周一或者周二，到底周几你猜？' AS '日期';
END CASE;
END//
DELIMITER ;
CALL `pro_case`(3);
#case练习：存储过程名:pro_case2(i int)，
#如果i=1，则拨打"爸爸"的电话，如果i=2则拨打"妈妈",否则：您打错了
DELIMITER //
CREATE PROCEDURE pro_case2(i INT)
BEGIN
CASE i
WHEN 1 THEN
SELECT '拨打father的号码';
WHEN 2 THEN
SELECT '拨打mother的号码';
ELSE
SELECT '您打错了';
END CASE;
END//
DELIMITER ;
CALL `pro_case2`(1);
###while循环
#Java中while循环的语法:
WHILE(条件){
循环体;
循环终止条件;
}
#存储过程名：pro_while2(i int)，如果i=100，则计算1到100之间的所有数之和，返回最终结果
DELIMITER //
CREATE PROCEDURE pro_while2(IN i INT,OUT total INT)
BEGIN
DECLARE a INT DEFAULT 1;
SET total=0;
WHILE a<=i DO
SET total=total+a;
SET a=a+1;
END WHILE;
END//
DELIMITER ;
CALL `pro_while2`(100,@aaa);
SELECT @aaa;
#如果i=100则往users表中插入100条数据
DROP PROCEDURE IF EXISTS pro_while1;
DELIMITER //
CREATE PROCEDURE pro_while1(IN i INT)
BEGIN
DECLARE a INT DEFAULT 1;
WHILE a<=i DO
INSERT INTO `users` SET username='test',money=100;
SET a=a+1;
END WHILE;
END//
DELIMITER ;
CALL pro_while1(10);
SELECT COUNT(*) FROM users;
TRUNCATE TABLE users;
##loop循环:
CREATE PROCEDURE 存储过程名()
BEGIN
loop循环别名:LOOP
循环体;
LEAVE loop循环别名;
END LOOP;
END;
#-----------------------
#通过loop循环往users表中同时添加100条记录
DELIMITER //
CREATE PROCEDURE pro_loop()
BEGIN
DECLARE i INT DEFAULT 0;
loop_test1:LOOP
INSERT INTO `users` SET username='admin',money=200;
SET i = i+1;
IF i=100 THEN
LEAVE loop_test1;
END IF;
END LOOP;
END//
DELIMITER ;
CALL `pro_loop`();
#------------------------找出1-100之间能够被3整除的所有数之和-----------------------------
DROP PROCEDURE IF EXISTS `pro_sum`;
DELIMITER //
CREATE PROCEDURE `pro_sum`()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
WHILE i<=10 DO
IF i MOD 3=0 THEN
SET total=total+i;
END IF;
SET i = i+1;#循环结束条件
END WHILE;
SELECT total;
END//
DELIMITER ;
CALL `pro_sum`();
#作业
#书写一个存储过程，将`users`表中的所有money之和返回
SELECT SUM(money) FROM `users`;
DELIMITER //
CREATE PROCEDURE pro_qiuSum(OUT total FLOAT)
BEGIN
SELECT SUM(money) INTO total FROM `users`;
END//
DELIMITER ;
CALL pro_qiuSum(@a);
SELECT @a;
364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
