一、null的注意事项
null意味着没有值 或者 未知值
可以测试某个值是否为null is null
不能对null进行算数运算
所有和null进行运算的都为null
二、mysql的索引
主键索引 primary key
唯一索引 unique
常规索引 index
全文索引 fulltext
(1) 主键索引
主键索引是数据库中最常见的索引类型 主要确定数据表里数据记录的位置 给字段添加primary key 来确定索引值
注意:
每个表中 只能有一个主键索引
每个表中最好有一个主键索引 并不是必须的
主键索引可以有很多的候选项 (auto_increment,not null)
当表中的数据 被删除以后 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) 常规索引
常规索引 只作为提高数据的查询效率来使用的
缺点:
提高了查询效率 但是降低了增删改的效率
索引文件 占用磁盘空间
数据库文件存放的位置
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的区别
MyISAM表的存储文件为3个 InnoDB为2个
MyISAM不支持事物 innodb支持
MyISAM不支持外键 innodb支持
MyISAM表的查询效率高于innodb 但是innodb的安全性高于MyISAM
(1) MyISAM的文件说明
.frm文件 存储当前表结构的文件
在innodb和MyISAM中都存在
.MYD: 即MY DATA 存储表数据的文件
.MYI:即 MY INDEX 存储表索引的文件
(2) InnoDB的文件说明
.frm文件 存储当前表结构的文件
在innodb和MyISAM中都存在
.ibd 存储表数据和索引
(3) innodb事物处理操作
将当前表文件存储类型改为 innodb
alter table 表名 engine=innodb;
查看当前表的提交类型
select @@autocommit
如果值为1 则为自动提交
改为手动提交(开启事务)
set autocommit=0
事物开始
begin;
执行各种SQL语句
提交或者回滚
commit work;
rollback work;
注意:
事物是针对库中表的数据来操作的 并不是针对你当前的库 如果库被干掉了,那就一切都不存在
四、更改表结构
给当前的表添加一个新的字段 add
alter table user add sex enum('w','m') default 'w';
添加新字段在某个字段的后面 after
alter table user add info varchar(100) default '个人说明' after id;
添加新字段在第一位 first
alter table user add infoop varchar(100) default '个人说明' first;
更改字段名称并更改字段顺序 change
alter table user change infoop money decimal(6,2);
alter table user change money money decimal(6,2) after username;
更改字段类型 modify
alter table user modify username char(11);
添加字段索引
alter table user add index(username); #添加常规索引
alter table user add unique(username);
alter table user add index infoindex(info); 添加索引并添加索引名称
删除索引
alter table user drop key username;
创建一个表b和表a一样
create table b like a;
查看所有的索引
show index from 表名;
更改字段的字符集
alter table user modify username varchar(20) character set utf8;
五、INSERT 数据的添加
指定字段名称添加数据
insert into user(username,age,info) values('张三',18,'个人说明');
需要注意:如果有字段不为空且没有默认值 那么必须插入值
不指定字段添加值(需要将所有字段都插入值)
insert into user values(null,'李四',20,'个人说明李四')
指定字段添加多个值
insert into user(username,age,info) values('王五',30,'王五的说明'),('赵六',33,'赵六的个人说明')...;
不指定字段添加多个值
insert into user values(nulll, '王五',30,'王五的说明'),(null, '赵六',33,'赵六的个人说明')...;
六、SELECT 数据的查询
不指定字段查询(查询所有字段)
select * from 表名;
指定字段查询
select 字段名1,字段名2... from 表名;
查询字段并运算
select id+age from user;
起别名
select username as name from 表名;
select username name from 表名;
七、where 条件
(1) 比较运算符
>
<
>=
<=
!=/<>
=
(2) 逻辑运算符
逻辑与 and
select * from user where username='苍老师' and age<=30;
俩侧为真才为真
逻辑或 or
select * from user where username='苍老师' or age>=30;
只要满足一个就为真
and 和 or 一起使用
select * from user where id=1 or (age=18 and sex='w');
在...内 in
select * from user where id in(1,3,5,6,7); #和下面的写法一个意思
select * from user where id=1 or id=2 or id=6;
不在...内 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;
在...范围内 between ... and ...
select * from user where age between 20 and 33;和下面的写法一个意思
select * from user where age>=20 and age<=33;
不在...范围内 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 排序
默认升序 asc
select * from user order by age;
select * from user order by age asc;
降序 desc
select * from user order by age desc;
(4) is is not
is not
select * from user where username is not null;
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
包含查询
like ‘%字符%’
以某个字符开头的查询
like '字符%'
以某个字符结尾的查询
like '%字符'
%表示任意多个任意字符,_表示一个任意字符
八、聚合函数
最大值
max()
select max(age) from user;
最小值
min()
select min(age) from user;
统计
count()
select count(*) from user;
平均值
avg()
select avg(age) from user;
求和
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;