mysql数据库入门 3 +每日一问

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${}^{\prime };//selectname,uidfromuserwhereuidregex{p}^{\prime .}...$’;
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));