MySQL数据库笔记

MySQL数据库

关系型数据库管理系统

1.MySQL常用命令

show databases: 查看所有数据库

use: 数据库名，指定操作某个数据库

show tables：显示当前选中数据库中的所有表

show tables from 数据库名字：显示数据库中的表，不会改变所选中的数据库

select database()：查看所选中的数据库

desc 表名：查看某表的全部记录

select * from 表名：查看某表的全部记录

select version():查看数据库版本

mysql --version/mysql -V : 登录前查看数据库版本

create table 表名（列名 列类型 ...）: 创建表

2.MySQL的语法规范

不区分大小写，建议关键字大写，表名和列名小写。

每条命令用\g或；结尾，建议用；结尾。

根据命令需要，可以进行缩进或换行。

注释：

单行注释：#注释文字 或 --(空格)注释文字

多行注释：/* 注释文字 

DQL（DQL Data Query Language）数据查询语言

3.基础查询

语法：

select 查询列表 from 表名；

注意：查询列表可以是：表中的字段、常量、表达式和函数 查询的结果是一个虚拟的表格

-- *代表表所有字段，

SELECT * FROM student

-- 查看单列

SELECT name FROM student

-- 查看所有字段 PS:多个字段之间使用逗号隔开

SELECT name,department FROM student

-- 指定常量列

SELECT name,department,'某某中学' FROM student

-- 查询表达式

SELECT 2/100

-- 查询函数

SELECT VERSION()

-- 别名

SELECT name AS 姓名,department AS 院系 FROM student

SELECT name 姓名,department 院系 FROM student

-- 去重

SELECT department,name,DISTINCT sex FROM student

-- +号

SELECT 5+6

SELECT 5+4.5

SELECT 5+"hello" -- 5

SELECT NULL+5 -- NULL

-- 如果要实现拼接操作，则需要使用CONCAT(a,b,c) as 函数

SELECT CONCAT(name,sex) AS 性别和姓名 FROM student

4.按照条件查询

语法：

SELECT 字段A,字段B,字段C FROM student WHERE 筛选条件

1）.根据条件表达式筛选

> < <= >= !=

-- 成绩大于90分的信息

SELECT * FROM score WHERE grade>90

-- 成绩等于94分的信息

SELECT * FROM score WHERE grade=94

-- 成绩不等于94分的学生信息

SELECT * FROM score WHERE grade!=94

2） 多条件查询

AND 指两个条件同时满足

SELECT * FROM score WHERE grade>=90 AND c_name='计算机'

OR 或者的意思

SELECT * FROM score WHERE grade>=90 OR c_name='计算机'

3）.模糊查询

LIKE

-- %代指 0个或者多个字符 _代表单个字符

SELECT * FROM student WHERE address LIKE '%永%'

SELECT * FROM student WHERE address LIKE '_京_'

5.区间查找

SELECT * FROM score WHERE grade>=60 AND grade<=90

-- BETWEEN AND 包含边界

SELECT * FROM score WHERE grade BETWEEN 60 AND 90

-- IN 后面的括号中可以跟多个值，这些值必须是 in 前面字段包含的值

SELECT * FROM student WHERE name in ('张三','李四','王五')

SELECT * FROM student WHERE name = '张三'

-- is NULL

SELECT name FROM student WHERE name is NOT NULL

6. 排序

语法：

select 查询列表 from 表 where 筛选条件 order by 排序列表 [asc/desc]

默认为asc升序。

一般放在查询语句的最后面，limit语句除外。

-- 如果你不进行任何的筛选直接是根据某列进行排序，则不需要where

-- ASC 升序（默认）可以不写 desc 是降序

SELECT * FROM score ORDER BY grade DESC

-- 对计算机系的学生成绩进行降序排列

SELECT * FROM score WHERE c_name='计算机' ORDER BY grade DESC

10.13

字符函数

常见函数

优点：提高重用性、隐藏实现细节

调用：select 函数名(实参列表) from 表名

-- 统计学生表中有多少条记录

SELECT COUNT(id)FROM student

-- LENGTH(name)用来计算字节数 一个汉字是三个字节

SELECT LENGTH(name) FROM student

-- 字符串拼接

SELECT CONCAT(name,id) 姓名学号 FROM student

-- 大小写转换

-- UPPER(name) 将小写转换为大写

SELECT UPPER(NAME) FROM student

-- lower（str）将大写转换为小写

SELECT LOWER(NAME) FROM student

-- substr 字符串的截取，取索引为2的后面的所有内容

SELECT SUBSTR(ADDRESS,4) FROM student WHERE ID=901

-- 从下标为4的位置开始截取2个字符

SELECT SUBSTR(address,4,2) FROM student WHERE id=901

-- instr(str,substr) 查看address字段值中第一个福字的索引

SELECT instr(address,'福') FROM student WHERE id=905

-- TRIM去掉前后左右空格 中间不行

SELECT TRIM(address) FROM student WHERE id=905

-- trim("x" from "xstr") 只能替换前后字符

SELECT TRIM('京' from address) FROM student WHERE id=901

-- 左填充 lpad(str,n,c) 规定输出10个字符的长度，如果长度不够呢，使用*代替

SELECT lpad(address,10,'*') FROM student

-- 右填充

SELECT rpad(address,10,'*') FROM student

-- replace

SELECT REPLACE(address,"门","*") FROM student WHERE id=905

2.数学函数

-- 四舍五入

SELECT stu_id,ROUND(grade) FROM score01

-- 向上取整

SELECT stu_id,ceil(grade) FROM score01

-- 向下取整

SELECT stu_id,floor(grade) FROM score01

-- 保留小数点

SELECT truncate(grade,3) FROM score01

-- 取余

SELECT MOD(grade,8) FROM score01

-- 获取随机小数

SELECT rand()

rand() 获取0-1之间的随机数

3.日期函数

SELECT name,NOW() FROM student

-- curdate() 日期

SELECT name,CURDATE() FROM student

-- curtime() 返回当前时间

SELECT name,CURTIME() FROM student

SELECT '时间',CURTIME()

-- 获取年份

SELECT year(NOW()),MONTH(NOW()),day(NOW()),hour(NOW()),minute(NOW()),second(NOW())

-- 将字符串类型的时间日期转换为 时间格式

SELECT STR_TO_DATE('2021:10:13 14:35:54','%Y:%m:%d')

-- 将日期转换为字符

SELECT DATE_FORMAT(NOW(),'%Y:%m:%d')

两日期相差的天数

SELECT datediff('2021-12-13','2021-10-13')

-- 查看当前操作的是哪个数据库

SELECT DATABASE()

-- 用户

SELECT user()

-- md5 加密

SELECT MD5('hello')

5.流程控制函数

if (exp1,exp2,exp3) 若exp1为true，返回exp2，否则返回exp3

case

用法一：类似于Switch等值判断

case 要判断的字段或表达式

when 常量1 then 要显示的值 或 语句1；

…

when 常量n then 要显示的值 或 语句n；

else 要显示的值或语句m

end

例如：

SELECT stu_id ,grade 原始成绩,

CASE stu_id

WHEN 901 THEN grade*1.1

WHEN 902 THEN grade*1.2

WHEN 903 THEN grade*1.3

ELSE grade

END AS 新成绩

FROM score

用法二：类似于区间判断

case

when 条件1 then 要显示的值或语句1；

…

when 条件n then 要显示的值 或 语句n；

else 要显示的值或语句m

end

例如：根据学生成绩，判断登记

SELECT grade,

CASE

WHEN grade>90 THEN "a"

WHEN grade>80 THEN "b"

WHEN grade>70 THEN "c"

ELSE "D"

END AS 成绩登记

FROM score

分组函数

分组函数，做统计使用，又称统计函数或聚合函数

-- 统计每一个科目的平均成绩

SELECT c_name,AVG(grade) AS 平均成绩 FROM score GROUP BY c_name

-- 求每一个科目的最高成绩

SELECT c_name,MAX(grade) AS 最高成绩 FROM score GROUP BY c_name

-- 求每一个科目的最低成绩

SELECT c_name,MIN(grade) AS 最低成绩 FROM score GROUP BY c_name

-- 成绩综合

SELECT c_name,SUM(grade) AS 总成绩 FROM score GROUP BY c_name

-- HAVING子句就是对已经分组计算后进行过滤筛选

SELECT c_name,AVG(grade) AS 平均成绩 FROM score GROUP BY c_name HAVING AVG(grade)<90

注意：

MYISAM存储引擎下，count(*)效率高，INNODB下差不多

sum和avg对数值型处理，min和max可对字符型和日期型排序

所有分组函数都忽略null值，可和distinct搭配使用和分组函数一同查询的字段要求是group by后的字段

分组查询

语法：

select 分组函数，列

from 表名 【where 筛选条件】

group by 分组列表 【order by 字句】

注意：查询列表必须使分组函数和group by后出现的字段

特点：

分组前筛选 数据源为原始表 用where

分组后筛选 数据源为分组后结果集 用having

分组函数做条件肯定放在having子句中

能用分组前筛选的优先考虑分组前筛选group by支持单个字段，多个字段(用，隔开)，表达式或函数分组，也可以添加排序放在最后。

例如：

SELECT c_name,avg(grade) FROM score GROUP BY c_name //计算每个考试科目的平均成绩

#求每个系的最高分

SELECT c_name,AVG(grade) FROM score GROUP BY c_name

多字段分组

SELECT stu_id,c_name,AVG(grade) FROM score GROUP BY c_name,stu_id

HAVING 对之前分组的结构进行过滤

SELECT c_name,AVG(grade) FROM score GROUP BY c_name HAVING AVG(grade)<=90;

-- 子查询：一句SQL嵌套到另一个SQL语句中

/*

= 后面只能是一个值

IN 后面可以是多个值

NOT IN 和IN相反

*/

-- 查询计算系成绩大于80分的学生id

SELECT stu_id FROM score WHERE c_name='计算机' AND grade>80

-- 查询计算系成绩大于80分学生姓名

SELECT name FROM student WHERE id NOT IN (SELECT stu_id FROM score WHERE c_name='计算机' AND grade>80)

-- EXISTS 后面括号中能查询出来数据，就执行where的SQL

SELECT * FROM student WHERE EXISTS (SELECT * FROM score WHERE id = 10)

分页查询

特点：limit语句放在查询语句的最后

语法：

select 查询列表 from 表 .... limit offset，size

offset：要显示条目的索引 从0开始

size：要显示的条目个数

例如：

-- 1代指从索引为1的地方开始取值 索引是从0开始

-- 取3行数据

SELECT * FROM student WHERE id<905 LIMIT 1,3

10.14

连接查询又称多表查询

SQL92 等值连接

SELECT st.name AS 姓名,st.address AS 地址,s.grade AS 成绩 

FROM student st,score s WHERE st.id = s.stu_id

-- SQL92 非等值连接

-- score筛选后剩下3条数据，拿出来和student表中的数据做组合  3 * 6

SELECT * FROM student,score WHERE score.grade BETWEEN 60 AND 80

3 6 = 18

-- SQL92 自然连接

SELECT * FROM student s1,student s2 WHERE s1.id = s2.id

SQL99连接方式：

1.内连接:就和SQL92中等值连接是一样的

2.非等值连接

3.自然连接

4.外连接：

左外连接

右外连接

交叉连接

全外连接 （MySQL不支持）

1.内连接

求得是两张表中相同得数据，可以使用内连接从多张表中取值

SELECT * FROM student,score WHERE student.id=score.stu_id

表1 INNER JOIN 表2 ON 连接条件

SELECT * FROM student INNER JOIN score ON student.id=score.stu_id

SELECT * FROM a_table INNER JOIN b_table ON a_table.a_id=b_table.b_id

2.左外连接 LEFT JOIN

左侧表中所有内容全部展示，右侧表中展示和左侧相交的部分数据，空余的部分使用null代替

SELECT * FROM a_table LEFT JOIN b_table ON a_table.a_id = b_table.b_id

3.右外连接 RIGHT JOIN

SELECT * FROM a_table RIGHT JOIN b_table ON a_table.a_id = b_table.b_id

4.交叉连接 CROSS JOIN

展示的相交的部分

SELECT * FROM a_table CROSS JOIN b_table ON a_table.a_id =b_table.b_id

插入单行数据 语法；insert into 表名（字段...） values （值...）

INSERT INTO student(id,name,sex,birth,department,address) VALUES (907,'王宝强','男',1990,'计算机系','陕西省西安市')

插入多行数据 ，

修改 语法；update 表名 set 列 = 新值，... where 筛选条件

修改学号904的学生姓名为李大四

UPDATE student SET NAME='李大四'WHERE id=904

修改选择的行数据的多个字段信息，中间使用逗号隔开

UPDATE student SET NAME='李大四',sex='女' WHERE id=904

删除

方式一 单行的删除 语法；delete from 表名 where 筛选条件+

删除单行数据

DELETE FROM student WHERE id=913

删除多行数据

DELETE FROM student WHERE id IN (912,914)

DELETE FROM student WHERE id>907

方式二：

将表数据全部删除 语法：truncate table 表名，整个表全部删除

truncate table student

库和表的管理

创建数据库 create database 库名

删除数据库 drop database 库名

创建表

create table 表名(

字段名 数据库类型,

字段名 数据库类型,

字段名 数据库类型

)

删除表 drop table 表名

常见的数据类型

数值型

整型 int 浮点型 float double 字符型 char

日期型

date只保存日期

time只保存时间

year只保存年

datetime和timestamp保存日期+时间

datetime和timestamp区别

常见约束

主要是为了查询。因为主键能表示某行数据

主键 唯一

一旦给某个字段设置了主键，该字段不能为空

如果主键所对应的数据值为空，那么也只能有一个为空

-- 在修改表的时候添加主键

-- alter table user add primary key(id);

ALTER TABLE student ADD PRIMARY KEY(id)

-- 删除主键约束

-- 主键虽然删除了，但是NOT NULL 依然存在

ALTER TABLE student DROP PRIMARY KEY

-- 在创建表的时候添加主键

-- 创建表

-- 方式一：

CREATE TABLE student02 (

id INT(3) PRIMARY KEY,

name CHAR(30),

sex CHAR(1),

birth YEAR(4),

department CHAR(30),

address CHAR(30)

)

-- 方式二：

CREATE TABLE student03 (

id INT(3),

name CHAR(30),

sex CHAR(1),

birth YEAR(4),

department CHAR(30),

address CHAR(30),

PRIMARY KEY(id)

)

自增

自增开始值为1

每次自增1

-- 创建表的时候设置自增

CREATE TABLE student06 (

id INT(10) PRIMARY key auto_increment,

name CHAR(30),

sex CHAR(1),

birth YEAR(4),

department CHAR(30),

address CHAR(30)

)auto_increment=1000

-- 在修改表时添加自增

ALTER TABLE student03 CHANGE COLUMN id uid INT(11) auto_increment

-- 删除自增

ALTER TABLE student03 MODIFY uid INT(11)

-- 修改表时设置自增初始化值

ALTER TABLE student03 auto_increment=1000

10.15

1.外键

表间关系：

一对一

一对多

多对多

建立主从关系后，从表后新增一行外键约束

CONSTRAINT `sc_id` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)

外键约束是用来约束从表（根据主表的信息约束从表）

一个表外键约束能有多个，但是主键只能有一个

-- 在创建表的建立主键约束（主表要先完成创建，从表才可以关联）

CREATE TABLE score (

id int(10) NOT NULL AUTO_INCREMENT,

stu_id int(10) NOT NULL,

c_name varchar(20) DEFAULT NULL,

grade int(10) DEFAULT NULL,

PRIMARY KEY (id),

UNIQUE KEY id (id),

CONSTRAINT fk_stu_sc FOREIGN KEY(stu_id) REFERENCES student(id)

)

-- 在修改表的建立主键约束

alter table 表名 

add CONSTRAINT 外键名字 FOREIGN KEY(从表字段) REFERENCES 主表名(主表主键)

ALTER TABLE score ADD CONSTRAINT sc_id FOREIGN KEY(stu_id) REFERENCES student(id)

-- 删除外键 外键名不能加引号

ALTER TABLE score DROP FOREIGN KEY sc_id

2. 唯一性约束

-- 唯一性约束如果没有指定约束名字，会使用字段名命名

-- 创建表的时候

CREATE TABLE student02 (

id int(10) ,

name varchar(20) NOT NULL UNIQUE,

sex varchar(4) ,

birth year(4) ,

department varchar(20) NOT NULL,

address varchar(50) ,

PRIMARY KEY (id)

)

-- 修改表的时候

ALTER TABLE student ADD UNIQUE(name)

-- 删除唯一性约束

ALTER TABLE student DROP INDEX name

-- 指定唯一性约束的名字

ALTER TABLE student ADD CONSTRAINT student_name UNIQUE(name);

3. 非空约束

-- 在创建表的时候添加非空约束

CREATE TABLE student04 (

id int(10) ,

name varchar(20) NOT NULL,

sex varchar(4) NOT NULL,

birth year(4) NOT NULL,

department varchar(20) NOT NULL,

address varchar(50) ,

PRIMARY KEY (id)

)

-- 在修改表的时候添加

ALTER TABLE student CHANGE name name01 VARCHAR(30) NOT NULL;

-- 删除非空约束

ALTER TABLE student CHANGE name01 name VARCHAR(30)

-- 创建表的时候设置默认值

/*

如果设置了默认值，一旦指定默认值，插入数据的数据的时候不插入该字段，则使用默认值

如果插入该字段的值，则使用新插入的值

*/

CREATE TABLE student03 (

id int(10) ,

name varchar(20) NOT NULL,

sex varchar(4) ,

birth year(4) ,

department varchar(20) NOT NULL,

address varchar(50) DEFAULT '水帘洞',

PRIMARY KEY (id)

)

-- 修改表的时候创建默认值

ALTER TABLE student CHANGE address address VARCHAR(30) DEFAULT "高老庄"

-- 删除默认值

-- 没有专门对应的删除默认值的方法，只需要将默认值设置为空即可

ALTER TABLE student CHANGE address address VARCHAR(30) DEFAULT ""

-- 创建视图

CREATE VIEW stu_sc01 AS

SELECT student.id,student.name,student.birth,student.address,score.c_name,score.grade

FROM student INNER JOIN score ON student.id=score.stu_id

-- 查询地址是湖南的 姓名、年龄、院系、考试科目和成绩

SELECT * FROM stu_sc01 WHERE address LIKE '湖南%'

-- 指定视图中的字段名

CREATE VIEW stu_sc02 (A1,B1,C1) AS

SELECT student.name,score.c_name,score.grade

FROM student INNER JOIN score ON student.id=score.stu_id

-- 查看视图结构

DESC stu_sc02

-- 查看创建视图的语句

SHOW CREATE VIEW stu_sc02

10.18

-- 变量可以看出保存数据的容器

1.系统变量

MySQL官方已经定义好的，我们直接可以使用的

⑴全局变量

服务器层面，必须拥有super权限才能为系统变量赋值

作用域：服务器每次启动为所有全局变量赋初始值，针对所有会话有效，不能跨重启

MySQL启动时给变量也会是容器中装水，这个变量是任何一个连接的会话都可以使用的，这个初始值在重启之后的

失效了，需要在此启动的时候重新赋值

⑵会话变量

服务器为每一个连接的客户端都提供了系统变量

作用域：仅针对当前会话（连接）有效

2.自定义变量

就是我们自己可以根据自己的需求，自己来定义变量

1.全局变量

SHOW GLOBAL VARIABLES;

2.会话变量

SHOW SESSION VARIABLES;

3.查看满足条件的部分系统

SHOW GLOBAL VARIABLES LIKE "%char%"

4.根据全局变量名字查看值

SELECT @@global.character_set_client;

5.查看会话变量

SELECT @@SESSION.会话变量;

6.给变量赋值 

set GLOBAL admin_address='hello';

-- 自定义变量

1.用户变量

声明和初始化

SET @hello01=123456

SET @hello02:=123456

SELECT @hello03:=123456

赋值

SET @hello01='world'

SET @hello02:='world'

SELECT @hello03:='world'

查看变量的值

SELECT @hello03

-- 局部变量

-- 声明

DECLARE uname CHAR DEFAULT '李四';

赋值

SET @username='lisi'

#查看值

SELECT username

-- 存储过程结束符合

delimiter //

-- 创建存储过程

CREATE PROCEDURE get_stu01()

BEGIN

SELECT * FROM student;

END //

-- 创建带参数的存储过程

delimiter //

CREATE PROCEDURE get_str_name(in sname VARCHAR(20))

BEGIN

SELECT * from student WHERE name=sname;

END //

--调用存储过程

delimiter ;

call get_str_name('张老二');

-- 删除存储过程

DROP PROCEDURE get_str_name

-- 查看存储过程的状态

SHOW PROCEDURE STATUS LIKE 'get_stu01';

-- 查看存储过程的定义

SHOW CREATE PROCEDURE get_stu01;

-- 修改存储过程 

存储过程一点定义不能修改，只能修改存储过程的状态，如果有需要，

改变存储过程，则需要删除然后重新创建