MySQL学习笔记

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

简单的操作：
创建数据库：create database 数据库名；
进入当前数据库：use 数据库名;
查看数据表：show tables;
创建数据表：create table 表名(
    属性名 类型(长度)，
    属性名 类型(长度)，
)；
表中插入数据：insert into 表名 values('属性','属性');;
查看数据表中数据：select * from 表名;

数据库的操作

创建数据库：[]内容为可选（可写可不写）

create database [if not exists] db_name

     [charset=字符编码] [collate 校验规则]；

查看数据库服务器有哪些数据库：show databases;

查看数据库字符编码：show create database db_name;

修改数据库（改字符编码、改校验规则）：alter database db_name charset=新的字符编码；

删除数据库：drop database db_name;

备份数据库：
操作系统命令行下：mysqldump -u 用户名 -p 要备份的数据库名 > 存储备份文件路径
备份多个数据库：mysqldump -u 用户名 -p -B 要备份的数据库名1 数据库名2 > 存储备份文件路径
（加-B之后可以直接恢复）

恢复数据库：
mysql命令行下：source 存储备份文件路径；

数据表操作

创建表：

create table table_name(
    字段名称1 字段类型，
    字段类型2 字段类型，
    ……
)[charset=字符编码 collate 校验规则 engine 存储引擎];
create table user(
    id int,
    name varchar(10),
    password varchar(20)
);

查看表结构：desc table_name；

修改表：
新增字段（属性）：
alter table db_name add 新属性名称 新属性类型 [after 现有属性]；
alter table user add salary decimal(4,2) after 属性名称；（after放在那个属性之后）

修改现有字段类型
alter table table_name modify 现有属性名称 修改后的类型；
alter table user modify name varchar(20);//修改name属性类型为varchar(20)

删除某一属性（某一列）
alter table table_name drop 属性名;
alter table user drop salary;

修改数据表名
alter table table_name rename to 新表名;
alter table user rename to users;

修改表的字符集：

alter table table_name charset=新字符集;

修改属性名称：
alter table table_name change 现有属性名 修改后属性名 修改后的类型;（不修改类型也要加原来的类型）
alter table users change id qqid int;

数据类型

数值型
整型就用int(bigint)

浮点型
float(m,n):默认精度为7
decimal(m,n):m表示总位数，n表示小数位数，精度最高，小数就用decimal
decimal(4,2) : -99.99-99.99

字符串类型
char(L):定长字符串
varchar(m):不定长字符串
create table tt2(
    id int,
    c1 char(2),
    c2 varchar(2)
);
定长字符串检索效率高，空间利用率低；(身份证、电话号、银行卡号)
变长字符串检索效率低，空间利用率高。(姓名、密码、地址)

日期类型
datetime(日期时间类型):yyyy-mm-dd HH:ii:ss 占8字节
date(日期):yyyy-mm-dd 占3字节
timestamp(时间戳):yyyy-mm-dd HH:ii:ss 占4字节，无须用户管理，
每当有数据的新增或者修改，都会填充当前时间

enum和set
enum:单选类型

set:多选类型
find_in_set('要查找的数据',set集合)
find_in_set('a','a,b,c');

create table votes(
    username varchar(20),
    hobby set('写代码','吃饭','睡觉','看代码'),
    sex enum('男','女')
);
select * from votes where find_in_set('写代码',hobby);

表的增删改查

增加（insert into）
insert into table_name[列名]
    values(要插入的值，……)，(要插入的值……)……
 insert into users values(1,'123','123456');

更新（update）
update table_name set 属性名=属性值，…[where条件] [limit n]
没有where语句的更新为列属性更新、列更新时可以搭配limit（前多少个）来限制更新个数
有where语句时，只会改变where条件的相应行

删除
delete from table_name [where 条件]
不带where语句delete为一行一行删，返回删除的总个数（全表删除）。
truncate table table_name，直接把文件大小改为0，不会返回删除个数，速度更快。

单表查询
select [distinct] [*]| [列名1,列名2，……] from table_name;

去重查询：select distinct 列名 from table_name;

select可以为结果加一个别名 as
查询总成绩：select name,(math+chinese) as '总成绩'
        from student;

select语句的where子句
where子句对查询进行过滤,不可使用select起的别名。
查询所有语文大于数学成绩的同学：select name
                from student
                where chinese>math;

查询语文分数在80，90之间的学生：select name
                from student
                where chinese between 80 and 90;

select语句的order by语句
select [distinct] [*]| [列名1,列名2，……] 
    from table_name;
    [where]
    order by 列名 asc | desc
asc升序（默认），desc降序
order by语句出于select语句末尾
order by语句可以使用select起的·别名

对所有语文成绩排序
select chinese
    from student
    order by chinese；

select 分页查询
select 字段名 from 表名 where 条件 limit 起始位置,记录条数
查询所有学生信息，每页显示5条记录，请分别显示第一页，第二页。
select * 
    from student
    limit 5,5;

grop by语句
添加条件使用having

多表查询
显示员工姓名、工资以及所在部门的名字
select ename,sal,dname
    from emp,dept
    where emp.deptno = dept.deptno;

显示部门号为10的部门名，员工名和工资
select dname,ename,sal
    from emp,dept
    where emp.deptno = dept.deptno
    and emp.deptno = 10;

子查询：嵌套在其他sql语句中select语句,也叫嵌套查询
显示和'SMITH'同一部门的所有员工信息
select *
    from emp
    where deptno = (select deptno 
    from emp
    where ename = 'SMITH') and ename <> 'SMITH';

索引:以空间换时间(b+树)

create index index_name on 表名(作为索引的列名)

索引的分类
a.主键索引
b.唯一键索引
c.普通索引
create index emp_idx on emp(empno);
d.全文索引(text 大文本类型)

主键索引>唯一索引>普通索引

索引的使用
a.查询索引
show keys from 表名
b.删除索引
alter table 表名 drop index 索引名

创建索引的原则
a.比较频繁作为查询条件的字段应该创建索引。
b.唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件。(例如性别字段)
c.更新非常频繁的字段不适合创建索引，频繁修改二叉树结构
d.不会出现在where子句中的字段不适合创建索引

事务 - ACID特性

概念:
一系列的sql语句在开启事务后，要么全部发生，要么一个都不发生。

ACID:
A:原子性
事务是一个不可分割的工作单位，事务中的所有操作，要么都发生，要么都不发生。
C:一致性
事务使得数据库始终保持在一个一致的状态。
I:隔离性
多个用户并发访问数据库时，
数据库为每一个用户开启的事务不会被其他用户操作数据库所干扰。
D:持久性
事务一旦提交，对数据库的修改就是永久性的。