MySQL必备基础知识

最新推荐文章于 2024-08-21 16:15:38 发布
最新推荐文章于 2024-08-21 16:15:38 发布
阅读量1.3k 收藏 9
点赞数 2
分类专栏： thinking in Database 文章标签： 数据库 python mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44225602/article/details/90479035
版权

MySQL必备基础知识

一. 数据库的登陆方式

1.登陆的两种模式

(1).简单模式

# 直接以本机root用户登陆
> mysql -u root -p

(2).标准模式

# 登陆本地主机
# 类似于简单模式下的mysql -u root -p指令，都指本机的root用户登陆
> mysql -h 127.0.0.1 -u root -p
> mysql -h localhost -u root -p

# 登陆远程主机
> mysql -h xxx.xxx.xxx.xxx -u root -p
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10060)

# 默认不允许访问远程登陆, 需要授权root用户可以通过外网IP进行访问
> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option

参数含义说明

  • -u 是指user
  • -p 是指password
  • -h 主机地址

2.登陆时端口占用问题

3306端口是MySQL默认端口，不过也会有其他软件占用这个端口,解决办法
window环境下：

# 查看占用3306端口的PID
netstat -ano|findstr 3306

# 查看占用3306端口的应用程序
tasklist|findstr 3306

# 杀掉该进程
taskkill /f /t /im 进程id 或 进程名称

Linux环境下：

# 查看端口为3306的进程
netstat -an | grep 3306
#杀掉进程
kill 3306

# 其他方式查看进程
ps  aux 或 ps -ef |grep xxx

二. 数据库的退出方式

3种退出方式

  • \q
  • quit
  • exit

三. 数据库的主要操作

  • 增 create
  • 查 retrive
  • 改 update
  • 删 delete
    传说中的crud便是该四个单词首字母的缩写

1.对<库结构>和<表结构>相关操作为：

创建: create
查看：show
修改：alter
删除：delete

查看相应指令
库show databases;
表show tables;
当前 所在库select database();
创建库 信息show create database [库名];
表 结构desc [表名];
创建表 语句show create table [表名];
同上，但以竖状查看,更清晰show create table\G
创建相应指令
库create database [库名];
表create table [表名](字段);
库 并修改字符编码create database [库名] character set utf8
库 并判断该 库 是否已存在(防止报错)create database if not exists [库名]
表 并判断该 表 是否已存在(防止报错)create table if not exists [表名]

创建表时，字段的用法见下续;
创建表时，需进入到相应的库中进行创建，使用use [库名]

修改相应指令
库 字符编码alter database [库名] character set utf8;
表 字符编码alter table [表名] character set utf8;
字段 字符编码alter table [表名] modify [字段名] 字段类型约束条件 character utf8
删除相应指令
库drop database [库名]
表drop table [表名]
表 防止报错drop table if exists [表名]
库 防止报错drop database if exites [库名]
撤销 整个指令\c

注意

  1. MySQL以英文分号作为结束
  2. SQL命令不区分大小写
  3. 库无法直接退出，使用use直接进行库的切换
  4. 特别注意创建库时的编码，若库的编码如果错误，则会创建错误编码的表和字段，修改则需要分别修改三者的编码
  5. 更改默认创建库字符串编码（可以省去第4点），
C:\ProgramData\MySQL\MySQL Server 5.7
64行 ：default-character-set=utf8
  1. 更改不严谨报错模式。使操作出现的部分小错误不报错，而按照最低条件执行，并提出警告
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  1. windows下：表名、库名，不区分大小写；linux下：表名、库名，区分大小写。
  2. MySQL中库和表的名称具有唯一性，即：库名，同一个库内的表名不要出现相同的名称
  3. 命令输入完成时，若添加完分号进行执行时，没有执行，查看下左侧是否存在引号未闭合情况，加上引号再\c取消改语句。否则\c无法执行。

2. 进入数据库-选择数据库

只有进入数据库，才能针对该库创建表，以及对表进行数据操作

进入数据库相应指令
入库use [数据库名]

3.表字段的操作

a.创建表的主体结构：

create table if not exists [表名](
字段名称 字段类型 约束条件 字段说明，
字段名称 字段类型 约束条件 字段说明，
... ...
主键索引，
唯一索引，
常规索引
);

b.表的删除 字段 语句

alter table [表名] drop [字段名]

c.表的修改 字段 语句

#修改字段属性，及其名称
alter table [表名] change [字段名称] [新名称] [字段类型] [[约束条件] [字段说明]]
#修改字段属性
alter table [表名] modify [字段名称] [字段类型] [[约束条件] [字段说明]]

#将字段移位,放在字段名称2的后面
alter table [表名] modefy [字段名称1] after [字段名称2];
#将属性放到第一个，保留完整性约束条件
alter table [表名] modify [字段名称] [字段类型] first;

d.表的增加 字段 语句

alter table [表名] add [字段名称] [字段类型] [约束条件] [字段说明]

字段的增、删、改都用alter+add、drop、modify

4.表数据的操作

A.查询 表 数据 - SELECT

# 不指定字段查询 （不推荐）
select * from [表名]
# 指定字段查询 （推荐）
select [字段1, 字段2 ......] from [表名]
# 对查询到的字段起别名
select [字段1 as 别名1, 字段2 as 别名2 ......] from [表名]
# 给查询结果添加一个新的字段别名
select [字段1 as 别名1, 字段2 as 别名2 ......], [新值1 as 字段别名1，新值12 as 字段别名2 ......] from [表名]

B.插入 表 数据 - INSERT

#指定字段名称插入 单值
insert into [表名](字段1, 字段2 ......) values(值1, 值2 ......);
#指定字段名称插入 多值
insert into [表名](字段1, 字段2 ......) values(值1, 值2 ......), (值1, 值2 ......);


#不指定字段名称插入 单值
insert into [表名] values(值1, 值2 ......);
#不指定字段名称插入 多值
insert into [表名] values(值1, 值2 ......), (值1, 值2 ......);

注意：指定字段，按指定字段顺序给值；不给定字段，按默认表顺序执行
C.表中 值 的修改语句 - UPDATE

# 修改 一个 字段的值
update [表名] set [字段名]=[值];
# 修改 多个 字段的值 (不限定where条件会改变所有列)
update [表名] set [字段名1]=[值1],[字段名2]=[值2]......
# 给字段的值在原有基础上改变
update [表名] set [字段名1]=[改变1],[字段值2]=[改变2]......

注意：进行数据修改时，一定要给定where条件来限定修改行，否则默认修改整个表的数据
D.表中 值 的删除语句

# 不加where条件会默认删除整张表
delete from [表名] where [条件]

注意：进行数据删除时，一定要给定where条件来限定修改行，否则默认删除整个表的数据，并且auto_increment自增记录的值不会改变，需要归位

# 自增归位
alter table [表名] auto_increment=1;

# 清空表数据，并自增归位
truncate 表名;

四.三种字段类型

  • 数值类型
类型大小范围(有符号)范围(无符号)用途
tinyint1字节(-128, 127)2^8(0, 255)小整数值
small2字节(-32768,32767)2^16(0,65535)大整数值
int4字节（-2147483648, 2147483647)(0,4294967295)大整数值
float4字节单精度浮点型
double8字节双精度浮点型
decimaldecimal(m, d)如果m>d则为m+2，否则为d+2依赖于m和d的值依赖于m和d的值小数值(更精准)

实例：创建表语句

mysql> create table numtype(
	-> ttinyint tinyint,
	-> ssmallint smallint,
	-> iint int,
	-> ffloat float(6,2),
	-> ddouble double(6,2),
	-> ddecimal decimal(6,2)
	-> );

数值类型注意事项：

  1. 小数类型，不仅能够保证数据计算更为精确，还能节省空间
  2. float、double、decimal在存储的时候，小数点超出了会四舍五入
  3. 数值类型，tinyint、smallint、int等，给出后面的括号是没有意义的，不能限制存储长度，除非配合约束条件fillzero零填充的时候才有意义
  • 日期和时间类型
类型大小(字节)范围格式用途
date31000-01-01 ~ 9999-12-31YYYY-MM-DD日期值
time3-838:59:59 ~ 838:59:59HH:MM:SS时间值、持续时间
year11901 ~ 2155YYYY年份值
datetime81000-01-01 00:00:00 ~ 9999-12-31 13:59:59YYYY:MM:DD HH:MM:SS混合日期和时间值
timestamp41970-01-01 00:00:00 ~ 2038YYYYMMDDHHMMSS混合日期和时间

实例：创建表语句

mysql> create table timetype(
	-> ddate date,
	-> ttime time,
	-> yyear year,
	-> ddatetime datetime default now(),
	-> ttimestamp timestamp
	-> );

日期类型注意事项：

  1. 存储日期时，我们可以使用整型类存储时间戳，便于我们进行日期的计算。
  2. timestamp默认值不为空，默认位当前的时间戳。
  • 字符串类型
类型大小用途
char0 ~ 255字节定长字符串
varchar0 ~ 255字节变成字符串
tinyblob0 ~ 255字节不超过255个字符的二进制字符串
tinytext0 ~ 255字节短文本字符串
blob0 ~ 65535字节二进制形式的长文本数据
text0 ~ 65535字节长文本数据
mediumblob0 ~ 16777215字节二进制形式的中等长度文本数据
mediumtext0 ~ 16777215字节中等长度文本数据
longblob0 ~ 4294697295字节二进制形式的极大文本数据
longtext0 ~ 4294697295字节极大文本数据
enum(成员1,成员2…)65535个成员枚举：可赋予某个枚举成员
set(成员1,成员2…)64个成员集合：可赋予多个集合成员，多个集合成员用逗号隔开

创建表语句：

mysql> create table stringtype if not exists(
	-> cchar char,
	-> vvarchar varchar,
	-> ttinyblob tinyblob,
	-> ttinytext tinytext,
	-> bblob blob,
	-> ttext text,
	-> mmediumblob mediumblob,
	-> lloneblob longblob,
	-> llongtext longtext,
	-> eenum enum('f','m'),
	-> sset set('1','2','3','4')
	-> );

字符串类型注意事项：

  • char与varchar的区别
  1. char执行效率高于varchar
  2. char比varchar占用空间
  3. char和varchar类型长度范围都在0 ~ 255之间
  4. 当char类型传入值的长度小于给定的限定长度，则为空格填充到指定长度(占空间)
  5. 当varchar类型传入的值小于给定的限定长度，不会用空格填充(不占空间)
  6. 如果开启了不严谨报错，给出的值超过了限定长度，会自动截取
  • enum与set的区别
  1. enum只能选择多个成员中的一个成员
  2. set可以选择多个成员，如果存在重复的会自动去重
  3. enum和set都只能学则给定成员.
  • blob和text类型
  1. blob和text类型都是可以存放任意大数据的数据类型
  2. blob区分大小写，text不区分大小写

五.字段约束

1.unsigned无符号

  • 只能存储正数，不可出现负数
  • 只能用于设置数值类型
  • 最大存储长度增加一倍

实例：

mysql> create table unsignedtest(
	-> age1 tinyint,
	-> age2 tinyint unsigned
	-> );
	
mysql> insert into unsignedtest values(127,255);

2.zerofill零填充

  • 只能用于设置数值类型，不满最大限制长度，则用零补齐

实例：

alter table mytest add zf int(6) zerofill;
insert into mytest(zf) values(1234);
>>>001234   # 补位2个

3.auto_increment自增

  • 用于设置字段的自动增长

实例：

mysql> create table autoin(
	-> id int primary key auto_increment,
	-> username varchar(20)
	-> );

注意：

  1. 自增需要配合索引去使用
  2. 插入数据的时候，无需给值，自动递增给值，步长为1

下面 索引类型 部分的 primary key主键 部分有对自增步长的更改。

4.default默认值

  • 通过此属性来指定一个默认值，不给默认值，则默认值为null

实例：

mysql> alter table autoin add sex enum('w','m') default 'm';
mysql> insert into autoin(username) values('lyh');

5.null 和 not null 空和非空

  • null指定在字段没有给定默认值的情况下，值默认为null。
  • not null指定字段插入值时，必须非空。
    实例：
alter table autoin add number tinyint unsigned not null;

6.comment 设置说明

  • 设置注释、解释说明

实例：

mysql> create table commenttest(
		-> info varchar(20) comment '存储个人信息字段'
		-> );
mysql>alter table commenttest comment='用户信息表' ；

六.null值得注意事项

(1). null意味着没有值，或者未知值
(2). 不要对null进行算术运算，运算结果还是null
(3). 0和null都意味着假，其余值都为真

七.建表的注意事项

(1). 表的字段之间要用逗号隔开，最后一个字段不要存在逗号
(2). 数据表名不要和字段名重名
(3). auto_increment属性，必须依赖于主键索引
(4). 表名称和字段名称尽量不要使用MySQL的关键字
(5).使用反引号，会使创建表的效率增高

八.MySQL的索引类型

主要有四种索引

  • 主键索引 primary key
  • 唯一索引 unique
  • 常规索引 index
  • 全文索引 fulltext

(1)主键索引

关系数据库中最常见的索引类型，它起到唯一标识数据的作用，确定数据表里一条特定的数据记录的位置。在字段后添加primary key来设置该字段为主键索引。

注意：

  1. 最好为每张表制定一个主键，但不是必须指定
  2. 一个表只能指定一个主键，而且主键的值不能为空，它通常和auto_increment搭配

实例：

mysql> create table primarytest(
	-> id int unsigned primary key auto_increment
	-> username varchar(20)
	-> );

步长的自增
mysql的默认步长时基于会话session的，为全局变量，其中默认为1
(1)会话session

查看变量
show session variables like 'auto_inc%';

设置步长，只能针对当前对话
set session auto_increment_increment = 2设置步长会话为2

(2)全局global

查看全局
show global variables like 'auto_inc%'
修改全局级别的
set global auto_increment_increment = 1

(3)修改自增位和自增归位

修改自增位
alter table [表名] auto_increment=1;

自增归位(清空表，并自增归位)
truncate [表名]

(2)唯一索引

唯一索引和主键索引一样，都可以防止创建重复的值，但是不同之处在于，每个数据表中只能有一个主键索引，但是可以有多个唯一索引.使用unique对字段定义唯一索引

实例：

mysql> create table uniquetest(
	-> username varchar(20),
	-> email varchar(50),
	-> phone char(11),
	-> unique(username),
	-> unique uniqueemail(email),
	-> unique(phone)
	-> );

说明：
给username、email和phone设置了唯一索引，其中给email设置了索引名称uniqueemail，其余索引名称默认为字段名

注意：
如果再给表中字段插入值得时候报错，检查下是否有字段设置了唯一索引。

(3)常规索引

常规索引技术是关系型数据库查询中最重要的技术。如果要提升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升
缺点：

  1. 多占用磁盘空间
  2. 减慢插入、修改和删除的操作

创建常规索引：
创建常规索引可以使用key或index关键字随表一同创建

实例：

# 索引名称为lyhindex
mysql> create table indextest(
	-> username varchar(20),
	-> index lyhindex(username)
	-> );
# 索引名称为字段名
mysql> create table keytest(
	-> username varchar(20),
	-> key (username)
	-> );

说明：

  1. 给username字段设置一个常规索引，索引名称为lyhindex
  2. 如果不给索引字段起名称，默认索引名称为字段名

注意：

  1. 在给MySQL创建常规索引和唯一索引时，单独一行去创建，不要和字段放在同一行，否则会报错。
  2. 某些字段有大量查阅的时候，一个表可以存在多个常规索引，但是要根据具体的情况去设置。

(4)全文索引

全文索引在mysql中是一个fulltext索引类型，但是fulltext索引只能用于MyISAM表，并且只可以在char、varchar或text类型的字段上创建。

缺点：
fulltext是不支持中文全文索引的。

实例：

mysql> create table `fulltest`(
	-> `article` text,
	-> fulltext key `article`(`article`)
	-> )ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> alter table [表名] add fulltext(字段名称);

九.条件语句WHERE

实例表结构：

+----------+-------------+------+-----+-----------------------+----------------+
| Field    | Type        | Null | Key | Default               | Extra          |
+----------+-------------+------+-----+-----------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                  | auto_increment |
| sex      | tinyint(4)  | NO   |     | 1                     |                |
| username | varchar(20) | YES  |     | NULL                  |                |
| age      | tinyint(4)  | NO   |     | 18                    |                |
| userinfo | varchar(50) | NO   |     | lyh is a good student |                |
+----------+-------------+------+-----+-----------------------+----------------+

1. 比较运算符

(1) 大于 >

  • 将id大于5的人的性别改为女，年龄改为为20岁
update users set sex="女",age=20 where id>5;

(2) 小于 <

  • 将id小于5的人的性别改为男，年龄改为为18岁
update users set sex="男",age=18 where id<5;
  • 查看id小于4的人的性别和用户名
select sex,username from users where id<4;

(3) 大于等于 >=

  • 删除id大于等于6的数据
delete from users where id<=6;

(4) 小于等于 <=

  • 查询年龄小于等于23的数据
select * from users where id<=23;

(5) 等于 =

  • 查询性别为男的数据
select * from users where sex="男";

(6)不等于 != 或者 <>

  • 查询姓名不等于liyuhong的数据
select * from users where username!="liyuhong";
select * from users where username<>"liyuhong";

2. 逻辑运算符

(1) and 逻辑与运算 —— 两侧为真，结果为真

  • 查询年龄在18到23岁之间的人，不包括本身
select * from users where id>18 and age<23;
  • 修改年龄为30，id大于1，小于等于2的人
update users set age=30 where id>1 and id<=2;

(2) or 逻辑或运算 —— 一侧为真，结果为真

  • 查询年龄在18到20岁之间的人
select * from users where age=18 or age=20

(3) between … and … ——在…范围内，包括本身

  • 查询年龄在18到20岁之间的所有数据
select * from users where age between 18 and 20;
select * from users where age>=18 and age<=20;

(4) not between…and… ——不在…范围内

  • 查询年龄不在18到20岁之间的所有数据
select * from users where age not between 18 and 20;
select * from users where age<18 or age>20;

(5) in 在…里

  • 查询年龄在18或20岁的数据
select * from users where age in(18,20);
select * from users where age=18 or age=20;

(6) not in 不在…里

  • 查询年龄不在18或20岁的数据
select * from users where age not in(18,20);
select * from users where age!=18 and age~=20;

3. 升序和降序 order by排序

(1) 升序

  • 查询数据按照年龄升序（默认）
select * from users order by age;
select * from users order by age asc;

(2) 降序

  • 查询数据按年龄降序
select * from users order by age desc;

4. limit取值

结构：
limit x：取出x条数据
limit x,y：从x的位置取出y条数据

实例：

  • 取出3条数据
select * from users limit 3;
  • 取出年龄最大/最小的一条数据
select * from users order by age desc limit 1;
select * from users order by age limit 1;
  • 从0开始，取三条数据
select * from users limit 3;
select * from users limit 0,3;

分页实例：
数据一共100条
每页10条数据
第一页 limit 0,10
第二页 limit 10，20
第三页 limit 20，30
公式：
（nowpage-1）*10

5. is、is not对null值进行查询

  • 查询username为null的数据
select * from users where username is null;
  • 查询username不为null的数据
select * from users where username is not null;

注意：
因为null为特殊值，不可以用 != 或者 = 进行查询

6. 模糊查询like

(1).‘%字符’ 查询以 [字符] 结尾的数据

  • 查询以"鸿"字结尾的username的数据
select * from users where username like "%鸿";

(2).‘字符%’ 查询以 [字符] 开头的数据

  • 查询以"李"字开头的username的数据
select * from users where username like "李%"

(3).’%字符%’ 查询包含 [字符] 的数据

  • 查询包含"宇"字的username的数据
select * from users where username like "%宇%"

(4).’_'通配符 代表匹配任意一个字符

  • 查询用户名只有一个字符的数据
select * from users where username like '_';
  • 查询"宇鸿"字符串，以任意字符开头的数据
select * from users where username like '_宇鸿'

(5).not like 不近似

  • 查询用户名除了两个字符串以外的任意数据
select * from users where username not like '__'

(6).[charlist]原子表 表示原子表内任意一个字
从表中选取任意一个以赵、钱、孙、李开头的名字

select * from users where username like '[赵,钱,孙,李]%';

7. 去重 DISTINCT

select distinct [字段名] from [表名];

select distinct userinfo from user;

8. 子查询（查询条件还是一条SQL语句）

select * from [表名] where [字段名] in [SQL语句] ;

select * from users where age in (select age from users where sex="女");

十. 聚合函数

  1. count 统计个数
  2. max 最大值
  3. min 最小值
  4. sum 求和
  5. avg 求平均数
select count(*) as count,max(age),min(age),avg(age),sum(age) from users;

十一. 分组group by

主体结构：

select count(字段) from [表名] group by [字段];
  • 统计男生和女生分别有多少人
select sex,count(*) from user group by sex;
  • 统计每班有多少人
select class,count(*) from user group by class;
  • 统计每个班的男生和女生分别有多少人
select class,sex,count(*) from user group by class,sex

having分组条件的使用，相当于where

  • 查询人数大于2的班级
select class,count(*) as count from user group by class having count>2;
  • 查询班级为3班和4班的人数
select class,count(*) as count from user group by class having class in('3','4')
  • 查询班级为3班和4班的人数，并且人数大于2人
select class,count(*) as count from user group by class having class in('3','4') and count>2;

十二. 多表联查

表结构：
users表：

+----------+-------------+------+-----+-----------------------+----------------+
| Field    | Type        | Null | Key | Default               | Extra          |
+----------+-------------+------+-----+-----------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                  | auto_increment |
| sex      | tinyint(4)  | NO   |     | 1                     |                |
| username | varchar(20) | YES  |     | NULL                  |                |
| age      | tinyint(4)  | NO   |     | 18                    |                |
| userinfo | varchar(50) | NO   |     | lyh is a good student |                |
| class    | varchar(20) | NO   |     | onlin4                |                |
+----------+-------------+------+-----+-----------------------+----------------+

address表：

+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| uid     | int(11)          | NO   |     | NULL    |                |
| address | varchar(255)     | NO   |     | NULL    |                |
| code    | varchar(255)     | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

1.隐式内连接

主体结构：

select * from [表1],[表2]... where [表1].[字段名]=[表2].[字段名] and ...
  • 查询 id=1 的用户名和地址：
select * from users,address where users.id=address.uid and users.id=1;
  • 获取某个字段的值和起别名：
select u.username,a.address from users u,address a where u.id=a.uid and u.id=1;

2.显示内连接inner join

主体结构：

select * from [表1] inner join [表2] on 条件;

查询id为1的用户名和地址

select u.username,a.address from users u inner join address a on u.id=a.uid and u.id=1;

3.左连接

主体结构：

select * from [表1] left join [表2] on 条件;
  • 查询id为1的用户名和地址
select u.username,a.address from users u left join address a on u.id=a.uid and u.id=1;

4.右连接

主体结构：

select * from [表1] right join [表2] on 条件;

查询id为1的用户名和地址

select u.username,a.address from users u right join address a on u.id=a.uid and u.id=1;

注意

  1. 显示内连接和隐式内连接是相同的，会将两端匹配的数据查询出来
  2. 左连接会以左表为主表，右表为辅表，将主表的数据全部查询出来，辅表的数据没有的用null来占位。
  3. 右连接会以右表为主表，左表为辅表，将主表的数据全部查询出来，辅表的数据没有的用null来占位

十三. 其他操作

1.修改密码

set password for [用户名]@localhost=password('xxxxxx')

2.创建其他用户分配权限

(1)使用MySQL数据库

use mysql

(2)查看当前库下有哪些用户

select user from user

(3)创建用户、密码

create user 用户名 identified by '密码'

(4)赋予权限,all表示增删查

grant all on [root用户].* to [用户名]
grant insert,select on [root用户].* to [用户名]

(5)回收权限

revoke all on [root用户].* from [用户名]

(6)修改用户名

rename user [用户名] to [新用户名]

(7)删除用户

drop user [用户名]

(8)刷新

flush privileges

十四. 触发器

概述：他是一个特殊的存储过程，他是MySQL在insert、update、delete的时候自动执行，不能直接调用。

他主要包含四个要素：

  1. 监视地点（table）
  2. 监视事件（insert/update/delete）
  3. 触发时间（after/before）
  4. 触发事件（insert/update/delete）
    主题结构：
create trigger trigger_name
after/before insert/update/delete on table_name
for each row
begin
sql语句（触发一句或多句）
end;

创建两张表：商品表goods和订单表order来说明触发器的使用实例
goods表：

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| goods_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| goods_name | varchar(255)     | NO   |     | NULL    |                |
| goods_num  | int(11)          | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

order表：

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| order_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| goods_id  | int(11)          | YES  |     | NULL    |                |
| order_num | int(11)          | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

将goods表添加四条数据：

insert into goods(good_name, good_num) values('手机', 20),('单反',30),('电脑',40);

(1)实现购买任意商品，对应的商品数量相应的减少
分析：
监视地点：order表
监视事件：insert操作
触发事件：insert之后
触发事件：update操作

drop trigger if exists t1;
create trigger t1;
after delete on `order`
for each row
begin
update goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id

十五. 数据表类型与存储位置

MyISAM和InnoDB两种类型最为重要
MyISAM与InnoDB的区别：

  1. MyISAM类型的数据表会产生三个文件，InnoDB会产生两个文件
  2. MyISAM数据表类型效率更高
  3. InnoDB安全性高于MyISAM
  4. InnoDB支持事务处理，MyISAM不支持事务处理
  5. InnoDB支持外键，MyISAM不支持外键

MyISAM存储表文件的作用：

文件类型作用
.frm存储数据表的框架结构。文件名与表名是相同的，每个表对应一个同名的frm文件(MyISAM和InnoDB共有的文件)
.MYDmy data表数据文件
.MYImyindex

InnoDB存储表文件的作用：

文件类型作用
.idb存放数据库 表数据 和 索引

数据库的存储位置：
数据库也是以文件形式存放在磁盘上，Data文件中。

十六. InnoDB的事务处理

如果MySQL的配置文件没有更改过，那么如果默认为MyISAM数据表类型，则可以在配置文件my.ini中，更改为InnoDB，也可以通过命令去更改。

alter table [表名] engine = innodb/myisam

(1)查询当前是否为自动提交

select @@autocommit

如果值为1，则为自动提交

(2)开启事务处理

set autocommit=0

(3)事务开始

begin

(4)执行SQL语句

insert into [表名] values(.......)

(5)提交或回滚

commit work;
rollback work;

注意：

  1. 如果开启了事务处理，在处理数据后，如果没有进行提交或回滚，默认回滚，那么你刚才的操作和没操作一样。
  2. 只有InnoDB支持事务处理，而MyISAM不支持。

十七. 用Python操作MySQL

  1. 安装、使用库
pip install pymysql

import pymysql
  1. 链接数据库
db = pymysql.connect(主机名,用户名,密码,数据库名)
  1. 设置字符集
db.set_charset("utf8")
  1. 创建游标对象
cursor=db.cursor()
  1. 执行SQL语句
cursor.execute(sql语句)
  1. 获取结果集
#获取所有
cursor.fetch_all()
#获取一条
cursor.fetch_one()
  1. 获取受影响的行数
cursor.rowcount()
  1. 关闭数据库连接
db.close()

事务：
pymysql默认开启了事务处理，所以在添加数据的时候，需要commit或者rollback

实例：

try:
	sql="insert into user values(null, lyh, 21, wust, network engineer)"
	print(sql)
	cursor.execute(sql)
	db.commit()
except:
	db.rollback()

实例 登陆注册

表结构：

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(11)         | NO   | UNI | NULL    |                |
| password | varchar(12)      | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

MD5加密：

import hashlib

def dm5():
	m = hashlib.md5()
	m.update(str.encode("utf8"))
	print(m.hexdigest())

实例：

import pymysql,re


class LR:
    # 当进行类的实例化的时候 会自动调用 把一些作为类需要初始化的代码 进行操作
    def __init__(self,host='127.0.0.1',user='root',password='123456',db='test'):
        # 连接mysql数据库 选择test数据库
        self.db = pymysql.connect(host,user,password,db)
        # 设置字符集
        self.db.set_charset('utf8')
        # 创建游标对象
        self.cursor = self.db.cursor()

    # 封装公共的方法
    def publicFunc(self):
        self.username = input('请输入手机号码')
        self.password = input('请输入密码(6~12位)')
        # 正则匹配数据是否正确
        usernameRes = re.match('1[3-9][0-9]{9}$', self.username)
        passwordRes = re.match('\w{6,12}', self.password)
        # 这里的return 目前的作用是 阻止代码不再向下继续执行
        if not usernameRes:
            print('请输入正确的手机号码')
            return False
        if not passwordRes:
            print('请输入正确的密码')
            return False
            # 当上面的数据都没有任何问题 则进行查询该用户名是否存在
        sql = 'select username,password from users where username="{}"'.format(self.username)
        # 执行sql语句
        self.cursor.execute(sql)
        return True

    # 注册
    def register(self):
        # 调用公共的方法去进行判断
        if not self.publicFunc():
            return

        # 查询当前结果受影响的行数
        if self.cursor.rowcount:
            print('该用户名已存在')
            return
        # 异常处理  有问题则回滚  没有问题则提交
        try:
            # 插入数据
            sql = 'insert into user(username,password) values("{}","{}")'.format(self.username,self.password)
            self.cursor.execute(sql)
            self.db.commit()
            print('恭喜：%s 注册成功'%self.username)
        except:
            self.db.rollback()
            print('注册失败 当前系统繁忙')

    # 登录
    def login(self):
        # 调用公共的方法去进行判断
        if not self.publicFunc():
            return

        if not self.cursor.rowcount:
            print('请输入正确的用户名')
            return

        # 该用户存在 取出数据
        data = self.cursor.fetchone()
        if self.username == data[0] and self.password == data[1]:
            print('登录成功 欢迎：',self.username)
            return
        print('请输入正确的用户名和密码')



lr = LR()
lr.login()
BlackStar_L
关注
专栏目录
MySQL——基础知识总结超详细版本（一）
weixin_45699237的博客
04-08 5270
1、数据库概述以及常见概念的理解 存储数据用文件就可以了，为什么还要弄个数据库? 文件保存数据有以下几个缺点： 文件的安全性问题 文件不利于数据查询和管理 文件不利于存储海量数据 文件在程序中控制不方便 内存中的数据：断电后不保留 硬盘中的数据：持久化后的数据 需求出现：开发使用软件程序时，需要进行硬盘数据的管理 前提：硬盘中数据的管理要比内存中数据的管理复杂 导致：如果让程序员们完全自由地管理硬盘中的数据变得困难 所以：数据库管理软件就诞生了,由这些软件进行科学地、高效地管理硬盘中
mysql一行多列变成一列多行_MySql必备基础知识（一）
weixin_39950057的博客
11-20 1026
1　数据库概念(了解)1.1　什么是数据库数据库就是用来存储和管理数据的仓库！数据库存储数据的优先：可存储大量数据；方便检索；保持数据的一致性、完整性；安全，可共享；通过组合分析，可产生新数据。1.2　数据库的发展历程没有数据库，使用磁盘文件存储数据；层次结构模型数据库；网状结构模型数据库；关系结构模型数据库：使用二维表格来存储数据；关系-对象模型数据库；　　MySQL就是关系型数据库！1.3　常...
MySQL时间戳用BIGINT和TIMESTAMP类型的比较
最新发布
Mr_lee_1的博客
08-21 501
存储效率TIMESTAMP占用空间较小（4 字节），但仅适用于 1970 到 2038 年的时间范围。BIGINT占用空间较大（8 字节），但可以存储更大的范围和更高精度的时间。查询效率：两者在索引上都表现良好，但TIMESTAMP自动处理时区，适合时间范围查询。BIGINT适合存储和查询不需要时区处理的时间戳。选择哪种类型取决于你的应用需求。如果需要时区支持并且时间范围在 2038 年之前，TIMESTAMP是更好的选择。如果需要处理更高精度或更大范围的时间戳，BIGINT更合适。
Mysql必备基础知识
11-29
MySQL基础知识入门必备，对于刚刚开始学习MySQL数据库的新手很友好。
MySQL基础知识
m0_74288306的博客
08-19 1290
存储数据用文件就可以了，为什么还要弄个数据库?文件的安全性问题文件不利于数据查询和管理文件不利于存储海量数据文件在程序中控制不方便磁盘内存①mysql是数据库服务的客服端②mysqld是数据库服务的服务器端③mysql的本质是基于C(mysql)S(mysqld)的一种网络服务。
可能是全网最好的MySQL重要知识点/面试题总结
热门推荐
JavaGuide
06-29 2万+
文章目录什么是MySQL?事务相关什么是事务?事物的四大特性(ACID)介绍一下?并发事务带来哪些问题?事务隔离级别有哪些?MySQL的默认隔离级别是?索引相关为什么索引能提高查询速度什么是最左前缀原则？注意避免冗余索引Mysql如何为表字段添加索引？存储引擎一些常用命令MyISAM和InnoDB区别乐观锁与悲观锁的区别悲观锁乐观锁两种锁的使用场景乐观锁常见的两种实现方式1. 版本号机制2. CA...
MySql数据库(二)
xulimessage的专栏
01-27 670
2.1什么数据约束 对用户操作表的数据进行约束 2.2 默认值 作用： 当用户对使用默认值的字段不插入值的时候，就使用默认值。 注意： 1）对默认值字段插入null是可以的。 2）对默认值字段可以插入非null -- 1.1 默认值 CREATE TABLE student( id INT,
mySQL基础知识
lyxawdn的博客
04-21 2401
MySQL数据库基本操作-DDL 2、创建表 创建表格式： create table 表名（ 字段名1 类型[(宽度)] [约束条件] [comment '字段说明'], 字段名2 类型[(宽度)] [约束条件] [comment '字段说明'], 字段名3 类型[(宽度)] [约束条件] [comment '字段说明'] ）[表的一些设置]; 创建表是构建一张空表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。 3、对表结构的常用操作 简单操作： s
MySql必备基础知识.doc
07-05
本文档主要涵盖了MySQL的基础知识，包括数据库的概念、发展历史、常见的数据库系统、数据库的理解、MySQL的安装、目录结构、基本命令，以及SQL语言的概述。 1. **数据库概念** 数据库是一个用于存储和管理数据的...
mysql基础知识.pdf
06-09
了解其基础知识是任何数据库管理员或开发者的必备技能。以下是一些关于MySQL的基础知识，涵盖了从查询到数据操作的各个方面。 1. **基础查询**： - `SELECT`语句用于从数据库中选择数据。可以查询单个或多个字段，...
mysql必知必会知识点
大数据技术派
07-16 523
数据分析中，通常需要使用数据库来存储数据，包括一些关系型和非关系型数据库，关系型数据库中mysql使用很广泛，因为它免费，支持强大的功能。mysql是使用最广泛的结构化数...
mysql必备的基础知识
weixin_44741610的博客
10-15 135
数据型 整型 TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。 INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。 浮点数 FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。 FLOAT、DOUBLE 和 D
MySQL必备知识
qq_45893999的博客
06-22 2264
设计数据库 做一个数据库之前,一定要先规划好要做什么，如何做，以及需要存储哪些数据，这些数据之间的关系是 什么，需要满足哪些用户要求,都到先考虑好.这就是数据库设计的前提 SQL语句不区分大小写 单引号和双引号是字符串,区分大小写 ’ ’ " " SQL 语句尽量用大写 数据库的名字后缀加上 _db 表格名称的前面加上 _tb 数据库设计 数据库的设计就是将数据规范化,过程化 需求设计 概念结构设计 逻辑结构设计 物理结构设计 数据库的实施 数据库的维护 需求设计 需求设计(需求分析) 主要是根据用户的要
MySQL基础知识,入门必备
Sunflower
08-29 1511
数据库佛百六就是，仓库。这里有小伙伴要问了。仓库！我知道！俺家就是送快递的，好几个仓库的！这样说的仓库和数据库有神马相同之处，可以类比？就依这小伙家的仓库做类比吧。仓库里有快递对吧，然后这小伙是负责管理在这个仓库里的快递，这样一来进一步类比，你把快递当作数据，把那小伙当作管理员，埃~搁到一块就是管理仓库。这就叫数据库！！！！一句话：就是存储、管理数据的仓库！！外日忒得！！！
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值