MySQL必学4大语言

皮大大

发布于 2023-08-25 10:42:56

2130

发布于 2023-08-25 10:42:56

文章被收录于专栏：机器学习/数据可视化机器学习/数据可视化

MySQL必学4种语言

本篇文章记录的是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内容

MySQL中的DQL应该是最常用的，请参考《MySQL经典50题》

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2022-6-7，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

mysql

权限

数据

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度