mysql基本操作笔记

拓展博客地址： https://www.cnblogs.com/zhoubaojian/articles/7866292.html

mysql系统数据： https://www.cnblogs.com/progor/p/8462802.html

mysql用户权限管理： https://blog.csdn.net/qq_43028054/article/details/93138503

sql:结构化语言

数据库定义语言 : DDL（针对数据库对象）:create-创建、alter-修改、drop-删除、truncate-删除整表

数据库操作语言：DML（针对数据信息）:insert-插入、update-更新、delete-删除

数据库查询语言：DQL（针对数据信息）：select-查询

DCL：grant(授权)、revoke（回收）

1.启动服务

启动指定服务：

​ net start 服务名

关闭指定服务：

​ net stop 服务名

2.连接服务

mysql -u 用户名 -p -h(host) -P（port）

show databases; 查看数据库

use 数据库名； 选择数据库

数据库名.表名 不需要进入数据库使用数据库下的表

show tables; 查看所在数据库下的所有表

3.用户管理

1、创建用户

法一：创建用户（同时设置密码）

语法：create user 用户名称 [identified by ‘密码’];

法二：创建用户同时分配权限

语法：grant 权限 on 范围 to 用户 identified by ‘密码’;

2、删除用户

语法：drop user 用户名;

说明：

A、ceshi@192.168.1.2表示“ceshi”用户可通过IP为“192.168.1.2”的地址访问指定数据库服务器

B、ceshi@192.168.1.%表示“ceshi”用户可通过IP为“192.168.1.0”至“192.168.1.255”之间的地址访问指定数据库服务器

C、create user c identified by ‘111’;

上边语句创建用户c对应的host地址为“%”

3、更改密码

A、更改指定用户密码

语法：set password for 用户名=password(‘密码’);

B、更改自己的登录密码

语法：set password=password(‘密码’)；

4.权限管理

1、授权

语法：grant 权限 on 范围 to 用户 [with grant option];

说明：如果在授权语句中出现with grant option，代表被授权的用户可将自己的权限授予给别人

2、回收

语法：revoke 权限 on 范围 from 用户;

3、刷新权限机制

flush privileges;

说明：

A、show privileges; #查看数据库中支持的权限

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OKJHKBLz-1581150332223)(file:///C:\Users\l1998\AppData\Local\Temp\ksohtml13192\wps1.jpg)]

B、权限作用范围及分类

• 全局权限Global Privileges

  • 它是管理权限，作用到服务器上的所有数据库。要授予全局权限，使用 ON *.的语法，.*表示所有数据库的所有表（对象，视图等）中。MySQL 把全局权限保存在 mysql.user 表中

• 数据库权限Database Privileges

  • 作用到某个特定数据库的所有对象上。要授予数据库权限，使用 ON db_name.* 的语法，db_name.*表示db_name数据库下的所有表（对象，视图等）。 MySQL 把数据库权限保存在 mysql.db 表中

• 表权限Table Privileges

  • 应用到某个特定表的所有列上。要授予表权限，使用 ON db_name.tbl_name 的语法。 MySQL 把表权限保存在 mysql.tables_priv 表中

• 列权限

权限操作分类： 使用逗号分隔

l 列权限

作用在特定表的特定列上，在权限操作分类中体现列权限 select（col_name），记录到mysql.columns_prive表中 field

• 存储过程权限

  • 作用在存储过程和函数上，记录到mysql.procs_prive表中 procedure

• 代理用户权限

  • 作用是使一个用户成为另一个用户的代理，记录到mysql.proxies_priv表中 proxy

C、查看用户当前权限

语法：show grants for 用户名;

5.严格模式与非严格模式（配置文件中）

严格模式： MySQL自身对数据进行严格的校验（格式、长度、类型等）

非严格模式：MySQL自身对数据不进行严格的校验（格式、长度、类型等）

开启严格模式：

a. sql语句开启( 只对当前连接有效 )

set sql_mode=‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

b.修改配置文件my-defalut.ini

1. //开启

   sql-mode=“STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

2. //关闭

   sql-mode=“NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

c. 查看当前是严格还是非严格模式

select @@sql_mode;

6.mysql数据类型

a.数值型：

• a.1.整型；

  • tinyint 小整数值
  • smallint 大整数值
  • mediumint 大整数值
  • int 大整数值
  • bigint 极大整数值

• a.2.浮点型（不常用）:

  • float
  • double

• a.3.定点型(表示小数时常用)：

  • decimal

  decimal: https://blog.csdn.net/qq_34306360/article/details/79721619

b.字符型：

• char(字符型)
• varchar（能根据内容确定大小）
• blob
• text
• enum(多选一)
• set（多选多）

c.日期和时间类型

• date
• time
• datetime
• timestamp
• year

规则详细： https://blog.csdn.net/qq_34253619/article/details/98941103

7.mysql语句

a.注释：

/*

多行注释

*/

\# 单行注释

--  单行注释 （需在--后加空格） 

注意：在SQL语法中，[ ]表示可有可无

b、创建数据库语法：

create database 数据库名;

c、创建表语法：

create table 表名(

​ 字段名 字段类型(长度) …[unique,default now(),primary key,auto_increment ,not null]

);

unique 唯一键，唯一约束（有此项的字段不能出现重复值，否则会报错）

default 默认值，缺省约束

now（）键入时的系统时间

primary key 主键约束（有此项的字段不能出现重复值，否则会报错）

primary key(字段1，字段2) 组合主键约束（将字段1，字段2组合在一起成为表的主键）

auto_increment 自动增长（必须将相应字段设置成主键或主键的一部分）

not null 非空约束

d.删除库或表

drop database/table 数据库名/表名;

e.插入语句

insert into 表名[(字段1，字段2，…)] values(值1，值2，…);

insert into 表名(字段1,字段2) values(值1,值2),(值2,值3);

— 向表中插入多条数据

注意：

1. 如果针对表中所有字段添加数据，可省略字段列表
2. 字段列表不是表中所有字段时，非空无默认值字段必须出现
3. 值列表须与字段列表一一对应（数量，数据类型）
4. 对于字段列表中允许为空的字段，值可以使用null或default代替用户未指定情况
5. 对于字段列表中有默认字段，值可以使用default替代用户未指定情况

强调：正对比字符、日期和时间类型，数据值使用英文单引号括起来

特别的insert

f.更新语句

update 表名 set 字段1=值1 [,字段2=值2，…] [where 条件表达式];

注意：在update语句中，如果没有出现where子句，则表示针对表中所有数据进行更新操作；如果表中出现where子句，则必须先根据条件表达式筛选数据，再行更新操作

g.删除语句（删除数据）

只删除数据：

delete from 表名 [where 条件表达式]；

整表删除：truncate table 表名； --属于DDL

删除包括表结构在内的

drop table 表名;

补充：

truncate与delete的区别：

h.字段属性unsigned、zerofill的用法！！！

• unsigned 为非负数，用此类型可以增加存储数值范围!

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HHlRpXbE-1581150332225)(file:///C:\Users\l1998\AppData\Local\Temp\ksohtml9388\wps1.jpg)]

• zerofill属性，在数字长度不够的数据前面填充0，以达到设定的长度

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T2pBpAVw-1581150332226)(file:///C:\Users\l1998\AppData\Local\Temp\ksohtml9388\wps2.jpg)]

i.补充知识

desc 表名 查看指定表结构

show create table 表名 查看指定表定义语句

int(5)和int 存储的大小没有区别 int（5）只是限制了显示的宽度

8.约束

a.外键：

为字段构建外键时不能写在字段上，需单独再起一行书写

例如：

create table B(

id bigint unsigned auoto_increment primary key,

no bigint unsigned,

#构建外键约束

constraint fk_no_id foreign key (no) references A(id)

#在当前表no字段上构建引用于表A字段id的，名为fk_no_id的外键

);

被引用的表称为主表，引用的表称为从表，主表的被引用字段必须为主键或者唯一键

主表有被引用的字段时，不能直接删除主表，解决方法：

1.先删除从表对应数据(从表对应外键)，再删除主表数据

2.构建外键引用时，设置级联操作

3.将外键临时取消作用或删除外键的引用

b.删除约束(实质上是指修改表结构)

修改数据库对象：alter

• 删除表B的名为fk_no_id的外键
  • alter table B
  • drop constraint fk_no_id;
• 删除外键约束的语法：
  • alter table 表名
  • drop foreign key 外键名;
• 删除主键：(如果该主键对应字段为自增字段，则不能删除主键约束)
  • alter table 表名
  • drop primary key;
• 删除唯一键：
  • alter table 表名
  • drop index username;

9.对已存在的表进行结构修改

a.添加字段：

• alter table 表名
• add 添加的字段名 类型 [ not null … ];

如：

给表b添加username字段：

alter table b

add username vachar(20) not null ;

b.更改字段属性：

• alter table 表名
• modify 修改的字段名 修改的属性 字段原有的其他属性…；

如将表b的pwd字段修改为长度为30:

alter table b

modify pwd varchar(30) not null;

c.删除字段：

• alter table 表名
• drop 字段名;

10.运算符

11.模糊查询

a.通配符：

%：0或者多个字符

_:一个任意字符

12.子查询

子查询语句用（）括起来

a.select语句组成：

select 子句

from子句

where子句

order by 子句

limit子句

group by子句（having子句）

join技术

b.select语句的应用：

1.检索表中所有信息

• select * from 表名; *表示所有字段

2.检索表中部分字段信息

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

3.列重命名

• 方式一：select 字段名 as 重命名名 from 表名;
  • 如 : select cName as ‘课程名称’ from Courses;
• 方式二：select 字段名 重命名名 from 表名;

4.排序（order by ）

​ #select 字段列表 from 表名

​ order by 字段1 [asc升序] desc

​ [ , 字段2 [ asc ] | [ desc ] … ]

说明：

a.排序方式包含asc（升序，由小到大，默认值可以不写），desc（降序，由大到小）

b.可根据多个字段排序，但是每个字段有其自身排序方式，如果出现多个字段排序，后者是在前者出现重复值的情况下有意义

c.order by 子句出现在where子句的后边（如果有where子句的话）

d.聚合函数可以作为排序依据，如order by

• order by 字段名 依照字段的升序排序

• order by desc 字段名 依照字段的降序排序

• 当数据库语言设置为utf8,使用中文字段排序则需对排序字段进行编码的改变（使用 convert）

  ​ 如：

  select * from courses order by convert(cname using gbk)

5.取多少条数据（limit）

select * from 表名 limit m,n

表示从m+1条开始取，取n条（即取的时候不包含第m条）

补充：max（字段名）：获取该字段数据中的最大值，max（）函数为聚合函数，不能直接出现在聚合函数中（聚合函数不能直接出现在where语句中）

6.聚合函数

max(字段名)

min（字段名）

avg(字段名)：返回指定字段的平均值，只能针对数值型

sum（字段名)：返回指定字段的和值，只能针对数值型

count(字段名):返回结果集的信息条数

• count(*)
• count(1)
• count(主键字段)
• count(非主键字段)

注意：

1.max(字段)、avg(字段)、sum（字段）、count(非主键字段)在统计时自动排除字段中的null值

2.count(*)、count(1)、count(主键字段)返回表中所有符合条件的信息数目

3.avg(字段)、sum（字段）在统计字符字段时，返回值为0

7.取消重复值(distinct)

结果集中重复的数据值只会出现一次 写在字段前边

select distinct 字段名 from 表名;

select distinct 字段1,字段2 from 表名;

如果distinct后边出现多个字段时，表示针对多个字段组合值中的重复进行取消（即所有字段都重复才会被取消）

子查询使用规则：

外部数据大于内部数据使用in

外部数据小于内部数据使用exits

8.in子查询与exists子查询：

• in 子查询：

  select 字段列表 from 表A 表A.字段 in(

  ​ select 表B.字段 字段 from 表B

  ​ );

• exists子查询：

  select 字段列表 from 表A where exists(

  ​ select 表B.字段（主键字段） 字段 from 表B where 表B.字段=表A.字段

  ​ );

  注意：表A.字段与表B.字段有主外关联为佳

9.合并结果集union

默认将结果集中的重复值取消，如不想取消则在union后添加all

用法：

select 字段1 from 表1

union all

select 字段2 from 表2;

where和having的区别?

12.结构控制语句

a.if、case

if:

select concat( tgender,if(tage<18,‘未成年’，‘成年’) ) from teachers;

concat (ext1,ext2…) 将括号内的字符串或字段进行拼凑连接

if(ext1,ext2,ext3) 如果ext1为真，则返回ext2,否则返回ext3

case:

select (case tgender

​ when ‘男’ then ‘男性’

​ when ‘女’ then ‘女性’

​ else ‘其他…’ (else可省略)

​ end) as ‘性别’

from teachers;

select (case

​ when tage<18 then ‘未成年’

​ when tage>18 then ‘成年’

​ when tage<30 then ‘青年’

​ when tage<40 then ‘壮年’

​ else ‘老年’ （可省略）

​ end) as ‘状态’

from teachers;