Mysql数据库基础

添加用户

土办法

root@host#   mysql -u root -p 
Enter password: ******
mysql> use mysql;
Database changed

mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, updata_priv) 
VALUES('localhost', 'guest',  PASSWORD('guest123'), 'Y', 'Y', 'Y');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

tip：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
tip：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
tip：在注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。

GRANT 办法

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

连接

使用PHP脚本连接

<?php
$dbhost = 'localhost:3306';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysqli_error());
}
echo '数据库连接成功！';
mysqli_close($conn);
?>

数据类型

数值类型

类型	大小（字节）	用途
TINYINT	1	小整数值
SMALLINT	2	大整数值
MEDIUMINT	3	大整数值
INT	4	大整数值
BIGINT	8	极大整数值
FLOAT	4	单精度浮点数值
DOUBLE	8	双精度浮点数值

字符串类型

类型	大小（字节）	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据

tip:详情

基本语法

库

创建数据库
CREATE DATABASE <数据库名称>;
删除数据库
DROP DATABASE <数据库名称>;

表

创建数据表
CREATE TABLE table_name (column_name column_type);
eg：

 CREATE TABLE　IF NOT EXISTS `table_name`(
    `id` INT UNSIGNED AUTO_INCREMENT,
    `title` VARCHAR(100) NOT NULL,
    `author` VARCHAR(40) NOT NULL,
     `date` DATA,
     PRIMARY KEY (`id`)
 )ENGINE=InnoDB DEFAULT CHAREST=utf-8;

tip:详情

删除数据表
DROP TABLE table_name ;

表内数据

插入数据
INSERT INTO table_name (field1, ...) VALUES (value1, ...);
查询数据
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M];
更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause];
删除数据
DELETE FROM table_name [WHERE Clause];
LIKE 子句
SELECT field1, ... FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue';
eg:

'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
UNION 操作符
- 描述
  MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。tip:详情
- 语法
```
   SELECT expression1, expression2, ... expression_n
   FROM tables
   [WHERE conditions]
   UNION [ALL | DISTINCT]
   SELECT expression1, expression2, ... expression_n
   FROM tables
   [WHERE conditions];
```
- 参数
  DISTINCT：可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

MYSQL进阶

排序

描述
通过SELECT获取的数据再使用ORDER BY将数据按照某一字段排序
语法
SELECT field1,... FROM table_name ORDER BY field,... [ASC [DESC]];
- 你可以设定多个字段来排序。
- 使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
- 添加 WHERE...LIKE 子句来设置条件。

分组

描述
GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
语法
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
- function(column_name)可以类比COUNT()。*
示例

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

tip:使用SELECT field_name FROM table_name GROUP BY field_name ;去重数据

连接的使用（重要）

描述（过于复杂，详情请见菜鸟教程）
通过JOIN来联合对表格进行删、改、查；
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
示例
SELECT a.id, a.author, b.title FROM table1 a INNER JOIN table2 b WHERE a.author = b.author;

NULL

描述
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。
因此使用一下三个方法来判断是否为NULL
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
  错误示例：

SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL;
正确示例
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NOT NULL;

正则表达式（REGEXP ）

描述
通过正则表达式模糊查询，功能比LIKE更加丰富。详情
示例
- 查找name字段中以‘gdl’开头的所有数据：
  SELECT name FROM table_name WHERE name REGEXP '^gdl';
- 查找name字段中以‘gdl’结尾的所有数据：
  SELECT name FROM table_name WHERE name REGEXP 'gdl$';
- 查找name字段中包含‘gdl’的所有数据：
  SELECT name FROM table_name WHERE name REGEXP 'gdl';

MYSQL高级

事务

多条语句全部执行，保证原子性，不然就回滚事务。

特点

使用了InnoDB引擎的数据库才能使用事务机制
事务用来管理INSERT、UPDATE、DELETE语句
原子性、隔离性（并发操作（锁））、一致性、持久性

语法

BEGIN（START TRANSACTION）开启事务
COMMIT（COMMIT WORK）提交事务
ROLLBACK（ROLLBACK WORK ）事务回滚
SET AUTOCOMMIT=0 禁止自动提交
eg：详情

ALTER命令

对数据表结构进行操作（如修改表名、增加字段）

语法

删除，添加或修改表字段
ALTER TABLE table_name DROP|ADD|MODIFY field_name INT(10)|CHAR(20)|...| NOT NULL DEFAULT 100;

-修改字段默认值
ALTER TABLE table_name ALTER field_name SET DEFAULT 1000;
ALTER TABLE table_name ALTER field_name SET DROP DEFAULT;

-修改引擎和表名
ALTER TABLE table_name ENGINE=MYISAM;
ALTER TABLE table_name RENAME TO new_table_name;

索引

索引是一张表，保存了主键与索引，可以高效查询实例数据。在使用INSERT、UPDATE、DELETE会更新索引表，因此更新速度变慢，同时占用磁盘。

复制表

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

序列使用

使用AUTO_INCREMENT来定义
ALTER TABLE table_name AUTO_INCERMENT = 100;

重复数据的处理

表格中设置了PRIMARY KEY与UNIQUE是不会出现重复数据的。

语法
- 统计重复数据
  SELECT COUNT(*) AS repetitions, last_name, first_name FROM table_name GROUP BY last_name, first_name HAVING repetitions>1;
- 过滤重复信息
  SELECT DISTINCT last_name, first_name FROM table_name;
  SLECT last_name,first_name FROM table_name GROUP BY (last_name,first_name);
- 删除重复信息
```
    CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
    DROP TABLE person_tbl;
    ALTER TABLE tmp RENAME TO person_tbl;
```
ALTER IGNORE TABLE table_name ADD PRIMARY KEY (last_name, first_name);

导出/导入数据

基本方法

SELECT ... INTO OUTFILE

       SELECT * form table_name 
       INTO OUTFILE "temp/table.sql"
       FIELDS TERMINATED BY ',' OPTIONALLY BY ' " '
       LINES TERMINATED BY '\n';

LOAD DATA INFILE

        LOAD DATA INFILE "temp/table.sql" INTO TABLE table_name;

tips：在写出的时候会出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement的错误解决方法
出现这个错误是因为没有给数据库指定写出文件的路径或者写出的路径有问题。首先使用下面的命令 show variables like '%secure%'; 查看数据库的存储路径。如果查出的 secure_file_priv 是 null 的时候就证明在 my.ini 文件里面没有配置写出路径。这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加 secure_file_priv=E:/TEST 再重启 mysql 就可以了。

使用mydump
- 导出SQL格式数据
  mysqldump -u root -p database_name > database.sql
  mysqldump -u root -p --all-databases > database_all.sql
  mysqldump -u root -p database_name table_name > table.sql
- 导入sql格式数据
  mysqldump -u root -p database_name < database.sql
  mysqldump -u root -p database_name table_name < table.sql
- 将指定主机的数据库拷贝到本地
  mysqldump -h gdl.com -P 3306 -u root -p database_name > database.sql

内置函数

COUNT(计数)
SELECT COUNT(*) AS count FROM table_name;
COUNT(配合GROUP BY,计算某个学生所有记录次数)
SELECT a.uid, COUNT(a.uid) FROM record AS a GROUP BY a.uid;
SUM(求和)
SELECT SUM(money) AS all_money FROM table_name;
MAX(最大)
SELECT MAX(money) AS max_money FROM table_name;
MIN(最小)
SELECT MIN(money) AS min_money FROM table_name;

最后编辑于：2019.03.19 15:32:45

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,222评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,455评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,720评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,568评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,696评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,879评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,028评论 3赞 409
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,773评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,220评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,550评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,697评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,360评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,002评论 3赞 315
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,782评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,010评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,433评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,587评论 2赞 350

Mysql数据库基础