mysql总结

时间:2021-09-02 00:59:33

1.     Mysql

1.1.     Index and table searching of Mysql

1.1.1.     Basic concepts of Mysql and dbms

  • What is sql: a storage for storing data and sql means Structured Query language
  • Contents of sql : title and table
  • DB: database是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数字模型组织、描述和存储,具有较小的冗余,较高的数据独立性和易扩展性,并可为各种用户共享。
  • DMS:database Management System是一种操纵和管理数据库的大型软件,除了mysql外,还有甲骨文oracle的,IBM的db2,sql server,Access
  • Select now()不区分大小写,可以写成SELECT now()或者select now()或者Select now()
  • 支持换行输出(但要以“;”结尾),不能对单词跨行
  • 单行注释:--,多行注释:/*......*/

 

  • 安装mysql步骤:
  1. 这里我安装的时候出现错误5:说明没有用管理员身份运行。
  2. 从官网直接下载最新版(压缩版zip,mysql5.以后就不是msi了)
  3. 解压到本地目录,将mysql/bin文件夹,将间接路径…/bin添加到系统Path的环境变量下
  4. 有时候会出现解压后的mysql包里没有default.ini文件或者data文件夹,同时在C:/ProgramData里也没有
  5. 在命令行输入cmd,用管理员身份运行cmd
  6. 输入mysqld --initialize-insecure --user=mysql,这是在mysql根目录下会出现data文件夹
  7. 接下来输入mysqld –install,系统提示successfully installed表明安装成功
  8. 接下来运行net start MySQL,进入MySQL服务,如果服务启动成功,到下一步
  9. 输入mysql –u root –p,需要输入密码,第一次可以用enter键跳过
  10. 系统提示如下界面,说明mysql安装成功

mysql总结

删库:drop databases zrc: 出现Query OK表明删除成功

三种退出数据库命令行方法:q,exit,quit  

Mysql密码,忘记密码需要删除

mysql总结

  • 数据库命令

Create database 创建数据库

Create database if not exists s3; #如果不存在s3,就创建s3

Create database if not exists s4 character set gbk; #设置s4表的字符为gbk

 

Show database 查看数据库

Show create database; 查看创建的某个数据库

Show warning #查看警告内容

 

alter database s4 character set utf8 更改数据库编码

select database(); 查看进入的数据库序号

 

1.1.2.     数据库类型

主键(表头)ID两个特性:非空且唯一;非空 not null,唯一 unique

Mysql三种类型:数值,日期和时间

数值类型:区别是字节不一样

Char(3) 定义一个固定长度的字符串

Varchar(20) 定义一个不固定长度的字符串长

表的创建:

CREATE TABLE employee.Charger(

             id TINYINT PRIMARY KEY auto_increment,

             name VARCHAR(25),

             gender boolean,

             age INT DEFAULT 19,

             department VARCHAR(20),

             salart DOUBLE(7,2) 

)

Employee:库名

Charger:数据表名

1.1.3.     数据表操作

desc tab_name 查看表结构

show columns from tab_name 查看表结构

show tables 查看当前数据库中的所有的表

show create table tab_name 查看当前数据库表建表数据

alter table employee add is_married 向主键添加一个字段

alter table employee add entry_date date not null 添加入职时间

alter table employee DROP A 删除A列

alter table employee modify age smallint unique 修改字段类型

alter table employee modify age smallint not null default 18 after id; 移动字段位置

修改前:

 mysql总结

 修改后:

mysql总结

 

alter table employee change department depart varchar(20) after salart改字段名称并移动

rename table employee to emp 重命名表头employee 到emp

create table A(id int) id必须有

 

插入表数据语句

  • INSERT INTO emp (id,age,name,gender,salart,is_married) VALUES (1,38,”alex”,0,1700,1)
  • select * from emp; 从emp中查询数据表内容
  • INSERT INTO emp set name=”珊珊” 向table中插入name珊珊

修改表字段名

  • alter table emp change salart salary VARCHAR(20) 修改表table字段名
  • UPDATE emp SET salary=salary 20000, depart=”保安部” WHERE NAME=”yuan”; 修改数据

删除表字段名

  • DELETE From emp WHERE id=2 OR id=3; 删除记录
  • Truncate table emp; 删除记录

上述两者区别:delect逐条删除数据;truncate属于销毁emp表,重新创建新表

Select * from emp 和 desc emp区别:

  • · Select * from emp表示查看字段和数据内容;

mysql总结

  • · desc emp表示查看字段类型

mysql总结

选择并查看数据库:

  • · select database(); 选择数据库
  • · show tables; 查看数据库中的表(有哪些表)
  • · use s3;选择用户

 

例1向mysql中插入一个

CREATE TABLE ExamResult(

       id INT PRIMARY KEY auto_increment,

       name VARCHAR(20),

       JS DOUBLE,

       Django DOUBLE,

       Flask DOUBLE

);

 

INSERT INTO ExamResult VALUES (1,"yuan",98,98,98),

                                                (2,"xialv",35,98,67),

                                                (3,"alex",59,59,62),

                                                (4,"wusir",88,89,82),

                                                (5,"alvin",88,98,67),

                                                (6,"yuan",86,100,55);

mysql总结

选择查看所有name: select name from Examresult;

mysql总结

也可以同时查看多个字段名,比如name JS

 mysql总结

选择name列,去重复:select distinct name from examresult; 此时重复yuan,消除。

mysql总结

显示所有信息:select name,JS,Django,Flask from examresult;

 mysql总结

所有人分数 10分显示:select name,JS 10,Django 10,Flask 10 from examresult;

mysql总结

也可以写成中文名并在数值中加10: select name 姓名,JS 10 as JS成绩,Django 10 as django成绩,Flask 20 from examresult;

 mysql总结

显示成绩大于80的字段和数据:select name,JS from examresult where JS>80; (Where 常用于添加条件用。)

 mysql总结

查看88到100的数据:select name,JS from examresult where JS between 88 and 100; (包括88和100)

 mysql总结

select name,JS from examresult where JS in (88,99,77) 筛选出examresult中有88,99,77的数值

 mysql总结

select name,JS from examresult where name like "y%"; 模糊匹配y开头的name数据

 mysql总结

select name,JS from examresult where name like "a____"; 匹配a 后四位的name数据

 mysql总结

添加一个空值到examresult中 insert into examresult name value (“Tom”)

 mysql总结

Select name from examresult where JS is NULL; 查看name中空值的名字

 mysql总结

select name,JS from examresult order by JS; 按照升值对name排序

 mysql总结

select name,JS from examresult where JS>70 order by JS; name值>70并按照升序排列

select name,JS from examresult where JS>70 order by JS desc; name值>70并按照降序排列

select name,JS Django Flask as 总成绩 from examresult order by 总成绩 desc; 把JS Django Flask赋值给总成绩并按照降序排列;

 mysql总结

select name,JS Django Flask as 总成绩 from examresult where name="yuan" order by 总成绩; 选择上述总成绩并挑选出yuan进行升序排序;

mysql总结

错误语句分析:select JS as JS成绩 from examresult where JS成绩 > 70 不能执行,因为首先处理from examresult, 接着找到JS>70处,但是此时没有JS成绩的字段,所以报错

改变字段名:alter table examresult change JS JS成绩 double;

 

Group_by听所很难!

select name,sum(JS成绩) from examresult group by name; 分组后求和;

mysql总结 

Where和having 区别:where分组前过滤,having分组后过滤;

select name,sum(Django) from examresult group by name having sum(Django)>90;

mysql总结 

select * from examresult having id=3; 选出id=3

等于 select * from examresult where id =3;

mysql总结 

select count(name) from examresult where JS成绩>70; 取出JS成绩>70人的个数

 mysql总结

select sum(JS成绩)/count(name) from examresult; 求JS成绩平均数

select AVG(JS成绩) from examresult; 求JS成绩平均数

两者区别在于sum中考虑空值NULL,AVG过滤掉空值NULL

mysql总结 

select max(JS Django flask) from examresult; 求JS Django flask的最大值

select * from examresult limit 5; 限制显示的数据条数

mysql总结 

以下字符按顺序:

Where条件

Group by field

Having 筛选

Order by field

Limit 限制条数

正则匹配例子:

Select * from examresult where emp_name regexp “yu”;

Select * from examresult where emp_name regexp “yun$”;

1.2.     Matters of mysql

1.2.1.     多表查询值连接查询

创建老师表 学生表

mysql> CREATE TABLE lessonmysql.ClassCharger(

        id TINYINT PRIMARY KEY auto_increment,

        name VARCHAR(20),

       age INT,

       is_married boolean

);

 

mysql> CREATE TABLE Student(

        id INT PRIMARY KEY auto_increment,

        name VARCHAR(20),

        charger_id TINYINT

                              FOREIGN KEY (charger_id) REFERENCES classcharger(id)

) ENGINE=INNODB;

  

相关联的两个数据类型必须一致

插入老师名:

Insert into ClassCharger(name,age,is_married) values (

  "冰冰",52,0),

 ("丹丹",34,0),

 ("玩玩",32,0),

 ("jiji",28,0),

 ("dingding",35,0);

 

插入学生名:

mysql> INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),

        ("alvin2",4),

        ("alvin3",1),

        ("alvin4",3),

        ("alvin5",1),

        ("alvin6",3),

       ("alvin7",2);

 

创建表student2

CREATE TABLE Student2(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT,

FOREIGN KEY (charger_id) REFERENCES classcharger(id)

)ENGINE=INNODB;

 

删除charger冰冰后再次插值,将1改为5

INSERT INTO Student2(name,charger_id) VALUES ("alvin1",2),

("alvin2",4),

("alvin3",5),

("alvin4",3),

            ("alvin5",5),

            ("alvin6",3),

("alvin7",2);

Classcharger主表,student2是子表;

 

添加外键关联:

alter Table student ADD CONSTRAINT abc

            Foreign KEY(charger_id)

references classcharger(id);           

 

删除外键关联:

alter table student2 drop foreign key abc

1.2.2.     级联删除与set NULL

  • 外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动删除

CREATE TABLE Student3(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT,

FOREIGN KEY (charger_id) REFERENCES classcharger(id) on DELETE CASCADE

)ENGINE=INNODB;

 

  • Set NULL:当父表上update/delete记录时,将子表上匹配记录的列设为null

Alter table s3 add constraint s3_fk_cc foreign key (charger_id) References cc(id) on delete set null;

 

1.2.3.     多表查询之连接查询

多表查询:

            连接查询:

                                    内连接:inner join(左连接和右连接)

Select * from tableA where table.tableA_id = tableA.id

                                    外连接:left join right join

(select employee.emp_name,department.dept_name FROM employee LEFT JOIN department on employee.dept_id=department.dept_id; 左连接在左边显示全部字段)

同理左连接显示左边全部字段

                                    全连接:full join(左右字段都显示全)

(select employee.emp_name,department.dept_name FROM employee FULL JOIN department on employee.dept_id=department.dept_id; 左连接在左边显示全部字段

1.2.4.     多表查询之复合查询与子查询

mysql总结 

1.2.5.     Mysql之索引

添加索引:create index index_name on t2(id) $$

删除索引:drop index 索引名 on 表名

如:drop index index_name on t2;

如:drop index index_emp on emp1;

1.3.     Mql事务

Import pymysql

Conn = pymysql.connect()

1.3.1.     Python操作数据库pymysql

Mysql设置密码:

  • 第一次进入mysql时候不用密码,当password出来时,可以直接按enter进入;
  • 用flush.privileges刷新权限;(这一步,必须有,否则会报错)
  • 执行alter user “root”@localhost IDENTIFIED BY ”newpassport”
  • 用flush privileges再次刷新状态
  • 退出mysql,用ctrl z或者”q”
  • 管理员身份重新运行cmd,进入mysql的bin目录,执行net start mysql命令启动mysql
  • 执行mysql –u root –p
  • 输入新密码/修改后密码,登录mysql成功。

这是最后成功的状态:

mysql总结

 

 mysql总结

 

如果需要修改密码需要在重设密码前停止mysql进程,所以要启动下面命令:

Net stop mysql; 停止进程

mysql总结

 

Mysqld --shared-memory –skip-grant-tables 进入免密码模式

mysql总结

退出cmd,重启cmd后,输入net start mysql进入启动流程

输入进入密码操作mysql –u root -p

mysql总结

 

两种操作那个数据库的框架:pymysql和orm

1.3.2.     数据库之事务

事务:指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功,数据库开启事务命令。

1.3.3.     Mysql事务之savepoint

mysql总结 

Savepoint: 保留点,事务处理中设置的临时占位符,可以对它发布回退(区别于整个事务回退rollback)

Savepoint使用,需要配合rollback使用,

如:savepoint delete1---rollback to savepoint