MYSQL 笔记

---

Ubuntu 16.04 安装MySql

目录

• 一、安装MySql服务器和客户端
  • 登录
  • 创建数据库
  • 选择数据库
  • 查看数据库
  • 创建数据表
  • 查看数据表
  • 增加字段
  • 更改数据表名字
  • 添加数据
  • 更改数据
  • 查询数据
    • 过滤重复数据
  • 清除表数据
  • 删除表
  • 查看数据表表头
  • 修改字段为递增主键
  • 添加 UNIQUE KEY
  • 删除 UNIQUE KEY
  • 导出数据库
  • 更改登录密码
  • 报错
    • MySQL命令行无法输入中文
    • 错误：1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
    • TypeError:must be real number,not str
    • 在在MariaDB中，不能直接在子查询中使用 LIMIT 关键字

---

一、安装MySql服务器和客户端

sudo apt install mysql-server mysql-client

二、语法

登录

mysql -u root -p

创建数据库

CREATE DATABASE 数据库名;

选择数据库

use 数据库名;

查看数据库

show database;

创建数据表

CREATE TABLE table_name (column_name column_type);

查看数据表

show tables;

增加字段

一、mysql中不同位置增加字段
增加字段（在特定的字段后增加）
alter table 表名 add 需要修改/增加的信息 afer 字段名(在哪个字段后增加)

alter table dept Add column name varchar(20) not null default 0 AFTER sex;

• 把字段添加在第一个位置
  alter table 表名 add 需要修改/增加的信息 first;

alter table table_name Add column name varchar(20) not null default 0 first;

• 修改字段
  alter table table_name change old_name new_name varchar(30);

• 删除字段：
  alter table table_name drop column column_name ;

• 调整字段顺序：
  ALTER TABLE table_name CHANGE column_name column_name int not null default 0 AFTER some_column;

更改数据表名字

alter table old_name rename to new_name

添加数据

insert into table_name (colume1，colume2，...) values(val1,val2，...)

更改数据

alter table_name set col=xxx where xxx;

#记录中存在设为unique index的字段则更改，不存在则新增
#前提时先设定unique index

insert into info phone values("8888") on duplicate key update phone="6666";

# 等效于
insert into info phone values("8888");
update table info set phone="6666" where phone="8888";

查询数据

select column_1, column_2, ... from table_name <where>;

过滤重复数据

select distinct column_name from table_name;

清除表数据

不能与where一起使用。
truncate删除数据后无法rollback。

truncate table table_name

删除表

drop table table_name;

查看数据表表头

select column_name from Information_schema.columns where table_Name = 'table_name';#仅查看表头
show create table table_name; #查看表结构、字段类型
desc table_name;

修改字段为递增主键

alter table tb_name modify column_name int auto_increment primary key ;

添加 UNIQUE KEY

#1.建表时添加
CREATE TABLE `info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(18) NOT NULL ,
`phone` varchar(18) NOT NULL unique,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#2.建表后修改
alter table info add unique(phone);

删除 UNIQUE KEY

DROP INDEX 唯一约束名 ON 表名;

导出数据库

mysqldump [选项] --databases 库名 1 [库名 2] ··· > /备份路径/备份文件名
mysqldump [选项] 库名 [表名 1] [表名 2] ··· > /备份路径/备份文件名

更改登录密码

1. 在[mysqld]的位置添加skip-grant-tables，用来跳过密码验证的过程

[mysqld]
 
skip-grant-tables

2. 重启mysql

service mysqld restart

3. 直接输入mysql免密登录数据库
4. use mysql切换数据库
5. 更改密码

update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost';

6. 输入 flush privileges; 命令刷新权限
7. 输入quit; 退出

报错

MySQL命令行无法输入中文

解决方法：my.cnf配置文件只保留mysqld的字符集，其它去掉

1. 查看建表语句

show create table table_name;

CREATE TABLE `mytable` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`),
 `name` text NOT NULL,
 `title` text NOT NULL,
 `info` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=288 DEFAULT CHARSET=latin1

2. 表字符集修改，chartset为utf8

alter table table_name default character set utf8;

3. 字段字符集修改

 alter table tbale_name change column_old column_new varchar(255) character set utf8;

错误：1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

解决方法：

alter table table_name convert to character set utf8;

TypeError:must be real number,not str

因为Python向MYSQL写入数据无论是什么类型，都是以%s写入，包括占位符也要写成%s，不存在%d、%f这样的数据类型

在在MariaDB中，不能直接在子查询中使用 LIMIT 关键字

查询可用

SELECT column1, column2, ...
FROM (
  SELECT column1, column2, ...
  FROM table
  LIMIT [offset,] row_count
) AS subquery;

删除可用

DELETE t1
FROM table AS t1
INNER JOIN (
  SELECT column
  FROM table
  WHERE column = 'column'
  LIMIT 5
) AS t2 ON t1.column = t2.column;

t1: 它是 table 表的别名，用作DELETE语句的主体，表示要删除的表。
t2: 它是子查询的结果表的别名，表示在子查询中选择的列。