MySQL必须掌握4种语言！
俊红的数据分析之路
共 13437字，需浏览 27分钟
·
2022-06-27 16:59
本篇文章主要给大家介绍的是MySQL中常用的4种语言：
一、DDL
DDL，data defination language，指的是数据定义语言，其主要作用是创建数据库，对库表的结构进行删除和修改等操作。
进入数据库
mysql -uroot -p -- 使用这种方式，接下来需要输入密码。密码是暗文
mysql -uroot -p123456 -- 可以直接将密码123456放在参数p的后面，不安全参数解释：
u：指定用户
p：指定密码
全部命令
1. 数据库操作
show databases; // 显示所有的数据库
use school; // 使用school数据库
create database school; // 创建数据库
drop database school; // 删除某个数据库
2. 表操作
-- 创建表
create table user(字段1,字段2,...,字段n);
-- 查看创建表的SQL语句
show create table user;
-- 查看表的结构
desc user;
-- 删除表
drop table user;
-- 修改表名
alter table user rename to users;数据库操作
show databases; // 显示所有的数据库
use school; // 使用school数据库
create database school; // 创建数据库
drop database school; // 删除某个数据库
mysql> show databases; // 显示数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| peter |
| school |
| sys |
+--------------------+
6 rows in set (0.04 sec)
mysql> use school; // 选择使用数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A表操作
// 1、创建表
create table user(字段1,
字段2,
...,
字段n
); // 最后的分号不能忘记
// 2、查看所有的表
show tables;
// 3、查看表的结构
desc user;
// 4、查看创建表的SQL语句
show create table user;
// 5、删除表
drop table user;
// 6、修改表名
alter table user rename to users; # 表名改为users；to可省略最后的分号不能忘记😢
mysql> use school; // 使用一个数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; // 查看数据库下的所有表
+------------------+
| Tables_in_school |
+------------------+
| course |
| score |
| student |
| teacher |
| total |
+------------------+
5 rows in set (0.00 sec)创建表
主键 primary key和auto_increment必须连在一起使用书写规范：每个字段的语句最好分行写，容易检查 最后的分号不能忘记
# 创建user表：6种字段+1个主键
create table user( id int(10) unsigned not null auto_increment comment "user_id", //将id作为主键
name varchar(20) not null comment "user_name",
email varchar(50) not null comment "user_email",
age tinyint unsigned not null comment "user_age",
fee decimal(10,2) not null default 0.00 comment "user_fee",
createTime timestamp not null comment "user_time",
primary key(id)
); // 记得分号
查看表结构
mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| fee | decimal(10,2) | NO | | 0.00 | |
| createTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)查看创建表的SQL语句
show create table user;
字段操作
关键词是alter，先选中需要操作的表。
modify：修改change：改变名字add：添加字段 默认是末尾 指定位置添加
// 修改字段信息
alter table user modify name varchar(50) not null; # 将字段name 从20改为50个字符
// 修改字段名字
alter table user change email user_email varchar(50) not null; # 将email改成user_email
// 末尾添加字段
alter table user add password char(30) not null comment "user_password"; # 增加password字段
// 指定位置添加字段
alter table user add password1 char(30) not null comment "user_password1" after user_name; # 在name后面增加password1字段
// 删除字段
alter table user drop password1; #删除字段password1
// 原来的表格信息
mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| fee | decimal(10,2) | NO | | 0.00 | |
| createTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
# 修改字段信息
mysql> alter table user modify name varchar(50);
# 修改字段名字
mysql> alter table user change email user_email varchar(50) not null;
# 添加字段，末尾
mysql> alter table user add password char(30) not null comment "user_password";
# 指定位置添加字段
mysql> alter table user add password1 char(30) not null comment "user_password1" after name;
mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| password1 | char(30) | NO | | NULL | |
| user_email | varchar(50) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| fee | decimal(10,2) | NO | | 0.00 | |
| createTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| password | char(30) | NO | | NULL | |
+------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)二、DML
DML，data manipulation language，指的是数据操作语言。主要是对数据库中的表记录进行操作的语言，包含往表中插入数据、表中数据的更新、表的删除等
表中插入数据 表中数据更新 删除表
表中插入数据
-- 将字段名和字段的值一一对应起来，可以只插入部分字段
-- 省略了id和createtime字段
mysql> insert into user(
name,
email,
age,
fee,
password)
values("xiaoming",
"123456@qq.com",
20,
56.56,
Password("xiaoming") // 密码这里要用函数Password()
);
-- 包含所有字段信息
insert into user values(10, "nanan", "78173828@qq.com", 38, 89.19, 2019-10-02, Password("nanan"));字符串字段必须用引号括起来 密码需要使用函数 Password()语句末尾加分号 利用只插入部分字段 可以省去字段名，此时需要加上 id，而且必须填写所有的字段信息，不能只添加部分数据
数据更新
数据更新update使用最多的是where语句，指定某个条件下执行；如果不加where，则所有的字段都会被更改（慎重）
指定 id号指定字段的具体值 字段允许有多个，用逗号隔开
mysql> update user set name="nangying" where id=6; // 通过id指定
mysql> update user set fee=88.76 where fee=56.90; // 通过字段名直接指定
mysql> update user set email="81847919@qq.com", age=54 where id=7; // 同时修改多个值
mysql> update user set fee=88.88 where id in(2,4,6); // in的用法
mysql> update user set fee=66.66 where id between 2 and 6; // between ... and ...删除
删除表有两种情况：
delete：删除表，插入数据从上一次结束的id号开始继续插入；删除的记录仍存在truncate：清空表，重新插入数据id从1开始；不占内存空间
delete table user;
truncate table user;删除 delete表中的某条记录
delete from user where id=7; // 删除记录
insert into user (name,email,age,fee,password) values("lisi","9837194@qq.com", 36, 81.17, Password("lisi")); // id是从原来的基础上递增
关于truncate
# 删除数据
mysql> truncate table user;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from user;
Empty set (0.00 sec)
# 重新插入数据
mysql> insert into user (user_name, user_email, user_age, password, fee) values ("peter", "123456a@163.com", 27, password("101010"), 28.87);
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> select * from user;
+----+-----------+-----------------+----------+--------------------------------+-------+
| id | user_name | user_email | user_age | password | fee |
+----+-----------+-----------------+----------+--------------------------------+-------+
| 1 | peter | 123456a@163.com | 27 | *C3BC3E91915DCAE22014892F9827D | 28.87 |
+----+-----------+-----------------+----------+--------------------------------+-------+
1 row in set (0.00 sec)三、DCL
data control language，DCL，指的是数据控制语言，主要是对数据库中的登录和用户的权限进行控制的语言，包含
用户登录 MySQL数据库修改用户密码及忘记密码如何解决 创建普通用户及授权 撤销权限 revoke查看权限及删除用户
全部命令
1. 查看数据库中的用户及信息
mysql -uroot -p
show databases;
use mysql;
show tables;
select user, host, passord from user; # 所有的用户都在user表中
2. 创建新用户、授权、撤销权限和删除
-- 创建
create user "test"@"192.168.2.10" identified by"password"; # 指定用户test、ip和密码password
flush privileges; # 刷新权限
mysql -utest -h192.168.2.10 -p # 用test用户登录
-- 授权
grant select, insert, delete on shop.* to "test"@"192.168.2.10"; # shop 是数据库，test是数据库中的表
flush privileges; # 刷新权限
systemctl restart mysql; # 重启mysql
-- 创建用户的同时进行授权
grant select, insert, delete on shop.* to "test"@"192.168.2.10" identified by"password";
-- 查看权限
show grants for "test"@"192.168.2.10"\G # \G参数是为了输出好看
-- 撤销权限
revoke delete on shop.* to "test"@"192.168.2.10"; # 撤销shop数据库中test用户的delete权限
flush privileges; # 刷新权限
systemctl restart mysql; # 重启mysql
-- 删除用户
drop user "test"@"192.168.2.10";
-- 谨慎操作
grant all privileges on *.* to "test"@"192.168.2.10" # 将所有的权限给所有数据库
3. 修改用户密码
-- 已知用户原密码，能够进行登录
mysql -uroot -p
show databases;
use mysql;
show tables;
select user, host, password from user; # 所有的用户都在user表中
update user set password=PASSWORD("123456admin") where user="test"; # 将test用户的密码改成123456admin
flush privileges;
-- 忘记原来的密码：借助跳跃权限表，重启守护进程
mysql skip-grant-tables # 跳跃权限表
mysql # 重新进入mysql
show databases;use mysql;show tables;
select user, host, password from user; # 所有的用户都在user表中
update user set password=PASSWORD("123456admin") where user="test"; # 将test用户的密码改成123456admin
flush privileges;
4. 查看mysql服务
-- window
直接去任务管理器
-- linux
netstat -an # 找到3306端口关于root账户
默认情况下，MySQL数据库是指允许root账户登录并且在本机上登录的。
-uroot表示root账户-p表示需要密码没有 -h表示默认是本机localhost或者127.0.0.1登录
登录查看账户
MySQL数据库的服务端口号是3306，通过在mysql数据库的user表中查看登录数据库用户信息：
mysql> show databases; # 查看所有的数据库
mysql> use mysql; # 选择mysql数据库
mysql> show tables; # 查表数据库中的所有表
mysql> select user, host from user; # 查看这个表中的user和host信息
删除用户
需要注意的是删除了某个用户之后必须进行权限的刷新：
mysql> delete from user where host="%"; # 删除host为%的用户
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.00 sec)注意：当在实际的开发项目中，项目和数据库服务器不在同一个地方，可以指定ip连接进行访问
mysql> update user set host="192.168.1.10" where user="root";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user,host from user;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
当退出mysql重新进入，需要指定IP地址，就是上面设置的IP：
密码问题
1、修改密码
同样需要进入mysql数据库的user表中
mysql>update user set password=PASSWORD("admin") where user="root"; # 将root账户的密码改为admin
mysql> flush privileges; # 更新操作
Query OK, 0 rows affected (0.00 sec)2、忘记密码
如果忘记了密码，需要进入配置文件中
ubuntu@peter:~$ vim /etc/mysql/mysql.conf.d/mysqld.cnf找到下图中的
skip-grant-tables，将前面的#去掉，就是取消注释：取消权限认证，后台开启新的进程免密进入MySQL
重启
MySQL服务：systemctl restart mysql通过
MySQL直接进入：
然后按照上面的步骤重新设置密码即可
mysql> show databases;
mysql> use mysql;
mysql> select user, password from user;
# 接下来重新设置密码即可
四、DQL
DQL（data query language），指的是数据查询语言，主要的作用是对数据库中的数据进行查询的操作，也是最常见和最重要的功能。
查询的方法也是多种多样：联合查询、分组查询、内连查询、子查询等，还可以限制查询的条数等，下面介绍几种常见的查询
格式：
select
column1,
column2,... # 需要查询的字段
from table_name # 表名
where 条件简单查询
select
name,
age
from user
where id=4;
where id in(1,3,5,7);
where name = "xiaoming";过滤查询
过滤查询的关键字是distinct，去掉字段中的重复值
-- 过滤重复字段
select distinct(password) from user; # password是重复项
select distinct password from user; # 括号可以不用
连接查询
连接查询的关键字是concat
直接使用系统默认的连接方式，将原来的字段通过下划线进行连接 使用 concat...as...，as后面自己指定连接的新字段名带上连接符号的查询 concat_ws("+", 列名1，列名2)；其中"+"就是指定连接符
select concat(name, email) from user; # 结果中显示concat(name_email)
select concat(name, email) as nameEmail from user; # 将新的字段名用nameEmail来表示
模糊查询
模糊查询的关键字是like，中文翻译成像：
mysql> select user_name from student where user_name like "peter"; # 像peter
mysql> select user_name from student where user_name like "%e"; # %表示任意，表示名字以e结尾
mysql> select user_name from student where user_name like "%e%"; # 表示名字中含有e排序查询
对表中的记录进行升序asc或者降序desc的排列，默认的是升序asc，同时需要使用order by关键字：
升序： asc，默认情况降序： desc
select * from student order by user_age asc; # 年龄的升序
select * from student order by user_age desc; # 年龄的降序
聚合函数
select count(*) from student; # 总记录
select sum(列名) from student; # 总和
select avg(列名) from student; # 平均值
select max/min(列名) from student; # 最大/小值限制查询结果
限制查询的条数使用的是limit关键字
直接使用 limit使用 limit ... offset ...：指定从哪里开始显示，显示多少行简写： limit 5, 4：表示从第5行开始，显示4行数据
select name, age from user limit 5; -- 只显示5行数据
select name, age from user limit 5 offset 4; -- 从第4（offset）行开始显示5(limit)行数据
select name, age from user limit 4, 5 ; -- 效果同上：逗号之前是offset的内容，逗号之后是limit内容- END -
评论