Mysql-创建数据库和使用

• 显示已创建的数据库
• 切换使用的数据库
• 进行相关授权
• 创建和选择数据库
• • 创建数据库
  • 查询正在使用的数据库
• 创建数据表
• • 查询数据库中的表
  • 创建数据库表
  • 查询数据表的详情
• 加载数据和添加表数据
• • 加载数据
• 检索数据表中的数据
• • 语法
  • 查询所有数据
  • 删除数据库中所有数据
  • 修改记录
  • 查询指定的行
  • 查询指定的列
  • 对数据进行字段排序
  • 数据表中时间计算
  • NULL处理
  • 模糊匹配
  • 计数操作
  • 使用多个表

在前面，我们说到了连接mysql,连接上mysql进行相关语句就可以进行操作数据库。

显示已创建的数据库

#查询mysql已创建的数据库
mysql> show databases;
           

结果：

上面是查询的数据库列表，没有标记的mysql自带的数据库，我们来看看这几个数据库的作用：(语句显示的数据库列表在您的机器上可能不同;如果您没有SHOW DATABASES特权，SHOW DATABASES不会显示您没有特权的数据库)

• mysql: mysql数据库描述了用户访问权限
• test: test数据库通常可以作为工作空间供用户试用
• information_schema:保存了MySQL服务器所有数据库的信息
• sys: sys库里面的表、视图、函数、存储过程可以使我们更方便、快捷的了解到MySQL的一些信息
• performance_schema：用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况

切换使用的数据库

#切换使用的数据库
mysql>use mysql;
           

结果：

USE与QUIT一样，不需要分号，也可以输入分号进行区分（推荐）。USE语句也有另一种特殊之处：必须在一行上给出。

进行相关授权

其中your_mysql_name是MySQL用户名，而your_client_host是连接到服务器的主机

创建和选择数据库

创建数据库

#使用create database 数据库名
#创建名为test_stay的数据库
mysql>create database test_stay;
           

结果：

如果创建的时候，报错：ERROR 1044 (42000): Access denied for user ‘tony’@‘localhost’ to database ‘test_stay’，说明你当前登录的用户没有创建的权限。

我们在连接mysql的时候，可以直接指定相关的数据库，就不用使用use指令去切换数据库了

C\:>mysql -h localhost -u root -p test_stay
           

输入密码之后，直接进入test_stay数据库。

提示：刚显示的命令中的test_stay不是登录的密码.如果要在-p选项后在命令行上提供密码,则必须在中间没有空格（例如，以-ppassword而不是-p password）.但是，不建议将密码放在命令行中,因为这样做会使密码容易被登录到计算机上的其他用户监听.

查询正在使用的数据库

#使用select database()查询正在使用的数据库
mysql>select database();
           

结果：

创建数据表

查询数据库中的表

#查询当前数据库中的表
mysql>show tables;
           

如果返回Empty set 证明当前数据库中没有创建表

创建数据库表

通过create table 进行创建数据库

mysql> create table t_test(
    -> username varchar(12),
    -> password varchar(8),
    -> age int(3),
    -> birthday datetime);
           

证明创建成功，就可以使用show tables；查询数据库中的数据表

查询数据表的详情

使用describe或者desc查询表的详情

查询的结果：

使用desc的效果将是一样的

加载数据和添加表数据

加载数据

我们这里使用load data加载本地的数据，将本地数据插入到数据库指定的数据库中。

您可以创建一个文本文件，每行包含一个记录，其值由制表符分隔，并按在CREATE TABLE语句中列出的顺序给出。 对于缺失值可以使用NULL值. 要在文本文件中表示这些，请使用\ N.

我们在D盘中创建一个txt文件，内容如下：

我们如下命令记性将本地文本数据插入到数据库中：

插入结果：

提示有五条插入成功了。

如果在Windows上使用\ r \ n作为行终止符的编辑器创建了文件，则应改用以下语句：

您可以根据需要在LOAD DATA语句中显式指定列值分隔符和行尾标记,但是默认值为制表符和换行符。这些足以使该语句正确读取文件d:/my_test_stay.txt

如果该语句失败，则可能是默认情况下您的MySQL安装未启用本地文件功能。

我们也可以使用insert进行插入数据

在load data 和insert中对null值处理不一样，load data使用\N,insert直接使用null。

检索数据表中的数据

语法

检索数据表中的数据，需要使用select语句。

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
           

• what_to_select:指示要查看的内容。 这可以是列的列名，也可以是*表示“所有列”
• which_table：指示要从中检索数据的表
• conditions_to_satisfy: WHERE子句是可选的。 如果存在，conditions_to_satisfy指定行必须满足的一个或多个条件才有资格进行检索。

查询所有数据

删除数据库中所有数据

上面是先删除数据表中的数据，在进行查询

修改记录

修改表中的记录，我们可以使用update进行修改表中的指定的数据或者多行数据

由上面的操作可以看出，birthday的值已经修改。

UPDATE仅更改有问题的记录，不需要重新加载表。

查询指定的行

我们知道select指定后面可以接where语句，进行相关条件的筛选。下面是几个例子

#根据用户名查询t_test
mysql>select * from t_test where username='tony';
           

我们可以and或者or关联多个条件

#根据用户名和密码查询t_test
mysql>select * from t_test where username='tony' and password='123456';
#查询用户名为‘tony’或者密码为‘123456’的记录
mysql>select * from t_test where username='tony' or password='123456';
           

查询指定的列

如果不想看到表中的整个行，只需用逗号分隔感兴趣的列即可

#查询表t_test的username列
mysql>select username from t_test;
           

查询多个列

#查询username和age列
mysql>select username,age from t_test;
           

将查询出列的数据去重，使用DISTINCT关键字

在查询列的时候，我们也可以在后面添加where子句进行数据过滤

#查询年龄大于18的username和age列
mysql>select username,age from t_test where age >18;
           

对数据进行字段排序

我们有时候需要将数据查询出来进行排序，mysql查询的时候有一个默认的排序规则，但是这个规则可以不适合我们的业务使用，因此我们可以定义根据特定字段进行排序操作

我们可以使用ORDER BY,DESC，ASC关键字进行排序,下面是几个列子

上面两个排序是不一样的，在字符类型列上，与所有其他比较操作一样，排序通常以不区分大小写的方式执行。 这意味着除了大小写相同以外，其他列均未定义顺序。 可以这样使用BINARY强制对列进行区分大小写的排序：ORDER BY BINARY col_name

默认为升序排序，我们可以使用DESC进行降序

我们可以使用ASC进行升序排序

#多个字段同时排序
mysql>select username,age from t_test order by username,age DESC;
           

DESC关键字仅适用于紧接其前age的列名； 它不影响username列的排序顺序。

数据表中时间计算

MySQL提供了一些函数，可用于对日期执行计算。

通过用户的出生年月计算出年龄，可以使用TIMESTAMPDIFF函数。 TIMESTAMPDIFF函数的参数是要表示结果的单位，以及两个日期之间的差值。

mysql>select username,birthday,CURDATE(),
      TIMESTAMPDIFF(YEAR,birthday,CURDATE()) AS realAge from t_test;
           

上面使用了CURDATE()，得到当前的时间，TIMESTAMPDIFF函数进行计算

AS关键字将相关计算的结果取一个别名

对计算的进行排序

#使用查询出的结果realAge进行排序
mysql>select username,birthday,curdate(),timestampdiff(year,birthday,curdate()) as realAge from t_test order by realAge;
           

我们可以使用is not null 进行列的非空处理，反之可以使用is null进行空值处理

MySQL提供了一些用于提取部分日期的函数，例如YEAR()，MONTH()和DAYOFMONTH()。

#获取出生年月的月份
mysql>select username,birthday,month(birthday) as monthNum from t_test;
           

#获取出生年月的年份
mysql>select username,birthday,year(birthday) as monthNum from t_test;
           

month()作为where条件

当月份为12月的时候，不能进行添加操作，不然会变为13，将会出现错误信息，可以编写查询以便无论当前月份是什么都可以使用，从而不必在特定月份使用该数字。 DATE_ADD（）使您可以将时间间隔添加到给定的日期。 如果您在CURDATE（）的值上加上一个月，则用MONTH（）提取月份部分。

可以使用mod函数达到相同的效果

如果计算使用无效日期，则计算将失败并产生警告

NULL处理

NULL表示“缺少未知值”，并且与其他值的处理方式有所不同

不能使用算术比较运算符（例如=，<或<>）测试NULL

由于任何与NULL进行算术比较的结果均为NULL，因此您无法从此类比较中获得任何有意义的结果。

在MySQL中，0或NULL表示false，其他表示true。 布尔运算的默认真值是1。

在GROUP BY中，两个NULL值被视为相等.

在执行ORDER BY时，如果执行ORDER BY … ASC，则首先显示NULL值；如果执行ORDER BY … DESC，则最后显示NULL值。

使用NULL时的一个常见错误是假定无法在定义为NOT NULL的列中插入零或空字符串，但事实并非如此。 这些实际上是值，而NULL表示“没有值”。

因此，完全有可能在NOT NULL列中插入零或空字符串，因为这些实际上不是NOT NULL.

模糊匹配

MySQL提供了标准的SQL模式匹配以及基于扩展的正则表达式的模式匹配形式，该正则表达式类似于vi，grep和sed等Unix实用程序所使用的扩展正则表达式。

SQL模式匹配使您可以使用 _ 来匹配任何单个字符，并使用 ％ 来匹配任意数量的字符（包括零个字符）。在MySQL中，默认情况下，SQL模式不区分大小写。 使用SQL模式时，请勿使用=或<>。改用LIKE或NOT LIKE比较运算符。

#查询用户名为h开头的数据
mysql>select username,birthday from t_test where username LIKE "h%";
           

#查询用户名为yu结尾的数据
mysql>select username,birthday from t_test where username LIKE "%yu";
           

#查询用户名包含‘on’的数据
mysql>select username,birthday from t_test where username LIKE "%on%";
           

#查找用户名正好包含4个字符的数据，请使用_模式字符
mysql>select username,birthday from t_test where username LIKE '____';
           

MySQL提供的另一种模式匹配使用扩展的正则表达式。 在测试此类型的模式是否匹配时，可以使用REGEXP_LIKE函数或REGEXP或RLIKE运算符，它们可以达到REGEXP_LIKE同样的效果

下表描述了扩展正则表达式的一些特征：

• . 匹配任何单个字符。
• […] 字符类与括号内的任何字符匹配。 例如，[abc]匹配a，b或c。 要命名字符范围，请使用破折号。 [a-z]匹配任何字母，而[0-9]匹配任何数字。
• * 匹配零个或多个前面事物的实例。 例如，x* 匹配任意数量的x个字符，[0-9]*匹配任意数量的数字，.*匹配任意数量的任何字符。
• 如果正则表达式模式匹配成功，则该模式匹配成功.(这与LIKE模式匹配不同，后者仅在模式匹配整个值时才成功.)
• 要固定模式，使其必须与要测试的值的开头或结尾匹配，请在模式的开头使用^或在模式的结尾使用$。

#查询用户名为h开头的数据
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'^h');
           

#查询用户名为yu结尾的数据
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'yu$');
           

#查询用户名包含‘on’的数据
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'on');
           

#查找用户名正好包含4个字符的数据
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'^....$');
           

#查找用户名正好包含4个字符的数据
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'^.{5}$');
           

计数操作

使用 COUNT(*)计算行数

#进行分组统计计数
mysql>select username,count(*) from t_test group by username;
           

使用GROUP BY对每个记录的所有记录进行分组

#针对多个列进行分组
mysql>select username,age,count(*) from t_test group by username,age;
           

计数的时候，可以进行条件查找

如果除了COUNT()值之外还为要命名的列命名，则应该存在一个GROUP BY子句来命名那些相同的列。 否则，将发生以下情况：

• 如果启用了ONLY_FULL_GROUP_BY SQL模式，则会发生错误

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by
           

如果未启用ONLY_FULL_GROUP_BY，则通过将所有行视为一个组来处理查询，但是为每个命名列选择的值是不确定的。 服务器可以从任何行中自由选择值：

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        8 |
+--------+----------+
1 row in set (0.00 sec)
           

使用多个表

我们有时候将用户记录在一张表，相关用户的地址记录在一张表，这时候，我们想拿到用户的地址信息就要操作多张表。

我们创建一个地址表

mysql>create table addree(
	username varchar(12),
	address varchar(64),
	city varchar(16));
           

多表关联查询用户的地址信息

关于此查询，有几件事要注意：

• FROM子句连接两个表，因为查询需要从两个表中提取信息。
• 在合并（合并）来自多个表的信息时，您需要指定如何将一个表中的记录与另一个表中的记录匹配。 这很容易，因为它们都有名称列。 该查询使用ON子句根据名称值匹配两个表中的记录。
• 该查询使用INNER JOIN组合表。 当且仅当两个表都满足ON子句中指定的条件时，INNER JOIN才允许其中一个表中的行出现在结果中。
• 由于名称列同时出现在两个表中，因此在引用该列时，必须明确说明要表示的表。 这是通过在表名之前添加列名来完成的。