Mysql基础学习

最新推荐文章于 2022-10-28 22:00:00 发布

asp_monkey

最新推荐文章于 2022-10-28 22:00:00 发布

阅读量443

点赞数

分类专栏：数据库文章标签：基础查询

本文链接：https://blog.csdn.net/asp_monkey/article/details/78980699

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySql

1 概述

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。(文件系统)。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

SQL：Structure Query Language。（结构化查询语言）

数据库的表名列名不能是关键字

2 SQL分类（4种）

2.1 DDL**（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

CREATE、 ALTER、DROP

2.2 DML***（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

INSERT、 UPDATE、 DELETE

2.3 DQL***（Data Query Language）：数据查询语言，用来查询记录（数据）。

SELECT

2.4 DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

2.1 DDL

使用的关键字：CREATE、 ALTER、 DROP

2.1.1操作库

创建库mydb1

create database mydb1;

展示所有数据库*

show databases;

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database mydb2 character set utf8;

删除mydb3库

drop database mydb3;

查看当前使用的数据库

select database();

2.1.2 操作表

创建表

create table 表名(

字段1 字段类型,

字段2 字段类型,

...

字段n 字段类型

);

常用数据类型：

int：整型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

char：固定长度字符串类型； char(10) 'abc ' 256

varchar：可变长度字符串类型；varchar(10) 'abc' 65535

text：字符串类型;

blob：字节类型；

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

当前数据库中的所有表*

SHOW TABLES;

表employee增加一个image列。

ALTER TABLE employee ADD image blob;

修改job列，使其长度为60。

ALTER TABLE employee MODIFY job varchar(60);

删除image列,一次只能删一列。

ALTER TABLE employee DROP image;

表名改为user。

RENAME TABLE employee TO user;

列名name修改为username

ALTER TABLE user CHANGE name username varchar(100);

删除表

DROP TABLE user ;

2.2 DML（重要）

使用关键字：INSERT 、UPDATE、 DELETE

在mysql中，字符串类型和日期类型都要用单引号括起来。'tom' '2015-09-04'

空值：null

2.2.1插入操作 INSERT INTO

语法： INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)

VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');

(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');

2.2.2 修改操作 UPDATE

语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。 WHERE 列名=值

2.2.3 删除操作 DELETE

语法： DELETE FROM 表名【WHERE 列名=值】

删除表中名称为’zs’的记录。

DELETE FROM emp WHERE name=‘zs’;

删除表中所有记录。

DELETE FROM emp;

DELETE 删除表中的数据，表结构还在;删除后的数据可以找回

2.3 DQL操作（重要）

查询关键字：SELECT

DQL数据查询语言（重要）

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

查询返回的结果集是一张虚拟表。

语法： SELECT 列名 FROM表名【WHERE --> GROUP BY -->HAVING--> ORDER BY】

SELECT selection_list /要查询的列名称/

FROM table_list /要查询的表名称/

WHERE condition /行条件/

GROUP BY grouping_columns /对结果分组/

HAVING condition /分组后的行条件/

ORDER BY sorting_columns /对结果分组/

LIMIT offset_start, sizet /结果限定/

2.3.1基础查询

查询所有列

SELECT * FROM stu;

查询指定列

SELECT sid, sname, age FROM stu;

2.3.2 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

=、（!=、<>）<、<=、>、>=；

BETWEEN…AND；

IN(set)；

IS NULL； IS NOT NULL

AND；

OR；

NOT；

1 查询性别为女，并且年龄小于50的记录

　　SELECT * FROM stu WHERE gender='female' AND age<50;

2 查询学号为S_1001，或者姓名为liSi的记录

SELECT * FROM stu WHERE id ='S_1001' OR name='liSi';

3 查询学号为S1001，S1002，S_1003的记录

SELECT * FROM stu WHERE id IN ('S1001','S1002','S_1003');

4 查询年龄在20到40之间的学生记录

SELECT FROM stu WHERE age>=20 AND age<=40;或者

SELECT FROM stu WHERE age BETWEEN 20 AND 40;

2.3.3 模糊查询

使用关键字LIKE。

通配符: _ ：任意一个字符 %：任意0~n个字符

1 查询姓名中第2个字母为“i”的学生记录

SELECT FROM stu WHERE sname LIKE '_i%' ;

2.3.4 字段控制查询（去重复）*****

1 查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp

2 查看雇员的月薪与佣金之和

SELECT *,sal+comm FROM emp

3 comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现 NULL。下面使用了把NULL转换成数值0的函数IFNULL"

SELECT *,sal+IFNULL(comm,0) FROM emp;

4 给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0) total FROM emp

2.3.5 排序(重要)

order by 列名 asc(默认升序) desc（降序）

查询所有学生记录，按年龄升序排序

SELECT FROM stu ORDER BY sage ASC;

或者

SELECT FROM stu ORDER BY sage;

2.3.6 聚合函数

聚合函数 sum avg max min count

聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

COUNT

1 查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

2 查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

3 查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp WHERE sal > 2500;

SUM()

当需要纵向求和时使用sum()函数。

1 查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

2 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

3 统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

4 查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

2.3.7 分组查询

GROUP BY xx

根据xx分组

1 查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno

2 查询每个部门的部门编号以及每个部门的人数：

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

3 查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno , COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

HAVING子句

4 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

having与where的区别:

1.having是在分组后对数据进行过滤.

where是在分组前对数据进行过滤

2.having后面可以使用聚合函数(统计函数)

where后面不可以使用聚合函数

select --- from--- where ---group by---having--- order by

sql语句的书写顺序： 
 select * from 表名
              where  条件
              group by 列名
              having  条件
              order by  列名 asc/desc
              limit start,size  
 1.having和where比较
      where 在分组前过滤,
      having在分组后过滤,可以加聚合函数(max,min,sum.count.avg)，不能独立出现，必须分组后调用
 2.sql执行顺序：  
      from 表名
      where 条件
      group by 列名
      having  条件
      order by  列名 asc/desc
      limit start,size     
      select
  3.模糊查询 like  %代表任意字符  _代表一个字符   ，如果like后的值是字符串一定要用单引号

2.3.8 LIMIT 方言

1 查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开始，即第一行开始

2 查询10行记录，起始行从3开始

SELECT * FROM emp LIMIT 3, 10;

2.4 分页操作（MySql方言）

分页公式 limit （当前第几页—1）*每页显示的行数，每页显示的行数

3 数据完整性

完整性的分类

> 实体完整性

> 域完整性

> 引用完整性

1 实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型： 主键约束（primary key）

唯一约束(unique)

自动增长列(auto_increment)

1.1 主键约束（primary key）

第一种添加方式：

CREATE TABLE student(

Id int primary key,

Name varchar(50)

);

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student(

id int,

Name varchar(50),

Primary key(id)

);

CREATE TABLE student(

id int,

Name varchar(50),

Primary key(id，name)

//两个都是主键

);

第三种添加方式：

CREATE TABLE student(

Id int,

Name varchar(50)

);

ALTER TABLE student ADD PRIMARY KEY (id);

1.2 唯一约束（unique）

CREATE TABLE student(

Id int primary key,

Name varchar(50) unique

);

1.3 自动增长（auto_increment）

给主键添加自动增长的数值，列只能是整数类型，但是如果删除之前增长的序号，后面再添加的时候序号不会重新开始，而是会接着被删除的那一列的序号

CREATE TABLE student(

Id int primary key auto_increment,

Name varchar(50)

);

2 域完整性

域完整性约束：数据类型非空约束（not null）默认值约束(default)

2.1 not null

CREATE TABLE student(

Id int pirmary key,

Name varchar(50) not null,

Sex varchar(10)

);

2.2 default

CREATE TABLE student(

Id int pirmary key,

Name varchar(50) not null,

Sex varchar(10) default ‘男’

);

3 引用完整性

3.1主外键关联

要有外键必须先有主键，主键和外键的类型必须一致

子表的某一字段去参照主表的主键!

子表的字段叫做外键! 主外键关联!

CONSTRAINT 关系的名字(不能重复) foreign key (外键) references 主表名(主键)

ALTER TABLE 子表 ADD CONSTRAINT 约束名称 FOREIGN KEY(外键) REFERENCES 主表名(主键);

例如：主外键约束

两表的sid为参照

//1.创建主表

CREATE TABLE newstu( sid BIGINT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL

);

//创建一个字表 sroce

CREATE TABLE score( cid BIGINT PRIMARY KEY AUTO_INCREMENT, sre INT, sid BIGINT

);

CONSTRAINT 关系的名字(不能重复) foreign key (外键) references 主表名 (主键)

CONSTRAINT n_c_f FOREIGN KEY (sid) REFERENCES newstu (sid) );

ALTER TABLE 子表 ADD CONSTRAINT 约束名称 FOREIGN KEY(外键) REFERENCES 主表名(主键);

ALTER TABLE score ADD CONSTRAINT xxxxxxx FOREIGN KEY(sid) REFERENCES newstu (sid)

3.2 表关系

1 一对多 (多对一)

例子: 图书和图书类别学生和成绩

子表的外键关联主表主键 (主外键关联)

2 多对多

需要建立第三张关系表! 关联两个表! 而且关系表中肯定会包含两个字段!这两个字段

分别是两张主表的外键!

例子：

学生表（学号，姓名，…………）主键：学号

课程表（课程号，课程名，…………）主键：课程号

一个学生能选多门课，一个课可以由多个学生选，即是多对多关系

那么成绩表就是它们之间关系的体，即引用成绩表（学号，课程号，成绩）。

这里学号和课程号，分别是学生表和课程表中学号和课程号的外键

例子：

CREATE TABLE student(

sid BIGINT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) NOT NULL

);

CREATE TABLE teacher(

tid BIGINT PRIMARY KEY AUTO_INCREMENT, tname VARCHAR(20) NOT NULL

);

//多对多关系没有主子表之分 //多对多关系需要创建一个中间表使两个表创建关系

CREATE TABLE s_t(

sid BIGINT, tid BIGINT, CONSTRAINT s_t_s FOREIGN KEY (sid) REFERENCES student(sid), CONSTRAINT s_t_t FOREIGN KEY (tid) REFERENCES teacher(tid)

)；

多表查询纵向和横向

纵向：select * from a union select * from b （去重复）

select * from a union all select * from b （不去重复）

横向：

3.3 99查询法内连接外连接

-- 学生表 CREATE TABLE student(

stuid VARCHAR(10)PRIMARY KEY, stuname VARCHAR(50)

)；

-- 分数表 CREATE TABLE score(

stuid VARCHAR(10), score INT, courseid INT, CONSTRAINT fk_stu_sco FOREIGN KEY(stuid) REFERENCES student(stuid)

)；

1 练习: 查询出每名学生的分数

SELECT s.stuid,s.stuname,c.score,c.courseid FROM student s,score c WHERE s.stuid = c.stuid;

2 使用内连接的方式进行多表查询 SELECT * FROM student s INNER JOIN score c ON s.stuid = c.stuid; SELECT * FROM student s JOIN score c ON s.stuid = c.stuid;

3 使用外连接 左右指向的是主键

SELECT * FROM score c RIGHT OUTER JOIN student s ON s.stuid = c.stuid;

-- 学科表 CREATE TABLE course(

courseid INT PRIMARY KEY, cname VARCHAR(20) NOT NULL, tid INT

)；

-- 教师表teacher CREATE TABLE teacher( tid INT PRIMARY KEY, tname VARCHAR(20) NOT NULL, lid INT

);

三张表引申n张表

99查询法

SELECT * FROM student s ,score c ,course cc WHERE s.stuid = c.stuid AND c.courseid = cc.courseid;

内连接

SELECT * FROM student s INNER JOIN score c ON s.stuid = c.stuid;

INNER JOIN course cc ON c.courseid = cc.courseid;

外连接 :建议使用

SELECT * FROM student s LEFT OUTER JOIN score c ON s.stuid = c.stuid

LEFT OUTER JOIN course cc ON cc.courseid = c.courseid;

查询练习

行转列

asp_monkey

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql基础学习

MySql1 概述数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。(文件系统)。数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问
复制链接

扫一扫