Mysql 的体系结构
导出 导入 的路径目录
[root@host50 ~]# ls /var/lib/mysql-files/ ——目录是安装软件的时候就会创建
mysql> show variables like "secure_file_priv"; ——系统默认的路径,可以自定义修改
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv |
/var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)
自定义数据库的导入导出文件存储目录
[root@host50 ~]# mkdir /mydata
[root@host50 ~]# chown mysql /mydata/ ——把所属者和所属组修改成mysql
[root@host50 ~]# vim /etc/my.cnf ————修改配置文件,添加指定的目录
[mysqld]
secure_file_priv="/mydata"
:wq
[root@host50 ~]# systemctl stop mysqld——重起失败可以查看防火墙和SEliunx是否关闭
[root@host50 ~]# systemctl restart mysqld
mysql> show variables like "secure_file_priv";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: db3
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| secure_file_priv |
/mydata/ |
+------------------+----------+
1 row in set (0.02 sec)
导入数据的步骤:
1.把文件拷贝导mysql的指定目录下,也可以在数据库的服务里用sql命令执行
mysql> system cp /etc/passwd /mydata/ ————不用分号结束
mysql> system ls /mydata
passwd
mysql> system head -2 /mydata/passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
2.创建表
给表赋值 ,
给信息做索引
mysql> create table user(
name char(80), password char(1), uid int(2), gid int(2), comment varchar(150),homedir char(150), shell char(50),
index(name));
Query OK, 0 rows affected (0.24 sec)
mysql> desc user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name | char(80) | YES |
MUL | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(2) | YES | | NULL | |
| gid | int(2) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> select * from user;
Empty set (0.00 sec)
复合索引的创建??
3.导入数据 用sql命令导入数据
mysql> load data infile "/mydata/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
\n 是换行的意思
Query OK, 41 rows affected (0.06 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from user;
mysql> alter table user add id int(2) primary key auto_increment first;——插入id号,方便查找信息
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from user;
mysql> select * from user where id=15; ————查找表里信息
+----+------+----------+------+------+--------------------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+--------------------+---------+---------------+
| 15 | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
+----+------+----------+------+------+--------------------+---------+---------------+
1 row in set (0.00 sec)
数据的导出:把数据库服务器的表里记录存储导系统的文件里
1.用sql命令导出数据
mysql SQL查询命令 into outfile "/mydata/文件名"
mysql> select * from t1;
+----+-------+------+----------+
| id | name | age | pay |
+----+-------+------+----------+
| 1 | tom | 21 | 1800.00 |
| 2 | lucy | 25 | 18800.00 |
| 7 | jerry | 33 | 18000.00 |
| 8 | alice | 33 | 18000.00 |
| 9 | jerry | 23 | 18000.00 |
+----+-------+------+----------+
5 rows in set (0.00 sec)
mysql> select * from t1 into outfile "/mydata/t1.txt"; ——导出数据,不指定分隔符,就是默认的tab
Query OK, 5 rows affected (0.00 sec)
[root@host50 ~]# ls /mydata/t1.txt
/mydata/t1.txt
[root@host50 ~]# cat /mydata/t1.txt
1 tom 21 1800.00
2 lucy 25 18800.00
7 jerry 33 18000.00
8 alice 33 18000.00
9 jerry 23 18000.00
mysql> select * from t1 into outfile "/mydata/t2.txt" fields terminated by "#"; ——导出数据,指定分隔符,以#号进行分割
Query OK, 5 rows affected (0.00 sec)
[root@host50 ~]# cat /mydata/t2.txt
1#tom#21#1800.00
2#lucy#25#18800.00
7#jerry#33#18000.00
8#alice#33#18000.00
9#jerry#23#18000.00
mysql> select * from t1 into outfile "/mydata/t3.txt" fields terminated by "#" lines terminated by "!!!";
Query OK, 5 rows affected (0.00 sec)
——导出数据,指定以#号为分割符,行以!号为分割符
[root@host50 ~]# cat /mydata/t3.txt
1#tom#21#1800.00!!!2#lucy#25#18800.00!!!7#jerry#33#18000.00!!!8#alice#33#18000.00!!!9#jerry#23#18000.00!!!
导出数据是不包获,数据的字段名 导出数据不可以与目录下文件同名
mysql> select * from t1;
+----+-------+------+----------+
| id | name | age | pay |
+----+-------+------+----------+
| 1 | tom | 21 | 1800.00 |
| 2 | lucy | 25 | 18800.00 |
| 7 | jerry | 33 | 18000.00 |
| 8 | alice | 33 | 18000.00 |
| 9 | jerry | 23 | 18000.00 |
+----+-------+------+----------+
管理表记录
添加新记录 insert into
添加一条记录
insert into 表名 values (字段值列表)
mysql> insert into user values(42,"bob","x",2000,2000,"student user","/home/bob","/bin/bash");
Query OK, 1 row affected (0.03 sec)
添加多条记录
insert into 表名 values (字段值列表),(字段值列表)
mysql> insert into user values(43,"bob","x",2000,2000,"student user","/home/bob","/bin/bash")
-> ,(44,"bob","x",2000,2000,"student user","/home/bob","/bin/bash"),(45,"bob","x",2000,2000,"student user","/home/bob","/bin/bash");
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
——可以随便改变id 但是添加的类型一定要跟表结构一致
mysql> select * from user;
| 42 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |
| 43 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |
| 44 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |
| 45 | bob | x | 2000 | 2000 | student user | /home/bob | /bin/bash |
添加一条记录 指定字段赋值
insert into 表名(字段名列表) values (字段值列表)
mysql> insert into user (name,shell,uid) values("lucy","/sbin/nologin",1998);
Query OK, 1 row affected (0.04 sec)
添加多条记录 指定字段赋值
insert into 表名 (字段名列表)values (字段值列表),(字段值列表)
mysql> insert into user (name,shell,uid) values("alice","/sbin/nologin",1948);
Query OK, 1 row affected (0.03 sec)
mysql> select * from user;——添加了外键 可以自动添加id号
| 46 | lucy | NULL | 1998 | NULL | NULL | NULL | /sbin/nologin |
| 47 | alice | NULL | 1948 | NULL | NULL | NULL | /sbin/nologin |
查询记录select
select 字段名列表 from 表 where 条件;
select * from 表名
* 代表所有行所有列
mysql> select name,uid,shell from user;
mysql> select name,uid,shell from user where id=1;
mysql> select name,uid,shell from user where shell="/bin/bash";
无非就是这三种查询方式 ,变化最多是后面的条件
更新记录字段的值 update
主要是更新那些列的数据
update 表 set 字段名=值 ,字段名=值,。。。 where 条件;
mysql> update user set password="A",gid=1000;
mysql> select "password","gid" from user;
+----------+-----+
| password | gid |
+----------+-----+
| password | gid |
| password | gid |
| password | gid |
mysql> select password,gid from user;
+----------+------+
| password | gid |
+----------+------+
| A | 1000 |
| A | 1000 |
| A | 1000 |
| A | 1000 |
| A | 1000 |
mysql> update user set password="x" where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select password,gid from user;
+----------+------+
| password | gid |
+----------+------+
| x | 1000 |
mysql> select password,gid from user where id=1;
+----------+------+
| password | gid |
+----------+------+
| x | 1000 |
+----------+------+
1 row in set (0.00 sec)
删除记录 delete
delete from 表 where 条件;
mysql> select * from user where id=45;
+----+------+----------+------+------+--------------+-----------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+--------------+-----------+-----------+
| 45 | bob | A | 2000 | 1000 | student user | /home/bob | /bin/bash |
+----+------+----------+------+------+--------------+-----------+-----------+
mysql> delete from user where name="bob";——按添加删除记录
Query OK, 4 rows affected (0.03 sec)
mysql> select * from user where id=45;
Empty set (0.00 sec)
匹配条件的表示方式(select update delete)
对记录做select update delete时可以使用的条件表示方式:
基本匹配条件
数值比较 字符比较> >= = < <= !=
where 字段名 符号 数值
mysql> select * from user where id <=10;
+----+----------+----------+------+------+----------+-----------------+----------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+----------+----------+------+------+----------+-----------------+----------------+
| 1 | root | x | 0 | 1000 | root | /root | /bin/bash |
| 2 | bin | A | 1 | 1000 | bin | /bin | /sbin/nologin |
| 3 | daemon | A | 2 | 1000 | daemon | /sbin | /sbin/nologin |
| 4 | adm | A | 3 | 1000 | adm | /var/adm | /sbin/nologin |
| 5 | lp | A | 4 | 1000 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | A | 5 | 1000 | sync | /sbin | /bin/sync |
| 7 | shutdown | A | 6 | 1000 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | A | 7 | 1000 | halt | /sbin | /sbin/halt |
| 9 | mail | A | 8 | 1000 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | A | 11 | 1000 | operator | /root | /sbin/nologin |
+----+----------+----------+------+------+----------+-----------------+----------------+
10 rows in set (0.00 sec)
查看记录修改记录
mysql> select name,uid,gid from user where uid=gid;
+------+------+------+
| name | uid | gid |
+------+------+------+
| lisi | 1000 | 1000 |
mysql> select name from user where name="apache";
Empty set (0.00 sec)
mysql> select name,shell from user where shell !="/bin/bash";——按条件查找记录
+---------------------+----------------+
| name | shell |
+---------------------+----------------+
| bin | /sbin/nologin |
| daemon | /sbin/nologin |
| adm | /sbin/nologin |
mysql> update user set gid=2013 where shell !="/bin/bash";——按条件修改记录
Query OK, 41 rows affected (0.03 sec)
Rows matched: 41 Changed: 41 Warnings: 0
mysql> select name,shell,gid from user where shell !="/bin/bash";——按条件修改记录
+---------------------+----------------+------+
| name | shell | gid |
+---------------------+----------------+------+
| bin | /sbin/nologin | 2013 |
| daemon | /sbin/nologin | 2013 |
| adm | /sbin/nologin | 2013 |
修改空和非空
匹配空 is null 匹配非空 is not null
先写入几条记录:
mysql> insert into user(id,name)values(51,""),(52,"null"),(53,null);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select id,name from user where id>=51;
+----+------+
| id | name |
+----+------+
| 51 | |
| 52 | null |
| 53 | NULL |
+----+------+
3 rows in set (0.00 sec)
mysql> select name,shell from user where shell is not null;
+---------------------+----------------+
| name | shell |
+---------------------+----------------+
| root | /bin/bash |
| bin | /sbin/nolog
mysql> select name,shell from user where shell is null;
+------+-------+
| name | shell |
+------+-------+
| | NULL |
| null | NULL |
| NULL | NULL |
+------+-------+
mysql> select * from user where name="bin";
+----+------+----------+------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+---------------+
| 2 | bin | A | 1 | 2013 | bin | /bin | /sbin/nologin |
+----+------+----------+------+------+---------+---------+---------------+
1 row in set (0.01 sec)
mysql> update user set
password=null where name=
"bin";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where name="bin";
+----+------+----------+------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+---------------+
| 2 | bin |
NULL | 1 | 2013 | bin | /bin | /sbin/nologin |
+----+------+----------+------+------+---------+---------+---------------+
1 row in set (0.00 sec)
逻辑比较
OR 逻辑或 一个条件成立就可以
mysql> select name,uid from user where name="root" or uid=1;
+------+------+
| name | uid |
+------+------+
| root | 0 |
| bin | 1 |
+------+------+
2 rows in set (0.00 sec)
AND逻辑与 两个条件同时成立
mysql> select * from user where name="root" and uid=1 and shell="/bin/bash";
Empty set (0.00 sec)
!逻辑非 取反的意思
()提高优先等级 ——优先执行 默认是
与优先于
或
mysql> select name,uid from user where name="root" or name="bin" and uid=1; ——先执行
与,再执行
或
+------+------+
| name | uid |
+------+------+
| bin | 1 |
| root | 0 |
+------+------+
2 rows in set (0.00 sec)
mysql> select name,uid from user where (name="root" or name="bin") and uid=1;——优先执行
或,再执行
与
+------+------+
| name | uid |
+------+------+
| bin | 1 |
+------+------+
1 row in set (0.00 sec)
范围内匹配/去重显示
一。in 值列表 ——在。。。范围内
mysql> select name from user where name
in ("root","sync","lucy","bob");
+------+
| name |
+------+
| lucy |
| root |
| sync |
+------+
3 rows in set (0.00 sec)
二。not in 值列表 ——不在。。。范围内
mysql> select name,shell from user where shell
not in ("/bin/bash","/sbin/nologin");
+----------+----------------+
| name | shell |
+----------+----------------+
| sync | /bin/sync |
| shutdown | /sbin/shutdown |
| halt | /sbin/halt |
| mysql | /bin/false |
+----------+----------------+
4 rows in set (0.00 sec)
三。between 数字1 and 数字2 ——在什么之间
mysql> select * from user where uid between 10 and 20;
+----+----------+----------+------+------+----------+------------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+----------+----------+------+------+----------+------------+---------------+
| 10 | operator | A | 11 | 2013 | operator | /root | /sbin/nologin |
| 11 | games | A | 12 | 2013 | games | /usr/games | /sbin/nologin |
| 12 | ftp | A | 14 | 2013 | FTP User | /var/ftp | /sbin/nologin |
+----+----------+----------+------+------+----------+------------+---------------+
四。distinct (只适合select查询使用) ——去重显示
mysql> select shell from user;
mysql> select distinct shell from user; ——测试这两种查找命令的不一样
mysql> select shell from user where uid <=500;
mysql> select distinct shell from user where uid <=500;——测试两种查找命令的结果
高级匹配条件
模糊查询
where 字段名 like '表达式' _匹配任意一个字符 %匹配零个或多个字符
select name from user where name like ' ___'
select name from user where name like '%a%';
select name from user where name like '%';——这两条命令是一样的
select name from user where name like="";
mysql> select name from user where name is null;——字符不为空
正则表达式
where 字段名 regexp “正则表达式”;
正则表达式符号 ^ * $ | . []
mysql> select name from user where name regexp '^a.t*$';
Empty set (0.00 sec)
mysql> select name from user where name regexp '[0-9]';
Empty set (0.00 sec)
mysql> insert into user(name) values ("ya9ya"),("7yaya"),("yaya8");
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select name from user where name regexp '[0-9]';
+-------+
| name |
+-------+
| 7yaya |
| ya9ya |
| yaya8 |
+-------+
3 rows in set (0.00 sec)
mysql> select name,uid from user where uid regexp '....';
+-----------+-------+
| name | uid |
+-----------+-------+
| nfsnobody | 65534 |
| lisi | 1000 |
| bob | 2000 |
| lucy | 1998 |
| alice | 1948 |
| bob | 2000 |
| bob | 2000 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql> select name,uid from user where uid regexp '^....$';
+-------+------+
| name | uid |
+-------+------+
| lisi | 1000 |
| bob | 2000 |
| lucy | 1998 |
| alice | 1948 |
| bob | 2000 |
| bob | 2000 |
+-------+------+
6 rows in set (0.01 sec)
四则运算 +-*/%(字段类型必须是数值类型)
mysql> select name,uid from user where id<=10;
+----------+------+
| name | uid |
+----------+------+
| root | 0 |
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
| sync | 5 |
| shutdown | 6 |
| halt | 7 |
| mail | 8 |
| operator | 11 |
+----------+------+
10 rows in set (0.00 sec)
mysql> update user set uid=uid+1 where id<=10
-> ;
Query OK, 10 rows affected (0.02 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> select name,uid from user where id<=10;
+----------+------+
| name | uid |
+----------+------+
| root | 1 |
| bin | 2 |
| daemon | 3 |
| adm | 4 |
| lp | 5 |
| sync | 6 |
| shutdown | 7 |
| halt | 8 |
| mail | 9 |
| operator | 12 |
+----------+------+
10 rows in set (0.01 sec)
mysql> select name,uid,gid,uid+gid jirguo from user where name="root";
+------+------+------+--------+
| name | uid | gid | jirguo |
+------+------+------+--------+
| root | 1 | 1000 | 1001 |
+------+------+------+--------+
1 row in set (0.00 sec)
mysql> select name,uid,gid,"uid"+"gid" jirguo from user where name="root";
+------+------+------+--------+
| name | uid | gid | jirguo |
+------+------+------+--------+
| root | 1 | 1000 | 0 |
+------+------+------+--------+
数据存储引擎 ***
数据的导入:把系统文件的内容存储导数据库服务器的表里
有格式规律
管理表记录 select update insert delete