MySQL语法

1.创建数据库

mysql> create database samp_db character set gbk;

Query OK, 1 row affected

2.选择所要操作的数据库

mysql> use samp_db;

Database changed

3.创建数据库表

create table students
    (
        id int unsigned not null auto_increment primary key,
        name char(8) not null,
        sex char(4) not null,
        age tinyint unsigned not null,
        tel char(13) null default "-"
    );

Query OK, 0 rows affected

4.向表中插入数据

语法：

insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);

mysql> insert into students values(NULL, "王刚", "男", 20, "13811371377");

Query OK, 1 row affected

mysql> insert into students (name, sex, age) values("孙丽华", "女", 21);

Query OK, 1 row affected

5.查询表中的数据

语法：

select 列名称 from 表名称 [查询条件];

mysql> select name, age from students;
+--------+-----+
| name   | age |
+--------+-----+
| 王刚   |  20 |
| 孙丽华 |  21 |
+--------+-----+
2 rows in set

查询表中所有内容：

mysql> select * from students;
+----+--------+-----+-----+-------------+
| id | name   | sex | age | tel         |
+----+--------+-----+-----+-------------+
|  1 | 王刚   | 男  |  20 | 13811371377 |
|  3 | 孙丽华 | 女  |  21 | -           |
+----+--------+-----+-----+-------------+
2 rows in set

按特定条件查询:
where 关键词用于指定查询条件, 用法形式为: select 列名称 from 表名称 where 条件;

mysql> select * from students where sex="女";
+----+--------+-----+-----+-----+
| id | name   | sex | age | tel |
+----+--------+-----+-----+-----+
|  3 | 孙丽华 | 女  |  21 | -   |
+----+--------+-----+-----+-----+
1 row in set

where 子句不仅仅支持 "where 列名 = 值" 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如 =、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。

mysql> select * from students where age > 20;
+----+--------+-----+-----+-----+
| id | name   | sex | age | tel |
+----+--------+-----+-----+-----+
|  3 | 孙丽华 | 女  |  21 | -   |
+----+--------+-----+-----+-----+
1 row in set

mysql> select * from students where name like "%王%";
+----+------+-----+-----+-------------+
| id | name | sex | age | tel         |
+----+------+-----+-----+-------------+
|  1 | 王刚 | 男  |  20 | 13811371377 |
+----+------+-----+-----+-------------+
1 row in set

mysql> select * from students where id<5 and age>20;
+----+--------+-----+-----+-----+
| id | name   | sex | age | tel |
+----+--------+-----+-----+-----+
|  3 | 孙丽华 | 女  |  21 | -   |
+----+--------+-----+-----+-----+
1 row in set

6.更新表中的数据

语法：update 表名称 set 列名称=新值 where 更新条件;

mysql> update students set tel = "13288097888" where name = "孙丽华";
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
+----+--------+-----+-----+-------------+
| id | name   | sex | age | tel         |
+----+--------+-----+-----+-------------+
|  1 | 王刚   | 男  |  20 | 13811371377 |
|  3 | 孙丽华 | 女  |  21 | 13288097888 |
+----+--------+-----+-----+-------------+

7.删除表中的数据

delete from 表名称 where 删除条件;

mysql> delete from students where id = 3;
Query OK, 1 row affected
+----+------+-----+-----+-------------+
| id | name | sex | age | tel         |
+----+------+-----+-----+-------------+
|  1 | 王刚 | 男  |  20 | 13811371377 |
+----+------+-----+-----+-------------+

8.创建后表的修改

添加列

基本形式: alter table 表名 add 列名列数据类型 [after 插入位置];

mysql> alter table students add address char(60) after tel;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
+----+------+-----+-----+-------------+---------+
| id | name | sex | age | tel         | address |
+----+------+-----+-----+-------------+---------+
|  1 | 王刚 | 男  |  20 | 13811371377 | NULL    |
+----+------+-----+-----+-------------+---------+

修改列

alter table 表名 change 列名称列新名称新数据类型;

mysql> alter table students change tel telphone char(13) default "-";
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

删除列

alter table 表名 drop 列名称;

mysql> alter table students drop address;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
+----+------+-----+-----+-------------+
| id | name | sex | age | telphone    |
+----+------+-----+-----+-------------+
|  1 | 王刚 | 男  |  20 | 13811371377 |
+----+------+-----+-----+-------------+

重命名表

alter table 表名 rename 新表名;

mysql> alter table students rename maqi;
Query OK, 0 rows affected

删除整张表

drop table workmates;

删除整个数据库

drop database samp_db;

9.UNION 操作符

Websites:
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.cm/    |  1    | USA     |
|  2 | 淘宝          | https://www.taobao.com/   |  13   | CN      |
|  3 | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
|  4 | 微博          | http://weibo.com/         | 20    | CN      |
|  5 | Facebook      | https://www.facebook.com/ | 3     | USA     |
|  7 | stackoverflow | http://stackoverflow.com/ |  0    | IND     |
+----+---------------+---------------------------+-------+---------+

apps:
+----+----------+-------------------------+---------+
| id | app_name | url                     | country |
+----+----------+-------------------------+---------+
|  1 | QQ APP   | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+----------+-------------------------+---------+

UNION:去重

mysql> select country from Websites
    -> UNION
    -> select country from apps
    -> order by country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set

UNION ALL :显示所有

mysql> select country from Websites
    -> UNION ALL
    -> select country from apps
    -> order by country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+

UNION ALL :where

mysql> select name,country from Websites
    -> where country ='CN'
    -> UNION All
    -> select app_name ,country from apps
    -> where country = 'CN'
    -> order by country;
+----------+---------+
| name     | country |
+----------+---------+
| 淘宝     | CN      |
| 菜鸟教程 | CN      |
| 微博     | CN      |
| QQ APP   | CN      |
| 微博 APP | CN      |
| 淘宝 APP | CN      |
+----------+---------+

10.排序

正序：ASC，逆序：DESC

mysql> select * from runoob_tbl order by submission_date ASC;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      |  2017-04-12     |
|         4 | 学习 Java    | RUNOOB.COM    | 2015-05-01      |
|         3 |  学习 Python | RUNOOB.COM    | 2016-03-06      |
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
+-----------+--------------+---------------+-----------------+

10.GROUP BY

GROUP BY 语法

mysql> SELECT * FROM employee_tbl;
+----+------+---------------------+--------+
| id | name | date                | singin |
+----+------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+------+---------------------+--------+

mysql> select name,COUNT(*) from employee_tbl group by name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+------+----------+

使用 WITH ROLLUP

mysql> select name, sum(singin) as signin_count from employee_tbl group by name with ROLLUP;
+------+--------------+
| name | signin_count |
+------+--------------+
| 小丽 | 2            |
| 小明 | 7            |
| 小王 | 7            |
| NULL | 16           |
+------+--------------+

select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。


mysql> select coalesce(name,'总数'), sum(singin) as signin_count from employee_tbl group by name with ROLLUP;
+-----------------------+--------------+
| coalesce(name,'总数') | signin_count |
+-----------------------+--------------+
| 小丽                  | 2            |
| 小明                  | 7            |
| 小王                  | 7            |
| 总数                  | 16           |
+-----------------------+--------------+

11.连接

内连接

mysql> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      |  2017-04-12     |
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java    | RUNOOB.COM    | 2015-05-01      |
|         4 |  学习 Python | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C       | FK            | 2017-04-05      |
+-----------+--------------+---------------+-----------------+
5 rows in set

mysql> SELECT * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set

mysql> SELECT a.runoob_id,a.runoob_title,a.runoob_author,b.runoob_count FROM runoob_tbl a 
    -> INNER JOIN tcount_tbl b
    -> ON a.runoob_author = b.runoob_author;
+-----------+--------------+---------------+--------------+
| runoob_id | runoob_title | runoob_author | runoob_count |
+-----------+--------------+---------------+--------------+
|         1 | 学习 PHP     | 菜鸟教程      |           10 |
|         2 | 学习 MySQL   | 菜鸟教程      |           10 |
|         3 | 学习 Java    | RUNOOB.COM    |           20 |
|         4 |  学习 Python | RUNOOB.COM    |           20 |
+-----------+--------------+---------------+--------------+
4 rows in set

mysql> SELECT a.runoob_id,a.runoob_title,a.runoob_author,b.runoob_count FROM runoob_tbl a,tcount_tbl b
    -> WHERE a.runoob_author = b.runoob_author;
+-----------+--------------+---------------+--------------+
| runoob_id | runoob_title | runoob_author | runoob_count |
+-----------+--------------+---------------+--------------+
|         1 | 学习 PHP     | 菜鸟教程      |           10 |
|         2 | 学习 MySQL   | 菜鸟教程      |           10 |
|         3 | 学习 Java    | RUNOOB.COM    |           20 |
|         4 |  学习 Python | RUNOOB.COM    |           20 |
+-----------+--------------+---------------+--------------+
4 rows in set

左连接

MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

mysql> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      |  2017-04-12     |
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java    | RUNOOB.COM    | 2015-05-01      |
|         4 |  学习 Python | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C       | FK            | 2017-04-05      |
+-----------+--------------+---------------+-----------------+
5 rows in set

mysql> SELECT * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set

mysql> SELECT a.runoob_id,a.runoob_title,a.runoob_author,b.runoob_count FROM runoob_tbl a 
    -> LEFT JOIN tcount_tbl b
    -> ON a.runoob_author = b.runoob_author;
+-----------+--------------+---------------+--------------+
| runoob_id | runoob_title | runoob_author | runoob_count |
+-----------+--------------+---------------+--------------+
|         1 | 学习 PHP     | 菜鸟教程      |           10 |
|         2 | 学习 MySQL   | 菜鸟教程      |           10 |
|         3 | 学习 Java    | RUNOOB.COM    |           20 |
|         4 |  学习 Python | RUNOOB.COM    |           20 |
|         5 | 学习 C       | FK            | NULL         |
+-----------+--------------+---------------+--------------+
5 rows in set

右连接

读取右边数据表的全部数据，即便左边边表无对应数据。


mysql> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      |  2017-04-12     |
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java    | RUNOOB.COM    | 2015-05-01      |
|         4 |  学习 Python | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C       | FK            | 2017-04-05      |
+-----------+--------------+---------------+-----------------+
5 rows in set

mysql> SELECT * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set

mysql> SELECT a.runoob_id,a.runoob_title,a.runoob_author,b.runoob_count FROM runoob_tbl a 
    -> RIGHT JOIN tcount_tbl b
    -> ON a.runoob_author = b.runoob_author;
+-----------+--------------+---------------+--------------+
| runoob_id | runoob_title | runoob_author | runoob_count |
+-----------+--------------+---------------+--------------+
|         1 | 学习 PHP     | 菜鸟教程      |           10 |
|         2 | 学习 MySQL   | 菜鸟教程      |           10 |
|         3 | 学习 Java    | RUNOOB.COM    |           20 |
|         4 |  学习 Python | RUNOOB.COM    |           20 |
| NULL      | NULL         | NULL          |           22 |
+-----------+--------------+---------------+--------------+
5 rows in set

12.NULL

mysql> SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        |           20 |
| 菜鸟教程      | NULL         |
| Google        | NULL         |
| FK            |           20 |
+---------------+--------------+
4 rows in set

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set

mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        |           20 |
| FK            |           20 |
+---------------+--------------+
2 rows in set

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count <=> NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set

mysql>

最后编辑于：2017.12.07 17:21:44

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