MySQL数据库2

一、null的注意事项

1. null意味着没有值 或者 未知值
2. 可以测试某个值是否为null is null
3. 不能对null进行算数运算
4. 所有和null进行运算的都为null

二、mysql的索引

1. 主键索引 primary key
2. 唯一索引 unique
3. 常规索引 index
4. 全文索引 fulltext

(1) 主键索引

主键索引是数据库中最常见的索引类型 主要确定数据表里数据记录的位置 给字段添加primary key 来确定索引值

注意：

1. 每个表中 只能有一个主键索引
2. 每个表中最好有一个主键索引 并不是必须的
3. 主键索引可以有很多的候选项 （auto_increment,not null）

4. 当表中的数据 被删除以后 auto_increment 依然记录着下一个数据插入的行号值

   • truncate 表名 清空表 并将自增归位
   • alter table 表名 auto_increment=1

实例

mysql> create table myindex(
    -> id int unsigned primary key auto_increment not null
    -> );

(2) 唯一索引

唯一索引和主键索引 都一样 不能插入重复的值 不同的是 主键一个表只能存在一个 唯一索引可以存在多个

实例

mysql> create table myunique(
    -> username varchar(20) unique not null
    -> );

(3) 常规索引

常规索引 只作为提高数据的查询效率来使用的

缺点：

1. 提高了查询效率 但是降低了增删改的效率
2. 索引文件 占用磁盘空间

数据库文件存放的位置

windwos: C:ProgramDataMySQLMySQL Server 5.7Datahz03

ubuntu: /var/lib/mysql

(4) 全文索引

fulltext

alter table 表名 add fulltext 索引名称(索引字段)

创建表并添加索引的完整写法

mysql> create table user(
-> id int unsigned primary key auto_increment not null,
-> username varchar(20) unique,
-> age tinyint,
-> index(age)
-> #unique(username)
-> );
#给索引添加索引名称
mysql> create table user2(
    -> username varchar(20),
    -> age tinyint,
    -> unique myname(username),
    -> index aindex(age)
    -> );

例子的创建表

mysql> create table test(
    -> id int unsigned primary key auto_increment not null,
    -> username varchar(50) not null,
    -> userpass varchar(50) not null,
    -> telno varchar(20) not null,
    -> sex enum('w','m') not null default 'm',
    -> birthday date not null default '0000-00-00',
    -> index(username),
    -> index(userpass),
    -> unique(telno)
    -> );

三、表的存储类型

数据库引擎：MyISAM和InnoDB

索引顺序存取方法（ISAM, Indexed Sequential Access Method）

修改类型

alter table 表名 engine=表存储类型;

MyISAM和InnoDB的区别

1. MyISAM表的存储文件为3个 InnoDB为2个
2. MyISAM不支持事物 innodb支持
3. MyISAM不支持外键 innodb支持
4. MyISAM表的查询效率高于innodb 但是innodb的安全性高于MyISAM

(1) MyISAM的文件说明

1. .frm文件 存储当前表结构的文件

   在innodb和MyISAM中都存在

2. .MYD: 即MY DATA 存储表数据的文件
3. .MYI:即 MY INDEX 存储表索引的文件

(2) InnoDB的文件说明

1. .frm文件 存储当前表结构的文件

   在innodb和MyISAM中都存在

2. .ibd 存储表数据和索引

(3) innodb事物处理操作

1. 将当前表文件存储类型改为 innodb

   alter table 表名 engine=innodb;

2. 查看当前表的提交类型

   select @@autocommit

   如果值为1 则为自动提交

3. 改为手动提交(开启事务)

   set autocommit=0

4. 事物开始

   begin;

5. 执行各种SQL语句

6. 提交或者回滚

   1. commit work;
   2. rollback work;

注意：

事物是针对库中表的数据来操作的 并不是针对你当前的库 如果库被干掉了，那就一切都不存在

四、更改表结构

1. 给当前的表添加一个新的字段 add

   alter table user add sex enum('w','m') default 'w';

2. 添加新字段在某个字段的后面 after

   alter table user add info varchar(100) default '个人说明' after id;

3. 添加新字段在第一位 first

   alter table user add infoop varchar(100) default '个人说明' first;

4. 更改字段名称并更改字段顺序 change

   alter table user change infoop money decimal(6,2);

   alter table user change money money decimal(6,2) after username;

5. 更改字段类型 modify

   alter table user modify username char(11);

6. 添加字段索引

   alter table user add index(username); #添加常规索引

   alter table user add unique(username);

   alter table user add index infoindex(info); 添加索引并添加索引名称

7. 删除索引

   alter table user drop key username;

8. 创建一个表b和表a一样

   create table b like a;

9. 查看所有的索引

   show index from 表名;

10. 更改字段的字符集

    alter table user modify username varchar(20) character set utf8;

五、INSERT 数据的添加

1. 指定字段名称添加数据

   insert into user(username,age,info) values('张三',18,'个人说明');

   需要注意：如果有字段不为空且没有默认值 那么必须插入值

2. 不指定字段添加值（需要将所有字段都插入值）

   insert into user values(null,'李四',20,'个人说明李四')

3. 指定字段添加多个值

   insert into user(username,age,info) values('王五',30,'王五的说明'),('赵六',33,'赵六的个人说明')...;

4. 不指定字段添加多个值

   insert into user values(nulll, '王五',30,'王五的说明'),(null, '赵六',33,'赵六的个人说明')...;

六、SELECT 数据的查询

1. 不指定字段查询（查询所有字段）

   select * from 表名;

2. 指定字段查询

   select 字段名1,字段名2... from 表名;

3. 查询字段并运算

   select id+age from user;

4. 起别名

   select username as name from 表名;

   select username name from 表名;

七、where 条件

(1) 比较运算符

1. >
2. <
3. >=
4. <=
5. !=/<>
6. =

(2) 逻辑运算符

1. 逻辑与 and

   select * from user where username='苍老师' and age<=30;

   俩侧为真才为真

2. 逻辑或 or

   select * from user where username='苍老师' or age>=30;

   只要满足一个就为真

3. and 和 or 一起使用

   select * from user where id=1 or (age=18 and sex='w');

4. 在...内 in

   select * from user where id in(1,3,5,6,7); #和下面的写法一个意思

   select * from user where id=1 or id=2 or id=6;

5. 不在...内 not in

   select * from user where id not in(1,3,5,6,7); #和下面的写法一个意思

   select * from user where id!=1 and id!=2 and id!=6;

6. 在...范围内 between ... and ...

   select * from user where age between 20 and 33;和下面的写法一个意思

   select * from user where age>=20 and age<=33;

7. 不在...范围内 not between ... and ...

   select * from user where age not between 20 and 33;和下面的写法一个意思

   select * from user where age not between 20 and 33;

(3) order by 排序

1. 默认升序 asc

   select * from user order by age;

   select * from user order by age asc;

2. 降序 desc

   select * from user order by age desc;

(4) is is not

1. is not

   select * from user where username is not null;

2. is

   select * from user where username is null;

(5) limit

limit num 直接取出num条数据

select * from user order by age desc limit 1; #取出年龄最大的一条数据

limit x,num 从x的位置取出 num条数据

注意：

如果是从头取出num条数据 limit num == limit 0,num

分页计算：

求分页取值的偏移量

（nowPage-1）*everyPage

(6) 模糊查询 like

1. 包含查询

   like ‘%字符%’

2. 以某个字符开头的查询

   like '字符%'

3. 以某个字符结尾的查询

   like '%字符'

4. %表示任意多个任意字符，_表示一个任意字符

八、聚合函数

1. 最大值

   max()

   select max(age) from user;

2. 最小值

   min()

   select min(age) from user;

3. 统计

   count()

   select count(*) from user;

4. 平均值

   avg()

   select avg(age) from user;

5. 求和

   sum()

   select sum(age) from user;

九、group by 分组

查询男女分别多少人

select sex,count(*) from user group by sex;

统计每个年龄段分别多少人

select age,count(*) from user group by age;

统计每个年龄段的男女分别有多少人

select sex,age,count(*) from user group by sex,age;

groub by having 其中的having相当于where

查询每个年龄段人数大于1人的数据

select age,count(*) as count from user group by age having count>1;

查询每个年龄段人数大于1人的数据 并且年龄小于40

select age,count(*) as count from user group by age having count>1 and age<40;

查询每个年龄段人数大于1人的数据 并且年龄为10,20,30

select age,count(*) as count from user group by age having age in(18,20,30);

十、delete 删除

主体结构

delete from 表名 [where...]

注意：

where 应该加 如果没有where条件删除所有数据

实例

delete from user where username='xxx';

十一、update 修改

主体结构

update 表名 set 字段名=字段值,[字段名=字段值...[where.....]]

注意：

不要忘记where条件 否则修改全部

实例

update user set sex='m' where age in(18,20);

十二、多表联查

关联条件

外键

(1) 隐式内连接

select * from user,goods where user.id=goods.uid
select user.username,user.age,goods.goodsname from user,goods where user.id=goods.uid

起别名

select u.username,g.goodsname from user u,goods g where u.id=g.uid

(2) 显示内连接 inner join on

select * from user INNER JOIN goods ON user.id=goods.uid and goods.uid=1

select u.username,g.goodsname from user u INNER JOIN goods g ON u.id=g.uid and g.uid=1

注意：

隐式内连接和显示内连接其实是同一个查询 会将关联的数据全部查询出来

(3) 左链接 left join on

select u.username,g.goodsname from user u LEFT JOIN goods g ON u.id=g.uid

注意：

左链接会将左表作为主表 右表为辅表 会将主表所有数据查询出来 辅表没有关联的数据使用null来占位

select * from goods g left JOIN user u on u.id=g.uid

(4) 右链接 right join on

select u.username,g.goodsname from user u right JOIN goods g ON u.id=g.uid

select * from user u right JOIN goods g on u.id=g.uid

十三、以下代码作为了解

(1) 使用mysql库

use mysql;

(2) 查询当前有哪些用户

select user from user;

(3) 创建其它用户

create user zhangsan identified by '123456';

(4) 赋予权限

grant all on hz03.* to zhangsan;

all 代表所有权限

(5) 回收权限

revoke all on hz03.* from zhangsan;

(6) 删除用户

drop user zhangsan;