如果要创建的数据库不存在，则创建成功
create database if not exists westos;
drop database if exists westos;
use tmalldemodb;//tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
show databases;
clear;auto_increment：自增
字符串使用单引号括起来
所有语句后面加逗号，英文的，最后一个不用加
primary key主键，一般一个表只有一个唯一的主键，且必须要有
engine=innodb：数据库表的引擎
mysql> create table if not exists student( -> id int(4) not null auto_increment comment '学号', -> name varchar(30) not null default '匿名' comment '姓名', -> pwd varchar(20) not null default '123456' comment '密码', -> sex varchar(2) not null default '女' comment '性别', -> birthday datetime default null comment '出生日期', -> address varchar(100) default null comment '家庭地址', -> email varchar(50) default null comment '邮箱', -> primary key(id) -> )engine=innodb default charset=utf8;创键成功之后的图
show create database 数据库名;运行结果图
show create table 表名;
desc 表名；
innodb //默认使用myisam//早些年使用的事务：假设有两个SQL都执行，要么都成功，要么都失败，如果有一个成功一个失败，它就提交不上去
数据行锁定：假设有两条SQL去查同一个表，他会把这个表先锁住，剩下的数据再查的时候就要排队等待
外键约束：一张表连接到另一张表
全文索引：比如在百度上根据你输入的关键词(在数据库叫字段)区搜索你想要的结果
| myisam | innodb | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 （支持表锁） | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为myisam的2倍 |
常规使用操作：
myisam 节约空间，速度较快
innodb安全性高，事务的处理，多表多用户操作
alter table teacher rename as teacher1;
alter table teacher1 add age int(11);
alter table teacher1 modify age varchar(11);
②字段重命名（既可以给字段重命名，又可以修改约束）
alter table 表名 change 旧字段名 新字段名 列属性;
alter table teacher1 change age age1 int(11);
alter table teacher1 drop age1;
drop table if exists teacher1;
所有的创建和删除操作都尽量加上判断，以免报错
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
int 标准的整数 4个字节 常用
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数
char 字符串固定的大小 0-255
varchar 可变字符串 0-65535 常用
tinytext 微型文本 2^8-1
text 保存大文本 2^16-1
date 日期格式：YYYY-MM-DD
time 时间格式：HH:mm:ss
datetime 日期格式：YYYY-MM-DD HH:mm:ss 常用
timestamp 时间戳，从1970.1.1至现在的毫秒数 常用
year 年份表示
没有值，未知
注意，不要使用NULL进行运算，结果为NULL
无符号的整数
不能声明为负数
0填充的
假设你现在要写一个长度为10的int类型，但是你只写了个1，则他会用自动给你在1前面填充9个零
通常理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键~index,必须是整数类型
可以自定义设置主键自增的起始值和步长
假设设置为not null ，如果不给它赋值，就会报错！
Null,如果不填写值，默认就是null！
设置默认的值
sex,默认值为男，如果不指定该列的值，则会有默认的值！
设置默认的值
删除有外键关系的表的时候，必须要先删除字表，才能删除父表
insert
update
delete
insert into grade(gradename) value('大三');
insert grade(gradename) value('大二'),('大一');
insert into student(name,pwd,sex) values('张三','aaaa','男'),('李四','vvvv','女');
update student set name='TWQ' where id =2;
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或！= | 不等于 | 5<>6 | true |
| < | 小于 | 5<6 | true |
| > | 大于 | 5>6 | false |
| <= | 小于等于 | 5<=6 | true |
| >= | 大于等于 | 5>=6 | false |
| between a and b | 在a到b这个闭包区间内 | [a,b] | true |
| and | 我和你 && | 5>1 and 1>2 | false |
| or | 我或你 | 5>1 or 1>2 | true |
delete from student where id =1;
truncate table student;
相同点：都能删除数据，都不会删除表结构
不同：①truncate重新设置自增列，计数器会归零。②truncate不会影响事务
使用delete删除表中所有数据，不会影响自增的值
拓展（了解即可）关于delete删除的问题：删除后重启数据库
若该表使用的引擎为innodb，重启数据库后，自增列会从1开始（存在内存当中断电即失）
若采用的引擎是myisam 重启数据库后，会继续从上一个自增变量开始（存储在文件中，不会丢失）
delete from grade;
使用truncate删除表中所有数据，自增变量会归零
truncate table grade;
select * from student;select name,pwd from student;
②给字段或表名起别名(as)
select name as 姓名,pwd as 密码 from student;
select concat('姓名:',name) as 新名字 from student;
select distinct Student_id from result;
select version();
select 100*3-1 as 计算结果;
select Student_id,StudentResult+1 as 加一分后 from result;
| 运算符 | 语法 | 描述 |
|---|---|---|
| and | a and b | 逻辑与 |
| or | a or b | 逻辑或 |
| Not | not a | 逻辑非 |
//方式一：select student_id as 学生编号,studentresult as 分数 from resultwhere studentresult>=95 and studentresult<=100 and subject_id=23;//方式二：select student_id as 学生编号,studentresult as 分数 from resultwhere studentresult between 95 and 100 and subject_id=23;
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null,结果为真 |
| is not null | a is not nul | 如果操作符为不为null,结果为真 |
| between | a between b and c | 若a在b和c之间，则结果为真 |
| like | a like b | sql匹配，如果匹配b,则结果为真 |
| in | a in(a1,a2,a3,.....) | 假设a在a1,或者a2....其中的一个值中，结果为真 |
①查询姓唐的同学
like 结合 %(代表0到任意个字符) _（一个字符）
select name,birthday from student where name like '唐%';
②查询姓唐的同学,并且名字后面仅有一个字的
select name,birthday from student where name like '唐_';
③查询姓唐的同学,并且名字后面仅有个字的
select name,birthday from student where name like '唐__';
④查询名字中含有嘉字的同学 %嘉%
select name,birthday from student where name like '%嘉%';⑤查询id为3,4,5号的学生
select name,birthday from student where id in (3,4,5);
⑥查询家住在宁波和上海的学生
select name,address from student where address in ('宁波','上海');
⑦查询邮箱为空的学生
select name,email from student where email is null;
⑧查询邮箱不为空的同学
select name,email from student where email is not null;
①查询参加了考试的同学（学号，姓名，科目编号，分数）使用inner join来查询
思路：
分析需求，分析查询的字段来自哪些表，（连接查询）
确定使用哪种连接查询？ 7种
确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id
-- 这里给student表和result表起别名，就是因为这两张表中都有student_id这个属性，为了不扯皮，所以起别名并写明用哪个表的idselect s.student_id,name,subject_id,studentresultfrom student as sinner join result as rwhere s.student_id=r.student_id;
②使用right join来实现上述操作
join（连接的表） on(判断的条件) 连接查询
where 等值查询
select s.student_id,name,subject_id,studentresultfrom student sright join result ron s.student_id=r.student_id;
③使用left join操作上述问题
select s.student_id,name,subject_id,studentresultfrom student sleft join result ron s.student_id=r.student_id;由结果可知left join会将没有参与考试的学生也查了出来
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行（hang） |
| left join | 会从左表中返回所有的值 ，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值 ，即使左表中没有匹配 |
| ④查询缺考的同学 |
select s.student_id,name,subject_id,studentresult-- 起别名的时候as也可以省略from student sleft join result ron s.student_id=r.student_idwhere studentresult is null;
⑤查询参加了考试的同学的信息：学号，姓名，科目名，分数
思路：
分析需求，分析查询的字段来自哪些表，student，result，subject（连接查询）
确定使用哪种连接查询？ 7种
确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id ，成绩表中的subject_id =科目表中的subject_id;
select s.student_id,name,subject_name,studetresultfrom student sright join result ron s.student_id=r.student_idinner join subject subon r.subject_id=sub.subject_id;
⑥查询参加了高等数学考试的同学的信息：学号，姓名，科目名，分数
select s.student_id,name,subject_name,studentresultfrom student sright join result ron s.student_id=r.student_idinner join subject subon r.subject_id=sub.subject_idwhere subject_name ='高等数学';
总结：
我要查询哪些数据 select.....
从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
父类（找pid等于1的为一棵树的最顶端）
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术技术 |
子类
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 3 | 6 | web |
| 5 | 7 | ps技术 |
| 2 | 8 | 办公信息 |
①操作：查询父类对应的子类关系
| 父栏目 | 子栏目 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web |
| 美术技术 | ps技术 |
采用联表查询得到上表只需要让父类的categoryid等于子类的pid即可
//将一张表看成一模一样的表select a.categoryname as 父栏目,b.categoryname as 子栏目from category as a,category as bwhere a.categoryid = b.pid;
select name,subject_name,studentresultfrom student sinner join result ron s.student_id=r.student_idinner join subject subon r.subject_id = sub.subject_idorder by studentresult asc;
第一页： limit 0,5 起始查询下标:(1-1)5
第二页： limit 5,5 起始查询下标:(2-1)5
第三页： limit 10,5 起始查询下标:(3-1)5
第N页： limit (n-1)5,5 起始查询下标:(n-1)*pagesize
pagesize:页面大小
（n-1)*pagesize:起始查询下标
n:当前页
数据总数/页面大小 = 总页数
select student_id,r.subject_id,studentresultfrom result rinner join subject subon r.subject_id=sub.subject_idwhere subject_name='高等数学'order by studentresult desc;
方式二：使用子查询
select student_id,subject_id,studentresultfrom resultwhere subject_id =( select subject_id from subject where subject_name='高等数学');
-- 绝对值select abs(-8)-- 向上取整select ceiling(9.4)-- 向下取整select floor(9.4)-- 返回一个0-1之间的随机数select rand()-- 判断一个数的符号 0-0 返回-1，正数返回1select sign（10）-- 字符串长度select char length('发来的法计算')-- 拼接字符串select concat('我','adf','asd')-- 查询，从某个位置开始替换某个长度select insert('我爱变成helloworld',1,2,'超级热爱')-- 小写字母select lower('KjfdsSJK')-- 大写字母select upper('KjfdsSJK')-- 返回第一次出现的子串的索引select instr('twq123','3')-- 替换出现的指定字符串select replace('坚持就是胜利','坚持','努力')-- 返回指定的字符串(源字符串，截取的位置，截取的长度)select substr('坚持就是胜利',3,2)-- 反转select reverse('清晨我上马')运用
将姓唐的同学姓式改为姓汤
select replace(name,'唐','汤') from studentwhere name like '唐%';
-- 获取当前的日期select current date()-- 获取当前日期select curdate()-- 获取当前的时间select now()-- 获取本地时间select localtime()-- 获取系统时间select system()select year(now())select month(now())select day(now())select hour(now())select minute(now())select second(now())--系统select system_usr()select user()select version()| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| .... |
查询student表中一共有多少人
-- 都能够统计，表中的数据(想查询一个表中有多少个记录，就是用这个count())select count(name) from student -- count(字段)，会忽略所有的null值select count(*) from student; -- count(*) 不会忽略null值 ,本质在计算行数select count(1) from result; -- count(1) 不会忽略null值 ,本质在计算行数select sum(studentresult) as 总和 from resultselect avg(studentresult) as 平均分 from resultselect max(studentresult) as 最高分 from resultselect min(studentresult) as 最低分 from result-- 查询不同课程的平均分，最高分，最低分-- 核心：（根据不同的课程分组）select subject_name, avg(studentresult),max(studentresult),min(studentresult)from result rinner join subject subon r.subject_id =sub.subject_idgroup by r.subject_id -- 通过什么字段来分组having 平均分>80; -- 分组之后再添加条件必须使用having，详见select语法的图
注意：执行以上命令可能会如果没有修改MySQL的mode将会报以下错误
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.sub.subject_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方式：
在Navicat或者sqlyog的命令中执行以下命令即可
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';-- 明文密码insert into testmd5 values(1,'zhanshan','123456'),(2,'lisi','abcdefg'),(3,'wangwu','asdfgh');
-- 加密update testmd5 set pwd=MD5(pwd) where id =1; -- 对单个数据进行加密update testmd5 set pwd=MD5(pwd); -- 加密全部数据
-- 插入的数据的时候加密insert into testmd5 values(4,'xiaming',MD5('567890'));
要么都成功，要么都失败就是下面这个转账的事务，必须要子啊两条SQL执行完成之后该事务才算结束，体现事物的原子性
①、SQL执行 A给B转账 A最开始有1000 B最开始有200 A给B转200
②、SQL执行 B收到A的钱 此时A有800 B有400
①脏读：
指一个事务读取另外一个事务未提交的数据.
②不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同.（这个不一定是错误，只是某些场合不对）.
③虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致.
事务因提交不可逆转，被持久化到数据库中
-- MySQL是默认开启事务自动提交的set autocommit =0 -- 关闭set autocommit =1; --开启（默认的）-- 手动处理事务set autocommit =0 -- 关闭自动提交-- 事务开启start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内-- 提交：持久化（成功）commit-- 回滚：回到原来的样子（失败）rollback-- 事务结束set autocommit=1 -- 开启自动提交-- 创建一个转账的数据库create database shop character set utf8 collate utf8_general_ci;-- 在此数据库中创建表create table account( id int(3) not null auto_increment, name varchar(20) not null, money decimal(9,2) not null, primary key(id) )engine=innodb default charset=utf8;--往表里添加数据insert into account(name,money)values('A',2000.00),('B',10000);--模拟转账，事务set autocommit =0; -- 关闭自动提交start transaction; --开启一个事务update account set money=money-500 where name='A'; -- A减500update account set money=money+500 where name='B'; -- B加500commit; -- 提交事务rollback; -- 回滚set autocommit =1; -- 开启自动提交索引的定义：索引是帮助MySQL高效获取数据的数据结构，
提取句子主干，就可以得到索引的本质：索引就是数据结构
*唯一索引 (unique key)
避免重复列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引（key / index）
全文索引（fulltext）
-- 索引的使用-- 1.在创建表的时候给字段增加索引-- 2.创建完毕后，增加索引show index from student; -- 显示所有的索引信息-- 增加一个全文索引(索引名) 列名alter table school.student add fulltext index name (name);-- explain 分析sql执行的状况explain select * from student;-- 非全文索引explain select * from student where match(name) against('唐');给student表中的name添加字段之后结果图
while i <num do
insert into app_user(name,email,phone,gender,password,age)
values(concat('用户',i),'1430953131@qq.com',concat('18',floor(rand()((999999999-100000000)+100000000))),floor(rand()2),uuid(),floor(rand()*100));
set i =i+1;
end while;
return i;
end;
执行上述函数可能会报以下错误1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)解决方式：临时解决方法（重启MySQL后失效）：在命令行界面执行以下命令~~~sqlset global log_bin_trust_function_creators=TRUE;永久解决方法：
在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1
索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上
原文出处:MySQL完整版详解
群主管理都是支付大佬
