MySql基础

LSLWind
137 阅读17分钟

安装

Ubuntu20.04

ubuntu20.04可以直接使用apt安装

apt-get install mysql-server-y # 安装最新版本
apt-get install mysql-server-8.0 # 安装指定版本
apt-get install mysql-client-8.0

默认情况下，此时root密码为空。可以直接进入mysql环境。先执行命令

sudo /etc/init.d/mysql stop
sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld
sudo mysqld_safe --skip-grant-tables&

再执行如下命令修改密码：

sudo mysql --user=root mysql
UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

退出，重启mysql

sudo killall -u mysql # 直接回车
sudo /etc/init.d/mysql start # 重启

之后正常进入Mysql即可

win10

两种安装方式，一种是压缩包直接安装，另一种是可执行文件安装

可执行文件安装

官网下载，点击No thanks, just start my download.

选择Full，安装，进入校验界面，需要安装python与ViscualStudio，注意版本，此处为python3.8与ViscualStudio2019，VS2019需要安装C++桌面端基础组件

一直点击到设置密码，设置密码后添加用户，add user，一直点击到check界面，输入设置的用户名及密码测试连接，点击至安装结束

Docker

查询mysql：docker search mysql

默认拉取最新版本mysql：docker pull mysql

创建mysql数据文件目录，挂载并启动mysql的docker

docker run --name mysql
-v /home/ubuntu/mysql:/var/lib/mysql
-e MYSQL_ROOT_PASSWORD=root
-d mysql:latest

环境变量配置

win10环境添加Server 目录下的bin目录到环境变量Path：C：\Program Files\MySQL\MySQL Server 8.0\bin

卸载

Ubuntu

使用dpkg --list|grep mysql查看mysql依赖项，使用remove移除

sudo apt-get remove mysql-common

最后清除残留数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

数据库管理

创建数据库

CREATE DATABASE test_db;

通常加上判断语法，不存在时创建数据库

CREATE DATABASE IF NOT EXISTS test_db;

删除数据库

drop 命令格式：

drop database <数据库名>;
drop database test_db;

选择数据库

use test_db;

数据类型

数值类型

MySQL 支持所有标准 SQL 数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

  • 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
  • BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
类型大小范围（有符号）范围（无符号）用途
TINYINT1 Bytes(-128，127)(0，255)小整数值
SMALLINT2 Bytes(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 Bytes(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 Bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 Bytes--极大整数值
FLOAT4 Bytes--单精度 浮点数值
DOUBLE8 Bytes--双精度 浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值

日期

MySQL中常用的几种时间类型有：****date、datetime、time、year、timestamp

数据类型占用字节最小值最大值零值表示
date41000-01-019999-12-310000-00-00
datetime81000-01-01 00:00:009999-12-31 23:59:590000-00-00 00:00: 00
timestamp4197001010800012038年的某个时刻0000000000000000
time3-838:59:59838:59:5900:00:00
year1190121550000
  • datetime : 时间日期型，格式是YYYY-mm-dd HH:ii:ss，表示的范围是从1000到9999。但是有零值，0000-00-00 00:00:00；
  • date：日期，就是datetime中的date部分；
  • time：时间(段)，指定的某个区间之间，从-时间到+时间(有负时间表示)；
  • timestamp：时间戳，并不是常规意义时间戳(如：14253685)，范围是1970-01-01 00:00:00到2037年。格式为YYYY-mm-dd HH:ii:ss，与datetime完全一致；
  • year：yy和yyyy，yyyy的范围是1901-2155，yy的范围是1970-2069。两位year(00-69表示2000-2069,70-99表示1970~1999)。当应用只需要记录年份时，year比date更省空间

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据
  • char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
  • CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
  • BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
  • BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB ：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
  • 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

数据表管理

创建数据表

CREATE TABLE table_name (column_name column_type);
  • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  • PRIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键，列间以逗号分隔。
  • ENGINE 设置存储引擎，CHARSET 设置编码。
CREATE TABLE IF NOT EXISTS `tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除数据表

DROP TABLE table_name ;

修改数据表

使用ALTER命令修改数据库表、字段、存储引擎等

使用 ADD 子句来向数据表中添加列

ALTER TABLE testalter_tbl ADD col INT; #  col字段会自动添加到数据表字段的末尾

如果需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

使用 MODIFY 或 CHANGE 子句 修改字段类型及名称

ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT; # CHANGE 关键字之后紧跟着要修改的字段名，然后指定新字段名及类型。

使用 RENAME 子句修改数据表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

SQL语法

插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

使用INSERT INTO ，如果数据是字符型，必须使用单引号或者双引号，如："value"。

 INSERT INTO runoob_tbl
    -> (title, author, submission_date)
    -> VALUES
    -> ("MySQL", "au", NOW());

查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
  • 查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
  • SELECT 命令可以读取一条或者多条记录。
  • 可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
  • 可以使用 WHERE 语句来包含任何条件。
  • 可以使用 LIMIT 属性来设定返回的记录数。
  • 可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
  • limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

数据分页：

select * from _table limit (page_number-1)*lines_perpage, lines_perpage
select * from _table limit lines_perpage offset (page_number-1)*lines_perpage

where子句

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
  • 可以使用 BETWEEN、AND或 OR 指定一个或多个条件
  • 可以使用=，>等条件运算符
/*websites  表名   NAME alexa url country  字段*/
SELECT * FROM websites;                      /* 查询表所有数据 */
SELECT NAME FROM websites;                   /* 查询表字段数据 */
SELECT * FROM websites where name = "广西";   /* 查询表字段下条件数据 */
SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */
SELECT * FROM websites where id BETWEEN "1" AND "5";    /* 查询表下字段范围数据 */
SELECT * FROM websites WHERE name in ("广西","百度");    /* 查询表字段下固定条件数据 */
SELECT DISTINCT country FROM Websites;                  /* 查询去重值 */
SELECT * FROM Websites WHERE country = "CN" AND alexa > 50;  /*查询表下范围条件数据*/
SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */
SELECT * FROM Websites ORDER BY alexa;                      /* 查询表下值排序结果 */
SELECT * FROM Websites ORDER BY alexa DESC;                 /* 查询表下排序结果降序 */
SELECT * FROM Websites LIMIT 2;      /* 查询表下范围数据 */
SELECT name as zzz from websites;    /*别名查询表下数据*/

更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

可以同时更新一个或多个字段，可以在 WHERE 子句中指定任何条件。

UPDATE tbl SET title='学习 C++' WHERE id=3;

删除数据

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。

DELETE FROM table WHERE id = 1;

TRUNCATE TABLE 可以清空表，也就是删除所有行。

TRUNCATE TABLE mytable;

使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

like子句

在where like的条件查询中，SQL 提供了四种匹配方式。

  1. %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
  2. _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
  3. []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
  4. [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

查询内容包含通配符时，特殊字符 “%”、“_”、“[” 等语句无法正常实现，可以把特殊字符用 “[ ]” 括起便可正常查询。

SELECT * from tbl  WHERE author LIKE '%COM';
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的

UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
  • DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
  • ALL: 可选，返回所有结果集，包含重复数据。
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

ORDER BY 子句

使用ORDER BY 子句进行排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
  • ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下按升序排列
  • 可以添加 WHERE...LIKE 子句来设置条件
SELECT * from runoob_tbl ORDER BY submission_date ASC;

分组GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上可以使用 COUNT, SUM, AVG等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录：

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

HAVING 子句

having用来将分完组的数据用聚合函数进行统计，group by 、having、聚合函数通常一起使用。WHERE 先过滤出行，然后 GROUP BY 对行进行分组，HAVING 再对组进行过滤，筛选出需要的组，如果SQL 语句里没有 GROUP BY子句，此时整张表会被聚合为一组

SELECT emp_no, count(salary) t     #在工资表里查找员工编号、统计发工资的次数（取别名t）
from salaries 
where creat_date between '2020-01-01' and '2020-12-31'
group by emp_no        #通过员工编号编组
HAVING t>15       #查询发工资次数大于15次的

HAVING 操作的对象是组，那么其使用的要素是有一定限制的，能够使用的要素有 3 种：常数 、聚合函数和聚合键，聚合键也就是 GROUP BY 子句中指定的列名。

-- 使用谓词 ALL 求众数 all所有数据 any任意数据
SELECT salary, COUNT(*) AS cnt
FROM tbl_student_salary
GROUP BY salary
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM tbl_student_salary
    GROUP BY salary);

连接JOIN

可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。JOIN 按照功能大致分为如下三类：

  • INNER JOIN（内连接或等值连接） ：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

JOIN 的作用就是用来联合多表查询，左连接或右连接没有匹配的字段则置NULL

 SELECT a.id, a.author, b.count FROM tbl a INNER JOIN tbl b ON a.author = b.author;
 SELECT a.id, a.author, b.count FROM tbl a, tbl b WHERE a.author = b.author; # 等价

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；
  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO identifier 把事务回滚到标记点；
  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交
CREATE TABLE transaction_test( id int(5)) engine=innodb;  # 创建数据表
begin;  # 开始事务
insert into transaction_testvalue(5);
insert into transaction_testvalue(6);
commit; # 提交事务

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。拿汉语字典的目录页（索引）打比方，可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

创建最基本索引

CREATE INDEX indexName ON table_name (column_name)

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表时指定索引

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);

删除索引

DROP INDEX [indexName] ON mytable;

临时表

MySQL 临时表在需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

查询直接创建临时表

CREATE TEMPORARY TABLE 临时表名 AS
(
    SELECT *  FROM 表名
    LIMIT 0,10000
);

管理

用户授权

mysql 8已经将创建账户和赋予权限的方式分开

添加用户

CREATE USER '用户名' IDENTIFIED BY '密码';

赋予权限

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%'; # @ ip，%为任意ip

修改加密规则

ALTER USER '用户名'@'%' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;

更新用户密码

ALTER USER '用户名'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

更新配置信息

FLUSH PRIVILEGES;

数据导入导出

mysqldump

mysqldump.exe是mysql自带的导出工具，默认在msyql\bin目录下

mysqldump [选项] 数据库名 [表名] > 文件名

示例：

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db # 备份所有数据库
mysqldump -uroot -p test > /backup/mysqldump/test.db # 备份指定数据库
mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db # 备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db # 备份指定数据库排除某些表

jetbrains IDE工具

还是使用 mysqldump

选择mysqldump工具

avatar
文章
阅读
粉丝