MySQL基础知识

基本配置

1. 在文件夹下创建my.ini文件，配置内容如下

2. 后三条为报错提示

[mysqld]
​
# port
port = 3306
​
# set basedir to your MySQL installation path
basedir = D:\\mysql-5.7.31-winx64
​
# set datadir to the location of your data directory
datadir = D:\\mysql-5.7.31-winx64\\data
​
# TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
explicit_defaults_for_timestamp=true
​
# you should open the cmd as administrator!!!
​
# --initialize specified but the data directory has files in it. Aborting.
# delete the data directory and try again the initialization

创建与删除服务

创建："<文件夹路径>" --install <服务名字>

删除："<文件夹路径>" --remove <服务名字>

启动：net start <mysqlLearning>

停止：net stop <mysqlLearning>

安全设置

1. 进入数据库之后可以设置密码：set password = password("123456")

2. 若忘记密码，则再my.ini配置文件下的[mysqld]节点下添加：skip-grant-tables=1

   1. 再重启MySQL服务，即stop再start

   2. 直接无密码进入MySQL：mysql.exe -u root -p

   3. 使用指令修改：use mysql;

   4. update user set authenticatio_string = password('new password'),password_last_changed=now() where user='root';

   5. 修改配置文件，将skip-grant-tables=1注释即可

注入问题

1. sql执行语句需要直接使用%s，不可以用字符串格式化

import pymysql
​
user=input("please enter your name:")
pwd=input("please enter your password:")
​
conn=pymysql.connect(host='localhost',user='root',password='',database='userdb')
cur=conn.cursor()
cur.execute("select * from user where name=%s and password=%s",[user,pwd])
result=cur.fetchone()
if result:
    print("welcome to login")
else:
    print("login failed")
cur.close()
conn.close()

数据库管理

基本指令

1. 创建数据库（后面大写的是使用utf8编码以及排序问题，默认即可）

2. 删除数据库

3. 进入数据库

4. 查看数据库的表

create database <TestLearn> DEFAULT CHARSET UTF8 COLLATE utf8_general_ci;
drop database <TestLearn>;
use <TestLearn>;
show tables;

• 创建表

• tag,time,approve,uid,name,sex,review

create table kyobo(
    id int not null auto_increment primary key,         -- 不为空&自增&主键（不允许为空且不能重复）
    name varchar(20) not null,  -- 不允许为空
    email varchar(32) null,     -- 允许为空（默认）
    age int default 3           -- 插入数据时，若不给age列设置数值，则默认为3
)default charset=utf8;

• 显示表结构

desc bvdata;

• 删除表

drop table <tableName>;

• 清空表

delete from <tableName>;    -- 一般使用
truncate table <tableName>; -- 无法撤销

• 添加列

alter table <tableName> add <columnName> <type>;
alter table <tableName> add <columnName> <type> DEFAULT <defaultValue>;

• 删除列

alter table <tableName> drop column <columnName>;

• 修改列（类型、类型+名称），类型后面可加额外值，比如primary key等

alter table <tableName> modify column <columnName> <newType>;
alter table <tableName> change <originalColumnName> <newColumnName> <newType>;

• 修改列默认值

ALTER TABLE <tableName> ALTER <columnName> SET DEFAULT <newDefaultValue>;

• 删除列默认值

ALTER TABLE <tableName> ALTER <columnName> DROP DEFAULT;

• 添加主键

alter table <tableName> add primary key(<columnName>));

• 删除主键

alter table <tableName> drop primary key;

修改表中的数据

• 新增数据

insert into <tableName>(columnName1,columnName2...) values(Value1,Value2...);
insert into (columnName1,columnName2...) values(Value1,Value2...);
insert into <tableName> set columnName1=value1, columnName2=value2...;

• 删除数据

delete from <tableName> where <condition>;
delete from <tableName> where name="Kyobo" and age="21";
delete from <tableName> where age<20;

• 修改数据

update <tableName> set <columnName>=<newValue> where <condition>;
update kyobo set name="NRB" where age=21
update kyobo set name=concat("Nomination-",name) where age=21

• 查询数据

select * from <tableName>;
select * from <tableName> where <condition>;
select <columnName1,columnName2...> from <tableName>;

select * from <tableName> where name like '%omination%NRB' #通配符查询
select name, review from <tableName> order by approve; #升序查询，降序desc
select * from bvdata limit 5 offset 3; #limit限制查询条数，offset查询起始位置

基础查询

• 查询单个,多个或所有字段（为了区分关键字还是普通字段，字段可以加着重号，例如有字段NAME可以写成

select `NAME` from <tableName>;

select <columnName1,columnName2...> from <tableName>;
select * from <tableName>;

• 查询后为新列取别名（使用as或者空格）

• 若别名中有特殊符号，则使用引号select salary as "out put" from table;

select name as NAME, uid as UID from bvdata;
select name NAME, uid UID from bvdata;

• 去重

select distinct uid from bvdata;

• 判断是否为NULL

select ifnull(uid, 1445) as NewId from bvdata;

条件查询

1. 条件运算符：>, <, <>, >=, <=

2. 逻辑运算符：&&, ||, !, and, or, not（将条件连接到一起）

3. 模糊查询：like，通配字符：%（任意多个字符包括0个），_（任意单个字符），遇到关键字可以使用转义字符 \，或者手动设置转义字符 escape '$'

   1. between and， 包含临界值，select * from bvdata where uid between 1 and 1000;

   2. in，列表的值必须一致，select * from bvdata where approve in(0,1,2,3);

   3. is null, is not null（仅仅判断NULL，<=>为安全等于，NULL与数值皆可判断）

4. 排序查询：order by <columnName支持别名> asc/desc，可支持多个字段排序

   1. select * from bvdata order by uid asc, approve desc;

select * from <tableName> where <condition>;