1、启动服务器
//启动
net start mysql
//关闭
net stop mysql
2、登陆与退出
//登陆
//mysql.exe（或mysql） -h主机名地址 -P端口（默认3306） -u用户 -p密码
//方式一
mysql -hlocalhost - P3306 -uroot -p
//默认本机
mysql -uroot -p
//退出1
exit;
//退出2
\q;
quit;
3、数据库操作
// create database 数据库名 chartset [库选项]
create database mydatabse chartset uft8
//显示
show databases
//匹配显示 show databases like ‘匹配模式’ _:单个字符。%：全部字符
show databases like 'my_'
show databases like 'my%'
show databases like '%my'
show databases like '_my'
//显示创建语句
show create databse mydatabase
//选择使用数据库
use mydatabase
//修改数据库字符集
alter database mydatabse chartset gbk
//删除数据库 谨慎操作
drop database mydatabse
4、数据表操作
//创建表1 create table 表名（字段 字段类型 【字段属性】，字段 字段类型 【字段属性】）
use mydatabse;//切换到对应数据库
create table mytable(
name varchar(10),
age int,
);
//创建2 使用数据库.表名
create table mydatabase.mytable(
name varchar(10),
age int,
)
//从已有的表创建
create table newtable like oldtable；
create table newtable like mytable.oldtable；
//显示表
show tables；
show tables like '-patten';
//显示表结构
describe mytable；
desc mytable；
show columns from mytable；
//显示创建语句
show create table mytable；
//设置表属性 alter table mytable pro 【=】 val，表属性
alter table mytable charset【=】 gbk；
//修改表名
rename table old_name to new_name ;
//新增字段 alter table mytable add 新字段 类型 【属性】位置（first或者after 字段）
alter table mytable add new_col int first；
alter table mytable add new_col int after col_1；
//修改字段名 alter table mytable change old_name new_name 类型 【属性】【 位置】
alter table mytable change old_name new_name int first；
//修改字段类型/属性 alter table mytable modify 字段名 新类型 【新属性】 【新位置】
alter table mytable modify col int ；
//删除字段
alter table mytable drop col；
//删除表 drop table 表名，表名2...
drop table mytable;
5、mysql展示语句结束符
；和\g 为横着排
\G 为竖着排
6、数据操作
//插入数据 insert into 表名 （字段列表）values（值列表）
//不要求和表的顺序一样，也不要求全部数据
insert into mytable （name, age, class）values('za',18,'2班')
//所有字段均插入，简写
insert into mytable values('za',18,'2班')
//查询数据，不管什么查询，都会扫描全表
//查询全部
select * from mytable;
//查询部分 select (字段列表) from 表名;
select (name,age) from mytable;
//条件查询 select (name,age) from 表名 where 字段 = 值；sql中没有’==‘符号
select (name,age) from mytable where name = ‘za'；
//删除操作 delete from 表名 【where 条件】，如果没有where 系统会删除该表的全部数据
delete from mytable where name=‘za’；
//更新数据 update 表名 set 字段=新值 【where条件】；如果没有where，所有数据的该字段都会被修改
update mytable set name = ‘za' where name=’lisi‘；
7、设置字符集
常见字符集，ASCII，GB2312，BIG5（繁体）,Unicode,
mysql.exe与mysqld.exe 之间的处理关系包括三层，需要设置为统一的
//全部设置
set names gbk；
//查看字符相关系统变量
show variables like ‘character_set%‘；
character_set_client //客户端传入
character_set_connection//连接存
character_set_database//当前数据库的存储
character_set_results/返回结果
//单个修改字符集 set 变量名 = 值；
set character_set_client = gbk；
set character_set_connection = gbk；
set character_set_database = gbk；
set character_set_results = gbk；
8、列类型
（1）整数类型
- tinyint 迷你整型 一个字节 = 8位 范围 0-255；
- smallint 小整形 2个字节 0-65535 ；
- mediumint 中整形，3字节
- int 整形 4字节；
- bigint 大整形 8字节；
注意：因为整形有负数，所以是正负各一半。例如tinyint，实际上是-128-127；
无符号标识：类型后加上 unsigned；
填充长度：类型后加上 zerofill；左侧补零，使用后自动设置上unsigned。
//有符号整数
create table mytable (
int1 tinyint;
int2 smallint;
int3 mediumint;
int4 int;
int5 bigint;
);
desc mytable;
insert into mytable values(1,65534,255,255,255);
select * from mytable;
//无符号整数
create table mytable (
int1 tinyint unsigned,
int2 smallint unsigned,
int3 mediumint unsigned;,
int4 int unsigned,
int5 bigint unsigned,
);
desc mytable;
insert into mytable values(1,65534,255,255,255);
select * from mytable;
（2）小数类型
//浮点数 4字节
float：不指定小数位数
float(M,D):M个有效数字，小数占D位；
//定点数 整数部分一定准确，小数部分超出长度会四舍五入
decimal(M,D):M是总长度，不能超过65，D是小数长度，不能超过30
（3）时间
date:yyyy-mm-dd 3字节 10000-01-01到9999-12-12 初始值位0000-00-00；
time：hh:mm:ss 3字节,能表示-838：59：59~838：59：59，用于描述时间段；
datetime：yyyy-mm-dd hh:mm:ss 8字节，能表示1000-01-01 00：00：00~9999-12-12 23：59：59，可以为0，表示0000-00-00 00：00：00；
timestamp：时间戳，当数据被修改，自动更新，不能为空
year：1900-2155
（4）字符串
//定长字符串
char(L):L为0到255，中英文一样；
//变长字符串
varchar(L):L为0到65535，中英文一样,varchar要记录长度，所以会多使用1-2字节，数据小于127，则增加1字节，大于127就增加2字节。
//text 四种text，不用刻意选择 ，系统会自动选择合适的长度。
tinytext:1字节 长度为2^8+1;
text:2字节 长度为2^16+2;
mediumtext:3字节 长度为2^24+3;
longtext:4字节 长度为2^32+4;
//枚举类型 enum(值1，值2，...) 值为固定的几个选项，存储时是存的元素的下标，从1开始
create table mytb(
sex enum('male‘，’female‘）
）；
//set集合 将多个数据同时保存，1表示选中，0表示为为选中，自动计算存储空间大小
//1个字节对应8个选项，存储例如为选中（1，0，1，1，1，1，1，0），但最后在内存中会前后颠倒存放，变为（0，1，1，1，1，1，0，1）再转为10进制存储
create table mytb(
sex set('1‘，’2‘，'3'，'4'）
）；
注：mysql中会进行自动类型转换，遇到+ - * / 会自动将数据转换为数据，普通字符串转化为0.
9、列属性
//列属性即字段属性有6个：null，默认值，列描述，主键，唯一键，自动增长
//null，允许为空；但尽量避免为空，如果有字段允许为空，则系统需要保留1字节储存null。
//not null 不允许为空；
create table mytable(
name varchar(10) not null
);
//default val 默认值为val；
//显示告知 insert into mytable values（default）；
create table mytable(
name varchar(10) default 'zs',
);
insert into mytable values(default);
//comment '注释说明'
create table mytable(
name varchar(10) comment '这是名字'，
);
//show create table mytable 查看说明
//主键 ，在一张表内有且只有一个字段做主键，且值惟一；
//在列表字段后面加上 primary key
//方式一 创建中的时候选定
create table mytable(
name varchar(10) comment '这是名字'，
id int primary key,
);
//方式二 创建后选定
create table mytable(
name varchar(10) comment '这是名字'，
id int,
);
//创建后增加主键
alter table mytable add primary key(id);
//查看主键--查看表结构
desc mytable；
//查看主键--查看创建语法
show create table mytable;
//删除逐渐
alter table mytable drop primary key;
//复合主键
create table mytable(
primary key(std_id,course_id),//复合主键
);
//主键约束：主键对应的字段不能为空，且不能重复
//主键分类为：业务主键，具有业务意义；逻辑主键，自然增长的整形，应用广泛。
//自动增长：auto_increment,某字段给定该属性后，在没有提供数据时，会根据已存在的数据自动增长并填充，仅使用于数值类型，一张表最多一个自增长；
create table my_at(
id int primary key auto_increment,
name varchar(10)
);
//修改自增长的值
alter table my_at auto_increment = 10;
//删除自增长，字段后不再保留自增长属性
alter table my_at modify id int;
//系统中维护自增长的初始值和步长
show variables like 'auto_increment%';
//修改自增长
set auto_increment_increment = 10;
//唯一键
// unique key，保证字段中数据唯一
//一张表内可以有多个唯一键，但只能有一个主键
//唯一键允许为null，null可以有多个，null不参与比较
//字段后后添加
create table my_uq(
id int primary key auto_increment,
username varchar(10) unique
);
//表后添加
create table my_uq(
id int primary key auto_increment,
username varchar(10) ，
unique key(username)
) charset utf8;
//创建后增加
alter table my_uq add unique key(username);
//查看唯一键
desc my_uq;
//删除唯一键
alter table my_uq drop index username;
//修改唯一键，先删除后增加
复合唯一键，使用多个字段共同保证唯一性。
10、表关系
（1）一对一
一张表的一条记录与另一张表最多有一条明确的关系，只需要两张表使用同样的主键。
（2）一对多
多的关系表中维护一个字段，这个字段是一关系表的主键，如母子关系。
（3）多对多
一张表的一条记录在另一张表中可以匹配到多条记录，反之亦然，如师生关系。
设计第三张表维护对应关系，让中间的表与另外两张表形成两个一对多关系。
11、数据增删改查
（1）一次性插入多条
insert into mytable values('zs'),('ls'),('ww');
（2）主键冲突
在插入数据时，不确定是否已经存在主键，会导致错误
//主键冲突更新
insert into my_table values('id0001','zs') on duplicate key update name='zs';
//主键冲突替换
replace into my_table values('id0001','zs');
（3）蠕虫复制
成倍增长，从已有的数据中获取数据，并插入到数据表中,可能有主见冲突。
insert into my_table(name) select name from my_table;
（4）更新数据
注意加上where，否则是全部更新
update mytable set name = ’new_name' where name = 'zs';
//限制数量
update mytable set name = ’new_name' where name = 'zs' limit 4;
（5）删除数据
注意加上where，否则是全部删除，删除时无法充值auto_increment.
delete from mytable where name = 'zs';
//限制数量
delete from mytable where name = 'zs' limit 4;
//重置自增长
truncate mytable;
（5）查询数据
选项包括：all 全部，默认值；distinct 去重。所有字段都相同表示重复。
限制条件：
- where 条件
- group by 分组
- having 条件
- order by 排序
- limit 限制
//指令为： select 选项 字段列表 from 数据表 where 条件 group by 分组 having 条件 order by 排序 limit 限制数量
select all name from mytable where name='zs' ;
//取别名。避免多张表命名冲突
select distinct name as name1 from mytable;
//从多张表同时取数据,得到记录数相乘，字段拼接的，即笛卡尔积。
select * from mytable1,mytable2;
//动态数据，from后面不是一个实体表，而是从一个表中查询的返回结果，再从里面查询
select * from （select name from mytable）as my_table2;
//where 子句，全表查询，保留符合条件的数据
select * from mytable where name ='zs';
//group by 字句，根据指定的字段进行分组统计，如果只想看数据显示，group by没特别的含义，分组后只会显示第一条数据
//count() ;统计每组的数量，如果统计目标字段则不统计null字段，如果为*，代表统计记录
//avg();求平均
//sum();求和
//max();求最大
//min();求最小
select class_id,count(*),max(age),min(age),avg(age) from myclass group by class_id;
//group_concat(name) 将name 字段合并在一起
select class_id,count(*)，group_concat(name),max(age),min(age),avg(age) from myclass group by class_id;
//多分组，按照某个字段分组之后再按照另一个字段分组，group by class_id,gender;
select class_id,count(*)，group_concat(name),max(age),min(age),avg(age) from myclass group by class_id,gender;
//分组排序 group by class_id asc|desc,gender asc|desc
select class_id,count(*)，group_concat(name),max(age),min(age),avg(age) from myclass group by class_id asc;
//回溯统计，多分组后往上统计需要层层上报，每次回溯将产生一个新的统计数据
//group by with rollup;
select class_id,count(*)，group_concat(name),max(age),min(age),avg(age) from myclass group by class_id asc with rollup;
//having子句，和where一样，根据条件筛选
//having在group by 之后，可以针对分组数据进行筛选，where不行
//where 不能使用聚合函数
select class_id ,count(*) as number from mytable group by calss_id having count(*) >=3;
//order by 子句，根据校对规则对数据进行排序
//order by 字段 asc|desc，默认为asc
select * from mytable order by age asc;
//order by 也可以多字段排序，按字段顺序依次排序
select * from mytable order by age asc,class_id desc;
//limit 字句；限制数量，从第一条到指定的数量
select * from mytable limit 2;
//limit offset,length;
select * from mytable limit 1,2;
（6）查询中的运算符
//+ - * / % 基础运算，通常不在在查询条件中使用，而是用于结果，其中除法的结果是浮点数
select age+age from mytable;
// > 、>=、 <、 <=、 =、 <> 比较运算符用于查询中做限定。
//<=> 相等比较
select * from mytable where age>=20;
//区间查找 between num1 and num2；
select * from mytable where age between 8 and 30;
//and 、or 、not 逻辑运算。
select * from mytable where age between 8 and 30 and class_id = 1;
//in 运算，表示在集合中出现过
select * from mytable where age in (10,20);
//is 运算，专门判断字段是否是null，is null ； is not null.
select * from mytable where age is null;
//like 运算，模糊匹配，匹配字符串
// _ :匹配单个字符
//%：匹配多个字符
select * from mytable where name like ’z%';
（7）联合查询
联合查询是可以合并多个相似的查询结果，等同于一个表追加到另一个表，使用union 或者union all
是纵向合并，字段数不变
常见应用场景是：数据量大，会分表存储，从多张表取出数据，再合并显示
union理论上只需要拿到的字段数一致，并不需要字段一样，且只保留第一个select 语句的字段名。
//基本语法
select 语句1
union [选项]
select 语句2；
//选项包括 all 和distinct；
select name from mytable
union
select age from mytable;
//联合查询中使用order by子句，对应的select 语句需要用括号括起来,且 必须使用limit 数量 才会生效，通常取较大的数值。
（select name from mytable order by age asc limit 100000)
union
select age from mytable;
12、连接查询
将多张表连在一起查询，会导致字段和行数改变。
原因是关系型数据库有一对一。一对多。多对多的关系，需要利用这个关系保证数据的完整性。
//交叉连接 记录数=table1*table2，字段数 = table1+table2，即笛卡尔积,没有实际应用
//table1 cross join table2
select * from able1 cross join table2;
//内连接 从一张表中取出所有的记录去另一张表进行匹配，利用匹配条件进行匹配，成功则保留，如果没有匹配条件就是交叉连接
//内连接通常用在对数据有精准要求的地方，必须保证两个表中能进行数据匹配。
//语法 select * from table1 inner join table2 on 条件
//无条件
select * from table1 inner join table2 ；
//有条件,避免同名字段干扰，通常用 表名.字段名 作为条件。
select * from table1 inner join table2 on table1.class_id = table2.id;
//条件也可以使用where来限制，但一般使用on
select * from table1 inner join table2 where table1.class_id = table2.id;
//表名称较长的时候也可以使用别名
select * from table1 as t1 inner join table2 as t2 where t1.class_id = t2.id;
//外连接，把一张表作为主表，他的所有数据都会保留，根据条件去另一张表获取目标数据，从表中未匹配到，则从表的字段全部设置为null
//分为 左外连接，即左表作为主表；右外连接，即右表做为主表
//非常常用的方法。
select * from table1 as t1 left join table2 as t2 on t1.class_id =t2.id;
select * from table1 as t1 right join table2 as t2 on t1.class_id =t2.id;
//using关键字，在使用on的地方使用using代替，必须保证字段同名，链接后的同名字段只会保留一个
//select * from table1 as t1 right join table2 as t2 using (同名字段列表)；
13、子查询
主查询：第一条select语句，确定所获取的数据源。
子查询： sub query,是指当一个查询时另一个查询的条件时，称为子查询。在一条select语句中嵌入另一条select语句，被嵌入的select 语句就是子查询语句。
子查询要么作为条件，要么作为数据源，子查询是独立存在的，是完整的select语句。
分类：
按功能分为：
- 标量子查询：子查询返回的结果是一个数据，即只有一行一列
- 列子查询：返回一列多行
- 行子查询：返回一行多列
- 表子查询：返回多行多列
- exists子查询：返回结果为1或0
按位置分为：
where 子查询：子查询位置出现在where条件中
from子查询：子查询位置出现在from数据源中，作为数据源
//标量子查询，需求决定主查询，条件决定子查询。
//基本语法 select * from table1 where 条件判断 = (select 字段 from 数据源 where 条件判断)
select * from student where class_id = (select class_id from student where name='zs');
//列子查询
//语法：主查询 where 条件 in （列子查询）
select * from student where id in (select id from student where id between 1 and 3);
//行子查询
//语法：主查询 where 条件 (构造行元素) =（行子查询）,构造的行元素和子查询的数量必须一致。
select * from student where (stu_id,age) = (select max(stu_id),max(age) from student);
//表子查询,使用的是from
//语法：select 字段 from （表子查询）as 别名 [where ,group by having ...]
select * from (select * from student where class_id=1) as tb1 where age between 10 and 20;
//exists子查询
//语法 where exists(查询语句)，exists根据查询结果进行判断，如果存在就返回1，1的就保留
select name from student as s1 where exists(select stu_id from student as s2 where s1.class_id = s2.stu_id);
子查询中的特定关键字：
in：主查询 where 条件 in （列子查寻）
select * from student where stu_id in (select stu_id from student where stu_id >=2);
any：任意一个
=any(子查询)，条件在查询结果中有任意一个匹配，等价于in
<>any(子查询),条件在查询结果中与任意一个都不匹配
select * from student where stu_id =any (select stu_id from student where stu_id >=2);
select * from student where stu_id <>any(select stu_id from student where stu_id >=2);
some：与any 一样。
all：
=all(子查询)：等于里面所有
<>=all(子查询)：不等于里面所有
14、数据库备份和还原
整库备份，也叫做sql数据备份，备份的结果都是sql指令。
mysql中提供专门用于备份的客户端：mysqldump.exe
sql备份是常见备份方式，不止备份数据，还会备份sql指令，即使数据库完全毁坏也可以实现数据还原，但是被挖坟文件特别大，不适合大型数据备份。
//基本语法 ：mysqldump/mysqldump.exe -hPup 数据库名 [表1] >备份地址
//整库备份：只需要提供数据库名
mysqldump.exe -hlocalhost -P3306 -uroot -p mydatabase > c:/sever/temp/mydatabase.sql;
//单表备份：数据库后跟一张表
//多表备份：数据库后跟多张表
15、外键
外键：foreign key
一张表（A)中有一个字段，保存的值指向另一张表（B）的主键。
B:主表
A:从表
//增加外键
//1、创建时增加
//[constraint 外键名] 可以给外键命名
//[constraint 外键名] foreign key(字段) references 主表(主键)
//外键本身也是索引，创建时会自动增加索引。
create mytable(
stu_id int primary key auto_increment,
name varchar(10),
class_id int,
foreign key(class_id) references my_class(class_id),
) charset utf8;
//2、创建后增加
//alter table 从表 add [constraint 外键名] foreign key(字段) references 主表(主键);
alter table mytable add foreign key(class_id) references my_class(class_id);
//修改外键，外键只能先删除再增加
//删除外键，只能删除自己。普通索引无法删除
alter table mytable drop foreign key '外键名字'
//删除索引
alter table mytable drop index 索引名字；
外键约束：
1、从表受制于主表数据的存在，不能插入主表中不存在的数据
2、主表不能随意删除被从表引入的记录
3、约束模式，可以在创建外键时进行约束
4、外键约束的作用是保证数据的完整性，主表和从表数据一直，但是因为外键具有强大的约束作用，会导致后台数据变化不可控，使用较少。
add foreign key(class_id) references 主表(主键) on 约束模式
//district ：严格模式，默认，不允许操作
//cascade：级联模式，主表变化，从表也变化
//set null：置空模式，主表变化（删除），从表对应记录设置为空，前提是从表中对应字段允许为空。
//外键约束的对象是主表操作，从表本身就是不允许插入主表不存在的数据。
//在进行约束时，需要指定操作：update 和delete
//常见模式 on update cascade，on delete set null。
16、视图
视图的本质是sql指令(select 指令)
//语法 create view 视图名字 as select 语句；
//视图就是虚拟表，所以表的操作都可以用于视图
create view myview as select * from student；
//使用视图，就和使用表一样,但是视图本身没有数据，是临时执行select 语句得到的。
select * from myview;
//修改视图本质就是修改试图对应的select语句
alter view myview as 新的select语句；
//删除视图
drop view myview；
17.事务安全
事务是一个程序执行单元，由事务开始(begin transaction）和事务结束(end transaction)之间的全部操作组成。
基本原理：将指令存储起来，不直接更新数据表，而是等到确认命令后再进行操作。
mysql中通常是自动提交，但是也可以手动提交。
自动事务:autocommit,自动同步
//查询自动事务的变量
show variables like 'autocommit%'；
//设置关闭
set autocommit = off；
//自动事务关闭以后，需要用户提供指令是否同步修改
commit：提交，同步到数据库，同时事务会被清空
rollback：回滚，之前的操作全部不要了。
//通常不会关闭自动事务，太麻烦
手动事务：不管开始，还是结束都需要用户手动发送指令。
//start transaction ；开启事务，从这条语句以后的所有语句都不会直接写入到数据库
//事务处理，多个操作指令
//事务提交：commit/rollbak,到此时所有的事务才算结束
//回滚点 savepoint，当一系列的事物操作，如果有失败，可以回滚到这个位置。
//增加回滚 savepoint 回滚点名字；
//回到回滚点：rollback to 回滚点名字；回滚点之后的操作均无效；如果有多个回滚点，回滚到前面，会导致后面的回滚点失效。
//事务特点：原子性、一致性、隔离性(两个用户不能同时操作同一个数据)、持久性（事务一旦提交，数据库就会被修改)
18.变量
mysql也是编程语言，需要变量来保存数据。
//查看系统变量 show variables [like 匹配模式]。
//select 查询 ：select @@变量名；
//修改系统变量
//局部修改，只针对本次生效
set 变量名 = 值；
//全局修改，所有用户都修改，但是需要新连接的用户才能生效
set global 变量名 = 值；
set @@global.变量 = 值；
//会话变量，用户自定义的变量
set @变量名 := 值；因为mysql 没有==，所以用=表示全等，导致在用=进行赋值的时候可能出错，所以使用:=；
//mysql可以从表中取出是数据存到变量中，但是只能取出一行，因为mgsql没有数组。
//赋值且查看过程,
select @变量1 := 字段1，@变量2 := 字段2 from student limit 1；
//只赋值，不看过程
select 字段1 ，字段2 from student where 条件 into @变量，@变量2
//查看变量
select @变量1，@变量2；
//局部变量，在begin和end中间，使用declare声明
//declare 变量ming 数据类型 [属性];
598
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
