MySql基础

守护最好的坤坤
64 阅读10分钟

部分理论知识

数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数 4个字节
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
  • decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

字符串

  • char 字符串固定大小 0~255
  • varchar 可变字符串 0~65535 常用的String
  • tinytext 微型文本 2^8 - 1
  • text 文本串 2^16 - 1 保存大文本

注释

MySQL 单行注释

#注释内容
# --注释符后需要加一个空格，注释才能生效。
-- 注释内容

MySQL 多行注释

/*
  第一行注释内容
  第二行注释内容
*/

SQL语句

用户管理

-- 创建用户CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER zhangsan IDENTIFIED BY ' 123456'
-- 修改密码(修改当前用户密码) .
SET PASSWORD = PASSWORD(' 123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zhangsan = PASSWORD(' 123456')
-- 重命名RENAME USER 原来名字 TO 新的名字
RENAME USER zhangsan TO zmk2
-- 用户授权ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO zhangsan
-- 查询权限
SHOW GRANTS FOR zhangsan -- 查看指定用户的权限
SHOW GRANTS FOR root@ localhost
-- ROOT用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@ 'localhost" WITH GRANT OPTION
-- 撒销权限REVOKE哪些权限，在哪个库撤销， 给谁撒销
REVOKE ALL PRIVILEGES ON *.* FROM zhangsan
-- 删除用户
DROP USER zhangsan

操作数据库

创建数据库

# stu1 是数据库名
create database if not exists stu1

删除数据库

drop database if exists stu1

使用数据库

use `stu1`

查看所有数据库

show databases

操作数据表

创建表

create table if not exists `student` (
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码',
	`sex` varchar(2) not null default '男' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`address` varchar(100) comment '地址',
	`email` varchar(20) comment '邮箱',
	primary key(`id`)
)engine=innodb default charset=utf8

语法

create table if not exists `表名` (
    `列名` 列类型 属性 索引 注释,
    `列名` 列类型 属性 索引 注释,
    ........
    `列名` 列类型 属性 索引 注释
)表类型 字符集设置 注释

注意：表名列名用`(1左边的键) ，备注用 ‘ ’， 注意逗号位置 ，中英文

查看建表命令

show create table 表名

QQ截图20201231195944.png

修改表

修改表名

alter table student rename as student1

语法

alter table 旧表名 rename as 新表名

增加字段

alter table student add habit varchar(30)

语法

alter table 表名 add 字段名 列属性

修改表的约束

alter table student modify email varchar(100)

语法

alter table 表名 modify 字段名 属性

修改表的字段名

alter table student change habit habit1 int(10)

语法

alter table 表名 change 旧字段名 新字段名 属性

注意：change用来字段重命名，不能修改字段类型和约束，modify用来修改字段类型和约束，不能用来重命名

删除表中的字段

alter table student drop habit1

语法

alter table 表名 drop 列名

删除表

drop table if exists student

语法

drop table if exists 表名

所有的创建和删除尽量加上判断，以免报错

MySql数据管理

DML语言

insert

insert into `student` (`name`) values ('张三')
insert into `student` (`name`,`sex`) values ('李四','女')

语法

insert into `student` (`字段名1`,`字段名2`) values ('值1','值2')

插入多个字段

insert into `student` (`name`) values ('李四1'),('李四2')

update

修改所有的值，不建议使用，找死行为

update `student` set `name`='张三'

修改多个值

update `student` set `name`='张三',`address`='宇宙国'

带上条件查询

update `student` set `name`='李四',`address`='漂亮国' where `id`='1'

语法

update `表名` set `字段名`='新值',`字段名`='新值' where 条件
  • 列名最好带上 ``
  • 条件，筛选的条件，如果没有指定，会修改所有的值
  • value，是一个具体的值，也可以是一个变量
  • 多个设置的属性之间，可以用英文逗号隔开

delete

-- 删除所有的值，不建议使用，找死行为
delete from `student`

delete from `student` where `id`='1'

语法

delete from `表名` where 条件

truncate命令

作用：完全清空一个数据库，表的结构和索引约束不会变

--清空表
truncate `表名`

truncate 和 delete 的区别

  • 相同点：都能删除数据，都不会删除表结构
  • 不同点：
    • truncate 重新设置 自增列 计数器会归零
    • truncate 不会影响事务
  • InnoDB 自增列会从1开始（存在内存当中，断电即失）
  • MYISAM 继续在上一个自增量开始（存在文件中，不会丢失）

DQL查询数据（最重点）

select 语法

注意顺序 QQ截图20210101143217.png

查询全部

select * from `表名`

查询指定字段

select 列名,列名 from `表名`

别名

给结果起一个名字 AS 可以给表起别名，也可以给字段起别名

select name as '学生姓名',pwd as '密码' from `student`

语法

select 字段 as '列名',字段 as '别名' from `表名`

字符拼接函数concat

select concat('名字：', name) as '学生姓名',pwd as '密码' from `student`

去重distinct

去除重复数据，只显示一条

select distinct name from `student`

语法

select distinct 字段 from `表名`

数据库的列（表达式）

select version()  -- 查询系统版本
select 100*8-1 as '结果'  -- 用来计算
select @@auto_increment_increment  -- 查询自增的步长
select `id`+1 from student   -- 查询结果加一

where条件子句

select `id` from student where id=3
select `id` from student where id>=10 && id<=100
select `id` from student where id>=10 and id<=100
select `id` from student where id between 10 and 100
select `id` from student where id!=10
select `id` from student where not id>10

模糊查询（重点）

运算符语法描述
IS NULLa is null如果操作符为NUII,结果为真
IS NOT NULLa is not null如果操作符不为null,结果为真
BETWEENa between b and C若a在b和c之间，则结果为真
Likea like bSQL匹配，如果a匹配b，则结果为真
Ina in (a1,a2,3....)假设a在a1,或者a2..... 其中的某一个值中， 结果为真
-- like结合 %（代表0到任意个字符），_（代表一个字符）
select `字段` from 表名 where 字段 like '%关键字%' -- 含有关键字的记录
select `字段` from 表名 where 字段 like '关键字%'  -- 第一个字为关键字的记录
select `字段` from 表名 where 字段 like '%关键字'  -- 最后一个字为关键字的记录

-- _可以有多个，一个_代表一个字符
select `字段` from 表名 where 字段 like '关键字_'  -- 关键字后面只有一个值的记录，且第一个字为关键字
select `字段` from 表名 where 字段 like '%关键字_' -- 关键字后面只有一个值的记录

select `name` from student where name in ('张三','李四')

select `字段名` from 表名 where 字段名 in ('关键字','关键字','关键字')

连表查询

2020062918072196.png

类型描述
INNER JOIN内连接查询，会把关联表中，有关联关系的所有记录查询出来
LEFT JOIN左连接查询，它会以左表为主表，并且会将左表中的所有记录查询出来，如果右表没有与之对应关联数据，则用NULL填充指定字段
RIGHT JOIN右连接查询，它会以右表为主表，并且会将右表中的所有记录都查询出来，如果左表中没有与之对应的关联数据，则用NULL填充左表中的指定字段
FULL OUTER JOIN全连接查询，会将关联表中的所有数据都查询出来

使用mysql实现以上七种join查询

部门表

SELECT * FROM tbl_dept;

在这里插入图片描述 员工表

SELECT * FROM tbl_emp;

在这里插入图片描述

1.3.1.1 内连接查询

#内连接查询 查询两张表都有的记录
SELECT * FROM tbl_dept dept
INNER JOIN tbl_emp emp
ON dept.id=emp.id;

在这里插入图片描述

1.3.1.2 左连接查询

#左连接查询  只查询左表中有的记录
SELECT * FROM tbl_dept dept
LEFT JOIN tbl_emp emp
ON dept.id=emp.deptId;

在这里插入图片描述

1.3.1.3 右连接查询

#右连接查询
SELECT * FROM tbl_dept dept
RIGHT JOIN tbl_emp emp
ON dept.id=emp.deptId;

在这里插入图片描述

1.3.1.4 只查询左表中独有的数据

#查询dept表中独有的记录
SELECT * FROM tbl_dept dept
LEFT JOIN tbl_emp emp
ON dept.id=emp.deptId
WHERE emp.deptId IS NULL;

在这里插入图片描述

1.3.1.5 只查询右表中独有的数据

#查询emp表中独有的记录
SELECT * FROM tbl_dept dept
RIGHT JOIN tbl_emp emp
ON dept.id=emp.deptId
WHERE dept.id IS NULL;

在这里插入图片描述

1.3.1.6 全连接查询

注意：因为mysql不支持full outer join所以通过union实现全连接查询

#把两张表中的记录都查询出来 并进行去重 因为mysql不支持full outer join所以通过union实现全连接查询
SELECT * FROM tbl_dept dept
LEFT JOIN tbl_emp emp
ON dept.id=emp.deptId
UNION
SELECT * FROM tbl_dept dept
RIGHT JOIN tbl_emp emp
ON dept.id=emp.deptId;

在这里插入图片描述

1.3.1.7 查询两张表中各自独有的数据

#查询两张表中所有没有关联关系的记录
SELECT * FROM tbl_dept dept
LEFT JOIN tbl_emp emp
ON dept.id=emp.deptId
WHERE emp.deptId IS NULL
UNION
SELECT * FROM tbl_dept dept
RIGHT JOIN tbl_emp emp
ON dept.id=emp.deptId
WHERE dept.id IS NULL;

在这里插入图片描述

分页和排序

-- 排序:升序ASC，降序DESC
-- ORDER BY通过那个字段排序，怎么排
-- 查询的结果根据成绩降序排序

SELECT s.studentNo,StudentName,SubjectName, StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY studentResju1t ASC

-- limit 起始值，页面的大小
-- 语法:1imit(查询起始下标，pagesize)

SELECT s.StudentNo,StudentName,subjectName,StudentResult
FROM student S
INNER JOIN result r
ON r.SubjectNo = sub.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult ASC
LIMIT 5,5

-- (n-1) * pagesize，pagesize
-- [pagesize: 页面大小]
-- [(n-1)* pagesize:起始值]
-- [n:当前页]
-- [数据总数/页面大小=总页数]

子查询

-- 方式一: 使用连接查询
SELECT
	`StudentNo`,
	r.`SubjectNo`,
	`StudentResult`
FROM
	`result` r
	INNER JOIN `subject` sub ON r.SubjectNo = sub.SubjectNo 
WHERE
	SubjectName = '数据库结构-1' 
ORDER BY
	StudentResult DESC

-- 方式二:使用 子查询(由里及外)
SELECT
	`StudentNo`,
	`SubjectNo`,
	`StudentResult` 
FROM
	`result` 
WHERE
	SubjectNo = ( SELECT SubjectNo FROM SUBJECT WHERE SubjectName = '数据库结构-1' ) 
ORDER BY
	StudentResult DESC
	
-- 改进	
SELECT
	StudentNo,
	StudentName 
FROM
	student 
WHERE
	StudentNo IN (
        SELECT
            StudentNo 
        FROM
            result 
        WHERE
        	StudentResult > 80 
        AND SubjectNo = ( SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2' )
    )

MySQL常用函数

数学运算

SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0返回0 负数返回-1，正数返回1

字符串函数

SELECT CHAR_LENGTH('即使再小的帆也能远航')  -- 字符串长度
SELECT CONCAT('我', '爱','你们') -- 拼接字符串
SELECT INSERT ('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER ('ZMK') -- 小写字母
SELECT UPPER( 'zmk') -- 大写字母
SELECT INSTR (' zmk','m') -- 返回第一次出现的子串的索引
SELECT REPLACE('爸爸说坚持就能成功','坚持','努力')  -- 替换出现的指定字符串
SELECT SUBSTR('爸爸说坚持就能成功',4,6) -- 返回指定的子字符串(源字符串,截取的位置，截取的长度)
SELECT REVERSE('清晨我上马') -- 反转

时间和日期函数(记住)

SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期.
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR (NOW())
SELECT MONTH (NOW())
SELECT DAY (NOW())
SELECT HOUR(NOW())
SELECT MINUTE (NOW())
SELECT SECOND (NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

聚合函数（重点）

SELECT COUNT ('BornDate') FROM student;-- Count(字段)，会忽略所有的null 值
SELECT COUNT (*) FROM student; -- Count (*)，不会忽略null 值，本质计算行数
SELECT COUNT(1) FROM result; -- Count (1)，不会忽略忽略所有的null 值本质计算行数
SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result

扩展 MD5加密

CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testmd5 VALUES (1, 'zhangsan ','123456'),(2,'lisi', '123456'),(3, 'wangwu', '123456')

-- 加密
UPDATE testmd5 SET pwd=MD5 (pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5 (pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4, 'xiaoming' ,MD5('123456 '))

-- 如何校验:将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` ='xiaoming' AND pwd=MD5('123456')
avatar
文章
阅读
粉丝