mysql入门教学（小白篇）

使用命令行操作

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; -- 恢复默认值

注：本文仅仅是自己的一个学习笔记，目前更新到事务，剩下的内容会持续更新，如果又不对的地方，望各位大佬指出，谢谢😄

陕西科技大学镐京学院攻程狮社团