MySQL基础1——命令行操作MySQL

1. 连接MySQL数据库

打开命令行终端程序，windows CMD（首先要在系统环境变量中配置好MySQL的路径，具体方法请自行百度）或者使用cmder软件，或者在Navicat for MySQL中F6打开终端

输入命令 mysql -uroot -p 输入mysql的密码回车进入数据库
或者输入mysql -uroot -pxxx or mysql -uroot -pxxx -A 直接进入数据库。
root表示你进入数据库使用的用户名,xxx表示对应的密码,-A表示切换数据库的时候不会预读取数据库信息，这样能够更快地切换数据库。

2.查看mysql里面都有哪些数据库

show databases;//显示MySQL/data/目录下所有文件夹

3. 创建数据库test

create database <tablename> charset utf8;//注意创建的数据库的名称只能以字母或下划线开头(推荐使用小写字母，windows下不区分大小写，即使以大写字母命名，创建之后还是小写) Linux中区分大小写

4. 删除数据库

drop database test;//注意，删除数据库会把里面的表也一起删除

5. 切换数据库

use test;//切换到test数据库

6. 查看本数据库下所有表

show tables;

7. 查看tag表中的所有数据

select * from tag;

8. 查询表结构

desc tag;//查询tag表的表结构

查看系统字符集设置变量

mysql> show variables like "%character%";  # 查看系统字符集设置变量
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

9.创建一张表，表名为stu,包含主键，学生姓名，性别，年龄，班级id

create table stu(id int primary key auto_increment,name char(10) not null default '',sex enum('男','女') not null default '男',cid tinyint unsigned not null default 1); 这里的name char(10)表示name这个字段最多有10个字符，不区分汉字字母数字。

注释：建表的注意事项。

最好每一张表都设主键，根据表可能要存储的数据量来选择尽可能小的数据类型。主键自增。其他字段能用数值类型的不要用字符串类型。int后如果不限定长度，不限定非负，默认为int(11)；tinyint默认为tinyint(3)

性别字段用enum类型，enum后面的括号内是字符串，要加引号，但是MySQL处理enum是按数值类型储存的，所以比直接用字符串要快。

尽量不要有NULL值，text类型（文字内容字段的类型比如 BLOB, TEXT, GEOMETRY or JSON）不能有默认值

查看表生成的DDL：(数据定义语言(Data Definition Language))
show create table stu;

10. 往stu表中添加一条数据

insert into stu set name = '小明';
显示如下：

mysql> insert into stu set name = '小明';
Query OK, 1 row affected (0.11 sec)
mysql> select * from stu;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
|  1 | 小明 | 男  |   1 |
+----+------+-----+-----+
1 row in set (0.00 sec)

因为主键id自增，sex和cid都有默认值，所以只填写name值，其它项也不会有null值
一次填写多个字段，中间用,分割，如

mysql> insert into stu set name = '小红',sex = '女',cid = 2;
Query OK, 1 row affected (0.12 sec)
mysql> select * from stu;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
|  1 | 小明 | 男  |   1 |
|  2 | 小红 | 女  |   2 |
+----+------+-----+-----+
2 rows in set (0.00 sec)

一次添加多条多个字段可以使用这样的语句
insert into stu (name,sex,cid) value ('小刚','男',1),('Lucy','女',3),('李雷','男',1),('Lily','女',2); //句中的value也可以用values

11. 复制表操作

复制表结构
create table stu1 like stu;//按stu表的结构创建一个stu1表，
看一下效果：

mysql> desc stu1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11)          | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     |         |                |
| sex   | enum('男','女')  | NO   |     | 男      |                |
| cid   | int(10) unsigned | NO   |     | 1       |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> desc stu;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11)          | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     |         |                |
| sex   | enum('男','女')  | NO   |     | 男      |                |
| cid   | int(10) unsigned | NO   |     | 1       |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

两张表具有相同的表结构

复制表数据
insert into stu1 select * from stu;//把表stu中的数据全部添加到stu1中
同时复制表结构和表数据
create table stu2 select * from stu; # 这种方法会丢失字段的主键属性和主键自增属性。还需要手动修改添加这些属性(如果原表有的话)
alter table stu2 add primary key(id)
alter table stu2 modify id int unsigned auto_increment;

复制表结构还有2种方法，比如
create table c1 select*from stu limit 0 # 只复制结构，不用数据
create table c2 select*from stu where 1=2 # where 之后跟一个false,或者bool为false的运算式，也不会复制表数据。
同样这两种方法会丢失表的主键和自增属性（不推荐）
推荐方式是分2步，先复制表结构，再复制表数据。或者备份表，再导入。

12. 删除表中的数据

删除1条数据

mysql> select * from stu2;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
|  1 | 小明 | 男  |   1 |
|  2 | 小红 | 女  |   2 |
| 15 | 小刚 | 男  |   1 |
| 16 | Lucy | 女  |   3 |
| 17 | 李雷 | 男  |   1 |
| 18 | Lily | 女  |   2 |
+----+------+-----+-----+
6 rows in set (0.00 sec)

mysql> delete from stu2 where id = 15;//删除id为15的这条数据
Query OK, 1 row affected (0.13 sec)

###### 8. 有如下表，写出显示各班及格和不及格人数的sql
mysql> select * from stu2;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
|  1 | 小明 | 男  |   1 |
|  2 | 小红 | 女  |   2 |
| 16 | Lucy | 女  |   3 |
| 17 | 李雷 | 男  |   1 |
| 18 | Lily | 女  |   2 |
+----+------+-----+-----+

一次删除多条数据

mysql> delete from stu2 where id in (1,2,18);
Query OK, 3 rows affected (0.14 sec)

mysql> select * from stu2;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
| 16 | Lucy | 女  |   3 |
| 17 | 李雷 | 男  |   1 |
+----+------+-----+-----+
2 rows in set (0.00 sec)

如果不加where限定，会删除表中所有数据

mysql> delete from stu2;
Query OK, 2 rows affected (0.12 sec)

mysql> select * from stu2;
Empty set (0.00 sec)

清空表数据用truncate+表名，如：

mysql> select * from stu1;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
|  1 | 小明 | 男  |   1 |
|  2 | 小红 | 女  |   2 |
| 15 | 小刚 | 男  |   1 |
| 16 | Lucy | 女  |   3 |
| 17 | 李雷 | 男  |   1 |
| 18 | Lily | 女  |   2 |
+----+------+-----+-----+
6 rows in set (0.00 sec)

mysql> truncate stu1;//清空表stu1的所有数据
Query OK, 0 rows affected (0.08 sec)

mysql> select * from stu1;
Empty set (0.00 sec)

truncate和delete from+表名清空表的区别
delete from+表名清空表数据只是把表中的数据全部删除，还会保留主键自增的位置，比如当前表中最后一条数据的id为20，清空表之后，再添加1条数据，那么新增的那条数据的id为21.而如果用truncate清空表，新增数据的id会从1开始。

13.修改表操作

修改表名 alter table 旧表名 rename 新表名;

mysql> show tables;
+---------------+
| Tables_in_stu |
+---------------+
| stu           |
| stu1          |
| stu2          |
+---------------+
3 rows in set (0.00 sec)

mysql> alter table stu rename student;
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+---------------+
| Tables_in_stu |
+---------------+
| stu1          |
| stu2          |
| student       |
+---------------+
3 rows in set (0.00 sec)

修改字段名，用change
alter table student change name mingzi varchar(20) not null default '';
看例子：

mysql> select * from student;
+----+------+-----+-----+
| id | name | sex | cid |
+----+------+-----+-----+
|  1 | 小明 | 男  |   1 |
|  2 | 小红 | 女  |   2 |
| 15 | 小刚 | 男  |   1 |
| 16 | Lucy | 女  |   3 |
| 17 | 李雷 | 男  |   1 |
| 18 | Lily | 女  |   2 |
+----+------+-----+-----+
6 rows in set (0.00 sec)

mysql> alter table student change name mingzi varchar(20) not null default '';//把name字段改为mingzi，类型从char(10)改为varchar(20),其他不变。注意，改了字段后面一定别忘了加上对应的字段值类型
Query OK, 6 rows affected (0.30 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+-----+-----+
| id | mingzi | sex | cid |
+----+--------+-----+-----+
|  1 | 小明   | 男  |   1 |
|  2 | 小红   | 女  |   2 |
| 15 | 小刚   | 男  |   1 |
| 16 | Lucy   | 女  |   3 |
| 17 | 李雷   | 男  |   1 |
| 18 | Lily   | 女  |   2 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)

mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11)          | NO   | PRI | NULL    | auto_increment |
| mingzi | varchar(20)      | NO   |     |         |                |
| sex    | enum('男','女')  | NO   |     | 男      |                |
| cid    | int(10) unsigned | NO   |     | 1       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

修改字段，不改字段名，用modify
alter table student modify mingzi char(12);//把mingzi字段的类型改为char(12),后面没有写not null default '',看看表结构的变化：

mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11)          | NO   | PRI | NULL    | auto_increment |
| mingzi | char(12)         | YES  |     | NULL    |                |
| sex    | enum('男','女')  | NO   |     | 男      |                |
| cid    | int(10) unsigned | NO   |     | 1       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

默认值变成null了。

添加一个字段addtime
alter table student add addtime int(10) not null default 0;
看一下变化：

mysql> desc student;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(11)          | NO   | PRI | NULL    | auto_increment |
| mingzi  | char(12)         | YES  |     | NULL    |                |
| sex     | enum('男','女')  | NO   |     | 男      |                |
| cid     | int(10) unsigned | NO   |     | 1       |                |
| addtime | int(10)          | NO   |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

改一下cid字段

mysql> alter table student modify cid tinyint unsigned not null default 1;
Query OK, 6 rows affected (0.28 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(11)             | NO   | PRI | NULL    | auto_increment |
| mingzi  | char(12)            | YES  |     | NULL    |                |
| sex     | enum('男','女')     | NO   |     | 男      |                |
| cid     | tinyint(3) unsigned | NO   |     | 1       |                |
| addtime | int(10)             | NO   |     | 0       |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

由此可见tinyint默认会修改为tinyint(3)

删除一个字段：alter table 表名 drop 要删除的字段名;比如：
alter table student drop addtime;
删除自增
alter table student modify id int not null;//即修改主键字段的修饰语，去掉auto_increment，覆盖掉原来的
看效果：

mysql> alter table student modify id int not null;
Query OK, 6 rows affected (0.29 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(11)             | NO   | PRI | NULL    |       |
| mingzi | char(12)            | YES  |     | NULL    |       |
| sex    | enum('男','女')     | NO   |     | 男      |       |
| cid    | tinyint(3) unsigned | NO   |     | 1       |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.06 sec)

删除主键,alter table 表名 drop primary key;
注意：删除主键之前必须先要删除主键的自增，否则会报错。

mysql> alter table student drop primary key;
Query OK, 6 rows affected (0.42 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(11)             | NO   |     | NULL    |       |
| mingzi | char(12)            | YES  |     | NULL    |       |
| sex    | enum('男','女')     | NO   |     | 男      |       |
| cid    | tinyint(3) unsigned | NO   |     | 1       |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

14. 有如下表，写出显示各班及格和不及格人数的sql

+----+------+-----+-----+-------+
| id | name | sex | cid | score |
+----+------+-----+-----+-------+
|  1 | 小明 | 男  |   1 |   100 |
|  2 | 小红 | 女  |   2 |    30 |
|  3 | 小刚 | 男  |   1 |    66 |
|  4 | Lucy | 女  |   3 |    90 |
|  5 | 李雷 | 男  |   1 |    30 |
|  6 | Lily | 女  |   2 |    77 |
+----+------+-----+-----+-------+

mysql> select cid,sum(if(score>=60,1,0)) 及格,sum(if(score<60,1,0)) 不及格 from student group by cid;
+-----+------+--------+
| cid | 及格 | 不及格 |
+-----+------+--------+
|   1 |    2 |      1 |
|   2 |    1 |      1 |
|   3 |    1 |      0 |
+-----+------+--------+
3 rows in set (0.00 sec)

如果这篇文章对你有帮助，不妨点个赞哦 (˙˘˙)ᓂ--♡

最后编辑于：2018.09.14 18:33:46

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