mysql(1)

目录

一. 数据库概述

1. 数据库

2. 为什么学习数据库

3. 常见的数据库产品

4. 数据库的分类

二. SQL语句

1. DDL

1.1 创建删除数据库

1.2 创建表

1.2.1数据类型

1. 字符型

2. 日期时间型

3. 整形

4. 浮点型

1.2.2 表约束

1.2.3 表的一些其他操作

2. DML

2.1 插入数据

2.2 修改数据

2.3 删除数据

 3. DQL

3.1 查询结果处理:

3.1.1 查询函数

3.1.1 单行函数

3.1.1.1 字符函数

3.1.1.2 逻辑判断函数

3.1.1.3 数学函数

3.1.1.4 日期函数

3.2 分组函数

---

一. 数据库概述

1. 数据库

数据库:对数据进行存储管理的仓库

2. 为什么学习数据库

我们之前对数据的存储,要么存储在变量中,要么存储在对象中,但无论是变量还是对象,都是存储在内存中的,也就是说当程序运行结束,他们就会被销毁.后面我们又学习了IO,可以将数据存储在文件中,但是操作起来又很麻烦,那么有没有一种方式能将数据持久化存储的同时操作还简单呢,所以我们需要学习数据库

3. 常见的数据库产品

我们一般口头上所说的数据库其实是数据库管理系统(DataBase Management System),简称DBMS

国外:

Mysql:快捷,可靠,免费,开源

Oracle:功能强大,收费

SQL Server(微软):以前只能安装在Windows操作系统上

DB2(IBM):适合处理海量数据,收费

国内:

南大通用GBASE: 天津南大通用数据技术股份有限公司
达梦:武汉达梦数据库股份有限公司
人大金仓:北京人大金仓信息技术股份有限公司
神通:神舟通用公司

一般和政府相关的工作会用到国内的数据库

4. 数据库的分类

数据库分为关系型数据库和非关系型数据库

关系型数据库:以表为单位,表和表之间可以建立关联关系

非关系型数据库:通过键值对实现,一般用于访问量过大对关系型数据库进行缓存,来减少关系型数据库的压力

二. SQL语句

SQL(Structured Query Language):是一种特殊目的的编程语言,用于查询管理数据库

1. DDL

DDL(Data Definition Language):用来创建数据库和修改数据库表结构的SQL语句

1.1 创建删除数据库

-- 创建数据库
-- CREATE DATABASE [if not exists] 数据库名 [CHARSET utf8]

CREATE DATABASE test1; --创建一个名字为test1的数据库

CREATE DATABASE test2 CHARSET utf8; -- 创建一个名字为test2的数据库,并指定字符集为utf-8

CREATE DATABASE IF NOT EXISTS test3 CHARSET gbk; -- 如果名字为test3的数据库不存在就创建一个,,并指定字符集为gbk

-- 删除数据库
-- DROP DATABASE 数据库名
DROP DATABASE test1; -- 删除数据库test1

--修改数据库(数据库一旦创建就不能修改,只能修改数据库的字符集)
ALTER DATABASE test1 CHARSET gbk;

1.2 创建表

1. 明确表名

2.明确列名和列的数据类型

3. 为列设置约束

1.2.1数据类型

1. 字符型

字符型分为char(n)和vchar(n)

char(n):长度为n的定长字符串,例如n=5,如果我们只存储了2个字符进去,长度依然是5,会补空格,一般用于定长字符串

vchar(n):长度最大为n的字符串,长度是可变的,如果不够n,存储的字符串长度是多少,实际长度就是多少,不会补空格

2. 日期时间型

日期时间型分为date日期(年月日)和datetime时间(年月日时分秒)

3. 整形

整形有5种

(1)TINYINT:占一个字节

(2)SMALLINT:占两个字节

(3)MEDIUMINT:占三个字节

(4)INT:占四个字节

(5)BIGINT:占8个字节

一般常用的就是INT

4. 浮点型

Decimal(M,D)表示该浮点数有M位,保留D位小数

创建一个学生表,不加约束 

CREATE student(
num INT,
name VARCHAR(10),
gender CHAR(1),
birthday DATE,
phone CHAR(11),
address VARCHAR(30),
height DECIMAL(3,2),
reg_time DATETIME

)

1.2.2 表约束

表约束就是对表中的数据进行限制,不满足条件就不能添加到表中

1.主键约束 PRIMARY KEY:设置当前列为主键,设置为主键的列,不能为空,不能重复(唯一),且一个表中只能有一个主键

2.AUTO_INCREMENT:设置主键列为自动增长,只能设置主键列,且主键列必须为整数

3.不为空约束 NOT NULL:设置该列不能为空,可以设置多个列

4.唯一约束 UNIQUE:设置该列不能有重复,可以设置多个列

5.条件约束 CHECK(条件):满足该条件才能加入到表中,否则加不进去,可以设置多个列

1.2.3 表的一些其他操作

1.COMMENT:给表添加注释

2.DEFALUT:给表设置默认值

3.RENAME TABLE A(旧表名) TO B(新表名):修改表名

4.CREATE TABLE A(新表名) LIKE B(被复制表名):复制表结构,注意只是复制表的结构,不复制内容

5.ALTER TABLE 表名 ADD A(新的列名) B(列的数据类型) :用于已经创建好的表增加新的一列

创建一个学生表加约束

CREATE TABLE student(
   num INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(10) NOT NULL,
   gender CHAR(1) NOT NULL,
   birthday DATE,
   phone CHAR(11) NOT NULL  UNIQUE,
   address VARCHAR(30),
   height DECIMAL(3,2) CHECK(height<2.60),
   reg_time  DATETIME
)

2. DML

DML(Data Manipulation Language):数据操纵语言,用来对数据库中的表进行增删改操作的SQL语句

常用语句:insert(插入),delete(删除),update(修改)

2.1 插入数据

插入数据有4种方式

方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n)

-- 方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n)

INSERT INTO student(NAME,gender,birthday,phone)VALUES('张三','男','2003-3-5','15233336666')

INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
       VALUES('张三','男','2003-3-5','15233336688','汉中',1.75,NOW())

方式2: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n)

-- INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n)
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
       VALUES('张三丰','男','2003-3-5','15233336677','汉中',1.75,NOW()),
             ('李四','女','2003-3-3','15233336699','汉中',1.85,NOW()),
             ('王五','男','2003-4-3','15233336609','汉中',1.95,NOW())

方式3: INSERT INTO 表名 set 列名1=值1,..列名n=值n

-- 方式3:INSERT INTO 表名 set 列名1=值1,..列名n=值n
INSERT INTO student SET NAME = '赵六',gender='女',phone='15777778888'

方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)

-- 方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)
INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time) 
    SELECT NAME,gender,birthday,phone,address,height,reg_time FROM student

2.2 修改数据

切记修改数据时一定要加限制条件,否则会将整个数据库表信息都修改

-- 修改语句 需要注意条件的准确性, 否则修改所有的数据
 UPDATE student SET address='陕西西',height=1.65,reg_time = '2003-4-5 14:20:10' WHERE num = 1

2.3 删除数据

切记删除数据时一定要加限制条件,否则会将整个数据库表信息都删除

 -- 删除语句
 DELETE FROM stu -- 没有条件 删除所有的数据
 DELETE FROM student WHERE num  = 13 

 3. DQL

DQL(Data Query Language):基础查询语句,是使用最频繁的语句,可以从一个表中查询,也可以从多个表中查询

语法: SELECT 查询列表 FROM 表名

特点:

查询列表可以是:表中的字段,常量,表达式,函数

查询的结果是一个虚拟表格,只能读

3.1 查询结果处理:

1. 特定列查询: SELECT column1,column2 FROM table

-- 特定列查询: SELECT column1,column2 FROM table
SELECT num,NAME,gender FROM student

2. 全部列查询: SELECT * FROM table

-- 全部列查询: SELECT * FROM table
SELECT * FROM student

3. 算术运算符: + - * /

-- sql中+ - * /只能做算术运算 +不能连接字符串
SELECT num+100,NAME FROM student

4. 排除重复行: SELECT DISTINCT column1,column2 FROM table

-- 去除查询结果中重复数据,何为重复数据;所有的列都相同
SELECT DISTINCT NAME,gender,birthday FROM student

5.查询函数:有很多,下面逐一讲解

3.1.1 查询函数

查询函数分为单行函数和分组函数

单行函数:对查询的每条数据进行操作

分组函数:做统计使用,将多行数据合并成一行数据

3.1.1 单行函数

单行函数:对查询的每条数据进行操作

3.1.1.1 字符函数

length()	获取参数值的字节个数
char_length()	获取参数值的字符个数
concat(str1,str2...)	拼接字符串
upper()/lower()	将字符串变成大写/小写
substring(str,pos,length)	截取字符串,从pos位置开始截取length个,位置从1开始
instr(str,指定字符)	返回子串第一次出现的索引,如果找不到返回0
trim(str)	去掉字符串前后的空格或子串
lpad(str,length,填充字符)	用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符)	用指定的字符实现右填充将str填充为指定长度
replace(str,old,new)	用新的字符替换所有旧的字符

-- length() 获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM student

-- char_length() 获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME) FROM student

-- concat(str1,str2,.....) 拼接字符串

SELECT num,CONCAT(NAME,':',gender) FROM student

-- upper()/lower() 将字符串变成大写/小写

SELECT NAME,UPPER(NAME),LOWER(NAME) FROM student

-- substring(str,pos,length) 截取字符串 从pos位置开始截取length个   位置从1开始

SELECT NAME,SUBSTRING(NAME,2,1) FROM student

-- instr(str,指定字符) 返回子串第一次出现的索引，如果找不到返回0

SELECT NAME,INSTR(NAME,'三') FROM student

-- trim(str) 去掉字符串前后的空格或子串
SELECT NAME,CHAR_LENGTH(NAME),CHAR_LENGTH(TRIM(NAME)) FROM student

-- lpad(str,length,填充字符) 用指定的字符实现左填充将str填充为指定长度

SELECT NAME,LPAD(NAME,5,'x') FROM student

-- rpad(str,length,填充字符) 用指定的字符实现右填充将str填充为指定长度

SELECT NAME,RPAD(NAME,5,'m') FROM student

-- replace(str,old,new) 替换,替换所有的子串
SELECT NAME,REPLACE(NAME,'三','六')FROM student

3.1.1.2 逻辑判断函数

case when 条件 then 结果1 else 结果2 end	条件成立执行结果1不成立执行结果2,可以有多个case when
if(条件,结果1,结果2)	条件成立执行结果1,不成立执行结果2,只能有一个if,不像case when可以有多个
ifnull(被检测值,默认值)	被检测值如果为null,执行默认值,否则为原本的值

/*
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回
原本的值
if函数: if else的效果 if(条件,结果1,结果2)
*/

-- case when 一条
SELECT NAME,
(CASE WHEN height>=1.80 THEN '高个子' ELSE '正常身高'END)AS height,
gender
FROM student

-- case when 多条
SELECT NAME,
       (CASE WHEN height>=1.80 THEN '高个子'
             WHEN height>=1.60 THEN '正常身高'
             ELSE '低个子' END)AS height,
       gender
FROM student


-- ifnull()
SELECT NAME,IFNULL(address,'暂未录入')AS address FROM student

--if()
SELECT NAME,IF(height>=1.80,'高个子','正常身高')FROM student

3.1.1.3 数学函数

round(数值,保留小数的位数)	根据要保留几位进行四舍五入
ceil(数值)	向上取整
floor(数值)	向下取整
truncate(数值,保留小数的位数)	根据保留的位数进行截断
mod(被除数,除数)	取余
rand()	返回0-1之间的随机数

-- 数学函数

-- round(数值),四舍五入
SELECT NAME,ROUND(height) FROM student
-- 指定保留小数位数
SELECT NAME,ROUND(height,2) FROM student

-- ceil(数值) 向上取整,返回>=该参数的最小整数
-- floor(数值) 向下取整,返回<=该参数的最大整数
SELECT NAME,CEIL(height),FLOOR(height) FROM student

-- truncate(数值,保留小数的位数) 截断,小数点后截断到几位,不会四舍五入
SELECT NAME,TRUNCATE(height,1) FROM student

-- mod(被除数,除数) 取余 被除数为正,则为正,被除数为负,则为负
SELECT NAME,MOD(num,3) FROM student

-- rand()获取随机数,返回0-1之间的小数
SELECT NAME,RAND() FROM student

3.1.1.4 日期函数

now()	返回当前日期(年月日时分秒)
curdate()	返回当前年月日
curtime()	返回当前时分秒
year(带日期的列)	返回年份
month(带日期的列)	返回月份
day(带日期的列)	返回天数
datediff(日期1,日期2)	返回两个日期相差的天数
str_to_date(日期字符串,格式)	将指定字符串按照对应格式转为日期对象
date_format(日期对象,格式)	将日期格式化为指定的字符串

-- 日期函数

-- now()返回当前时间,年月日
SELECT NAME,NOW() FROM student

-- curdate()获取当前年月日,curtime()获取当前时分秒
SELECT NAME, CURDATE(),CURTIME() FROM student

-- 日期格式化 
-- year(带日期的列)获取年份
-- month(带日期的列) 获取月份
-- day(带日期的列) 获取天数
SELECT NAME,YEAR(reg_time),MONTH(reg_time),DAY(reg_time) FROM student

-- datediff(大时间,小时间)计算两个日期之间相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student
SELECT DATEDIFF(STR_TO_DATE('2020-1-1','%Y-%m-%d'),birthday) FROM student

-- str_to_date(日期字符串,格式)
SELECT STR_TO_DATE('2001-2-2','%Y-%m-%d') FROM student

-- date_format()将日期格式化为指定的字符串
SELECT NAME,DATE_FORMAT(birthday,'%Y-%m') FROM student

3.2 分组函数

分组函数就是将某一列的数据进行操作,得到一个数据

sum(列名)	求某一列的和
avg(列名)	求某一列的平均值
max(列名)	求某一列的最大值
min(列名)	求某一列的最小值
count(列名)	根据列名统计个数

注意:

1. sum()/avg()只能用于数值类型

2. max()/min()可以用于任何类型

3. 一般建议使用count(*)/count(主键列名),如果使用count(列名),当某一列有值为空时不会被count()计算到

-- 分组函数 / 聚合函数,统计函数
-- sum 求和,avg 平均值,max 最大值,min最小值,count计数
-- sum(),avg()只能用于数值类型
-- max(),min()可以适用于所有类型

SELECT SUM(height),AVG(height),MAX(height),MIN(height) FROM student

SELECT MAX(birthday),MIN(birthday) FROM student

-- 统计行数 count(*) 官方推荐的 count(1) count(列名) 列的值为空的话,是不统计的
SELECT COUNT(num) FROM student