目录
数据类型
1、数值型
2、日期/时间型
3、文本型
数据库
1、创建数据库
2、删除数据库
数据表
1、创建数据表 create
2、删除数据表 drop
3、修改数据表
4、修改表名
5、drop、delete与truncate的区别
查看数据库、数据表
1、使用/选择数据库 use
2、查看数据库 show
3、查看数据表 show
4、查看表结构 desc/describe/show columns from
5、查看数据库下数据表的信息
6、查看创建数据表的语句
表数据操作
1、插入数据 insert
2、更新数据 update
3、Group by、having
4、Join
5、Union操作符
6、去重操作 distinct/group by
7、聚集函数
8、查询中使用的各种运算符
9、为列名/表名指定别名
10、MySql函数
11、Select语句的一般格式
12、Mysql中字符串使用单引号
正文
1、登录:-h localhost -u root -p root
2、mysql命令行有两种方式结尾:a.分号(;) b. \g
注意: \g 与前面的命令中间有空格,否则会报错,如下图。
参考网址:https://www.cnblogs.com/stono/p/8227394.html
数据类型
1、MySql中为字段选择合适的数据类型是数据库优化的方式之一。
MySql支持多种数据类型,大致分为三类:数值型、日期/时间型和字符串(字符)型。
Mysql支持所有标准SQL数值数据类型。
数值数据类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),近似数值数据类型(FLOAT、REAL、DOUBLE PRECISION)
关键字INT: Integer, 关键字DEC:Decimal
BIT数据类型保存“ 位字段值 ”,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TinyINT、MediumINT和BigINT。
数值型:NUMBER型
这些整数类型拥有额外的选项 UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED 属性,那么范围将从 0 开始,而不是某个负数。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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的值 | 小数值 |
Decimal为SQL Server、Mysql等数据库的一种数据类型,不属于浮点数类型,可以在定义时划定整数部分以及小数部分的位数。
使用精确小数类型不仅能够保证数据计算更为精确,还可以节省存储空间,例如百分比使用decimal( 4, 2) 即可。存储数据范围是:-10^38 ~ 10^38-1 的固定精度和小数位的数字。一个decimal类型的数据占用了2~17个字节。
与double相比,decimal类型具有更高的精度和更小的范围,它适合于财务和货币计算。
Decimal数据类型详解,参考网址1:https://blog.csdn.net/weixin_38125277/article/details/80837321
网址2: https://blog.csdn.net/qq_34306360/article/details/79721619
日期/时间型:DATE型
类型 | 大小 (字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | '1000-01-01' 到 '9999-12-31' | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59' 到 '838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 2位或4位格式的年。4位格式:1901到2155。 2位格式:70到69,表示从1970年到2069年。 | 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:01' UTC 到 '2038-01-09 03:14:07' UTC 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 TIMESTAMP值使用Unix纪元('1970-01-01 00:00:00' UTC)至今的描述来存储。 | YYYY-MM-DD HH:MM:SS | 日期和时间值的组合,时间戳 |
说明:
虽然DATETIME和TIMESTAMP返回相同的格式,但它们的工作方式很不同。在INSERT 或 Update查询中,TIMESTAMP自动把自身设置为当前的日期和时间。TimeStamp也接受不同的格式,比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
文本型:
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串; 保存固定长度的字符串,在括号中指定可保存最大字符串的长度。 |
VARCHAR | 0-65535 字节 | 变长字符串; 保存可变长度的字符串,在括号中指定可保存最大字符串的长度。 |
TINYTEXT | 0-255字节 | 短文本字符串 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
说明:
a. BINARY 和 VARBINARY 是二进制字符串,即 它们包含的是字节字符串而不是字符字符串。
b. char 和 varchar:
(1) char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此。
(2) char(n) 固定长度,char(4) 不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数 +1 个字节(n<=255)或2个字节(n>255),所以 varchar(4)存
入3个字符将占用4个字节。
(3) char类型的字符串检索速度要比varchar类型快。
c. varchar 和 text
(1)varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节(n>255),text 是实际字符数 +2 个字节。
(2)text 类型不能有默认值。
(3)varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下,text 的索引似乎不起作用。
d. 二进制数据(_Blob) 和 text
(1)存储方式不同,_blob 以二进制方式存储,不区分大小写。 _text 以文本方式存储,英文存储区分大小写。
(2) _blob 存储的数据只能整体读出。
(3) _text 可以指定字符集, _blob 不能指定字符集。
参考网址:http://www.runoob.com/mysql/mysql-data-types.html
数据库
1、创建数据库:create database 数据库名; 例: create database info;
说明:创建数据库前先判断是否存在该数据库的语句:create database if not exists info default charset utf8 collate utf8_general_ci;
a. 如果数据库不存在则创建,存在则不创建。 b. 创建数据库Info,并设定编码集为utf8.
2、删除数据库:drop database 数据库名; 例: drop database info;
说明:在mysql中执行删除数据库命令后,会弹出一个提示框。
实例: 从下图中可以看出,删除数据库时中间" drop test "会报错。
数据表
1、创建基本表: create table 表名(<表名> < 数据类型>); 查看创建表的语句: show create table 表名; show create table course;
说明:a. 在创建数据表时,常常指定数据引擎以及字符集类型,如:Engine=InnoDB DEFAULT CHARSET=utf8;
b. 讲解很详细,参考网址:https://www.cnblogs.com/BeginMan/p/3249472.html
创建数据表的语句和查看创建表时的语句:
2、删除基本表:drop table 表名
说明:a. truncate table 表名;
b. delete from 表名 [where 子句];
注:如果省略where子句,表示删除表中全部元组,但表的定义仍在字典中。也就是说,delete语句删除的是表中的数据,而不是关于表的定义。
c. 删除数据表三种方式的区别:
(1) drop table 表名; 删除表全部数据和表结构,立刻释放磁盘空间,InnoDB和MyISAM都是如此。
(2) truncate table 表名;删除表全部数据,保留表结构,立刻释放磁盘空间,InnoDB和MyISAM都是如此。
(3) delete from 表名 ;删除表全部数据,保留表结构,MyISAM会立刻释放磁盘空间,InnoDB不会释放磁盘空间。
(4)delete from 表名 where xxx;带条件地删除表数据,保留表结构,MyISAM和InnoDB都不会释放磁盘空间。
(5)delete方式,如果想释放磁盘空间,使用 “ optimize table 表名;”,MyISAM和InnoDB都会释放磁盘空间。
delete from student where name= "张三"; optimize table student;
(6)delete from 方式虽然未释放磁盘空间,但是以后添加数据仍然可以使用这部分空间。
实例:
truncate 表名; 表结构仍然存在。
delete from 表名 where clause ;
drop table 表名; 再次查看表时会报错,提示表不存在
3、修改基本表:使用Alter table语句修改基本表
语法:
Alter table 表名
新 增 字 段: Add column <新列名> <数据类型> [ 完整性约束]
删 除 字 段: Drop column <列名> 注释: column 是可选的,可带、可不带
修改字段类型: Alter column <列名> <数据类型>
说明:
a. 新增字段 add 和 删除字段 drop 语句中的column 是可选的,可带,可不带。
b. 删除字段 drop
c. 修改字段 alter, modify, change
Modify:只修改字段类型,不能修改字段名称.
语法: alter table 表名 modify 字段名称 字段类型 [约束]; alter table student modify dizhi text;
Change : 可选择是否修改字段类型和字段名称.
change关键字后,要修改的字段名,新字段名及新数据类型,这三项缺一不可。
由于三项必须填写,如果不想修改字段类型和字段名称,那么在新字段名称和新字段类型的位置仍写原字段名称或字段类型,这种方法可行。
语法: alter table 表名 change 字段名称 新字段名称 新字段类型; alter table student change i j bigint;
Alter :只能添加、删除默认值,不能修改字段类型,不能修改字段名称。添加默认值时一定要加上关键字set。
修改字段默认值 : Alter table student alter i set default 1000;
删除字段默认值: Alter table student alter i drop default;
d. 指定新增字段的位置:first 关键字(设定为第一列)和after关键字(设定位于某个字段之后),这两个关键字只作用于ADD子句。
新增字段:
删除字段:
添加默认值:使用alter 命令
4、修改表名
Alter table 原表名 rename to 新表名;
5、drop、delete和truncate 三种方式详细区别,参考网址:
drop用于删除数据库、数据表等,delete和truncate用于删除表数据
网址1: http://www.runoob.com/mysql/mysql-drop-tables.html
网址2: http://www.runoob.com/mysql/mysql-delete-query.html
网址3: https://blog.csdn.net/fzy198926/article/details/78190633
查看数据库、数据表
1、使用数据库、选择数据库:use 数据库名;
注意:创建和删除数据库、数据表时,中间都要带上database、table,而选择数据库时没有带database.
2、查看有哪些数据库: show databases; 注:结尾要带s
3、当前数据库下有哪些数据表:show tables; 注:结尾有s
4、查看表结构:desc 表名;或 describe 表名; 简写/全写两种方式均可 或 show columns from 表名;
5、查看某个数据库下所有表的类型、状态: show table status from 数据库名 [ like 'pattern' ]; like ' pattern ' 表示查询哪些表的具体表名。
a. show table status from try; 查询 try 数据库下所有表的信息。
网址: https://www.cnblogs.com/lxwphp/p/8109261.html 讲解了各个字段的含义。
b. 查看数据库 try 下 student 表中的信息。
6、查看创建数据表的语句: show create table 表名; show create table course;
表数据操作
1、向表中插入数据
a. insert into 表名 [(field1, field2 …… field n)]
values (value1, value2 …… value n);
说明: 如果INTO子句没有指明(或列出)任何列名,则新插入的元组必须在每个属性列上均有值。
如果指出了新增加的元组在哪些属性列上要赋值,属性的顺序可以与CREATE TABLE中的顺序不一样。
b. 另一种方法: 也称为复制另一张表数据
insert into ……select 子句
2、更新表数据:
update 表名 set field1=values1, field2 = values2 [where clause];
insert into 与 update的区别:insert into 是插入一条新记录,update是在原来表的数据上进行更新、修改。
3、Group by子句
Group by子句将查询结果按某一列或多列的值分组,值相等的为一组。
对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组,聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组,即每一组都有一个函数值。
Having短语:如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则使用having短语指定筛选条件。
说明:
a. where子句与having短语的区别:在于作用对象不同。
where子句:作用于基本表或视图,从中选择满足条件的元组。
having短语:作用于组,从中选择满足条件的组。
b. (新的知识点)with rollup:可以实现在分组统计数据的基础上再进行相同的统计(sum, avg, count ……)
c. (新的知识点)coalesce( )函数: 返回参数(列名)中第一个非NULL的字段值,注意不是为空。
该函数的详细介绍网址:https://blog.csdn.net/hackage4619/article/details/58586449
4、Join的使用
在真正的应用中经常需要从多个数据表中读取数据,因此在mysql中使用Join来联合多表查询。
JOIN按照功能大致分为如下四类:
a. Inner join (内连接,或等值连接,可以省略Inner,效果一样):获取两个表中字段匹配关系的记录。
b. Left join(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
c. Right join(右连接):获取右表所有记录,即使左表没有对应匹配的记录。
d. Full join (全连接):只要左表和右表其中一个表中存在匹配,则返回行。full join结合了left join 和 right join的结果。
参考网址1: http://www.runoob.com/mysql/mysql-join.html
参考网址2:https://www.runoob.com/sql/sql-join-full.html
5、Union操作符
功能:用于连接两个以上的select语句的结果,使其组合到一个结果集中。
语法:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
说明: a. (重要)可以只检索其中几个字段,并不是所有字段。
b. Distinct是默认选项,即默认情况下Union操作符删除了重复的数据。 All, 返回所有结果集,包含重复数据。
6、去重操作
a. distinct 关键字:select distinct age from person;
b. group by : select age from person group by age;
7、聚集函数(Aggregate functions)
count( [ distinct | all ] * ) 统计元组个数
count( [ distinct | all ] <列名> ) 统计一列中值的个数
sum( [ distinct | all ] <列名>) 计算一列值的总和(此列必须是数值型)
avg( [ distinct | all ] <列名>) 计算一列值的平均值(此列必须是数值型)
max( [ distinct | all ] <列名>) 求一列值中的最大值
min( [ distinct | all ] <列名>) 求一列值中的最小值
说明:
在聚集函数遇到空值时,除count( * ) 外,都跳过空值而只处理非空值。注意, where子句中是不能使用聚集函数作为条件表达式的。
8、常用的查询条件
比较: =,>,<,>=,<=,!=,<>,!>,!< (不等于两种形式: != 和 <>)
确定范围: between and, not between and
确定集合: In, Not in
字符匹配:Like , Not Like
通配符: a. %(百分号):任意长度的字符串。 b. _(下划线):任意单个字符。
注:如果匹配串中不含通配符,like效果类似于=(等于);not like类似于 != 或 <> (不等于)
空值: Is Null, Is not null
说明: a. 处理NULL值时不能使用 = NULL 或 !=NULL,需要使用IS NULL和 IS NOT NULL运算符。
b. 不能比较NULL 和 0,它们不是等价的。
多重条件(逻辑运算):And, Or, Not
9、为表名或列名指定别名(Alias):select name as a from student as b;
10、MySql函数
数值处理函数:
弧度制:用弧长与半径之比度量对应圆心角角度的方式,叫做弧度制,用符号rad表示,读作弧度。由于圆弧长短与圆半径之比,不因为圆的大小而改变,所以弧度数是一个与圆的半径无关的量。角度以弧度给出时,通常不写弧度单位。弧度制的精髓就在于同意了度量弧与角的单位,从而大大简化了有关公式及运算,尤其在高等数学中,其优点格外明显。
函数名 | 功能描述 | 实例 |
ABS(x) | 返回x的绝对值 | 返回-1的绝对值: Select abs(-1) -- 返回1 |
Radians(x) | 将角度转换为弧度。 | 180°转换为弧度:select radians(180) -- 3.1415926 |
Cos(x) | 求余弦值(参数x是弧度,而不是度数) | Select cos(2); |
Sin(x) | 求正弦值(参数x是弧度,而不是度数) | Select sin(radians(30)); |
Tan(x) | 求正切值(参数x是弧度,而不是度数) | Select tan(1.75); -- 5.520379922 |
PI() | 返回圆周率3.141593 | Select PI() -- 3.141593 |
Exp(x) | 返回e的x次方 | 计算e的三次方:select exp(3) |
Sqrt(x) | 返回x的平方根 | 25的平方根: Select sqrt(25); |
Ceil(x) | 返回大于或等于x的最小整数 | select ceil(1.5) -- 返回2
|
Floor(x) | 返回小于或等于x的最大整数 | select floor(1.5) -- 返回1
|
Greatest(expr1, expr2, expr3……) | 返回列表中的最大值。 | select greatest(2, 134, 343, 8, 45) -- 返回343 Select greatest(“Google”,”Runoob”,”Apple”) -- 返回Runoob |
Least(expr1, expr2, expr3……) | 返回列表中的最小值。
| select greatest(2, 134, 343, 8, 45) -- 返回2 Select greatest(“Google”,”Runoob”,”Apple”) -- 返回Apple |
字符串函数:len(), substring(), lower(), upper(), left(), right(), Ltrim(), rtrim(), soundex()
函数名 | 功能描述 | 实例 |
Len() | 返回文本字段中值的长度 | Select len(city) from person |
Substring(s,n,length) 同mid(s,n,length) | 从字符串s的n位置开始,截取长度为length的子字符串 |
|
Lower(s), 同 lcase(s) | 将字符串s全部转换为小写字母 | Select lower(“RUNOOB”) -- runoob |
Upper(s), 同 ucase(s) | 将字符串s全部转换为大写字母 |
|
Left(s,n) | 返回字符串s的前n个字符 | Select left(“apple”,2) -- ap |
Right(s,n) | 返回字符串s的后n个字符 | Select right(“runoob”,2) -- ob |
Ltrim() | 去掉字符串s开始处的空格 | Select ltrim(“ runoob”) -- runoob |
Rtrim() | 去掉字符串s结尾处的空格 | Select rtrim(“runoob ”) -- runoob |
Soundex() |
|
|
Space(n) | 返回n个空格 | Select space(10) |
Strcmp(s1,s2) | 比较两个字符串 | Select strcmp(“apple”,”dog”) |
Soundex( )函数的用法,参考网址:https://blog.csdn.net/qq_29741945/article/details/70159705
soundex( )是一个将任何文本串转换为描述其语音表示的字母数字模式的函数。soundex考虑了类似的发音字符和音节,使得对字符串进行发音比较而不是字母比较。虽然,soundex不是SQL概念,但是多数DBMS都提供对soundex的支持。
日期/时间函数
函数名 | 功能描述 | 实例 |
Now() | 返回系统当前日期和时间 |
|
Date() | 从日期或日期表达式中提取日期值 | Select date(now()) 或 select date(“2018-12-22”) |
Year() | 返回年份 |
|
Month() | 返回月份 |
|
Day() | 返回日期部分 |
|
Hour() | 返回时间中的小时部分 |
|
Minute() | 返回时间中的分钟值 |
|
Dayofweek() | 返回日期对应的星期几,1是星期日,2是星期一,以此类推 |
|
Datediff(d1,d2) | 返回日期之间相隔的天数 | Select datediff(“2018-11-28”,”2018-12-22”) |
Curdate(), 同current_date() | 返回当前日期 |
|
Curtime() , 同Curren_time() | 返回当前时间 |
|
Format()函数:用于对特定字段设置显示格式。
11、Select语句的一般格式
SELECT [ ALL | DISTINCT ] <目标列表达式> [ 别名 ] [ , <目标列表达式> [ 别名 ] ] ……
FROM < 表名或视图名 > [ 别名 ] [ , < 表名或视图名 > [ 别名 ] ] ……
WHERE <条件表达式>
GROUP BY <列名1> HAVING <条件表达式>
ORDER BY <列名2> [ ASC | DESC ]
注: 几个关键字的使用顺序: where, group by , having, order by