mySQL常用指令操作

mySQL数据库的启动

1. DOS命令方式启动和关闭：

   • net start mysql

   • net stop mysql

2. 登录数据库：

   1. mysql -u用户名 -p密码

   2. mysql -hip地址 -u用户名 -p密码

   3. mysql --host=IP地址 --user=用户名 --password=密码

3. 退出MySQL：

   • quit或exit

DDL：操作数据库、表

关键字：create，drop，alter

操作数据库

1. 创建：

   ​ *create database 数据库名称;

   ​ *create database if not exists 数据库名称;

   ​ *create database 数据库名称 character set 字符集名;

2. 查询：

   ​ *show databases;

   ​ *show create database 数据库名称;

3. 修改：

   ​ *alter database 数据库名称 character set 字符集名程;

4. 删除：

   ​ *drop database 数据库名称;

   ​ *drop database if exists 数据库名称;

5. 使用数据库

   ​ *use 数据库名称;

   ​ *select database( );

操作表

1. 创建：

   *create table 表名(

   ​ 列名1 数据类型1,

   ​ 列名2 数据类型2,

   ​ 列名n 数据类型n

   );

2. 查询：

   • show tables;

   • desc 表名;

3. 修改：

   • alter table 表名 raname to 新的表名;

   • alter table 表名 character set 字符集名称;

   • alter table 表名 add 列名 数据类型;

   • alter table 表名 change 列名 新列名 新数据类型;

   • alter table 表名 modify 列名 新数据类型;

4. 删除：

   • drop table 表名;

   • drop table if exists 表名；

DML: 增删改表中数据

关键字：insert，delete，update

1. 添加数据：

   • Insert into 表名(列名1，列名2，.....列名n) values (值1，值2，.... 值n);

   • insert into 表名 values(值1，值2，.... 值n);

2. 删除数据：

   • delete from 表名[where 条件]

3. 修改数据：

   • update 表名 set 列名1 = 值1, 列名2 = 值2, ...[where 条件];

DQL: 查询表中的记录（重点）

关键字：select， where

全部查询

• select * from 表名;

基础查询

1. 多个字段查询：

   • select 字段名1，字段名2.... from 表名;

2. 去除重复

   • select distinct 列名1，列名2，... from 表名;

3. 计算列：

   • ifnull(表达式1，表达式2)：null参与运算，计算结果都为null

   • select 列名1，列名2，列名1+ifnull(列名2 ,0)from 表名；

     • 表达式1：哪个字段需要判断是否为null

     • 如果该字段为null后的替换值

4. 起别名：

   • select 列名1，列名2，(列名1+ifnull(列名2 ,0) ) as 列名3 from 表名；

条件查询

1. where子句后跟条件

2. 运算符

   • <,>, <=, >=, = ,!=

   • between .. and

   • in(集合)

   • like: 模糊查询

     • 占位符：

       *—：单个任意字符

       *%：多个任意字符

   • is null

   • and 或者 &&

   • or 或 ||

   • not 或 ！

DQL:查询语句（重点）

1.排序查询：

• order by 排序字段1 排序方式1，排序字段2 排序方式2...

  • 排序方式：

    • ASC(ascending): 升序，默认的。

    • DESC(descending):降序。

2.聚合函数：

• count: 计算个数

• max: 计算最大值

• min: 计算最小值

• sum: 计算和

• avg: 计算平均值

  • 注意：聚合函数的计算，排除null值， 解决方案：ifnull

3.分组查询：

• group by 分组字段；

  • 注意：where和having的区别：

    1.where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行

    限定，如果不满足结果，则不会被查询出来

    2.where后不可以跟聚合函数，having可以进行聚合函数的判断。

4.分页查询：

• limit开始的索引，每页查询的条数。

  • 公式：开始的索引 = （当前的页码 - 1）* 每页显示的条数

约束

关键字：primary key, not null, unique, foreign key

1.非空约束：not null, 值不能为null

​ 1.创建时添加约束：

​ create table stu(

​ id int,

​ name varchar(20) not null

​ );

​ 2.创建表完后，添加约束：

​ alter table stu modify name varchar(20);

​ 3.删除name的非空约束

​ alter table stu modify name varchar(20);

2.唯一约束：unique, 值不能重复

​ 1.创建表时，添加唯一约束

​ create table stu(

​ id int,

​ phone_number varchar(20) unique

​ );

​ 2.删除唯一约束：

​ alter table stu drop index phone_number;

​ 3.在创建表后，添加唯一约束

​ alter table stu modify phone_number varchar(20) unique;

3.主键约束：primary key

​ 1.创建表时，添加主键约束:

​ create table stu(

​ id int primary key,

​ name varchar(20)

​ );

​ 2.删除主键:

​ alter table stu drop primary key;

​ 3.创建完表后，添加主键:

​ alter table stu modify id int primary key;

​ 4.自动增长:

​ *在创建表时，添加主键约束，并且完成主键自增长：

​ create table stu(

​ id int primary key auto_increment,

​ name varchar(20)

​ );

​ *删除自动增长：

​ alter table stu modify id int;

​ *添加自动增长：

​ alter table stu modify id int auto_increment;

4.外键约束：

​ 1.在创建表时，添加外键：

​ create table 表名(

​ ......

​ 外键列

​ constraint 外键名称 foreign key (外键名称) references 主表名称（主表列名称）

​ );

​ 2.删除外键：

​ alter table 表名 drop foreign key 外键名称；

​ 3.创建表之后，添加外键

​ alter table 表名 add constraint 外键名称 foreign key (外键字段名)references 主表名称(主表列名称);

5.级联操作：

​ 1.添加级联操作：

​ alter table 表名 add constraint 外键名称 foreign key(外键字段名称)references 主表名称（主表列名称）on update cascade on delete cascade;

​ 2.分类：

​ *级联更新： on update cascade

​ *级联删除：on delete cascade

DCL: 管理用户、授权（了解）

管理用户

1. 添加用户：

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

2. 删除用户：

   • 语法：DROP USER '用户名'@'主机名';

3. 修改用户密码：

   UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

   SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

   • mysql中忘记了root用户的密码？

     1. cmd -- > net stop mysql 停止mysql服务

        • 需要管理员运行该cmd

     2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables

     3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

     4. use mysql;

     5. update user set password = password('你的新密码') where user = 'root';

     6. 关闭两个窗口

     7. 打开任务管理器，手动结束mysqld.exe 的进程

     8. 启动mysql服务

     9. 使用新密码登录。

4. 查询用户：-- 1. 切换到mysql数据库USE myql;-- 2. 查询user表SELECT * FROM USER;

   • 通配符： % 表示可以在任意主机使用用户登录数据库

权限管理

 

1. 查询权限：-- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';

   2. 授予权限：
       -- 授予权限
       grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
       -- 给张三用户授予所有权限，在任意数据库任意表上
       
       GRANT ALL ON *.* TO 'zhangsan'@'localhost';
   3. 撤销权限：
       -- 撤销权限：
       revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
       REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';