安装：
初始化 D:\mysql-8.0.12-winx64\mysql-8.0.12-winx64\bin\mysqld --initialize-insecure
运行服务端 D:\mysql-8.0.12-winx64\mysql-8.0.12-winx64\bin\mysqld
运行客户端 D:\mysql-8.0.12-winx64\mysql-8.0.12-winx64\bin\mysql -u root -p
# 查看数据库 show databases;
# 创建数据库 creat database db1;
# 安装windows服务：
# C:\Windows\system32>D:\mysql-8.0.12-winx64\mysql-8.0.12-winx64\bin\mysqld --install [--remove移除]
# 添加后可以在windows的服务里看到“MySQL”，管理员身份运行cmd，输入net start/stop MySQL即可启动
登陆数据库后创建用户
create user ‘username‘@‘ip‘ identified by ‘userpwd‘;
# ip 填 % 表示所有的ip地址都可登陆
为用户授权
grant all privileges on db.t to ‘username‘@‘ip‘;
# all privileges表示除授权权限外拥有所有的权限 也可填select,delete (查权限和删权限)等
# db.t 表示对哪个数据库下的哪个表有此权限，填*.*意味着拥有 所有数据库下的所有表 的权限
取消授权：
revoke update,delete on db.t1 from ‘username‘@‘ip‘;
创建数据库
create database db2 default charset utf8; # 设置默认编码
删除
drop database 数据库名;
查
show databases;
创建表
create table t1(id int auto_increment primary key,name char(10))engine=innodb default charset=utf8;
删除表
drop table 表名;
清空表：
delete from 表名; # 自增的id不重置
truncate table 表名; # 自增的id重置
表的增删改查
insert into 表名(列名,列名2) value(列1值,列2值);
delete from 表名 where 条件;
update 表名 set 列=值 where 条件;
select * from 表名;
外键：
create table t1(
uid int auto_increment primary key,
name varchar(32),
t2_id int,
constraint fk_user_userinfo foreign key (t2_id) references userinfo(id)
)engine=innodb default charset=utf8;
create table t2(
id int auto_increment primary key,
sex char(1)
)engine=innodb default charset=utf8;
innodb原子操作
navicat 链接数据库报错
# 2059 - Authentication plugin ‘caching_sha2_password‘ cannot be loaded: ......
# use mysql
# select user,host,plugin,authentication_string from user;
# alter user ‘mingyaun‘@‘%‘ identified with mysql_native_password by ‘123456‘;
desc tabl_name;
显示表结构，字段类型，主键，是否为空等属性，但不显示外键。
show create table table_name;
该命令把创建表的DDL显示出来，于是表结构、类型，外键，备注全部显示出来了。
关键字
- in not in
- between and
- limit
- group by having
- order by
- like "%a"
- left join xx on 关系
- select * from (select * from tb where id< 10) as B;
- select (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as y,from score as s1;
- case when min(num) <10 THEN 0 ELSE min(num) END
- select avg(num),sum(case when num <60 THEN 0 ELSE 1 END) from score GROUP BY course_id order by AVG(num) asc,jgl desc;
import pymysql
# conn = pymysql.connect(host="localhost",user=‘root‘,password=‘‘,database="db666")
# cursor = conn.cursor()
# # sql = "select * from userinfo where username=‘%s‘ and password=‘%s‘" %(user,pwd,)
# # select * from userinfo where username=‘uu‘ or 1=1 -- ‘ and password=‘%s‘
# # ret = cursor.execute(sql)
# # result = cursor.fetchone()
# sql = "select * from userinfo where username=%s and password=%(p)s"
# cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{‘u‘:user,‘p‘:pwd})
# cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# conn.commit() # 增加，删，改
# result = cursor.fetchone()
# result = cursor.fetchall()
# result = cursor.fetchmany(4)
# 新插入数据的自增ID: cursor.lastrowid
# cursor.close()
# conn.close()
