版本：5.7.28
服务器：mysql 192.168.4.20
1.MySQL存储引擎的配置
查看服务支持的存储引擎
查看默认存储类型
更改表的存储引擎
设置数据库服务默认使用的存储引擎
查看存储引擎信息
mysql> SHOW ENGINES;
-------------------- --------- -------------------
| Engine | Support |
-------------------- --------- ---------------------
| InnoDB | DEFAULT |
| MRG_MYISAM | YES |
| MEMORY | YES |
| BLACKHOLE | YES |
| MyISAM | YES |
| CSV | YES |
| ARCHIVE | YES |
| PERFORMANCE_SCHEMA | YES |
| FEDERATED | NO |
-------------------- --------- --------------------
查看默认存储类型
mysql> SHOW VARIABLES LIKE ‘default_storage_engine‘;
------------------------ --------
| Variable_name | Value |
------------------------ --------
| default_storage_engine | InnoDB |
------------------------ --------
修改默认存储引擎
指令修改
mysql> SET default_storage_engine=MyISAM;
mysql> SHOW VARIABLES LIKE ‘default_storage_engine‘;
------------------------ --------
| Variable_name | Value |
------------------------ --------
| default_storage_engine | MyISAM |
------------------------ --------
配置文件修改
]# vim /etc/my.cnf
...
default_storage_engine=MEMORY //改用MEMORY引擎
]# systemctl restart mysqld.service
mysql> SHOW VARIABLES LIKE ‘default_storage_engine‘;
------------------------ --------
| Variable_name | Value |
------------------------ --------
| default_storage_engine | MEMORY |
------------------------ --------
2.数据导入、导出
2.1 将/etc/passwd文件导入MySQL数据库
新建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 | |
---------- -------------- ------ ----- --------- -------
查看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指定的文件夹下。
拷贝数据到目录
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(*) |
----------
| 24 |
----------
查看user前5条记录，列出用户名、UID
mysql> SELECT name ,uid FROM user LIMIT 5;
-------- -----
| name | uid |
-------- -----
| root | 0 |
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
-------- -----
为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条记录导出
安全导出
mysql> SELECT * FROM user WHERE uid>100 LIMIT 10 into outfile ‘/var/lib/mysql-files/test.txt‘;
以将userdb库user表中UID大于100的前10条记录导出为/myload/user1.txt
修改默认目录
]# ls -ld /var/lib/mysql-files/
drwxr-x--- 2 mysql mysql 36 2月 3 10:29 /var/lib/mysql-files/
]# mkdir /myload
]# chown mysql /myload
]# ls -ld /myload
drwxr-xr-x 2 mysql root 6 2月 3 10:32 /myload
# vim /etc/my.cnf
[mysqld]
...
secure_file_priv="/myload"
]# systemctl restart mysqld
mysql> SHOW VARIABLES LIKE ‘secure_file_priv‘;
------------------ ----------
| Variable_name | Value |
------------------ ----------
| secure_file_priv | /myload/ |
------------------ ----------
导出user表中UID小于5的记录
如果以默认的‘n‘ 为行分隔，导出操作同样可不指定LINES TERMINATED BY：
mysql> SELECT * FROM userdb.user 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.user WHERE uid<5
-> INTO OUTFILE ‘/myload/user1.txt‘
-> FIELDS TERMINATED BY ‘:‘;
]# wc -l /myload/user1.txt
5 /myload/user1.txt
3.操作表记录
表记录的插入
表记录的更新
表记录的查询
表记录的删除
3.1 删除表的所有记录
mysql> DELETE FROM user;
mysql> SELECT * FROM user;
3.2 给t1插入三条数据
mysql> CREATE TABLE t1(
-> name varchar(10),
-> sex enum("boy","girl"),
-> age int(3)
-> );
mysql> INSERT t1 VALUES
-> (‘Jim‘,‘girl‘,24),
-> (‘Tom‘,‘boy‘,21),
-> (‘Lily‘,‘girl‘,20);
mysql> SELECT * FROM t1;
------ ------ ------
| name | sex | age |
------ ------ ------
| 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 sex=‘boy‘;
3.5 删除表记录时，可以限制条件，只删除符合条件的记录
mysql> DELETE FROM t1 WHERE age < 18;
3.6 删除表记录时，如果未限制条件，则会删除所有的表记录
mysql> DELETE FROM t1;
4.查询及匹配条件
mysql> SELECT * FROM stu_info;
------ ------ ------
| name | sex | age |
------ ------ ------
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
------ ------ ------
4.1 查询stu_info表一共有多少条记录
mysql> SELECT count(*) FROM stu_info;
----------
| count(*) |
----------
| 3 |
----------
4.2 计算stu_info表中各学员的平均年龄、最大年龄、最小年龄
mysql> SELECT avg(age),max(age),min(age) FROM stu_info;
---------- ---------- ----------
| avg(age) | max(age) | min(age) |
---------- ---------- ----------
| 21.6667 | 24 | 20 |
---------- ---------- ----------
4.3 计算stu_info表中男学员的个数
mysql> SELECT count(sex) FROM stu_info WHERE sex=‘boy‘;
------------
| count(sex) |
------------
| 1 |
------------
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 sex=‘girl‘;
4.9 列出stu_info表中年龄小于23岁的学员，或者女学员的记录
mysql> SELECT * FROM stu_info WHERE age < 23 OR sex=‘girl‘;
4.10 如果某个记录的姓名属于指定范围内的一个，则将其列出
mysql> SELECT * FROM stu_info WHERE name IN
-> (‘Jim‘,‘Tom‘,‘Mickey‘,‘Minnie‘);
4.11 计算1234与5678的和
mysql> SELECT 1234 5678;
-----------
| 1234 5678 |
-----------
| 6912 |
-----------
4.12 输出stu_info表各学员的姓名、15年后的年龄
mysql> SELECT name,age 15 FROM stu_info;
------ --------
| name | age 15 |
------ --------
| Jim | 39 |
| Tom | 36 |
| Lily | 35 |
------ --------
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> SHOW SESSION VARIABLES;
mysql> SHOW SESSION VARIABLES;
mysql> select @@sql_mode;
-------------------------------------------------------------------------------------------------------------------------------------------
| @@sql_mode |
-------------------------------------------------------------------------------------------------------------------------------------------
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
-------------------------------------------------------------------------------------------------------------------------------------------
说明：如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测到功能依赖项，only_full_group_by在默认情况下是不启用的。
执行以下命令：
mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
mysql> set session sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
这两个命令，去掉 sql_mode 的 ONLY_FULL_GROUP_BY
现在执行
mysql> SELECT * FROM stu_info GROUP BY age;(ASC,由小到大,默认)
------ ------ ------
| name | sex | age |
------ ------ ------
| Lily | girl | 20 |
| Tom | boy | 21 |
| Jim | girl | 24 |
------ ------ ------
mysql> SELECT * FROM stu_info GROUP BY age DESC;(由大到小）
------ ------ ------
| name | sex | age |
------ ------ ------
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
------ ------ ------
4.18 限制查询结果的输出条数，LIMIT
mysql> SELECT * FROM stu_info LIMIT 2;
4.19 列出stu_info表中年龄最小的2条学员记录由小到大输出
mysql> SELECT * FROM stu_info GROUP BY age ASC LIMIT 2;
------ ------ ------
| name | sex | age |
------ ------ ------
| Lily | girl | 20 |
| Tom | boy | 21 |
------ ------ ------
4.20 分组查询结果，GROUP BY
针对stu_info表，按性别分组，分别统计出男、女学员的人数：
mysql> SELECT sex,count(sex) FROM stu_info GROUP BY sex;
------ ------------
| sex | count(sex) |
------ ------------
| boy | 1 |
| girl | 2 |
------ ------------
列出查询字段时，可以通过AS关键字来指定显示别名，比如上述操作可改为：
mysql> SELECT sex AS ‘性别‘,count(sex) AS ‘人数‘
-> FROM stu_info GROUP BY sex;
-------- --------
| 性别 | 人数 |
-------- --------
| boy | 1 |
| girl | 2 |
-------- --------