2407-mysql笔记

数据库(Database)，简称db
mariadb=mysql

常见的数据库：mysql、oracle、高斯（Gauss）、redis、sqlserver、SQLite、HBase

一、SQL（Structured Query Language）：结构化查询语言

1、作用：用于访问和处理数据库的标准计算机语言

2、语法特点：

（1）SQL对关键字的大小不敏感（针对windows）

（2）SQL语句可以单行或者多行书写，每行以分号结束

（3）SQL注释

3、mysql基本操作-ddl

（1）对数据库的基本操作

功能	SQL
show databases;	查看所有的数据库
create database [if not exists] mydb1 [charset=utf8];	创建数据库
use mydb1;	切换库
select database();	显示当前所在库
drop database [if exists] mydb1;	删除数据库

（2）对数据库的常用操作-创建表

创建表格式：

create table [if not exists] 表名 (字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
) [表的一些设置];

create table emp(eid int(1) comment '员工编号',ename varchar(1) comment '员工姓名'
);insert into emp values 
(-2147483648,'A'),
(21333,'B');select * from emp;

注：
（1）常见的数据类型有：
①数值类型

类型	大小	用途
tinyint	1byte	小整数值
int或integer	4byte	大整数值
double	8byte	浮点数值

tinyint范围：[0,255]
int范围：[-2147483648,2147383647]

②日期和时间类型

类型	格式	用途
date	YYYY-MM-DD	日期值
datetimestamp	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	YYYYMMDDHHMMSS	混合日期和时间值，时间戳

③字符串类型’
char-定长
varchar-变长
如果是字符串类型，会有宽度的限制，宽度填多少就代表字符串有多长

如果是数值类型，比如INT(1)，1不代表数据的长度，如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然显示出来

（3）对表结构的常用操作

sql	功能
show tables	查看当前库有哪些表
show create table emp;	查看指定某个表的创建语句
desc 表名;	查看表结构
drop table 表名;	删除表

show tables;show create table emp;desc emp;drop table emp;

4、mysql数据库基本操作-dml

DML是指数据操作语言，Data Mainpulation Language，用于对数据库表中记录进行更新、删除、插入等操作
关键字：
insert-插入
delete-删除
update-更新

（1）数据插入

语法格式：
#向表中指定某些列插入数据
insert into 表名 (列名1,列名2,列名3...)
values (值1,值2,值3...);#向表中所有列插入数据
insert into 表名 values (值1,值2,值3...);

#向student表中sid name gender age birth 字段添加数据
insert into student
(sid,name,gender,age,birth)
values
(1001,'张三','男',20,'1994-12-01');#向student表中所有字段添加数据
insert into student
values 
(1002,'赵敏','女',18,'1995-12-03','上海');#向student表中所有字段添加多行数据
insert into student
VALUES
(1003,'张三丰11','男',88,'1918-08-13','北京'),
(1004,'张三丰111','男',36,'1983-7-11','北京');

注：以上两种格式均可，SQL语句以分号结尾，可以在一条insert语句中同时插入多个列表值，values只需要写一次，括号之间用逗号隔开

（2）数据修改

语法格式：
update 表名 set 字段名 = 值,字段名 = 值...;update 表名 set 字段名 = 值,字段名 = 值... where 条件;

#将所有学生的地址修改为重庆
update student set address = '重庆';#将sid为1004的学生地址修改为北京
update student set address = '北京' where sid = 1004;#将sid为1004的学生地址修改为beijing并且性别修改为男
update student 
set address = 'beijing',gender = '男'
where sid = 1004;

（3）数据删除

语法格式：
delete from 表名 [where 条件];truncate table 表名
或者
truncate 表名

#删除sid为1004的学生数据
delete from student where sid = 1004;#删除表所有数据
delete from student;#再添加两条数据
insert into student
values 
(1003,'张三丰11','男',88,'1918-08-13','北京'),
(1004,'张无忌1','男',36,'1983-07-11','北京');#清空表数据
truncate student;

注：delete和truncate原理不同，delete只删除内容，而truncate类似drop table再create table，可以理解将整个表删除，然后再创建该表

5、mysql约束

何为约束，constraint，约束实际上就是表中数据的限制条件
作用：为了保证表中记录的完整性和有效性，比如用户表的手机号不能为空，身份证号不能重复

分类：
（1）主键约束（primary key） PK
（2）自增长约束（auto_increment）
（3）非空约束（not null）
（4）唯一性约束（unique）
（5）默认约束（default）
（6）零填充约束（zerofill）
（7）外键约束（foregin key）FK

（1）主键约束

可以加到某个列上，也可以加到多个列上

①概念：

a.主键约束相当于唯一约束和非空约束的组合，即主键约束的列不允许重复，也不允许出现空值
b.每个表最多允许一个主键

②添加单列主键

a.在定义字段的同时指定主键

语法格式：
create table 表名(...<字段名> <数据类型> primary key,...
)

INSERT INTO emp1
(name,dept_id,salary)
VALUES
('张三',8801,3600);
#Field 'eid' doesn't have a default value
#设置了主键，该字段必须要给值insert into emp1 
values 
(60051,'张三丰',5003,5000)

b.在定义完字段之后指定主键

语法格式：
create table 表名(...[constraint <约束名>] primary key(字段名)
);

create table emp2(eid int comment '编号',name varchar(20) comment '姓名',dept_id int comment '部门编号',salary double comment '工资',constraint pk1 primary key(eid)
);#主键测试emp2
#主键=唯一+非空组合
insert into emp2 VALUES
(1001,'张安',10,6000);insert into emp2 values
(1002,'杰森',40,6000);insert into emp2 values 
(NULL,'杰森',40,6000);insert into emp2 (name,dept_id,salary) values
('杰森',40,6000);

③添加联合主键(多列主键)

语法格式：
create table 表名 (...primary key (字段1,字段2,....字段n)
)

create table emp3 (name varchar(20) comment '姓名',dept_id int comment '部门编号',salary double comment '薪资',constraint ndpk primary key (name,dept_id)
)insert into emp3 VALUES
('111',10,5000),
('112',10,6000);insert into emp3 values
('113',10,7000),
('111',20,8000);insert into emp3 VALUES
('114',10,9000),
('113',20,10000);

注：多个键作为主键时里面列的值不能完全相同，允许其中几个相同，也不允许出现空值

（2）自增长约束

概念：当字段设置为自增长约束后，在插入数据时，不需要用户输入数据，而由数据库系统根据定义自动赋值，每增加一条记录，该字段会以相同的步长进行增长，一般是跟主键一块搭配使用

语法格式：
create table 表名 (...字段名 数据类型 auto_increment...
)

#创建madb1数据库
create database if not exists madb1 charset = utf8;create table t_user1(id int primary key auto_increment,name varchar(20)
);insert into t_user1 VALUES
(NULL,'张三');insert into t_user1 (name) VALUES ('李四');

①特点

a.auto_increment的初始值是1，每新增一条记录，字段值自动嘉1
b.一个表中只能有一个字段使用auto_increment
c.auto_increment约束的字段必须具备NOT NULL 属性
d.支持的类型（TINYINT、SMALLINT、INT、BIGINT）等
e.如果最大值达到上限，auto_increment就会失效

②指定自增字段的初始值

方式1：创建表时指定
语法格式：
creat table 表名(... primary key auto_increment,...
) auto_increment = 10;

#指定自增字段的初始值-方式1
create table t_user2 (id int primary key auto_increment,name varchar(20)
) auto_increment = 100;insert into t_user2 values
(NULL,'aaa'),
(NULL,'bbb');

③delete和truncate区别

delete数据之后自动增长从断点开始
truncate数据之后自动增长是从默认起始值开始

（3）非空约束

①添加非空约束

语法格式：
create table 表名 (字段名 数据类型 not null,...
);

create table t_user6 (id int,name varchar(20) not null,address varchar(20) not null
);insert into t_user6 (id) values (1001); #不可以
insert into t_user6 (id,name,address) values (1001,NULL,NULL); #不可以
insert into t_user6 (id,name,address) values (1001,'NULL','NULL'); #可以
insert into t_user6 (id,name,address) values (1001,'',''); #可以

注：单纯的NULL才表示空值，加上引号会变成字符串

（4）唯一性约束

唯一约束（unique key）是指所有记录中的字段值不能重复，但是唯一约束的列可以为NULL

①添加唯一约束

语法格式：
create table 表名(字段名 字段类型 unique,...
)

create table t_user8 (id int,name varchar(20),phone_number varchar(20) unique
);#测试唯一性约束
insert into t_user8 values
(10,'aaa','13111112222');insert into t_user8 VALUES
(11,'aab','13111112222');insert into t_user8 values
(12,'aac',NULL);insert into t_user8 VALUES
(13,'aad',NULL);insert into t_user8 (id,name) VALUES
(14,'aba');#NULL和任何值都不相同，NULL!=NULL

（5）默认约束

默认值约束用来指定某列的默认值

①添加默认约束

语法格式：
create table 表名(字段名 字段类型 default 默认值,....
)

create table t_user10 (id int,name varchar(20),address varchar(20) default '北京'
);insert into t_user10 (id,name)
values (10,'aac'),(11,'ace');

（6）零填充约束

zerofill：
①插入数据时，当该字段的值长度小于定义的长度时，会在该值的前面补上相应的0
②zerofill默认为int(10)

①添加零填充约束

语法格式：
create table 表名 (字段名 类型 zerofill,...
)

create table t_user11 (id INT zerofill,name varchar(20)
);insert into t_user11 VALUES
(31,'aac'),(32,'ddd');

6、MySQL基本操作-DQL

DQL：Data Query Language，数据查询语言

语法格式：select [all|distinct]<目标列的表达式1> 别名1,<目标列的表达式2> 别名2...
from <表名> 别名,表名 别名
[where 条件表达式]
[group by 列名]
[having 条件表达式]	
[order by 列名 [asc|desc]]
[limit 数字1,数字2]

简化版语法

select *|列名 from 表名 where 条件;

create database if not exists mydb2;

（1）运算符

①算术运算符

运算符	说明
+	加法
-	减法
*	乘法
DIV或/	除法
%或MOD	取模，求余数

select 5+2;select 5-2;select 5*2;select 5/2;
select 5 DIV 2;select 2%5;
select 2 MOD 5;#将每件商品的价格加10元
select pname,price,price+10 from product;#将每件商品的价格上调10%
select pname,price,price*1.1 from product;
select pname,price,price+price*0.1 from product;

注：乘法可能有运算结果不精确问题

②比较运算符

运算符	说明
=	等于
<和<=	小于和小于等于
>和>=	大于和大于等于
<=>	安全的等于
!=或<>	不等于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个参数或者多个参数时，返回最小值
GREATEST	当有两个参数或者多个参数时，返回最大值
BETWEEN AND	判断一个值是否介于两个值之间
IN	判断一个值是否在IN列表中
NOT IN	判断一个值是否不是在IN列表中
LIKE	模糊查询，通配符匹配

#使用least求最小值
select least(10,20); #10
select least(10,30,20); #10
select least(10,null,30); #null#使用greatest求最大值
select greatest(10,20,30); #30
select greatest(10,null,30); #null#1、查询商品名称为'海尔洗衣机'的商品的所有信息
#*代表全部字段
select * from product where pname = '海尔洗衣机';#2、查询商品价格为800的商品
select * from product where price = 800;#3、查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where price not in (800);
select * from product where not (price = 800);#4、查询商品价格大于等于60元的所有商品信息
select * from product where price >= 60; 
select * from product where not (price < 60); #5、查询商品价格在200到1000之间(包含200和1000)的所有商品
select * from product where price >= 200 and price <= 1000;
select * from product where price between 200 and 1000;#6、查询商品价格时200或800的所有商品
#or或||
select * from product where price = 200 or price = 800;
select * from product where price = 200 || price = 800;
select * from product where price in (200,800);