MySql

1、结构化查询语句分类

名称 解释 命令
DDL(数据定义语言) 定义和管理数据对象,如数据库,数据表等 CREATE、DROP、ALTER
DML(数据操作语言) 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL(数据查询语言) 用于查询数据库数据 SELECT
DCL(数据控制语言) 用来管理数据库的语言,包括管理权限及数据更改 GRANT、COMMIT、ROLLBACK

2、控制台操作数据库

1、进入数据库

1、mysql -uroot -p密码 进入数据库 显示密码
2、mysql -uroot -p 回车
密码 进入数据库 不显示密码

2、数据库的基本操作

exit 退出数据库 没进入数据库则关闭dos命令窗口
use 打开
\c 结束一条语句

修改密码:mysqladmin -u用户名 -p旧密码 password 新密码

1、库的基本操作

创建库 create database 库名

查看所有的库 show databases;

删除库:drop database 库名;

查询当前使用的数据库 select database();

查看当前库的版本 select version();

进入库 use 库名;

查看库内的表 show tables

2、表的基本操作

创建表 create table 表名(表内的列及他的约束 (若多条则以逗号分割))

DEFAULT ‘默认值’ 设置默认值

查询该表的约束 desc 表名

修改表名 ALTER TABLE 旧表名 RENAME AS 新表名

删除表 DROP Table 表名

3、字段的基本操作

增加字段 ALTER TABLE 表名 add 字段名 字段的属性

删除字段 Alter table 表名 drop 字段名

修改字段 ALTER TABLE 表名 MODIFY 字段名 列类型 [ 属性 ]

修改字段名 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型 [ 属性】

3、控制约束

1、增加主键约束

语法:

1
alter table 表名 add constraint  约束名 primary key  表名 (主键字段);

2、删除主键约束

语法:

1
alter  table 表名 drop  primary key;

3、增加外键约束

1
alter table 从表 add  constraint  约束名 foreign   key   (从表外键字段)   references  主表  (主键字段);

4、删除外键约束

1
alter table 表名 drop  foreign  key  外键约束名;

4、数据库的CRUD

1、where条件子句

运算符 含义
= 等于
<> 或 != 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围之间
AND 并且
OR
in(2,4) 范围语句,当值等于2或3时
like 模糊查询,可以利用%号匹配0个或多个字符 例:id like ‘%2%’

2、添加数据

俩种语法:

1、insert into 表名 Values (要增加的数据) 必须要全部属性都得输入,否则会报错

2、insert into 表名(选择要增加的数据) Values (要增加的数据)

不能为空的必须要增加,否则会报错并且表名括号后面的数据要跟values后面的一一对应

3、修改数据

1、update 表名 set 要修改的列 =新数据 这样子是全部替换为新数据

2、update 表名 set 要修改的列(逗号分割)=新数据 where 条件语句 满足条件则替换

4、删除数据

1、delete from 表名 这样子则是删除所有的数据

2、delete from 表名 where 条件语句 根据条件语句删除

5、查询数据

1、普通语法

1、select * from 表名

2、select * from 表名 where 条件语句

3、查询结果的列重命名 select 查询的列(多个则以逗号分割) as(也可以用空格代替) 新的名字 from 表名

2、设置查询顺序

升序: select 表内的列 from 表 order by 表内的列 asc

降序: select 表内的列 from 表 order by 表内的列 desc

默认为升序

3、统计函数
函数 作用
sum(字段) 求查询数据该字段的总和
max(字段) 求查询数据该字段的最大值
min(字段) 求查询数据该字段的最小值
avg(字段) 求查询数据该字段的平均值
count 求查询数据的总个数
1
2
3
4
5
6
例:
select SUM(id) from student
select max(id) from student
select min(id) from student
select avg(id) from student
select count(0) from student
4、分组函数

语法:select * from 表名 GROUP BY 字段

1
例:select * from student GROUP BY money
5、Having

利用与分组后筛选

语法:select * from 表名 GROUP BY 字段

1
例:select * from student GROUP BY money Having money>20
6、连接查询
1、内连接

语法:

1
2
select * from 表1 join 表2 on 表1和表2的相同字段恒等式
select * from 表1,表2 where 表1和表2的相同字段恒等式

例:

1
2
select * from student join clazz on student.cid=clazz.id
select * from student,clazz where student.cid=clazz.id
2、外连接

语法:

1
2
select * from 表1 LEFT JOIN 表2 on 表1和表2的相同字段恒等式
select * from 表1 RIGHT JOIN 表2 on 表1和表2的相同字段恒等式

例:

1
2
select * from student LEFT JOIN class on student.class_id=class.cid
select * from student RIGHT JOIN class on student.class_id=class.cid
3、子查询

在一个查询语句中又嵌套了另外一个查询语句,子查询必须包含在一个圆括号内

语法:select * from 表 where cj 条件字句 (子查询)

7、limit 分页

例:

1
select * from student LIMIT 2,3

作用:下标从0开始,下标为2后取3个

8、DISTINCT
1
DISTINCT  去除重复

多表查询时,有可能会有数据重复,这时候我们就要使用去除重复了,直接插入在select后面即可

9、导入数据

作用:根据条件语句,将一个表内查询出来的数据添加到另外一个表内

例:

1
insert into xxx(字段) select StudentName from student;

注意:

  • xxx(字段):要插入数据的表名(字段)
  • StudentName:要跟xxx(字段)一一对应,否则报错

单行处理函数 ifnull(放可能为null 的数据)

查出来的第一个表 union 查出来的第二个表

5、索引

1、唯一索引(unique)

作用:避免同一个表中某数据中的值重复

与主键适应的区别 主键索引只能有一个 唯一索引有多个

2、常规索引(INDEX)

作用:快速定位特定数据

注意:

index和key关键字都可设置常规索引

应加在查找条件的字段

不宜添加太多常规索引,影响数据的插入、删除和修改操作

3、全文索引(FULLTEXT)

作用:快速定位特定数据

注意:

  • 只能用于MyISAM类型的数据表
  • 只能用于 CHAR 、 VARCHAR、TEXT数据列类型
  • 适合大型数据集

4、建表后索引操作

1、添加索引语法

1
ALTER TABLE 表名 ADD  索引类型(数据列名)

2、删除索引

1
2
3
DROP  INDEX 索引名 ON  表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KE

3、查看索引

1
SHOW  INDEX(或KEYS) FROM 表名

6、视图

1、增加视图语法

1
CREATE VIEW s视图名  AS(select 语句)

**2、删除视图语法 **

1
DROP VIEW [IF EXISTS (判断)] 视图名;

7、函数

1、数字函数

函数 作用
abs(x) 返回x的绝对值
ceil(x) 返回大于等于x的最小整数 例:select ceil(1.5) 返回2
floor(x) 返回小于等于x的最大整数 例:select floor(1.)5 返回1
round(x) 四舍五入
rand(x) 返回0-1的随机数
mod(x,y) 返回x除以y的余数J
sqrt(x) 返回x的平方
pow(x,y) 返回x的y次方
format(x,n) 返回x的n位小数,若不是数字则输出0,n位小数

2、字符串函数

函数 作用
char_length(s) 返回s的字符数
length(s) 返回s的字节数
concat(s1,s2…..) 将字符串s1,s2拼接
concat_ws(p,s1,s2…) 将字符串s1,s2拼接用p隔开
upper(s) 将字符串s转换为大写
lower(s) 将字符串s转换为为小写
LTRIM(s) 去除字符串s右边的空格
ririm(s) 去除字符串s右边的空格
trim(s) 去除字符串s左右的空格
substring(s,n) 从n开始输出字符串s 例:select substring(‘2545248’,3) 返回45248
substring(s,n,i) 从n开始输出字符串s的i个 例:select substring(‘2545248’,3,2) 返回45
replace(s,s1,s2) 从字符串s中找到s1用s2替换 例:select replace(‘ectd’,’e’,’c) 返回cctd 没找到则正常输出

3、日期函数

函数 作用
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期时间
adddate(d,n) 返回日期d的n天后
adddate(d,interval n type) 返回日期d的n type(时间类型)后
subdate(d,n) 返回日期d的n天前
subdate(n,interval type) 返回日期d的n type(时间类型)前
date_format(d,f) 将日期根据fg

4、加密函数

函数 作用
password(str) 生成一个经过加密的字符串。
md5(str) 使用md5进行加密(128位)
SHA5() 使用SHA5进行加密(160位)
encode(str,key) 使用key对密码进行加密,可解密
decode(str,key) 使用key对密码进行解密。

5、定义函数

1
2
3
4
5
6
7
CREATE FUNCTION 函数名(参数)
RETURNS 返回类型
BEGIN
内容
return 返回结果
END
必须要返回值

6、删除自定义函数

1
DROP FUNCTION 函数名;

8、流程控制语句

1、if语句

语法:

1
2
3
4
IF 条件 then set  jg=(赋值);
elseif 条件 then set jg=(赋值);
else set jg=(赋值);
end if;

例:

1
2
3
4
IF cj>=90 then set  jg='优秀';
elseif cj>=70 then set jg=’良好‘;
else set jg='一般';
end if;

2、case 语句

1
2
3
4
5
case
when 条件 then set jg='优秀'(赋值);
when 条件 then set jg='一般'(赋值);
......;
end case;

若输出的要求case语句内没有的话,则会报错

3、loop语句

语法:

1
2
3
4
循环名字:loop
内容
end loop 循环名字
可以没有循环名字

leaven 循环名字 用来结束循环 必须要循环名字才可以结束
iterate 循环名字 跳过一次循环 必须要循环名字才可以结束

4、reprat 语句

语法:

1
2
3
4
循环名字 repeat
内容;
unril 结束条件
end reprat;

5、while语句

语法:

1
2
3
while 循环条件 do 
内容 ; 当条件成立则运行内容
end while

9、存储过程

创建存储过程语法:

1
2
3
4
CREATE PROCEDURE 名字(参数) 
BEGIN
存储过程主体
END

运行存储过程语法:

1
call 存储名

存储过程没有返回值,但是我们可以通过参数的方式返回结果,存储过程提供了三种参数类型。

  • IN:输入参数(默认),表示调用时必须制定参数。
  • OUT:输出参数,表示在存储过程中可以改变,并且可以返回。
  • INOUT:输入输出参数:表示可以在调用时传递参数,并在存储过程中改变并返回。

删除存储过程语法:

1
DROP PROCEDURE 过程名

10、异常

1、异常处理

语法:

1
2
3
4
declare 处理方式 handler for 错误代码(或一个标准的sql值)异常处理块
处理方式continue 遇到错误不处理,继续执行
exit 遇到错误马上退出
undo 遇到错误则回滚 mysql暂时不支持

2、命名异常

语法:

1
declare 异常名称 condition for 条件类型

11、触发器

1、创建触发器语法:

1
2
3
create trigger 触发器名称 触发时机  通过什么触发
on 在那张表增加数据
for each row sql语句

2、触发器类型 NEW和OLD的使用

  • INSERT NEW表示新增的数据。OLD在INSERT中不存在。
  • UPDATE OLD表示原始表的数据,NEW表示将要更新的数据。
  • DELETE OLD表示原始表的数据。NEW在DELETE中不存在。

3、查看触发器语法

1
show triggers;

4、删除触发器语法

1
drop trigger 触发器名