1 语法基础
1.1 DDL(数据定义语句)
create table/database
alter table/database
drop table/database
1.2 DML(数据管理语句)
insert
delete
update
select
1.3 注释： –
1.4 常见类型
int
char --定长字符
varchar --可变字符
date --日期
datetime --日期加时间
2 数据库操作
# 创建数据库
create database 数据库名
# 查看数据库
show databases;
show create database database_name \G
show create database db_test;
# 删除数据库
drop database database_name;
3 表操作
创建表
create table [if not exists] 表名(
id int not null auto_increment primary key comment'主键字段',
username char(64) comment'用户名' default'root',
password varchar(64) comment'密码'
)engine=myisam charset=utf8mb4;
or
create table 数据库名.表名(
id int not null auto_increment primary key comment'主键字段',
username char(64) comment'用户名' default'root',
password varchar(64) comment'密码'
)engine=myisam charset=utf8mb4;
说明：
1.null|not null 字符是否为空
2. default 默认值
3.auto_increment 自动增长
4.primary key 设为主键
5.engine 表的存储引擎(innodb | myisam)
常用引擎
innodb,myisam不同的储存引擎就是储存数据的格式不一样.
IO模型,是针对硬盘的 curd insert->create update read->select delete
I 写 innodb 在写的时候更加的有优势 del update insert
o 读 myisam 读的时候更加的有优势 select
中文乱码问题
# windows默认的是GBK,linux默认是utf8
set names gbk
set names utf8
显示表
显示所有表
show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
显示表结构
desc 表名;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | char(64) | YES | | root | |
| password | varchar(64) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
show create table 表名\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键字段',
`username` char(64) DEFAULT 'root' COMMENT '用户名',
`password` varchar(64) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
show create table 表名;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键字段',
`username` char(64) DEFAULT 'root' COMMENT '用户名',
`password` varchar(64) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除表
drop table [if exists] `表1`,`表2`;
更改表
改表名
alter table `old_name` rename `new_name`;
增加表字段
alter table `表名` add `字段名` 数据类型;
alter table user2 add age int(3) first; #添加在表的第一个
alter table user2 add height int(3) after age; #添加字段在指定字段的后面
修改字段属性
alter table `表名` modify `属性名(字段名)` 数据属性;
修改字段名/属性/字段位置
alter table `表名` change `原字段名` `新的字段名` [数据属性] [ after '字段名'];
删除字段
alteer table `表名` drop `字段名`;
修改表引擎
alter table `表名` engine=innodb|myisam;
移动表
alter table `原表名` rename to 其它数据库名.指定表名;
复制表
create table `新表` select * from `原来的表`;
特点:
1.旧表的数据会一起复制过来到新表中
2.不能复制主键
create table `新表` like `原来的表`;
特点:
1.它可以复制主键
2.但是不会复制数据
数据操作
数据的插入
1.insert into user(`id`,`age`,`sex`) values('',18,'tom');
# 值的个数要和字段数一致
2.insert into user(`age`,`sex`) values(20,'jack');
# id为自动增长字段,可以不写
3.insert into user values(null,25,'lily');
# 不写字段名,要一一对应字段个数
4.insert into user values(null,30,'lyb'),
(null,40,'wj'),(null,50,'ybq');
#一次插入多条
5.insert into user set age=60,sex='hx';
#一次只能插入一条数据
数据更新
update user set sex='pgone' where id=8;
#update 表名 set 字段名='值',字段名=123 where 字段(一般使用主键)=值;
update user set sex='贾乃亮',age=40 where id=5;
#修改多个字段
数据删除
delete from user where id=8;
#delete from 表名 where 字段(一般为主键)=值
#一条一条的删
delete from user where True;
#删除全部
#它也是一条一条的删
truncate user;
#记录你原来建表语句,然后删除整个表,在创建,数据全部被清空
查询语句
select 字段(结果集) from 表名(数据源) [where 条件] [group by 分组][having 条件][order by 排序 asc|desc][limit限制 s,n]
字段表达式
select 查询也可以用来做计算
--显示系统时间戳
select unix_timestamp();
--显示随机数
select rand();
dual表
dual表不是一个实实在在存在的表,它是为了保证select语句的完整性的.
eg：
mysql> select rand() from dual;
+--------------------+
| rand() |
+--------------------+
| 0.7353704960414865 |
+--------------------+
1 row in set (0.00 sec)
is null/is not null
筛选数据为空或不为空
select * from stumarks where ch is null or math is null;
between / not between
筛选指定范围内的数据
mysql> select * from stumarks where ch between 75 and 100;
+-------+------+------+
| stuno | ch | math |
+-------+------+------+
| 1 | 88 | 99 |
| 2 | 89 | 100 |
| 5 | 100 | 99 |
| 6 | 96 | 91 |
+-------+------+------+
4 rows in set (0.00 sec)
mysql> select * from stumarks where ch not between 75 and 100;
+-------+------+------+
| stuno | ch | math |
+-------+------+------+
| 3 | 67 | 76 |
| 4 | 50 | 59 |
+-------+------+------+
2 rows in set (0.00 sec)
运算符
a.算数运算符
+ - * / % ++ --
b.比较运算符
> < >= <= (<> !=)不等于
c.逻辑运算符
and 与
or 或
not 非
通配符
_ #匹配一个字符
% #匹配所有
聚合函数
sum() #求某列的和
avg() #求某列的平均值
max() #最某列的大值
min() #最某列的小值
count() #统计某列的行数
模糊查询
mysql> select * from stuinfo where sname like '%立%';
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
+-----+--------+------+------+--------+
1 row in set (0.00 sec)
mysql> select * from stuinfo where sname like '_力';
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 4 | 小力 | 男 | 20 | 天津 |
+-----+--------+------+------+--------+
1 row in set (0.00 sec)
分组查询 – group by
将查询的结果分组显示,分组的目的在于方便统计.
mysql> select * from stuinfo;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 3 | 小强 | 男 | 22 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | 小芳 | 女 | 20 | 天津 |
+-----+--------+------+------+--------+
mysql> select sname,sex,age,city from stuinfo group by city;
+--------+------+------+--------+
| sname | sex | age | city |
+--------+------+------+--------+
| 小京 | 男 | 20 | 北京 |
| 小力 | 男 | 20 | 天津 |
| 张立 | 女 | 18 | 宿州 |
| 小丽 | 女 | 21 | 重庆 |
+--------+------+------+--------+
#每个结果只显示一个
#group_concat()可以将同一组的字段连接在一起
mysql> select group_concat(sname) as sname ,group_concat(sex) as sex,group_concat(age) as age,city from stuinfo group by city;
+---------------+---------+-------+--------+
| sname | sex | age | city |
+---------------+---------+-------+--------+
| 小京,小强 | 男,男 | 20,22 | 北京 |
| 小力,小芳 | 男,女 | 20,20 | 天津 |
| 张立 | 女 | 18 | 宿州 |
| 小丽 | 女 | 21 | 重庆 |
+---------------+---------+-------+--------+
4 rows in set (0.00 sec)
#安数字分组,会按照升序排列
mysql> select * from stuinfo group by age;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 3 | 小强 | 男 | 22 | 北京 |
+-----+--------+------+------+--------+
4 rows in set (0.00 sec)
条件过滤 – having/where
where:设置查询的条件,字段必须是存在的
having: 设置查询的条件,条件字段必须在结果集中;
#having 的查询效率要高于where
select * from stuinfo where age>20; #正确
select * from stuinfo having age>20; #正确
select sname from stuinfo where age>20; #正确
select sname from stuinfo having age>20; #错误,原因age不在结果集中(没有查询age这个字段)
select sname,age from stuinfo having age>20; #正确
排序 – order by
# 升序
mysql> select * from stuinfo order by age;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 6 | 小芳 | 女 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 3 | 小强 | 男 | 22 | 北京 |
+-----+--------+------+------+--------+
6 rows in set (0.09 sec)
# 降序
mysql> select * from stuinfo order by age desc;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 3 | 小强 | 男 | 22 | 北京 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 2 | 小京 | 男 | 20 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 6 | 小芳 | 女 | 20 | 天津 |
| 1 | 张立 | 女 | 18 | 宿州 |
+-----+--------+------+------+--------+
6 rows in set (0.00 sec)
分页 – limit
limit 起始位置,显示长度
#第一个不写默认从1开始
mysql> select * from stuinfo limit 3;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 3 | 小强 | 男 | 22 | 北京 |
+-----+--------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from stuinfo limit 3,3;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 4 | 小力 | 男 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | 小芳 | 女 | 20 | 天津 |
+-----+--------+------+------+--------+
3 rows in set (0.00 sec)
查询选项
all :和 * 是一个意思,表示查询所有的
distinct: 去除重复的数据
mysql> select distinct city from stuinfo;
+--------+
| city |
+--------+
| 宿州 |
| 北京 |
| 天津 |
| 重庆 |
+--------+
4 rows in set (0.01 sec)
insert into … select …
#选择一个表中的数据插入到另一个表中(两个表结构要一致)
insert into stuinfoo select * from stuinfo;
union联合查询
union:
1.all # 显示全部记录
2.distinct # (去除重复的值 他是默认)
Union的要求:
1.两边的select语句的字段数要一致
2,字段名可以不一样,最终按照第一个select语句的字段名返回
3.两边可以具有相同或不同的数据类型
mysql> select * from stuinfo having city='北京' and sex='男' union all select * from stuinfoo having city='天津' and sex='女';
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 2 | 小京 | 男 | 20 | 北京 |
| 3 | 小强 | 男 | 22 | 北京 |
| 6 | 小芳 | 女 | 20 | 天津 |
+-----+--------+------+------+--------+
mysql> (select * from stuinfo having sex=1 order by age desc limit 1000) union all (select * from stuinfoo having sex=2 order by age limit 1000);
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 3 | 小强 | 男 | 22 | 北京 |
| 2 | 小京 | 男 | 20 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 1 | 张立 | 女 | 18 | 宿州 |
| 6 | 小芳 | 女 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
+-----+--------+------+------+--------+
6 rows in set (0.01 sec)
多表查询
分类
1.内连接
2.外连接
a.左外连接
b.右外连接
3.交叉连接
4.自然连接
内连接
mysql> select * from stuinfo inner join stumarks on stuinfo.sid=stumarks.stuno;
+-----+--------+------+------+--------+-------+------+------+
| sid | sname | sex | age | city | stuno | ch | math |
+-----+--------+------+------+--------+-------+------+------+
| 1 | 张立 | 女 | 18 | 宿州 | 1 | 88 | 99 |
| 2 | 小京 | 男 | 20 | 北京 | 2 | 89 | 100 |
| 3 | 小强 | 男 | 22 | 北京 | 3 | 67 | 76 |
| 4 | 小力 | 男 | 20 | 天津 | 4 | 50 | 59 |
| 5 | 小丽 | 女 | 21 | 重庆 | 5 | 100 | 99 |
| 6 | 小芳 | 女 | 20 | 天津 | 6 | 96 | 91 |
+-----+--------+------+------+--------+-------+------+------+
6 rows in set (0.00 sec)
# inner 可以不用写
# 给表取别名的 as 也可以省略不写
mysql> select * from stuinfo a join stumarks b on a.sid=b.stuno;
+-----+--------+------+------+--------+-------+------+------+
| sid | sname | sex | age | city | stuno | ch | math |
+-----+--------+------+------+--------+-------+------+------+
| 1 | 张立 | 女 | 18 | 宿州 | 1 | 88 | 99 |
| 2 | 小京 | 男 | 20 | 北京 | 2 | 89 | 100 |
| 3 | 小强 | 男 | 22 | 北京 | 3 | 67 | 76 |
| 4 | 小力 | 男 | 20 | 天津 | 4 | 50 | 59 |
| 5 | 小丽 | 女 | 21 | 重庆 | 5 | 100 | 99 |
| 6 | 小芳 | 女 | 20 | 天津 | 6 | 96 | 91 |
+-----+--------+------+------+--------+-------+------+------+
6 rows in set (0.00 sec)
# inner join数据不完整就不显示
大坑别踩:
表连接肯定经常要用,如果你查询单条数据,而这条数据的从表信息不完整,使用内链接,查询出的结果为空,
但是,python代码的写法是一致的,为了避免python报错,基本不使用内链接.
外连接
left join
#以左边的表为准,右边表中没有的记录用null表示
mysql> select * from users a left join stuinfo b on a.uid=b.sid;
+-----+--------+------+--------+------+------+--------+
| uid | name | sid | sname | sex | age | city |
+-----+--------+------+--------+------+------+--------+
| 1 | 鸡哥 | 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小昌 | 2 | 小京 | 男 | 20 | 北京 |
| 3 | 大恒 | 3 | 小强 | 男 | 22 | 北京 |
| 4 | 小智 | 4 | 小力 | 男 | 20 | 天津 |
| 5 | 骚男 | 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | a | 6 | 小芳 | 女 | 20 | 天津 |
| 7 | b | NULL | NULL | NULL | NULL | NULL |
+-----+--------+------+--------+------+------+--------+
7 rows in set (0.00 sec)
right join
##以右边的表为准,左边表中没有的记录用null表示
mysql> select * from users a right join stuinfo b on a.uid=b.sid;
+------+--------+-----+--------+------+------+--------+
| uid | name | sid | sname | sex | age | city |
+------+--------+-----+--------+------+------+--------+
| 1 | 鸡哥 | 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小昌 | 2 | 小京 | 男 | 20 | 北京 |
| 3 | 大恒 | 3 | 小强 | 男 | 22 | 北京 |
| 4 | 小智 | 4 | 小力 | 男 | 20 | 天津 |
| 5 | 骚男 | 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | a | 6 | 小芳 | 女 | 20 | 天津 |
+------+--------+-----+--------+------+------+--------+
6 rows in set (0.00 sec)
子查询
例题:查询数学成绩大于80分的学生
#普通的查询
mysql> select a.sname,b.math from stuinfo a right join stumarks b on a.sid=b.stuno where b.math>80;
+--------+------+
| sname | math |
+--------+------+
| 张立 | 99 |
| 小京 | 100 |
| 小丽 | 99 |
| 小芳 | 91 |
+--------+------+
4 rows in set (0.00 sec)
# 子查询
mysql> select sname from stuinfo where sid in (select stuno from stumarks where math>80);
+--------+
| sname |
+--------+
| 张立 |
| 小京 |
| 小丽 |
| 小芳 |
+--------+
4 rows in set (0.00 sec)
# 查找数学分数最高的学生
# 子查询
mysql> select sname from stuinfo where sid=(select stuno from stumarks where math=(select max(math) from stumarks ));
+--------+
| sname |
+--------+
| 小京 |
+--------+
1 row in set (0.00 sec)
mysql> select a.sname,b.math from stuinfo a left join stumarks b on a.sid=b.stuno where b.math=(select max(math) from stumarks );
+--------+------+
| sname | math |
+--------+------+
| 小京 | 100 |
+--------+------+
1 row in set (0.00 sec)
exists / not exists
如果有人math超过100分,就显示所有学生的信息
select * from stuinfo where exists(select * from stumarks where math>=100);
#成绩未达到100分就显示
select * from stuinfo where not exists(select * from stumarks where math>=100);
子查询分类
# 找出语文成绩最高的男生和女生:
mysql> select a.sname,b.ch from stuinfo a left join stumarks b on a.sid=b.stuno where b.ch in (select max(c.ch) from stumarks c left join stuinfo d on c.stuno=d.sid group by sex );
+--------+------+
| sname | ch |
+--------+------+
| 小京 | 89 |
| 小丽 | 100 |
+--------+------+
2 rows in set (0.00 sec)
字符编码
1、MySQL可以在服务器上、数据库、表、字段上指定字符编码
2、在服务器上指定字符编码是安装服务器的时候指定的
3、数据库、表、字段上是创建时候指定的
create table test(
name varchar(10) charset utf8
)charset=gbk;
gbk,gb2312,utf8的区别
这三种都是常用的字符编码方式，gbk和gb2312(自带)是简体中文的编码，utf8是国际通用编码。
Python、MySQL、jQuery等等都是开源的产品，开源产品建议使用utf8编码
gbk和gb2312都是简体中文，但是内部编码方式是不一样的。
在python中
在gbk或gb2312下，一个中文占用2个字节,在utf8下，一个中文占用3个字节
注意:
在数据库中如果字符编码是utf8的，一个汉字字符长度就是1，gbk一个汉字的字节长度是2。
create table `emp1`(
sex varchar(16383)
)engine=myisam charset=utf8mb4;
65535/3-1
create table `emp2`(
sex varchar(32766)
)engine=myisam charset=gbk;
65535/2-1
*数据库如果是UTF8,那么你在此库中建立的’表’如果没有设定字符集,默认使用数据库的字符集
在传输的时候字符编码
mysql服务器接受的编码
mysql> show variables like 'character_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 # 服务器接受客户端的编码 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 # 返回字符的编码 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
现在客户端发送的编码和服务器接受的编码不一致
解决:
windows默认的是GBK
````set character_set_client=gbk;```
字符编码不一样返回的就不一样
返回的是utf8 windows不认识会乱码
解决:
```set character_set_results=gbk;``
小技巧:
set names gbk; -> set names 可以一次设置修改3个字符编码;
mysql> show variables like 'character_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
校对集
在某种字符集下，字符和字符的关系成为校对集。比如(ASCII)a和B的大小关系，如果区分大小写a>B，如果不区分大小写a<B
不同的校对集的比较规则不一样。
在定义表的时候可以指定校对集
#utf8_general_ci 使用_ci这种校对集不区分大小写
create table t1(
name char(1)
)charset=utf8 collate=utf8_general_ci;
create table t2(
name char(1)
)charset=utf8 collate=utf8_bin;
insert into t1 values ('a'),('B');
insert into t2 values ('a'),('B');
mysql> select * from t1 order by name;
+------+
| name |
+------+
| a |
| B |
+------+
2 rows in set (0.00 sec)
#排序查询以后没有区分大小写说明a和b不区分大小写;
mysql> select * from t2 order by name;
+------+
| name |
+------+
| B |
| a |
+------+
2 rows in set (0.00 sec)
#按照正序排列,对照ASCLL编码,B<a,B在a前面那么这种二进制校对集区分大小写;
牢记:
_bin：按二进制编码比较
_ci：不区分大小写比较
#显示所有的字符集
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
#显示所有校对集
mysql> show collation;
数据类型
| 名称 | 类型 | 说明 |
|---|---|---|
| INT | 整型 | 4字节整数类型，范围约+/-21亿 |
| BIGINT | 长整型 | 8字节整数类型，范围约+/-922亿亿 |
| REAL | 浮点型 | 4字节浮点数，范围约+/-1038 |
| DOUBLE | 浮点型 | 8字节浮点数，范围约+/-10308 |
| DECIMAL(M,N) | 高精度小数 | 由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算 |
| CHAR(N) | 定长字符串 | 存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串 |
| VARCHAR(N) | 变长字符串 | 存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串 |
| BOOLEAN | 布尔类型 | 存储True或者False |
| DATE | 日期类型 | 存储日期，例如，2018-06-22 |
| TIME | 时间类型 | 存储时间，例如，12:20:59 |
| DATETIME | 日期和时间类型 | 存储日期+时间，例如，2018-06-22 12:20:59 |
整型
| 整型 | 占用字节 | 范围 |
|---|---|---|
| tinyint | 1 | -2 ^7 ~ 2^7-1 (-128~127) |
| smallint | 2 | -2 ^15 ~ 2^15-1 (-32768~32765) |
| mediumint | 3 | -2 ^23 ~ 2^23-1 (-8388608~8388607) |
| int | 4 | -2 ^31~ 2^31-1 (-2147483648~2147483647) |
| bigint | 8 | -2 ^63 ~ 2^63-1 (太大了) |
unsigned (无符号)
一个数是无符号数，那么这个数肯定是非负数
数据库mysql第一条记录一定是1(起码是),绝对不能是0
tinyint unsigned 2^8-1
无符号数的范围相当于是有符号数的两倍。
mysql> create table test1(
-> age tinyint unsigned
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (128);
Query OK, 1 row affected (0.00 sec)
显示宽度
整形支持显示宽度，显示宽度就是最小的显示位数，比如int(11)表示最少用11
位数字表示这个数，如果不够用0来做前导。默认情况下显示位数不起作用，必须集合zerofill才起作用
create table stu(
id int(5),
age int(5) zerofill
);
#插入测试数据
insert into stu values (1,23),(2,123456);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu;
+------+--------+
| id | age |
+------+--------+
| 1 | 00023 |
| 2 | 123456 | #超过显示宽度5就显示值本身
+------+--------+
显示宽度不决定整型的显示大小，只是在值的位置不够的时候用前导0来填充，如果超过显示位数，就不加前导0.显示本身.
浮点型
| 浮点型 | 占用字节 | 范围 |
|---|---|---|
| float | 4 | -3.4E+38 ~ 3.4E+38 |
| double | 8 | -1.8E+308 ~ 1.8E+308 |
create table test2(
num1 float,
num2 double
);
insert into test2 values(23.123,2.123);
#float理论上只保留小数点一位,根据实际版本
#double理论上只保留小数点两位, 根据实际版本
insert into test2 values(2.2E2,2.123);
#也支持科学计数法
insert into test2 values(999.999999999999999999999999999,2.123456);
#丢失精度
python中我们没有双精度这一类型,float代表浮点数.
定点数
decimal(M,D),M的最大值是65,D的最大值是30,默认是(10,0)
create table test3(
num1 float(10,2),
num2 decimal(10,2)
);
insert into test3 values(123.57,1234567.00);
mysql> select * from test3;
+-------------+-------------+
| num1 | num2 |
+-------------+-------------+
| 12345679.00 | 123.57 |
| 12345679.00 | 12346623.57 |
+-------------+-------------+
2 rows in set (0.00 sec)
#定点数可以保留多个小数点位,它在有些情况下也会失去精度.浮点数的执行效率要高于定点数.
#浮点数和定点数都支持 无符号.
字符类型
| 数据类型 | 描述 |
|---|---|
| char | 定长(255) |
| varchar | 可变长度(65535) |
| tinytext | 2^8-1 (255) |
| text | 2^16-1 (65535) |
| mediumtext | 2^24-1 |
| longtext | 2^32-1 |
char(10)和varchar(10)
相同点:都是不能超过10个字符
不同点:char你输入5个字符,它还是占用了10个字符
varchar你输入5个字符,他会返还多于的空间
char的最大长度是255
varchar的理论长度是65535
实际上达不到,我使用的是gbk(mysql中占用2个字符),那么65535/2.
create table test(
name varchar(32766)
)charset=gbk;
#utf8在创建的时候报错了,最多支持21845个字符,因为utf8一个汉字要使用3个字符
create table test(
name varchar(65535)
)charset=gbk
#utf8在创建的时候报错了,最多支持32767个字符,因为gbk一个汉字要使用2个字符
枚举(enum)
列出一些选项的,单选.
create table test(
name varchar(32),
sex enum('男','女','保密')
);
insert into test values('贾乃亮','男');
#插入正常
insert into test values('PGONE','人妖');
#插入报错
枚举型在数据库内部是通过整型来管理的,第一个值1,第二个值是2,依次向后推
insert into test values('王宝强',1);
insert into test values('刘运斌',3);
枚举型的优点:
1.限制值
2.节省空间
3.运行效率高
思考:已知枚举型占用2个字节,请问最多可以设置多少个枚举值?
2个字节16位,2^16-1,最多可保存65535/2个值
集合(set)
列出数据类型,保存多选
create table test2(
name varchar(32),
hobby set('吃','睡觉','看书','鉴黄')
);
insert into test2 values('刘运斌','吃,睡觉,鉴黄');
insert into test2 values('刘运斌-2','吃,睡觉,李小璐');
集合和枚举一样,为每个元素分配一个固定的值,分配方式不一样.
它的管理也是使用整型
集合是这么分配的:
'吃','睡觉','看书','鉴黄'
2^0 2^1 2^2 2^3 如果后面还有依次向后推
保存的时候,把多个值(转换为整型了)加起来,这样值会边的很大(浪费资源)
一个集合他的元素通过一个位表示,有几个集合元素就需要几个位.
思考:已知集合占用8个字节,那么集合能保存多少个选项?
只能保存64个选项.
日期时间型
| 数据类型 | 描述 |
|---|---|
| date | 日期,占用8个字节 |
| time | 时间 |
| datetime | 日期时间 |
| year | 年份,占用一个字节 |
| timestamp | 时间戳,占用4个字节 |
(1)datetime
#格式 年-月-日 时:分:秒
create table test4(
create_at datetime
);
insert into test4 values('2018-01-12 15:00:53');
insert into test4 values(now());#表示的是当前的时间
insert into test4 values('10000-12-31 23:59:59'); #错误
(2)date
仅表示时间的日期部分
(3)time
表示时间部分,也表示时间间隔,范围是-838:59:59 ~ 838:59:59
create table test5(
create_at time
);
insert into test5 values('12:12:12');
insert into test5 values('212:12:12');
insert into test5 values('-800:12:12');
insert into test5 values('-839:59:59'); #错误的,超出范围
#time支持以天的方式表示时间间隔
insert into test5 values('10 12:59:59');
(4)timestamp
从1970年1月1日 00:00:00秒的格林威治时间开始计算,在python中是特殊类型,但在mysql中显示为datatime格式
create table test6(
create_at timestamp
);
insert into test6 values(now());
insert into test6 values('2018-01-12 15:43:30');
insert into test6 values('2038-01-19 11:14:07');#这里就是一个节点
insert into test6 values('2038-01-19 11:14:08');#超过了4个字节
timestamp在格式上和datetime是一样的,它们的区别在于:
datetime 从1到9999,而timestamp从1970年~2038年.
timestamp占用4个字节,到2038年超过4个字节的长度了.
(5)year
占用一个字节,只能是255个数,以1900年为基数,范围1900+1 ~ 1900+255.
create table test7(
create_at year
);
insert into test7 values(1900);#错误的
insert into test7 values(1901);
insert into test7 values(2155);
insert into test7 values(2156);#错误的,不在范围内
布尔型(boolean)
mysql不支持布尔型,所以用1和0代替.
create table test8(
num boolean
);
insert into test8 values(True);
insert into test8 values(1);
insert into test8 values(False);
insert into test8 values(0);
insert into test8 values('A');#错误
布尔型的使用是比较少的,可以用tinyint代替,或者用enum代替
列属性
null/not null # 是否能为空
default # 设置默认值
primary key # 标识主键
auto_increment # 自增
unique # 要求唯一不能重复
comment # 添加备注
外键 – foreign key
外键：
从表的公共字段
特点：
主表中不存在的记录从表中不能插入
从表存在记录主表中不能先删除
必须先删除从表再删除主表
eg：
# 学生表(主表)
create table stu(
id int auto_increment,
name varchar(138) not null,
phone varchar(13) not null,
email varchar(128) default "",
primary key(id)
)engine=innodb;
# 成绩表(从表)
create table stumarks(
id int auto_increment primary key,
stuno int,
math float,
english float,
foreign key (stuno) references stu(id)
)engine=innodb;
#修改表的时候添加一个外键
修改 表 成绩表 增加 外键 学号 关联 学生信息表的id字段
alter table stumarks add foreign key (stuno) references stuinfo(id);
#当主表删除 从表设置为空(指的是外键字段)
#on delete set null
#当主表删除 从表中整条数据跟着删除
#on delete set cascade
#当修改主表时 从表的关联字段也跟着修改
#on update cascade
alter table stumarks add foreign key (stuno) references stuinfo(id) on delete set null on update cascade;
mysql> show create table stumarks;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stumarks | CREATE TABLE `stumarks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stuno` int(11) DEFAULT NULL,
`math` float DEFAULT NULL,
`english` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `stuno` (`stuno`),
CONSTRAINT `stumarks_ibfk_1` FOREIGN KEY (`stuno`) REFERENCES `stu` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# CONSTRAINT `stumarks_ibfk_1`:是mysql分配给外键的名字
# 指定一个名字给外键(添加一个索引)
alter table stumarks add CONSTRAINT `stuno` foreign key (stuno) references stuinfo(id) on delete cascade on update cascade;
# 删除外键
alter table stumarks drop foreign key stumarks_ibfk_1;
# 提醒:
从表的关联(外键)字段一定不能是从表的主键
如果要在某个字段上添加外键,这个字段必须有索引才可以,
如果这个字段没有索引就直接添加外键,那么mysql会自动创建索引.
外键的使用
1.严格约束(外键约束),保证引用的完整性
2.置空操作(set null):主表记录删除或者更新,从表的外键字段设置为null
3.联级操作(cascade): 主表的记录删除或者更新,从表外键字段一起发生变化
4.一般都是删除的时候外键字段置空,修改的时候更新关联
外键在数据量特别小的时候会用上,一般是开发者自己清楚数据库表的设计,
你知道哪个是主表,哪个是从表,然后手动修改,使用'事务',为什么?
1.mysql表中设置外键会影响效率.
2.一般来说,每个从表都是单独数据,需要用到单独操作.
数据库中的关系
一对一
一对多
多对多
三范式：
第一范式:(1NF)确保每一列的数据原子化(不可在分割)
第二范式:基于第一范式(1NF),非主键字段必须依赖主键字段(一个表只描述一件事情)
第三范式:必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）；
视图
1.视图是一张虚拟的表,他表示一张表的部分数据或多张表的综合数据,
视图的结构是建立在表的基础上
2.视图中没有数据,只有表结构,视图中的数据在基表中获取
3.一张表可以创建多个视图,一个视图可以引用多张表
创建视图
语法
create [or replace] view `视图名`
as
sql语句
eg：
# 创建视图
create view stu_view_1 as select sid,sname,age,sex,city,ch,math from stuinfo a left join stumarks b on a.sid=b.stuno;
#查看视图
mysql> select * from stu_view_1;
+-----+--------+------+------+--------+------+------+
| sid | sname | age | sex | city | ch | math |
+-----+--------+------+------+--------+------+------+
| 1 | 张立 | 18 | 女 | 宿州 | 88 | 99 |
| 2 | 小京 | 20 | 男 | 北京 | 89 | 100 |
| 3 | 小强 | 22 | 男 | 北京 | 67 | 76 |
| 4 | 小力 | 20 | 男 | 天津 | 50 | 59 |
| 5 | 小丽 | 21 | 女 | 重庆 | 100 | 99 |
| 6 | 小芳 | 20 | 女 | 天津 | 96 | 91 |
+-----+--------+------+------+--------+------+------+
6 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| stu_view_1 |
| stuinfo |
| stuinfoo |
| stumarks |
| stumarkss |
+-------------------+
5 rows in set (0.00 sec)
修改视图
alter view stu_view_1
as
select * from stuinfo;
#使用视图,大大的增加了查询的效率
删除视图
drop view stu_view_1;
使用视图查询
# 找出数学成绩最高的男生和女生:
mysql> select sname, max(math) from stu_view_1 group by sex;
+--------+-----------+
| sname | max(math) |
+--------+-----------+
| 小京 | 100 |
| 张立 | 99 |
+--------+-----------+
事物
什么是事务?
1.事务是一个不可拆分的工作单元;
2.事务是作为一个整体向系统提交的,要么一起执行,要么一起不执行;
3.事务是不支持嵌套的
#注意
事务只能在innodb引擎下使用
特性
1.原子性:不可拆分
2.一致性:要么一起执行,要么一起不执行
3.隔离性:事务彼此没关系
4.永久性:一旦执行成功,不可修改
示例
#事务先要开启
start transaction;
#语句
mysql> insert into stuinfo (sname,sex,age,city) values('朱江',2,20,'合肥');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stuinfo;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 3 | 小强 | 男 | 22 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | 小芳 | 女 | 20 | 天津 |
| 7 | 朱江 | 女 | 20 | 合肥 |
+-----+--------+------+------+--------+
7 rows in set (0.00 sec)
#不成功->回滚
rollback;
mysql> select * from stuinfo;
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 3 | 小强 | 男 | 22 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | 小芳 | 女 | 20 | 天津 |
+-----+--------+------+------+--------+
6 rows in set (0.00 sec)
#成功->提交
commit;
#查看自动提交事务是否开启
show variables like 'autocommit';
#如果是关的
set autocommit=0|1
索引
普通索引
# 方法一 -- 创建索引
create index `索引名` on `表名`(`表字段`);
# 方法二 -- 修改表结构
alter table `表名` add index `索引名`(`表字段`);
# 方法三 -- 创建表
create table `表名`(
id int not null,
username varchar(16) not null,
index [`索引名`] (`表字段`)
);
唯一索引
# 方法一
create unique index `索引名` on `表名`(`表字段`);
# 方法二
alter table `表名` add unique index `索引名`(`表字段`);
# 方法三
create table `表名`(
id int not null,
username varchar(16) not null,
unique [`索引名`] (`表字段`)
);
主键索引
alter table `表名` add primary key (`表字段`)
删除索引
alter table `表名` drop index `索引名`;
显示索引信息
show index from `表名`
设置结束符
mysql> delimiter //
mysql> desc stumarkss //
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| stuno | int(11) | NO | PRI | NULL | |
| ch | tinyint(4) | YES | | NULL | |
| math | tinyint(4) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
存储过程
语法:
create procedure 存储过程名(参数,…)
begin
//代码
end//
注意：存储过程中有很多的SQL语句，
SQL语句的后面为了保证语法结构必须要有分号（;），
但是默认情况下分号表示客户端代码发送到服务器执行。
必须更改结束符
通过delimiter指令来跟结束符
delimiter // #将结束字符定义为//(原来是;)
创建存储过程
#简单的
#如果存储过程中就一条SQL语句，begin…end两个关键字可以省略。
mysql> create procedure pro_1()
-> select * from stuinfo;
-> //
Query OK, 0 rows affected (0.00 sec)
#调用存储过程
mysql> call pro_1()//
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 1 | 张立 | 女 | 18 | 宿州 |
| 2 | 小京 | 男 | 20 | 北京 |
| 3 | 小强 | 男 | 22 | 北京 |
| 4 | 小力 | 男 | 20 | 天津 |
| 5 | 小丽 | 女 | 21 | 重庆 |
| 6 | 小芳 | 女 | 20 | 天津 |
+-----+--------+------+------+--------+
6 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#包涵多条sql语句的
#in代表输入参数,可以省略
mysql> create procedure pro_2(in param int)
-> begin
-> select * from stuinfo where sid=param;
-> select * from stumarks where stuno=param;
-> end//
Query OK, 0 rows affected (0.00 sec)
#调用
mysql> call pro_2(2)//
+-----+--------+------+------+--------+
| sid | sname | sex | age | city |
+-----+--------+------+------+--------+
| 2 | 小京 | 男 | 20 | 北京 |
+-----+--------+------+------+--------+
1 row in set (0.00 sec)
+-------+------+------+
| stuno | ch | math |
+-------+------+------+
| 2 | 89 | 100 |
+-------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
参数的类别
在存储过程中，没有return，如果需要返回值，通过输出参数来实现
在MySQL中，参数分为3类，
输入参数（in），
输出参数（out）,
输入输出参数（inout），
默认情况下是是输入参数（in）
删除存储过程
语法:drop procedure [if exists] 存储过程名
mysql> drop procedure if exists pro_1//
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看存储过程的信息
show create procedure pro_2\G
*************************** 1. row ***************************
Procedure: pro_2
sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `pro_2`(in param int)
begin
select * from stuinfo where sid=param;
select * from stumarks where stuno=param;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
局部变量
语法：declare 变量名 数据类型 [初始值]
通过:select ...into…或set命令给变量赋值
eg：
mysql> create procedure pro_1(id int)
> begin
> declare name varchar(64);
> declare sexx char(10);
> select sname,sex into name,sexx from stuinfo where sid=id;
> select name,sexx from dual;
> end//
Query OK, 0 rows affected (0.00 sec)
mysql> call pro_1(1)// +--------+------+
| name | sexx |
+--------+------+
| 张立 | 女 |
+--------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#通过set给变量赋值
create procedure pro_5(in num1 year,in num2 year,in name varchar(32))
begin
declare num int default 0;
set num=num2-num1; #得到年龄
update stuinfo set age=num where sname=name;
select * from stuinfo where sname = name;
end//
call pro_5(1996,2018,'小芳')//
全局变量
全局变量前面必须有一个@，全局变量的数据类型取决于变量的值。如果一个全局变量没有赋值，他的数据类型为null。
set @name='小芳'//
select * from stuinfo where sname=@name//
系统变量
通过两个@开头的都是系统变量
select @@version from dual//
+-----------+
| @@version |
+-----------+
| 5.7.21-1 |
+-----------+
1 row in set (0.00 sec)
| 系统命令 | 作用 |
|---|---|
| @@version | 版本号 |
| current_date | 当前日期 |
| current_time | 当前时间 |
| current_timestamp | 当前日期和时间 |
带有输出参数的存储过程
#带有out关键字的参数,在存储过程运行结束以后,默认返回
create procedure pro_6(in num int,out result int)
begin
set result=num*num;
end//
#调用
#@result 接受返回值
call pro_6(6,@result)//
select @result from dual//
SQL编程(了解)
(1) if-elseif-else语句
#语法:
if 条件 then
//代码1
elseif 条件 then
//代码2
else
//代码3
end if;
create procedure pro_8(in grade int)
begin
if grade=1 then
select '金牌会员' as '等级';
elseif grade=2 then
select '普通会员' as '等级';
else
select '游客' as '等级';
end if;
end //
#调用
call pro_8(3)//
(2) case-when语句
create procedure pro_9(in num int)
begin
case num
when 1 then select '杀马特' as '气质';
when 2 then select '屌丝' as '气质';
when 3 then select '正常人' as '气质';
when 4 then select '贵族' as '气质';
else select '输入不正确' as '气质';
end case;
end //
call pro_9(0)//
#显示学员的学号、姓名、性别、语文成绩、等级
select sid,sname,sex,ch,case
when ch>=90 then '等级A'
when ch>=80 then '等级B'
when ch>=70 then '等级C'
when ch>=60 then '等级D'
else '等级E'
end as '等级' from stuinfo left join stumarks using(sid)//
select sid,sname,sex,ch from stuinfo left join stumarks using(sid)//
(3)loop循环
loop遇到leave退出
create procedure proc(in num int)
begin
declare total int default 0;
declare i int default 0;
sign:loop
set total=total+i;
set i=i+1;
if i>=num then
leave sign;# leave=break
end if;
end loop;
select total from dual;
end //
call proc(100)//
#如果没有设置标签名,leave loop
#sign是循环名,用于结束循环,可以自己随意取名字
(4)while循环
#语法:
while 条件 do
//代码
end while
create procedure pro_11(in num int)
begin
declare total int default 0;
declare i int default 0;
while num>=i do
set total=total+i;
set i=i+1;
end while;
select total from dual;
end //
call pro_11(100)//
(5)repeat循环
#语法
repeat
代码
until 条件 -- 直重复到条件为true才结束
end repeat
create procedure pro_12(in num int)
begin
declare total int default 0;
declare i int default 0;
repeat
set total=total+i;
set i=i+1;
until i>num
end repeat;
select total from dual;
end //
call pro_12(100)//
(6)leave和iterate
leave类似于break，iterate类似于continue
create procedure pro_13()
begin
declare i int default 0;
sign:while i<5 do
set i=i+1;
if(i=3) then
#leave sign; -- 类似于break
iterate sign; -- 类似于continue
end if;
select i from dual;
end while;
end //
call pro_13()//
MySql函数
内置函数
(1).数字类
| 语句 | 含义 |
|---|---|
| select rand() from dual; | 随机数 |
| select * from stuinfo order by rand(); | 随机排序 |
| select round(5.6); | 四舍五入 |
| select ceil(5.3); | 向上取整 |
| select floor(5.6); | 向下取整 |
(2).大小写转换
| 语句 | 含义 |
|---|---|
| select ucase(‘i am lyb’); | 大写 |
| select lcase(‘I AM LYB’); | 小写 |
(3).截取字符串
| 语句 | 含义 |
|---|---|
| select left(‘abcdefg’,3); | 截取左边的3位 |
| select right(‘abcdefg’,3); | 截取右边3位 |
| select substring(‘abcdefg’,2,3); | 从第2位开始取3个字符，起始位置从1开始 |
(4).字符串拼接
select concat(sid,sname,age,sex,city) from stuinfo;
mysql> select concat(sid,sname,age,sex,city) from stuinfo;
+--------------------------------+
| concat(sid,sname,age,sex,city) |
+--------------------------------+
| 7小明18male上海 |
| 8小刚20male北京 |
| 9小强22male重庆 |
| 10小力23male天津 |
| 11小丽21female北京 |
| 12小月20female天津 |
| 13小yb18male重庆 |
| 17百强18male黑龙江 |
| 18百强118male黑龙江 |
| 19百强218male黑龙江 |
+--------------------------------+
(5).coalesce(str1,str2):如果str1不为null则显示str1，否则显示str2
select sid,sname,coalesce(ch,'缺考'),coalesce(math,'缺考') from stuinfo left join stumarks using(sid);
mysql> select sid,sname,coalesce(ch,'缺考'),coalesce(math,'缺考') from stuinfo l
eft join stumarks using(sid);
+-----+-------+---------------------+-----------------------+
| sid | sname | coalesce(ch,'缺考') | coalesce(math,'缺考') |
+-----+-------+---------------------+-----------------------+
| 11 | 小丽 | 100 | 80 |
| 8 | 小刚 | 60 | 98 |
| 10 | 小力 | 50 | 51 |
| 9 | 小强 | 67 | 88 |
| 7 | 小明 | 88 | 10 |
| 12 | 小月 | 96 | 97 |
| 17 | 百强 | 缺考 | 缺考 |
| 18 | 百强1 | 缺考 | 缺考 |
| 19 | 百强2 | 缺考 | 缺考 |
+-----+-------+---------------------+-----------------------+
(6).length(字节长度)、char_length(字符长度)、trim(去两边空格)、repace(替换)
select length('千锋');
select char_length('千锋');
select length(trim(' 千锋 '));
select replace('pgone','one','two');
(7).时间戳
select unix_timestamp();
(8).将时间戳转成当前时间
select from_unixtime(unix_timestamp());
(9).获取当前时间
select now(),year(now()),month(now()),day(now()),hour(now()), minute(now()),second(now())\G
#现在时间,年,月,日,时,分,秒
(10).dayname(),monthname(),dayofyear()
select dayname(now()) as `星期`,monthname(now()) as `月份`,dayofyear(now()) as `本年第几天`;
(11).datediff(结束日期，开始日期)
例题计算自己活了多少天
select datediff(now(),'1998-01-01');
(12).md5():md5加密
select md5('@123456.');
自定义函数
#语法:
Create function 函数名(形参) returns 返回的数据类型
begin
//函数体
end
#第一步
delimiter //
#不带参数的函数
create function myfun() returns varchar(32)
begin
return '千锋python';
end //
#调用函数
select myfun()//
#带参数
create function myfun_1(num1 int,num2 int) returns int
begin
declare num int default 0;
set num=num1+num2;
return num;
end //
select myfun_1(100,200)//
#删除函数
drop function myfun_1//
触发器
1、触发器是一个特殊的存储过程
2、不需要直接调用，在MySQL自动调用的
3、是一个事务，可以回滚
(1)触发器的类型(触发事件)
1、insert触发器
2、update触发器
3、delete触发器
(2)创建触发器
#语法:
Create trigger 触发器名 触发时间[before|after] 触发事件 on 表名 for each row
Begin
//代码
end
(3)new表和old表
1、这两个表是个临时表
2、当触发器触发的时候在内存中自己创建，触发器执行完毕后自动销毁
3、他们的表结构和触发器触发的表的结构一样
4、只读，不能修改
stuinfo curd
打开文件,内存中需要加载,会随即分配一个空间用来保存文件的所有数据,->old 6
在新的一轮操作后,内存会生成新的空间,这个空间里面保存了新的数据变化->new 7
(5)insert触发器
#在stuinfo中插入一个值,就会自动在stumarks中插入一条数据
#after insert 表示的是在insert动作执行完毕以后触发
#on stuinfo for each row 针对的stuinfo表,并且可以读取到每一行的变化
#触发器中定义的局部变量不能与表中的字段名一致,否则会发生字段识别问题(识别不出到底是字段,还是变量)
create trigger trig1
after insert on stuinfo for each row
begin
declare sidno int default 0;
declare nch int default 0;
declare nmath int default 0;
declare nseat int default 0;
set sidno=new.sid;
set nseat=new.seat;
insert into stumarks set sid=sidno,ch=nch,math=nmath,seat=nseat;
end //
insert into stuinfo values(null,'鸡哥',1,23,'安庆',16)//
(6)update触发器
create trigger trig2
after update on stuinfo for each row
begin
declare sidno int default 0;
declare seatno int default 0;
set seatno=new.seat;
set sidno =new.sid;
update stumarks set seat=seatno where sid =sidno;
end //
select ((select max(seat) from stuinfo)+1)//
update stuinfo set seat=17 where sid=8//
(7)delete触发器
create trigger trig3
after delete on stuinfo for each row
begin
declare sidno int default 0;
set sidno =old.sid; #删除了新表里面就没有了,只能从老表里面拿
delete from stumarks where sid=sidno;
end //
delete from stuinfo where sid =8//
#触发器能做钩子函数
(8)查看 和 删除 触发器
show triggers\G
drop trigger if exists trig1//
用户管理
[mysqld] --skip--grant--tables
#(5.5最好用)
#--skip--grant--tables 跳过登陆验证(MYSQL服务器开起中)
(1)创建用户
语法：create user ‘用户名’@’允许登录的主机地址’ identified by 密码
#%代表数据库的库名
create user 'ruidong'@'%' identified by '123456';
(2)删除用户
语法：drop user 用户
drop user ruidong;
(3)增加用户权限
#将python的所有表的select权限付给ruidong用户
grant select on python.* to 'ruidong'@'%';
#将所有数据库中所有表的所有权限付给ruidong用户
grant all privileges on *.* to 'ruidong'@'%';
#创建用户并授权
grant all privileges on *.* to 'hal'@'%' identified by '123456' with grant option;
#创建好用户以后,刷新mysql用户权限表
flush privileges ;(linux ,mac)
revoke select on python.* from 'ruidong'@'%'; #删除select权限
revoke all privileges on *.* from 'ruidong'@'%'; #删除所有权限
(4)mysql57忘记密码
1、首先停止mysql服务进程：
service mysqld stop
2.#然后编辑mysql的配置文件my.cnf(如果是windows的话找到my.ini)
vim /etc/my.cnf
3.#找到 [mysqld]这个模块：
#在最后面添加一段代码
skip-grant-tables ##忽略mysql权限问题，直接登录
#然后保存 :wq!退出
#启动mysql服务：
service mysqld start
#直接进入mysql数据库：
mysql
#选择mysql数据库
use mysql;
#对user表的root用户进行密码修改
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
#特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了
#而是将加密后的用户密码存储于authentication_string字段
#执行刷新
flush privileges;
#exit退出mysql
exit;
#启动服务
service mysqld start
932
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
