mysql笔记

mysql笔记

开启mysql服务

net start mysql;

关闭mysql

net stop mysql;

连接数据库

mysql -u root -p;

创建数据库

create datebase 库名 charset utf8;

查看数据库

show databases;

进入数据库

use 库名;

删除库

drop database if exists 库名;

查看表的结构

show create database test;

导入SQL文件

source sql文件地址

创建表

 create table 表名 (
   id int primary key auto_increment, 
   // 字段名 类型 是否为空
   name varchar(30) not null, 
   description varchar(100) null
   ) charset utf8;

增加表结构

alter table 表名 add 键名 varchar(2) null

查看表结构

desc 表名;

删除表

drop table if exists 表名

向表内添加数据

// 单条插入
insert into 表名 set 键 = '值';

// 多条插入
insert into  表名 (键名用逗号隔开) values(值同样用逗号隔开),(值同样用逗号隔开)

复制表

create table 新表名 like 旧表名;

查询表数据

// 查询所有
select * from 表名;

// 按需查询
select 键名1,键名2 from 表名;

// 条件查询
select * from 表名 where id > 1;

distinct 去重

where 判断

or 或者
in (2, 3) 包含
not in(2, 3) 不包含
like 包含 '%中文%'
not like 不包含 '%中文%'
and 并且 必须全部满足
between 介于 10 and 100;
not between 不介于 10 and 100;

mysql对null的处理

 select * from 表明 where 键名 is not null;
is null 等于空
is not null 不等于空

select name,ifnull(content,'测试信息') from class;

排序

select * from 表名 order by 键名 asc;
desc // 从大到小排序
asc  // 从小到大排序
键名 asc, 键名2 asc; 可以写多个字段

order by id asc limut 3; 取前三的值;
order by id asc limut 1,2; 1代表从哪开始取 2代表取几个;

order by id asc limit 3; 取前三的值;

// 当有相同的值
select * from class where age = (select age from class where age is not null order by age asc limit 1);

随机排序

order by rand() desc; 

自定义排序

field(键名, '张'. '李');

修改表数据

update 表名 set 键名 = 值 where(条件) 键名 is null;

删除表数据

delete from 表名 where(条件) 键名 is null;

日期高级玩法

根据时间函数格式化想要的时间

select name,date_format(时间键名, '%Y年%m月%d日') from 表名;

时间函数

获取当前时间

select CURRENT_DATE,CURRENT_TIME,NOW();

获取时间部分值

select YEAR(updated_at),MONTH(updated_at),DAY(updated_at) from 表名;

统计 / 分组

AVG() 返回平均值;
count() 返回某列的行数
max()   返回某列的最大值
min()   返回某列的最小值
sum()   返回某列的和

-- 分组 group by
select count(id), class_id from class where left(updated_at, 4) > '2000'
group by class_id  // 根据 谁
order by count(id) desc
limit 1;

-- 增加 having 子句原因是，WHERE 关键字无法与合计函数一起使用。
having 键名 > 1;

查询每个数据

select * from 表 where 键名 in (select min(键名) from stu group by  键名)

group by 和 order 的区别

数据类型

字符串类型

非二进制

char      定长 
varchar   变长
text      长文本

字符集

utf8

校对规则

utf8_general_ci   不区分大小写

字符串函数

left(键名,个数)
right(键名,个数)
mid(键名,位置,个数)
mid(键名,位置) 从位置开始取全部

concat(str1,str2,…)  连接字符串

-- 截取字符串
substring（str, pos）
substring（str, pos, length）
说明：substring（被截取字段，从第几位开始截取）
substring（被截取字段，从第几位开始截取，截取长度）

-- 返回字符串的长度
CHAR_LENGTH()

-- if判断
select if(条件, 结果) from 表;

正则表达式在mysql内的使用

select * from class where REGEXP '^.h';

数值类型

tinyint 一个字节 0 或 1
int

-- 浮点数和定点数
float 7位以内数值精准

-- 枚举类型 (单选)
enum('男', '女') // 1 男 2  女

-- 多选
set
-- 二进制模糊匹配

多表查询

一对一 有一个主表和从表
一对多 多记录少的
多对多 中间表

笛卡尔积

  select class.sname, sty.name from class, sty sty where class.class_id = sty.class_id 

全连接 INNER JOIN

select class.name, sty.name from class inner join sty 
on 条件 
inner join 表名
on 条件

外连接 right join / left join

外链接包括LEFT JOIN 与 RIGHT JOIN ，
可以简单理解为 
LEFT JOIN  会包含左侧所有表记录
RIGHT JOIN 会包含右侧表全部记录

另一边没有数据就不会展示出来

自连接

select * from sty inner join sty on sty.class_id = sty.class_id where sty.sname = '帅子' and sty.sname != '帅子'

## UNION

```sql
select * from class 
union
select * from class;

union 合并两张表 并去除重复的数据
union all 合并两张表 不去除重复的数据

删除多表操作

DELETE s FROM stu as s 
LEFT JOIN user_lesson as ul
ON s.id = ul.stu_id
WHERE ul.lesson_id IS NULL;

事务

begin;
insert into stu(name, sex) values('帅子', '22');
insert into class(name, sex) values('帅子', '22');
commit;   提交事务
ROLLBACK; 回滚事务

全局事务

set autocommit = 0;
insert into stu(name, sex) values('帅子', '22');
insert into class(name, sex) values('帅子', '22');
commit;   提交事务
ROLLBACK; 回滚事务