mysql 学习笔记

最新推荐文章于 2023-05-19 17:34:51 发布

xiangzilong

最新推荐文章于 2023-05-19 17:34:51 发布

阅读量307

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_36270681/article/details/104256132

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

https://www.cnblogs.com/majj/category/1231415.html 笔记资料
Window版本安装

1、下载

       MySQL Community Server 5.7.16

       http://dev.mysql.com/downloads/mysql/

2、解压

如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:\mysql-5.7.16-winx64

3、初始化

MySQL解压后的 bin 目录下有一大堆的可执行文件，执行如下命令初始化数据：

       cd c:\mysql-5.7.16-winx64\bin

       mysqld --initialize-insecure

4、启动MySQL服务

执行命令从而启动MySQL服务

       # 进入可执行文件目录
       cd c:\mysql-5.7.16-winx64\bin

       # 启动MySQL服务

       mysqld
       # 放后台运行mysql
       start /b mysqld

       查看mysql 是否启动
       netstat -aon | findstr 3306 或者查看进程pid tasklist | findstr mysql
       杀死进程
       taskkill /F /PID ‘pid号’

5、启动MySQL客户端并连接MySQL服务

由于初始化时使用的【mysqld --initialize-insecure】命令，其默认未给root账户设置密码

       # 进入可执行文件目录
       cd c:\mysql-5.7.16-winx64\bin

       # 连接MySQL服务器
       mysql -u root -p

       # 提示请输入密码，直接回车

#数据库操作
       查看数据库的基础信息，版本，端口之类
           \s；
       为了统一字符编码，修改mysql的配置文件my.ini 关闭数据库添加完再启动
           创建 D:\mysql\mysql-8.0.19-winx64\my.ini

[mysqld]
# 设置mysql的安装目录 **后面的路径一定是安装sql的目录（自己电脑的）**
basedir=D:\mysql\mysql-8.0.19-winx64
# 设置mysql数据库的数据的存放目录，必须是data
datadir=D:\mysql\mysql-8.0.19-winx64\data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY
# mysql端口
port=3306
# 字符集
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
#修改密码验证方式
default_authentication_plugin = mysql_native_password
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

       #创建表(设计表)
           create table t1(id int,name char(10));

       #查看表的属性，编码类型，引擎
           show create table t1;

       插入表数据
           insert into t1(id,name) values(1,'tom'),(2,'龙哥');
           如果全局设置没有改为utf-8则创建表需要手动添加
           create table t1(id int,name char(10)) default charset='utf-8'

       查看表数据
           select id from t1; select name from t1;
           select * from t1;


       删除表
           drop table t1;

查询当前登录用户
select user();

初始化root 密码
mysqladmin -u root -p password "123456"

切换库
use student

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

       修改数据库
       alter database student charmset utf8；

       #用户管理
       创建用户
           create user '用户名'@'IP地址' identified by '密码';
       删除用户
           drop user '用户名'@'IP地址';
       修改用户
           rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
       修改密码
           set password for '用户名'@'IP地址' = Password('新密码')

       5、授权管理
           show grants for '用户'@'IP地址' -- 查看权限
           grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
           revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限

       创建用户并授权
           grant select privileges on class.student to 'zilong'@'%' identified by '123456';
           对网段授权在mysql可能用不了在mariadb可以
           grant all privileges on *.* to 'zilong'@'192.168.3.%' identified by '123456';


       密码忘记，重置root密码
           #关闭数据库
           # 启动免授权服务端
           mysqld --skip-grant-tables

# 客户端
mysql -u root -p

           # 修改用户名密码
           update mysql.user set authentication_string=password('666') where user='root';
           刷新权限
           flush privileges;
       https://www.bilibili.com/video/av51299056?p=354

       sql的基础语法
           1.对库的操作
               查看所有库
               show databases;

               查看所有库
                   show databases;

               进入到mysql库
                   use mysql;

               查看当前所在库的位置
                   show database();

               删除库
               drop db1;

               #创建数据库
                   create database student;
                   # utf-8
                   CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
               # gbk
                   CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

           2.对表操作
               #创建表(设计表)
                   create table t1(id int,name char(10));

               如果全局设置没有改为utf-8则创建表需要手动添加
                   create table t1(id int,name char(10)) default charset='utf-8'

               #查看表的属性，编码类型，引擎
                   show create table t1;

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

               查看表结构；
                   desc t1;

               # 在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少
                   mysql> select x,char_length(x),y,char_length(y) from t1;

           3,对数据：

               插入表数据
                   insert into t1(id,name) values(1,'tom'),(2,'龙哥');

                   insert into t1 values(3,'武汉')



               查看表数据
                   select id from t1;
                   select name from t1;
                   select * from t1;
                   select id,name from student.t1 where id=2;

               修改表数据
                   #id 等于3 的人改成tom
                   update student.t1 set name='tom' where id = 3;


               删除表
                   drop table t1;

                   删除t2表记录（数据）；
                       delete from t2;

                   删除id =3 的行
                       delete from t2 where id = 3;

               复制表
                   创建t5，并复制t1 的表结构和表数据
                       create table test.t5 select * from student.t1;
                   只复制t1 的表结构，不要表数据 1>2 条件不成立
                       create table test.t6 select * from student.t1 where 1>2;
                       create table test.t7 like student.t1

           3.存储引擎
               指定表类型/存储引擎
                   create database test;
                   create table test.t1(id int)engine=Innodb;       特点：支持事物
                   create table test.t2(id int)engine=myisam;       特点：不支持事物
                   create table test.t3(id int)engine=memory; 特点：内存引擎，服务重启数据消失
                   create table test.t4(id int)engine=blackhole; 特点：黑洞引擎，数据插入立马消失，不存在记录

           三.3.数据类型
                   #1. 数字：
                       整型：tinyint int bigint
                       小数：
                           float ：在位数比较短的情况下不精准
                           double ：在位数比较长的情况下不精准
                               0.000001230123123123
                               存成：0.000001230000

                           decimal：（如果用小数，则用推荐使用decimal）
                               精准
                               内部原理是以字符串形式去存

                           创建t1 规定x字段为tinyint数据类型（默认是有符号的）
                               mysql> create table t1(x tinyint);

                           # 创建表时定义记录的字符为无符号类型（0,255），使用unsigned
                               mysql> create table t2(x tinyint unsigned);

                   #2. 字符串：
                       char（10）：简单粗暴，浪费空间，存取速度快
                           　　　　root存成root000000
                       varchar：精准，节省空间，存取速度慢

sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别比如地址或描述信息

>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。

                   #3. 时间类型：
                       最常用：datetime 时间函数 now() 获取当前时间
                           create table t8(d date , t time ,dt datetime);
                           insert into t8 values(now(),now(),now());

                           例子：创建一个学生表，id 姓名出生年份出生年月日进班时间学习时间
                           create table student(
                           id int unsigned,
                           name varchar(30),
                           born_year year,
                           birth date,
                           born_time time,
                           xuexi_time datetime
                           );

                           insert into student values(1,'tom','2020','2020-01-01','07:06:05','2020-01-01 08:20:30');

                   #4. 枚举类型与集合类型
                   　　 enum 和set
                           create table consumer(
                           id int,
                           name varchar(50),
                           sex enum('male','female','other'),
                           level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一
                           fav set('play','music','read','study') #在指定范围内，多选多
                           );

                           insert into consumer values
                           (1,'赵云','male','vip2','read,study'),
                           (2,'赵云2','other','vip4','play');

           4.约束（表的设计）
               作用：保证数据的一致性和完整性
               表的设计：
                   1.not null 与default
                       create table tb1(
                       nid int not null defalut 2,
                       num int not null
                       );

                   2，unique         某个字段唯一，比如手机号，身份证，部门
                       #单列唯一多列唯一
                       create table department(
                       id int,
                       name char(10) unique
                       );
                       特殊：组合唯一，只要两列记录，有一列不同，既符合联合唯一的约束
                       mysql> create table services(
                               id int,
                               ip char(15),
                               port int,
                               unique(id),
                               unique(ip,port)
                           );



                   3，primary key（索引优化查询）
                   主键：不为空并且唯一，一个表只允许一个 innodb 引擎必须有一个（通常是id ,cid，sid）
                   但是可以做复合主键
                       create table stu(
                       id int primary key auto_increment, #自增长
                       name varchar(15),
                       sex enum('male','female','other') not null,
                       age tinyint not null default 18,
                       phone char(11) unique,
                       fav set('play','music','read','study')
                       );

                       insert into stu(name,sex,phone,fav) values('tom','male','12345678901','music')

                   4，auto_increment 自增长
                   5，foreign key     外键：表关联
                       一对多：
                           dep：被关联表，主表，最先创建
                               create table dep(
                               id int primary key auto_increment,
                               name varchar(20) not null,
                               des varchar(30) not null
                               );

                               insert into dep(name,des) values
                               ('IT','IT技术部门'),
                               ('销售部','销售部门'),
                               ('财务部','花钱太多部门'),
                               ('公关部','美女太多部门')
                               ;


                           emp:关联表，从表
                               create table emp(
                               eid int primary key auto_increment,
                               name varchar(10),
                               age int not null,
                               dep_id int not null,
                               constraint fk_dep foreign key(dep_id) references dep(id)
                               on delete cascade #同步删除
                               on update cascade #同步更新
                               );

                               insert into emp values
                               (1,'tom',18,1,1),
                               (2,'jack',20,2,2),
                               (3,'lili',18,3,3),
                               (4,'xixi',20,4,4);

                       外键变种：
                           多对多：3表联合
                               #书名
                               create table book(
                               id int primary key auto_increment,
                               name varchar(15)
                               );
                               #作者
                               create table autho(
                               id int primary key auto_increment,
                               name varchar(10)
                               );
                               #多表关联
                               create table autho_book(
                               id int primary key auto_increment,
                               book_id int not null,
                               autho_id int not null,
                               constraint fk_book foreign key(book_id) references book(id)
                               on delete cascade
                               on update cascade ,
                               constraint fk_autho foreign key(autho_id) references autho(id)
                               on delete cascade #同步删除
                               on update cascade #同步更新
                               );

                       作业：

                       班级表 ---学生表
                       老师表 ---课程表
                       成绩表 ----课程表id+学生表id

                           #班级表
                               create table class(
                               cid int primary key auto_increment,
                               caption varchar(10) not null
                               );
                               insert into class values
                               (1,'三年二班'),
                               (2,'一年三班'),
                               (3,'三年一班');
                           #学生表
                               create table student(
                               sid int primary key auto_increment,
                               sname varchar(10) not null,
                               gender enum('男','女') not null,
                               class_id int not null,
                               constraint fk_class foreign key(class_id) references class(cid)
                               on delete cascade
                               on update cascade
                               );
                               insert into student values
                               (1,'钢蛋','女',1),
                               (2,'铁锤','女',1),
                               (3,'三炮','男',2);
                           #老师表
                               create table teacher(
                               tid int primary key auto_increment,
                               tname varchar(10) not null
                               );
                               insert into teacher values
                               (1,'波多野结衣'),
                               (2,'苍井空'),
                               (3,'饭岛爱一');
                           #课程表
                               create table course(
                               cid int primary key auto_increment,
                               cname varchar(10),
                               teacher_id int not null,
                               constraint fk_teacher foreign key(teacher_id) references teacher(tid)
                               on delete cascade
                               on update cascade
                               );
                               insert into course values
                               (1,'生物',1),
                               (2,'体育',1),
                               (3,'物理',2);
                           成绩表
                               create table score(
                               sid int primary key auto_increment,
                               student_id int,
                               course_id int,
                               number int,
                               constraint fk_student foreign key(student_id) references student(sid)
                               on delete cascade
                               on update cascade ,
                               constraint fk_course foreign key(course_id) references course(cid)
                               on delete cascade
                               on update cascade
                               );
                               insert into score values
                               (1,1,1,60),
                               (2,1,2,59),
                               (3,2,2,100);

                   单表查询：
                           一、单表查询的语法
                           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.限制结果的显示条数

                           例子：部门表
                               create table employee(
                               id int primary key auto_increment,
                               name varchar(20) not null,
                               sex enum('male','female') not null default 'male', #大部分是男的
                               age int(3) unsigned not null default 28,
                               hire_date date not null,
                               post varchar(50),
                               post_comment varchar(100),
                               salary double(15,2),
                               office int,#一个部门一个屋
                               depart_id int
                               );
                               #插入记录
                               #三个部门：教学，销售，运营
                               insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values
                               ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
                               ('alex','male',78,'20150302','teacher',1000000.31,401,1),
                               ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
                               ('yuanhao','male',73,'20140701','teacher',3500,401,1),
                               ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
                               ('jingliyang','female',18,'20110211','teacher',9000,401,1),
                               ('jinxin','male',18,'19000301','teacher',30000,401,1),
                               ('xiaomage','male',48,'20101111','teacher',10000,401,1),

                               ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
                               ('丫丫','female',38,'20101101','sale',2000.35,402,2),
                               ('丁丁','female',18,'20110312','sale',1000.37,402,2),
                               ('星星','female',18,'20160513','sale',3000.29,402,2),
                               ('格格','female',28,'20170127','sale',4000.33,402,2),

                               ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
                               ('程咬金','male',18,'19970312','operation',20000,403,3),
                               ('程咬银','female',18,'20130311','operation',19000,403,3),
                               ('程咬铜','male',18,'20150411','operation',18000,403,3),
                               ('程咬铁','female',18,'20140512','operation',17000,403,3)
                               ;

                               查询年龄18 28 38的
                                   select * from employee where age in(18,28,38);
                               查询name 里面包含x 的人
                                   select * from employee where name like 'x%'; #x 开头
                                   select * from employee where name like '%x%'; #包含x
                               查询分组
                                   SELECT post from employee GROUP BY post
                               查询每个组有多少人
                                   SELECT COUNT(id),post from employee GROUP BY post
                               查询每个组发多少工资
                                   SELECT SUM(salary),post from employee GROUP BY post
                               查询单个组里面的最高薪资
                                   SELECT max(salary),post from employee GROUP BY post
                               查询单个组里面的最低薪资
                                   SELECT min(salary),post from employee GROUP BY post
                               统计男人和女人有多少人
                                   SELECT COUNT(1) ,sex from employee GROUP BY sex
                               查询每个组发工资的平均值
                                   SELECT avg(salary),post from employee GROUP BY post

                               HAVING 二次筛选
                                   查询post分组里面人数大于2的
                                       select post,group_concat(name),count(id) from employee group by post HAVING COUNT(id) <2 ;
                           作业：
                               1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
                                       select post,group_concat(name),count(id) from employee group by post HAVING COUNT(id) <2 ;
                               2. 查询各岗位平均薪资大于10000的岗位名、平均工资
                                       select post,avg(salary) from employee group by post HAVING avg(salary) > 10000;
                               3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
                                       select post,avg(salary) from employee group by post HAVING avg(salary) > 10000 and avg(salary) <20000;

                           升序和降序 RDER BY age ASC,id DESC;
                               1. 查询所有员工信息，先按照age升序排序，id 降序排序
                                   SELECT * from employee ORDER BY age ASC,id DESC;
                               2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
                                   select group_name(name),avg(salary) from employee group by post having
                               3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列　
                                   select GROUP_CONCAT(name), post ,avg(salary) from employee group by post HAVING avg(salary) > 10000;

                           limit 限制查询的记录数:
                               select * from employee limit 0,3;

                   多表查询：
                           部门表
                           create table department(
                           id int,
                           name varchar(20)
                           );
                           员工表
                           create table employee(
                           id int primary key auto_increment,
                           name varchar(20),
                           sex enum('male','female') not null default 'male',
                           age int,
                           dep_id int
                           );
                           insert into department values
                           (200,'技术'),
                           (201,'人力资源'),
                           (202,'销售'),
                           (203,'运营');

                           insert into employee(name,sex,age,dep_id) values
                           ('egon','male',18,200),
                           ('alex','female',48,201),
                           ('wupeiqi','male',38,201),
                           ('yuanhao','female',28,202),
                           ('nvshen','male',18,200),
                           ('xiaomage','female',18,204)
                           ;

                       外链接#多表组合，先求笛卡尔积，在找到2个id相等的
                           select * from employee,department where employee.dep_id = department.id;
                       #查询公司技术部门名字是员工名字是 egon
                           select department.name,employee.name from employee,department where employee.dep_id = department.id HAVING employee.name = 'egon'
                           and department.name = '技术';

                       内链接公司多用）：多表组合，先求笛卡尔积，在找到2个id相等的（公司多用）
                       SELECT * from department INNER JOIN employee on employee.dep_id = department.id;


                       左链接left
                           select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
                       右链接
                           select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

                       全链接
                           select * from employee left join department on employee.dep_id = department.id
                           union select * from employee right join department on employee.dep_id = department.id;

               符合条件查询
                       以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，
                       即找出年龄大于25岁的员工以及员工所在的部门
                           select employee.name ,department.name, employee.age from employee left join department on employee.dep_id = department.id
                           where employee.age >25;

                       以内连接的方式查询employee和department表，并且以age字段的升序方式显示。
                           select * from employee left join department on employee.dep_id = department.id order BY age ASC;
                           select * from employee left join department on employee.dep_id = department.id HAVING age > 25 order BY age ASC;

               嵌套查询：将查询的结果当作条件
                       #查询平均年龄在25岁以上的部门名
                       select id,name from department where id in ( SELECT dep_id from employee GROUP BY dep_id HAVING avg(age) >25);

                       # 查看技术部员工姓名
                           SELECT * from employee where dep_id in
                           (select dep_id from employee LEFT JOIN department on employee.dep_id = department.id HAVING dep_id =200 );

                       #查看不足1人的部门名
                           select name from department where id not in (SELECT dep_id from employee GROUP BY dep_id)

pychar 连接数据库的方法：
#第一步要先安装模块pymysql pip install pymysql
import pymysql
user = input('请输入用户:')
pwd = input('请输入密码:')
#建立连接
conn = pymysql.connect(
host='localhost',
user='root',
password="123456",
database='db13',
port=3306,
charset='utf8'
)
#创建游标
cur = conn.cursor()
               #sql = 'select * from userinfo where name = "%s" and password = "%s"' % (user,pwd)
               #sql = 'select * from userinfo where name = %s and password = %s'
sql = 'select * from userinfo where name = %(name)s and password = %(password)s'
print(sql)
               #res = cur.execute(sql,[user,pwd]) #把上面的参数拿到下面来，防注释攻击
               #res = cur.execute(sql,(user,pwd))
res = cur.execute(sql,{'name':user,'password':pwd})
print(res) #res=1 查询的结果有一条数据为真否则为假
#游标关闭，连接关闭
cur.close()
conn.close()
if res:
print('登陆成功')
else:
print('登陆失败')


2.
import pymysql
# user = input('请输入用户:')
# pwd = input('请输入密码:')
#建立连接
conn = pymysql.connect(
host='localhost',
user='root',
password="123456",
database='db13',
port=3306,
charset='utf8'
)
# #创建游标
# cur = conn.cursor()
# sql = 'insert into userinfo(name,password) value (%s,%s)'
# res = cur.execute(sql,(user,pwd))
# print(res)
# #增删改一定要commit()
# conn.commit()
# #游标关闭，连接关闭
# cur.close()
# conn.close()

#查询
#创建游标
#cur1 = conn.cursor()
#创建游标，元组转成字典
cur1 = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql1 = 'select * from userinfo'
res1 = cur1.execute(sql1)
#一条一条的查询
# result = cur1.fetchone()
# print(result)
# result = cur1.fetchone()
# print(result)

#范围多条123456
# result =cur1.fetchmany(2)
# print(result)

#获取所有
result= cur1.fetchall()
print(result)
cur1.close()
conn.close()