MySQL必须掌握4种语言!
共 13437字,需浏览 27分钟
·
2022-06-27 16:59
本篇文章主要给大家介绍的是MySQL中常用的4种语言:
![](https://filescdn.proginn.com/d1d1b4d64435aa6cdf188995c0d15e9b/1f44665dad93f5b6a00c175b0bb61905.webp)
一、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)
); // 记得分号
![](https://filescdn.proginn.com/cd8ac10604971c5b2586729fd4c9edfe/79577096c7847f8d7024d8f201454743.webp)
查看表结构
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;
![](https://filescdn.proginn.com/7c290708a423d78cafb485fa52a8eb43/255db5fefbbcfe70e693fb085c40b782.webp)
字段操作
关键词是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
,而且必须填写所有的字段信息,不能只添加部分数据
![](https://filescdn.proginn.com/cbf7c3ccbc836d1c83337e28e2b29620/c322b74236bda1efa82c67250ba00c4f.webp)
数据更新
数据更新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是从原来的基础上递增
![](https://filescdn.proginn.com/b54c0ea9e3d0ea628123007ccb09b58c/df30c361c1abaec32fc0b500604667d3.webp)
![](https://filescdn.proginn.com/f42ef60190511448030053582a6c5e23/670729c6a661d6419ac1d7ae739e338d.webp)
关于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
登录
![](https://filescdn.proginn.com/0eea6623c949f63eae535612f13fb5c1/603b41d86843e57a62c2788dfd2b0093.webp)
登录查看账户
MySQL
数据库的服务端口号是3306
,通过在mysql
数据库的user
表中查看登录数据库用户信息:
mysql> show databases; # 查看所有的数据库
mysql> use mysql; # 选择mysql数据库
mysql> show tables; # 查表数据库中的所有表
mysql> select user, host from user; # 查看这个表中的user和host信息
![](https://filescdn.proginn.com/ed5cf03887bd42da30c0c821af3ebc42/6f33dd5023db1fbf186ce59f9d29a402.webp)
删除用户
需要注意的是删除了某个用户之后必须进行权限的刷新:
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)
![](https://filescdn.proginn.com/49e64efc1f4f391dddab985fe8c62bd0/17bc63fb6f954c259ca7f8da66b9693a.webp)
当退出mysql
重新进入,需要指定IP
地址,就是上面设置的IP
:
![](https://filescdn.proginn.com/fff16f1df9d5590a79c80adeb9748d3b/8f161e9807509ab091159b81d8df0290.webp)
密码问题
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
直接进入:
![](https://filescdn.proginn.com/2ce066213b857ecb5d8835d635ed056d/254ccb787cf4f70d6fcc297f9b2739c1.webp)
然后按照上面的步骤重新设置密码即可
mysql> show databases;
mysql> use mysql;
mysql> select user, password from user;
# 接下来重新设置密码即可
![](https://filescdn.proginn.com/3ea8c6095ec4af98420fb163375d60be/0cfdbc68f58f0663490ac3f39705ac60.webp)
四、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; # 括号可以不用
![](https://filescdn.proginn.com/ae8f1aaae5989f99f8a6330e46cac018/f04eefab13a58df2c62478685e7538a8.webp)
连接查询
连接查询的关键字是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来表示
![](https://filescdn.proginn.com/6f5c68f998684921891d51deb00c0068/cef281ab0481cd506c86885db9cbb786.webp)
![](https://filescdn.proginn.com/beafddf2c41a8419f19621a6c47b89a3/9160c5bf99ef53d48d709b269bdcf1f6.webp)
![](https://filescdn.proginn.com/c146f606c9098776f45ee366534872be/1b9a7ff5a9e911b966daab113a4a864c.webp)
模糊查询
模糊查询的关键字是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; # 年龄的降序
![](https://filescdn.proginn.com/e83b93ad32c38bad71d7b17429cea591/a0caf7798ad98426892ac0b8a2c57c3a.webp)
聚合函数
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 -