MySql Study Notes

mysql version : 5.7.*

---

基本使用

1. 登录
   1. 打开cmd命令窗口 windows+r 输入cmd
   2. mysql -u用户名 -p密码

mysql -uroot  -p123

2. 退出

exit;

数据库操作

1. 查看所有的数据库

show databases;

2. 创建数据库

-- create database  数据库名  default charset=utf8;
create database test default charset=utf8;

3. 删除数据库

-- drop database 数据库名
drop database test;

4. 使用数据库

-- use 数据库名;
use test;

数据类型

1. 数值
   1. tinyint
   2. int
   3. float
   4. decimal (以字符串的形式存储小数)
2. 字符串
   1. char 定长字符串,最大255
   2. varcahr 变长字符串
   3. text 长文本
3. 日期
   1. timestamp 以年月日形式存储一个时间戳

注: 除了数值型,其他类型在使用时都需要用引号("")括起来

字段约束

1. unsigned 无符号的整数(正数)
2. auto_increment 自增(必须是主键才能自增)
3. not null 这一列的值不能为null
4. default 默认值(如果插入/修改时字段值为空就采用默认值)

数据表操作

1. 创建表

-- create table 表名(
-- 字段1  数据类型  字段约束,
-- 字段2  数据类型  字段约束,
-- ....
-- 字段n  数据类型  字段约束
-- );

create table user(
    id int unsigned primary key auto_increment,
    name char(4) not null,
    sex  char(1)  not null,
    age  tinyint not null
);

2. 删除表

-- drop table 表名
drop table user;

3. 修改表结构

   1. 添加字段

   -- alter table 表名 add 要添加的字段名 添加字段的属性
   -- 向 user 表中添加 一个 height 字段
   alter table user add height int unsigned not null;
   

   2. 删除字段

   -- alter table 表名 drop 字段名
   -- 删除 user 表中的 height 字段
   alter table user drop height;
   

   3. 修改字段( 索引不会丢失 )

   -- 修改字段属性:alter table 表名 modify 字段名-字段属性;
   -- 将user表中的 age 字段修改为可以为 不能为负数
   alter table user modify age tinyint unsigned not null;
   
    -- 修改字段名:alter table 表名 change 原字段名 新字段名-字段类型-字段属性;
    -- 将user表 sex 字段修改为 gender
    alter table user change sex gender int tinyint unsigned not null;
   

   4. 修改表名称

   -- rename table 原表名 to 新表名;
   rename user to users;
   

4. 查看表

-- desc 表名
-- 查看表结构
desc user;

-- 查看数据库中所有的数据表
show tables;

数据表数据操作

1. 添加数据

-- 添加一条数据: insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);
-- 此时id是自增属性,可以不填写
insert into user(name,age) values('user1',18);

-- 添加多条数据: insert into 表名(字段1,字段2...字段n) values(值1,值2...值n),(值1,值2...值n);
insert into user(name,age) values('user2',20),('user3',22),('user4',24);

注 : 插入数据是,字段和值必须一一对应,插入多个数据是,括号之间必须用逗号(,)隔开

2. 删除数据

-- 删除数据(自增属性不会重置): delete from 表名 where 条件
delete form user where id=1;

-- 清空数据(自增属性会重置): truncate 表名
truncate user;

注 : 删除数据是必须要写明 where 条件,不然会删除表中所有的数据

3. 修改数据

-- update 表名 set 字段1=值1,字段2=值2...字段n=值n where 字段=值;
update user set name='testUserName',age=30  where id=1;

注 : 删除数据是必须要写明 where 条件,不然会修改表中所有的数据

查询数据(重点)

查询数据的关键字使用顺序(不能修改):

select => from => where => group by => having => order by => limit

1. 查询所有数据

   -- select * from 表名
   select * from user;
   

2. 查询指定字段

   -- select 字段1,字段2..字段 from 表名
   select name,age from user;
   
   -- 查询指定字段并去重: select distinct 字段 from 表名
   -- 查询user表中所有不重复的名字
   select distinct name from user;
   

3. 带条件的查询 where

   -- select *|字段列表 from 表名 where 条件
   select * from user where id=1;  -- 查询user表中id为一的数据
   select * from user where id>3;  -- 查询user表中所有id大于3的数据
   select * from user where id>=3; -- 查询user表中所有id大于等于3的数据
   select * from user where id<5;  -- 查询user表中所有id小于5的数据
   select * from user where id<=5; -- 查询user表中所有id小于等于5的数据
   select * from user where id in(1,3,5);         -- 查询user表中id是1或3或5的数据
   select * from user where id between 10 and 20; -- 查询user表中id在10到20之间的数字
   select * from user where name is null;     -- 查询user表中所有name字段值为null的数据
   select * from user where name id not null; -- 查询user表中所有name字段值不为null的数据
   select * from user where id>3 and name is not null; -- 查询user表中id大于3并且name不为空的数据
   select * from user where id>3 or name is not null;  -- 查询user表中id大于3或者name不为空的数据
   

4. 模糊查询 like

   -- select * from 表名 where 字段 like '%需要查找的内容%';
   -- 查询所有user表中name字段包含 测试 的数据
   select name,age from user where name like '%测试%';
   
   -- 查询所有user表中name字段包含所有 是以任意字符开头并且以测试结尾的数据
   select name,age from user where name like '_测试';
   
   -- 查询所有user表中name字段包含所有 是以任意字符开头并且以测试开头的数据
   select name,age from user where name like '测试_';
   

   模糊查询特殊字符:

   1. _____ : 任意一个字符
   2. %: 任意0个或多个字符

5. 分组聚合(重在理解,没有数据,笔记难做)

   1. 数据分组 group by

   -- select 分组字段 from 表名 group by 分组字段;
   -- 根据性别把所有数据分为两组,并查看每组有多少数据
   select sex,count(*) from user group by sex;
   
   -- 根据性别把所有数据分为两组,并查看每组有多少数据
   
   

   注: 一般所有分组字段(group by后的字段)也需要写在select后面

   2. 聚合函数

   -- 最大值: max()
   -- 最小值: min()
   -- 平均值: avg()
   -- 求总和: sum()
   

   注: 所有的聚合函数都是对分组之后的数据进行操作的

6. 排序 order by

-- 对查询后的数据进行排序
-- select * 字段列表 from 表名 order by 字段1,字段2 [desc];

-- 根据 age 字段升序排序,默认升序
select * from user order by age;

-- 根据 age 字段降序排序后,然后根据id字段升序排序
select * from user order by age desc,id;

7. 分页

-- select * from 表名 limit 从第n条数据开始显示,显示m条数据

-- 从user表中查询数据,从第5条开始显示,显示5条
select * from user limit 5,5;

-- 从user表中查询数据,从第0条开始显示是,0可以不写
select * from user limit 5;

8. 多表查询(多个表必须建立关系)
   1. 内联查询

-- select * from 表名1,表名2 where 表名1.表名1有关系的字段 = 表名2.表名2有关系的字段

select * from user,info where user.id = info.id;
-- 或者用以下这种方式
select * from user inner join info on user.id=info.id;

2. 左|右连接(两种方式一样的)

select * from users left join score on  user.id=score.id;

其他笔记

修改 Mysql 默认字符集 ( wampserver )

mysql服务器和客户端都使用utf8的编码:
1)找到mysql的配置文件【 my.ini 】
2)找到[client]后面加【 default-character-set = utf8 】
3)找到[wampmysqld64]后面加【 character-set-server = utf8 】
4)找到[wampmysqld64]后面加【 collation-server = utf8_general_ci 】

5. 使用【s】命令查看字符集编码(如果结果如下说明修改成功)
   Server characterset: utf8
   Db characterset: utf8
   Client characterset: utf8
   Conn. characterset: utf8

如果以上不管用就请使用以下方法:-------------------

1)打开mysql中的配置文件: my.ini
2)找到[client] 在其下面添加: default-character-set=utf8
3)找到[mysql]在其下面添加 : default-character-set=utf8
4)找到[mysqld](一般是在最后面那行)下面添加 : character-set_server=utf8
5)加完之后保存,重启mysql服务,再执行 show variables like '%char%'; 查看是否修改成功

查看MySQL默认字符集编码

查看服MySQL支持的编码: show character set

查看MySQL服务器和客户端的字符集: show variable like %char%

修改Mysql密码( 默认没密码 )

1. mysql -uroot -p
2. update mysql.user set authentication_string=password('密码') where user='root'
3. flush privileges; (或者重启MySQL服务)

此时 密码 就是需要修改的密码

数据创建

1. 导入数据

-- 在 windows dos 命令行中
-- mysql -u用户名 -p密码 数据库名 数据表名 sql文件名
mysql -uroot -p123 数据库名 < 1.sql

2. 导出数据

注: 导出数据会生成对应的sql文件,导入数据是,需要有相应的表结构

-- 在 windows dos 命令行中
-- mysqldump -u用户名 -p密码 数据库名 数据表名>sql文件名
mysqldump -uroot -p123 test user > 1.sql

修改默认表引擎

1. 找到mysql的配置文件 my.ini

# 查找 default-storage
# 将 default-storage-engine=MYISAM 修改为 default-storage-engine=InnoDB
default-storage-engine=InnoDB

两个表引擎之间的区别:

1. innoDB 支持外键,支持事务 MYISAM 不支持外键,不支持事务