一,数据库体系结构
- mysql 存储引擎
1,mysql 默认存储引擎(innodb myisam) mysql服务软件自带的功能程序
2,列出可用的存储引擎类型
-show engines 或 show engines\G
3,查看已有表使用的存储引擎
- show create table 表名
4,修改数据库服务器默认使用的存储引擎
-# vim /etc/my.snf
-【mysqld】
- default-storage-engine=myisam
- service mysql restart 启服务
- myisam存储引擎的特点
1,主要特点
-支持标记锁
-不支持事务,事务回滚,外键
2,相关的表文件
-表名.frm
-表名.myi
-表名.myd
- innoDB存储引擎
3,主要特点
-支持行级锁定
-支持事务,事务回滚,支持外键
4,相关文件
-xxx.frm xxx.ibd
-ib_logfile0 ib_logfile1
- mysql 事务特性
1,Atomic:原子性
-事务的整个操作是一个整体,不可分割,要么全成功,要么全失败
2,Consistency:一致性
-事务操作的前后,表中的记录没有变化
3,Isolation:隔离性
-事务操作是相互隔离不受影响的
4,Durability:持久性
-数据一旦提交,不可改变,永久改变表数据
5,例
-show variables like "autocommit"; //查看提交状态
- set autocommit=off; //关闭自动提交
- rollback; //数据回滚,在事务执行过程中任何一步操作失败,都会恢复之前所以操作
-commit; //提交数据
数据库的导入和导出
- 数据库的导出
1,查看默认使用目录及目录是否存在
-show variables like "secure_file_priv";
2,设置目录及查看修改结果
-[root@localhost ~]# mkdir /myload ; chown mysql /myload
-[root@localhost ~]# vim /etc/my.cnf
-[mysqld]
secure_file_priv="/myload"
-[root@localhost ~]# systemctl restart mysqld
-mysql> show variables like "secure_file_priv";
3,基本用法
-select 查询.. .. into outfile "目录名/文件名" fields terminated by "分割符" lines terminated by "\n";
-注意事项 导出的内容由SQL查询语句决定,禁用SELINUX
例:导出userdb 库 user 表中 uid 小于 100的用户记录 导出mysql 库user表的前10条记录,值需包括user,host两个字段的信息
-mysql> select * from userdb.user where uid<100 into outfile "/
-myload/user2.txt";
-mysql> select user,host from mysql.user into outfile "/myload/
-user3.txt";
- 数据库的导入
1,基本用法
-load data infile "目录名/文件名" into table 表名 fields terminated by "分隔符" lines terminated by "\n";
-注意事项 字段分隔符要与文件内的一致,指定导入文件的绝对路径,导入数据的表字段类型要与文件字段匹配,禁用 SElinux
-例:
-mysql> create database userdb; //创建userdb库
-mysql> create table userdb.user(
-name char(50),password char(1),uid int(2),gid int(2),
-comment varchar(100),homedir char(60),shell char(50),
-index(name)); 表结构参考/etc/passwd 文件
-load data infile “myload/user.txt”into table userdb.user
-fields terminated by ":" lines terminated by "\n"; //导入数据
-mysql> alter table user add id int(2) zerofill primary key auto_increment first; 添加字段ID到前面
管理表记录
- 增加表记录
-语法格式1:给所有字段赋值
-insert into 表名 values(字段 1 值, .. .. ,字段 N 值), 第 1 条表记录
-( 字段 1 值, .. .. ,字段 N 值 ) , 第 2 条表记录
-( 字段 1 值, .. .. ,字段 N 值 ) , 第 3 条表记录
-语法格式2:给指定字段赋值
-insert into 表名(字段 1 值, .. .. ,字段 N 值)values( 字段 1 值, 字段 2 值 ,字段 N 值 ) ,第 1 条表记录
-( 字段 1 值, 字段 2 值,字段 N 值 ) , 第 2 条表记录
-( 字段 1 值, 字段 2 值 ,字段 N 值 ) , 第 3 条表记录
-例alter table 表名 add 字段1 char(11) after 字段2;//添加字段1到2后面
-alter table 源表名 rename 新表名; //修改表名
-alter table change 源字段名 新字段名 类型(宽度) [ 约束条件] //修改字段名
-modify 字段名 类型(宽度) [ 约束条件] 修改已有字段的类型宽度及约束条件,修改时不能与已经存储的数据矛盾的话不允许修改
-注意事项 字段值要与字段类型相匹配,对于字符类型的字段,要用双或单引号括起来 依次给所有字段赋值时,字段名可以省略 只给一部分字段赋值时,必须明确写出对应的字段名称
- 查询表记录
-格式2:SELECT 字段 1, .. .., 字段 N FROM 表名 where 条件表达式;
-select *from user where name="root"; 按条件查询
-select id,name from user where id>=10; 查询ID大于等于10的用户
-select name,uid,gid from user where uid=gid; 查询uid=gid的名字
-注意事项 使用* 可匹配所有字段 指定表名时,可采用 库名 表名 的性式
- 更新表记录
-格式1:更新表内的所有记录
update 表名 set 字段 1= 字段 1 值 ,字段 2= 字段 2 值 ,字段 N= 字段 N 值 ;
-格式2:只更新符合条件的部分记录
update 表名 set 字段 1= 字段 1 值 ,字段 2= 字段 2 值 ,字段 N= 字段 N值,where 条件表达式;
update 表名 set password="A"; 批量修改密码
update 表名 set password="A" where name="root"; 加条件修改
- 删除表记录
-格式1:仅删除符合条件的记录
delete from 表名 where 条件表达式;
-格式2:删除所有的表记录
delete from 表名;
- 在已有表复制表结构
-create table 新表 select *from 源表 where 1<>1; //复制表结构
-create table 新表 select * from 源表; //复制表
基本查询条件
- 数值比较
类型 | 用途 |
= | 等于 |
> >= | 大于,大于或等于 |
< <= | 小于,小于或等于 |
!= | 不等于 |
- 逻辑比较
-多个判断条件使用
类型 | 用途 |
OR | 逻辑成 |
AND | 逻辑与 |
! | 逻辑非 |
() | 提高优先级 |
- 范围内匹配/去重显示
-匹配范围内的任意一个值即可
类型 | 用途 |
in(值列表) | 在..里.. |
NOT in(值列表) | 不在..里.. |
between 数字1 and 数字2 | 在..之间.. |
distinct字段名 | 去重显示 |
聚集函数
-avg( 字段名 ) : 求平均值
-sum( 字段名 ) :求和
-min( 字段名 ) : 统计最小值
-max( 字段名 ) :统计最大值
-count( 字段名 ) :统计个数
查询结果排序
-语法格式 order by 字段名 【asc|desc】
查询结果分组
-语法格式 group by 字段名
查询结果过滤
-SQL 查询 HAVING 条件表达式;
-SQL 查询 where 条件 HAVING 条件表达式
-SQL 查询 group by 字段名式 HAVING 条件表达;
限制查询结果显示行数
-SQL 查询 LIMIT N; 显示查询结果前 N 条记录
-SQL 查询 LIMIT N,M ;显示指定范围内的查询记录
-SQL 查询 where 条件查询 LIMIT N ;显示查询结果前 N 条记录
-SQL 查询 where 条件查询 LIMIT N , M ;显示指定范围内的查询记录
查询方式总结
查询方式 | 关键字 |
查询条件 | where |
分组 / 排序 / 限制条目数 / 查询结果过滤 | order by/group by/limit/having |
单表查询 | select .. .. from .. .. where .. .. |
嵌套查询 | select .. .. from .. .. Where .. .. (select .. .. ) |
多表查询 | select .. .. from 表 1, 表 n where .. .. |
左连接查询 | left .. .. Join .. .. on |
右连接查询 | right .. .. join .. .. on |