my python day8

2023-08-12 19:18:10

一、数据库

数据是存放数据的仓库，是长期存在才计算机内的有组织，可共享的数据集合。

数据库管理系统是操作和管理数据库的大型软件。

如MySQL、Oracle、SQLite、Access、MS SQL Server

mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。

数据库特点：1.数据库结构化

　　　　　　2.数据共享，冗余度低，易扩散

　　　　　　3.数据独立性高

　　　　　　4.数据有DBMS统一管理和管理

　　　　　　　　a.数据的安全性保护

　　　　　　　　b.数据的完整性检查

　　　　　　　　c.并发控制

　　　　　　　　d.数据库恢复

二、mysql

mysql是一个关系型数据库管理系统。

数据库管理软件分类：1.关系型数据库。如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用

　　　　　　　　　　2.非关系型数据库：mongodb，redis，memcache

SQL语句主要用于存取数据、查询数据、更新数据和管理数据库系统，SQL主要分为3种类型数据

1.DDL语句，数据库定义语句，数据库、表、视图、索引、存储过程，如CREATE ALTER DROP

2.DML语句，数据库操作语句，插入数据INSERT 删除数据DROP 更新数据UPDATE 查询数据SELECT

3.DCL语句，数据控制语句，例如控制用户的访问权限GRANT、REVOKE

登入mysql mysql -h 127.0.0.1 -P 3306 -u root -p 密码

1⃣️、创建数据库

CREATE DATABASE [IF NOT EXIST] 数据库名. [CHARACTER SET UTF-8];

2⃣️、数据库相关操作

SHOW  DATABASES; #显示所有的数据库
SHOW CREATE DATABASE 数据库名。#显示创建的一个数据库
SELECT DATABASE()  #
USE 数据库名  #选择数据库
DROP DATABASE 数据库名 #删除数据库
ALTER DATABASE 数据库名 CHARACTER SET UTF-8

3⃣️、表

表相当于文件，表中的一条记录就相当于文件的一条内容，不同的是，表中的记录有对应的标题，称为表的字段。

id、name、age、sex 是字段，一行一行内容为记录。

4⃣️创建表

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的


CREATE TABLE tab_name (field1 type [完整约束性条件],field2 type）；

CREATE TABLE student(id INT PRIMARY key AUTO_INCREMENT,  
                name VARCHAR(20),
                 age INT ,
                 result INT );

5⃣️ 表的相关操作

SHOW TABLES;  #显示所有的表
SHOW CREATE TABLE TABLE 表名  #查看表详细结构
DESC 表名   #查看表结构
DROP 表名 #删除表

6⃣️数据类型

1.整数类型

TINYINT SMALLINT MEDIUMINT INT BIGINT

2.浮点型

定点数类型 DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE。

#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] M为最大个数，D为小数点后几位

3.日期类型

DATE TIME DATETIME TIMESTAMP YEAR

 YEAR
            YYYY（1901/2155）

        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

        TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

        DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

        TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

4.字符串类型

#char类型：定长，简单粗暴，浪费空间，存取速度快 char(10)

#varchar类型：变长，精准，节省空间，存取速度慢.

5.枚举类型和集合类型

enum 单选只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

7⃣️非限制性约束条件

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录。主键的特征 非空且唯一。= not null + unique 
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;

或在创建表之后 alter table employee add constraint fk_name foreign key(dpt_id)
references department(id)

8⃣️修改表字段

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

View Code

9⃣️表记录的操作

使用INSERT实现数据的插入
UPDATE实现数据的更新
使用DELETE实现数据的删除
使用SELECT查询数据以及。

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

#更新表记录
语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

删除表记录
语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

练习：
    更新MySQL root用户密码为mysql123
    删除除从本地登录的root用户以外的所有用户

表记录查询
SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数


2⃣️.对于where约束
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3.in(10,20,30)在10、20、30之中
4.like 'egon%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
3⃣️.groupby 分组
分组发生在wherw之后，
才 mysql 8.0中会出现

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 's444.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

出现该问题的解决方案：1.检查mysql的版本。select @@sql_mode;

　　　　　　　　　　　查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

　　　　　　　　　　2.去掉ONLY_FULL_GROUP_BY，重新设置值。

set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER ,NO_ENGINE_SUBSTITUTION'; 　　　　　　　　　　3.上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行： set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 聚合函数

SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

4⃣️ having 筛选

　　#！！！执行优先级从高到低：where > group by > having

　#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

  #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数


5⃣️ order by 排序   asc 升序。desc 降序

　　 SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

6⃣️ limit。限制显示几条

　　select * from student limit 1,2 ; 跳过1，再显示两条

正则表达式。

SELECT * FROM employee WHERE name REGEXP '^ale';

码农公寓

相关文章