MySQL

• MySQL

  • 数据库操作

  • 数据库表操作

  • MySQL数据类型

    • 数字型

    • 日期和时间类型

    • 字符串类型

    • 字段的约束条件

  • sql查询操作命令

    • DISTINCT

    • ORDER BY

    • WHERE

    • IN

    • NOT IN

    • BETWEEN AND

    • LIKE

    • LIMIT

    • IS NULL

  • 数据分组与连接

    • GROUP BY

  • 连接

    • INNER JOIN

    • LEFT JOIN

    • RIGHT JOIN

  • 字段处理

    • 修改字段名

    • 修改字段的数据类型

    • 增加字段

    • 删除字段

MySQL

MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成。

mysql.jpg

登陆命令：

mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码

–p密码选项不一定是要在最后； –u、-h、-p后无空格。

数据库操作

#查看数据库
show databases; 

#选择一个数据库
use 数据库名;

#创建数据库
create database 数据库名;

#查看创建数据库的SQL语言
show create database 数据库名;

#删除数据库
DROP DATABASE 数据库名;

#查看表
show tables;

数据库表操作

#选择一个数据库
use 数据库名;

#创建表
create table student(id int(11) not null,name char(20));

#查看表结构
desc student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| std_id | int(11)  | NO   |     | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

#插入数据
-- 不指定字段名称
INSERT user VALUE(1001,'king');
INSERT user VALUES(1001,'king');

-- 指定字段
insert into user(id,name) values(1001,’zhangsan’)[,(1002,lisi)];
insert user set id=1001 ,name='zhangsan',;

-- 获取其他表的数据插入
insert student(name) select name from user;


#查询数据
select * from student; 
+--------+----------+
| std_id | name     |
+--------+----------+
|   1001 | zhangsan |
+--------+----------+

#删除数据
delete from student where id=1001;

#修改数据
update student set name='LiSi' where id=1001;

#删除数据
delete from student where id=1001;

MySQL数据类型

数字型

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

int(num)代表数据不够num位的情况下，用数字0填充。数据会自动变成unsigned类型的

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY 年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038-01-19 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

字段的约束条件

• NULL：数据列可包含NULL值；

• NOT NULL：数据列不允许包含NULL值；

• ZREOFILL: 零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED

• DEFAULT：默认值；

• PRIMARY KEY：KEY 主键；

• UNIQUE KEY: 唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外

• AUTO_INCREMENT：自动递增，适用于整数类型；

• UNSIGNED：是指数值类型只能为正数；

• CHARACTER SET name：指定一个字符集；

• COMMENT：对表或者字段说明；

• FOREIGN KEY: 外键约束；

http://blog.csdn.net/lipengcn/article/details/51111667

https://github.com/jaywcjlove/mysql-tutorial/blob/master/21-minutes-MySQL-basic-entry.md

http://www.runoob.com/mysql/mysql-tutorial.html

sql查询操作命令

DISTINCT

当从表中查询数据时，您可能会得到重复的行。为了删除这些重复行，可以在SELECT语句中使用DISTINCT 子句。

SELECT  name FROM `test`

+------+
| name |
+------+
| tom  |
| amy  |
| tom  |
+------+

SELECT DISTINCT name FROM `test`;

+------+
| name |
+------+
| tom  |
| amy  |
+------+

如果查询多列数据，要每一个数据相同才会被删除。

ORDER BY

语法：

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

使用ORDER BY可以按照某个字段进行排序。 使用ASC或DESC按照升序或者降序排列。

SELECT name FROM `test` GROUP BY name DESC;

+------+
| name |
+------+
| tom  |
| mus  |
| amy  |
+------+

默认是ASC升序。

WHERE

在查询语句中使用WHERE语句来设定查询条件。

SELECT * FROM test WHERE name='tom';
+-------------+------+-----+------+
| id          | name | ps  | code |
+-------------+------+-----+------+
| 00000000123 | tom  | abc | 2221 |
| 00000000125 | tom  | abc | 0133 |
+-------------+------+-----+------+

可以结合使用AND或OR的逻辑运算符。

自定义排序函数FIELD():

SELECT 
    orderNumber, status
FROM
    orders
ORDER BY FIELD(status,
        'key_part1',
        'key_part2');

IN

在WHERE子句中，筛选符合匹配的数据。

SELECT * FROM test WHERE name IN ('tom');
+-------------+------+-----+------+
| id          | name | ps  | code |
+-------------+------+-----+------+
| 00000000123 | tom  | abc | 2221 |
| 00000000125 | tom  | abc | 0133 |
+-------------+------+-----+------+

带有子查询的MySQL IN

SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (SELECT 
            orderNumber
        FROM
            orderDetails
        GROUP BY orderNumber
        HAVING SUM(quantityOrdered * priceEach) > 60000);

运算顺序为：

-- 第一步
SELECT 
    orderNumber
FROM
    orderDetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000;

-- 第二步
SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (10165,10287,10310);

having子句在查询过程中慢于聚合语句(sum,min,max,avg,count).而where子句在查询过程中则快于聚合语。

NOT IN

与IN运算符相反。

BETWEEN AND

BETWEEN AND判断某字段值是否在给定的范围内。

select * from where userId between 5 and 7;

-- 等同于

select * from user where userId >= 5 and userId <= 7;

LIKE

LIKE操作符常用在模式匹配中查询数据。

与LIKE操作符一起使用：百分比 %和下划线_。

• percent（%）通配符允许您匹配任何零个或多个字符的字符串。

• 下划线（_）通配符允许您匹配任何单个字符。

-- 查找m结尾的数据
SELECT 
    name
FROM
    employees
WHERE
    firstname LIKE '%m';

-- 查找包含‘_20’字符的数据，ESCAPE 后面指定转义字符，默认为斜杠/
SELECT 
    productCode, productName
FROM
    products
WHERE
    productCode LIKE '%$_20%' ESCAPE '$';

LIMIT

LIMIT子句在SELECT语句中用于约束结果集中的行数。

SELECT
    customernumber,
    customername,
    creditlimit
FROM
    customers
ORDER BY
    creditlimit DESC
LIMIT 3;

+----------------+------------------------------+-------------+
| customernumber | customername                 | creditlimit |
+----------------+------------------------------+-------------+
|            141 | Euro+ Shopping Channel       |   227600.00 |
|            124 | Mini Gifts Distributors Ltd. |   210500.00 |
|            298 | Vida Sport, Ltd              |   141300.00 |
+----------------+------------------------------+-------------+

如果LIMIT后还有参数，代表偏移量，从0开始。

IS NULL

判断数据是否为空，为NULL则返回true。

SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NULL;

http://www.manongjc.com/mysql_basic/mysql-tutorial-basic.html

数据分组与连接

GROUP BY

GROUP BY是SELECT的可选部分，将数据按表达式进行分组。

聚合函数允许我们执行某组行的计算并返回一个值。GROUP BY子句通常与聚合函数来执行计算，并为每个分组返回一个值。

SELECT status,count(*) as total  from orders GROUP BY `status` ;

+------------+----------+
| status     | total    |
+------------+----------+
| Cancelled  |        6 |
| Disputed   |        3 |
| In Process |        6 |
| On Hold    |        4 |
| Resolved   |        4 |
| Shipped    |      303 |
+------------+----------+

连接

INNER JOIN

产生的结果集中，是两者某字段的交集。可以简写成join;

-- Table A 是左边的表。
-- Table B 是右边的表。
id name       id  name
-- ----       --  ----
1  **Pirate**     1   Rutabaga
2  Monkey         2   **Pirate**
3  **Ninja**      3   Darth Vader
4  Spaghetti      4   **Ninja**

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

-- 结果:
id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

img/Inner_Join.png

JOIN子句必须指定连接条件，连接条件的关键字ON在INNER JOIN语句之后。连接条件是用于在主表和其他表之间匹配行的条件。

LEFT JOIN

产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代。

SELECT * FROM TableA
LEFT JOIN TableB
ON TableA.name = TableB.name

-- 结果
id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

img/Left_Outer_Join.png

RIGHT JOIN

产生表B的完全集，而A表中匹配的则有值，没有匹配的则以null值取代。

SELECT * FROM TableA
RIGHT  JOIN TableB 
ON TableA.name = TableB.name ORDER BY TableB.name;

-- 结果
+------+-------------+----+-------------------+
| id   | name        | id | name              |
+------+-------------+----+-------------------+
| NULL | NULL        |  1 | Rutabaga          |
|    4 | Pirate      |  2 | Pirate            |
| NULL | NULL        |  3 | Darth Vader       |
|    6 | Ninja       |  4 | Ninja             |
+------+-------------+----+-------------------+

在sql查询语句中，还有FULL JOIN，但MySQL中没有支持。 具体可参考：https://code.ziqiangxuetang.com/sql/sql-join-full.html

教程：http://www.manongjc.com/mysql_basic/mysql-tutorial-basic.html

字段处理

修改字段名

ALTER TABLE 表名 Change 旧属性名 新属性名 新数据类型;

修改字段的数据类型

ALTER TABLE 表名 MODIFY 属性名 新数据类型;

增加字段

ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];

删除字段

ALTER TABLE 表名 DROP 属性名;

参考：http://blog.csdn.net/lipengcn/article/details/51119535