MySQL学习笔记第二天

MySQL学习笔记第二天

1.修改删除表

修改表

ALTER TABLE student RENAME teacher; 修改表名

ALTER TABLE teacher ADD address VARCHAR(20); 给表添加字段

修改表的字段（重命名 修改约束）

ALTER TABLE teacher CHANGE age age1 INT(3); //重命名

ALTER TABLE teacher MODIFY address VARCHAR(30); //修改约束

删除表

ALTER TABLE teacher DROP age1; 删除表的字段

DROP TABLE IF EXISTS teacher; //删除表

注意：

• 所有的创建删除操作尽量都增加判断操作
• 所有的字段名用``包裹
• 注释 –
• sql关子健大小写不敏感，建议写小写
• 所有的符号用英文

2.MySQL数据管理

2.1外键

创建表 添加外键方式1：

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
SHOW CREATE TABLE `student`
DROP TABLE IF EXISTS `student`
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `gradeid` INT(10) NOT NULL  COMMENT'年级id',
  `birth` DATETIME NOT NULL COMMENT '出生日期',
  `address` VARCHAR(30) NOT NULL COMMENT '家庭住址',
  `email` VARCHAR(20) DEFAULT NULL COMMENT '邮件',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `Fk_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 

方式二：

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`) 

以上操作都是物理外键 ，数据库级别的外键，我们不建议使用，避免数据库过多造成困扰

最佳实现

• 数据库就是单纯的表 ，只用来存储数据 ，只有行（数据）和列（字段）
• 我们想使用多张表，想使用外键，就用程序来实现

2.2 DML语言

数据操作语言

• insert
• update
• delete

2.3添加（insert）

-- insert into 表名([字段名1，字段名2]) values('值1','值2','值3')
INSERT INTO `grade`(`gradename`) VALUES ('大一')
--由于主键自增，我们不需要插入gradeid的值
INSERT INTO `grade` VALUES ('大一')  --插入失败（如果不写表的字段，他就会一一匹配）
--一个字段插入多个值
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大三'),('大四')

---------------------------------------
INSERT INTO `student`(`id`,`name`,`password`,`sex`) 
 VALUES('20192788','cjp','jiu177...','男') 

2.4修改（update）

语法：update 表名 set 列=值 where（条件）

--修改一个属性
UPDATE `student` SET `sex`='女' WHERE `id`='20192788'
UPDATE `student` SET `name`='cyz'WHERE `id`='20192788'
--修改多个属性
UPDATE `student` SET
   `name`='曹久平',`sex`='男',`gradeid`='3',`birth`='2000-03-11',`email`='1770364681@qq.com'
   WHERE `id`='20192788'

• 条件：where子句 运算符 等于 大于 小于 在某个区间内修改

<>:不等于

BETWEEN…AND…:闭合区间[]

AND: 多个条件组合

OR：多个条件的一个

2.5删除（delete）

delete from 表名 where 条件

DELETE FROM `student` WHERE `password`='dsda

TRUNCATE命令

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

TRUNCATE `student`

delete 和truncate的区别

相同点：都可以删数据，都不会删除表结构

不同点：

• TRUNCATE 重新设置自增列，计数器会归零
• TRUNCATE不会影响事务

了解：delete删除现象 删除后重启数据库

当引擎为:

• INNODB :因为数据存在内存中 断电即失 所以自增也会归零
• MYISAM：数据存储在文件中，断电也没事，所以自增会继续从上一次开始

3.DQL查询语言（最重点）

（Data Query LANGUAGE）数据查询语言

• 所有的查询操作都用它
• 简单地查询，复杂的查询都能用它做
• 数据库中最核心的语言，最重要的语言
• 使用频率最高

3.1指定查询字段

SELECT * FROM `student`  --查询全部学生
SELECT `id`,`name` FROM `student`  --查询指定字段
-------给字段名换名
SELECT `id` AS 学号,`name` AS 姓名 FROM `student` AS s

--函数  Concat 拼接
SELECT CONCAT(‘姓名：’,name) AS 新名字 FROM `student`

语法 ：select 字段名。。。from 表名

有的时候列名字不是见名知意就起别名

• 去重 distinct

  SELECT DISTINCT id from student

• 数据库的列（表达式）

  SELECT VERSION(); --函数
  SELECT 100*3-4 AS ‘计算结果’; --计算
  SELECT @@auto_increment_increment AS 步长; --查询自增步长（变量）
  –查询所有考试成绩加一分
  SELECT id,name,score+1 AS ‘提分后’ FROM score;

数据库中的表达式；文本值，列，null，函数，计算表达式，系统变量。。。

select 表达式 from 表

3.2where条件子句

作用：检索数据中心符号和条件的值

搜索的条件有一个或多个条件组成，结果都是一个布尔值

逻辑运算符

and A and B

or A or B

Not Not A

--查询所有学生的成绩
   SELECT `id`,`socre` FROM score
   
   --查询成绩在九十五分以上的 100分以下的，使用WHERE
   SELECT `id`,`socre` FROM score 
   WHERE score>=95 AND score<=100 

   --使用WHERE 和BETWEEN。。。AND
   SELECT `id`,`socre` FROM score 
   WHERE score BETWEEN 95 AND 100
   
   --查询学号除了20192788外的所有学生成绩
   SELECT `id`,`socre` FROM score 
   WHERE id!=20192788;   --WHERE  NOT id=20192788

模糊查询 :比较运算符

IS NULL a is null 如果操作符为空 结果为真

IS NOT NULL a is not null 如果操作符不为空 结果为真

BETWEEN…AND a between b and c 如果a在b和c之间 结果为真

LIKE a like b SQL匹配，若a能匹配到b，结果为真

IN a in（a1,a2,a3…） 假设a在a1,a2,a3…里面，结果为真

  -------------LIKE的使用
   --查询姓刘的同学
   --LIKE结合 %(代表任意个字符)  _(代表一个字符)
   SELECT `id`,`name` FROM `student`
   WHERE `name` LIKE '刘%'；
   
   --查询姓刘的两个字的同学
   SELECT `id`,`name` FROM `student`
   WHERE `name` LIKE '刘_';--三个字的就是'刘__'
   
   --查询名字里面有‘平’的同学
    SELECT `id`,`name` FROM `student`
    WHERE `name` LIKE '%平%';
    
  ----------------IN（具体的一个值或多个值）的使用
  --查询学号为'1001','1002','1003'的同学
  SELECT `id`,`name` FROM `student`
  WHERE `id` IN(1001,1002,1003);
  
  --查询在北京的学生
  SELECT `id`,`name` FROM `student`
  WHERE `address` IN（'北京'）;
  
  
  ---------------------NULL ,NOT NULL
  --查询地址为空的学生
  SELECT `id`,`name` FROM `student`
  WHERE `address`=''OR `address`IS nul
  
  --查询有出生日期的同学
  SELECT `id`,`name` FROM `student`
  WHERE `birth` IS NOT NULL;

3.3联表查询

JOIN 对比

  --===============联表查询JOIN==================
  --查询参加了考试的同学的学号、姓名、科目编号、、成绩
  SELECT * FROM `student`
  SELECT * FROM `score`
  
  
 /*思路
 1.分析需求  分析查询的数据来自哪些表 （连接查询） 确定使用那种连接查询
 2.确定交叉点（这两个表中哪个字段是相同的）
 判断的条件 ： student 表中的id = score表中的id
 */
  --INNER JOIN
  SELECT s.id,stName,subId,score
  FROM student AS s
  INNER JOIN score AS sc
  ON s.id=sc.id
  
  
  --RIGHT JOIN
  SELECT s.id,stName,subId,score
  FROM student AS s
  RIGHT JOIN score AS sc
  ON s.id=sc.id
  
  
  --RIGHT JOIN
  SELECT s.id,stName,subId,score
  FROM student AS s
  LEFT JOIN score AS sc
  ON s.id=sc.id

区别

• inner join 如果表中至少有一个值匹配，就返回行
• right join 会从右表中返回值 ，即使左表中没有匹配
• left join 会从左表中返回值 ，即使右边中没有匹配

JOIN（连接的表）…ON（判断条件） 连接查询

WHERE 等值查询 两种结果一样

--查询参加考了考试的学生的学号 姓名 科目名 分数  
SELECT `s.id`,`name`,`subName`,`score`
FROM student s
RIGHT JOIN score sc
ON s.id = sc.id
INNER JOIN `subject` AS su
ON su.id = sc.id