mysql 的使用笔记 适合已经学过的

Mysql使用笔记

数据类型

数据形式	描述
varchar （1-65535）	变长字符串
char（1-255）	定长字符串
text 0-65535个字符（smalltext,tinytext,longtext）	文本数据
int或integer 范围 (0，4 294 967 295)	大整数值
bigint 范围(0，18 446 744 073 709 551 615)	极大整数值
decimal（m,d）	小数值
date	日期值（年 月 日）
datetime	日期值（年 月 日 时间）
enum(“选项1”，“选项2”，“选项3”……)	单选项字符串数据类型
set(“选项1”，“选项2”，“选项3”……)	多选字符串的数据类型

数据约束

约束形式	作用
NOT NULL	数据非空
primary key	主键
unique key	唯一性约束
列名 foreign key references 表名（列名）	外键约束
default 默认值	默认值约束
无check约束

列名后添加约束

#创建一张学生信息表
CREATE TABLE stuinfo( 
#字段Id数字类型，为主键  
    id INT PRIMARY KEY,#主键
#字段学生名字符类型字符个数最长20，非空且唯一
    stuName VARCHAR(20) NOT NULL ,#非空
#性别字段
    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查mysql中不生效
#座位号数字类型，唯一
    seat INT UNIQUE,#唯一
#年龄数字类型，默认值是18
    age INT DEFAULT  18,#默认约束
#课程编号，外键
    majorId INT REFERENCES major(id)#外键
#创建一张课程信息表
CREATE TABLE major(
#课程ID主键
    id INT PRIMARY KEY,
#课程名称字符个数最长20
    majorName VARCHAR(20)
);

表内加约束

CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键

表外加约束

CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); #4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

常用函数

数字函数

函数使用形式	介绍
CEIL(x)/CEILING(x)	返回大于或等于 x 的最小整数
FLOOR(x)	返回小于或等于 x 的最大整数
ABS(x)	返回x的绝对值
AVG(expression)	返回一个表达式的平均值，expression 是一个字段
EXP(x)	返回 e 的 x 次方
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值
MAX(expression)	返回字段 expression 中的最大值
MIN(expression)	返回字段 expression 中的最大值
POW(x,y)/POWER(x,y)	返回 x 的 y 次方
RAND()	返回 0 到 1 的随机数
ROUND(x)	四舍五入 取整
SUM(expression)	返回指定字段的总和

字符串函数

函数使用形式	介绍
ASCII(‘AB’)	返回字符串 s 的第一个字符的 ASCII 码
LENGTH/CHAR_LENGTH(s)/CHARACTER_LENGTH(s)	返回字符串 s 的字符数
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置
INSERT(s1,x,len,s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
LOCATE(s1,s)	从字符串 s 中获取 s1 的开始位置
LCASE(s)/LOWER(s)	将字符串 s 的所有字母变成小写字母
UCASE(s)/UPPER(s)	将字符串 s 的所有字母变成大写字母
TRIM(s)	去掉字符串 s 开始和结尾处的空格
LTRIM(s)	去掉字符串 s 开始处的空格
RTRIM(s)	去掉字符串 s 结尾处的空格

日期函数

函数使用形式	介绍
CURDATE()/CURRENT_DATE()	返回当前日期
CURRENT_TIMESTAMP()	返回当前日期和时间
DATE()	字符串转化为时间DATE(“2017-06-15 11:11:16”);
DATEDIFF(d1,d2)	相隔天数
DATE_FORMAT	按表达式 f的要求显示日期 d
DAYOFMONTH(d)	计算日期 d 是本月的第几天
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期 SELECT ADDDATE(“2017-06-15”, INTERVAL 10 DAY); ---- 返回2017-06-25
FROM_UNIXTIME()	时间戳转日期
UNIX_TIMESTAMP()	得到时间戳

mysql高级函数

函数使用形式	介绍
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
CONV(x,f1,f2)	返回 f1 进制数变成 f2 进制数

创建 表 视图

创建表格

CREATE TABLE tab_student(
    ID VARCHAR(20),
    Score INT
);

创建视图

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

多表连接

自由连接（笛卡尔积） 表一 join 表二 on 约束

左连接 from 左表 left join 右表 on 左表.字段 = 右表.字段

右连接 from 左表 right join 右表 on 左表.字段 = 右表.字段

sql

先执行集合条件语句 再执行where 中。

where （条件语句）< > = in

having （集合条件语句）group by

增删改查案例

增加

【插入单行】
insert [into] <表名> (列名) values (列值)
例：insert into Strdents (姓名,性别,出生日期) values (‘开心朋朋’,‘男’,‘1980/6/15’)

【将现有表数据添加到一个已有表】
insert into <已有的新表> (列名) select <原表列名> from <原表名>
例：insert into tongxunlu (‘姓名’,‘地址’,‘电子邮件’)
select name,address,email
from Strdents

【使用union关键字合并数据进行插入多行】
insert <表名> <列名> select <列值> tnion select <列值>
例：insert Students (姓名,性别,出生日期)
select ‘开心朋朋’,‘男’,‘1980/6/15’ union（union表示下一行）
select ‘蓝色小明’,‘男’,‘19**//’

删除

【删除<满足条件的>行】
delete from <表名> [where <删除条件>]
例：delete from a where name=‘开心朋朋’（删除表a中列值为开心朋朋的行）

【删除整个表】
truncate table <表名>
truncate table tongxunlu
注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能用语有外建约束引用的表

修改

update <表名> set <列名=更新值> [where <更新条件>]
例：update tongxunlu set 年龄=18 where 姓名=‘蓝色小名’

查找

精确（条件）查询
select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

【查询部分行列–条件查询】
例：select i,j,k from a where f=5
说明：查询表a中f=5的所有行，并显示i,j,k３列