MySQL基本操作

登录：

[root@host]# mysql -u root -p

查看版本：

[mysql]> select version();

更改密码：

mysql> alter user user() identified by "123";

创建用户：

查询MySQL用户:

1、查询MySQL数据库中所有用户：

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

2、查询数据库中具体某个用户的权限：

mysql> show grants for 'root'@'localhost';

3、查看user表的表结构。需要具体的项可以根据表的结构来查询。

mysql> desc mysql.user;

修改用户权限：

mysql> GRANT ALL ON *.* TO `用户名`@`127.0.0.1` WITH GRANT OPTION;

创建数据库：

CREATE DATABASE 数据库名;

CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci; //如果数据库不存在则创建，存在则不创建

删除数据库：

drop database <数据库名>;

注：一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

删除表内数据：

delete from 表名 where 删除条件;

清除表内数据，保存表结构：

truncate table 表名;

删除表：

drop table 表名;

创建数据表：

CREATE TABLE IF NOT EXISTS `WeeklyPaper`

(`WeeklyPaper_id` INT UNSIGNED AUTO_INCREMENT,

`WeeklyPaper_date` DATE,

`WeekArrange` VARCHAR(100) NOT NULL,

`PlanNextWeek` VARCHAR(40) NOT NULL,

PRIMARY KEY ( `WeeklyPaper_id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除数据表：

DROP TABLE table_name ;

插入数据：

INSERT INTO WeeklyPaper

(WeekArrange, PlanNextWeek)

VALUES

("数据可视化财务指标数据","巩固数据库+hive查询+python数据处理");

查询表结构：

desc table_name;

查询数据：

SELECT column_name,column_name

FROM table_name

[WHERE Clause] //包含任何条件

[LIMIT N] //设定返回记录数

[ OFFSET M] //语句开始查询的数据偏移量，默认为0；

WHERE子句：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...

[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件；

你可以在 WHERE 子句中指定任何条件。

你可以使用 AND 或者 OR 指定一个或多个条件。

WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

查询区分大小写：

mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM';

UPDATE更新：

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

mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]

UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where

runoob_id = 3;

DELETE语句：

DELETE FROM table_name [WHERE Clause]

LIKE子句：

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';

'%a' //以a结尾的数据

'a%' //以a开头的数据

'%a%' //含有a的数据

'_a_' //三位且中间字母是a的

'_a' //两位且结尾字母是a的

'a_' //两位且开头字母是a的

查询以 java 字段开头的信息。

SELECT * FROM position WHERE name LIKE 'java%';

查询包含 java 字段的信息。

SELECT * FROM position WHERE name LIKE '%java%';

查询以 java 字段结尾的信息。

SELECT * FROM position WHERE name LIKE '%java';

UNION操作符

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions]

UNION [ALL | DISTINCT]

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions];

SELECT country, name FROM Websites

WHERE country='CN'

UNION ALL

SELECT country, app_name FROM apps

WHERE country='CN'

ORDER BY country;

ORDER BY子句

mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC;

mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC;

MySQL 拼音排序

如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

SELECT * FROM runoob_tbl ORDER BY runoob_title;

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：

SELECT * FROM runoob_tbl ORDER BY CONVERT(runoob_title using gbk);

GROUP BY 语句

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

WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

在多个表中查询数据JOIN

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL：

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

正则表达式

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

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

删除，添加或修改表字段：

ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT FIRST;

ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT AFTER c;

修改字段类型及名称：

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

修改字段默认值

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

mysql> SHOW COLUMNS FROM testalter_tbl;

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; //删除

修改数据表类型：

查看数据表类型可以用SHOW TABLE STATUS

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;

mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G

修改表名：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

修改存储引擎：修改为myisam

alter table tableName engine=myisam;

删除外键约束：keyName是外键别名

alter table tableName drop foreign key keyName;

修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面

alter table tableName modify name1 type1 first|after name2;

索引

创建索引：

CREATE INDEX indexName ON mytable(username(length));

修改表结构（添加索引）

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;

唯一索引

创建唯一索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构：

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

添加删除索引

四种方式添加数据表索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

添加删除主键

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

mysql> SHOW INDEX FROM table_name; \G

MySQL复制表

一、获取数据表的完整结构：

mysql> SHOW CREATE TABLE runoob_tbl \G;

二、修改SQL数据表名：

mysql> CREATE TABLE `clone_tbl` (

-> `runoob_id` int(11) NOT NULL auto_increment,

-> `runoob_title` varchar(100) NOT NULL default '',

-> `runoob_author` varchar(40) NOT NULL default '',

-> `submission_date` date default NULL,

-> PRIMARY KEY (`runoob_id`),

-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)

-> ) ENGINE=InnoDB;

三、在数据库中创建新的克隆表 clone_tbl

mysql> INSERT INTO clone_tbl (runoob_id,

-> runoob_title,

-> runoob_author,

-> submission_date)

-> SELECT runoob_id,runoob_title,

-> runoob_author,submission_date

-> FROM runoob_tbl;

复制表方法二：

CREATE TABLE targetTable LIKE sourceTable;

INSERT INTO targetTable SELECT * FROM sourceTable;

来给大家区分下mysql复制表的两种方式。

第一、只复制表结构到新表

create table 新表 select * from 旧表 where 1=2

或者

create table 新表 like 旧表

第二、复制表结构及数据到新表

create table新表 select * from 旧表

MySQL序列使用

AUTO_INCREMENT

mysql> CREATE TABLE insect

-> ( id INT UNSIGNED NOT NULL AUTO_INCREMENT,

-> PRIMARY KEY (id),

-> name VARCHAR(30) NOT NULL, # type of insect

-> date DATE NOT NULL, # date collected

-> origin VARCHAR(30) NOT NULL # where collected);

mysql> INSERT INTO insect (id,name,date,origin) VALUES

-> (NULL,'housefly','2001-09-10','kitchen'),

-> (NULL,'millipede','2001-09-10','driveway'),

-> (NULL,'grasshopper','2001-09-10','front yard');

mysql> SELECT * FROM insect ORDER BY id;

获取AUTO_INCREMENT值

在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

设置序列开始值：

mysql> CREATE TABLE insect

-> ( id INT UNSIGNED NOT NULL AUTO_INCREMENT,

-> PRIMARY KEY (id),

-> name VARCHAR(30) NOT NULL,

-> date DATE NOT NULL,

-> origin VARCHAR(30) NOT NULL)engine=innodb auto_increment=100 charset=utf8;

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

杜峰

最后编辑于：2019.07.10 18:20:53

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