mysql入门使用

说明

• 数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据
• RDBMS 关系数据库管理系统 Relational Database Management System
• 参照完整性:参照的完整性要求关系中不允许引用不存在的实体
• MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB
• BSON( Binary Serialized Document Format),支持Date格式
• JSON(JavaScript Object Notation)
• 所有的数据库名,表名,表字段都是区分大小写
• 表名等保留字用反引号,其他使用双引号
• decimal(a,b),a表示数字整数长度加上小数长度,b表示小数最大长度

命令

# 历史路径
cat  ~/.mysql_history

# 查看mysql相关基本信息
ps -ef|grep mysqld

# 启动mysql
mysqld_safe &

# 远程连接
mysql -uliuende -p -h 123.56.26.41 -P3306

客户端

-- 清空命令行,重新开始输入
\c

-- 显示数据库
SHOW DATABASES;

-- 显示数据表
SHOW TABLES;

-- 数据表结构
-- MUL 可以重复 非唯一索引列 比如外键
-- PRI 主键约束 primary
-- UNI 唯一约束 unique
SHOW COLUMNS FROM `t_coop_dep`;

-- 数据表索引
SHOW INDEX FROM `t_coop_dep`;

-- 当前数据库所有表信息
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 't_two';

-- 指定数据库指定表
SHOW TABLE STATUS FROM `teamclinci` LIKE 't_coop%';

-- 查询结果按列打印
SHOW TABLE STATUS FROM `teamclinci` LIKE 't_coop%'\G;

-- 更换数据库
use `mcomplete`;

-- 查看当前数据库
STATUS

-- 查看变量
SHOW VARIABLES LIKE 'tx_isolation';
SHOW LOCAL VARIABLES;
SHOW GLOBAL VARIABLES;
SELECT @@tx_isolation;

数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `mtest` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci

-- 删除数据库
DROP DATABASE IF EXISTS `mtest`;

数据表

增

• 如果规定字段为自增,则此字段必须为主键,否则允许表没有主键
• 当前时间统一使用CURRENT_TIMESTAMP

CREATE TABLE IF NOT EXISTS `t_one`(
`id` INT UNSIGNED AUTO_INCREMENT, 
`name` VARCHAR(20) NOT NULL, 
`code` VARCHAR(10) NOT NULL, 
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, 
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`),
INDEX `name` (name(length))  
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

改

-- 增加字段
-- AFTER指定位置
ALTER TABLE `t_app` ADD `app_name` VARCHAR(100) NOT NULL AFTER `id`;

-- 删除字段
ALTER TABLE `t_two` ADD code VARCHAR(20);

-- 修改字段允许为NULL 或者修改类型
ALTER TABLE `runoob_tbl` MODIFY `runoob_author` varchar(100);

-- 修改字段名
ALTER TABLE `t_two` CHANGE `code` `ncode` varchar(10);

-- 修改默认值
ALTER TABLE `t_two` ALTER `ncode` SET DEFAULT 10;

-- 删除默认值
ALTER TABLE `t_two` ALTER `ncode` DROP DEFAULT;

-- 修改数据表类型
ALTER TABLE `t_two` ENGINE = MYISAM
ALTER TABLE `t_two` auto_increment = 9;

-- 修改表名
ALTER TABLE `t_two` RENAME TO t_two;

-- 重置序列
ALTER TABLE insect DROP id
ALTER TABLE `t_three_copy` ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY (id);

-- 设置序列初始值
ALTER TABLE `t_three_copy` AUTO_INCREMENT = 100;

表约束

• 约束时可同时指定多个字段

-- 主键约束 PRI
ALTER TABLE `t_two` ADD PRIMARY KEY (`id`,`name`);

-- 唯一性约束 UNI
ALTER TABLE `t_two` ADD UNIQUE KEY (`code`) USING BTREE;

-- 键约束 MUL 创建索引
ALTER TABLE `t_two` ADD KEY (`id`);

删

-- 删除主键
ALTER TABLE `t_two` DROP PRIMARY KEY;

复制

-- 复制表结构
SHOW CREATE TABLE `t_three` \G

-- 复制表数据
INSERT INTO `t_three_copy` SELECT * FROM `t_three`;

临时

仅当前连接可见

-- 创建临时表
CREATE TEMPORARY TABLE

-- 删除临时表
DROP TABLE sales;

数据

增

-- 单条数据
INSERT INTO `t_one` (`name`, `code`) VALUES ('刘恩德', 'liuende');

-- 多条数据
INSERT INTO `t_app` 
(`app_name`, `url`, `country`) 
VALUES 
('QQ APP', 'http://im.qq.com/', 'CN'), 
('微博 APP', 'http://weibo.com/', 'CN'), 
('淘宝 APP', 'https://www.tabbao.com/', 'CN');

-- 忽略重复数据
INSERT IGNORE INTO t_three_copy(code,name) VALUES ('xdx',442343234);

-- 替换重复数据
REPLACE INTO t_three_copy(code,name) VALUES ('xdx',4444);

删

-- 删除表
DROP TABLE `t_two`;

-- 删除表指定数据
DELETE FROM `t_one` WHERE CONDITION;

-- 删除表全部数据
DELETE FROM `t_one`;
TRUNCATE TABLE `t_one`;

改

UPDATE `t_one` SET `name`='刘刘恩德' WHERE `id`=1;

查

• 可以同时指定多张表查询
• WHERE条件不区分大小写

-- 通用查询
SELECT * FROM `c_category` WHERE `created_at`>'2018-01-10 19:01:17' LIMIT 4 OFFSET 2;

-- 区分大小写
SELECT * FROM `t_one` WHERE BINARY `code`='liuendE'

-- 关键字
SELECT * FROM `t_one` WHERE `code` LIKE '%liuende'

-- 排序[ ASC | DESC ]
-- 默认为ASC
SELECT * FROM `t_app` ORDER BY `id` DESC;

NULL

• NULL值与任何其它值的比较(即使是NULL)永远返回false

-- IS NULL
SELECT * FROM `runoob_tbl` WHERE `runoob_author` IS NULL;

-- IS NOT NULL
SELECT * FROM `runoob_tbl` WHERE `runoob_author` IS NOT NULL;

-- <=>
SELECT * FROM `runoob_tbl` WHERE `runoob_author` <=> NULL;

UNION

-- 不包含重复数据
SELECT `country` FROM `t_app` UNION SELECT `country` FROM `t_web`;
-- 包含重复数据
SELECT `country` FROM `t_app` UNION ALL SELECT `country` FROM `t_web`;

GROUP BY

• 内置包含排序

-- 计数
-- COUNT 计算行数
SELECT `name`,COUNT(*) FROM `employee_tbl` GROUP BY `name`;

-- 函数(SUM,AVG,COUNT)
-- WITH ROLLUP 在分组统计数据基础上再进行"相同"的统计
-- coalesce(合并),设置一个名称取代NULL
SELECT coalesce(`name`,'总数'),SUM(singin) FROM `employee_tbl` GROUP BY `name` WITH ROLLUP;

JOIN

• INNER JOIN: 和JOIN相同, 获取两个表中字段相同的记录, 可用WHERE实现
• LEFT JOIN: 获取左表所有记录,即使右表没有对应匹配的记录
• RIGHT JOIN: 获取右表所有记录,即使左表没有对应匹配的记录

REGEXP

• [^…] 负值字符集合

-- 匹配开头
SELECT * FROM `t_one` WHERE code REGEXP '^liuende';

-- 匹配结尾
SELECT * FROM `t_one` WHERE code REGEXP 'o$';

-- 包含
SELECT * FROM `t_one` WHERE code REGEXP 'end';

-- 或
SELECT * FROM `t_one` WHERE code REGEXP '^liuende|o$';

事务(transaction)

隔离级别

• read uncommitted
• read committed
• repeatable read
• serializable

-- 查看隔离级别
SELECT @@tx_isolation

-- 设置当前会话隔离级别
set session transaction isolation level read uncommitted;

作用

• 使用了Innodb数据库引擎的数据库或表才支持事务
• 维护数据库的完整性

条件(ACID)

1. 原子性(Atomicity),失败时回滚(Rollback)
2. 一致性(Consistency),写入数据必须符合预设规则
3. 隔离性(Isolation),允许事务并发,包含四个级别读未提交(Read uncommitted),读提交(read committed),可重复读(repeatable read)和串行化(Serializable)
4. 持久性(Durability),事务结束时,对数据的修改是永久的

说明

• 默认设置下,事务都是自动提交的,即执行SQL语句后就会马上执行COMMIT操作
• SET AUTOCOMMIT=0 或者 显式地使用命令BEGIN或START TRANSACTION来开启事务

索引

说明

• 索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录
• 唯一索引的值必须唯一,但允许有空值

使用

-- 显示索引
SHOW INDEX FROM `t_three`\G;

-- 创建索引
CREATE INDEX `name` ON `t_three`(`name`(3));
ALTER TABLE `t_three` ADD INDEX `name`(`name`);

-- 唯一索引
CREATE UNIQUE INDEX IDX_code ON t_three(code(4));

-- 删除索引,动词 on
DROP INDEX IDX_code ON `t_three`;
ALTER TABLE `t_three` DROP INDEX IDX_code;

其他

重复数据

-- 统计
SELECT COUNT(*) AS num, `id`, `name` FROM `t_three_copy` GROUP BY name HAVING num < 1;

-- 过滤重复数据
SELECT DISTINCT `name` from `t_three_copy`; -- 只显示指定字段
SELECT * FROM `t_three_copy` GROUP BY `name`; -- 显示全部

-- 删除重复数据
CREATE TABLE `tmp` SELECT * FROM `t_three_copy` GROUP BY `CODE`;
DROP TABLE `t_three_copy`;
ALTER TABLE `tmp` RENAME TO t_three_copy;

SQL防注入

• 永远不要使用动态拼装sql
• 为每个应用使用单独的权限有限的数据库连接
• 应用的异常信息应该给出尽可能少的提示

数据导入/导出

• 查看SHOW GLOBAL VARIABLES LIKE '%secure%',secure_file_priv为null表示mysqld 不允许导入/导出
• 开关默认为NULL,修改配置在my.cnf中增加secure_file_priv=’/tmp’
• 导出文件不能是已存在文件

导出

-- 导出数据txt
SELECT * FROM `t_one` INTO OUTFILE '/tmp/t_one.txt';

-- 导出数据csv OPTIONALLY表示只有字符串类型的值会包含在'"'中
SELECT * FROM `t_one` INTO OUTFILE '/tmp/t_one.txt' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

-- mysqldump
-- 导出原始数据 
-- --no-create-info:是否导出数据库创建sql;
-- --tab:指定导出文件
mysqldump -uroot -p --no-create-info --tab=/tmp/mtest mtest t_one;

-- 导出sql 
-- -d:表示导出表结构; 
-- --all-databases:导出所有数据库; 
-- 可以导出指定表
mysqldump -uroot -o mtest t_one>/tmp/mtest/1.sql;

-- 数据库拷贝到其他数据
mysqldump -u root -p mtest | mysql -h 127.0.0.1 mtest2

导入

-- 导入数据txt
LOAD DATA INFILE '/tmp/mtest/t_one.txt' INTO TABLE `t_one`;
-- 改变导入字段顺序
LOAD DATA INFILE '/tmp/mtest/t_one.txt' INTO TABLE `t_one`(id,code,name,created_at,updated_at);

-- 导入csv
LOAD DATA INFILE '/tmp/mtest/t_one.txt' INTO TABLE `t_one` 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

内置

元数据

--当前时间
SELECT NOW();

-- 版本
SELECT VERSION();

-- 数据库名
SELECT DATABASE();

-- 用户名
SELECT USER();

-- 最后的插入表中的自增列的值
SELECT LAST_IN

SQL

-- 
SET NAMES utf8;

-- 
SET FOREIGN_KEY_CHECKS = 0;

函数

NOW() 当前时间

常量

CURRENT_TIMESTAMP 当前时间

配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid