MySql查询

1 数据库类型

a 关系型数据库
MySql Oracle DB2 Sql Sever 像表格一样行列对应存储数据
b 非关系型数据库
Redis MongoDB 存储内容以对象属性形式表示

2 创建数据库SQL

-- mysql版本查询
select VERSION();
-- 登录mysql数据库命令 -p后紧接命令不能有空格 或者-p回车再输密码
mysql -u root -p1234
-- 修改mysql数据库登录用户名密码命令
update mysql.user set authentication_string =password('1234') where user='root' and Host='localhost';
-- 刷新用户权限
flush PRIVILEGES;
-- 查看所有数据库
show databases;
-- 显示数据库中所有的表信息
describe reggie.address_book;
-- 创建数据库
create database if not exists database_001; 
-- 删除数据库
drop database if exists database_001;
-- 使用数据库  
-- (`` 飘号) 重要的表名字或者字段名需要用飘号 特别容易被当做单
use`database_001`;

3 数据库的数据类型

3.1 数值

• tinyint 十分小的数据 1字节
• smallint 较小数据 2字节
• mediumint 中等大小数据 3字节
• int 标准整数 4字节
• bigint 较大的数据 8字节
• float 浮点数 4字节
• double 浮点数 8字节
• decimal 字符串形式浮点数 金融专用

3.2 字符串

• char 固定大小的字符串 0-255
• varchar 可变字符串 0-65535 string
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1

3.3 时间日期

• date YYYY-MM-DD 日期格式
• time HH:mm：ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 常用
• timestamp 时间戳 1970.01.01到现在的毫秒数 常用
• year 年份

3.4 null

没有值 未知，不要使用null进行运算结果为null

4 数据库的字段属性

Unsigned

• 无符号的整数

• 申明该列不能为负数
  zerofil

• 0填充的

• 不足的位数使用0填充 int(4), 1 0001,2 0002
  自增

• 通常默认为在上条数据上加1

• 常用来设计主键index必须是整数类型

• 可以自定义主键自增起始值和步长
  非空 NULL NOT NULL

• null 不写值默认null

• not null 不能为空
  其他 每个表都有的字段、

• id 主键

• version 乐观锁

• is delete 伪删除 管理员查看记录删除时间

• gmt_create 创建时间

• gmt_update 修改时间

5 mysql创建表语句格式

-- 查看数据库下所有表
show tables;
-- AUTO_INCREMENT 自增
-- 字符串使用 '' 括起来
-- 所有语句后加 , 最后一个后面不用加
-- PRIMARY KEY 主键 一个表只能有一个唯一主键
create table if not exists `sch`.`student`(
`id` int(11) not null AUTO_INCREMENT   comment '学号',
`name` varchar(20) not null comment '姓名',
`birth` datetime comment '生日',
`addr` varchar(50) comment '籍贯',
primary key(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--- 创建表格式
create table if not exists `数据库名`.`表名`(
`字段名` 数据类型 [属性] [索引] [注释],
`字段名` 数据类型 [属性] [索引] [注释],
......
`字段名` 数据类型 [属性] [索引] [注释],
)[表类型] [字符集设置] [注释]

6 MySql 表增加修改删除语句

-- 查询时间
select now();
-- 查看用户
select user();
-- 查看数据库
select database();
-- 查看创建数据库语句
show create database database_001;
-- 查看数据表的定义语句
show create table student;
-- 显示表结构
desc student;
-- 修改表名： alter table 旧表名 rename as  新表名；
alter table student rename as student_yong;
-- 刷新
show table status;
show status;
-- 增加表的字段：alter table 表名 add 字段名 列属性；
alter table student_yong add is_del int(1) comment '是否删除 0 否 1 是';
desc student_yong;

-- 修改表字段(修改约束 字段重命名) alter table 表名 modify 字段名 属性；
alter table student_yong modify is_del varchar(1); -- 修改约束
-- alter table 表名 change 字段名 修改后字段名 属性；
alter table student_yong change is_del is_delete int(1); -- 修改字段名
desc student_yong;

-- 删除字段名： alter table 表名 drop 字段名
alter table sudent  drop addr;
desc sudent;
-- 删除表： drop table if exists 表名;
drop table if exists students;

-- 注意事项
-- 1 `字段名`  字段名飘号包裹
-- 2 mysql 注释 -- /**/
-- 3 SQL大小写不敏感 建议小写
-- 4 所有符号全英文
-- 5 创建删除注意加上if exists  判段

7 数据库管理(增删改查)

1. 外键

-- 创建有外键的表
create table if not exists `database_001`.`students`(
`grade_id` int(4) not null auto_increment comment '学号',
`name` varchar(20) comment '姓名',
`age` int(2) comment '年龄',
`class` varchar(8) comment '班级',
`grade` varchar(4) comment '年级',
`addr` varchar(50) comment '籍贯',
primary key (`grade_id`),
key `FK_grade_id`(`grade_id`),
constraint `FK_grade_id` foreign key (`grade_id`) references `students`(`grade_id`)
)engine = INNODB default charset=utf8;

删除有外界关系的表的时候 要先删除引用别人的表 再删除被引用的表 否则报错

-- 另外一种添加外键的方式
-- alter table `表名` add constraint 约束名 foreign key(作为外键的列) references 那个表(`字段名`);
alter table `students`
add constraint `FK_grade_name` foreign key (`name`) references `grade`(`name`);
-- 这些都是添加的物理外键 数据库级别的外键 不建议使用以免造成困扰
-- 数据库是单纯的表只用来存数据 只有行(数据) 列(字段)
-- 要使用多张表 使用外键 用程序实现

3. 添加

-- 插入语句 添加
-- insert into 表名(字段1,字段2,字段3,...) values('值1','值2','值3',...);
insert into grade(`name`,`grade`,`class`) values('徐三爷','17','三年级');
-- 插入多个字段
insert into `grade`(`name`) values('许二爷'),('许大爷');
insert into `grade`(`id`,`name`,`grade`,`class`) values('5','徐老二','一年级','三班'),
('6','徐老wu','一年级','三班'),('7','徐老六','一年级','三班');

– 语法：insert into 表名(字段1,字段2,字段3) values (‘值1’,‘值2’,‘值3’,…);
– 注意事项：
– 1 字段和字段之间用英文逗号 , 隔开
– 2 字段可以省略 但后面的值要一一对应 不能少
– 3 可以同时插入多条数据 VALUES 后面的值 需用逗号 , 隔开即可 values(),(),(),…
5. 修改

-- update 表名 set COLUMN=value,[COLUMN=value,...] where [条件];
-- update `表名` set `字段名` ='值' where `字段名`='值';
-- 注意事项：
-- 1 COLUMN是表字段名 尽量带上``
-- 2 where后面的筛选条件一定加上 否则会修改所有的列
-- 3 多个设置属性之间用英文逗号 , 隔开
update grade set name='小可爱' where id ='1';

6. 删除

-- delete 删除数据：delete from 表名 [where 条件]
delete from test where id='1';
-- truncate 完全清除数表数据 表的结构和索引不会变
-- truncate table 表名
-- 删除 delete 和 truncate 
-- 相同点： 都会删除数据记录 不会删除表结构
-- 不同点：	truncate 重新设置自增列计数器归零 不会影响事务
use database_001;
create table if not exists test(
`id` int(11) not null auto_increment comment '序号',
`name` varchar(50) comment '姓名',
`age` int(2) comment '年龄',
primary key(`id`)
)engine INNODB default charset=utf8;

insert into test values('1','二牛','12'),('2','二狗','12'),('3','二猪仔','10');

truncate  table test;
delete from test;

-- 注意：delete删除后 重启数据库 
-- INNODB 自增列从1开始 因为数据存在内存中 断电即失
-- MYISAM  继续从上一个自增量开始 数据存在文件中 不会丢失

7 DQL(Data Query Language)(查询数据)

7.1 指定查询

-- sql 语句全语法  [] 可选 {} 必选
select [all | distinct]
{* | table.*| [table.field1[as alias1][,table.field2[as alias2]][,...]}
from table_name [as table_alias]
[left | right | inner join [table_name2]] -- 联合查询
[where ...] -- 指定结果需要满足的条件
[group by ...] -- 指定结果按那几个字段分类
[having ...] -- 过滤分组的记录必须满足的次要条件
[order by ...] -- 指定查询记录按一个或者多个条件排序
[limit {[offset,]row_count | row_countoffset offset}];-- 指定查询的记录从那条到那条

-- DQL 指定字段查询
-- 查询全部学生 selet 字段 from 表名 
select * from `student`;
-- 查询指定字段
select `studentno`,`studentname` from `student`;
-- 起别名查询 字段 as 别名 可以给查询字段起别名 也可以给查询表起别名
select `studentname` as `name` from `student` as `stu`;
-- 函数 concat(a,b)
select concat('姓名：',`studentname`) as '新名字' from `student`;

7.2 distinct 去重

-- 查询全部的考试成绩
select * from result;
-- 查询那些同学参见了考试
select `studentno` from result;
-- 去重 去除select 查询语句中重复数据 只显示一条
explain select distinct `studentno` from result;

7.3 数据库的列(表达式)

-- 数据库的列(数据表达式)
-- select 表达式 from 表
-- 数据库中的表达式：文本值 列 null 函数 计算表达式 系统变量 ...
-- 查询系统版本(函数)
select version();
-- 用来计算(表达式)
select 100*3-1 as 计算结果;
-- 查询自增的步长(变量)
select @@auto_increment_increment from result;

7.4 where条件语句

-- 模糊查询 区间
select `studentno`,`subjectno`,`examdate`,`studentresult` from `result` where `studentresult` >=60 and `studentno` >=1002;
-- 查询除了1000号之外所有同学的成绩
select `studentno`,`subjectno`,`examdate`,`studentresult` from 	`result` where `studentno` !=1000;
select `studentno`,`subjectno`,`examdate`,`studentresult` from  `result` where  not `studentno` = 1000;

模糊查询

运算符	语法
IS NULL	a IS NULL a为NULL 结果为真
IS NOT NULL	a IS NOT NULL a为 NOT NULL 结果为真
BETWEEN	a BETWEEN b AND c a在b 和c 之间
LIKE	a like b a匹配b 结果为真
IN	a IN(A1,A2,A3…) a在A1,A2…中 结果为真

select `studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard` 
from `student` where `studentname` like '张%';
select `studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard` 
from `student` where `studentname` like '_十%';
select `studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard` 
from `student` where `address` in('甘肃');
select `studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard` 
from `student` where `address` is null or `address`="";

7.5 联表查询

inner join	如果表中至少有一个匹配就返回行
left join	会从左表返回所有值 即使右表没有匹配
right join	会从右表返回所有值 即使左表没有匹配

-- 联表查询
select * from student union all
select stu.* from student stu left join result res on stu.studentno = res.studentno;
-- 内连接 inner join 表名 on 条件
use school;
select * from student as stu
inner join result as re
on stu.studentno = re.studentno;

select * from student stu
left join result re
on stu.studentno = re.studentno;

select * from student stu
right join result re
on stu.studentno = re.studentno;

select studentno from student stu
union 
select studentno from result re

-- MYSQL 没有full outer join 语法 用union两部分的查询结果实现交集 并集
-- 左连接 和 右连接实现 左右并集的所有部分

select * from student stu left join result re on stu.studentno = re.studentno 
union 
select * from student stu right join result re on stu.studentno = re.studentno;

-- 左连接不包含右边表
select * from student stu left join result re on stu.studentno = re.studentno where re.studentno is null;
-- 右连接不包含左边表
select * from student stu right join result re on stu.studentno = re.studentno where stu.studentno is null;
-- 求以上两步分并集
select * from student stu left join result re on stu.studentno = re.studentno where re.studentno is null
union
select * from student stu right join result re on stu.studentno = re.studentno where stu.studentno is null;

select s.studentno,studentname,subjectname,studentresult
from student s
left join result r
on s.studentno = r.studentno 
inner join `subject` su
on r.subjectno = su.subjectno;

-- 左连接 left join 以左表为基础 返回左表全部记录和on条件与左表关联的表记录
-- 右连接 right join 以右表为基础 返回右表全部记录和on条件与右表关联的表的记录
-- 内连接 inner join 两个表条件都满足才返回符合条件的记录 
-- mysql 没有全外连接