《mysql基础教程》笔记

安装 mysql

  • 以 windows 环境为例

第 1 步，下载并解压 MySQL

  • 官网下载，然后解压到对应目录

第 2 步，设置配置文件

  • 新建配置文件my.ini，放置在根目录，内容如下
      [mysqld]
  # 设置3306端口
  port=3306
  # 设置mysql的安装目录
  basedir=G:\WWW\mysql
  # 设置mysql数据库的数据的存放目录
  datadir=G:\WWW\mysql\data
  # 允许最大连接数
  max_connections=200
  #允许连接失败的次数。防止有人从该主机试图攻击数据库系统
  max_connect_errors=10
  # 服务端使用的字符集默认为UTF8
  character-set-server=utf8
  # 创建新表时将使用的默认存储引擎
  default-storage-engine=INNODB
  # 默认使用“mysql_native_password”插件认证
  default_authentication_plugin=mysql_native_password
  [mysql]
  # 设置mysql客户端默认字符集
  default-character-set=utf8
  [client]
  # 设置mysql客户端连接服务端时默认使用的端口
  port=3306
  default-character-set=utf8

第 3 步，初始化 MySQL 环境

  • 以管理员身份运行 cmd，进入 bin 目录，输入mysqld --initialize --console

  • 成功后，跟在 root@localhost:后面的为初始化密码，需要记录一下

第 4 步，安装 MySQL 服务

  • 继续以 bin 目录为基础，键入mysqld install

第 5 步，关闭和启动 MySQL

 命令 : net start mysql # 启动命令

 命令 : net stop mysql # 关闭命令

连接 mysql

  • 以管理员身份，进入 mysql 包的 bin 目录

  • 如果未曾启动 mysql，则需先进行启动

命令行连接

  • 输入如下命令

    mysql -uroot -p -P3306 -hlocalhost
    • "-u" 表示登录用户名
    • "-p" 表示密码单独输入
    • "-P" 表示端口号
    • "-h" 表示主机地址

  • 修改初始密码

    • 第一次用初始密码登录成功后须修改初始密码，如果不修改初始密码则会提示错误信息。

    • 修改初始密码命令如下：

      ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

客户端工具连接

  • Windows 环境下使用的连接工具是 Navicat12，连接方式如下：

  • 如上图填写好相应的输入栏，可以点击"连接测试"，出现如下图则表示可以连接成功，选择“确定”即可连接 MySQL:

nodejs 连接

  var mysql=require('mysql');
  var connection=mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'mysql'
  });

  connection.connect();

  connection.query('select 1 + 1 as solution', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results[0].solution);
  });

删除 mysql

Windows 下使用的是源码安装方式，卸载时先执行如下命令，然后删除源码包文件夹即可

 命令 : cd /d d:\WWW\mysql\bin

 命令 : mysqld remove

多实例配置

端口号概念

  • 客户端通过ip 地址找到对应的服务器端，通过端口号找到对应的应用程序。

多实例应用场景

  • 企业级开发可能面临多个项目需要不同 mysql 版本的情况。

  • MySQL 默认端口号为 3306，因此不同实例的 MySQL 之间会有端口号冲突的情况。

安装多实例步骤

  • 第一步，复制解压包，重命名包

  • 第二步，新建 my.ini，修改端口号

  • 第三步，以管理员身份 cmd 到 bin 目录，执行初始化命令

    mysqld --initialize --console # 记住初始密码

  • 第四步，安装 MySQL 服务

    # 因为之前安装MySQL已经加载过MySQL服务，为了防止冲突，需要区分，添加实例名进行安装
mysqld install mysql3308

  • 第五步，启动 mysql 服务

    net start mysql3308 # 书写实例名

  • 第六步，登录 MySQL

    mysql -uroot -p -P3308

  • 3309 实例包同上操作

创建子账号

  • 同样是以管理员身份 cmd 到 bin 目录下，先以 root 账号登陆

    mysql -uroot -p -P3306 -hlocalhost

  • 新建一个 MySQL 子账号

    CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

  • root 账号退出登录

    quit;

  • 使用子账号登录 MySQL

    mysql -utest -p -P3306 -hlocalhost

创建数据库

命令行操作

  • 进入 bin 目录，登入数据库连接

    mysql -uroot -p

  • 新建数据库

    # 数据库命名一般以小写英文字母和下划线组成
create database item_name;

  • 第一步 右键 sql 连接

  • 第二步 新建数据库

  • 第三步 字符集选择——数据库名自定义，字符集选 utf8，排序规则选 utf8_general_ci

  • 第四步 点击确定，新建完成

查看数据库

show databases;

选中数据库

use item_name;

接下来的操作都会在 item_name 这一数据库中执行，除非你使用 use 命令重新切换到了另一个数据库

删除数据库

drop database item_name;

设计表

  • 第一范式，表中字段要做到不可再分，一个字段只存储一项具体信息。

  • 第二范式，表中必须存在业务主键，并且全部非主键依赖于业务主键。

  • 第三范式，一个表中应该不包含已在其它表中已包含的非主键字段，一张表最多只存两层同类型信息。

  • 反范式设计，降低范式就是增加字段，减少查询时的关联，适当反范式化设计可以提升查询效率和工作效率。

查看表

show create table table_name;

新建表

create table `student`  (
  `id` int(10) unsigned not null auto_increment,
  `name` varchar(50) not null default '无名',
  `age` int(10) unsigned not null default 0,
  `id_number` varchar(18) not null default '',
  primary key (`id`)
);

  • 其中 student 为表名称，id、name、age、id_number 为字段名称，跟在字段名称后面的是字段的数据类型

  • unsigned 表示无符号

  • auto_increment 表示自增

  • primary key (id) 表示设置 id 为业务主键

  • not null 表示不为空 null

  • default '无名' 表示默认值为无名

修改表

修改表名称

alter table
  table_name1
rename to
  table_name2;

新增表字段

# 新增性别字段
alter table
  `new_student`
add column
  `sex` tinyint(2)
unsigned
not null
default
  1
comment
  '性别 : 1:男 2:女'
after
  `id_number`;
  • tinyint 小整数整型
  • unsigned 表示无符号
  • not null 表示不为 null
  • default 表示默认值
  • comment 表示注释说明
  • after 表示在 id_number 字段之后

删除表字段

# 删除性别字段
alter table
  `new_student`
drop column
  `sex`;

修改表字段

  • 修改字段名称

    # 将name字段改为new_name
alter table
  `new_student`
change column
  `name` `new_name` char(50)
character set
  utf8
collate
  utf8_general_ci
not null
default
  '无名'
after
  `id`;

  • 修改字段默认值

    # 修改new_name字段默认值
alter table
  `new_student`
modify column
  `new_name` char(50)
character set
  utf8
collate
  utf8_general_ci
not null
  default '小花'
after
  `id`;

  • 修改字段数据类型

    # varchar类型修改为char类型
alter table
  `new_student`
modify column
  `name` char(50)
character set
  utf8
collate
  utf8_general_ci
not null
default
  '无名'
after
  `id`;

  • 关键字语法说明

    • character set 表示字符集设置
    • collate 表示 mysql 的列排序与比较
    • not null 表示不为 null
    • default 表示默认值
    • after 表示在 id 字段之后

删除表

drop table new_student;

清空表

# 内部过程（先删除数据表，再新建一张做替代，因此自增主键会重置）
truncate table teacher;

增

insert into
  teacher (name,age,id_number)
values
  ('秦小贤',18,'42011720200604088X');

删

删除一条

delete from
  teacher
where
  id=8;

删除全部

# 不会改变自增主键的增长值
delete from teacher;

改

改一列字段值

# teacher为数据表名，age=33为更改设置，where id=30为对应行所满足的条件
update
  teacher
set
  age=33
where
  id=30;

改多列字段值

update
  teacher
set
  age=18, id_number='44444444440604099X'
where
  id=30;

查

查所有数据

select * from teacher;

查指定条数结果集

select * from teacher limit 10;

查指定起始位置条数结果集

select * from teacher limit 10,10;

查询指定字段列结果集

select sex, age from teacher;

条件查询

条件类型

条件 说明 例子
< 小于 where age < 18
> 大于 where age > 18
< 小于 where age <= 18
< 小于 where age >= 18
<> 不等于 where age <> 18
= 等于 where name='小明'
like 满足模糊条件 where num like '776%'
not like 不满足模糊条件 where num not like '776%'
and 逻辑与 where age > 18 and name like '776%'
or 逻辑或 where age > 18 or name like '776%'
between and 在两个值之间(包含两端值) where age between 18 and 30
not between and 不在两个值之间(不包含两端值) where age not between 18 and 30
is null 为空(null 值) age is null
is not null 不为空(null 值) age is not null

like 表达式

  • %表示指代任意内容

  • 基本使用

    select
  *
from
  teacher
where
  name 
like 
  '王%'
字符 说明
'%值%' 表示包含'值'的表达式,且'值'前后都有内容
'%值' 表示以'值'结尾的表达式,'值'前面有内容
'值%' 表示以'值'开头的表达式,'值'后面有内容

联合查询

union all

# 把2个查询结果集并到一起（不去重）
select * from teacher where age > 20
union all
select * from teacher where age > 25;

union

# 把2个查询结果集并到一起（去重）
select * from teacher where age > 20
union
select * from teacher where age > 25;

连表查询

left join 左连接

# 以左边的表为基准，若右表没有对应的值，用null来填补
select
  *
from
  course
left join
  teacher
on
  course.teacher_id=teacher.id;

right join 右连接

# 以右边的表为基准，若左表没有对应的值，用null来填补
select
  *
from
  course
right join
  teacher
on
  course.teacher_id=teacher.id;

inner join 内连接

# 展示的是左右两表都有对应的数据
select
  c.id as course_id,c.*,t.*
from
  course c
inner join
  teacher t
on
  c.teacher_id=t.id;
  • c.id as course_id 表示将 id 字段重命名为 course_id 展示
  • course c 和 teacher t 为表的简写用法

多表混合连接

# 展示的是左右两表都有对应的数据
select * from
  student a
left join
  student_course b
on
  a.id=b.student_id
right join
  course c
on
  b.course_id=c.id
inner join
  teacher d
on
  c.teacher_id=d.id;

排序

从小到大

select
  *
from
  teacher
order by
  age
asc;

从大到小

select
  *
from
  teacher
order by
  age
desc;

多字段混合

# 先按照age从大到小排序，再按照id从小到大排序
select
  *
from
  teacher
order by
  age desc,
  id asc;

字符串类型字段排序

# 对字符串类型的排序，其实是对字符串编码的排序，排序规则是按照字符ASCII码对应值的大小来的
select
  *
from
  teacher
order by
  name
asc;

分组

单字段分组

# 需要查出course表中教师相关信息，按照teacher_id字段分组可去除重复数据
select
  teacher_id
from
  course
group by
  teacher_id;

多字段分组

# 按照teacher_id、course_id分组来展示所有教师的所有课程信息
select
  c.teacher_id, a.course_id, c.course_name, d.name
from
  student_course a
inner join
  student b
on
  a.student_id=b.id
inner join
  course c
on
  a.course_id=c.id
inner join
  teacher d
on
  c.teacher_id=d.id
group by
  c.teacher_id, a.course_id;

补充说明

  • 单字段分组是以一个字段来判断数据是否重复分组出来的结果

  • 多字段分组是以多个字段同时来判断是否重复分组出来的结果

  • 分组的目的主要是用来进行聚合统计的，比如使用 count(*)对分组重复的总数进行统计

    select
  teacher_id, count(*)
from
  course
group by
  teacher_id;

去重

单字段去重

# 若只需要学生信息，则需要对结果进行去重
select 
distinct 
  student_id 
from 
  student_course a 
inner join 
  student b 
on 
  a.student_id=b.id;

多字段去重

# 若需要得到所有教师的所有课程信息，则需要多个字段对结果去重
select 
distinct
  a.course_id, b.course_name, b.teacher_id, c.name
from 
  student_course a 
inner join 
  course b 
on 
  a.course_id=b.id 
inner join 
  teacher c 
on 
  b.teacher_id=c.id;
  • 多字段去重意思就是只有所有指定的列信息都相同，才会被认定为重复的信息。

聚合函数

AVG函数求平均值

select 
  avg(age) 
from 
  teacher;

SUM函数求总和

select 
  sum(age) 
from 
  teacher;

COUNT函数求总条数

# 还可以传字段或1
select 
  count(*) 
from 
  teacher;

MIN函数取最小值

select 
  min(age)
from 
  teacher;

MAX函数取最大值

select 
  max(age)
from 
  teacher;

GROUP BY HAVING

# 使用HAVING对分组结果进行筛选，选出选课学生平均年龄大于20的课程数据
select 
  a.course_id, c.course_name, avg(age) 
from 
  student_course a 
inner join 
  student b 
on 
  a.student_id=b.id 
inner join 
  course c 
on 
  a.course_id=c.id
group by 
  a.course_id, c.course_name;
having 
  avg(age) >= 20;

条件判断函数

IF函数

select 
  name, 
  if(age > 17,'成年','未成年') as age_group, 
  id_number 
from 
  student;

IFNULL函数

select 
  name, age, id_number,
  ifnull(email,'default@qq.com') as full_email 
from 
  teacher;

CASE条件判断

select 
*，
case name
  when 'Tom'  then '汤姆'
  when 'Jack' then '杰克'
  when 'Mary' then '玛丽'
  when 'Timo' then '提莫'
  when 'Bob'  then '鲍勃'
  when 'Judy' then '朱蒂'
else 
  '未定义' 
end as 
  'chinese_name'
from 
  teacher;

时间日期函数

NOW函数

# 获取当前日期和时间（yyyy-MM-dd HH:mm:ss）
select 
  now() 
as 
  date_time 
from 
  teacher;

CURDATE函数

# 获取当前日期，不包含时间（yyyy-MM-dd）
select 
  curdate() 
as 
  date_time 
from 
  teacher;

CURTIME函数

# 获取当前日期，不包含时间（HH:mm:ss）
select 
  curtime() 
as 
  date_time 
from 
  teacher;

UNIX_TIMESTAMP函数

# 将日期转化为UNIX时间戳
select 
  unix_timestamp() 
as 
  date_time 
from 
  teacher;

正则表达式

select 
  * 
from 
  student 
where 
  name 
regexp 
  '小';
posted @ 2020-04-21 09:45  戡玉  阅读(186)  评论(0)    收藏  举报