数据库应用_表结构_键值
随笔结构:1.数据导入导出; 2.管理表格的记录; 3.数据的匹配条件.
一,数据的导入与导出
* mysql默认的导入导出路径为/var/lib/mysql-files/
* 定义路径的变量名secure_file_priv
* 修改路径的方式:1).创建目录mkdir /myfile;
2).赋权chown mysql /myfile;
3).将secure_file_priv写入配置文件/etc/my.cnf;
4).重启服务;
5).进入数据库使用show variables like "%secure_file_priv%"查看路径是否修改成功.
1.导入数据
例:将/etc/passwd文件内容导入db3库下的user表,并添加行号字段.
思路:建库db3->建表user->导入数据->为表格添加id号
1.1 建库 create database db3;
1.2. 建表
1.3. 导入数据:mysql>load data infile “/var/lib/mysql-files/” into table db3.user fields terminated by “:” lines terminated by “\n”;
1.4. 为表格添加id号: alter table user add id primary key auto_increment first;
2.导出数据
2.1 命令格式:1).>select 命令字符 into outfile "目录/文件";
2).>select 命令字符 into outfile "目录/文件" fields terminated by "分隔符";
3).>select 命令字符 into outfile "目录/文件" fields terminated by "分隔符" lines terminated by "\n";
例: 将库db3中的user表id<=10的数据导出,保存至/myfile/user3.txt.
mysql> select * from user where id<=10 into outfile "/myfile/user3.txt";
Query OK, 10 rows affected (0.00 sec)
2.2 在/myfile/user3.txt目录下查看导出内容:
2.3 注意事项: 1).导出的数据行数由select查询决定;
2).导出的是表记录,不包括字段名;
3).自动创建存储导出数据的文件,而且文件在路径中具有唯一性;
二, 管理表记录
内容概要: 对表格增加,删除,改动,查询.
1. 增加表格记录
1.1 给所有字段赋值:
mysql>insert into 库.表 values (字段值x),(字段值y),...(n个字段值);
例: mysql> insert into user values(27,"cuihua","x",411,401,"test user","/home/jack/","/bin/bash/");
1.2 指定字段赋值:
mysql>insert into 库.表(字段名x,字段名y) values (字段值a),(字段值b),...(n个字段值);
例: mysql> insert into user(name,uid) values("m1",405),("m2",406),("m3",407);
1.3 注意事项: 1).字段值要与字段类型匹配;
2).字符类型的内容,要用””括起来;
3).依次对所有字段赋值时,字段名可以省略;对部分字段赋值,则必须写明对应字段名称;
4).没有赋值,则使用默认值或自增长赋值;
2. 删除表格记录
2.1 删除所有记录:
mysql>delete from 库.表;
例: mysql>delete from db3.user;
2.2 条件匹配删除:
mysql>delete from 库.表 where 条件表达式;
例: mysql>delete from db3.user where id<=3;
3.更新/改动表格记录
3.1 更新所有记录:
mysql> update 库.表 set 字段名="值",字段名="值"...;
例: mysql>update db3.user set name=”jack”,password=”AA”;
3.2 条件匹配更新记录:
mysql> update 库.表 set 字段名="值",字段名="值"... where 条件表达;
例: mysql>update db3.user set name=”jack”,password=”AA” where uid=400;
3.3 注意事项: 1).字段值与字段类型要匹配; 2).字符类型需要使用双引号括起来.
4.查询表记录
4.1 查看所有记录:
mysql> select 字段1...,字段n from 库.表;
例: mysql> select name,uid,gid from db3.user;
4.2 条件查询:
mysql>select 字段1...,字段n from 库.表 where 条件表达式;
例: mysql> select * from db3.user where name=”jim”;
4.3 注意事项: 1).命令行的*代表所有字段; 2).字段列表决定显示列的个数,条件决定显示的行数.
三, 匹配条件
基本匹配条件概要: 1).数值比较; 2).字符比较; 3).逻辑比较; 4).范围内比较与去重显示.
高级匹配条件概要: 1).模糊匹配; 2).正则匹配; 3).四则运算.
操作查询结果: 1).聚集函数; 2).排序; 3).分组; 4).过滤; 5).限制显示行数.
3.1 基本匹配条件
3.1.1数值比较
3.1.2 字符比较
3.1.3 逻辑匹配
3.1.4 范围匹配与去重显示
例:
mysql> select uid from user where uid in (3,5,6);
mysql> select name,shell from user where shell not in ("/bin/bash","/sbin/nologin");
mysql> select name,uid from user where uid between 0 and 100;
mysql> select distinct shell from user where uid>=100;
3.2 高级匹配
3.2.1 模糊查询
格式: where 字段名 like "通配符" ("_"标识匹配一个字符,"%"表示匹配0-n个字符)
例:
mysql> select name from user where name like "_ _";
mysql> select name from user where name like "%_%";
mysql> select name from user where name like "%a%";
mysql> select name from user where name like "a%";
mysql> select name from user where name like "%a";
3.2.2 正则表达regexp
格式: where 字段名 regexp '正则表达' ( 常用正则符号^ $ . [] * | )
例:
mysql> select name from user where name regexp '^r|y$';
mysql> select name from user where name regexp '^r.*$';
mysql> select name,uid from user where uid regexp '[0-9]';
mysql> select name,uid from user where uid regexp '^....$';
3.2.3 四则运算
例:
mysql> select name,uid,gid,uid+gid zonghe from user where name="bin";
mysql> select name,uid,gid,(uid+gid)/2 pjz from user where name="bin";
update user set gid=gid+1;
mysql> select name,age,2019-age your_birth from user where name="root";
3.3 操作查询结果
3.3.1 聚集函数,即mysql内置的数据统计函数
例:
mysql> select min(uid) from user;
mysql> select max(uid) from user;
mysql> select sum(uid) from user;
mysql> select count(*) from user where shell="/sbin/nologin";
mysql> select count(name) from user where shell="/sbin/nologin";
3.3.2 对查询结果排序
格式: sql查询命令行 order by 字段名(通常为数值类型) asc|desc; (asc升序|desc降序),(默认升序排列)
例:
mysql> select name,uid from user where uid>=10 and uid<=100 order by uid;
mysql> select name,uid from user where uid>=10 and uid<=100 order by uid asc;
mysql> select name,uid from user where uid>=10 and uid<=100 order by uid desc;
3.3.3 对查询结果分组
格式: sql查询命令行 group by 字段名(通常为字符类型)
例:
mysql>select shell from user where gid<=1000 group by shell;
mysql>select gid from user where gid<=1000 group by gid;
mysql> select homedir from user where gid<=1000 group by homedir;
3.3.4 对查询结果过滤
格式: sql查询命令行 having 表达式
例:
mysql> select name from user where id<=20 having name="adm";
mysql> select name from user where id<=20 having name="games";
3.3.5 限制显示结果的行数
格式1: sql查询命令行 limit 数字; (显示查询结果前多少条记录)
格式2: sql查询命令行 limit 数字1,数字2; (显示指定范围内的查询记录)
备注: 数字1-示意起始行, 数字2-示意总行数, 0表示第一行.
例:
mysql> select name,uid,gid from user where id<=10 limit 2;
mysql> select name,uid,gid from user where id<=10 limit 0,2;
mysql> select name,uid,gid from user where id<=10 limit 1,6;(从第1行开始,总共显示6行)
结束
