MySQL 学习笔记（二）基础操作

创建数据库

mysql> create database test;

显示所有数据库

mysql> show databases;

mysql> use test

删除数据库

mysql> drop database test1

创建表


mysql> create table student(name Varchar(20), parents varchar(20), sex char(1),

    -> birth Date);

删除表内容

1 2	mysql> DELETE FROM STUDENT;

显示所有表


mysql> show tables ;

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

| Tables_in_test |

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

| student        |

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

1 row in set (0.00 sec)`

显示当前表


mysql> select database();

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

| database() |

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

| test       |

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

1 row in set (0.00 sec)

如果当前没有使用表，则返回NUll.

显示表详情


mysql> describe student;

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

| Field   | Type        | Null | Key | Default | Extra |

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

| name    | varchar(20) | YES  |     | NULL    |       |

| parents | varchar(20) | YES  |     | NULL    |       |

| sex     | char(1)     | YES  |     | NULL    |       |

| birth   | date        | YES  |     | NULL    |       |

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

4 rows in set (0.03 sec)

向表内添加内容

可以通过LOAD DATA和INSERT语句向新建的表内插入数据。

通过load data 添加数据

我们先按照自己的想法在txt文件中写入如下数据，命名为1.txt.


LILEI JAY M 1998-07-23

HANMEIMEI JONE F 1035-23

导入student表.


mysql> load data local infile 'D:/mysql-5.7.13/1.txt' into table student;

Query OK, 3 rows affected, 2 warnings (0.03 sec)

Records: 3  Deleted: 0  Skipped: 0  Warnings: 2

查询表内容


mysql> select * from student ;

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

| name                 | parents | sex  | birth |

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

| LILEI JAY M 1998-07- | NULL    | NULL | NULL  |

| HANMEIMEI JONE F 103 | NULL    | NULL | NULL  |

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

2 rows in set (0.00 sec)

和我们想要的不一样。分析一下原因，上面我们的txt文件用的是空格隔开的，但其实应该用制表符隔开。


LILEI	 JAY	M	1998-07-23

HANMEIMEI	 JONE	F	1035-23

重新插入，查询。


mysql> select * from student;

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

| name                 | parents | sex  | birth      |

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

| LILEI JAY M 1998-07- | NULL    | NULL | NULL       |

| HANMEIMEI JONE F 103 | NULL    | NULL | NULL       |

| LILEI                | JAY     | M    | 1998-07-23 |

| HANMEIMEI            | JONE    | F    | 0000-00-00 |

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

不符合DATE格式的生日，自动变成了0000-00-00格式。如果值为NULL，则在txt文件中记录为\N.


LILEI	 JAY	M	1998-07-23

HANMEIMEI	null	\n	1035-23

ZHANGSAN	（空格）	\N	1998-07-23

插入，查询。


mysql> load data local infile 'D:/mysql-5.7.13/1.txt' into table student;

mysql> select * from student;

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

| name                 | parents | sex  | birth      |

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

| LILEI                |  JAY    | M    | 1998-07-23 |

| HANMEIMEI            | null    |

    | 0000-00-00 |

| ZHANGSAN             |         | NULL | 1998-07-23 |

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

总结： LOAD导入数据时，用制表符隔开关键字，用\N表示NULL，用换行符隔开语句。

如果上面语句执行失败，可能是因为您安装的MySQL基于安全问题考虑，默认情况不启用本地文件功能。具体可查询 MySQL学习笔记（五）安全性/ LOAD DATA LOCAL安全问题

通过`INSERT`来插入数据

我们可以通过INSERT关键字按照表的样式来插入单行数据，在SQL标准中应使用单引号，在MySQL中对SQL进行了扩展，允许使用单引号和双引号两种。


mysql> INSERT INTO STUDENT

    -> VALUES("WANG","Dian","M","2010-08-23");

Query OK, 1 row affected (0.06 sec)


mysql> INSERT INTO STUDENT

    -> VALUES('WANG','Dian','M',NULL);

Query OK, 1 row affected (0.06 sec)

查询一下结果，均插入成功。


mysql> SELECT * FROM STUDENT;

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

| name      | parents | sex  | birth      |

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

| LILEI     |  JAY    | M    | 1998-07-23 |

| HANMEIMEI | null    | F    | 1996-12-12 |

| ZHANGSAN  | Jone    | F    | 1998-07-23 |

| WANG      | Dian    | M    | 2010-08-23 |

| WANG      | Dian    | M    | NULL       |

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

向表中添加字段

通过alter add命令增加表的字段。

通常格式是alter table 表名 add 字段类型其他

例


mysql> alter table student add lesson varchar(30);

Query OK, 0 rows affected (0.55 sec)

Records: 0  Duplicates: 0  Warnings: 0



mysql> describe student;

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

| Field   | Type        | Null | Key | Default | Extra |

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

| name    | varchar(20) | YES  |     | NULL    |       |

| parents | varchar(20) | YES  |     | NULL    |       |

| sex     | char(1)     | YES  |     | NULL    |       |

| birth   | date        | YES  |     | NULL    |       |

| lesson  | varchar(30) | YES  |     | NULL    |       |

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

如果不加说明会自动添加在最后一列的位置，如果希望添加在指定的一列，可以用

alter table 表名 add 字段类型 after 已有列名

例如 alter table student add lesson varchar(20) after name 将会插入在name后的位置。若想添加到第一列的话，则是alter table student add lesson varchar(20) first 。

删除字段

alter table 表名 drop field_name

例：


mysql> alter table student drop lesson;

Query OK, 0 rows affected (0.42 sec)

Records: 0  Duplicates: 0  Warnings: 0



mysql> describe student;

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

| Field   | Type        | Null | Key | Default | Extra |

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

| name    | varchar(20) | YES  |     | NULL    |       |

| parents | varchar(20) | YES  |     | NULL    |       |

| sex     | char(1)     | YES  |     | NULL    |       |

| birth   | date        | YES  |     | NULL    |       |

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

4 rows in set (0.00 sec)

向表中添加主键

添加一个自动增长的id作为主键。

mysql> alter table student add id int(10) auto_increment not null first, add pri
mary key(id);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------------+------+------------+----------+------------+
| id | name       | parents    | sex  | birth      | lesson   | teacher    |
+----+------------+------------+------+------------+----------+------------+
|  1 | LiLei      | JiangJiang | F    | 2008-09-14 | Math     | NULL       |
|  2 | JiangJiang | Jeon       | F    | 1998-02-14 | Math     | NULL       |
|  3 | HanMei     | JAY        | M    | 2010-07-23 | Math     | NULL       |
|  4 | WangLiLi   | WangJing   | M    | 2009-06-19 | Language | NULL       |
|  5 | Lucy       | JingJE     | M    | 2009-05-28 | Computer | NULL       |
|  6 | Lucy       | Jingee     | M    | 2011-04-28 | History  | NULL       |
|  7 | YangGuang  | YangYun    | F    | 1996-03-24 | History  | NULL       |
|  8 | ZhangYue   | ZhangGuo   | F    | 2007-04-13 | Science  | NULL       |

删除自动增加

1	mysql> alter table student change id id int(10);

删除主键
要删除主键，先要删除自动增加，然后取消主键。

1	mysql> alter table student drop primary key;

从表中筛选查询信息

通常的格式是


SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

更新数据

1 2	mysql> update student set birth="1998-09-12" where name='LIUYI';

选择特定的行

查询1998年7月23日之后出生的学生。


mysql> select * from student where birth>='1998-07-23';

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

| name     | parents | sex  | birth      |

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

| LILEI    |  JAY    | M    | 1998-07-23 |

| WANGMING | JANE    | M    | 2012-03-24 |

| LIUYI    | LIUSHAN | F    | 1998-09-12 |

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

3 rows in set (0.00 sec)

查询查询1998年7月23日之后出生的女学生。


mysql> select * from student where birth>='1998-07-23' and sex='F';

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

| name  | parents | sex  | birth      |

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

| LIUYI | LIUSHAN | F    | 1998-09-12 |

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

1 row in set (0.00 sec)

逻辑运算符除了可以用AND外还可以用OR。例如，查询1998年7月23日之后出生的女学生或者是1998年7月23日之前出生的男学生的姓名，多个查询语句最好用()分隔开。


mysql> select name,birth from student where (birth >='1998-07-23' and sex='F')

    -> or ( birth <='1998-07-23' and sex = 'M');

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

| name  | birth      |

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

| LILEI | 1998-07-23 |

| LIUYI | 1998-09-12 |

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

2 rows in set (0.00 sec)

将结果排序

ORDER 默认为升序，若要降序输出的话，添加DESC关键字。


mysql> select name,birth from student order by name, birth DESC;

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

| name      | birth      |

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

| HANMEIMEI | 1996-12-12 |

| LILEI     | 1998-07-23 |

| LIUYI     | 1998-09-12 |

| WANGMING  | 2012-03-24 |

| ZHANGSAN  | 1997-07-23 |

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

5 rows in set (0.00 sec)

时间计算函数

TIMESTAMPDIFF() 函数可以计算两个时间的时间差，我们可以用as来给输出的数据重命名一个标签，CURDATE()函数表示现在的时间。


mysql> select name,birth,CURDATE(),

    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

    -> from student order by age;

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

| name      | birth      | CURDATE()  | age  |

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

| WANGMING  | 2012-03-24 | 2017-06-27 |    5 |

| LILEI     | 1998-07-23 | 2017-06-27 |   18 |

| LIUYI     | 1998-09-12 | 2017-06-27 |   18 |

| ZHANGSAN  | 1997-07-23 | 2017-06-27 |   19 |

| HANMEIMEI | 1996-12-12 | 2017-06-27 |   20 |

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

5 rows in set (0.04 sec)

WHERE用法，查找生日是7月，父母不为空的学生。


mysql> select name,parents,birth from student

    -> where parents IS NOT NULL and  MONTH(birth)=7;

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

| name     | parents | birth      |

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

| LILEI    |  JAY    | 1998-07-23 |

| ZHANGSAN | Jone    | 1997-07-23 |

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

2 rows in set (0.00 sec)

也可以通过简单的计算，根据现在的月份，来确定到某一指定的月份。

MONTH函数可以返回1～12. 而MOD(something,12)返回0-11.

现在当前是六月，我们分别用两种方法取出生日在12月和3月的小朋友。


mysql> select name,birth from student

    -> where month(birth)= MONTH(DATE_ADD(CURDATE(),INTERVAL 6 MONTH));

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

| name      | birth      |

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

| HANMEIMEI | 1996-12-12 |

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

1 row in set (0.00 sec)

例二：


mysql> select name,birth from student

    -> where month(birth)= MOD(MONTH(CURDATE()),12)-3;

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

| name     | birth      |

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

| WANGMING | 2012-03-24 |

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

1 row in set (0.00 sec)

模式匹配

`LIKE`

LIKE 的几个例子：

查询以W开头的学生。


mysql>  select * from student where name like 'w%';

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

| name     | parents | sex  | birth      |

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

| WANGMING | JANE    | M    | 2012-03-24 |

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

1 row in set (0.00 sec)

查询名字末尾是I的学生。


mysql>  select * from student where name like '%I';

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

| name      | parents | sex  | birth      |

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

| LILEI     |  JAY    | M    | 1998-07-23 |

| HANMEIMEI | NULL    | M    | 1996-12-12 |

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

2 rows in set (0.00 sec)

查询名字中含有W的学生。


mysql>  select * from student where name like '%W%';

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

| name      | parents | sex  | birth      |

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

| WANGMING  | JANE    | M    | 2012-03-24 |

| JIANGWANG | JIANGLI | F    | 2010-05-23 |

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

2 rows in set (0.00 sec)

查询名字有五个字母的学生。用五个下划线。


mysql>  select * from student where name like '_____';

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

| name  | parents | sex  | birth      |

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

| LILEI |  JAY    | M    | 1998-07-23 |

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

1 row in set (0.00 sec)

正则表达式

MySQL允许使用REGEXP ，RLIKE 运算符在SQL中匹配模式，否定形式为NOT REGEXP ，NOT RLIKE。

正则表达式中一些常用通配符和对应操作。

通配符	行为
^	匹配所搜索字符串的开头位置
$	匹配所搜索字符串的末尾位置
.	匹配任何单个字符
[…]	匹配方括号中的任何字符
^...	匹配非方括号中的任何字符
p1Ip2	匹配任何模式P1或P2
*	匹配前面字符零次或多次
+	匹配前面字符一次或多次
{n}	匹配前面字符的n个实例
{m,n}	匹配从前面字符的m到n个实例

可以用破折号表示某一范围，例如REGEXP '[A-Z]'匹配任何字母，REGEXP '[0-9]'匹配任何数字。

例如，含有零个或多个W的字符。


mysql>  select * from student where name REGEXP 'W*';

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

| name      | parents | sex  | birth      |

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

| LILEI     |  JAY    | M    | 1998-07-23 |

| HANMEIMEI | NULL    | M    | 1996-12-12 |

| ZHANGSAN  | Jone    | F    | 1997-07-23 |

| WANGMING  | JANE    | M    | 2012-03-24 |

         | NULL    | NULL | NULL       |

| JIANGWANG | JIANGLI | F    | 2010-05-23 |

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

6 rows in set (0.00 sec)

含有一个或多个w的字符。


mysql>  select * from student where name REGEXP 'W+';

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

| name      | parents | sex  | birth      |

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

| WANGMING  | JANE    | M    | 2012-03-24 |

| JIANGWANG | JIANGLI | F    | 2010-05-23 |

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

2 rows in set (0.00 sec)

MySQL不区分大小写，如果查询结果要区分大小写，可以添加BINARY关键字。

例：查询姓名以W开头的数据。


mysql>  select * from student where name RLIKE '^w';

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

| name     | parents | sex  | birth      |

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

| WANGMING | JANE    | M    | 2012-03-24 |

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

1 row in set (0.00 sec)



mysql>  select * from student where name RLIKE BINARY '^w';

Empty set (0.05 sec)

查询名字中含有W的数据。


mysql>  select * from student where name regexp 'w';

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

| name      | parents | sex  | birth      |

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

| WANGMING  | JANE    | M    | 2012-03-24 |

| JIANGWANG | JIANGLI | F    | 2010-05-23 |

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

查询名字是5个字母的数据。


mysql>  select * from student where name regexp '^.....$';

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

| name  | parents | sex  | birth      |

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

| LILEI |  JAY    | M    | 1998-07-23 |

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

也可以用：


mysql>  select * from student where name regexp '^.{5}$';

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

| name  | parents | sex  | birth      |

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

| LILEI |  JAY    | M    | 1998-07-23 |

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

1 row in set (0.00 sec)

计数

查询表内有多少行数据。


mysql> select count(*) from student;

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

| count(*) |

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

|        6 |

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

1 row in set (0.00 sec)

查询表内有多少男生，多少女生。


mysql> select sex,count(*) from student group by sex;

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

| sex  | count(*) |

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

| F    |        4 |

| M    |        4 |

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

2 rows in set (0.00 sec)

查询有多少叫LUCY或LILEI的同学。


mysql> select name,sex,count(*) from student

    -> where name='LUCY'or name='LILEI'

    -> group by name,sex;

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

| name  | sex  | count(*) |

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

| LILEI | M    |        2 |

| LUCY  | F    |        2 |

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

2 rows in set (0.00 sec)

如果没有group by语句，会将所有结果视为一个组来处理，但是每个命名列是不明确的。


mysql> select name,sex,count(*) from studentsele

    -> where name='LUCY'or name='LILEI';

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

| name  | sex  | count(*) |

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

| LILEI | M    |        4 |

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

1 row in set (0.00 sec)

查询不重复的信息，可以使用关键字DISTINCT。


mysql> select distinct lesson from student;

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

| lesson   |

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

| Math     |

| Language |

| Computer |

| History  |

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

4 rows in set (0.00 sec)

多个表的应用

新建一个teacher表


mysql> select * from teacher;

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

| name       | sex  | subject  | date       |

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

| ChengChung | F    | Math     | 1990-04-23 |

| ZhangHan   | F    | Language | 1989-09-21 |

| ZiJin      | M    | History  | 1991-03-12 |

| LILEI      | F    | Computer | 1976-07-08 |

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

student表为：


mysql> select * from student;

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

| name       | parents    | sex  | birth      | lesson   |

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

| LiLei      | JiangJiang | F    | 2008-09-14 | Math     |

| JiangJiang | Jeon       | F    | 1998-02-14 | Math     |

| HanMei     | JAY        | M    | 2010-07-23 | Math     |

| WangLiLi   | WangJing   | M    | 2009-06-19 | Language |

| Lucy       | JingJE     | M    | 2009-05-28 | Computer |

| Lucy       | Jingee     | M    | 2011-04-28 | History  |

| YangGuang  | YangYun    | F    | 1996-03-24 | History  |

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

7 rows in set (0.00 sec)

有时候为了得到完整的结果，我们需要从两个或多个表中获取结果。

使用表别名

Alias关键字，使用一个列名别名。可以方便程序阅读和书写。


mysql> select s.name, s.lesson, t.name

    -> from student as s, teacher as t

    -> where s.lesson='Math' and t.subject ='Math'

    -> order by s.name;

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

| name       | lesson | name       |

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

| HanMei     | Math   | ChengChung |

| JiangJiang | Math   | ChengChung |

| LiLei      | Math   | ChengChung |

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

3 rows in set (0.02 sec)

inner join

数据库中的表可以通过inner join建立内联接，将彼此联系起来。例，使用on子句根据课程来匹配两个表。


mysql> select s.name as student,s.lesson,t.name as teacher

    -> from student as s inner join teacher as t

    -> on s.lesson = t.subject;

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

| student    | lesson   | teacher    |

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

| LiLei      | Math     | ChengChung |

| JiangJiang | Math     | ChengChung |

| HanMei     | Math     | ChengChung |

| WangLiLi   | Language | ZhangHan   |

| Lucy       | Computer | LILEI      |

| Lucy       | History  | ZiJin      |

| YangGuang  | History  | ZiJin      |

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

7 rows in set (0.00 sec)

若要将表中的记录与同一表中的其他记录进行比较，inner join也可以对自身进行内连接，比如查询一下学生表中选择同一课程的男生和女生。


mysql> select s.name,s.lesson,s.sex,t.name,t.sex

    -> from student as s inner join student as t

    -> on s.lesson = t.lesson and s.sex = 'M' and t.sex = 'F';

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

| name   | lesson  | sex  | name       | sex  |

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

| HanMei | Math    | M    | LiLei      | F    |

| HanMei | Math    | M    | JiangJiang | F    |

| Lucy   | History | M    | YangGuang  | F    |

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

3 rows in set (0.00 sec)

[1]. 参考: https://dev.mysql.com/doc/refman/5.7/en/entering-queries.html