Mysql命令

1. 数据库操作

1.1 查询数据库

1
2
3
4
5
-- 查询所有数据库
show databases;

-- 查询当前所处数据库
select database();

1.2 创建数据库

1
2
3
4
5
6
7
8
-- 创建一个数据库dh_test
create database dh_test;

-- 创建一个数据库dh_test如果这个数据库存在则不报错
create database if not exists dh_test;

-- 创建一个数据库dh字符集设为utf-8
create database dh default charset utf8mb4;
1
2
-- 创建数据库语法，如果需要设置字符集的话
create database [if not exists] 数据库名 [default charset字符集] [collate排序语法];

1.3 删除数据库

1
2
3
4
5
-- 删除数据库dh
drop database dh;

-- 删除一个数据库dh如果这个数据库不存在则不报错
drop database if exists dh;
1
2
-- 删除数据库语法，删除一个数据库如果这个数据库不存在则不报错
drop database if exists 数据库名;

1.4 使用及切换数据库

1
2
-- 进入/切换 数据库到use dh_test;
use dh_test;
1
use 数据库名;

2. 表操作

2.1 查询表数据

1
2
3
4
5
6
7
8
-- 查询当前数据库所有表
show tables;

-- 查询表结构
desc 表名;

-- 查询指定表的建表语句(desc的强化版查看详细结构)
show create table 表名;

2.2 创建表

1
2
-- 案例案例：1.编号(纯数字)2.员工工号(字符串类型，长度不超过10位)3.员工姓名(字符串类型，长度不超过10位)4.性别(男/女,储存一个汉字)5.年龄(正常人年龄,不可能粗存负数)6.身份证号(二代身份证号均为18位，身份证中有X这样的字符)7.入职时间(取值年月日即可)
create table yuangong( id int comment "编号", workno varchar(10) comment "工号", name varchar(10) comment "姓名", gender char(1) comment "性别", age tinyint unsigned comment "年龄", idcard char(18) comment "身份证号", entrydate date comment "入职时间")comment "员工表";
1
create table 表名(字段1 字段1类型[comment 字段1注释]),字段2 字段2类型[comment 字段2注释],字段3 字段3类型[comment 字段3注释],,字段n 字段n类型[comment 字段n注释]

2.3 修改表数据

2.3.1 表中添加字段

1
2
-- 案例：在上面员工案例中添加一个字段nickname（类型为varchar（20））
alter table yuangong add nickname varchar(20) comment "昵称";
1
alter table 表名 add 字段名 类型(长度) [comment注释] [约束];alter table 表名 add 字段名 类型(长度) [comment注释] [约束];

2.3.2 表中修改字段

1
2
-- 案例：在上面员工案例中的nickname字段修改为username(类型为varchar(30))
alter table yuangong change nickname username varchar(30) comment "用户名";
1
alter 表名 change 旧字段名 新字段名 类型(长度) [comment注释] [约束];

2.3.3 表中删除字段

1
2
-- 案例：在上面员工案例中的username字段删除
alter table yuangong drop username;
1
alter table 表名 drop 字段名;

2.3.4 表中修改表名

1
2
-- 修改yuangong表名为emp
alter table yuangong rename to emp;
1
alter table 表名 rename to 新表名;

2.3.5 删除表

1
2
3
4
-- 删除表
drop table if exists 表名;
或者
truncate table 表名;

3. 数据操作

3.1 添加数据(insert)

1
2
3
4
5
6
7
8
-- 给指定字段添加数据给指定字段添加数据
insert into emp(id, workno, name, gender, age, idcard, entrydate) values (1,"1","itcast","男",10,"111111111122345678","2000-01-01");

-- 给全部字段添加数据
insert into emp values (2,"2","张无忌","男",18,"112222333455556678","2005-01-01");

-- 批量添加数据
insert into emp values (3,"3","韦一笑","男",38,"154645678154678945","2005-02-01"),(4,"4","赵敏","女",26,"154645673454678945","2005-03-01");
1
2
3
4
5
6
7
8
-- 给指定字段添加数据给指定字段添加数据
insert into 表名(字段名1,字段名2,......)values(值1,值2,......);

-- 给全部字段添加数据
insert into 表名 values (值1,值2,......);

-- 批量添加数据
insert into 表名 values (值1,值2,...), (值1,值2,...), (值1,值2,...);

3.2 修改数据(update)

1
2
3
4
5
6
7
8
9
10
11
12
-- 案例1：修改id为1的数据，将name修改为donghao
update emp set name = "donghao" where id = 1;

-- 案例2：修改id为1的数据，将name修改为小昭，年龄修改为88
update emp set name = "小昭",age="88" where id = 1;

-- 案例3：将所有员工入职日期修改为2008-01-01
update emp set entrydate = "2008-01-01";

-- 案例4：此时emp表中有个Opdate字段，数据中有多个2023-09-21,我想只将前面的2023修改为2024,后面的月份不变
Set SQL_SAFE_UPDATES = 0; -- 此命令为关闭mysql的安全模式 能够使不带where语法的sql语句正常运行，默认mysql不允许不带where的运行，防止误删数据
Update emp set Opdate = replace(Opdate,'2023','2024')
1
2
3
4
-- 修改数据
update 表名 set 字段名1=值1,字段名2=值2,...[where条件];

Update 表名 set 字段名 = replace(字段名,被替换的数据,要替换的数据)

3.3 删除数据(delete)

  • 当有外键约束无法删除时可执行以下sql指令关闭外键约束后，即可成功强制删除该数据
    SET FOREIGN_KEY_CHECKS=0;
1
2
3
4
5
6
7
8
-- 案例1：删除age为18的数据
delete from emp where age = "18";

-- 案例2：删除所有员工
delete from emp;

-- 案例3：删除所有员工，不保留上一次最新的id,再次新增数据时id从1开始
truncate emp;
1
2
3
delete from 表名 [where条件]delete from 表名 [where条件]

truncate 表名;

4. 数据库查询

4.1 插入表和数据(可练习使用)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 创建表
create table emp(
id int comment "编号",
workno varchar(10) comment "工号",
name varchar(10) comment "姓名",
gender char(1) comment "性别",
age tinyint unsigned comment "年龄",
idcard char(18) comment "身份证号",
workaddress varchar(50) comment "工作地址",
entrydate date comment "入职时间"
)comment "员工表";

-- 插入数据
insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)values (1,"1","柳岩","女",20,"123456719123456789","北京","2000-01-01"),
(2,"2","张无忌","男",18,"123456729123456789","北京","2005-09-01"),
(3,"3","韦一笑","男",38,"123456329123456789","上海","2005-08-01"),
(4,"4","赵敏","女",18,"123456789423456789","北京","2009-12-01"),
(5,"5","小昭","女",16,"123456789123459789","上海","2007-07-01"),
(6,"6","杨潇","男",28,"123456789123656789","北京","2006-01-01"),
(7,"7","范瑶","女",40,"123456789143456789","北京","2005-05-01"),
(8,"8","黛绮丝","女",38,"123356789123456789","天津","2015-05-01"),
(9,"9","范凉凉","女",45,"123456889123456789","北京","2010-04-01"),
(10,"10","陈友谅","男",53,"123456789163456789","上海","2011-01-01"),
(11,"11","张士诚","男",55,"123456789123466789","江苏","2015-05-01"),
(12,"12","常遇春","男",32,"123456789123756789","北京","2004-02-01"),
(13,"13","张三丰","男",88,"123456289123456789","江苏","2020-11-01"),
(14,"14","灭绝","女",65,"123856789123456789","西安","2019-05-01"),
(15,"15","胡青牛","男",70,"123456789523456789","西安","2018-04-01"),
(16,"16","周芷若","女",18,null,"北京","2012-06-01");

4.2 基本查询

4.2.1 查询多个字段

1
2
3
4
5
-- 例1：查询指定字段name,workno,age,返回
select name,workno,age from emp;

-- 例2：查询所有字段返回
select * from emp;
1
2
3
4
5
-- 查询多个字段
select 字段1,字段2,字段3,... from 表名;

-- 查询所有字段
select * from 表名;

4.2.2 设置别名

1
2
-- 例1：查询所有员工的工作地址，起别名mysql
select workaddress as "工作地址" from emp;
1
select 字段1 [AS 别名1],字段2[AS 别名2]...from 表名;select 字段1 [AS 别名1],字段2[AS 别名2]...from 表名;

4.2.3 去除重复记录

1
2
-- 例1：查询公司员工的上班地址(去重)
select distinct workaddress as "工作地址" from emp;
1
select distinct 字段列表 from 表名;

4.3 条件查询

4.3.1 比较运算符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- > 大于 
-- 例：查询年龄大于20的员工信息
select * from emp where age > 20;

-- >= 大于等于 
-- 例：查询年龄大于等于20的员工信息
select * from emp where age >= 20;

-- < 小于
-- 例：查询年龄小于20的员工信息
select * from emp where age < 20;

-- <= 小于等于
-- 例：查询年龄小于等于20的员工信息
select * from emp where age <= 20;

-- = 等于
-- 例：查询年龄等于88的员工
select * from emp where age = 88;

-- <>或!= 不等于
-- 例：查询员工年龄不等于88的员工信息
select * from emp where age != 88;
或者
select * from emp where age <> 88;

-- between...and... 在某个范围之内(含最小、最大值)
-- 例：查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;

-- in(...)  在in之后的列表中得到值，多选一
-- 例：查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age in(18,20,40);

-- like 占位符 模糊匹配(_匹配单个字符，一个下划线代表一个字符,%匹配任意个字符)
-- 例1：查询姓名为两个字的员工信息
select * from emp where name like "__";
--例2：查询身份证号最后一位是X的员工信息
select * from emp where idcard like "%X";
或者
select * from emp where idcard like "_________________X";

-- is null 是null
-- 例：查询没有身份证号得到员工信息
select * from emp where idcard is null;
1
select 字段列表 from 表名 where 条件列表;
4.3.2 逻辑运算符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- and或&& 并且(多个条件同时成立)
-- 例1：查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from emp where age >= 15 && age <=20;
或者
select * from emp where age >= 15 and age <=20;
-- 例2：查询性别为女且年龄小于25岁的员工信息
select * from emp where gender = "女" and age < 25;

-- or或|| 或者(多个条件任意一个成立)
-- 例：查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40;

-- not或！ 非，不是
-- 例：查询有身份证号的员工信息
select * from emp where idcard is not null;

1
select 字段列表 from 表名 where 条件列表;

4.4 分组查询

4.4.1 通过聚合函数查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- count 统计数量
-- 例：统计该企业员工数量
select count(*) from emp;

-- max 最大值
-- 例：统计该企业员工的最大年龄
select max(age) from emp;

-- min 最小值
-- 例：统计该企业员工的最小年龄
select min(age) from emp;

--# avg 平均值
-- 例：统计该企业员工的平均年龄
select avg(age) from emp;

-- sum 求和
-- 例：统计西安地区员工的年龄之和例：统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = "西安";
1
select 聚合函数(字段列额表) from 表名;
4.4.2 通过group by having 查询
1
2
3
4
5
6
7
8
-- 例1：根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;

-- 例2：根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;

-- 例3：查询年龄小于45的员工，并根据工作地址进行分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having count(*) >= 3;

where与having区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
判断条件不同：where不能对聚合函数进行判断，而having可以

  1. 分组之前用where group by是分组查询 分组之后用having
  2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
1
2
3
4
5
-- 使用语法
group by 分组字段列表   having 分组后条件列表

-- 查询语法
select 字段列表 from 表名 [where条件] group by 分组字段名 [having 分组后过滤条件];

4.5 排序查询

1
2
3
4
5
6
7
8
9
10
11
12
-- asc 升序(默认值)
-- 例：根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
或者
select * from emp order by age;

-- desc 降序
-- 例1：根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc;

-- 例2：根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age asc ,entrydate desc ;
1
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

4.6 分页查询

1
2
3
4
5
6
7
-- 例1：查询第1页员工数据，每页展示10条记录
select * from emp limit 0,10;
或者
select * from emp limit 10;

-- 例2：查询第2页员工数据，每页展示10条记录
select * from emp limit 10,10;

注意：

  1. 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
  2. 分页查询是数据库的方言，不同数据库有不同的实现，mysql中是limit
  3. 如果查询得到是第一页数据，起始索引可以省略，直接简写为limit10
1
select 字段列表 from 表名 limit 起始索引,查询记录数;

4.7 DQL查询语句案例练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查询年龄为20，21，22，23岁的女性员工信息
select * from emp where gender = "女" and age in(20,21,22,23);

-- 查询性别为 男，并且年龄在20-40岁(含)以内的姓名为三个字的员工
select * from emp where gender = "男" and (age between 20 and 40) and name like "___";
或者
select * from emp where gender = "男" and age>=20 and age<=40 and name like "___";

-- 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender,count(*) from emp where age>60 group by gender;

-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
select name,age from emp where age <= 35 order by age asc,entrydate desc;

-- 查询性别为男，且年龄在20-40岁(含)以内得的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
select * from emp where gender = "男" and age between 20 and 40 order by age asc ,entrydate asc limit 5;

4.8 DQL编写顺序

1
select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数

4.9 DQL执行顺序

1
from 类名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 select 字段列表 order by 排序字段列表 limit 分页参数

5. 用户及权限操作

5.1 管理用户

5.1.1 查询用户
1
2
use mysql;
select * from user;
5.1.2 创建用户
1
2
3
4
5
-- 例1：创建用户itcast，只能够在当前主机localhost访问，密码123456
create user "itcast"@"localhost" identified by "123456";

-- 例2：创建用户trkj，可以在任意主机访问该数据库，密码123456；
create user "trkj"@"%" identified by "123456";
1
create user "用户名"@"主机名" identifled by "密码";create user "用户名"@"主机名" identifled by "密码";
5.1.3 修改用户密码
1
2
-- 例：修改用户trkj的访问密码为1234
alter user "trkj"@"%" identified with mysql_native_password by "1234";
1
alter user "用户名"@"主机名" identifled with mysql_native_password by "新密码";
5.1.4 删除用户
1
2
-- 例：删除itcast@localhost用户
drop user "itcast"@"localhost";
1
drop user"用户名"@"主机名";

5.2 控制权限

5.2.1 操作权限
  1. all,all privileges 所有权限
  2. select             查询数据
  3. insert             插入数据
  4. update             修改数据
  5. delete             修改数据
  6. drop               删除数据库/表/视图
  7. create             创建数据库/表
5.2.2 管理权限
1
2
3
4
5
6
7
8
-- 查询权限
show grants for "trkj"@"%";

-- 授予权限
grant all on dh_test.* to "trkj"@"%";

-- 撤销权限
revoke all on dh_test.* from "trkj"@"%";
1
2
3
4
5
6
7
8
-- 查询权限
show grants gor "用户名"@"主机名";

-- 授予权限
grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";

-- 撤销权限
revoke 权限列表 on 数据库名 from "用户名"@"主机名";
5.2.3 mysql配置允许外界连接
1
2
3
4
5
6
7
8
9
10
11
12
-- 进入数据库
use mysql;

-- 该方法就是直接修改更改"mysql"数据库里的"user"表里的"host"项，从"localhost"改为"%"
update user set host="%" where user="root";

-- 刷新权限
flush privileges;

-- 查看所有用户和用户对应的权限
SELECT user,host FROM mysql.user;

1
2
3
4
5
6
7
-- 1.当发现数据库分配%权限后还是无法连接时，请使用以下命令检查是否存在两个root用户 
SELECT user,host FROM mysql.user;

-- 2.此时如果确实存在一个权限为'root'@'localhost' 另一个权限未为'root'@'%'，则需手动删除localhost权限的root
DROP USER 'root'@'localhost';

-- 至此再次重新连接mysql，不出意外应该就是能够连接上了，如果连接不上就去找百度看看吧，哈哈哈~