MySQL语法

作者: Mr_码客 | 来源:发表于2017-05-24 18:37 被阅读0次

MySQL教程
mysql查询
MySQL教程
MySQL学习笔记一之MySQL语法
Mysql Join语法解析与性能分析
Mysql——进阶语法
查看Mysql执行计划
SQL
mysql 找回误删表的数据方法(必看)
java 与mysql对应类型以及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>

MySQL教程
MySQL 入门教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL ...
mysql查询
mysql 查询语法
MySQL教程
MySQL 入门教程MySQL 安装MySQL 管理MySQL PHP 语法MySQL 连接MySQL 创建数据库...
MySQL学习笔记一之MySQL语法
title: MySQL学习笔记一之MySQL语法tags: MySQL 数据库categories: MySQL...
Mysql Join语法解析与性能分析
Mysql Join语法解析与性能分析Mysql - JOIN详解
Mysql——进阶语法
Mysql-进阶语法导语在上一篇《MySQL的基本语法》中我们了解了mysql基本的增删改查的语句。本篇我们将...
查看Mysql执行计划
1、MySQL语法 MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个”EXPLAIN”即可...
SQL
一、SQL语法实例：mysql> use RUNOOB ; mysql> setnames utf8 ...
mysql 找回误删表的数据方法(必看)
mysqlbinlog 命令的语法格式：mysqlbinlog mysql-bin.0000xx | mysql ...
java 与mysql对应类型以及MySQl基本语法
java 与mysql对应类型以及MySQl基本语法Mysql,Oracle,Java数据类型对应 Apache ...

MySQL语法

1.创建数据库

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

3.创建数据库表

4.向表中插入数据

5.查询表中的数据

6.更新表中的数据

7.删除表中的数据

8.创建后表的修改

9.UNION 操作符

10.排序

10.GROUP BY

11.连接

12.NULL

相关文章

MySQL教程

mysql查询

MySQL教程

MySQL学习笔记一之MySQL语法

Mysql Join语法解析与性能分析

Mysql——进阶语法

查看Mysql执行计划

SQL

mysql 找回误删表的数据方法(必看)

java 与mysql对应类型以及MySQl基本语法

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读