数据库

最新推荐文章于 2024-07-13 10:38:18 发布

ambition_star

最新推荐文章于 2024-07-13 10:38:18 发布

阅读量134

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/ambition_star/article/details/103649416

版权

数据库操作#

1.查询名字为女和攻击力在100 英雄的id

mysql> select name from sanguo where gender='w' union select id from sanguo where attack>100;
+-----------+
| name      |
+-----------+
| 孙尚香    |
| 甄姬      |
| 大乔      |
| 小乔      |
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |
| 6         |
| 7         |
| 8         |
| 9         |
| 10        |
| 11        |
| 12        |
| 13        |
+-----------+

2.#子组查询

mysql> select name from (select * from sanguo where attack>200) as a where  gender='m';
+--------+
| name   |
+--------+
| 关羽   |
| 吕布   |
| 赵云   |
| 周瑜   |
| 盖轮   |
| 赵信   |
| 貂蝉   |
+--------+

3.#执行先后顺序
(7)SELECT
(8)[DISTINCT]
(1)FROM
(3)JOIN
(2)ON
(4)WHERE
(5)GROUP BY
(6)HAVING
(9)ORDER BY
(10)LIMIT

4.#攻击力最大的三国中

mysql> select max(attack) from sanguo;
+-------------+
| max(attack) |
+-------------+
|         360 |
+-------------+

5.#表中一共有多少个英雄

mysql> select count(*) from sanguo;
+----------+
| count(*) |
+----------+
|       13 |
+----------+

6.#蜀国英雄中攻击值大于200的英雄的数量

mysql> select count(*) from (select * from sanguo where country='蜀国') as e where attack>200;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

mysql> select country,avg(attack) from sanguo group by country;
#三个国家的平均攻击力
+---------+-------------+
| country | avg(attack) |
+---------+-------------+
| 魏国    |    219.2500 |
| 蜀国    |    234.0000 |
| 吴国    |    197.5000 |
+---------+-------------+

7.#所有国家的男英雄中英雄数量最多的前2名的国家名称及英雄数量

mysql> select country,count(*) from sanguo where gender='m' group by country order by count(name) desc limit 1;
+---------+----------+
| country | count(*) |
+---------+----------+
| 蜀国    |        4 |
+---------+----------+

8.#找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力

mysql> select country,avg(attack) from sanguo group by country having avg(attack)>105 order by avg(attack) desc limit 2;
+---------+-------------+
| country | avg(attack) |
+---------+-------------+
| 蜀国    |    234.0000 |
| 魏国    |    219.2500 |
+---------+-------------+

9.#表中有那几个国家

mysql> select distinct country from sanguo;
+---------+
| country |
+---------+
| 蜀国    |
| 魏国    |
| 吴国    |
+---------+

10.#表中有个国家

mysql> select count(distinct country) from sanguo ;
+-------------------------+
| count(distinct country) |
+-------------------------+
|                       3 |
+-------------------------+

11.#查询时攻击力翻倍

mysql> select name,attack*2 from sanguo ;
+-----------+----------+
| name      | attack*2 |
+-----------+----------+
| 静姝      |      250 |
| 关羽      |      510 |
| 宋尚香    |      360 |
| 吕布      |      600 |
| 赵云      |      720 |
| 周瑜      |      640 |
| 甄姬      |      400 |
| 曹操      |      250 |
| 大乔      |      220 |
| 小乔      |      220 |
| 盖轮      |      500 |
| 赵信      |      500 |
| 貂蝉      |      504 |
+-----------+----------+

12.#添加索引

mysql> create index name_1 on  sanguo (name);
Query OK, 0 rows affected (0.36 sec)
mysql> desc sanguo;
+---------+----------------------------------+------+-----+---------+----------------+
| Field   | Type                             | Null | Key | Default | Extra          |
+---------+----------------------------------+------+-----+---------+----------------+
| id      | int(11)                          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10)                      | NO   | MUL | NULL    |                |
| gender  | enum('m','w')                    | YES  |     | NULL    |                |
| country | enum('魏国','蜀国','吴国')       | YES  |     | NULL    |                |
| attack  | int(10) unsigned                 | YES  |     | 100     |                |
| defense | int(10) unsigned                 | YES  |     | 10      |                |
+---------+----------------------------------+------+-----+---------+----------------+

13.#扩展: 借助性能查看选项去查看索引性能

show variables like 'profiling';
set profiling = 1;   打开功能 (项目上线一般不打开)
show profiles   查看语句执行信息
#创建表后增加外键
alter table person add constraint dept_fk foreign key(dept_id) references dept(id);

14.#表中添加

mysql> create table b(id int,name char(10),a_id int,constraint aa foreign key (a_id) references a(id));
Query OK, 0 rows affected (0.38 sec)

15.#级联约束
restrict(默认) : on delete restrict on update restrict
当主表删除记录时,如果从表中有相关联记录则不允许主表删除
当主表更改主键字段值时,如果从表有相关记录则不允许更改
cascade :数据级联更新 on delete cascade on update cascade
当主表删除记录或更改被参照字段的值时,从表会级联更新
set null : on delete set null on update set null
当主表删除记录时,从表外键字段值变为null
当主表更改主键字段值时,从表外键字段值变为null
no action
同 restrict,都是立即检查外键限制

16.#可以两张表同时查询

mysql> select dname,name from dept,person where person.id=dept.id;
+-----------+-----------+
| dname     | name      |
+-----------+-----------+
| 人事部    | 米线      |
| 秘书部    | 蘑菇头    |
| 技术部    | 王炸      |
| 纪检部    | 茅台      |
| 市场部    | 球球      |
| 销售部    | 冷檬      |
+-----------+-----------+
6 rows in set (0.00 sec)

17.#内连接查询

mysql> select person.name,dept.dname from person inner join dept on person.id=dept.id;
+-----------+-----------+
| name      | dname     |
+-----------+-----------+
| 米线      | 人事部    |
| 蘑菇头    | 秘书部    |
| 王炸      | 技术部    |
| 茅台      | 纪检部    |
| 球球      | 市场部    |
| 冷檬      | 销售部    |
+-----------+-----------+
6 rows in set (0.00 sec)

18.#左连接

mysql> select name,dname from person left join dept on person.id=dept.id;
+-----------+-----------+
| name      | dname     |
+-----------+-----------+
| 米线      | 人事部    |
| 蘑菇头    | 秘书部    |
| 王炸      | 技术部    |
| 茅台      | 纪检部    |
| 球球      | 市场部    |
| 冷檬      | 销售部    |
+-----------+-----------+
6 rows in set (0.00 sec)

19.#创建视图

mysql> create view nnme as select name from person;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from nnme;
+-----------+
| name      |
+-----------+
| 米线      |
| 蘑菇头    |
| 王炸      |
| 茅台      |
| 球球      |
| 冷檬      |
+-----------+
6 rows in set (0.00 sec)

20.#删除视图

mysql> drop view nnme;
Query OK, 0 rows affected (0.00 sec)

21.#修改视图

mysql> alter view nnme as  select name from person;
Query OK, 0 rows affected (0.06 sec)

23.#函数

mysql> create function st1() returns int
begin
return (select age from person where  id=1);
end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select st1();
+-------+
| st1() |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)

24.#带参数函数

mysql> delimiter //
mysql> create function st2(uid int)
    -> returns int
    -> begin
    -> return (select age from person where id=uid);
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select st2(1);
+--------+
| st2(1) |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

25.#删除函数

mysql> drop function st;
Query OK, 0 rows affected (0.00 sec)

26.#存储过程

mysql> delimiter $$
mysql> create procedure st()
    -> begin
    -> select * from dept;
    -> select * from person;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call st();
+----+-----------+
| id | dname     |
+----+-----------+
|  1 | 人事部    |
|  2 | 秘书部    |
|  3 | 技术部    |
|  4 | 纪检部    |
|  5 | 市场部    |
|  6 | 销售部    |
+----+-----------+
6 rows in set (0.00 sec)

+----+-----------+------+------+---------+
| id | name      | sex  | age  | sept_id |
+----+-----------+------+------+---------+
|  1 | 米线      | w    |   20 |       1 |
|  2 | 蘑菇头    | m    |   28 |       2 |
|  3 | 王炸      | m    |   25 |       3 |
|  4 | 茅台      | m    |   30 |       4 |
|  5 | 球球      | w    |   25 |       5 |
|  6 | 冷檬      | w    |   23 |       6 |
+----+-----------+------+------+---------+
6 rows in set (0.00 sec)

27.#存储过程参数

mysql> delimiter $$
mysql> create procedure st1(in num int) begin select num ; set num=100 ;select num; end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call st1(1);
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

28.*************存储过程三个参数的区别

IN 类型参数可以接收变量也可以接收常量,传入的参数在存储过程内部使用即可,但是在存
储过程内部的修改无法传递到外部。
OUT 类型参数只能接收一个变量,接收的变量不能够在存储过程内部使用(内部为
NULL),但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的,所以外部
可以获取这个修改后的值。
INOUT类型参数同样只能接收一个变量,但是这个变量可以在存储过程内部使用。在存储过
程内部的修改也会传递到外部。
设置变量方法: set @[变量名] = 值; 表示这是一个用户变量,使用时用@[变量名]。 在函数
内部设置declare [变量名] [变量类型]为局部变量,局部变量可以使用set赋值或者着使用into

关键字
29.函数和存储过程区别

函数有且只有一个返回值,而存储过程不能有返回值。
函数只能有输入参数,而存储过程可以有in,out,inout多个类型参数。
存储过程中的语句功能更丰富,实现更复杂的业务逻辑,可以理解为一个按照预定步骤调用的执
行过程,而函数中不能展示查询结果集语句,只是完成查询的工作后返回一个结果,功能针对性
比较强。
存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调
用。

30.**事务**：一件事从开始发生到结束的过程
事务四大特性
1. 原子性(atomicity)
一个事务必须视为一个不可分割的最小工作单元,对于一个事务来说,不可能只执行其中的一部
分操作,整个事务中的所有操作要么全部提交成功,要么全部失败回滚
2. 一致性(consistency)
事务完成时,数据必须处于一致状态,数据的完整性约束没有被破坏。
3. 隔离性(isolation)
数据库允许多个并发事务同时对其数据进行读写和修改的能力,而多个事务相互独立。隔离性可
以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
4. 持久性(durability)
一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不
会丢失。
31.**#开启事务**
mysql>begin; # 方法1
mysql>start transaction; # 方法2

#提交事务保存事务全部操作   删除表无法恢复
mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
#回滚取消自己的操作
mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!
若删除表有外键约束关系，若要删除表为主表，则主表记录删除后相应的从表记录也被删除，若被恢复则从表记录也被恢复。

32.**#事务隔离**
隔离级别
1.读未提交:read uncommitted
事物A和事物B,事物A未提交的数据,事物B可以读取到
这里读取到的数据叫做“脏数据”
这种隔离级别最低,这种级别一般是在理论上存在,数据库隔离级别一般都高于该级别
2.读已提交:read committed
事物A和事物B,事物A提交的数据,事物B才能读取到
这种隔离级别高于读未提交
换句话说,对方事物提交之后的数据,我当前事物才能读取到
这种级别可以避免“脏数据”
这种隔离级别会导致“不可重复读取”
3.可重复读:repeatable read
事务A和事务B,事务A提交之后的数据,事务B读取不到
事务B是可重复读取数据
这种隔离级别高于读已提交
MySQL默认级别
虽然可以达到可重复读取,但是会导致“幻像读”
3.串行化:serializable
事务A和事务B,事务A在操作数据库时,事务B只能排队等待
这种隔离级别很少使用,吞吐量太低,用户体验差
这种级别可以避免“幻像读”,每一次读取的都是数据库中真实存在数据,事务A与事务B
串行,而不并发
``
33.**#目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-**
科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

各种范式呈递次规范,越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细,一个数据
库中需要的表也就越多,此时多个表联接在一起的花费是巨大的,尤其是当需要连接的两张或者多张
表数据非常庞大的时候,表连接操作几乎是一个噩梦,这严重地降低了系统运行性能。所以通常数据
库设计遵循第一第二第三范式,以避免数据操作异常,又不至于表关系过于复杂。
范式简介:

第一范式: 数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等组合的
数据项。简单来说要求数据库中的表示二维表,每个数据元素不可再分。
例如: 在国内的话通常理解都是姓名是一个不可再拆分的单位,这时候就符合第一范式;但是在
国外的话还要分为FIRST NAME和LAST NAME,这时候姓名这个字段就是还可以拆分为更小的单
位的字段,就不符合第一范式了。
第二范式: 第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分,所有属
性依赖于主属性。即选取一个能区分每个实体的属性或属性组,作为实体的唯一标识,每个属性
都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。
第三范式: 在第二范式的基础上属性不传递依赖,即每个属性于其它非主属性。要求一个关系中
不包含已在其它关系已包含的非主关键字信息。其实简单来说就是合理使用外键,使不同的表中
不要有重复的字段就好了。

```sql
34.1**、查看所有存储引擎**
mysql> show engines;
2、**查看已有表的存储引擎**
mysql> show create table 表名;
3、**创建表指定**
create table 表名(...)engine=MyISAM,charset=utf8,auto_increment=10000;
4、**已有表指定**
alter table 表名 engine=InnoDB;

mysql> explain select * from dept where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | dept  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

35.EXPLAIN主要字段解析:
table:显示这一行的数据是关于哪张表的
type:这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL,一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys:显示可能应用在这张表中的索引。如果为空,表示没有可能应用的索引。
key:实际使用的索引。如果为NULL,则没有使用索引。
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
rows:MySQL认为必须检索的用来返回请求数据的行数

36.SQL优化
尽量选择数据类型占空间少,在where ,group by,order by中出现的频率高的字段建立索引
尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段
少使用like %查询,否则会全表扫描
子查询优化为join查询
控制使用自定义函数
单条查询最后添加 LIMIT 1,停止全表扫描
where子句中不使用 != ,否则放弃索引全表扫描
尽量避免 NULL 值判断,否则放弃索引全表扫描
优化前:select number from t1 where number is null;
优化后:select number from t1 where number=0;
在number列上设置默认值0,确保number列无NULL值
尽量避免 or 连接条件,否则会放弃索引进行全表扫描,可以用union代替
优化前:select id from t1 where id=10 or id=20;
优化后: select id from t1 where id=10 union all
select id from t1 where id=20;
尽量避免使用 in 和 not in,否则会全表扫描
优化前:select id from t1 where id in(1,2,3,4);
优化后:select id from t1 where id between 1 and 4;

mysql> create table depp select dname from dept ;
Query OK, 6 rows affected (0.34 sec)
Records: 6  Duplicates: 0  Warnings: 0
#复制表
mysql> select * from depp;
+-----------+
| dname     |
+-----------+
| 人事部    |
| 秘书部    |
| 技术部    |
| 纪检部    |
| 市场部    |
| 销售部    |
+-----------+
6 rows in set (0.00 sec)

37.#数据库备份

mysql> mysqldump -uroot -p zxz_1 > /home/tarena/桌面/1906/month02/进程通讯/zxz_1.sql;
1. 备份命令格式
mysqldump -u用户名 -p 源库名 > ~/stu.sql
--all-databases 备份所有库
db_name 备份单个库
-B 库1 库2 库3 备份多个库
库名 表1 表2 表3 备份指定库的多张表
2. 恢复命令格式
mysql -uroot -p 目标库名 < stu.sq
用户权限管理

38.#×××××××××开启MySQL远程连接×××××××××××××

更改配置文件,重启服务!
1.sudo su
2.cd /etc/mysql/mysql.conf.d
3.cp mysqld.cnf mysqld.cnf.bak
4.vi mysqld.cnf #找到44行左右,加 # 注释
#bind-address = 127.0.0.1
[mysqld]
character_set_server = utf8
5.保存退出
6.service mysql restart
7.修改用户表host值
use mysql;
update user set host='%' where user='root';
8.刷新权限
flush privileges;
添加授权用户
1. 用root用户登录mysql
mysql -uroot -p123456
2. 添加用户 % 表示自动选择可用IP
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
3. 授权
grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
4. 刷新权限
flush privileges;
权限列表
all privileges 、select 、insert ,update,delete,alter等。
库.表 : *.* 代表所有库的所有表
示例
1. 创建用户
mysql>create user
'work'@'%'
identified by '123';
2. 添加授权用户work,密码123,对所有库的所有表有所有权限
mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
mysql>flush privileges;
3. 添加用户duty,密码123,对db2库中所有表有所有权限
mysql>grant all privileges on books.* to 'duty'@'%' identified by '123' with grant option;
mysql>flush privileges;

-----------------------------------#pymysql使用流程------------------------------------------

1. 建立数据库连接(db = pymysql.connect(...))
2. 创建游标对象(cur = db.cursor())
3. 游标方法: cur.execute("insert ....")
4. 提交到数据库或者获取数据 : db.commit()/db.fetchall()
5. 关闭游标对象 :cur.close()
6. 断开数据库连接 :db.close()
#常用函数：
db = pymysql.connect(参数列表)
host :主机地址,本地 localhost
port :端口号,默认3306
user :用户名
password :密码
database :库
charset :编码方式,推荐使用 utf8
数据库连接对象(db)的方法
cur = db.cursor() 返回游标对象,用于执行具体SQL命令
db.commit() 提交到数据库执行
db.rollback() 回滚,用于当commit()出错是回复到原来的数据形态
db.close() 关闭连接
游标对象(cur)的方法
cur.execute(sql命令,[列表]) 执行SQL命令
cur.executemany(sql命令,[data]) 根据数据列表项多次执行SQL命令,一般用于写操作。
cur.fetchone() 获取查询结果集的第一条数据,查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录,返回结果为元组嵌套元组, ((记录1),(记录2))。
cur.fetchall() 获取所有查找到的记录,返回结果形式同上。
cur.close() 关闭游标对象

ambition_star

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库

数据库操作#1.查询名字为女和攻击力在100 英雄的idmysql> select name from sanguo where gender='w' union select id from sanguo where attack>100;+-----------+| name |+-----------+| 孙尚香 || 甄姬 || 大乔 ...
复制链接

扫一扫