MySQL入门实战笔记

2022-05-20 20:07:29

1. MySQL安装与配置

MySQL是一个关系型数据库管理系统，本步骤指导您如何在服务器上安装MySQL。

以CentOS为例：

  1. 执行如下命令，更新YUM源。 
rpm -Uvh  http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

2. 执行如下命令，安装MySQL。

yum -y install mysql-community-server --nogpgcheck

3. 执行如下命令，查看MySQL版本号。

mysql -V

返回结果如下，表示您已成功安装MySQL。

MySQL入门实战笔记


4. 执行如下命令，启动MySQL服务。

systemctl start mysqld

5. 执行如下命令，设置MySQL服务开机自启动。

systemctl enable mysqld

6. 配置MySQL的root用户密码。


1）执行如下命令，查看/var/log/mysqld.log文件，获取root用户的初始密码。

grep 'temporary password' /var/log/mysqld.log

返回结果如下所示，您可以查看到root用户的初始密码。

 MySQL入门实战笔记


2）执行以下命令，使用root用户登录数据库。

mysql -uroot -p

返回结果如下所示，输入root用户的初始密码。（输入的密码是不会显示出来的）

MySQL入门实战笔记

3）执行以下命令，修改MySQL的root用户的初始密码。

set global validate_password_policy=0;  #修改密码安全策略为低（只校验密码长度，至少8位）。
ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';


4）执行以下命令，授予root用户远程管理权限。 

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '12345678';


  1. 输入exit退出数据库。

 MySQL入门实战笔记


  1. 执行以下命令，使用root用户登录数据库。

mysql -uroot -p12345678
  1. 执行如下命令，创建test数据库。

create database test;
  1. 执行如下命令，查看当前数据库列表。此时，可以看到三个数据库：information_schema ，mysql，performance_schema，sys，test 。

show databases;

注意：进入 mysql 之后，每条命令结尾都要有分号。

MySQL入门实战笔记

11. 执行命令 use mysql;，连接 mysql 数据库。然后执行命令 show tables; ，查看 mysql 数据库中的数据表。使用命令 exit ，退出 MySQL 界面。

use mysql;

show tables;

exit

 

MySQL入门实战笔记

12. 返回 Linux 界面，执行如下命令，将名为 test 的数据库备份到当前目录的 test.sql。界面显示 Enter password，输入 MySQL 数据库的登录密码 12345678 。根据备份的数据库大小，等待时间长短不同。完成后，使用命令 ll 查看备份文件，界面查看到备份文件 test.sql ，完成备份。

mysqldump -uroot -p test >test.sql

ll

 

MySQL入门实战笔记

13. 返回 Linux 界面，执行如下命令，将 test.sql 导入数据库。界面显示 Enter password ，输入 MySQL 数据库的登录密码 12345678 。

说明：其中参数 -D 是指定数据库为test。

mysql -uroot -p -Dtest<test.sql

MySQL入门实战笔记


14. 还有一种导入方法：输入命令：mysql -uroot -p12345678 ，进入 MySQL 数据库。输入 use test; ，连接数据库 test ，输入 source /root/test.sql; ，将 test.sql 导入数据库 test ，全部出现 Query OK ，则表示数据库导入成功。

mysql -uroot -p12345678

use test;

source /root/test.sql;

 

MySQL入门实战笔记


2. SQL基础操作

1. 数据库表操作

本小节主要介绍对数据库表的基本操作，其中包括新增、删除、更新和重命名等。


1.使用CREATE TABLE语句创建一个数据表。

CREATE TABLE `USER` (   `user_id` int(128) NOT NULL AUTO_INCREMENT,   `name` varchar(64) NOT NULL,   `age` int(11) DEFAULT NULL,   PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

语句解析：

  • 1.
  • CREATE TABLE `USER`：新表的名字，表名称在关键字CREATE TABLE后给出。
  • `name` varchar(64) NOT NULL,：列名name，类型varchar，长度限制64，此列不能为空。
  • PRIMARY KEY (`user_id`)：设置USER表主键为user_id。
  • `user_id` int(128) NOT NULL AUTO_INCREMENT,：AUTO_INCREMENT通常用于主键，表示主键自增，数值会自动+1。

2. 使用ALTER TABLE语句更新数据表。

在年龄age列后面，为USER表新增一列性别sex，0代表女，1代表男。

ALTER TABLE user ADD COLUMN sex tinyint(1) NOT NULL COMMENT 'sex,woman:0,man:1' AFTER `age`;


3. 使用RENAME TABLE语句对数据表重命名。

修改表名user为student。

RENAME TABLE USER TO student;

语句格式：

RENAME TABLE table_name_a TO table_name_b;


4. 使用DROP TABLE语句删除数据表。

说明：此操作不能撤销，请谨慎操作。

删除学生表。

DROP TABLE student;

语句格式：

DROP TABLE table_name;


2. 数据操作

本小节主要为大家介绍MySQL中常用的数据查询、删除、更新、插入等基本操作。


1. 使用CREATE TABLE语句创建一个数据表。

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',  `name` varchar(32) DEFAULT NULL COMMENT 'name',  `age` int(11) DEFAULT NULL COMMENT 'age',  `address` varchar(32) DEFAULT NULL COMMENT 'address',  `sex` tinyint(1) DEFAULT NULL COMMENT 'sex,woman:0,man:1',  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8


2. 插入数据

插入语句说明：

  • table_name(col_name,...)中的字段名列表必须和values(expr,...)字段值列表一致。
  • 如果数据是字符型，必须使用单引号或者双引号。
  • 被AUTO_INCREMENT标记的自动递增的主键，在插入数据时，可以不设置值。


INSERT INTO USER        (name, age, address, sex) VALUES        ('zhangsan', 21, 'jiangxi', 0),        ('lisi', 22, 'hubei', 0),        ('wangwu', 23, 'hunan', 0),        ('lilei', 24, 'henan', 1),        ('hanmeimei', 25, 'hebei', 1),        ('xiaoming', 26, 'shandong', 1),        ('xiaoli', 27, 'shanxi', 1) ;


3. 查询数据

查询语句格式：

SELECT col_name,...   FROM table_name  WHERE where_condition  GROUP BY col_name,... HAVING where_condition  ORDER BY col_name,...  LIMIT offset,row_count

SELECT id,name,age,address,sex FROM user;


4. 更新数据

更新语句格式：

UPDATE table_name SET col_name=expr,... WHERE where_condition

。

UPDATE `user` SET `age` = 28,`address` = 'sichuan' WHERE `name` = 'xiaoming' ;

5. 删除数据


删除语句格式：

DELETE FROM table_name WHERE where_condition

。

DELETE FROM `user` WHERE `name` = 'xiaoming' ;

 

3. 函数操作

本小节主要为大家介绍MySQL中常用的内置函数。


1. 使用CREATE TABLE语句创建一个数据表。  

CREATE TABLE student_score(sid INT PRIMARY KEY NOT NULL, sname VARCHAR(30), sage INT, ssex VARCHAR(8), score INT(11)); insert into `student_score`(`sid`, `sname`, `sage`, `ssex`,`score`) values (1001, 'xiaohua', 17,'0', 75),  (1002, 'xiaohong', 18,'0', 80),  (1003, 'wangwu', 18,'1', 90),  (1004, 'lisi', 17,'1', 68),  (1005, 'zhangsan', 19,'1', 73),  (1006, 'xiaohei', 19,'1', 100),  (1007, 'xiaoma', 20,'0', 77),  (1008, 'xiaoli', 17,'1', 82),  (1009, 'xiaobai', 19,'0', 88),  (1010, 'wentong', 18,'0', 53);


2. AVG()函数

SELECT avg(score) FROM student_score;

执行结果如下：

MySQL入门实战笔记 


3. COUNT()函数  

SELECT COUNT(*) FROM student_score;

执行结果如下：

 MySQL入门实战笔记

4. MAX()函数  

SELECT MAX(score) FROM student_score;

执行结果如下：

 MySQL入门实战笔记

5. MIN()函数  

SELECT MIN(score) FROM student_score;

执行结果如下：

 MySQL入门实战笔记

MySQL入门实战笔记

6. SUM()函数

SELECT SUM(score) FROM student_score;

执行结果如下：

MySQL入门实战笔记 


 

4. 组合查询

本小节主要介绍如何使用GROUP BY、HAVING和ORDER BY等进行分组查询。


1. 使用CREATE TABLE语句创建数据表。  

CREATE TABLE `student` (  `sid` int(11) NOT NULL,  `sname` varchar(30) DEFAULT NULL,  `sage` int(11) DEFAULT NULL,  `ssex` varchar(8) DEFAULT NULL,  PRIMARY KEY (`sid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `student`(`sid`,`sname`,`sage`,`ssex`) values (1005,'xiaohua',19,'0'), (1004,'xiaohong',18,'0'), (1003,'wangwu',18,'1'), (1002,'lisi',17,'1'), (1001,'zhangsan',18,'1'); CREATE TABLE `sc` (  `sid` int(11) NOT NULL,  `cid` int(11) NOT NULL,  `score` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `sc`(`sid`,`cid`,`score`) values (1001,101,75), (1001,102,85), (1002,101,65), (1002,102,95), (1003,101,65), (1003,102,95), (1004,101,80), (1004,102,80), (1005,101,75), (1005,102,85); CREATE TABLE `course` (  `cid` int(11) NOT NULL,  `cname` varchar(30) DEFAULT NULL,  `tid` int(11) DEFAULT NULL,  PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `course`(`cid`,`cname`,`tid`) values (102,'yuwen',2), (101,'shuxue',1); CREATE TABLE `teacher` (  `tid` int(11) NOT NULL,  `tname` varchar(30) DEFAULT NULL,  PRIMARY KEY (`tid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `teacher`(`tid`,`tname`) values (2,'liulaoshi'), (1,'wanglaoshi');


2. 查询所有同学的学号和成绩。

# 使用WHERE s. sid = sc.sid 来消除笛卡尔积。 SELECT s.sid ,s.sname ,c.score FROM student AS s ,sc AS c WHERE s.sid = c.sid ;


3. 查询语文成绩在80以上同学。  

SELECT a.score,        b.cname,        s.sname   FROM sc as a,        course AS b,        student AS s  WHERE a.cid= b.cid    AND s.sid= a.sid    AND a.cid= 102    AND a.score> 80


4. 查询语文成绩比数学成绩高的同学。  

SELECT a.sid,        a.score,        s.sname   FROM( SELECT sid, score   FROM sc  WHERE cid= "102") a,        ( SELECT sid, score   FROM sc  WHERE cid= "101") b,        student as s  WHERE a.score > b.score    AND a.sid= b.sid    AND s.sid= a.sid


3. SQL问题诊断与调优

MySQL常见的表创建、索引创建等操作。


1、创建数据库表。

创建数据库test_tb。

CREATE TABLE IF NOT EXISTS `test_tb`(

  `id` INT UNSIGNED AUTO_INCREMENT,

  `name` VARCHAR(100) NOT NULL,

  `address` VARCHAR(40) NOT NULL,

  `birthday` DATE,

  PRIMARY KEY ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;


2、输入如下SQL语句，查看SQL执行计划。

explain select * from test_tb where name='abc';

返回如下如下结果，你可以发现key和key_len的值都是null。

MySQL入门实战笔记


3、输入如下SQL语句，查看test_tb表的表结构。

show create table test_tb;

返回如下结果，您可以查看到test_tb表的表结构。

MySQL入门实战笔记

4、创建test_tb表的索引。

alter table test_tb add index(name);

返回如下结果，表示test_tb表的索引创建成功。

MySQL入门实战笔记


5、重复第5步和第6步的操作，再次查看完整的test_tb表的表结构，您会发现表结构中多了一个KEY `name` (`name`)。

MySQL入门实战笔记

6、在SQLConsole页签中，输入如下SQL语句，查看执行SQL计划，单击执行。

explain select * from test_tb where name='abc';

返回如下如下结果，你会发现key和key_len的值发生了改变。

MySQL入门实战笔记


7、输入如下SQL语句，查看数据库运行中的SQL。

select * from `information_schema`.`PROCESSLIST`

返回如下结果，您可以查看到数据库运行中的SQL。

MySQL入门实战笔记

上一篇：MySQL入门实战3：SQL问题诊断与调优


下一篇：基于 Mesh 的统一路由在海外业务的实践