MySQL必须掌握4种语言!

x33g5p2x  于2022-06-27 转载在 Mysql  
字(11.0k)|赞(0)|评价(0)|浏览(316)

本篇文章主要给大家介绍的是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 -

相关文章