三十一.MySQL存储引擎 、 数据导入导出 管理表记录 匹配条件

1.MySQL存储引擎的配置 查看服务支持的存储引擎 查看默认存储类型 更改表的存储引擎 设置数据库服务默认使用的存储引擎 1.1 查看存储引擎信息 mysql> SHOW ENGINES\G   1.2 查看默认存储类型 mysql> SHOW VARIABLES LIKE ‘default_storage_engine‘; +------------------------+--------+ | Variable_name          | Value  | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+   1.3 修改默认存储引擎 1.3.1 指令修改 mysql> SET default_storage_engine=MyISAM; mysql> SHOW VARIABLES LIKE ‘default_storage_engine‘; +------------------------+--------+ | Variable_name          | Value  | +------------------------+--------+ | default_storage_engine | MyISAM | +------------------------+--------+ 1.3.2 配置文件修改 ]# vim /etc/my.cnf ... default_storage_engine=MEMORY   //改用MEMORY引擎 ]# systemctl restart mysqld.service mysql> SHOW VARIABLES LIKE ‘default_storage_engine‘;   2.数据导入、导出 2.1 将/etc/passwd文件导入MySQL数据库 2.1.1 新建userdb库、user表 mysql> CREATE DATABASE userdb; mysql> USE userdb; mysql> CREATE TABLE user(     -> name varchar(24) NOT NULL,     -> passwd varchar(48) DEFAULT ‘x‘,     -> uid int(5) NOT NULL,     -> gid int(5) NOT NULL,     -> fullname varchar(128),     -> homedir varchar(64) NOT NULL,     -> shell varchar(24) NOT NULL     -> ); mysql> DESC user; +----------+-------------+------+-----+---------+-------+ | Field    | Type        | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | name     | varchar(24) | NO   |     | NULL    |       | | passwd   | varchar(48) | YES  |     | x       |       | | uid      | int(5)      | NO   |     | NULL    |       | | gid      | int(5)      | NO   |     | NULL    |       | | fullname | varchar(128) | YES  |     | NULL    |       | | homedir  | varchar(64) | NO   |     | NULL    |       | | shell    | varchar(24) | NO   |     | NULL    |       | +----------+-------------+------+-----+---------+-------+   2.1.2 查看mysql的工作文件夹 mysql> SHOW VARIABLES LIKE ‘%secure_file_priv‘; +------------------+-----------------------+ | Variable_name    | Value                 | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 在MySQL 5.7.6版本之后，导入文件只能在secure_file_priv指定的文件夹下。   2.1.3 拷贝数据到目录 mysql> system cp /etc/passwd /var/lib/mysql-files/ mysql> LOAD DATA INFILE ‘/var/lib/mysql-files/passwd‘     -> INTO TABLE user     -> FIELDS TERMINATED BY ‘:‘     -> LINES TERMINATED BY ‘\n‘;(这条默认有，除非用其他分隔用） mysql> SELECT COUNT(*) FROM user; +----------+ | COUNT(*) | +----------+ |       42 | +----------+   2.1.4 查看user前5条记录，列出用户名、UID mysql> SELECT name ,uid FROM user LIMIT 5; +--------+-----+ | name   | uid | +--------+-----+ | root   |   0 | | bin    |   1 | | daemon |   2 | | adm    |   3 | | lp     |   4 | +--------+-----+   2.1.5 为user表中的每条记录添加自动编号 mysql> ALTER TABLE user ADD sn int(4) AUTO_INCREMENT PRIMARY KEY FIRST; mysql> SELECT sn,name FROM user LIMIT 5; +----+--------+ | sn | name   | +----+--------+ |  1 | root   | |  2 | bin    | |  3 | daemon | |  4 | adm    | |  5 | lp     | +----+--------+   2.2 从MySQL数据库中导出查询结果 以将userdb库user表中UID小于100的前10条记录导出为/myload/user1.txt   2.2.1 修改默认目录 ]# ls -ld /var/lib/mysql-files/ drwxr-x--- 2 mysql mysql 20 2月  13 15:10 /var/lib/mysql-files/ ]# mkdir /myload ]# chown mysql /myload ]# ls -ld /myload drwxr-xr-x 2 mysql root 6 2月  13 15:37 /myload # vim /etc/my.cnf  [mysqld] ... secure_file_priv="/myload" ]# systemctl restart mysqld  mysql> mysql> SHOW VARIABLES LIKE ‘secure_file_priv‘;   2.2.2 导出user表中UID小于5的记录 如果以默认的‘\n‘ 为行分隔，导出操作同样可不指定LINES TERMINATED BY： mysql> SELECT * FROM userdb.user1 WHERE uid<5; +--------+--------+-----+-----+----------+----------------+---------------+ | name   | passwd | uid | gid | fullname | homedir        | shell         | +--------+--------+-----+-----+----------+----------------+---------------+ | root   | x      |   0 |   0 | root     | /root          | /bin/bash     | | bin    | x      |   1 |   1 | bin      | /bin           | /sbin/nologin | | daemon | x      |   2 |   2 | daemon   | /sbin          | /sbin/nologin | | adm    | x      |   3 |   4 | adm      | /var/adm       | /sbin/nologin | | lp     | x      |   4 |   7 | lp       | /var/spool/lpd | /sbin/nologin | +--------+--------+-----+-----+----------+----------------+---------------+ 导出： mysql> SELECT * FROM userdb.user1 WHERE uid<5     -> INTO OUTFILE ‘/myload/user1.txt‘     -> FIELDS TERMINATED BY ‘:‘; ]# wc -l /myload/user1.txt 5 /myload/user1.txt   3.操作表记录 表记录的插入 表记录的更新 表记录的查询 表记录的删除   3.1 删除test.user1表的所有记录 mysql> DELETE FROM user1; mysql> SELECT * FROM stu_info;   3.2 给t1插入三条数据 mysql> INSERT t1 VALUES     -> (‘Jim‘,‘girl‘,24),     -> (‘Tom‘,‘boy‘,21),     -> (‘Lily‘,‘girl‘,20);   3.3 只插入部分字段的值 mysql> INSERT INTO t1(name,age)     -> VALUES(‘Jerry‘,27); 3.3 更新表记录时，若未限制条件，则适用于所有记录 mysql> UPDATE t1 SET age=10; 则所有人年龄都是10   3.4 更新表记录时，可以限制条件，只对符合条件的记录有效 mysql> UPDATE t1 SET age=20     -> WHERE gender=‘boy‘;   3.5 删除表记录时，可以限制条件，只删除符合条件的记录 mysql> DELETE FROM t1 WHERE age < 18;   3.6 删除表记录时，如果未限制条件，则会删除所有的表记录 mysql> DELETE FROM t1;   4.查询及匹配条件   4.1 查询stu_info表一共有多少条记录 mysql> SELECT count(*) FROM stu_info;   4.2 计算stu_info表中各学员的平均年龄、最大年龄、最小年龄 mysql> SELECT avg(age),max(age),min(age) FROM stu_info;   4.3 计算stu_info表中男学员的个数 mysql> SELECT count(gender) FROM stu_info WHERE gender=‘boy‘;   4.4 列出stu_info表中年龄为21岁的学员记录 mysql> SELECT * FROM stu_info WHERE age=21;   4.5 列出stu_info表中年龄超过21岁的学员记录 mysql> SELECT * FROM stu_info WHERE age>21;   4.6 列出stu_info表中年龄大于或等于21岁的学员记录 mysql> SELECT * FROM stu_info WHERE age>=21;   4.7 列出stu_info表中年龄在20岁和24岁之间的学员记录 mysql> SELECT * FROM stu_info WHERE age BETWEEN 20 and 24;   4.8 列出stu_info表中年龄小于23岁的女学员记录 mysql> SELECT * FROM stu_info WHERE age < 23 AND gender=‘girl‘;   4.9 列出stu_info表中年龄小于23岁的学员，或者女学员的记录： mysql> SELECT * FROM stu_info WHERE age < 23 OR gender=‘girl‘;   4.10 如果某个记录的姓名属于指定范围内的一个，则将其列出 mysql> SELECT * FROM stu_info WHERE name IN     -> (‘Jim‘,‘Tom‘,‘Mickey‘,‘Minnie‘);   4.11 计算1234与5678的和 mysql> SELECT 1234+5678;   4.12 输出stu_info表各学员的姓名、15年后的年龄 mysql> SELECT name,age+15 FROM stu_info;   4.13 列出stu_info表中姓名以“J”开头的学员记录 mysql> SELECT * FROM stu_info WHERE name LIKE ‘J%‘;   4.14 列出stu_info表中姓名以“J”开头且只有3个字母的学员记录 mysql> SELECT * FROM stu_info WHERE name LIKE ‘J__‘;   4.15 列出stu_info表中姓名以“J”开头且以“y”结尾的学员记录 mysql> SELECT * FROM stu_info WHERE name REGEXP ‘^J.*y$‘; 效果等同于： mysql> SELECT * FROM stu_info WHERE name Like ‘J%y‘;   4.16 列出stu_info表中姓名以“J”开头或者以“y”结尾的学员记录： mysql> SELECT * FROM stu_info WHERE name REGEXP ‘^J|y$‘; 效果等同于： mysql> SELECT * FROM stu_info WHERE name Like ‘J%‘ OR name Like ‘%y‘;   4.17 列出stu_info表的所有记录，按年龄排序 mysql> SELECT * FROM stu_info GROUP BY age (ASC);(由小到大,默认) mysql> SELECT * FROM stu_info GROUP BY age DESC;(由大到小）   4.18 限制查询结果的输出条数，LIMIT mysql> SELECT * FROM stu_info LIMIT 3;   4.19 列出stu_info表中年龄最大的3条学员记录 mysql> SELECT * FROM stu_info GROUP BY age DESC LIMIT 3;   4.20 分组查询结果，GROUP BY 针对stu_info表，按性别分组，分别统计出男、女学员的人数： mysql> SELECT gender,count(gender) FROM stu_info GROUP BY gender; +--------+---------------+ | gender | count(gender) | +--------+---------------+ | boy    |             3 | | girl   |             2 | +--------+---------------+ 列出查询字段时，可以通过AS关键字来指定显示别名，比如上述操作可改为： mysql> SELECT gender AS ‘性别‘,count(gender) AS ‘人数‘     -> FROM stu_info GROUP BY gender; +--------+--------+ | 性别   | 人数   | +--------+--------+ | boy    |      3 | | girl   |      2 | +--------+--------+

三十一.MySQL存储引擎 、 数据导入导出 管理表记录 匹配条件

标签：shel   user   文件夹   sam   rom   incr   emc   rdb   自动编号