使用命令行操作
mysql -uroot(账号) -p12345(密码) -- 连接数据库
flush privileges; -- 刷新权限
---------------------------
-- 所有的语句都使用分号;结尾
show databases; -- 查看所有的数据库
use school -- 切换数据库: use 数据库名
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有表的信息
create database westos; -- 创建一个数据库
exit; -- 退出连接
-- 单行注释(sql的本来的注释)
/**/(sql的多行注释)
注:本人使用的软件是SQLyog
操作数据库
创建数据库
create database [if not exists] 库名 //[if not exists]表示如果数据库不存在则创建
删除数据库
drop database [if exists] 库名 //[if exists]表示如果数据库存在则删除
查看数据库
SHOW DATABASES -- 查看创建的所有的数据库
数据库表
创建表
//格式:
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
//示例:
create table `student`(
`id` int(4) not null auto_increment comment '学号',
`pwd` varchar(20) not null unsigned(非负) unqinue(取值为一) default '123456' comment '登陆密码',
`name` varchar(30) not null comment '姓名',
`sex` varchar(2) not null default '男' comment '性别',
primary key (`id`)//定义主键
)
增加表的字段
alter table 表名 add 字段名 字段属性
-- ALTER TABLE students ADD game varchar(20)
修改表的字段
ALTER TABLE 表名 MODIFY 字段名 修改后的字段属性(修改约束)
-- ALTER TABLE students MODIFY game INT(10)
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段属性(字段重命名)
-- ALTER TABLE students CHANGE game game1 varchar(10)
删除表的字段
ALTER TABLE 表名 DROP 字段名
-- ALTER TABLE students DROP game1
删除指定数据
delete from `student` where id = 1;
删除数据库或表
drop database/table 名字在这里插入代码片
设置数据库表的字符集编码
CHARSET=utf8
-
不设置的话,会是mysql默认的字符集编码(不支持中文。)
-
MySQL的默认编码是Latin1,不支持中文
-
在my.ini中配置默认的编码
- character-set-server=utf8
注意
- 所有的创建和删除尽量加上判断,以免报错
- `` 字段名使用这个包裹
- 注释:-- /**/
- sql关键字大小写不敏感,建议小写
- 所有符号全部用英文
视图
CREATE VIEW cs_s(视图名)
AS SELECT `学号`,`姓名`,`籍贯`,`学院`
FROM students(表名) WHERE `学院`="计算机" with check option
-- 修改视图
update 视图名 set `姓名`='a' where `姓名`='b'
-- 添加
insert into 视图名(要插入的数据名) values(对应的数据)
外键
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段,要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)references 引用
CREATE TABLE `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '登陆密码',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` VARCHAR(2) DEFAULT '男' COMMENT '性别',
`date` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`adress` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`Email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid`(`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
key 约束名(`作为外键的字段`),
constraint 约束名 foreign key(`作为外键的字段`) references 表(`字段`)
查询数据
-- 发现重复数据,去重DISTINCT
SELECT DISTINCT `studentno`
FROM `result`
模糊查询之like结合%
-- like结合 %(代表0到任意个字符) _(一个字符)
-- 查询姓赵的同学
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentname` LIKE '赵%'
-- 查询姓赵的同学,后面只有一个字的
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentname` LIKE '赵_'
-- 查询姓赵的同学,后面只有两个字的
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentname` LIKE '赵__'
-- 查询名字中间有佳字的同学 %佳%
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentname` LIKE '%佳%'
模糊查询之in(查询具体的一个或多个值)
-- 查询1000,1002,1003号学员
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (1000,1002,1003)
联表查询
inner join
/*思路:
1.分析需求,分析查询的字段来自哪些表(连接查询)
2.确定使用哪种连接查询(7种)(我感觉这种方式最好理解)
3.确定交叉点(这两个表中哪个数据是相同的)
4.判断的条件:学生表 `studentno` = 成绩表 `studentno`
*/
SELECT `student`.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`
INNER JOIN `result`
WHERE `student`.`studentno`=`result`.`studentno`
排序
-- ASC(升序) DESC(降序) ORDER BY(组,通过哪个字段排序)
ORDER BY `studentresult` DESC
-- 分页(10组数据一页)
LIMIT 0,10
子查询
-- 查询 数据结构-1 的所有考试结果(学号,科目编号,成绩),成绩降序排列
-- 使用子查询(由里及外)
-- 查询所有 数据结构-1 的学生学号
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno`
FROM `subject`
WHERE `subjectname`='数据结构-1'
)
ORDER BY `studentresult` DESC
数据库的字段属性
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充的
- 不足的位数,使用0来填充, – int(3),5===>005
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键~index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null:
- 假设设置为not null,如果不给它赋值,就会报错。
- NULL ,如果不填写值,默认就是null
默认:
- 设置默认的值
- sex,默认值为男,如果不指定该列的值,则会有默认的值
事务(重点)
要么都成功,要么都失败
事务原则:
- 原子性
- 要么都成功,要么都失败
- 一致性
- 事务前后的数据完整性保持一致
- 隔离性
- 事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离
- 隔离所导致的一些问题:
- 脏读:指一个事务读取了另外一个事务未提交的数据;
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同;
- 虚读(幻读):是指在一个事务读取到了别的事务插入的数据,导致前后读取不一致。
- 持久性
- 事务一旦提交则不可逆转,被持久化到数据库中
执行事务原理:
-- mysql是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认的)
-- 手动处理事务
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交:持久化(成功!)
COMMIT
-- 回滚:回到原来的样子(失败!)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
模拟场景
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE 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',10000.00)
-- 模拟转账:事务
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' -- A加500
COMMENT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
注:本文仅仅是自己的一个学习笔记,目前更新到事务,剩下的内容会持续更新,如果又不对的地方,望各位大佬指出,谢谢😄