修改update 直接表名,不用写table
平均成绩,注意(linux+math)/2是加号,avg是求每科的平均成绩
统计班里的人数,统计人数count(name)
where linux=(select max(linux) from t1);
查询有个同学叫...j?n...的:
select * from t1 where regexp name=(.*j.n.*);不存在,where后面要接列名
select * from t1 where name regexp ‘.*j.n.*’;才是对的(regexp 后面的内容用单引号引起来)
select * from t1 where name like ‘%j_n%’;使用通配符而不使用正则表达式因为通配符运行效率更快(使用%的话前面需要加like,并且%的内容要用单引号引起来,别忘了下划线_是代表任意一个字符)
数据类型:数值类型、字符串类型、日期时间类型
数值类型:整型和浮点型
1个字节有8位,最大有8个1,2的8次方-1是255
无符号(正的):0~255:-128~127
int占4个字节32位,最大有32个1,2的32次方-1是4294967295
int占
和长度无关,和类型有关
如若写了int(1) unsighed无符号整型,就会忽视int设置的1长度,会取长度较大的类型:unsighed
浮点型:
float(M,D) M是总长度,D小数的位数,存储空间和tinyint一样是4个字节(tiny的范围是0~255)
start:
create table t4(fl float(10,4),db double(10,4));
insert into t4(表名) values(??);
2.浮点型
float(M,D) M是总长度,D小数的位数 存储空间4B
double(M,D) 存储空间8B
实验:
create table fb(fl float(1,1));
insert into fb values(0.75);结果是0.8,发现会四舍五入而且只显示1位小数,且最大是0.9,若插入0.945,会认为是0.94,然后精确到0.9(四舍五入),注意他的这个四舍五入有点假,估计他制作2,1的时候忘了5要入吧,
create table fb(fl float(2,1));
insert into fb values(9.96);不行因为四舍五入为10 了
规律:
右边是1的情况下:
左边若是1,则1减1为0,最大整数部分为0位
左边若是2,则2减1为1,最大整数部分为1位
左边若是3,则3减1为2,最大整数部分为2位
左边若是10,则10减1为9,最大整数部分为9位
注意小数点精度是看后一位
右边是2的情况下:(注意左边必须要>=右边,不然创建的时候会报错)
左边若是2,则2减2为0,最大整数部分为0位
左边若是3,则3减2为1,最大整数部分为1位
左边若是10,则10减2为8,最大整数部分为8位
推理:
右边是4的情况下:
左边若是10,则10减4为6,最大整数部分为6位
zerofill:零填充,补0
创建表不能用关键字zerofill
create table zf(id tinyint zerofill);用int的话默认有10位数,不足补0
insert into zf values (1);
insert into zf values (11);
insert into zf values (111);
select * from zf ;
+------+
| id |
+------+
| 001 |
| 011 |
| 111 |
+------+
字符串类型
char定长字符串 存储占的字节数4B char(10) 占用10个字节 括号中的长度范围0~255
varchar 变长字符串 存储占的字节数1B varchar(10) 按照存的字符串的长度分配 括号中的长度范围0~65535
例:存字符A使用varchar会占用2个字节的存储空间
为什么是2字节?
变长字符串需要1个字节存储字符串的长度’\c’这个是隐藏的,占一个字节
values char(4) storage varchar(4)storage
'' ' ' 4B '' 1B
'AB' 'AB' 4B 'AB' 3B
'ABCD' 'ABCD' 4B 'ABCD' 5B
'ABCDEF' 'ABCD' 4B 'ABCD' 5B
对于char型,存储时候长度不够,默认会在字符串后面补空格,但是查询时,空格会被脱掉。
定长字符串char和变长字符串varchar的区别:
根据情况使用可以节省空间
区别:定长字符串char存储占用的字节固定不变,始终为括号里面的值,而变长字符串varchar存储占用的字节会根据实际使用情况而发生改变,并且会额外占用一个字节。
enum:枚举类型 多个给定值中选择一个
例如sex:男和女只能选择一个
set:集合类型 多个给定值中选者一个或多个
start:
create table t5(sex enum(‘male’,’female’));
insert into t5 set sex=’hehe’;会报错,因为只能从male和female里面选择一个,enum是单选
create table t6(aaa set(‘A’,’B’,’C’));
insert into t5 set aaa=’A’;
insert into t5 set aaa=’A,B’;都可以,因为set是多选,重复选是重叠,选的里面又不存在的就不会写进去
日期时间类型 :
DATE:日期型 3字节
格式:'YYYY-MM-DD'
TIME:
格式:'HH:MM:SS'
DATETIME
格式:'YYYY-MM-DD HH:MM:SS'
TIMESTAMP
格式:'YYYY-MM-DD HH:MM:SS'
YEAR
格式:'YYYY'
MariaDB [test]> create table time_test(
-> d date,
-> t time,
-> dt datetime,
-> ts timestamp,
-> y year);
Query OK, 0 rows affected (0.04 sec)
MariaDB [test]> insert into time_test values(now(),now(),now(),now(),now());
MariaDB [test]> select * from time_test;
+------------+----------+---------------------+---------
| d | t | dt | ts | y |
+------------+----------+---------------------+----------
| 2017-06-01 | 10:17:07 | 2017-06-01 10:17:07 | 2017-06-01 10:17:07 | 2017 |
+------------+----------+---------------------+----------
约束:
约束一般用于字段上
约束有哪些?
非空、唯一、默认值、主键、外键、自增
语法:字段名 数据类型[宽度|notnull|unique|default 默认值|auto_increment]
create table t7(id intnot null); id不能为空
insert into t7 ;
create table t8(id int unique);id唯一不能重复
insert into t7 values(1);
insert into t7 values(1);不能重复
create table t11(sex char(10) default(‘male’));
insert into t11 values();插入空值也是male
创建一个自增的:
create table t12(id int auto_increment);报错,需要给一列并且那个列是主键,改为:
create table t12(id int unique auto_increment);唯一+自增
唯一+非空就是主键
唯一+自增也就是非空,可以给空值,因为会自增
主键:
create table t13(id int not null unique);或者
create table t14(id int primary key);
desc t14;或者
所有字段定义完后在定义主键
create table t15(id int,name char(10),sexchar(10),primary key(id));
若id和name定义为联合主键,id能不能重复?name能不能重复?
create table t16(id int,name char(10),sexchar(10),primary key(id,name));
insert into t16 values(1,’tom’,’male’);
select * from t16;
insert into t16 values(1,’jim’,’male’);
select * from t16;
发现id可以重复
insert into t16 values(2,’jim’,’male’);
select * from t16;
发现name可以重复
insert into t16 values(1,’jim’,’male’);
报错,因为:不能一起都同时重复
联合主键的所有列的数据不能完全一样
特征:一张表只能有一个主键,联合主键算是一个主键
作用:快速定位数据,唯一标识表中的每一条记录
创建的条件:非空且唯一
外键:FOR只能插入主键有的数据,不能插入没有的数据
表一class:
id(PRI) class
1011 yun
0726 java
0823 yun
0826 java
表二member:
class_id(FOR) name sex
1011
0726
不能插入0888
如果外键里面已经插入了数据1011,不能直接在主键表里面删除那个数据1011,需要先删除外键里面的1011,再删表一里面的1011才行
尝试一下:删除、更新(会不会连着一起更新呢?)(有一个操作可以连接起来)
我居然不会操作制作表一和表二并且赋予主键(创表的时候可以赋予)和外键?
create table class(id int primary key,classchar(4));
desc class;
insert into class values(1011,’yun’),(0726(此时显示的是726),’java’),(0823,’yun’),(0826,’java’);
select * from class;
create table member(class_id int,name char(20),sexenum(‘male’,’female’),foreign key(class_id) references依赖 class(id));
show create member;查看外键表的属性
select * from class;
insert into member values(1012,’jim’,’male’);报错member_ibfk_1外键不能添加主键里面不存在的数据,要改为:
insert into member values(1011,’jim’,’male’);
insert into member values(0826,’tom’,’male’);
若删主键表里面的0826,看能不能删?能不能改?
delete from class where id=0826;报错因为外键表里面有这个数据所有不能删不能改(delete from要一起写不能掉了from)
若更新主键表里面外键不存在的数据,看能不能改?能改
只有主键和外键那一列的数据受限制,其它列不受影响
级联删除和级联更新?连着一起更新?连接起来?
on delete cascade on update cascade既可以一起删除一起更新
drop table member;
create table member(class_id int,name char(20),sexenum(‘male’,’female’),foreign key(class_id) references依赖 class(id) on deletecascade on update cascade);
insert into member values(1011,’jim’,’male’);
insert into member values(0826,’tom’,’male’);
看看能不能将主键里面的0826改为0827?能!此时外键的表里面的0826也变成了0827,实现了级联更新。
那删外键里面的会不会影响到主键里面的呢?能删外键里面的但不能改外键里面的数据,不加级联那一段的话也是能删外键里面的但不能改外键里面的数据。
删除外键:
alter table member drop foreign key member_ibfk_1;
添加外键:
create table t1(id int,name char(10),primarykey(id));
create table t2(id int,name char(10),foreignkey(id) references t1(id) on delete cascade on update cascade);
删除外键:
alter table t2 drop foreign key t1_ibfk_1;
数据库里面的索引:
索引就是类似书的目录,字典中的音序表,它的功能就是纯粹加快查询速度。但是创建索引的时候要花很多时间。
对于数据频繁更新的表不适合创建索引。
添加索引:
第一种方法:在创建表时直接创建索引
create table index1(id int,name char(10),index(id));
第二种方法:如果创建表的时候忘了创建索引
alter table index1 add index(id);
删除
drop index id on index1;
alter table index1 drop index id;
查看
show index from index1;
show create table index1;
show profiles;可以查看运行所花费的时间
存储引擎:
常见的分为innodb支持事务和myisam不支持事务但访问速度快,其中innodb有两个文件innodb和.frm,myisam有3个文件.frm、.MYI、.MYD,都在db1里面ls可以看到
show engines\G查看支持的引擎
show create table t1;存储引擎在最下面可以看到
创建表的时候默认有存储引擎innodb
alter table test.t3 engine=myisam;修改存储引擎
show create table t1;存储引擎在最下面可以看到
事务:
set autocommit=0; 关闭自动提交功能
提交就是保存,保存就不能回滚rollback(后悔药、不可以返回去)
数据库权限:
看的权限:select
修改的权限:
可以登陆但没有任何权限:
create user ‘xiaoming’;
grant授权:
一般来说很少使用create创建用户,而大多情况使用grant来创建用户
grant all(指所有权限但不包括grant) on *.* 在哪个库上面.在哪个表上面
所有的库 所有的表 to tom@’192.168.1.63’ identified by ‘123456’(必须用引号)
flush privileges;别忘了使用grant创建用户之后要刷新一下数据库
切换到1.63
mysql -u tom -p123 -h 192.168.1.63
show databases;可以看到库(说明有看的权限)
drop database t2;可以删除(说明有删除的权限)
use mysql;show tables;可以查看所有的权限表
其中user表是全局权限
select * from user where user=’tom’\G
可以发现这里面的grant权限是N,说明只有root用户才能使用grant
还可以这样表示:jim@'192.168.1.%'
grant select权限名字 on *.* to jim@’%’;所有主机,且没有密码
flush privileges;
mysql -u jim -h 192.168.10.11
show databases;
use db1;
show tables;只能看
drop table t1;不能删
use mysql;select * from user where user=’tom’\G
可以发现这里面的select权限是Y,好,那如果全是N呢,是不是就没有任何权限了?不是的,只是没有全局的,还要看库级和表级和列级权限
show databases;
use db1;
grant all on db1.* to tom@’%’;给了一个具体的库名(库级权限)
flush privileges;
use mysql;select * from db where user=’tom’\G查看库级权限
grant all on db1.t1 to tom@’%’;只能看到t1表
flush privileges;
use mysql;select * from tables_priv where user=’tom’\G查看表级权限
grant select(id这是列名) on db1.class to lilei@’%’;
flush privileges;
show databases;
use db1;
show tables;
select * from class;报错,因为不能看全部的列,要改为:
select id from class;
use mysql;select * from columns_priv where user=’tom’\G查看列级权限
revoke 权限 on 库名.表名 from 用户@主机;
revoke all on *.* from root_test@’%’;回收全部权限
delete from mysql.user where user!=’root’;删除用户
创建一个拥有全部权限的lbx数据库用户,且能在所有主机上登陆,没有密码
grant all on *.* to lbx@'%';
注意使用grant之后就要刷新一下
flush privileges;
全局:
use mysql;select * from user where user='lbx';
库级:
grant all on db1.* to lbx@'%';
flush privileges;
use mysql;select * from ? where user='lbx';
?改为db,库级就是在db里面看
表级:
grant all on db1.t1 to lbx@'%';
flush privileges;
use mysql;select * from tables_? where user='lbx';
改为tables_priv表级和列级都有个_priv记住了
列级:
gran select(id) on db1.t1 to lbx@'%';
flush privileges;
use mysql;select * from col? where user='lbx';
改为columns_priv,阔路mns这样记住
有密码:
grant all on *.* to lbx@'%' previliges by '123';
previliges不存在,改为privileges,而且这个是刷新的参数
flush privileges;
有密码的是identified鉴定 by '123'