mysql数据库入门 3
++++++++++++DAB1_day03
一、数据导入导出
修改搜索路径
]# mkdir /myload
]# chown mysql /myload
]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload“
:wq
]# systemctl restart mysqld
mysql> show variables like “secure_file_priv”; 查看
数据导入
作用:把系统文件的内容存储到数据的数据库
数据导入步骤
-默认只有root用户有数据导入权限
-建表
-导入数据
load data infile “目录/文件名”
into table 库名.表名
field terminaled by “分隔符”
lines terminaled by “\n”;
命令格式:
mysql> sql查询命令 into outfile “目录/文件名”
[ fields terminated by “分隔符号” lines terminated by “\n”];
mysql> select * from db3.user where id <= 5
into outfile “/var/lib/mysql-files/a.txt”;
mysql> select * from db3.user where id <= 5
into outfile “/var/lib/mysql-files/a1.txt”
fields terminated by “#”;
mysql> select * from db3.user where id <= 5
into outfile “/var/lib/mysql-files/a2.txt”
fields terminated by “#” lines terminated by “!!!”;
]# ls /var/lib/mysql-files/
]# vim /var/lib/mysql-files/a.txt
注意事项
具体如下:
-字段分隔符要与文件一致
-表字段类型和字段个数要与文件匹配-
-导入数据时指定文件的绝对路径
/etc/passwd
root❌0:0:root:/root:/bin/bash
用户:密码:uid:gid:用户描述信息:用户家目录:解析器
mysql> create database db3;
mysql> create table db3.user(
-> name char(50),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(150),
-> homedir char(50),
-> shell char(50)
-> );
mysql> desc db3.user;
mysql> select * from db3.user;
mysql> load data infile “/myload/passwd” into table db3.user fields terminated by “:” lines terminated by “\n”; //数据导入数据库
mysql> alter table db3.user add id int primary key auto_increment first; //增加id号,自增长
mysql> select * from db3.user;
mysql> select * from db3.user where id=1; //查看root的数据
数据导出
作用:把表记录存储到系统文件里
格式1
mysql> select命令 into outfile “目录名/文件” ; //文件名不需要提前建立,使用默认分隔符
格式2
mysql> select命令 into outfile “目录名/文件”
fields terminated by “分隔符”;
格式3
mysql> select命令 into outfile “目录名/文件”
fields terminated by “分隔符” lines terminated by “分隔符”;
mysql> select * from db1.user into outfile “/root/user1.txt”; //出错,导出只能导出到secure-file目录
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show variables like “secure_file_priv”;
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+
mysql> select * from db1.user into outfile “/var/lib/mysql-files/user1.txt”;
Query OK, 21 rows affected (0.00 sec)
可以手动修改secure-file目录
]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload“
:wq
]# systemctl restart mysqld
mysql> show variables like “secure_file_priv”; 查看
注意事项
具体如下:
-导出数据行数由sql查询决定
-导出的只有表记录,不包括表字段名
-自动创建数据的文件
存储数据文件,具有唯一性(不能混合存储)
增加表记录
语法格式
格式1:添加1条记录,给所有字段赋值
inser into 表名 values(字段值列表);
格式2:添加N条记录,给所有字段赋值
insert into 表名
values (字段值列表),(字段值列表),(字段值列表);
语法格式3:添加1条记录,给指定字段赋值
inser into 表名(字段名列表) values(字段列表);
格式4:添加N条记录,给指定字段赋值
insert into 表名(字段名列表)
values
(字段列表),
(字段列表),
(字段列表);
注意事项:
-字段值要与字段类型[相匹配
-字符类型的字段要用""括起来
-依次给所有字段赋值是,字段名可以省略
-只给部分字段赋值,必须明确写出对应的字段名称.
-没有赋值的字段使用默认或自增长赋值
查询
语法格式
格式1:查看所有记录
select 字段1 …字段N from 库名.表名;
格式2:条件查询
select 字段1 … 字段N from 库名.表名;
where 条件表达式;
注意事项
-*表示所有字段
-查看当前库表记录时库名可以省略
-字段列表决定好显示列个数
-条件决定显示行的个数
mysql> select id,name,shell from user;
mysql> select * from user;
mysql> select id,name,shell from user where name=“adm”;
mysql> select * from user where id<=5;
更新表记录
语法格式
格式1 批量更新
update 库名.表名
set 字段名=值,字段名=值,字段名=值…;
格式2,条件匹配更新
update 库名.表名
set 字段名=值,字段名=值,字段名=值,…;
where 条件表达式;
注意事项
-字段值要与字段类型相匹配
-对于字符类型的字段,值要用双括号括起来
-若不使用where限定条件,会更新所有记录字段值
限定条件时,只更新匹配条件的记录字段值
删除表记录
语法格式
格式1 条件匹配删除
delete from 库名.表名 where 条件表达式;
格式2 删除所有记录
delete from 库名.表名;
注意条件
-不加条件删除表中的所有行
匹配条件
基本匹配条件(适用于 select\ update \ delete)
1.数值比较 > >= < <= = !=
2.字符比较 = !=
3.范围内比较
4.逻辑比较 (多个判断条件)
逻辑与 and 或&& 多个判断条件同时成立
逻辑或 or 或|| 多个判断条件,某个成立即可
逻辑非 ! 或 not
or是逻辑或;and是逻辑与;!是逻辑非
5.空/非空比较 is null is not null
6.去掉重复数据
高级匹配条件(适用于 select\ update \ delete)
1.模糊匹配
用法 where 字段名 like ‘通配符’
_标准一个字符
% 表示0~n个字符
select name from user where name like ‘’; //名字是3个字符
select name from user where name like '%_’; //名字至少是4个字符
select name from user where name like ‘%a%’; //名字包含a的
select name from user where name like ‘a%’; //以字母a开头的名字
2.正则匹配
用法
where 字段名 regexp ‘正则表达式’
正则元字符 ^ $ . [ ] * |
select name from user where name regexp ‘^r.*y
′
;
/
/
s
e
l
e
c
t
n
a
m
e
,
u
i
d
f
r
o
m
u
s
e
r
w
h
e
r
e
u
i
d
r
e
g
e
x
p
′
.
.
.
.
'; // select name,uid from user where uid regexp '^....
′;//selectname,uidfromuserwhereuidregexp′....’;
insert into user(name) values (“aay”),(“n8n”);
select name from user where name regexp ‘[0-9]’;
3.四则运算
mysql> select name ,uid ,gid ,uid+gid unit from user where name=“bin”;
mysql> select name ,uid,gid,uid+gid unit from user ;
mysql> select name ,uid,gid,(uid+gid)/2 aver from user;
mysql> update user set gid=gid+1;
mysql> alter table user add age tinyint unsigned default 27 after name;
mysql> select name,age,2019-age birth_y from user where name=“root”; ///名字只能用_下划线,-表示减号
聚集函数
MYSQL 内置数据统计函数
avg(字段名)
sum(字段名)
min(字段名)
max(字段名)
count(字段名)
mysql> select min(uid) from user ;
mysql> select max(uid) from user;
mysql> select avg(uid) from user;
mysql> select sum(uid) from user;
mysql> select count(*) from user;
查询结果排序
SQL用法 : order by 字段名 [ asc|desc ];
asc 升序排序
desc 降序排序
mysql> select name,uid from user where
-> uid>=10 and uid <=500;
mysql> select name,uid from user where uid >=10 and uid <=500 order by uid;
mysql> select name,uid from user
-> where uid>=10 and uid <=500 order by uid desc ;
查询结果分组
SQL查询 group by 字段名
mysql>select shell from user ;
mysql> select shell from user group by shell;
mysql> select gid from user;
mysql> select gid from user group by gid;
查询结果过滤
mysql> select name from user where id<=20 having name=“rsync”;
mysql> select name from user where id<=20 having name=“adm”;
限制查询结果显示行数
mysql> select id,name,password from user where id<=20 limit 5;
mysql> select id,name,password from user where id<=20 limit 10;
mysql> select name,uid from user where id<=10 limit 0,2;
mysql> select name,uid from user where id<=10 limit 1,3;
mysql> select name,uid from user where id<=10 limit 3,3; //从第4行开始显示,显示3行
练习课
出错
mysql> mysql> load data “/xing/passwd”
-> into table teadb.usertab
-> fields terminated by “:”
-> lines terminated by “\n”;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
排错:
修改了mysql配置文件,一定要重启
[root@fay50 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/xing"
[root@fay50 ~]# systemctl restart mysqld
已解决
修改id10到50 的用户,sex 是girl
mysql> update teadb.usertab //注意是用update同步更新
-> set sex=“girl” //用set设置
-> where id between 10 and 50; //两个数字之间用between
两种答案
统计性别是girl的用户有多少个
方法一
select count(sex) from usertab where sex=“girl”;
方法二
select count(*) from usertab where sex=“girl” ;
优化代码
查看性别是girl 用户里的uid 号最大的用户名,叫什么
mysql> select max(uid) from teadb.usertab where sex=“girl”;
mysql> select username from teadb.usertab where uid=999;
优化结果:
mysql> select username from usertab where sex=“girl” order by uid desc limit 1; // order by uid desc 根据uid倒叙排列,limit 1 打印最后一行
为表添加记录,只为有部分数据,部分为空
mysql> insert into usertab(username,uid) values(“rtestd”,1000),(“rtest2d”,2000);
查找uid是4位数的用户和uid号
mysql> select username,uid from usertab where uid like “____”;
删除以d开头的用户(删除表记录)
mysql> delete from usertab where username like “d%”;
删除字段
mysql> alter table abc.xyz drop comment;
设置表中所有字段值不允许为空
每日有问190612:
1.在表db1.t1中的第一行添加新字段countnum 要求:为4位的正整数
2.在表db1.t1中countnum字段添加为索引字段,索引名称为cc
3.新建表db1.t2,创建复合主键tom和jerry ,要求:tom和jerry都为char(10)
6月12日问题答案:
1.mysql> alter table db1.t1 add countnum int(4) unsigned first;
2.mysql> create index cc on db1.t1(countnum);
3.mysql> create table db1.t2( tom char(10),jerry char(10),primary key(tom,jerry));