mysql03
时间：2021-07-01 10:21:17
帮助过：4人阅读
-- 01.启动mysql服务 使用管理员身份启动cmd
net start mysql
-- 02.关闭服务
net stop mysql
-- 03.连接mysql数据库 如果是本机 可以省略 -h ip地址 -p和密码不能有空格
mysql
-h ip地址
-u用户名
-p密码
-- 04.退出数据库
exit
-- 05.查询当前所有的数据库
show databases;
系统默认的四个数据库
information_schema:存放的是一些数据库对象信息，包含：用户表信息，字段信息，字符集信息等！
performance_schema:存放的是数据库的性能参数信息！
mysql:存放的系统用户权限信息！
test:数据库管理系统默认创建的测试数据库！任何用户都可以使用！
-- 06.sql(结构化查询语言)
01.DDL(数据定义语言)
create drop alter
02.DQL(数据查询语言)
select
03.DML（数据操作）
insert delete update
04.DCL(数据控制语言)
commit rollback grant revoke
-- 07.逻辑运算符
and or not
-- 08.创建数据库
CREATE DATABASE t12;
SHOW DATABASES;
-- 09.切换数据库
USE t12;
-- 10.删除数据库
DROP DATABASE t12;
-- 11.创建表 反引号` 是用来区分mysql中的关键字
-- ZEROFILL:不足位数使用0代替！超过位数，实际显示！
CREATE TABLE `test_01`(
tid INT(
4) ZEROFILL
);
-- 新增数据
INSERT INTO test_01(tid)
VALUES(
20);
INSERT INTO test_01
VALUES(
2000);
INSERT INTO test_01
VALUES(
20000);
-- 12.默认int是有符号的 可以取负数
CREATE TABLE `test_02`(
tid INT(
4)
);
-- 同时插入多条记录 负数可以插入
INSERT INTO test_02
VALUES(
20),(
1234),(
-123456)
-- UNSIGNED 无符号的 只能是整数 包含0
CREATE TABLE `test_03`(
tid INT(
4) UNSIGNED
);
-- 同时插入多条记录 负数不可以插入
INSERT INTO test_03
VALUES(
20),(
1234),(
-123456)
-- 删除测试的三个表
DROP TABLE test_01;
-- 13. 字符串类型
-- 01.char 固定长度的字符串
CREATE TABLE test_01(
NAME CHAR(
20)
);
INSERT INTO test_01
VALUES(
‘哈哈‘)
-- 最后一个 啊 会被自动截断！ 因为字符串超出了规定长度
INSERT INTO test_01
VALUES(
‘哈哈啊‘)
-- 02.varchar 可变长度的字符串
CREATE TABLE test_02(
NAME VARCHAR(
2)
);
-- 最后一个 啊 会被自动截断！ 因为字符串超出了规定长度
INSERT INTO test_02
VALUES(
‘哈哈啊‘)
-- 看不出区别
-- char(20)默认就已经开辟了20个空间
-- varchar(20)开辟了实际使用的空间
-- 14.日期格式
-- DATE : YYYY-MM-DD
-- DATETIME: YYYY-MM-DD HH:MM:SS
-- TIMESTAMP: YYYY-MM-DD HH:MM:SS 1970年1月1日开始的 ！首选！
-- TIME : HH:MM:SS
-- YEAR : YYYY
-- 15.创建表
-- creat table [if not exists] 表名(
-- 字段1 数据类型 [字段属性][约束][索引][注释],
-- 字段2 数据类型 [字段属性][约束][索引][注释]
-- )[表类型][表字符集][注释];
-- 16.字段的约束和属性
-- 01.非空约束 not null 字段不允许为空
-- 02.默认约束 default 字段的默认值
-- 03.唯一约束 unique key(字段名称) uk
-- 设置字段是唯一的不允许重复，可以为空，但是只能有一个空值
-- 04.主键约束 primary key(字段名称) pk
-- 设置表中的主键，唯一标识该行！
-- 05.外键约束 foreign key(字段名称) fk
-- 建立在从表中,用于关联两个表之间的关系
-- 需要指定引用了主表中的哪个字段
-- 06.自动增长 auto_increment
-- 设置字段自动增长，默认从1开始！通常设置在主键上！
-- 07.注释 COMMENT 给字段增加解释
CREATE TABLE IF NOT EXISTS student(
sid INT(
4) ZEROFILL
PRIMARY KEY AUTO_INCREMENT COMMENT
‘学生编号‘,
sname VARCHAR(
20)
NOT NULL COMMENT
‘学生姓名‘,
sage INT(
3)
NOT NULL DEFAULT 20 COMMENT
‘学生年龄‘
);
-- 新增数据
INSERT INTO student(sname,sage)
VALUES(
‘小黑‘,
DEFAULT);
INSERT INTO student(sid,sname,sage)
VALUES(
2,
‘小黑2‘,
DEFAULT);
-- 如果指定了主键的值，就是指定的！ 下次的自增冲动指定的值开始！
INSERT INTO student(sid,sname,sage)
VALUES(
789,
‘小黑3‘,
DEFAULT);
-- 查询刚刚插入数据的主键
INSERT INTO student(sname,sage)
VALUES(
‘小黑4‘,
DEFAULT);
SELECT @@identity;
第2次课
-- 切换到指定的数据库
USE t12
-- 创建Student表
CREATE TABLE IF NOT EXISTS student(
studentNo INT(
4)
PRIMARY KEY COMMENT
‘学号‘,
loginPwd VARCHAR(
20)
NOT NULL COMMENT
‘密码‘,
studentName VARCHAR(
50)
NOT NULL COMMENT
‘姓名‘,
sex CHAR(
2)
NOT NULL DEFAULT ‘男‘ COMMENT
‘性别‘,
gradeID INT(
4) UNSIGNED COMMENT
‘年级编号‘,
phone VARCHAR(
50) COMMENT
‘手机号码‘,
address VARCHAR(
255)
DEFAULT ‘地址不详‘ COMMENT
‘地址‘,
bornDate DATETIME COMMENT
‘出生日期‘,
email VARCHAR(
50) COMMENT
‘邮箱‘,
identityCard VARCHAR(
18)
UNIQUE KEY COMMENT
‘身份证号‘
)COMMENT=‘学生表‘;
两种方式备份和导入数据
01.使用dbms
02.cmd命令行工具 前提 必须配置了mysql环境变量
备份： mysqldump -u用户名
-p 数据库名称
>路径
备份指定的表：mysqldump -u用户名
-p 数据库名称 表名1 表名2
>路径
恢复： mysql -u用户名
-p 数据库名称
< 路径
--必须存在数据库
-- 创建年级表
CREATE TABLE IF NOT EXISTS grade(
gradeId INT(
4)
PRIMARY KEY COMMENT
‘年级编号‘,
gradeName VARCHAR(
20)
NOT NULL COMMENT
‘年级名称‘
)ENGINE=INNODB
DEFAULT CHARSET
=utf8 COMMENT
=‘年级表‘;
-- 查询本数据库中下所有的表
SHOW TABLES;
-- 查看表的结构
DESC student;
-- 删除表
DROP TABLE IF EXISTS 表名
-- 修改表名 alter table 旧表名 rename [to] 新表名
ALTER TABLE student RENAME
[TO] stu;
-- 添加字段
-- alter table 表名 add 字段名称 属性
ALTER TABLE student
ADD age
INT(
3)
NOT NULL COMMENT
‘年龄‘;
-- 修改字段 alter table 表名 change 旧字段 新字段 属性
ALTER TABLE student CHANGE age ages
INT(
4)
NOT NULL;
-- 删除字段 alter table 表名 drop 字段名称
ALTER TABLE student
DROP ages;
-- mysql系统帮助文档
help 查询内容（命令）;
-- 添加主键
ALTER TABLE 表名
ADD CONSTRAINT pk_studentNo主键名称
PRIMARY KEY 表名(主键字段)
-- 创建外键约束 必须在从表中创建
ALTER TABLE 从表名称
ADD CONSTRAINT
fk_student_grade外键约束名称 FOREIGN KEY(从表的外键字段)
REFERENCES 主表名称(主键)
-- 给student表中增加外键
并且设置 删除是为
cascade
-- 新增grade表中3条信息
INSERT INTO grade
VALUES(
1,
‘1年级‘),(
2,
‘2年级‘),(
3,
‘3年级‘);
-- 查询表中所有的记录
SELECT * FROM grade;
-- 删除年级编号为1的记录 的时候，对应的所有学生 也被删除了
DELETE FROM grade
WHERE gradeid
=1;
并且设置 删除是为 set null!
-- 查询所有的存储引擎
SHOW ENGINES
-- 查看当前默认的存储引擎
SHOW VARIABLES
LIKE ‘storage_engine%‘;
-- 修改存储引擎
-- 修改my.ini配置文件 default- storage-engine=InnoDB
-- 重启服务
--修改指定表中的存储引擎
alter table 表名 engine
=引擎
-- MyISAM类型的标文件
01.
*.frm 表结构的定义文件
02.
*.MYD 数据文件
03.
*.MYI 索引文件
-- InnoDB类型的标文件
01.
*.frm 表结构的定义文件
02.
*.ibd 数据文件
-- datadir地址下会有一个ibdata1：存储了所有InnoDB类型表中的数据！
-- 查询student表中所有的数据
SELECT * FROM student;
-- update 表名 set 字段1=值1,字段2=值2 [where 条件]
-- 修改所有学生的密码 为 admin
UPDATE student
SET loginPwd
=‘admin‘
-- 修改学生编号为2的密码 为 admins
UPDATE student
SET loginPwd
=‘admins‘
WHERE studentNo
=2
-- 删除数据 delete from 表名 [where 条件]
DELETE FROM student;
-- 删除学生编号为2的数据
DELETE FROM student
WHERE studentNo
=2;
-- truncate table 表名！
-- delete 和truncate的区别
01.delete可以加条件
02.truncate只能删除表中所有的数据
03.delete删除之后可以恢复数据
04.truncate不能恢复数据！慎用！
05.truncate指定速度比delete快！
06.truncate语句删除后将重置自增列！其他结构不变！
-- 创建学生课程表 subject
CREATE TABLE IF NOT EXISTS `subject`(
subjectNo INT(
4)
PRIMARY KEY AUTO_INCREMENT COMMENT
‘课程编号‘,
subjectName VARCHAR(
50)
NOT NULL COMMENT
‘课程名称‘,
classHour INT(
4) COMMENT
‘学时‘,
gradeId INT(
4) COMMENT
‘年级编号‘
)COMMENT=‘课程表‘;
-- 查询student表中所有的数据
SELECT studentName,address
FROM student;
-- 按照课时的大小 进行降序排列
-- asc：默认值！升序 desc:降序
SELECT * FROM `subject`
ORDER BY classHour
DESC
-- 查询年级编号为1的课程，并且按照课时升序排列
SELECT * FROM `subject`
WHERE gradeId
=1
ORDER BY classHour
ASC
-- 查询年级编号为1的课程，并且按照课时升序排列的同时
-- 按照课程编号在降序排列
SELECT * FROM `subject`
WHERE gradeId
=1
ORDER BY classHour
ASC,subjectNo
DESC
-- 使用别名 as
-- select 字段[as] 别名 from 表名
-- 查询课程名称 和 课时
SELECT subjectName
AS ‘课程名称‘,classHour
AS ‘课时‘
FROM `subject`
-- 合并列
01.
+的数据类型 必须一致
02.
+的如果是字符类型，拼接字符串
03.
+的是数值类型,就是求和！
SELECT * FROM student
-- 学生姓名和地址 进行合并
SELECT (studentNo
+‘,‘+gradeId )
AS ‘整合体‘
FROM student
-- 查询电话号码为空的行 is null
SELECT * FROM student
WHERE phone
IS NULL
-- 查询电话号码不为空的行 is not null
SELECT * FROM student
WHERE phone
IS NOT NULL
第3次课
-- 聚合函数
-- 查询表中一共有多少条记录
SELECT COUNT(
*)
FROM student
-- 查看表中指定字段不为空的记录
SELECT COUNT(phone)
FROM student
-- 查询年龄最大的数据
SELECT MAX(age)
FROM student
-- 查询年龄最小的数据
SELECT MIN(age)
FROM student
-- 求所有年龄的平均值
SELECT AVG(age)
FROM student
-- 求所有年龄之和
SELECT SUM(age)
FROM student
-- 字符串函数
-- 字符串的连接
SELECT CONCAT(
‘My‘,
‘S‘,
‘ql‘);
-- 字符串替换 下标从1开始
SELECT INSERT(
‘大家辛苦了！‘,
1,
3,
‘哈哈‘);
-- 字符串的截取 下标从1开始
SELECT SUBSTRING(
‘大家辛苦了！‘,
1,
3);
-- 字符串变大 小 写
SELECT LOWER(
‘AAA‘);
SELECT UPPER(
‘aaa‘);
-- 常用的时间和日期函数
-- 01.获取当前的日期
SELECT CURDATE();
-- 02.获取当前的时间
SELECT CURTIME();
-- 03.获取当前的日期和时间
SELECT NOW();
-- 04.返回日期是一年中的第几周
SELECT WEEK(NOW());
-- 05.只获取年
SELECT YEAR(NOW());
-- 06.只获取小时
SELECT HOUR(NOW());
-- 07.获取两个日期差
SELECT DATEDIFF(NOW(),
‘2017-03-07‘);
-- 08.返回现在的时间加上某个时间之后的日期
SELECT ADDDATE(NOW(),
24);
-- 数学函数
SELECT CEIL(
3.1);
-- 天花板函数
SELECT FLOOR(
3.9);
SELECT RAND();
-- 0-1之间的随机数
-- 把所有的成绩降低10%后加5分，再查询及格成绩
-- 并且按照成绩 降序排列
SELECT studentNo
AS ‘学号‘,(studentResult
*0.9+5)
AS ‘成绩‘
FROM result
WHERE (studentResult
*0.9+5)
>=60
ORDER BY ‘成绩‘ DESC
-- 查询所有年级编号为1 的学生信息，并且按照 学号升序排列
-- 01. 显示前4条记录
SELECT * FROM student
WHERE gradeid
=1
ORDER BY studentno
LIMIT 4
-- 02. 每页显示4条 显示第2页的内容
SELECT * FROM student
WHERE gradeid
=1
ORDER BY studentno
LIMIT 4,
4
-- 需要注意的是：第一条记录的是 0！
-- limit a,b
-- a:从那一条记录开始
-- b:每页显示多少条数据
-- 将学生表中学号为20000的学生邮箱改成stu@20000.163.com
-- 密码改成000
UPDATE student
SET email
=‘stu@20000.163.com‘,loginpwd
=‘000‘
WHERE studentNo
=20000
-- 将科目表中课时数大于200的并且年级编号是1的课时减少10！
UPDATE `subject`
SET classHour
=classHour
-10
WHERE classHour
>=200 AND gradeid
=1
-- 将所有年级编号是1的学生姓名，性别，出生日期，手机号码
-- 都保存到一个新的表中 student_new
CREATE TABLE IF NOT EXISTS student_new(
SELECT studentName,sex,bornDate,phone
FROM student
WHERE gradeid
=1
)
-- 01.查询2016年2月17日考试 前5名的学员的学号和分数
SELECT studentNo 学号,studentResult 分数
FROM result
WHERE examDate
=‘2016-02-17‘
ORDER BY 分数
DESC
LIMIT 5
-- 02. 将所有女学生按年龄从大到小排序
-- 从第2条记录开始显示6名女学生的姓名、年龄、出生日期、手机号信息
SELECT studentName 姓名,(
YEAR(NOW())
-YEAR(bornDate)) 年龄,
bornDate 出生日期,phone 手机号码 FROM student
WHERE sex
=‘女‘
ORDER BY 年龄
DESC
LIMIT 1,
6
-- 03.按出生年份分组统计学生人数
-- 将各组中人数达到2人的年份和人数显示出来
SELECT YEAR(bornDate) 出生年份,
COUNT(studentNo) 人数
FROM student
GROUP BY 出生年份
HAVING 人数
>=2
-- 04.查询参加2016年2月17日考试的所有学员的最高分、最低分、平均分。
SELECT MAX(studentResult) 最高分,
MIN(studentResult) 最低分,
AVG(studentResult) 平均分
FROM result
WHERE examDate
=‘2016-02-17‘
5.
select 字段1，字段2....
1.
from 表名
2.
where 条件1
and 条件2
3.
group by 分组依据
4.
having 分组条件
6.
order by 排序字段
7. limit 分页
-- 查询grade和student所有的数据
SELECT * FROM student,grade
13+4
13*4
-- 笛卡尔积
-- 怎么解决这个问题？ 找出两个表中关联的字段
SELECT * FROM student,grade
WHERE student.`gradeId`
=grade.`gradeId`
-- 查询出 年龄比 李斯文 小的学生信息
-- 01.先查询李斯文的年龄 1993-07-23
SELECT bornDate
FROM student
WHERE studentName
=‘李斯文‘
-- 02.把查询出来的结果当成条件继续查询
SELECT bornDate
FROM student
WHERE bornDate
>‘1993-07-23‘
-- 疑问？ 能不能把两个sql合并在一起执行
-- 我们就把 ‘1993-07-23‘当成一个java中的变量名
-- 指向了01的sql
STRING a
="
521";
Stirng b=a
+"
125";
syso(b)
syso(a+"
125")
-- 使用子查询来实现
-- 子查询就是一个嵌套在select,insert,update或者delete语句或者
-- 其他查询语句中的查询语句！
-- 语法： select ... from 表 where 字段 比较运算符（子查询）
SELECT bornDate
FROM student
WHERE
bornDate>(
SELECT bornDate
FROM student
WHERE studentName
=‘李斯文‘)
-- 子查询和比较运算符联合使用的使用，必须保证子查询返回的值是不能多于一个！
-- 查询出 最近一次java课程 考试成绩的最高分和最低分
-- 01.时间是最大的 最高分和最低分 result
-- 02.java课程 subject
-- 分析 分解书写
-- 01.先获取课程java的编号
SELECT subjectNo 学号
FROM `subject`
WHERE
subjectName=‘java‘
-- 02.最近java课程 的考试日期
SELECT MAX(examDate) 考试日期,
MAX(studentResult) 最高分,
MIN(studentResult) 最低分
FROM result
WHERE subjectNo
=1
-- 03.最近的日期
SELECT MAX(examDate)
FROM result
-- 使用子查询
SELECT MAX(examDate) 考试日期,
MAX(studentResult) 最高分,
MIN(studentResult) 最低分
FROM result
WHERE subjectNo
=(
SELECT subjectNo 学号
FROM `subject`
WHERE subjectName
=‘java‘)
AND examDate
=(
SELECT MAX(examDate)
FROM result)
-- 日期是最近的！
-- 查询java课程考试成绩为60的学生姓名
-- 01.先找java的课程编号
SELECT subjectNo 学号
FROM `subject`
WHERE
subjectName=‘java‘
-- 02.找java的成绩是60的
SELECT studentResult 成绩
FROM result
WHERE studentResult
=
