一，数据导入 数据导出
- 导入导出数据的默认存储文件路径是/var/lib/mysql-files
show variables like "secure_file_priv"; （查看路径）
自定义数据导出，导入文件存储的目录
oot@host50 ~]# mkdir /mydata （自定义的目录名）
[root@host50 ~]# chown mysql /mydata/ （给自定义目录名给执行权限）
[root@host50 ~]# vim /etc/my.cnf [mysqld]下面写入创建目录 secure_file_priv=自定义目录
[root@host50 ~]# systemctl restart mysqld
数据导入：把系统文件的内容存储到数据库服务器的表里。
有格式规律
导入数据的步骤：
1。把系统文件拷贝指定的目录下
mysql> system cp /etc/passwd /mydata/ （ 加system可以在数据库执行命令）
mysql> system ls /mydata
2.创建存储文件内容的表
mysql> create table db3.user(name char(50), （创建存储文件的表）
-> password char(1), 密码
-> uid int(2), 用户的id
-> gid int(2), 组的id
-> comment varchar(150), 备忘录
-> homedir char(150), 加目录
-> shell char(50), 语言
-> index(name)); 索引为name
mysql> desc user;
导入数据：1，字段分隔符要与文件内的一致2，指定导入文件的绝对路径3，导入数据的表字段类型要与文件字段匹配4，禁用Selinex保护机制 (导入分隔符要一样，导出可以自定义）
3.导入数据(格式：load data infile "目录名/文件名" into table 表名 fields terminated by "分隔符 " lines terminatedby"\n";
mysql> load data infile "/mydata/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
mysql> select * from user; （把/mysdata/passwd文件的内容导入db3库中的user表）
mysql> alter table user add id int(2) primary key auto_increment first; ( 加行号自动增长，主键不重复不为空）
mysql> select * from user;
mysql> select * from user where id=15; (寻找user表id为15的用户)
数据导出：1.导出的内容由SQL的语句决定2.导出的是表中的记录，不包括字段名 3.禁用Slinux
数据导出：把数据库服务器的表里的记录存储到系统文件里。
格式：sql查询命令 into outfile "目录名/文件名" fields terminated by "分隔符" lines terminated by "\n"
mysql> select * from t1 into outfile "/mydata/t1.txt"; （把t1表的记录导出/mydata/t1.txt中）
mysql> select * from t1 into outfile "/mydata/t2.txst" fields terminated by "#";（把t1表的数据导出到/mydata/t2.txst中）
mysql> select * from t1 into outfile "/mydata/t3.txt" fields terminated by "#" lines terminated by "!!!"; （把t1表的数据导出到/mydata/t3.txt中）
二：管理表记录
管理表记录
添加表记录 格式： insert into 表名 values(字段值列表) (给所有的字段赋值)
格式2：insert into 表名 values（字段值列表),.....添加n条
mysql> insert into user
-> values
-> (42,"bob","x",2000,2000,"student user","/home/bob","/bin/bash"); （给表结构所有字段赋值）
mysql> insert into user values(43,"bob","x",2000,2000,"student user","/home/bob","/bin/bash");
格式3： insert into 表名（字段名列表）values(字段值列表）; (给指定的字段赋值）
格式4：insert into (字段名列表)values(字段值列表)，...添加n条
mysql> insert into user (name,shell,uid)
-> values
->("lucy","/sbin/nologin",1998); （给指定的字段赋值）
mysql> insert into user (name,shell,uid) values ("tom","/sbin/nologin",1928); （给指定字段赋值）
mysql> insert into user (name,shell,uid) values ("alice","/sbin/nologin",1948);
语法注意格式：字段值要与字段型相匹配
2.对于字段类型的字段，要用双引或单引括起来
3.依次给所有的字段赋值时，字段名可以省略
4.只给一部分赋值时，必须明确写出相应的名称
查询记录 select
格式：select 字段1, ..字段n from 表名;
格式2:select 字段1，..,字段n from 表名; where 条件表达式;
select * from user;
mysql> select name,uid,shell from user where shell="/bin/bash"; （查询。name,uid,shell,条件user表的shell是/bin/bash)
mysql> select name,uid,shell from user; (user表指定查询的条件)
更新记录字段的值 update
格式：update 表 set 字段名=值，字段名n=值n，...where 条件； (只更新符合条件的部分记录)注意：不使用where会更新所有记录。
格式:update 表 set 字段名=值，字段名n=值n (更新表内的所有内容)
update user set password="a",gid=1000; (更新user表所有的密码或组id）
update user set password="x" where id=1; (只更新符合条件的部分记录)
select password ,gid from user; (查询密码组id）
删除记录 delete
格式：delete from 表 where 条件; （仅删除符合条件的记录)
格式：delete from 表 (删除所有记录)
delete from t1;
select * from t1;
select * from user where name="bob"; （查询user表的name字段为bob的）
delete from user where name="bob"; （删除user表的name的字段为bob的）
三：匹配条件的表达式：
匹配条件的表示方式(select update delete
格式：where 字段名 符号 数值 数值比较：类型：>(大于）<(小于)=(等于)!=（不等于mysql> select * from user where id <=10; （查询user表id小于等于10的） >=(大于或不等于）<=(小于或等于)
mysql> select name,uid from user where uid=9; （查询user表字段name,uid,条件uid等于9的）
mysql> select name,uid,gid from user where uid=gid; （查询user表字段name,uid,gid,条件是uid等于gid)
字符比较：where 字段名 符号 "值" 字符比较 非空 匹配空
mysql> select name,shell from user where shell != "/bin/bash"; =(相等） !=（不相等) is null(匹配空)is null not(非空)
匹配空 :is null is not null
insert into user(id,name)values(51,""),(52,"null"),(53,"null"); (在user表插入表记录给字段id,name,赋值）
select id,name from user where id>=51; （查询user表的字段id,name 条件id大于等于51；）
mysql> select id,name from user where name is null; （查询user表的字段，id,name,条件name匹配为空）
mysql> select id,name from user where name="null" （查询user表的字段，id,name,条件name等于null）
mysql> select id,name from user where name=""; （查询user表字段id,name, 条件name等于字符空)
mysql> select name,shell from user where shell is not null; (查询user表字段，name,shell,条件shell 不为空)
mysql> select id,name,shell from user where shell is null; (查询user表字段，id,name,条件shell为空）
mysql> update user set password=null where name="bin"; （更新user表的密码等于null,条件name等于bin)
mysql> select * from user where name="bin"; （查询user表所有记录，条件name等于bin)
逻辑比较：and(与) or(或) ! not(非) ()(提高优先级）
mysql> select * from user where name="root" and uid=1 and shell="/bin/bash"; (查询user表，条件name等于root，与uid等于1，与shell等于/bin/bash;)
mysql> select name,uid from user where name="root" or uid=1; (查询user表字段,name，uid,条件name等于root，或者uid等于1）
mysql> select name,uid from user where name="root" or name="bin" and uid=1; （查询user表字段，name,uid 条件name等于root，或者name等于bin与uid等于1）
mysql> select name,uid from user where (name="root" or name="bin") and uid=1; (提高优先级）（查询user表字段，name,uid,条件（name等于root 或者name等于bin）与uid等于1（加括号优先级匹配）
范围内匹配：
mysql> select name from user where name in("root","sync","lucy","bob"); （查询user表字段name,条件name范围为root,sync,lucy,bob)
mysql> select name,uid from user where uid in (1,7,27,1000); (查询user表字段name，uid,条件uid范围为1,7,27,1000)
mysql> select name,shell from user where shell not in ("/bin/bash","/sbin/nologin"); (查询user表的字段name,shell,条件shell不为空，匹配范围为/bin/bash /sbin/nologin)
mysql> select * from user where uid between 10 and 20; (查询user表字段，条件uid在10与20之间） between(在什么之间）
mysql> select * from user where uid>=10 and uid<=20; （查询 user表 条件uid大于等于10与uid小于等于20）
distinct (只适合select查询使用) distinct(不同的）
mysql> select shell from user;
mysql> select distinct shell from user; （去掉user表字段shell重名的，显示不同的）
mysql> select shell from user where uid <=500; (查询user表字段，shell,条件uid小于等于500）
mysql> select distinct shell from user where uid <=500; （查询user表字段shell，去掉重名的，显示不同的，条件uid小于等于500
四：高级匹配条件
模糊查询
-匹配任意一个字符
%匹配零个或多个字符
格式：where 字段名 like '表达式';
select name from user where name like '_'; （查找user表字段name，条件name匹配任意一个相同字符）
mysql> select name from t2 where name like '%a%'; ( 查找t2表字段name，条件name匹配中间字符以a相同的任意一个或者几个字符）
mysql> select name from t2 where name like '%-%'; （查找t2表字段name,条件中间带-的任意字符）
mysql> select name from user where name like '%_%'; (查找user表字段name，条件至少两个字符以上）
mysql> select name from t2 where name like '%'; (查找t2表字段，name，条件任意一个或者多个字符）
五：正则表达式：
where 字段名 regexp '正则表达式' 正则元字符：^ $ . [] * |
select name from user where name regexp '^a.*t$'; (查找user表字段name,条件，以a 开头的t结尾的所有任意字符)
insert into user(name)values("ya9ya"),("7yaya"),("yaya"); (插入数据到user表,给字段name分别赋值为ya9ya,7yaya,yaya)
select name from user where name regexp '[0-9]'; (查找user表字段name,条件，包含0-9的任意数字）
select name from t1 where name regexp '^[0-9]'; (查找t1 表字段name,条件以0-9开头的任意数字）
select name from t1 where name regexp '[0-9]$'; (查找t1表字段name,条件以0-9结尾的任意数字)
select name,uid from t1 where uid regexp '..'; （查找t1表字段name，uid条件uid为任意2位数以上的字符）
select name,uid from t1 where uid regexp '^..$'; (查找表字段name,uid条件uid任意两位数）
六：四则计算：+ - * / %(字段类型 必须是数值类型) 四则运算数则类型：+ - * （乘法) / （除法） % （取余数）
update user set uid=uid+1 where id<=10; (修改user表字段，uid自动加1 条件id前10行小于等于10； ）
select name,uid,gid ,uid+gid jiehuo from user where name="root"; (查找user表字段name,uid,gid,uid加gid 等于结果 条件name等于root
mysql> select name,uid,gid,(uid + gid) / 2 pjjz from t1 where name="root";
- mysql> alter table t1 add age tinyint(2) unsigned default 18 after name; (添加年龄字段age到user表结构name后面，赋值为所有用户年龄为18）
mysql> select name,age from t1 where name ="root"; (查找t1表字段name,age，条件name 为root)
ysql> select name,age,2018-age s_year from t1 where name="root"; (查找t1表字段，name,age,条件为root，当前年份减去root年龄等于root真实年龄）
七：聚集函数(MYSQL服务自带的对数据做统计的命令) MYSQL 内置数据统计函数 ：
avg(字段名）统计字段平均值 sum(字段名)统计字段之和) min(字段名）统计字段最小值) max(字段名)统计字段最大值 count（字段名)统计字段值个数
mysql> select sum(uid) from t1;
mysql> select sum(uid) from t1 where id<=10;
mysql> select avg(uid) from t1 where id<=10;
mysql> select min(uid) from t1 where id<=10;
mysql> select max(uid) from t1 where id<=10;
mysql> select name from t1 where shell="/bin/bash";
mysql> select sum(uid),avg(uid),max(uid),min(gid) from t1;
mysql> select name from t1 where shell="/bin/bash";
mysql> select count(name) from t1 where shell="/bin/bash";
mysql> select count(id),count(name) from t1;
八：操作查询结果 select .... from user where .... ;
格式：
select 查询 from group by 字段名(通常是字符型字段)；查询分组
mysql> select shell from t1;
mysql> select shell from t1 group by shell;
mysql> select distinct shell from t1;
select 查询 from order by 字段名(通常是数值类型字段) 排序方式; 排序【asc | desc】
mysql> select name,uid from t1 where uid>=10 and uid<=500;
mysql> select name,uid from t1 where uid>=10 and uid<=500 order by uid desc;
limit 限制查询结果显示的行数
格式：
sql查询 limit n;显示当前结果前n条记录
sql查询 limit n,m;显示指定范围内的查询记录
sql查询 limit where 条件查询 limit 3; 显示查询结果前3条记录
sal查询 where 条件查询 limit 3,3;从第4条开始，共显示3条
select 查询 from limit 数字1, 显示查询结果的前几行
select 查询 from limit 数字1, 数字2; 显示指定范围内的行; ( 0表示第1行）
mysql> select name,shell from t1;
mysql> select name,shell from t1 limit 5;
mysql> select name,shell from t1 where uid <=500;
mysql> select name,shell from t1 where uid <=500 limit 2;
mysql> select name,shell from t1 where uid <=500 limit 1,5;
mysql> select * from t1;
mysql> select * from t1 limit 4,3;
having 条件 在查询结果里查找数据
格式：
select 查询 from having 条件表达式;
sql查询 where 条件 having 条件表达式;
sql查询 group by 字段名 having 条件表达式;
select name from t1 where in<=10 having 条件;
ysql> select name,uid from t1 where uid >=1000 having name is null;
mysql> select name,uid from t1 where uid >=1000 having uid=65534;
mysql> select name,uid from t1 where uid >=1000 having name="bob";
九：mysql 存储引擎
什么是存储引擎？ 是mysql数据库服务自带功能程序。处理表的处理器。
每种存储引擎有不同的功能和数据存储方式。
查看当前数据库服务，支持的存储引擎？
mysql> show engines;
修改数据库服务默认使用的存储引擎？
root@host50 ~]# systemctl stop mysqld
root@host50 ~]# vim /etc/my.cnf (加入default-storage-engine=myisam)
ysql> show engines;
建表是指定表使用的存储引擎
ysql> create database db5;
mysql> create table t1(id int);
create table t2(id int); engine=存储引擎名;
create table t2 (id int) engine=memory;
create table t3 (id int) engine=innodb;
修改表使用的存储引擎
alter table 表名 engine=存储引擎名;
查看表使用的存储引擎
show create table 表名;
常用存储引擎的特点？(myisam innodb)
myisam存储引擎特点
lnnodb的特点：支持行级锁，支持外键，事务和事务回滚（事务transactions) 支持表级锁
每个表对应2个表文件
表frm 表结构数据
表.ibd 表数据和index索引
myisam存储引擎特点 不支持事务和事务回滚 外键
每个表对应3个表文件
表.frm 表.MYD 表.MYI
事务：（Transactions):一次sql操作从建立连接到操作完成断开连接的访问过程给称作事务。
支持事务的可以做事务回滚：一次sql操作有任意一步没有执行成功会恢复所有操作。 （对innodb存储引擎的表 访问时 必须任意一步操作都成功，才能完成操作，）
innodb存储引擎的表有对应的事务文件记录所有sql命令
cd /var/lib/mysql
ibdata1 ib_logfile0 ib_logfile1
事务的特点：ACID
mysql> set autocommit=off;
mysql> show variables like "autocommit";
commit：手动提交
rollback：回滚操作
tt1
create table t5(name char(10)) engine=innodb;(支持回滚手动提交)
select * from t5;
tt2
select * from t5;(看不到)
tt1
insert into t5 values("bob");
select * from t5;
tt2
select * from t5;(看不到)
tty1
commit;(提交)
select * from t5;
tt2
select * from t5;(能看到)
tt1
delete from t5;
select * from t5;
tt2
select * from t5;(能看到因为没有提交）
tt1
rollback;（回滚，没有提交都可以悔,提交了就正确执行)
例子：set autocommit=off;
show variables like "autommit";
commit;
rollback;
tty1
create table db5.t5(name char(10));
insert into t5 values("bob"),("bob");
commit;
tty2
select * from db5.t5;
tty1
delete from db5.t5;
select * from db5.t5;
tty2
select * from db5.t5;
tty1
select * from db5.t5;
rollback;
工作中建表时，如何决定表使用的存储引擎？
锁类型：
读锁：当对一张表执行查询（select）操作时 会加读锁
写锁：(排他锁或互斥锁）当对一张表执行写(insert update delete)操作时会加锁
执行写操作多的表适合使用innodb存储引擎,这样并发访问量大。
执行查询操作多的表适合使用myisam存储引擎，节省系统资源。
select * from t1 where id <=10;(前10行）
innodb 表锁
update t1 set name="bob" where id=3;
update t1 set name="bob" where id=2;
每个锁对应2个表文件
表.frm 表结构数据
表.ibd 表数据和index索引
修改
数据库管理
NSD DBA 基础
DAY03内容
09:00 ~ 09:30
上午
下午
09:30 ~ 10:20
10:30 ~ 11:20
作业讲解和回顾
MySQL 存储引擎
11:30 ~ 12:00 数据导入导出
14:00 ~ 14:50 管理表记录
15:00 ~ 15:50
16:10 ~ 17:00
17:10 ~ 18:00
匹配条件
总结和答疑MySQL 存储引擎
MySQL 工作原理
MySQL 体系结构
MySQL 存储引擎
MySQL 存储引擎
存储引擎的配置
修改表的存储引擎
设置默认存储引擎
存储引擎特点
MyISAM 存储引擎
InnoDB 存储引擎
MySQL 锁机制
事务特性 (ACID)MySQL 工作原理MySQL 体系结构
知
识
讲
解
各种存储
引擎组件
SQL 接口 / 解析器 / 优化器 / 缓存MySQL 存储引擎
• 作为可插拔式的组件提供
知
识
讲
解
– MySQL 服务软件自带的功能程序,处理表的处理器
– 不同的存储引擎有不同的功能和数据存储方式
• 默认的存储引擎
– MySQL 5.0/5.1 ---> MyISAM
– MySQL 5.5/5.6 ---> InnoDBMySQL 存储引擎 ( 续 1)
• 列出可用的存储引擎类型
– SHOW ENGINES; 或 SHOW ENGINES\G
知
识
讲
解存储引擎的配置修改表的存储引擎
• 建表时手动指定
知
识
讲
解
– 未指定时,使用默认存储引擎
– SHOW CREATE TABLE xxx\G ;可确认设置默认存储引擎
• 修改 /etc/my.cnf 配置文件
– default-storage-engine=xxxx
知
识
讲
解
[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
default-storage-engine=InnoDB
[root@dbsvr1 ~]# service mysql restart
Shutng down MySQL....
[ 确定 ]
Startng MySQL........
[ 确定 ]存储引擎特点Myisam 存储引擎
• 主要特点
知
识
讲
解
– 支持表级锁
– 不支持事务、事务回滚、外键
• 相关的表文件
– 表名 .frm 、
– 表名 .MYI
– 表名 .MYDInnoDB 存储引擎
• 主要特点
知
识
讲
解
– 支持行级锁定
– 支持事务、事务回滚、支持外键
• 相关的表文件
– xxx.frm 、 xxx.ibd
– ibdata1
– ib_logfile0
– ib_logfile1MySQL 锁机制
• 锁粒度
知
识
讲
解
– 表级锁:一次直接对整张表进行加锁。
– 行级锁:只锁定某一行。
– 页级锁:对整个页面( MySQL 管理数据的基本存储单
位)进行加锁。
• 锁类型
– 读锁(共享锁):支持并发读。
– 写锁(互斥锁、排它锁):是独占锁,上锁期间其他
线程不能读表或写表。MySQL 锁机制(续 1 )
• 查看当前的锁状态
– 检查 Table_lock 开头的变量, % 作通配符
知
识
讲
解事务特性 (ACID)
• Atomic :原子性
知
识
讲
解
– 事务的整个操作是一个整体,不可分割,要么全部成
功,要么全部失败。
• Consistency : 一致性
– 事务操作的前后,表中的记录没有变化。
• Isolation :隔离性
– 事务操作是相互隔离不受影响的。
• Durability :持久性
– 数据一旦提交,不可改变,永久改变表数据事务特性 (ACID) (续 1 )
• 示例
知
识
讲
解
mysql> show variables like "autocommit"; // 查看提交状态
mysql> set autocommit=off; // 关闭自动提交
mysql> rollback ; // 数据回滚
mysql> commit; // 提交数据案例 1 : MySQL 存储引擎的配置
1. 可用的存储引擎类型
2. 查看默认存储类型
课
堂
练
习
3. 更改表的存储引擎数据导入导出
数据导入导出
数据导入导出
设置搜索路径
数据导入
数据导出数据导入导出设置搜索路径
• 查看默认使用目录及目录是否存在
知
识
讲
解
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value
|
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
[root@localhost ~]# ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 31 4 月 19 14:15 /var/lib/mysql-files/设置搜索路径(续 1 )
• 修改目录及查看修改结果
知
识
讲
解
[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";
+------------------+----------+
| Variable_name | Value
|
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+数据导入
• 基本用法
– LOAD DATA INFILE “ 目录名 / 文件名”
INTO TABLE 表名
知
识
讲
解
FIELDS TERMINATED BY “ 分隔符”
LINES TERMINATED BY “\n”;
• 注意事项
– 字段分隔符要与文件内的一致
– 指定导入文件的绝对路径
– 导入数据的表字段类型要与文件字段匹配
– 禁用 SElinux数据导入(续 1 )
• 案例需求
– 将本地用户的记录导入 userdb 库的 user 表里
– 为每条用户记录添加记录编号
知
识
讲
解
mysql> create database 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 文件数据导入(续 2 )
mysql> load data infile "/myload/user.txt" into table userdb.user fields
terminated by ":" lines terminated by "\n";
知
识
讲
解
mysql> alter table userdb.user add id int(2) zerofill primary key
auto_increment first;
mysql> select id ,name,uid from userdb.user limit 3;
+----+-----------+------+
| id | name
| uid |
+----+-----------+------+
| 01 | root
| 0 |
| 02 | bin
| 1 |
| 03 | daemon | 2 |
+----+-----------+------+
3 rows in set (0.00 sec)数据导出
• 基本用法
知
识
讲
解
– SELECT 查询 .. ..
INTO OUTFILE “ 目录名 / 文件名”
FIELDS TERMINATED BY “ 分隔符”
LINES TERMINATED BY “\n”;
• 注意事项
– 导出的内容由 SQL 查询语句决定
– 禁用 SElinux数据导出(续 1 )
• 案例需求
知
识
讲
解
– 导出 userdb 库 user 表中 uid 小于 100 的用户记录
– 导出 mysql 库 user 表的前 10 条记录,只需包括 user
、 host 两个字段的信息
mysql> select * from userdb.user where uid<100 into outfile "/
myload/user2.txt";
Query OK, 18 rows affected (0.00 sec)
mysql> select user,host from mysql.user into outfile "/myload/
user3.txt";
Query OK, 3 rows affected (0.00 sec)案例 2 :数据导入 / 导出
使用 SQL 语句完成下列导出、导入操作:
课
堂
练
习
1 )将 /etc/passwd 文件导入 userdb 库 user 表并给每
条记录加编号
2 )将 userdb 库 user 表中 uid 小于 100 的前 10 条记
录导出,存为 /myload/user2.txt 文件管理表记录
增加表记录
管理表记录
语法格式
查询表记录 语法格式
更新表记录 语法格式
删除表记录 语法格式增加表记录语法格式
• 格式 1 :给所有字段赋值
知
识
讲
解
– INSERT INTO 表名
VALUES
( 字段 1 值, .. .. ,字段 N 值 ) ,
( 字段 1 值, .. .. ,字段 N 值 ) ,
( 字段 1 值, .. .. ,字段 N 值 ) ,
.. .. ;
第 1 条表记录
第 2 条表记录
第 3 条表记录语法格式(续 1 )
• 格式 2 ,给指定字段赋值
知
识
讲
解
– INSERT INTO 表名 ( 字段 1,.. .., 字段 N)
VALUES
( 字段 1 值,字段 2 值,字段 N 值 ) ,
( 字段 1 值,字段 2 值,字段 N 值 ) ,
( 字段 1 值,字段 2 值,字段 N 值 ) ,
.. .. ;
第 1 条表记录
第 2 条表记录
第 3 条表记录语法格式(续 2 )
• 注意事项
知
识
讲
解
– 字段值要与字段类型相匹配
– 对于字符类型的字段,要用双或单引号括起来
– 依次给所有字段赋值时,字段名可以省略
– 只给一部分字段赋值时,必须明确写出对应的字段名称查询表记录语法格式
• 格式 1
– SELECT 字段 1, .. .., 字段 N FROM 表名 ;
知
识
讲
解
• 格式 2
– SELECT 字段 1, .. .., 字段 N FROM 表名
WHERE 条件表达式 ;
• 注意事项
– 使用 * 可匹配所有字段
– 指定表名时,可采用 库名 . 表名 的形式更新表记录语法格式
• 格式 1 ,更新表内的所有记录
知
识
讲
解
– UPDATE 表名
SET
字段 1= 字段 1 值 ,
字段 2= 字段 2 值 ,
字段 N= 字段 N 值 ;语法格式(续 1 )
• 格式 2 ,只更新符合条件的部分记录
知
识
讲
解
– UPDATE 表名
SET
字段 1= 字段 1 值 ,
字段 2= 字段 2 值 ,
字段 N= 字段 N 值 ;
WHERE 条件表达式 ;语法格式(续 2 )
• 注意事项
知
识
讲
解
– 字段值要与字段类型相匹配
– 对于字符类型的字段,要用双或单引号括起来
– 若不使用 WHERE 限定条件,会更新所有记录
– 限定条件时,只更新匹配条件的记录删除表记录语法格式
• 格式 1 ,仅删除符合条件的记录
– DELETE FROM 表名 WHERE 条件表达式 ;
知
识
讲
解
• 格式 2, 删除所有的表记录
– DELETE FROM 表名 ;案例 3 :操作表记录
• 练习表记录的操作
课
堂
练
习
– 表记录的插入
– 表记录的更新
– 表记录的查询
– 表记录的删除匹配条件
基本查询条件
数值比较
字符比较 / 匹配空 / 非空
逻辑匹配
范围匹配
高级查询条件
匹配条件
模糊查询
正则表达式
聚集函数
四则运算
去重查询
操作查询结果
查询结果排序
查询结果分组
查询结果过滤
限制显示行数基本查询条件数值比较
• 字段类型必须数据数值类型
类 型
知
识
讲
解
mysql> alter table t1 add age tinyint(2) unsigned default 18 after name;
用 途
= 等于
> 、 >= 大于、大于或等于
< 、 <= 小于、小于或等于
!= 不等于字符比较 / 匹配空 / 非空
• 字段类型必须
类 型
知
识
讲
解
用 途
= 相等
!= 不相等
IS NULL 匹配空
IS NOT NULL 非空逻辑比较
• 多个判断条件时使用
类 型
知
识
讲
解
用 途
OR 逻辑或
AND 逻辑与
! 逻辑非
( ) 提高优先级范围内匹配 / 去重显示
• 匹配范围内的任意一个值即可
知
识
讲
解
类 型
用 途
In ( 值列表 ) 在...里...
Not in ( 值列表 ) 不在...里...
Between 数字 1 and 数字
2 在...之间...
DISTINCT 字段名 去重显示高级查询条件模糊匹配
• 基本用法
知
识
讲
解
– WHERE 字段名 LIKE ' 通配字串 '
– 通配符 _ 匹配单个字符
– % 匹配 0~N 个字符
• 示例
– 列出 name 值“以 J 开头或以 Y 结尾”的记录正则匹配
• 基本用法
知
识
讲
解
– WHERE 字段名 REGEXP ' 正则表达式‘
– ^ $ . [ ] *
• 示例
– 列出 name 值“以 J 开头或以 Y 结尾”的记录四则运算
• 运算操作mysql> select name from t2 where name like '%a%';
– 字段必须是数值类型
知mysql> create table t1(id int);
识
讲
解
类 型
用 途
+ 加法
- 减法
* 乘法
/ 除法
% 取余数(求模)聚集函数
• MySQL 内置数据统计函数
知
识
讲
解
– avg( 字段名 ) : 求平均值mysql> select name from t2 where name like '%a%';
– sum( 字段名 ) :求和
– min( 字段名 ) : 统计最小值
– max( 字段名 ) :统计最大值mysql> create table t1(id int);
– count( 字段名 ) :统计个数操作查询结果查询结果排序
• 基本用法
知
识
讲
解
– SQL 查询
ORDER BY 字段名
[ asc | desc ]
通常是数值类型字段查询结果分组
• 基本用法mysql> select min(uid) from t1 where id<=10
知
识
讲mysql> create table t1(id int);
解
– SQL 查询
group by 字段名
通常是字符类型字段查询结果过滤
• 基本用法
知
识
讲
解
– SQL 查询 HAVING 条件表达式;mysql> select name,uid,gid,(uid + gid) / 2 pjjz from t1 where name="root";
– SQL 查询 where 条件 HAVING 条件表达式;
– SQL 查询 group by 字段名
式;
HAVING 条件表达限制查询结果显示行数mysql> select name,uid,gid,(uid + gid) / 2 pjjz from t1 where name="root";
• 基本用法
知
识
讲
解
– SQL 查询 LIMIT N; 显示查询结果前 N 条记录
– SQL 查询 LIMIT N,M ;显示指定范围内的查询记录
– SQL 查询 where 条件查询 LIMIT N ;显示查询
结果前 N 条记录
– SQL 查询 where 条件查询 LIMIT N , M ;显
示指定范围内的查询记录案例 4 :查询及匹配条件
• 练习常见的 SQL 查询及条件设置
课
堂
练
习
– 创建 stu_info 表,并插入数据
– 练习常见 SQL 查询及条件设置总结和答疑
总结和答疑
查询表记录 查询方式总结
数据导入 问题现象
故障分析及排除mysql> create table t1(id int);
数据导出
问题现象
故障分析及排除查询表记录查询方式总结
知
识
讲
解
查询方式 关键字
查询条件 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数据导入问题现象
• 数据导入失败
知
识
讲
解
– 报错 1 : Errcode: 13 - Permission denied: .. ..
– 报错 2 : Data too long for column .....
mysql> load data infile "/tmp/a.txt" into table user fields
terminated by ":" lines terminated by "\n";
ERROR 29 (HY000): File '/tmp/a.txt' not found (Errcode: 13 -
Permission denied)
mysql> load data infile "/tmp/a.txt" into table user fields
terminated by ":" lines terminated by "\n";
ERROR 1406 (22001): Data too long for column 'comment' at row
1故障分析及排除
• 问题 1 :
知
识
讲
解
– SELinux 策略阻止访问文件
mysql> alter table t1 add age tinyint(2) unsigned default 18 after name;
– 可执行 setenforce 0 禁用 SELinux
• 问题 2 :
– 导入数据与字段类型不匹配,需要修改字段类型:
alter table 表 modify 字段名 类型 .. ..数据导出问题现象
• 导出数据保存到自定义目录失败
– 报错: (Errcode: 13 - Permission denied) .. ..
知
识
讲
解
mysql> select user,host from mysql.user into outfile
"/datadir/b.txt";
ERROR 1 (HY000): Can't create/write to file '/datadir/b.txt' (Errcode:
13 - Permission denied)故障分析及排除
• 原因分析
– 对目录没有 w 权限
知
识
讲
解
• 解决办法
– 让 mysql 用户对目录有 w 权限
[root@dbsvr1 ~]# chown mysql /datadir/
[root@dbsvr1 ~]# ls -ld /datadir
drwxr-xr-x. 2 mysql root 4096 5 月 20 23:09 /datadir2) | YES | | NULL | |
18万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
