mysql基础
-- 创键数据库test(如果不存在)
CREATE DATABASE IF NOT EXISTS test
-- 删除数据库test(如果存在)
DROP DATABASE IF EXISTS test
-- 使用该数据库
USE school
-- 表名或字段名是特殊字符用``包含(tab键上🗡)
USE `use`
-- 查看所有数据库
SHOW DATABASES
-- 建表(auto_increment自增) PRIMARY KEY(设置主键)
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
ALTER TABLE student RENAME AS teacher -- 修改表名
ALTER TABLE teacher ADD age INT(11) -- 增加表的字段
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher CHANGE age age1 INT(1) -- 字段重命名
ALTER TABLE teacher DROP age1 -- 删除字段
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY ( 作为外键的列) REFERENCES 哪个表(哪个字段)
INSERT INTO `grade` (`gradename`) VALUE('大四') -- insert into 表名 字段 值
INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES('张三','aaaaaaa','男'),('李四','aaaaaaa','男'),('王五','aaaaaaa','男')
UPDATE `student` SET `name`='r7ftf' WHERE id=1 -- 修改student表id=1的名字为r7ftf(不指定条件的情况下会改动这张表的所有名字)
UPDATE `student` SET `name`='r7ftf',`email`='3050752968@qq.com' WHERE id=2
UPDATE `student` SET `name`='r7ftf1',`email`='3050752968@qq.com' WHERE id BETWEEN 2 AND 5 -- between...and... id在2~5 [2,5]
UPDATE `student` SET `birthday`=CURRENT_TIME -- 为birthday设置当前时间(变量)
DELETE FROM `student` WHERE id=1 -- 删除id=1的数据
TRUNCATE TABLE `student` -- 清空一张表自增会归零
DELETE FROM `student` -- 清空一张表不会影响自增 InnoDB(重启数据库 自增列会从1开始(存在内存，断电即失)) MyISAM(继续从上次自增量开始(存在文件，不会丢失))
SELECT * FROM student1 -- 查询全部学生信息 select 字段 from 表
SELECT `name`,`address` FROM student1 -- 查询指定字段
SELECT `name` AS 姓名,`address` AS 地址 FROM student1 AS s -- 给查询字段取别名(可以给字段取别名也可以给表取别名)
SELECT CONCAT('姓名：',`name`) AS 新名字 FROM student1 -- 字符拼接函数
SELECT DISTINCT `subject` FROM result -- 查询去除重复数据后的数据
SELECT VERSION() -- 查询系统版本
SELECT 100*3-1 AS 计算结果 -- 用来计算
SELECT @@auto_increment_increment -- 查询自增的步长
SELECT `score`+1 FROM result -- 加1分查看
SELECT * FROM result WHERE score>=80 AND NOT score=85 -- 查询成绩大于80且不为85
SELECT * FROM result WHERE `score` BETWEEN 80 AND 85 -- 查询成绩80~85
SELECT * FROM `student1` WHERE `birthdate` LIKE '199%' -- %(代表0到任意个字符)
SELECT * FROM `student1` WHERE `name` LIKE '张_' -- _(代表一个字符)
SELECT * FROM `student1` WHERE `id` IN (1,3,5) -- 查询在in范围内的数据
-- join on 连接查询
-- where 等值查询
-- 联表查询join on
SELECT s.name AS 姓名, c.score AS 成绩
FROM students s
JOIN scores c ON c.id = s.id;
-- Inner Join
SELECT s.name AS 姓名, c.score AS 成绩
FROM students s
INNER JOIN scores c ON c.id = s.id;
-- Right Join
SELECT s.name AS 姓名, c.score AS 成绩
FROM students s
RIGHT JOIN scores c ON c.id = s.id;
-- Left Join
SELECT s.name AS 姓名, c.score AS 成绩
FROM students s
LEFT JOIN scores c ON c.id = s.id;
SELECT s.`name`,c.score,s.id
FROM students AS s,scores AS c
WHERE s.id=c.id
ORDER BY score DESC -- 排序(desc 降序 asc 升序)
LIMIT 0,7 -- 分页(limit(初始下标,每页数))
SELECT s.`name`,c.score,s.id
FROM students AS s,scores AS c
WHERE score IN (SELECT score FROM scores WHERE score=85)
SELECT ABS(-9) -- 取绝对值
SELECT CEILING(9.3) -- 向上取整
SELECT FLOOR(9.6) -- 向下取整
SELECT RAND() -- 随机数(0~1)
SELECT SIGN(-10) -- 返回数的符号
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串长度
SELECT CONCAT('m','y','s','q','l') -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱') -- 查询 从某个位置替换某个长度
SELECT LOWER('R7ftf') -- 小写
SELECT UPPER('R7ftf') -- 大写
SELECT INSTR('r7ftf7','7') -- 返回第一次出现'7'的子串索引
SELECT REPLACE('超级电风扇','电','大') -- 替换指定的字符串
SELECT SUBSTR('超级电风扇',2,3) -- 返回指定子字符串(源字符串,截取的位置,截取的长度)
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(score) FROM scores -- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM scores -- count(*) 不会忽略null值
SELECT COUNT(1) FROM scores -- count(1) 不会忽略null值
SELECT SUM(score) AS 总和 FROM scores -- sum(字段) 求和
SELECT AVG(score) AS 平均分 FROM scores -- avg(字段) 取平均值
SELECT MAX(score) AS 最高分 FROM scores -- max(字段) 求最大值
SELECT MIN(score) AS 最低分 FROM scores -- min(字段) 求最小值
SELECT `name`,AVG(score) AS 平均分,MAX(score) AS 最高分
FROM students s
INNER JOIN scores c
ON s.id=c.id
GROUP BY `name` -- 通过什么字段来分组
HAVING 平均分>85 -- 过滤条件
-- ======================测试MD5加密===========================
CREATE TABLE testmad5(
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 testmad5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','654321')
-- 加密
UPDATE testmad5 SET pwd=MD5(pwd) WHERE id=1
SELECT * FROM testmad5
UPDATE testmad5 SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmad5 VALUES(5,'阿',MD5('123456'))
-- 查询加密后对应的数据
SELECT * FROM testmad5 WHERE `name`='张三' AND pwd=MD5('123456')
-- ==================事务===================
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启(默认) */
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到提交前状态（失败！）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
-- 一组事务(转账模拟)
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 创建shop数据库
USE shop -- 使用shop数据库
-- 建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),('B',100000.00)
-- 查表
SELECT * FROM account
-- 模拟转载：事务
SET autocommit=0;-- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE account SET money=money-500 WHERE `name`='A' -- A减500
UPDATE account SET money=money+500 WHERE `name`='B' -- B减500
COMMIT;-- 提交事务,就被持久化了！回滚没有用
ROLLBACK;-- 回滚
SET autocommit=1; -- 恢复默认值
DESC account -- 查看表结构
ALTER TABLE account MODIFY COLUMN `name` VARCHAR(200); -- 修改名为 "account" 的表中的 "name" 列的数据类型，修改为 VARCHAR(200)。
ALTER TABLE account RENAME COLUMN `name` TO o_name; -- 将名为 "name" 的列重命名为 "o_name"
ALTER TABLE account ADD COLUMN last_login DATETIME; -- 向 "account" 表中添加一个名为 "last_login" 的 DATETIME 类型列
ALTER TABLE account DROP COLUMN last_login;-- 从 "account" 表中删除名为 "last_login" 的列
-- 创建表
CREATE TABLE test(
`id` INT(3) AUTO_INCREMENT,
`name` VARCHAR(20),
`sex` VARCHAR(6),
PRIMARY KEY(id)
)
DESC test -- 查看表结构
DROP TABLE test -- 删除表
INSERT INTO test (`name`,`sex`)VALUES('张三','男'),('阿甘','男'),('小翠','女') -- 添加数据
SELECT * FROM test -- 查表
ALTER TABLE test MODIFY sex VARCHAR(10) DEFAULT '女' -- 将表 "test" 中的 "sex" 列的数据类型修改为 VARCHAR(10)，并设置默认值为 '女'。
INSERT INTO test (`name`) VALUES ('小易') -- 添加表数据
UPDATE test SET sex='男' WHERE id=3 -- 修改表数据
DELETE FROM test WHERE id=1 -- 删除表数据
-- regexp .(任意一个字符) ^(开头) $(结尾) [abc](其中任意一个字符) [a-z](范围内的任意一个字符) A|B(A或者B)
SELECT * FROM test WHERE `name` REGEXP '^小.$'
ALTER TABLE test ADD COLUMN last_login DATETIME;
-- 在 SQL 中，要检索具有 NULL 值的列，您应该使用 "IS NULL" 条件，而不是使用等号 "="。
-- NULL 值在 SQL 中表示缺少值或未知值，因此在比较时需要使用 "IS NULL" 或 "IS NOT NULL" 条件。使用等号 "=" 无法判断列是否为 NULL，因为 NULL 与任何值（包括它自身）进行比较的结果都是未知的。
SELECT * FROM test WHERE last_login = NULL -- 错误
SELECT * FROM test WHERE last_login IS NULL -- 正确
SELECT * FROM test WHERE last_login <=> NULL -- 正确
SELECT * FROM test WHERE `name`= '' -- 查找空字符串
SELECT * FROM test ORDER BY id DESC,last_login ASC
SELECT * FROM test ORDER BY 1 ASC -- 第一列字段升序排序
SELECT COUNT(*) FROM test -- COUNT(*) 是一个聚合函数，用于计算指定表中的行数。
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id<=3
SELECT DISTINCT sex FROM test -- 去重
-- union 合并查询结果 UNION 操作中，要求两个 SELECT 语句具有相同的列数，并且对应的列具有相同的数据类型。
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id<=3
UNION -- 默认去除重复
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id>3
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id<=3
UNION ALL -- 不去除重复
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id>3
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id<=3
intersect -- 交集
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id>=3
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id<=3
except -- 差集
SELECT id,`name`,COUNT(sex) FROM test GROUP BY id HAVING id>=3
CREATE TABLE new_test SELECT * FROM test -- 创建一个名为 "new_test" 的新表，并从现有的 "test" 表中复制所有数据到新表中
SELECT * FROM new_test
INSERT INTO new_test SELECT * FROM test
SELECT EXISTS(SELECT * FROM new_test WHERE sex='女')-- 如果 "new_test" 表中存在满足条件的记录（sex='女'），则返回 1（真），否则返回 0（假）。
-- inner join 内连接只返回两个表中都有的数据
-- left join 左连接就是返回左表中所有的数据和右表中匹配的数据 右表中没有的数据用null填充
-- right join 右连接就是返回右表中所有的数据和左表中匹配的数据 左表中没有的数据用null填充
SELECT * FROM new_test
INNER JOIN account
ON new_test.id=account.id
SELECT * FROM new_test
LEFT JOIN account
ON new_test.id=account.id
SELECT * FROM new_test
RIGHT JOIN account
ON new_test.id=account.id
-- 查询使用了逗号（,）来表示内连接。然而，为了提高查询的可读性和可维护性，推荐使用显式的 INNER JOIN 语法。
SELECT * FROM new_test,account
WHERE new_test.id=account.id
-- 索引的分类
-- 主键索引(primary key) 唯一的标识,主键不可重复,只能有一个列作为主键
-- 唯一索引(unique key) 避免出现重复的列,唯一索引可以重复,多个列都可以标识位 唯一索引
-- 常规索引 (key/index) 默认的，key,index关键字来设置
-- 全文索引 (fulltext) 快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM test
-- 增加一个全文索引 索引名（列名）
ALTER TABLE test ADD FULLTEXT INDEX `name`(`name`)
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM test -- 非全文索引
EXPLAIN SELECT * FROM test WHERE MATCH(`name`)AGAINST('小')
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男,1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写的标志
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user (`name`, `email`, `phone`, `gender`, `password`, `age`)
VALUES (
CONCAT('用户', i),
CONCAT(i, '@qq.com'),
CONCAT('18', FLOOR(RAND() * 9999999999)),
FLOOR(RAND() * 2),
UUID(),
FLOOR(RAND() * 100)
);
SET i = i + 1;
END WHILE;
RETURN i;
END $$
DELIMITER ;
SELECT mock_data()-- 执行函数
SELECT * FROM app_user WHERE `name`='用户99999' -- 用时 1.029 sec
SELECT * FROM app_user -- 用时0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999'
-- id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app_user WHERE `name`='用户99999' -- 用时0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999'
SHOW INDEX FROM app_user
-- 索引在小数据量的时候用处不大，但在大数据量的时候，区别十分明显
-- 索引原则
-- 索引不是越多越好
-- 不要对经常变动的数据加索引
-- 小数据量的表不需要加索引
-- 索引一般加在常用来查询的字段上
-- 索引的数据结构
-- hash类型的索引
-- btree:innodb的默认数据结构
-- 解释：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
-- 创建用户
CREATE USER r7ftf IDENTIFIED BY '123456'
-- 修改密码<修改当前用户密码>
SET PASSWORD = PASSWORD('123456')
-- 修改密码<修改指定用户密码>
SET PASSWORD FOR r7ftf = PASSWORD('123456')
-- 重命名
RENAME USER r7ftf TO r7ftf2
-- 用户授权 all privileges 除了给其他用户授权有全部的权限 库.表
GRANT ALL PRIVILEGES ON *.* TO r7ftf2
-- 查询权限
SHOW GRANTS FOR r7ftf2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- root用户多个WITH GRANT OPTION(用于给其他用户赋予权限)
-- 撤销权限 revoke 哪些权限
REVOKE ALL PRIVILEGES ON *.* FROM r7ftf2
-- 删除用户
DROP USER r7ftf2
-- mysql 备份
-- 为什么要备份:
-- 保证重要的数据不丢失
-- 数据转移
-- mysql数据库备份的方式
-- 直接拷贝物理文件(data)
-- 在sqlyog这种可视化工具中手动导出
-- 使用命令行导出mysqldump命令行使用
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
-- mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
-- 导入数据
-- 登录的情况下,切换到指定的数据库 source 备份文件
-- source d:/a.sql
-- 没登陆时 mysql -u用户名 -p密码 库名<备份文件
-- 为什么需要设计
-- 当数据库比较复杂时就需要设计了
-- 糟糕的数据库设计
-- 数据冗余,浪费空间
-- 数据库插入和删除都会麻烦,异常[尽量屏蔽使用物理外键]
-- 程序的性能差
-- 良好的数据库设计
-- 节省内存空间
-- 保证数据库的完整性
-- 方便我们开发
-- 关于数据库的设计
-- 分析需求：分析业务和需要处理的数据库的要求
-- 概要设计：设计关系图E-R图
-- 三大范式
-- 第一范式(1NF) 原子性：保证每一列不可再分(防止出现歧义)
-- 第二范式(2NF) 满足第一范式的前提下,每张表只描述一件事情
-- 第三范式(3NF) 满足第一范式和第二范式的前提下,确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
-- 规范性 和 性能的问题
-- 关联查询的表最好不超过三张表
-- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
-- 在规范性能的问题的时候,需要适当的考虑一下 规范性！
-- 故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
-- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
-- JDBC 数据库驱动
-- SUN 公司为了简化 开发人员的(对数据库的统一)操作,提供了一个(JAVA操作数据库的)规范,俗称JDBC
-- 不同数据库的规范由具体的厂商去做对于开发人员只需要掌握JDBC接口的操作即可
-- java.sql javax.sql 还需导入数据库驱动包 mysql-connector-java-版本号.jar
浙公网安备 33010602011771号