mysql基础学习

1. 数据库操作

• 创建数据库

create database [if not exists] database_name;

• 查看数据库

show databases;

• 选择数据库

use database_name;

• 删除数据库

drop database [if exists] database_name;

• 综合示例

create database garden;
use graden;
show databases;
drop database garden;
show databases;

2. 数据库表操作

• 创建表

create table t_class(
    classno int,
    cname varchar(20),
    loc varchar(40),
    stucount int
);

• 查看数据库中的表

show tables;

• 查看表内数据

# describe语句查看表定义
describe t_class ;

# show create table查看表的详细定义
show create table t_class \G;

• 删除表

drop table table_name;

• 修改表

# 修改表名
alter table old_name rename [to] new_name;

# 增加字段,表尾增加字段
alter table add propName propType;

# 增加字段，表首增加字段
alter table add propName propType first;

# 增加字段，在表的指定字段之后增加
alter table add pNameNew propType after pNameOld;

# 删除字段
alter table tableName drop propName;

# 修改字段的数据类型
alter tableName modify propName propType;

# 修改字段的名字
alter tableName change pNameOld pNameNew pOldType;

# 同时修改字段的名字和类型
alter tableName change pOldName pNewName pNewType; 

# 修改字段的顺序
alter tableName modify pName1 propType first|after pName2;

3. 操作表的约束

• 完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性的约束条件，数据库系统不在执行用户的操作
• 完整性约束条件

约束条件	说明
primary key（PK）	主键，唯一标识对应的元组
foreign key（FK）	外键，是与之联系的某个表的主键
not null（NK）	属性值非空
unique（UK）	属性值唯一
auto_increment	该属性的值自动增加，一个表中只有一个字段使用该约束
default	为该属性设置默认值
unsinged（无符号）	设置该属性值为无符号类型
zerofill（零填充）	位数不足则补零占位

• 完整实例

-- 两种定义外键的方式
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '密码' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birth` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(10) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

desc student;

alter table `student`
add constraint `FK_grade` foreign key(`gradeid`) references 
`grade`(`gradeid`);

-- 另外一种方式
create table if not exists `student`(
	`id` int not null auto_increment comment 'id值',
	`name` VARCHAR(10) not null DEFAULT '匿名' comment '姓名',
	`age` VARCHAR(10) not null comment '年龄',
	`pwd` VARCHAR(20) not null default '123456' comment '密码',
	`sex` VARCHAR(2) not null default '女' comment '性别',
	`gradeid` int not null comment '学生年级',
	`address` VARCHAR(20)  default null comment '家庭住址',
	`email` VARCHAR(15)  default null comment '邮箱',
	primary key(`id`),
	key `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` foreign key(`gradeid`) REFERENCES `grade`(`gradeid`) 
)ENGINE = INNODB;

4. 常用的偷懒语句

-- 查看创建数据库的语句
show create database databaseName;

-- 查看创建数据库表的语句
show create table tableName;

-- 查看表的内容
desc tableName;
describe tableName;

5. Mysql数据操作

在Mysql软件中，可以通过SQL语句中的DML语句来实现数据的操作，内容包括

• 插入数据
• 修改数据
• 删除数据

5.1 插入数据

create table `t_class`(
	`classno` int(11),
   `cname` varchar(20),
   `loc`  varchar(20),
   `advisor` varchar(20)
);
desc t_class;

• 插入完整的记录

insert into t_class(classno,cname,loc,advisor) 
VALUES(1,'高一二班','八教3楼','张三');

insert into t_class
VALUES(2,'高一三班','八教3楼','周杰伦');

-- 插入多条完整的记录
insert into t_class(classno,cname,loc,advisor) 
VALUES(1,'高一二班','八教3楼','张三'),
(4,'高二1班','五教3楼','王力宏'),
(5,'高二2班','五教3楼','林俊杰'),
(6,'高二3班','五教2楼','张杰');

• 插入记录的一部分

insert into t_class(classno,cname) 
VALUES(3,'高一五班');

-- 插入多条部分记录
insert into t_class(cname,advisor) 
VALUES('高一二班','张三'),
('高二1班','王力宏'),
('高二2班','林俊杰'),
('高二3班','张杰');

• 插入查询结果

insert into t_class(cname,loc,advisor) 
select(cname,loc,advisor) from t_class
where classno = 1;

5.2 更新数据

• 更新特定数据

update t_class
set loc = '逸夫楼' , advisor = '李白'
where classno = 3;

• 更新所有数据

update t_class
set classno = 6,cname = '火箭班',loc = '清华园',advisor = '蔡元培'
where cname = '高二2班';

5. 3删除数据

• 删除特定数据

delete from t_class where cname = '高二2班';
delete from t_class where loc = null;

• 删除全部数据

delete from t_class;

6. Mysql数据类型

6.1 整数类型

整数类型	字节数
tinyint	1
smallint	2
mediumint	3
int(最常用的整数类型)	4
integer（和int相同）	4
bigint	8

6.2 浮点数类型

浮点数类型	字节数
float(适用于表示购物系统的货物标价)	4
double	8
decimal（m,d）或者dec（m,d）（存储字符串，适用于表示货币金额）	m+2

6.3 日期与时间类型

时间类型	字节数
year(年份)	1
time（时间）	3
date（日期）	4
datetime（日期时间，最常用）	8
timestamp（时间戳，较常用）	4

6.4 字符串类型

• char和varchar类型都是在创建表时指定最大长度

类型	备注
char（m) ，m为最大长度	适用于长度变化不大，查询速度要求高的字符串类型
varchar（m），m为最大长度	适用于长度变化较大的字符串，占用空间小，灵活

• text类型是一种特殊的字符串类型，用于存储文章等文本数据

类型	存储空间
tinytext	值的长度+2
text	值得长度+2
mediumtext	值的长度+3
longtext	值得长度+4

• enmu类型又称为枚举类型
• set类型

6.5 二进制类型

• binary和varbinary存储二进制字符串，在创建时指定最大长度
• bit类型存储二进制数，在创建时指定最大长度
• blob类型存储数据量很大的二进制数据，如图片等

类型	取值范围
binary（m）	字节数为m，长度为0-m的定长字符串
varbinary（m）	字节数为值长度+1，长度为0-m的变长字符串
bit（m）	m位二进制数，m最大值为64
tintblob	可变长二进制数，最多255个字节
blob	可变长二进制数，最多2的16次方-1个字节
mediumblob	可变长二进制数，最多2的24次方-1个字节
longblob	可变长二进制数，最多2的32次方-1个字节

7. mysql运算符

7.1 运算符简介

• 算数运算符

select 5+6 加法操作,10-5 减法操作 ,2*5 乘法操作,
8 / 2 除法操作 , 8 div 4 除操作,  8%2 取余 , 8 mod 5 取余操作; 
-- 如果除数为零，将是非法运算，返回结果为NULL

create table t_student(
	`id` int(11) primary key,
	`name` varchar(20),
	`age` int(4),
	`gender` varchar(8)
);
create table t_score(
	`stuid` int(11),
	`cheinse` int(4),
	`math` int(4),
	`english` int(4),
	constraint fk_stuid foreign key(`stuid`) references t_student(`id`)
);
select `name`,`cheinse`,`math`,`english`, cheinse + math + english total
from t_student , t_score 
where id = stuid;

• 逻辑运算符

select 5 and 6 , 0 and 7 , 0 and NULL,
9 && 2, 0 && 12, 0 && NULL, 14 &&NULL;

select 5 or 6 , 0 or 7 , 0 or 0 , 3 or null,
9 || 2 , 0 || 12 , 0 || null , 14 || null;

• 比较运算符

select 3 = 3 数值比较 , 'sky' = 'have' 字符串比较, 3*4 = 2*6 表达式比较,
1 != 1 数值比较, 'dragon' <=> 'dragon'字符串比较 ,2+7 <=> 6 + 3 表达式比较;

select 8 is not null , null is null;

select !1 , not 0 , ! null , not null;

• 位运算符

8. 单表查询

8.1 基本数据查询

• 查询所有字段数据

  • 列出表的所有字段

  select id,name,age,sex,loc from infor;
  

  • ' * ' 号的使用

  select * from student;
  

• 查询特定字段数据

select age , name from infor;

• distinct 关键字避免重复数据查询

select distinct id ,age ,name from student;

• 实现数学四则运算数据查询

select chinese , math , chinese + math total from score;

• 设置显示格式数据查询

select concat(name,'学生的总分是:', chinese+math+english) from score;

8.2 条件数据查询

• 查询特定记录

SELECT * FROM product WHERE product_id = 1;

• 带IN关键字的查询
  • 对于关键字 in 查询的集合中如果存在null，则不影响查询结果
  • 对于关键字 not in ，查询的集合中如果存在null，则查询结果为空

SELECT * FROM product WHERE product_id  in (1,2,3);
SELECT * FROM product WHERE product_id  not in (1,2,3);

• 带between and 关键字的查询

SELECT * FROM product WHERE product_id  between 1 and 3;

• 带LIKE的模糊查询
  • % 匹配任意长度字符串
  • _ 匹配单个字符

SELECT * FROM product WHERE product_type LIKE '%用%';
SELECT * FROM product WHERE product_type LIKE '_房__';

• 带 IS NULL的空值查询

SELECT * FROM product WHERE regist_date IS NULL;

• 带 AND的多条件查询

SELECT * FROM product WHERE regist_date IS NULL and id = 1;

• 带OR的多条件查询

SELECT * FROM product WHERE regist_date IS NULL or id = 1;

• 对查询结果进行排序
  • desc 降序
  • asc 升序（默认）

SELECT * FROM product WHERE product_id  in (1,2,3)  ORDER BY product_id asc;
SELECT * FROM product WHERE product_id  in (1,2,3)  ORDER BY product_id desc;

8.3 统计函数和分组查询

• count（）统计函数

count（*） ： 统计数据记录的条数，不管表字段的值是否为空

count（字段名） 统计指定字段记录的条数，忽略字段值为空的记录

SELECT COUNT(*) AS number from product;
SELECT COUNT(*) AS number from product WHERE sale_price <= 1000;
SELECT COUNT(regist_date) number from product;

• avg（）计算平均值函数

avg（字段名）：对指定字段值的记录计算平均值，忽略字段值为空的情况

SELECT AVG(sale_price) AS '平均值' from product;
SELECT AVG(sale_price) AS '平均值' from product WHERE sale_price > 1000;

• sum（）求和函数

sum（字段名）：对指定字段值的记录求和，忽略字段值为空的情况

SELECT SUM(sale_price) AS '统计和' from product WHERE sale_price > 1000;

• max（）最大值函数

• min（）最小值函数

max（字段名）：对指定字段值的记录求出最大值，忽略字段值为空的情况

min（字段名）：对指定字段值的记录求出最小值，忽略字段值为空的情况

max和min函数不会忽略数值为0的记录

SELECT max(sale_price) AS '最大值' from product;
SELECT min(sale_price) AS '最小值' from product;

• 针对没有记录的数据库表，count函数返回0，其他函数返回NULL

• 简单分组查询

在现实应用中经常需要对记录进行分组，然后在对这些分组后的数据进行统计计算，在具体进行分组查询时，分组所依据的字段的值一定要有重复值，否则分组没有任何意义

having和where关键字的区别：

where关键字主要用来实现条件限制数据记录

having关键字主要用来实现条件限制分组数据记录

SELECT GROUP_CONCAT(product_name) AS '商品名' , product_type '商品类型' from product GROUP BY product_type; 

-- 多个字段分组查询
SELECT GROUP_CONCAT(product_name) AS '商品名' , product_type '商品类型' from product GROUP BY product_type , sale_price; 

-- 使用HAVING字句限定分组查询
SELECT GROUP_CONCAT(product_name) AS '商品名' , product_type '商品类型' from product 
GROUP BY product_type
HAVING AVG(sale_price) > 500; 

8.4 限制数据查询记录的数量（Limit）

作用：实现查询记录的显示数量

类别：

​ 制定初始位置：从指定初始位置开始，用于实现分页

​ 不指定初始位置：默认从第一条记录开始

• 不指定初始位置

-- 指定显示4条数据，未指定初始位置
select product_name from product ORDER BY sale_price LIMIT 4;

• 指定初始位置

-- 指定显示3条数据，指定初始位置为0
select product_name from product ORDER BY sale_price LIMIT 0,3;

8.5 使用正则表达式查询

正则表达式通常被用来检索替换某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串，MySQL使用REGEXP指定正则表达式的字符匹配模式

select product_name from product WHERE product_name REGEXP '^T';
select product_name from product WHERE product_name REGEXP '锅$';

综合示例

create table student(
	`id` int(10) not null unique COMMENT '学号',
	`name` VARCHAR(20) NOT NULL COMMENT '名字',
	`sex` VARCHAR(4) COMMENT '性别',
	`birth` YEAR COMMENT '出生年',
	`department` VARCHAR(20) COMMENT '院系',
	`address` VARCHAR(50) COMMENT '地址',
	PRIMARY KEY(`id`)
);