由浅入深学习Mysql(二)

eg.1修改删除表

利用上一个博客中所创建的westos库下的student表来进行操作,操作过程为 **增加表的字段 **-删除表的字段 - 修改表的字段名-修改表的字段属性-修改表名

代码：

USE `WESTOS`  -- 必须先定位wstos库不然会报错 
ALTER TABLE student ADD age VARCHAR(3)  -- 增加表的字段
ALTER TABLE student ADD grade VARCHAR(3)

/*代码必须一行一行执行,当我将下面两行代码一起执行的时候会报错*/
ALTER TABLE student ADD grade1 VARCHAR(3)   
ALTER TABLE student ADD age1 VARCHAR(3)

ALTER TABLE student DROP age1 -- 删除表的一个字段
/*下面一行代码批量删除了两个字段,注意要加,*/
ALTER TABLE student      
DROP age,
DROP grade

ALTER TABLE student CHANGE grade1 age INT(1)  -- 修改表的字段名
ALTER TABLE student MODIFY age VARCHAR(3)  -- 修改表的字段属性
ALTER TABLE student RENAME student1 -- 修改表名

注意事项

1. 在做修改删除表之前先定位wstos库不然会报错
2. 在增加表的字段的时候,代码必须一行一行执行,当我将两行代码一起执行的时候会报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ALTER TABLE student ADD age1 VARCHAR(3)’ at line 2
3. 批量删除字段时注意每个drop 字段名 后面要加 , 最后一个一个drop不用加

eg.2DML语言来实现对数据的添加、修改、删除

数据库的意义就在于用文件的形式来进行数据存储,可以利用各种代码来队数据进行操作管理。

1.对上段代码中的student1表添加数据

代码：

ALTER TABLE student1 RENAME student  -- 为了方便操作先把表名改回来
DESC student1 -- 先查看表一共有多少表头一共有id name sex email 四个
INSERT INTO `student`(`name`) VALUES ('王五') -- 增加了一个王五
INSERT INTO `student`(`name`,email) 
VALUES ('王六','123456'),('王七','123456'),('王八','123456'),('王九','123456')
-- 连续增加了四个人
INSERT INTO `student` VALUES('6','王十','女','654321') -- 字段可以省略,但是就要一一对应,相当于默认所有字段都写了,都要填

语法：

insert into `table` (`字段名1`,`字段名2`,`字段名3`...)values('值1','值2','值3'...)
需要一一对应,如果省略了字段名则默认为所有的字段都有一一对应的值

2.对上段代码中的student1表修改数据

代码：

UPDATE `student` SET `name`='李一'  -- 不带条件的修改会直接修改表内该表名下的所有数据！！ 
update `student` set `name`='李二' where sex='男' -- 条件会精准的修改所有符合条件的数据
/*多个条件精确定位修改数据*/
UPDATE `student` SET `name`='李六',`sex`='女' WHERE id BETWEEN 3 AND 5 -- 有between..and,id后面不能加=,否则会报错
UPDATE `student` SET `name`='李五',`sex`='女' WHERE id>=1&&id<=4&&sex='男'
UPDATE `student` SET `name`='李七'WHERE sex!='男'&&id BETWEEN 3 AND 5

修改流程

UPDATE `student` SET `name`='李七'WHERE sex!='男'&&id BETWEEN 3 AND 5

先看where 条件筛选出sex不为男的id在3.4.5之间的三行,再将其name修改为李七

3.对上段代码中的student1表删除数据

代码

delete from `student` where id=2 -- 利用条件定位删除指定数据
truncate `student`  -- 直接清空了表内所有数据,且id归零
delete from `student` -- 也可以删除表内数据但是不建议这样使用
/*同样都可以删除数据且不会删除表结构,为什么不建议使用delet
truncate 会重新设置自增列,使上面表的id计数器归零
truncate 不会影响事务
*/

eg.3DQL查询数据

DQL（Data Query LANGUAGE）:数据查询语言

1.查询表、表的指定字段内容，给不易阅读的字段起别名

代码：

SELECT * FROM student  -- 查询表的所有内容
SELECT `id`,`name` FROM student  -- 查询表的指定字段
SELECT `id` AS 学号,`name` AS 名字 FROM student AS 名单 -- 有时候名字不容易辨认可以给表的字段,表起别名
SELECT CONCAT('姓名：',NAME)AS 新名字 FROM student -- CONCAT('A:'B)：可以将A:B这样拼接起来的函数

2.查重，函数，表达式

代码：

SELECT `name`FROM student    -- 查询所有的学生姓名 如图1
SELECT DISTINCT `name` FROM student -- 将多余的学生姓名去掉,重复的姓名只显示一个 如图2
SELECT VERSION()   -- VERSION函数,可以显示系统版本号
SELECT 100*3-1 AS 计算结果  -- 可以作为计算工具来计算
SELECT @@auto_increment_increment  -- 可以查询自增的步长
SELECT `id`,email+1 AS 新邮箱 FROM student -- 可以再原来的基础上再将表内值做修改 
/*修改的格式为 select 字段名1,字段名2,...（as 新别名） from 表名*/   
SELECT `id`,CONCAT(`name`,'狗' ) AS 新名字 FROM student  -- 如图3

3.条件语句

作用：检索数据中符合条件的值,

1.多个where语句组成的条件子句，利用逻辑运算符来连接

代码

SELECT  `id`,`name` FROM student WHERE email =123456 && id BETWEEN 2 AND 6 -- and或者&&都可以，逻辑与
SELECT  `id`,`name` FROM student WHERE email =123456 OR id BETWEEN 2 AND 6 -- or或者||都可以，逻辑或
SELECT  `id`,`name` FROM student WHERE email!=654321-- not或者!都可以,逻辑非

2.模糊查询

代码

SELECT id,NAME FROM student WHERE NAME LIKE '李_'  -- 查询姓名为李某的同学ID
SELECT id,NAME FROM student WHERE NAME LIKE '李%'  -- 查询姓名为李某某的同学ID
/*在上面两行代码中,只有LIKE后面的不同，其中李_意思是姓名中以李为开头,李后面只有一个字的同学,李%的意思是姓名中以李为开头，后面跟0到任意个字符*/
SELECT id,NAME FROM student WHERE id IN(1,2,3,4,5)  -- IN()可以固定多个值
SELECT id,NAME FROM student WHERE email IS NOT NULL   -- 查询email不为空的同学
SELECT id,NAME FROM student WHERE email IS  NULL -- 查询email为空的同学

理解：将 _ % 想象成拼接的另一个字符,类似于concat(‘a’,b)函数中的a,只不过这个a,是代表了任意字符,%相比于_的范围使我们可选择的范围变得更大举个例子：

SELECT id,NAME FROM student WHERE NAME LIKE '李_'  -- 查询姓名为李某的同学ID
SELECT id,NAME FROM student WHERE NAME LIKE '李__' -- 查询姓名为李某某的同学ID
SELECT id,NAME FROM student WHERE NAME LIKE '_李_' -- 查询姓名为某李某的同学ID
SELECT id,NAME FROM student WHERE NAME LIKE '李%' -- 查询姓名为李或者李某或李某某或者李某某某或者李某某某某...的同学ID
SELECT id,NAME FROM student WHERE NAME LIKE '%李%' -- 这个范围就更大了

4.连表查询

在我理解里相当于加了范围的条件语句,以前是一张表里的数据通过条件去筛选,现在是两张表里的数据通过表里的数据去筛选

将其格式理解为

查询 别名1.字段1，字段2，字段3， 
从 表1 别名1
inner join(left join)(right join) 表2 别名2
on 别名1.字段1= 别名2.字段名
where 条件
/*重点是找到共同点,主表,副表,条件*/

代码

插入课堂上老师给的代码创建了school数据库下student result subject grade表
-- 查询缺考的同学
/*思路：
表：student result
共同点：studentNO studentName
条件：studentresult is null
*/
SELECT stu.studentNO,studentName,SubjectNo,studentResult
FROM student AS stu
inner join result AS res
ON stu.studentNO=res.studentNO
WHERE studentresult is null

-- 查询参加考试的同学的信息，学号,学生姓名,科目名,分数
/*思路
表：student result subject
共同点：student与result：studentNO
       result与subject:subjectNO
条件 studentresult is not null
 */
SELECT stu.studentNO,studentName,SubjectName,studentResult
FROM student AS stu
RIGHT JOIN result AS res
ON stu.studentNO=res.studentNO
INNER JOIN subject AS sub
on res.subjectNO=sub.subjectNO
WHERE studentresult is not null

拓展:自连接

有的因为数据将两张表的数据综合到一张表上,比如下图的例子,这个时候可以把这张表看成两张一模一样的表来做连表查询,这样的操作叫做自连接

代码：

/*创建一个名为category的新表*/
CREATE TABLE IF NOT EXISTS `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
`pid` INT(10) NOT NULL COMMENT'父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT'主题名字',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*导入数据 其中pid是父类的意思*/
INSERT INTO `category` 
VALUES('3','1','软件开发'),
('2','1','信息技术'),
('5','1','美术设计'),
('4','3','数据库'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 查询父子关系
SELECT a.`categoryName`AS'父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

程序结果：

5.分页和排序

/*排序： 升序 ASC    
        降序 DESC
格式：ORDER BY 字段A ASC  根据字段A内的值升序排列
     ORDER BY 字段A ASC  根据字段A内的值降序排列
*/
/*分页：让数据看起来更清爽有序
格式：LIMIT 起始值,页面的大小
     LIMIT 0,5  -- 从0条数据开始,一共排列五条数据
     第一页：limit 0,5
     第二页：limit 5,5
     第三页：limit 10,5
     第n页： limit (n-1)*5,5
*/

ASC
降序 DESC
格式：ORDER BY 字段A ASC 根据字段A内的值升序排列
ORDER BY 字段A ASC 根据字段A内的值降序排列
*/
/*分页：让数据看起来更清爽有序
格式：LIMIT 起始值,页面的大小
LIMIT 0,5 – 从0条数据开始,一共排列五条数据
第一页：limit 0,5
第二页：limit 5,5
第三页：limit 10,5
第n页： limit (n-1)*5,5
*/