- mysql 安装、sql语法
- 一条sql语句完成MySQL去重留一
- mysql 事务、索引、锁、分区/分表、sql优化、查询优化
- mysql 安装配置使用、sql语法
- MySQL主键、创建索引、UNION 和 UNION ALL
window下的 MySQL客户端 或 代码程序 连接Linux的MySQL服务器,修改linux下的mysql配置:
1. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
2. sudo service mysql restart
4.mysql -uroot -p密码
5.use mysql
6.update user set host = '%' where user = 'root';
7.flush privileges
window下的MySQL客户端 或 代码程序 连接window的MySQL服务器,修改window下的mysql配置:
1.mysql -uroot -padmin
2.use mysql
3.update user set host = '%' where user = 'root'; 修改root登录访问权限
4.flush privileges
第一种方式:set password for 'root'@'localhost'=password('新密码')
第二种方式:mysqladmin -uroot -pchuanzhi1 password chuanzhi
注意:可能会报以下错误,但是实际是仍然成功修改密码的:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
错误:Access denied for user 'root'@'localhost' (using password:YES)
出现这种错误有两种可能,一是MySQL的root用户的密码错误,二是权限不够的问题
解决方法:
一、修改root用户的密码
1.知道原来的myql数据库的root密码
①在终端命令行输入 mysqladmin -u root -p password "新密码" 回车 ,Enter password: 【输入原来的旧密码】
②登录mysql系统修改, mysql -uroot -p 回车 Enter password: 【输入原来的密码】
mysql>use mysql;
mysql> update user set password=password("新密码") where user='root'; 【密码注意大小写】
mysql> flush privileges;
mysql> exit;
然后使用刚才输入的新密码即可登录。
2.不知道原来的myql的root的密码
首先,你必须要有操作系统的root权限了。要是连系统的root权限都没有的话,先考虑root系统再走下面的步骤。 类似于安全模式登录系统。
需要先停止mysql服务,这里分两种情况,一种可以用service mysqld stop,另外一种是/etc/init.d/mysqld stop
当提示mysql已停止后进行下一步操作
在终端命令行输入:mysqld_safe --skip-grant-tables &
(其中 –skip-grant-tables 的意思是跳过授权表,通过此参数来跳过输入密码,后面跟得 & 符号是表示设置)
输入:mysql,也可以回车之后在输入命令: mysql (登录mysql系统)
进入 mysql数据库,然后通过语句修改密码:
mysql> use mysql;
mysql> UPDATE user SET password=password("新密码") WHERE user='root'; 【密码注意大小写】
mysql> flush privileges;
mysql> exit;
重新启动mysql服务
这样新的root密码就设置成功了。
二、修改root用户的登录权限
1.可以使用mysql -uroot -p密码进入mysql数据库的情况下:
mysql> use mysql
mysql> update user set host = '%' where user = 'root';
mysql> flush privileges
2.不能使用mysql -uroot -p密码进入mysql数据库的情况下:
需要先停止mysql服务,这里分两种情况,一种可以用service mysqld stop,另外一种是/etc/init.d/mysqld stop
当提示mysql已停止后进行下一步操作
在终端命令行输入:mysqld_safe --skip-grant-tables &
(其中 –skip-grant-tables 的意思是跳过授权表,通过此参数来跳过输入密码,后面跟得 & 符号是表示设置)
输入:mysql,也可以回车之后在输入命令: mysql (登录mysql系统)
进入 mysql数据库,然后通过语句修改密码:
mysql> use mysql
mysql> update user set host = '%' where user = 'root';
mysql> flush privileges
mysql> exit;
然后重新启动mysql服务就可以了
解决中文乱码
解决:打开start_navicat文件
将export LANG="en_US.UTF-8"改为export LANG="zh_CN.UTF-8"
出现SQL语句语法错误时,可以根据 near 的提示错误 出现在 哪个位置
一.操作数据库:
1.mysql登录:
mysql -u root -p密码
mysql -u root -p 回车后再输入密码
2.退出mysql:quit、exit、ctrl + d
3.查看数据库:show databases;
4.查看当前使用的数据库:select database();
5.选择使用/切换 指定的数据库:use 数据库名;
6.创建数据库:
1.create database 数据库名;
(创建数据库时,默认DEFAULT CHARACTER SET latin1,即拉丁,但不支持中文,要设置为charset=utf8)
2.create database 数据库名 charset=utf8; 创建数据库时设置字符集为utf8;utf8支持中文;
3.alter database 数据库名 character set utf8; 修改数据库的字符集为utf8
4.alter table 表名 convert to character set utf8 collate utf8_general_ci; 修改表的字符集为utf8 和 排序规则为 utf8_general_ci
7.查看 “创建数据库的”语句:show create database 数据库名;
8.删除数据库:drop database 数据库名;
(1.操作表之前先必须“use 数据库名”进入某个数据库,才能对某个数据库中的表进行操作;
2.给表中插入汉字数据的话,要求table表和database数据库的字符集都是charset=utf8;)
二.操作表:
1.查看表:show tables;
2.sql文件中使用注释:--
3.创建表:
1.craete table 表名(字段名/列名 字段类型 约束 , ......);
2.craete table if not exists 表名(字段名/列名 字段类型 约束 , ......); 如果不存在该表才创建该表
3.craete table 表名(
id int unsigned not null primary key auto_increment,
name varchar(15),
age tinyint unsigned default 0,
high decimal(5,2) default 0,
gender enum(“男”,”女”,“请选择”) default “请选择” );
(1)约束(not null / default 值 / primary key / auto_increment)
(2)int默认表示有符号,int unsigned 表示无符号;
(3)有多个约束时,不分先后;
(4)decimal(5,2) 表示 最大5位数字,其中两位小数;
(5)枚举 enum(“...”,”...”) default “...” 表示枚举类型的字段只能插入添加“列举出的值的”其中一个,无法保存其他没有列出的值;
枚举的数据值为中文的话,要求 数据库 为 charset=utf8 或 table表 为 CHARSET=utf8
(6)如果数据库为 charset=utf8的话,那么创建出来的表 默认为以下配置:ENGINE=InnoDB DEFAULT CHARSET=utf8;
4.craete table 表名(......) select ...... from table:
1.在创建表名1时,如果select的字段名和create的字段名同名的话,会直接批量把 “select查询表名2中字段名2下的”
所有字段值 给插入到 “表名1中字段名1” 下:
craete table 表名1 (字段名1 字段类型 约束 , ......) select 字段名1 from 表名2
2.在创建表名1时,如果select的字段名和create的字段名不同名的话,给select的字段名起一个“和create的字段名同名”的
别名,此时同样会把 “select查询表名2中字段名2下的”所有字段值 给插入到 “表名1中字段名1” 下:
craete table 表名1 (字段名1 字段类型 约束 , ......) select 字段名2 as “和表名1的字段名1同名的”别名 from 表名2
3.在创建表表名1时,如果select的字段名和create的字段名不同名的话,会批量把 表名2中的字段名2 和
字段名2下的所有字段值 都给添加到 表名1中:
craete table 表名1 (字段名1 字段类型 约束 , ......) select 字段名2 from 表名2
4.查看表的结构:desc 表名;
5.查看 “创建表的”语句:show create table 表名;
6.修改表结构:
1.添加字段:alter table 表名 add 字段名/列名 字段类型 约束; (not null / default 值 / primary key / auto_increment)
2.修改字段的类型/约束(字段不重命名):alter table 表名 modify 字段名/列名 字段类型 约束;
3.修改字段的类型/约束(字段可以重命名):alter table 表名 change 原字段名/列名 新字段名/列名 新字段类型 新约束关系;
4.删除字段:alter table 表名 drop 字段名/列名;
7.删除表:drop table 表名;
三.增删改查(CURD)
1.查询:
select * from 表名 where 过滤条件;(没有where则查询表中全部数据)
select 字段1 , ... from 表名 where 过滤条件; 查询显示指定列的数据/指定字段的数据
(没有where则查询表中所有行中的指定列的数据/指定字段的数据)
1.“数据库名 . 表名 . 字段名”、“数据库名 . 表名 .* ”:
如果要运行的SQL语句中 使用的table表 不存在于 当前正在使用的数据库中,
即执行“use 数据库名”进入的数据库中 不存在 “SQL语句中使用到的”table表的话,
那么必须加上 “数据库名 . 表名 . 字段名”,用以指代执行的是别的数据库中的 table表;
2.“表名 . 字段名”、“表名.*”:
当前“use 数据库名”进入的数据库中存在 “SQL语句中使用到的”table表;
而SQL语句中同时又存在多张table表的话,一般建议在字段名都加上表名,指定是哪张表中的字段,
但也可以省略不加表名;
3.sql语句末尾加上 \G ,不需要加上“分号;” ,能够使查询出的结果集中每一行数据 以一列打竖 地显示;
4.select 语句还可以放到 craete table 后面 或 insert into table 后面;
2.表中插入数据:
1.insert / insert into 表名(字段名1, ...) values / value(字段值1, ...); 表示可以给表中部分的指定字段添加数据值
2.insert / insert into 表名 values / value(字段值1, ...); 表示给表中所有字段插入数据
3.insert / insert into 表名1 (字段名1) select 字段名2 from 表名2 ; 表示先查询出 表名2 中的某字段值数据,
然后把该某字段值数据插入到 表名1 中的某字段中,表名1中的该字段名和表名2的该字段名同不同名都可以;
(1.如果插入的数据包括id时,并且id主键是自动增长的话,那么id的字段值必须使用占位符:0 / NULL / default;
2.有多少字段名就必须有多少字段值,而且字段的类型必须跟字段值对应;
3.如果字段是枚举类型的话,那么只能插入添加“列举出的”其中一个元素值,不能插入添加“列举之外的”数据值;
4.枚举列出的每个元素值都有对应的枚举值:
大部分编程语言中:第一个元素的枚举值都是默认从 0 开始,依次每个元素的枚举值递增加一;
SQL中:第一个元素的枚举值都是默认从 1 开始,依次每个元素的枚举值递增加一;)
3.多行批量插入:
insert / insert into 表名(字段名1, ...) values / value(字段值1, ...) , (字段值1, ...), ......;
insert / insert into 表名 values / value(字段值1, ...) , (字段值1, ...), ......;
4.修改:
1.update 表名 set 字段名/列名 = 字段值 where 过滤条件;(没有where则全部的每行数据中的某字段值都修改)
2.连接更新:
第一步:
update 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.字段名 = 别名2.字段名 set 别名1.字段名 = 别名2.字段名
把 表名2中的字段值 赋值给 表名1中的字段
第二步:
如果表名1中的该修改前的字段值 和 修改后的字段值的类型不一致的话,则需要把表名1中的该字段的类型也修改为符合的类型
修改字段的类型/约束(字段不重命名):alter table 表名1 modify 字段名/列名 字段类型 约束;
修改字段的类型/约束(字段可以重命名):alter table 表名1 change 原字段名/列名 新字段名/列名 新字段类型 新约束关系;
5.删除:
delete from 表名 where 过滤条件;(没有where则删除表中全部数据)
物理删除:delete
逻辑删除(假删除):使用is_delete字段表示是否被删除状态,is_delete字段的类型可以是bit(1表示被删除,0表示没有被删除)
做逻辑删除的操作:alter table 表名 add is_delete bit default 0 给表中增加is_delete字段
update 表名 set is_delete = 1 where 过滤条件
1.“字段 as 别名”
1.“字段 as 别名”:不会修改表结构中的字段名,别名只是临时使用;
2.如果使用了 “表名 as 别名”的话,那么也要使用 “别名 . 字段名”,不能再使用 “表名 . 字段名”
3.聚合函数(字段名) as 别名
4.as 可省略
2.去重distinct
select distinct 字段 from table
3.条件查询 where
1.比较运算符:>、<、>=、<=、=、!=、<>
where 字段名 > / < / >= / <= / = / != / <> 值
!= / <> 都表示 “不等于”
2.逻辑运算符:
1.not、!=
字段名 != 值
not 字段名 = 值
(要求“and、or”左右两边的条件表达式都是完整的,即不能“字段名 = 值1 and 值2”,
而必须是“字段名 = 值1 and 字段名 = 值2”)
2.and:条件表达式 and 条件表达式;必须同时符合两个条件;
正确写法:“字段名 = 值1 and 字段名 = 值2”
3.or:条件表达式 or 条件表达式;只需要符合其中一个条件;
正确写法:“字段名 = 值1 or 字段名 = 值2”
3.模糊查询:
1.like:字段名 like “字符%”:查询以该字符为开头的字符串
%:匹配任意多个字符,可有可无;
_:匹配任意一个字符,至少有一个;
2.正则表达式:rlike(使用特殊符号“. * [] {} () ^ $”表示的意思才是真正的正则表达式)
字段名 rlike “^字符1 .* 字符2$”: 查询以该字符1 为开头、字符2为结尾、中间可有可无可任意多个字符 的字符串
4.范围查询:(可以是在非连续的值的范围 或在 连续的值的范围内 进行查询)
字段名 in (值1 , ...):在范围内进行查询
字段名 not in (值1 , ...):不在范围内进行查询
5.between 值1 and 值2:
字段名 between 值1 and 值2 ------------> 字段名1 >= 值1 and 字段名1 <= 值2
表示在连续的范围内进行查询,并且包含两边的值
字段名 not between 值1 and 值2
表示不在连续的范围内进行查询,并且不包含两边的值
6.null 空判断:is null、is not null
字段名 is null
字段名 is not null
7.asc升序、desc降序:
order by 字段:默认升序 (值从小到大)
order by 字段 asc:升序 (值从小到大)
order by 字段 desc:降序(值从大到小)
order by 字段1 asc , 字段2 desc:多个字段进行排序(先根据 字段1 按照 asc升序 进行排序,如果 字段1 有多行数据值相等时,
再根据 字段2 对该多行数据 按照 desc降序 进行排序)
8.select 函数():
select now()
select database()
select count(值/字段名):count() 中必须带有参数值/字段名
select count(字段名) from table:计算以某个字段(某列) 所拥有的总行数
select count(*) from table:count(*) 计算总行数
9.max(字段名):最大值
select max(字段名)
min(字段名):最小值
select min(字段名)
10.子查询:
进行子查询中,括号括起来的SQL语句优先执行;
select 字段 from table where 字段 = (select 字段 from table where ......)
1.标量子查询(一行一列):where 字段 比较运算符 (select ... from table)
2.用 ANY / SOME / ALL 修饰 比较运算符:
列级子查询(一列多行):where 字段 比较运算符 any / some (select ... from table) ;
where 字段 != ALL / <>ALL (select ... from table) ;
ANY / SOME 表示符合任何一个条件就可以了;
ALL 表示符合全部条件 ;
!=ALL / <>ALL 表示不符合全部条件;
3.使用 IN / NOT IN 的子查询,NOT IN 相当于 != ALL 、<>ALL ;
列级子查询(一列多行,同一字段中有多个值):where 字段 in (select ... from table) ;
一般都可以使用 内连接语句 来代替 列级子查询;
3.行级子查询(一行多列,每个字段都有一个值):
where (字段1 , 字段2 , ...) = (select 字段1 , 字段2 , ... from table) ;
4.表级子查询(子查询的结果集数据是多行多列,有多个字段,而每个字段都有多个值)
1.要把 “子查询语句返回的多行多列的”结果集数据 作为 表名 放在 from / join 后面的话,
要给该子查询的多行多列结果集 起一个别名 来作为 表名 使用;
1.select ...... from (select * from table) as 别名
2.select ...... from table1 inner / left / right join (select 字段名1 , 字段名2 , ...... from table)
as 别名 on table1.字段名 = 别名.字段名
2.主查询语句可以使用where 多个字段 in 子查询语句中的 多个字段 的值;
(in 用于 一列多行,即in 用于 一个字段 对应 多个值)
select ...... from table where (字段1 , 字段2) in (select 字段1 , 字段2 from table)
5.EXISTS / NOT EXISTS 表示如果子查询返回任何行,EXISTS将返回TRUE,否则为FALSE,用的比较少。
where exists (子查询语句)
11.聚合函数/集合函数:
1.求和:select sum(字段名)
求多行数据一列字段值的和:sum(字段名)
求一行数据多列字段值的和:sum(字段名1 + 字段名2 + ......)
2.求平均值:写法一:select avg(字段名)
写法二:sum(字段名1) / count(字段名1);sum和count中的字段名都是同一个字段
错误写法:sum(字段名) / count(*):
12.round(值/字段名 , 保留的小数位):按照四舍五入规则 保留指定的小数位;
round(5.456 , 2):按照四舍五入规则 保留2位小数位
round(avg(字段名) , 2):对某字段计算出的平均值按照四舍五入规则 保留2位小数位
13.SQL语法的帮助文档:进入mysql后,执行问号? 加上 SQL语法单词,即可查询该 SQL语法单词的用法
14.分组:group by 字段名(把只要 该字段的字段值 都为同一个值 的多行数据 都会被 分到 同一组)
select 字段名1 from table group by 字段名1
1.按某字段的值进行分组查询;
该某字段的值首先会被去重,然后按照“只要该字段为该分组的字段值的”行数据都会被分为这一组;
2.正确写法:
1.group by 后面进行分组的字段 出不出现在 select 后面的查询字段中 都可以;
2.select 后面出现的 单独使用的 查询字段 都必须出现在 group by 后面进行分组的字段中;
3.如果 select 后面的查询字段中 要使用 “不是group by的”分组字段的话,那么便要在select 后面,
把该“不是group by的分组字段的” select 查询字段 放到聚合函数(sum()等)中 或 group_concat()中;
4.sum() / count() / 聚合函数() / group_concat() 都可以把 “不是group by的分组字段的” select 查询字段 作为参数;
group_concat():可以把多个“不是group by的分组字段的” select 查询字段 拼接为 一个字符串;
3.错误写法:select 后面出现的 单独使用的 查询字段 如果没有出现在 group by 后面进行分组的字段中 是错误的;
15.group_concat(字段名):可以把多个“不是group by的分组字段的” select 查询字段 拼接为 一个字符串;
1.select 字段名1 , group_concat(字段名2) from table group by 字段名1
2.select 字段名1 , group_concat(字段名1 , 字段名2 , ......) from table group by 字段名1
3.select 字段名1 , group_concat(字段名1 , “-” , 字段名2 , “-” , ......) from table group by 字段名1
使用 “-” 作为分隔符,那么使用group_concat拼接多个字段为一个字符串时,该字符串中的每个字段之间都带有“-”,
那么便可以使用正则表达式根据“-”从该字符串中取出每个字段值;
4.group by 后面的字段 可以不需要出现在 select 和 from之间,但 select 和 from之间不能单独使用 group by 后面不存在的字段,
但可以在select 和 from之间 使用 聚合(集合)函数 或 group_concat() 带上 group by 后面不存在的字段;
写法顺序:select...from...where...group by...having...order by...limit start count
执行顺序:from ---> where ---> group by ---> select ---> having ---> order by ---> limit start count
where、group by、having 、聚合函数:
1.where 后面不能使用 聚合函数
2.having 后面要使用 聚合函数 的话,必须在 select 中写出该聚合函数
16. having 条件表达式:
group by 分组之后进行条件过滤,要使用 having;
group by 字段 having 条件表达式:先进行分组,然后在已经分好组的基础上之后,再进行条件过滤;
条件表达式中使用到的字段可以和group by使用的字段是同一个,也可以是不同;
17.where 和 having 的区别:
where 对源数据进行条件过滤;
having 是对于分组之后的数据作进一步的条件筛选操作;有 having 一定有 group by,有 group by 不一定有 having
18.分页:limit start , count
start :第一种意思:start 可以省略,此时start 默认值为0,表示默认从第一行数据开始;表示从哪行什么位置开始,作为起始位置;
第二种意思:跳过多少行数据,从哪行什么位置开始,作为起始位置;
count:从起始位置向后获取count条数据;如果不足count行,则有多少获取多少;
分页公式:limit (n - 1) * m , m:每页显示 m 条数据,当前页数为 n(页数 n 从 1 开始),
表示从第 (n - 1) * m 条/行 开始查询 m 条数据 显示到 页数为 n 的当前页;
19.SQL 语法格式:select 查询字段 from table
where 条件表达式(对源数据进行条件过滤)
group by 字段 (把不同的字段值作为一个分组)
having 条件表达式(对已经分好组的数据 进行条件过滤)
order by 字段 asc/desc (对某字段进行升序/降序的排序)
limit (n - 1) * m , m (从第 (n - 1) * m 条/行 开始查询 m 条数据 显示到 页数为 n 的当前页)
20.笛卡尔积:
错误写法:from table1 , table2 where table1.id = table2.id (where 是 过滤条件)
21.连接查询:(on 是 连接条件,可以使用where 替换 on)
1.内连接:
1.select table1.字段 table2.字段 from table1 inner join table2 on table1.id = table2.id(... inner join ... on ...)
select table1.字段 table2.字段 from table1 join table2 on table1.id = table2.id (... join ... on ...)
select table1.字段 table2.字段 from table1 cross join table2 on table1.id = table2.id(... cross join ... on ...)
2.select t1.字段 t2.字段 from table1 as t1 inner join table2 as t2 on t1.id = t2.id
(可以使用 as 别名,但使用别名后就不能使用表名了)
3.select t1.* t2.* from table2 as t2 inner join table1 as t1 on t1.id = t2.id(table1 和 table2 前后交换也可以)
2.左外连接:
以左边的表作为主表,左表(主表)中的所有每行数据都会显示,当右表不存在对应左表(主表)中的数据时,
那么右表中该不满足连接条件的位置则会使用NULL作为填充;
1.select table1.字段 table2.字段 from table1 left join table2 on table1.id = table2.id (... left join ... on ...)
2.select t1.字段 t2.字段 from table1 as t1 left outer join table2 as t2 on table1.id = table2.id (... left outer join ... on ...)
3.右外连接:
以右边的表作为主表,右表(主表)中的所有每行数据都会显示,当左表不存在对应右表(主表)中的数据时,
那么左表中该不满足连接条件的位置则会使用NULL作为填充;
1.select table1.字段 table2.字段 from table1 right join table2 on table1.id = table2.id; (... right join ... on ...)
2.select t1.字段 t2.字段 from table1 as t1 right outer join table2 as t2 on table1.id = table2.id; (... right outer join ... on ...)
3.select table1.字段 table2.字段 from table2 left join table1 on table1.id = table2.id;
因为在使用右外连接,可能会在左表中使用NULL来填充,那么有时候想更美观地不在左表中显示出NULL的话,
便可以使用 “左外连接left join” 代替 右外连接,同时把左表和右表的位置互换,达到原本使用右外连接的效果;
4.多表(内/左外/右外/自)连接:
1.双表(内/左外/右外)连接:from table1 (inner / left / right) join table2 on table1.id = table2.id;
2.三表 内连接:
实际都是前两张表连接后组成一张新的表,该新的表再连接第三张表;下面的例子即为 table1连接table2后组成一张新的表,
该新的表再连接table3,而连接条件可以是table1或table2的字段连接table3的字段;
1.select ... from table1 inner join table2 on table1.字段名 = table2.字段名 inner join table3 on table1.字段名 = table3.字段名;
2.select ... from table1 inner join table2 on table1.字段名 = table2.字段名 inner join table3 on table2.字段名 = table3.字段名;
3.双表 自连接:from table as parent inner join table as son on parent.id = son.parent_id;
4.三表 自连接:from table as parent inner join table as son on parent.id = son.parent_id inner join table as grandson
on son.id = grandson.parent_id;
5.三表 (左外/右外)连接:
select ...... from table1 (letf / right) join table2 on table1.字段名 = table2.字段名 (letf / right) join table3
on table1.字段名 = table3.字段名;
当三张表进行 左外连接 / 右外连接 时,在 table1 左连接/右连接 table2 后,实际是 table1 继续进行 左连接/右连接 table3,
而不是 table2 进行 左连接/右连接 table3,因为 table1 才是 主表,table2 和 table3 都是从表;
而如果是 table2 进行 左连接/右连接 table3 的话,就会变成 table2 又是主表又是从表,而这是错误的;
22.(自关联 可以使用 内连接 / 左外连接 / 右外连接)
select parent.字段, son.字段 from table1 as parent inner join table1 as son on parent.id = son.parent_id
select parent.字段, son.字段 from table1 as parent right outer join table1 as son on parent.id = son.parent_id
select parent.字段, son.字段 from table1 as parent left outer join join table1 as son on parent.id = son.parent_id
把多张表 的数据放到同一张表中,可以使用 自关联;
下面的需求都可以用到 自关联:
1.把 省表、市表、区表 都放到同一张表中:
省数据的parent_id为NULL,而表示市数据的parent_id等于表示省数据的id,以此类推。
2.把 一级菜单、二级菜单、三级菜单 都放到同一张表中;
一级菜单 的parent_id为NULL,而二级菜单的parent_id 等于 一级菜单的id,以此类推。
3.需求:查询员工名称和其对应经理的名称
SELECT e.empno, e.ename, m.ename FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;
22.导入SQL文件,并自动执行SQL文件:
source SQL文件相对于启动mysql程序的相对路径 / SQL文件的绝对路径
比如说:SQL文件在用户目录下,又是在用户目录下启动mysql的话,那么即可以使用 source ./ sql文件名
导入sql文件到数据库的步骤:
1.第一步:mysql -u用户名 -p
2.第二步:create database 数据库名 charset=utf8
3.第三步:use 数据库名
4.第四步:
第一种命令:(source为 mysql下的命令)source (相对启动mysql的路径/绝对路径)sql文件名
第二种命令:(mysql为 非mysql下的命令)mysql -u用户名 -p 数据库名 < (相对启动mysql的路径/绝对路径)sql文件名
一.数据备份与恢复:
1.备份数据库命令:mysqldump -u用户名 -p 数据库名 > 相对路径/绝对路径下的sql文件(该sql文件以 .sql 为后缀名;> 表示定向输出)
创建出新的sql文件备份着指定数据库中的数据(mysql / mysqldump 指令 都不是进入mysql数据库后用的指令);
再进行备份完数据库,最好是重启一下mysql服务:sudo service mysql restart,防止后面进行修改表结构时出现意外错误;
2.数据恢复:
1.第一步:mysql -u用户名 -p
第二步:create database 数据库名 charset=utf8
第三步:use 数据库名
第四步:
第一种命令:(source为 mysql下的命令)source (相对启动mysql的路径/绝对路径)sql文件名
第二种命令:(mysql为 非mysql下的命令)mysql -u用户名 -p 数据库名 < (相对启动mysql的路径/绝对路径)sql文件名
2.先创建一个新的数据库用于保存“要进行数据恢复的”数据库数据:create database 数据库名 charset=utf8:
“备份了数据库数据的”sql文件中都会写有 “Database: 数据库名”;
3.数据恢复的命令:mysql -u用户名 -p 数据库名 < 相对路径/绝对路径下的sql文件(该sql文件以 .sql 为后缀名;< 表示定向输入);
(mysql / mysqldump 指令 都不是进入mysql数据库后用的指令)
4.如果出现access denied for user权限不足的话,执行 1.use mysql; 2.update user set host='%' where user='用户名';
5.在恢复数据库数据时,会执行以下操作:
先判断数据库中的表是否存在,如果存在则删除,再创建新的表;
先使用写锁,再进行批量插入,最后再解锁;
第一步:DROP TABLE IF EXISTS 表名;
第二步:CREATE TABLE 表名(......)
第三步:LOCK TABLES 表名 WRITE;
第四步:INSERT INTO 表名 VALUES(...), (...), ......
第五步:UNLOCK TABLES;
6.host='%':host列是指定登录的ip,比如说 user=root host=192.168.1.1 ,这里的意思就是说,root用户只能通过 192.168.1.1的客户端
去访问,而%则是个通配符, 如果host=192.168.1.%,那么就表示 只要是 ip为host=192.168.1.前缀的客户端都可以连接,
那么host=%的话则表示所有ip都有权去连接。
二.E-R关系:一对一关系、一对多关系、多对多关系(需要创建中间表)
三.设置外键:
1.设置外键要注意的事项:
1.要求被外键引用的字段为主键;
2.要求“被设置为外键的”字段 和 “被外键引用的”字段 都已存在表中;
3.要求“被设置为外键的”字段的值 必须 存在于 “被外键引用的”字段的值中;
4.要求“被设置为外键的”字段 和 “被外键引用的”字段 的类型、约束 都必须要一致;
5.如果要删除“被外键引用的”字段中的值的话,首先就要求保证 外键字段中的值 没有使用(引用)到 “被外键引用的”字段中的值;
如果外键字段中的值使用(引用)到 “被外键引用的”字段中的值时,是无法直接删除“被外键引用的”字段中的值的,
因此此时就需要先删除 外键字段中 使用(引用)到 “被外键引用的”字段中的值,才能再删除该“被外键引用的”字段中的值;
6.当要进行把表中某字段设置为外键时,如果设置不上的话,可以尝试重启 mysql服务:sudo service mysql restart;
如果之前对当前使用的数据库进行了备份数据库的操作的话,则建议重启 mysql服务,防止后面进行修改表结构时出现意外错误;
2.创建表时的设置外键:
1.foreign key(外键字段名) references 被外键引用的表名(被外键引用的主键名);
2.在创建表时,并不需要手动给外键字段名添加外键约束名称,而是会自动给外键字段名添加外键约束名称,
在查看“创建表的”语句时,可以看到 不仅会给 foreign key 前自动加上 “CONSTRAINT 外键约束名称”,
还自动会给该外键字段创建索引key,可以看到还自动带上了表示索引的 KEY ‘外键字段名’(‘外键字段名’)语句;
3.修改表的字段为外键:
1.foreign key 前面 可手动加 外键约束名称(不手动加也会自动创建一个外键约束名称),
创建外键约束名称的语句:CONSTRAINT 外键约束名称(外键约束名称可随意);
2.alter table 表名 add foreign key(外键字段名) references 被外键引用的表名(被外键引用的主键名);
3.alter table 表名 add CONSTRAINT FK_ID foreign key(外键字段名) references 被外键引用的表名(被外键引用的主键名);
4.只要是创建外键,都会给该 外键字段名 自动加上 外键约束名称 和 索引key;
4.取消表中外键的外键约束关系:
1.drop外键:仅是打破该字段上的外键关系,而不会删除“为外键的”字段本身;
2.第一步:drop外键,打破外键关系;
alter table 表名 drop foreign key 外键约束名称
(外键约束名称 并不是 外键字段名,CONSTRAINT后面的名称 就是外键约束名称)
3.第二步:删除给外键字段设置的索引key;
alter table 表名 drop key 外键字段名
四.MySql access denied for user错误:user权限不足;
解决:update user set host='%' where user='用户名'
一.创建connection连接数据库对象,根据connection连接数据库对象获取cursor游标对象来执行sql语句;
二.python2:导入 pymysql
python3:导入 mysqldb
pymysql安装:
window安装 pymysql:pip3 install pymysql
linux安装 pymysql:sudo pip3 install pymysql
三.操作MySql的API:
1.创建连接数据库的connection连接对象:
conn = connect(host=”localhost”,user=”用户名”,password=”密码”,database=”数据库名”,port=3306,charset=”utf8”)
2.获取操作数据库的cursor游标对象:
cur = conn.cursor()
3.编写sql语句:
sql = “...”
4.执行sql语句:
ret = cur.execute(sql)
sql语句中可加可不加“分号;”;execute返回值ret为影响的数据行数;
5.如果是对数据的更新操作(插入/修改/删除 操作),只有在commit提交之后,才会把更新操作的影响提交到数据库,
此时才会真正更新数据库中数据,因此可以在提交之前,撤销刚才的更新操作:
conn.commit()
6.关闭 cursor游标对象 和 关闭 connection连接对象:
cur.close()
conn.close()
四.在终端中,执行的每个更新操作的sql语句都会自动自动开启事务,执行完之后,并会自动提交关闭事务;
手动开启事务的命令:begin;
回滚的命令(回滚后自动关闭事务):rollback;
提交的命令(提交后自动关闭事务):commit;
select查询无需开启事务,因为不涉及到数据更新;
1.mysql可以手动开启事务,当手动开启事务之后,在commit提交关闭事务之前,所有更新操作都不会影响到数据库中的数据;
2.手动开启事务之后,多个sql语句中只要有一个sql语句失败了,那么当前该事物就是失败的状态,此时应该执行rollback回滚操作,
那么之前已经执行了的sql语句全都会被回滚撤销,不会影响到数据库中的数据;
3.一旦执行了commit 或 rollback,就会自动关闭当前事务;
五.获取select查询结果集中的行数据:
1.获取select查询结果集中的一行数据:
查询出数据时,返回值类型为元祖,元祖中为一行数据;
如果查询不出数据,则返回None;
ret = cur.execute(sql)
ret = cur.fetchone()
2.获取select查询结果集中的所有的每行数据:
查询出数据时,返回值类型为元祖,大元祖中又嵌套多个小元祖,嵌套在大元祖中的每个小元祖即为每行数据;
如果查询不出数据,则返回 空元祖;
ret = cur.execute(sql)
ret = cur.fetchall() # 获取出多行数据
for item in ret: # 遍历出每行数据
print(item[i]) # 获取每行数据中每个字段
六.
1.类中的 def __init__(self):
1.创建连接数据库的connection连接对象
self.conn = connect(host=”localhost”,user=”用户名”,password=”密码”,database=”数据库名”,port=3306,charset=”utf8”)
2.获取操作数据库的cursor游标对象
self.cur = self.conn.cursor()
2.类中的某功能函数中 执行sql:
ret = self.cur.execute(sql) 返回影响行数
ret = self.cur.fetchall() 获取出select查询结果集中所有的每行数据
3.容易造成sql注入的参数化sql:'%s'必须带上单/双引号,因为用户输入的数据不带有引号;
除非用户输入的数据带有引号,那么'%s'才不带有''引号;
sql = “insert into table(id,name) values(%d,'%s')”%(id,name)
ret = self.cur.execute(sql) 返回影响行数
self.conn.commit()
4.防止sql注入的参数化sql写法:
sql = “insert into table(id,name) values(%d,%s)”
ret = self.cur.execute(sql,[id,name]) 返回影响行数
self.conn.commit()
5.如果cur.execute(sql, 列表[字段值]/元祖(字段值,))的参数是列表/元祖时,任何类型的字段值都会被自动加上引号成为字符串;
因此SQL中的%s占位符两边不能带上引号,而本来是使用%d的占位符也改为使用%s,即字段值本身是int值,在sql中的占位符也要使用%s;
即一般是“insert into table(id,name) values(%d,'%s')”%(id,name),但使用了cur.execute(sql,[id,name])之后,
都要使用“insert into table(id,name) values(%s,%s)”,%s占位符两边也不能带上引号;
6.如果cur.execute(sql,{字典})的参数是字典时,任何类型的字段值都会被自动加上引号成为字符串;
sql语句要写为 where name = %(name)s;
同时要使用 cur.execute(sql, {"name":name});
7.前端页面会对password密码进行加密操作(md5/sha1),防止在被抓包时泄露明文密码数据;
使用md5加密某数据的话,无论该某数据大小多大长度多长,加密后的密文长度都是由32位数字和字母混合组成,而且不可逆无法解密;
mysql数据库直接存储password密码的密文数据,在进行password密码比较时,直接进行密文间的比较;
md5加密:(md5加密后的密文长度是由32位数字和字母混合组成;但是md5仍然是不安全,可以通过md5暴力破解)
mysql中指令:select md5(“...”)
终端下指令:md5 -s “...”
sha1加密:(sha1加密后的密文为40数字和字母混合组成)
mysql中指令:select password(“...”)
表中的存储password密码的字段类型应使用char,不应该使用varchar;
passwd char(32) not null 用于存储md5加密后的密文
passwd char(40) not null 用于存储sha1加密后的密文
使用sha1对明文数据
from hashlib import * # import hashlib
sha = sha1() # hashlib.sha1()
pwd = “字符串”
sha.update(pwd.encode()) 把编码后的二进制数据传入,对该明文数据加密为密文
sha_pwd = sha.hexdigest() 以16进制形式返回该密文数据
8.表中的存储password密码的字段类型应使用char定长的字符串,不应该使用varchar变长的字符串;
passwd char(32) not null 用于存储md5加密后的密文为32位数字和字母混合组成
passwd char(40) not null 用于存储sha1加密后的密文为40位数字和字母混合组成
9.now_time = time.strftime(“%Y-%m-%d %H:%M:%S”,time.localtime())
使用'%s'存储 now_time日期时间值
10.new_id = cursor.lastrowid:(lastrowid不是函数)
1.最近一次execute执行的sql语句是插入操作的话,那么 游标对象cursor.lastrowid 和 连接对象connect.insert_id()
都能获取最近一次插入数据的id,即都能获取刚刚插入的最后一条数据的id。
2.游标对象cursor.lastrowid 和 连接对象connect.insert_id()都需要放在 commit之前,否则返回0;
3.执行顺序:1.cursor.execute("INSERT INTO test (nama) VALUES (%s)", name)
2.print(int(cursor.lastrowid)) # 获取最后插入行的主键ID
11.SQL注入问题:
1.原因:给参数化的sql中的'%s'插入sql
2.SQL注入的例子:
1.delete from table where id = '%s'%id:给'%s'插入“;”,变成删除所有数据
2.select * from table where name = '%s' AND passwd = '%s'%(“用户名' or '1'='1”,123)
查询所有数据,此处要求name必须是存在数据库的表中。
3.SELECT * FROM ren WHERE '1'='1' or name='0' AND passwd = "0";
查询所有数据,此处name和passwd都可以是任何值,因为当'1'='1'表示true值,忽略后面所有条件,
直接成功执行该语句查询出表中所有数据。
4.String userName = "1' or '1'='1";
String password = "123456";
sql = "SELECT COUNT(userId) FROM t_user WHERE userName=' " + userName + " ' AND password =' " + password + " ' ";
执行的SQL注入注入后的sql:SELECT COUNT(userId) FROM t_user WHERE userName='1' or '1'='1' AND password ='123456’;
3.解决SQL注入问题:
使用列表:cur.execute(sql,[与字段名同名的参数名]);sql语句中使用的%s两边均不能加上引号
使用元祖:cur.execute(sql,(与字段名同名的参数名,));sql语句中使用的%s两边均不能加上引号
使用字典:cur.execute(sql,{“字段名”:与字段名同名的参数名});sql语句中where 字段名 = %(与字段名同名的参数名)s
12.使用view视图,把sql语句封装到view视图中:
1.创建view视图:create view 视图名 as 要封装的sql语句;
2.创建出来的view视图会和table表处于同一级别,可以使用“show tables”查看到所有的table表和view视图
3.view视图的使用:把view视图当做表名一样使用;
如:select * from 视图名
4.删除view视图:drop view 视图名
一.导入sql文件到数据库中:
1.第一步:mysql -u用户名 -p
2.第二步:create database 数据库名 charset=utf8
3.第三步:use 数据库名
4.第四步:
第一种命令:(source为 mysql下的命令)source (相对启动mysql的路径/绝对路径)sql文件名
第二种命令:(mysql为 非mysql下的命令)mysql -u用户名 -p 数据库名 < (相对启动mysql的路径/绝对路径)sql文件名
二.
1.真静态URL:每个网页有真实的物理路径,也就是真实存在服务器里的;类似 域名/news/2012-5-18/110.html;
2.伪静态URL:类似 域名/course/74.html,这个URL和真静态URL类似。他是通过伪静态规则把动态URL伪装成静态网址;
也是逻辑地址,并不是真实物理存在服务器硬盘里的;
比如:请求路径为 “股票代码.html”,但实际访问的是 update.html;
3.动态URL:带?问号,并带有键值对,其中value更是可以变动的;每个URL只是一个逻辑地址,并不是真实物理存在服务器硬盘里的;
类似 域名/NewsMore.asp?id=5 或者 域名/DaiKuan.php?id=17;
三.
1.在controller模板中:
1.连接mysql数据库API:
1.创建连接数据库对象、游标对象:
connect = connect(......);
cursor = connect.cursor();
2.select查询出表中所有数据,返回一个结果集:
cursor.execute(sql, [参数]);
ret = cursor.fetchall();
3.遍历该结果集中每行数据,每行数据都为一个元祖,一行数据中的每个字段(每列)都是一个元祖中的每个元素:
for item in ret:
item[i] # 取出每个字段值
4.关闭“连接数据库”的对象、关闭游标对象:
cursor. close();
connect.close();
2.读取磁盘上的html模板文件的数据内容:
file = open(“./templates/XXX.html”);
content = file.read();
file.close();
3.把遍历出的结果集中的每行每列数据加载到html模板中的标签中;
4.返回已经加载好动态数据的html文件内容数据到浏览器上显示;
四.当前进行的 跳转页面时,使用的是 伪静态URL:请求路径为 “股票代码.html”,但实际访问的是 update.html
五.导入的模块报错:不存在该模块
1.解决:
1.下载安装该模块:sudo pip3 install 模块名
2.保证当前目录下的代码文件名 不能和 模块名 同名;
3.在终端下,使用python3指令运行py文件时,如果py文件是通过拖动方式,把py文件拖动到python3指令后面的话,
python3指令后面会自动加上该py文件的绝对路径;
2.容易出错的点:使用拖动py文件到终端下执行的方式,需要注意的是,拖动py文件时使用的是绝对路径,
而当 “当前路径” 和 运行的py文件路径 不一样时,而运行的代码中 又需要读取 “运行的py文件所在路径下的”其他文件时,
就会导致 无法读取“运行的py文件所在路径下的”其他文件(模块),原因即 “当前路径” 和 需要读取的文件(模块) 的路径 不一样。