MySQL基本操作大全

一、概念：

数据： data

数据库： DB

数据库管理系统：DBMS

数据库系统：DBS

MySQL：数据库

mysql：客户端命令（用来连接服务或发送sql指令）

SQL：结构化查询语言，其中MySQL支持这个。

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

MySQL->库->表->数据

SQL语句中的快捷键

\G 格式化输出（文本式，竖立显示）

\s 查看服务器端信息

\c 结束命令输入操作

\q 退出当前sql命令行模式

\h 查看帮助

二、连接数据库：

mysql -h 主机名 -u 用户名 -p密码库名

C:\>mysql --采用匿名账号和密码登陆本机服务

C:\>mysql -h localhost -u root -proot --采用root账号和root密码登陆本机服务

C:\>mysql -u root -p --推荐方式默认登陆本机

Enter password: ****

C:\>mysql -u root -p lamp61 --直接进入lamp61数据库的方式登陆

三、授权：

格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';

--实例：创建zhangsan账号，密码123，授权lamp61库下所有表的增/删/改/查数据,来源地不限

mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified by '123';

mysql> grant all on *.* to zhangsan@'%' identified by '123';

Query OK, 0 rows affected (0.00 sec)

四、SQL的基本操作

mysql> show databases; --查看当前用户下的所有数据库

mysql> create database [if not exists] 数据库名; --创建数据库

mysql> use test; --选择进入test数据库

mysql> show create database 数据库名\G --查看建数据库语句

mysql> select database(); --查看当前所在数据库位置

mysql> drop database [if exists] 数据库名; --删除一个数据库

mysql> show tables; --查看当前库下的所有表格

mysql> desc tb1; --查看tb1的表结构。

mysql> show create table 表名\G --查看表的建表语句。

mysql> create table demo( --创建demo表格

-> name varchar(16) not null,

-> age int,

-> sex enum('w','m') not null default 'm');

Query OK, 0 rows affected (0.05 sec)

mysql> desc demo; --查看表结构

+-------+---------------+------+-----+---------+-------+

+-------+---------------+------+-----+---------+-------+

| age | int(11) | YES | | NULL | |

| sex | enum('w','m') | NO | | m | |

+-------+---------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql>drop table if exists mytab; -- 尝试删除mytab表格

--添加一条数据

mysql> insert into demo(name,age,sex) values('zhangsan',20,'w');

Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values('lisi',22,'m'); --不指定字段名来添加数据

Query OK, 1 row affected (0.00 sec)

mysql> insert into demo(name,age) values('wangwu',23); --指定部分字段名来添加数据

Query OK, 1 row affected (0.00 sec)

--批量添加数据

mysql> insert into demo(name,age,sex) values('aaa',21,'w'),("bbb",22,'m');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from demo; --查询数据

mysql> update demo set age=24 where name='aaa'; --修改

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> delete from demo where name='bbb'; --删除

Query OK, 1 row affected (0.00 sec)

mysql>\h -- 快捷帮助

mysql>\c -- 取消命令输入

mysql>\s -- 查看当前数据库的状态

mysql>\q -- 退出mysql命令行

五、 MySQL数据库的数据类型：

MySQL的数据类型分为四大类：数值类型、字串类型、日期类型、NULL。

5.1 数值类型：

*tinyint(1字节) 0~255 -128~127

smallint(2字节)

mediumint(3字节)

*int(4字节)

bigint(8字节)

*float(4字节) float(6,2)

*double(8字节)

decimal(自定义)字串形数值

5.2 字串类型

普通字串

*char 定长字串 char(8)

*varchar 可变字串 varchar(8)

二进制类型

tinyblob

blob

mediumblob

longblob

文本类型

tinytext

*text 常用于<textarea></textarea>

mediumtext

longtext

*enum枚举

set集合

5.3 时间和日期类型：

date 年月日

time 时分秒

datetime 年月日时分秒

timestamp 时间戳

year 年

5.4 NULL值

NULL意味着“没有值”或“未知值”

可以测试某个值是否为NULL

不能对NULL值进行算术计算

对NULL值进行算术运算，其结果还是NULL

0或NULL都意味着假，其余值都意味着真

MySQL的运算符：

算术运算符：+ - * / %

比较运算符：= > < >= <= <> !=

数据库特有的比较：in，not in, is null,is not null,like, between and

逻辑运算符：and or not

like: 支持特殊符号%和_ ; 其中 %表示任意数量的任意字符，_表示任意一位字符。

六、表的字段约束：

unsigned 无符号(正数)

zerofill 前导零填充

auto_increment 自增

default 默认值

not null 非空

PRIMARY KEY 主键（非null并不重复）

unique 唯一性（可以为null但不重复）

index 常规索引

七、建表语句格式：

create table 表名(

字段名类型 [字段约束],

字段名类型 [字段约束]

...

);

mysql> create table stu(

-> id int unsigned not null auto_increment primary key,

-> name varchar(8) not null unique,

-> age tinyint unsigned,

-> sex enum('m','w') not null default 'm',

-> classid char(6)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> desc stu;

+---------+---------------------+------+-----+---------+----------------+

+---------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | YES | | NULL | |

| sex | enum('m','w') | NO | | m | |

+---------+---------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

mysql> show create table stu\G --查看建表的语句

*************************** 1. row ***************************

Table: stu

Create Table: CREATE TABLE `stu` (

`id` int(10) unsigned NOT NULL auto_increment,

`name` varchar(8) NOT NULL,

`age` tinyint(3) unsigned default NULL,

`sex` enum('m','w') NOT NULL default 'm',

`classid` char(6) default NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql>

mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp

61');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu(name,age,sex,classid) values('lisi',22,'w','lamp61');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu(name,age,classid) values('wangwu',21,'lamp61');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu values(null,'qq',24,'w','lamp62');

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu values(null,'aa',20,'m','lamp62'),(null,'bb',25,'m','lamp

63');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from stu;

+----+----------+------+-----+---------+

| id | name | age | sex | classid |

+----+----------+------+-----+---------+

| 1 | zhangsan | 20 | m | lamp61 |

| 2 | lisi | 22 | w | lamp61 |

| 3 | wangwu | 21 | m | lamp61 |

| 4 | qq | 24 | w | lamp62 |

| 5 | aa | 20 | m | lamp62 |

| 6 | bb | 25 | m | lamp63 |

+----+----------+------+-----+---------+

6 rows in set (0.00 sec)

八、修改表结构：

-------------------------------------

格式： alter table 表名 action（更改选项）;

更改选项：

1. 添加字段：alter table 表名 add 字段名信息

例如：

-- 在user表的最后追加一个num字段设置为int not null

mysql> alter table user add num int not null;

-- 在user表的email字段后添加一个age字段，设置int not null default 20；

mysql> alter table user add age int not null default 20 after email;

-- 在user表的最前面添加一个aa字段设置为int类型

mysql> alter table user add aa int first;

2. 删除字段：alter table 表名 drop 被删除的字段名

例如：-- 删除user表的aa字段

mysql> alter table user drop aa;

3. 修改字段：alter table 表名 change[modify] 被修改后的字段信息

其中：change可以修改字段名， modify 不修改

例如：

-- 修改user表中age字段信息（类型），（使用modify关键字的目的不修改字段名）

mysql> alter table user modify age tinyint unsigned not null default 20;

-- 修改user表的num字段改为mm字段并添加了默认值（使用change可改字段名）

mysql> alter table user change num mm int not null default 10;

4. 添加和删除索引

-- 为user表中的name字段添加唯一性索引，索引名为uni_name;

mysql> alter table user add unique uni_name(name);

-- 为user表中的email字段添加普通索引，索引名为index_eamil

mysql> alter table user add index index_email(email);

-- 将user表中index_email的索引删除

mysql> alter table user drop index index_email;

5. 更改表名称：

ALTER TABLE 旧表名 RENAME AS 新表名

6. 更改AUTO_INCREMENT初始值:

ALTER TABLE 表名称 AUTO_INCREMENT=1

7. 更改表类型：

ALTER TABLE 表名称 ENGINE="InnoDB"

MySQL数据库中的表类型一般常用两种：MyISAM和InnoDB

区别：MyISAM类型的数据文件有三个frm(结构)、MYD（数据）、MYI（索引）

MyISAM类型中的表数据增删改速度快，不支持事务，没有InnoDB安全。

InnoDB类型的数据文件只有一个 .frm

InnoDB类型的表数据增删改速度没有MyISAM的快，但支持事务，相对安全。

九、数据的DML操作：添加数据，修改数据，删除数据：

----------------------------------------------------------

1. 添加数据

格式： insert into 表名[(字段列表)] values(值列表...);

--标准添加（指定所有字段，给定所有的值）

mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');

Query OK, 1 row affected (0.13 sec)

mysql>

--指定部分字段添加值

mysql> insert into stu(name,classid) value('lisi','lamp138');

Query OK, 1 row affected (0.11 sec)

-- 不指定字段添加值

mysql> insert into stu value(null,'wangwu',21,'w','lamp138');

Query OK, 1 row affected (0.22 sec)

-- 批量添加值

mysql> insert into stu values

-> (null,'zhaoliu',25,'w','lamp94'),

-> (null,'uu01',26,'m','lamp94'),

-> (null,'uu02',28,'w','lamp92'),

-> (null,'qq02',24,'m','lamp92'),

-> (null,'uu03',32,'m','lamp138'),

-> (null,'qq03',23,'w','lamp94'),

-> (null,'aa',19,'m','lamp138');

Query OK, 7 rows affected (0.27 sec)

Records: 7 Duplicates: 0 Warnings: 0

2. 修改操作：

格式：update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件

-- 将id为11的age改为35，sex改为m值

mysql> update stu set age=35,sex='m' where id=11;

Query OK, 1 row affected (0.16 sec)

Rows matched: 1 Changed: 1 Warnings: 0

-- 将id值为12和14的数据值sex改为m，classid改为lamp92

mysql> update stu set sex='m',classid='lamp92' where id=12 or id=14 --等价于下面

mysql> update stu set sex='m',classid='lamp92' where id in(12,14);

Query OK, 2 rows affected (0.09 sec)

Rows matched: 2 Changed: 2 Warnings: 0

3. 删除操作

格式：delete from 表名 [where 条件]

-- 删除stu表中id值为100的数据

mysql> delete from stu where id=100;

Query OK, 0 rows affected (0.00 sec)

-- 删除stu表中id值为20到30的数据

mysql> delete from stu where id>=20 and id<=30;

Query OK, 0 rows affected (0.00 sec)

-- 删除stu表中id值为20到30的数据（等级于上面写法）

mysql> delete from stu where id between 20 and 30;

Query OK, 0 rows affected (0.00 sec)

-- 删除stu表中id值大于200的数据

mysql> delete from stu where id>200;

Query OK, 0 rows affected (0.00 sec)

十、数据的DQL操作：数据查询

==============================================

格式：

select [字段列表]|* from 表名

[where 搜索条件]

[group by 分组字段 [having 子条件]]

[order by 排序 asc|desc]

[limit 分页参数]

mysql> select * from stu;

+----+----------+-----+-----+---------+

| id | name | age | sex | classid |

+----+----------+-----+-----+---------+

| 1 | zhangsan | 20 | m | lamp138 |

| 2 | lisi | 20 | m | lamp138 |

| 3 | wangwu | 21 | w | lamp138 |

| 4 | zhaoliu | 25 | w | lamp94 |

| 5 | uu01 | 26 | m | lamp94 |

| 6 | uu02 | 28 | w | lamp92 |

| 7 | qq02 | 24 | m | lamp92 |

| 8 | uu03 | 32 | m | lamp138 |

| 9 | qq03 | 23 | w | lamp94 |

| 10 | aa | 19 | m | lamp138 |

| 11 | sad | 35 | m | lamp94 |

| 12 | tt | 25 | m | lamp92 |

| 13 | wer | 25 | w | lamp94 |

| 14 | xx | 25 | m | lamp92 |

| 15 | kk | 0 | w | lamp94 |

+----+----------+-----+-----+---------+

15 rows in set (0.00 sec)

十一、where条件查询：

1. 查询班级为lamp138期的学生信息

mysql> select * from stu where classid='lamp138';

2. 查询lamp138期的男生信息（sex为m）

mysql> select * from stu where classid='lamp138' and sex='m';

3. 查询id号值在10以上的学生信息

mysql> select * from stu where id>10;

4. 查询年龄在20至25岁的学生信息

mysql> select * from stu where age>=20 and age<=25;

mysql> select * from stu where age between 20 and 25;

5. 查询年龄不在20至25岁的学生信息

mysql> select * from stu where age not between 20 and 25;

mysql> select * from stu where age<20 or age>25;

6. 查询id值为1,8,4,10,14的学生信息

select * from stu where id in(1,8,4,10,14);

mysql> select * from stu where id=1 or id=8 or id=4 or id=10 or id=14;

7. 查询lamp138和lamp94期的女生信息

mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';

mysql> select * from stu where (classid='lamp138' or classid='lamp94') and sex='w

十二、导入和导出：

-----------------------------------

-- 将lamp138库导出

D:\>mysqldump -u root -p lamp138 >lamp138.sql

Enter password:

---- 将lamp138库中的stu表导出

D:\>mysqldump -u root -p lamp138 stu >lamp138_stu.sql

Enter password:

-- 将lamp138库导入

D:\>mysql -u root -p lamp138<lamp138.sql

Enter password:

-- 将lamp138库中stu表导入

D:\>mysql -u root -p lamp138<lamp138_stu.sql

Enter password:

D:\>

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

MySQL基本操作大全

推荐阅读更多精彩内容