一、概述
MySQL是一个关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
二、创建数据库
创建数据库需使用SQL语句
1、创建新库
格式create database 库名;
mysql> create database database1; #直接新建库
mysql> create database new_database character set utf8; #并指定其编码格式
2、创建表
格式create table 表名(字段 字段类型(长度) ...);
例:
mysql> create table user_1(
-> id int not null auto_increment,
-> name varchar(30) not null,
-> hobby varchar(100) not null,
-> primary key(id))default charset = utf8;
三、对数据库的操作
1、查看数据库 : show databases;
2、使用数据库 : use 数据库名;
3、修改数据库:
(1)修改表
?修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
?添加字段 :ALTER TABLE 表名 ADD字段名 列属性[属性]
?修改字段 :ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
?删除字段 :ALTER TABLE 表名 DROP 字段名
(2)删除数据表
DROP TABLE [IF EXISTS] 表名
四、对数据库数据进行增删改查
1、添加数据
格式 insert into 表名 (字段,字段)values (值,值);
例:
mysql> insert into users (name,student_id) values ("hhh","001");
2、删除数据
格式delete from 表名 where 字段 = 值;
例:
mysql> delete from users where id=1;
mysql> delete from users where id in (6,7,8); #删除多条语句
3、修改数据
格式update 表名 set 字段 = "值" where 条件;
例:
mysql> update student set age = 20 where name = "Alice";
4、查询数据
(1)指定查询字段
查询表中所有的数据列结果 , 采用 " * " 符号;select * from 表名;
查询指定字段select name from student;
(2)AS子句作为别名
作用:可给数据列取一个新别名;可给表取一个新别名;可把经计算或总结的结果用另一个新名称来代替
#as可省略
#为列取别名
select studentno as 学号,studentname as 姓名 from student;
#为表取别名
select studentno,studentname from student as s;
#为查询结果取一个新名字
select concat('姓名:',studentname) as 新名字 from stuydent;
(3)DISTINCT关键字
作用 : 去掉SELECT查询返回的记录结果中重复的记录(返回所有列的值都相同),只返回一条(去重)
例:查询性别为男的学生所在的年级
select distinct gradeid from student where sex = 1;
(4)where条件语句
用于检索数据表中 符合条件 的记录
搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.
搜索条件的组成
逻辑操作符
比较操作符
注意: 数值数据类型的记录之间才能进行算术运算;相同数据类型的数据之间才能进行比较;
#查询考试成绩在95-100之间
SELECT Studentno,StudentResult FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
#或者
select studentno,studentresult from result
where studentresult betwenn 95 and 100;
#查询除了100号同学外其他同学的成绩
select studentno,studentresult from result
where studentno != 100;
#或者
select studentno,studentresult from result
where not studentno=100;
(5)模糊查询
用比较操作符like与%(代表任意个字符)或者_(一个字符)结合起来使用
查询姓名中含有特殊字符的需要使用转义符号 \
例:
查询姓李的同学的学号及姓名
select studentno,studentname from student
where studentname lile '李%';
查询姓李名字有两个字的同学的学号及姓名
select studentno,studentname from student
where studentname like '李__'; #这是两个下划线
查询名字里含有夜字的学生学号和姓名
select studentno,studentname from student
where studentname like '%夜%';
查询学号为100,150,200的学生学号和姓名
select studentno,studentname from student
where studentno in(100,150,200);
查询出生日期没填写的学生学号和姓名
select studentno,studentname from student
where brondate is null;#不能写=null 这是代表错误的
查询没有写家庭住址的同学学号和姓名
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;#空字符串不等于null
(5)连接查询
?JOIN对比
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join
以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充
右外连接 right join
以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充
例:
查询有成绩的学生的学号、姓名、科目编号、分数
#内连接
select s.studentno,studentname,subjectno,studentresult
from student s
inner join result r
on r.studentno = s.studentno
#右连接
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on r.studentno = s.studentno
#左连接 #将会查询所有学生,没有成绩的学生也会查出来
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on r.studentno = s.studentno
#等值连接
select s.studentno,studentname,subjectno,studentresult
from student s,result r
where r.studentno = s.studentno
查询没有成绩的学生的学号、姓名、科目号、成绩(左连接的应用场景)
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on r.studentno = s.studentno
where studentresult is null;
?自连接
自连接的核心思想是把一张表看成两张一模一样的表,然后将这两张表连接查询
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
需要创建一个表来演示自连接,代码如下
# 创建一个表
CREATE TABLE `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
# 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
创建好的表如下
查询:
mysql> select a.categoryName as '父栏目',b.categoryname '子栏目'
-> from category as a,category as b
-> where a.categoryid = b.pid;
结果:
±---------±---------+
| 父栏目 | 子栏目 |
±---------±---------+
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
| 信息技术 | 办公信息 |
±---------±---------+
五、排序和分页
1、排序
语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。
例:查询数据库结构的考试结果,包括学生的学号、姓名、成绩
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on r.studentno = s.studentno
inner join subject sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构'
order by studentresult desc,studentno
2、分页
语法 :
SELECT * FROM table
LIMIT [offset,] rows [rows OFFSET offset];
#第一个数为页码,第二个数为单页面显示条数
例1:查询数据库结构的考试结果,包括学生的学号、姓名、成绩,并将结果每五条分为一页,显示第一页
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on r.studentno = s.studentno
inner join subject sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构'
order by studentresult desc,studentno
limit 0,5;