MySQL基本select查询及数据类型

基本select查询

知识要点
设置系统变量@@sql_mode
基本SELECT语句
在SELECT语句中的算术表达式和null值
列别名
使用串联操作符和distinct关键字
desc命令

增删改查
对数据库常规的四个操作
插入：insert
删除：delete
修改：updata
查询：select

select查询简介
查询对象有四个
1.全表查询
select * from PLAYERS; #全表查询PLAYERS（*代表所有的列）
2.查询某些行
select * from PLAYERS where PLAYERNO between 6 and 8; #查询PLAYERS表中所有的列球员编号是678的（也就是查询球员编号6，7，8的行）
3.查询某些列
select PLAYERNO,NAME,SEX from PLAYERS; # 查询PLAYERNO,NAME,SEX三列
4.查询某些行里的某些列
select PLAYERNO,NAME,SEX from PLAYERS where PLAYERNO between 6 and 8; #查询球员编号6.7.8行中PLAYERNO,NAME,SEX三列
补充：查询库里面有哪些表
show tables;

select语句语法
select 列名 from 表名 where 条件

select后面的列的一些常规用法
1.* #访问所有的列
2.某些指定的列
select PLAYERNO,NAME,SEX from PLAYERS;
3.算术运算（在列上进行加减乘除）
select playerno,amount,（12amount） +100 from penalties; #列出 playerno,amount并在amount列上乘12加100
4.列别名
select PLAYERNO,AMOUNT6.5 “ren min bi” from PENALTIES; #给AMOUNT*6.5起别名叫ren min bi
5.字符串连接
1）利用concat函数连接
select NAME ,concat(TOWN, ’ ‘ ,STREET, ‘ ‘ ,HOUSENO) “player home address” from PLAYERS;
#将town.street.houseno连接在一起显示，并起别名为player home address 在这里插入图片描述注：：concat使用限制：当列上有空值时不能使用
使用concat_ws可以添加分隔符，也可以解决concat的使用限制

举例1：用逗号作为分隔符 在这里插入图片描述举例2：在这里插入图片描述2）利用管道符连接，但首先需要修改参数
往sql_mode加PIPES_AS_CONCAT @@session表示当前回话在这里插入图片描述||’ ’||表示中间留有空格在这里插入图片描述使用help去学习和使用select函数
mysql> help functions;
You asked for help about help category: “Functions”
For more information, type 'help ', where is one of the following
categories:
Bit Functions
Comparison operators
Control flow functions
Date and Time Functions ##与日期时间相关的函数
Encryption Functions
Information Functions ##与系统相关的函数
Logical operators ##逻辑运算符相关的函数
Miscellaneous Functions
Numeric Functions ##与数字运算相关的函数
String Functions ##与字符串相关的函数

mysql> help String Functions;
…
mysql> help CONCAT;
…
帮助会列出的函数的使用语法，函数说明，以及函数示例

mysql常量（数据类型）
mysql常量有以下几种分类
1.整数（int）
create table t2 (id int UNSIGNED)；创建t2表和id列，id列是正整数，不能存负数

2.小数（dec）
create table t3 (d1 dec(10,2)); 创建t3表d1列，d1列可以创建小数，最长为10位。小数占2位在这里插入图片描述3.浮点数（float）
create table t5 (f1 float)； 创建t5表和f1列，f1列可以插入浮点数。
浮点数示例：5e2表示510^2 5e-2表示510^-2

4.字符串（varchar）
1)crerate table t6(v1 varchar(20)); 创建t6表和v1列，v1列的可以插入的数据类型是字符串，最大长度是20
2)插入什么字符要注意添加单引号在这里插入图片描述3)注：要插入字符串abc’abc时的命令语句是insert into t6 values (‘abc’’abc’);在这里插入图片描述4)create table t7 (d1 varchar(20) character set utf8); 创建t7表和d1列指定字符集为utf8在这里插入图片描述补充：gbk字符集也可以支持中文

5.日期
年 月 日
表示日期有两种写法
1）901101
2）19901101
都表示1990年11月01日
create table t12 (d1 date); 创建t12表和列，数据类型是date
insert into t12 values(19901101)在这里插入图片描述补充：时间函数
insert into t12 values(STR_TO-DATE(‘01,10,1999’,’%d,%m,%Y)); 01 10 1999 分别对应d m Y在这里插入图片描述6.布尔（boolean）真假的问题，0为真
create table t9 (b1 BOOLEAN);在这里插入图片描述7.16进制
0X101 表示16进制

8.位（bit）（二进制）
create table t11(b1 bit(5)); 创建t11表和b1列,数据类型是位，且最大为5位
insert into t11 values(b’111’); 插入111位
使用select bin(b1) from t111;表示以二进制的形式展示出来在这里插入图片描述9.time数据类型（时分秒）
1）create table t13 (t1 time); 创建的t1列是time数据类型的
insert into t13 values(191001); 插入19:10:01时分秒

2）create table t14(t1 time(3));表示插入的秒小数点后边可以加三位数

3）也可以使用STR_TO-DATE函数，但使用前最好设置一个参数
set @@session.sql_mode=’ansi_quotas’;
设置完参数就可以灵活设置了
insert into t14 values(str_to_date(‘19:10:01’,’%H:%i:%s’)) 分别对应时分秒

10.datetime数据类型（年月日时分秒）
create table t15(d1 datetime(3)); 创建的d1列是datetime数据类型，秒后边有三位小数
insert into t15 values(‘1990-10-01,19:01:01’);在这里插入图片描述也可以使用STR_TO-DATE函数,需一一对应在这里插入图片描述补充：时间日期显示
可以使用date_format函数以自定义格式显示

时区

查看linux操作系统的时区
cat /etc/sysconfig/clock
cat /usr/share/zoneinfo/Asia/Shanghai CST-8表示东八区
2.mysql时区

查看时区：
1）系统时区
show variables like ‘system%’；
2）全局时区
select @@global.time_zone;
3）会话时区
select @@session.time_zone;
会话时区默认等于全局时区

设置时区
1）设置全局时区（对数据库有影响）
set @@global.time_zone=’+8:00’;
或者使用
set @@global.time_zone=’Asia/shanghai’;
但在使用这个命令设置全局时区时 需要先运行一个脚本，脚本是mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p123 mysql 脚本会把操作系统的时区（/usr/share/zoneinfo）加载到mysql数据库中
可以使用select * from mysql.time_zone_name;查询脚本是否成功，有数据说明成功！！
使用Asia/shanghai重启MySQL会失效，把default_time_zone=Asia/shanghai加到配置文件中就会永久生效
补充：show variables like ‘%time_zone%’; ##查看变量像time_zone类型的

3.mysql时区正确使用规则
对mysql我们要正确设置time_zone
当我们新建表的时候，数据类型是timestamp时，我们会用到时区
mysql> create table t1(d1 timestamp);
当我们往d1列中插入日期数据时，不管那个时区的日期，都会转成0时区保存在d1列中，当我们查取d1列的信息，d1列就会把0时区再转为当初存储的时区日期
但要注意必须正确设置自己的会话时区，也就是set @@session.time_zon要设置正确，符合自己的时区

补充：会话时区默认等于全局时区，但是会话时区可以修改例如set @@session.time_zone=’+10:00’;修改以后不等于全局(global)时区

4.mysql正确使用举例
会话1
mysql> set @@session.time_zone=’+8:00’; #设置当前会话为东八区
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.time_zone; #查看设置是否成功
±--------------------+
| @@session.time_zone |
±--------------------+
| +08:00 |
±--------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(‘2019-03-03 22:00:00’); #插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1; #查看刚才插入的数据
±--------------------+
| d1 |
±--------------------+
| 2019-03-03 22:00:00 |
±--------------------+
1 row in set (0.00 sec)

然后在另一个会话设置时区，插入时间数据
会话2
mysql> set @@session.time_zone=’+10:00’; #设置会话2是东十区
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.time_zone;
±--------------------+
| @@session.time_zone |
±--------------------+
| +10:00 |
±--------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(‘2019-03-03 22:00:00’);
ERROR 1046 (3D000): No database selected
mysql> use yang
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into t1 values(‘2019-03-03 22:00:00’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1; #查看到会话2在东八区插入的时间数据为2019-03-04 00:00:00 因为东八区比东十区快两个小时
±--------------------+
| d1 |
±--------------------+
| 2019-03-04 00:00:00 |
| 2019-03-03 22:00:00 |
±--------------------+
2 rows in set (0.00 sec)