重要概念:DBS包括DB和DBMS
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle旗下产品。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。
一、连接:
C:\Users\11405>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
二、创建数据库:
mysql> create database bd1802 default character set utf8;
Query OK, 1 row affected (0.00 sec)
三、修改命令提示符界面显示编码集:
mysql> set names GBK;
Query OK, 0 rows affected (0.00 sec)
MySQL中的数据类型及其说明:
类型 | 大小 | 范围(有符号) | 用途 |
TINYINT | 1 字节 | (-128,127) | 小整数值 |
SMALLINT | 2 字节 | (-32 768,32 767) | 大整数值 |
MEDIUMINT | 3 字节 | (-8 388 608,8 388 607) | 大整数值 |
INT或INTEGER | 4 字节 | (-2 147 483 648,2 147 483 647) | 大整数值 |
BIGINT | 8 字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | 极大整数值 |
FLOAT | 4 字节 | 单精度,不规定长度时,默认是(6,2)但整数位可以超出4位。 | 浮点型 |
DOUBLE | 8 字节 | 双精度 | 浮点型 |
DECIMAL | DECIMAL(M,D)M代表数字总共位数,D代表小数位数。规定长度后,整数位最长为M-D。 | 浮点型 | |
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 | 8字节 | 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
CHAR | 0-255字节 | char(10) | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 | |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 | |
TINYTEXT | 0-255字节 | 短文本字符串 | |
BLOB | 0-65535字节 | 二进制形式的长文本数据 | |
TEXT | 0-65535字节 | 长文本数据 | |
MEDIUMBLOB | 0-16777 215字节 | 二进制形式的中等长度文本数据 | |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 | |
LOGNGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 | |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
dual的概念:
在没有引用表的情况下,允许将dual指定为样本表名,dual纯粹是为了方便人们要求所有SELECT语句都应该有来自和可能的其他子句。MySQL可能忽略这些子句。如果没有其它参照表,MySQL就不需要dual表。
例如:select 1+2+2*3 from dual;或者select 1+2+2*3;
一、常用的字符串函数:
1、ascii(str):返回指定字符串的第一个字符的ascii码,字符串为空字符串时,返回0 ,字符串为null时,返回null
例如:select ascii('你好');返回的是第一个字符的字节码的第一个字节。
2、ord(str):如果字符串的第一个字符是单字符时,与函数ascii(str)一样。如果不是单字节。如 UTF8中,是三个字节。 返回值为: ((first byte ascii code)*256+(second byte ascii code))[*256+third byte asciicode...]
3、conv(n,from_base,to_base),将from_base进制中的数n转成to_base进制下的表示方式
mysql> select conv('f',16,2);--16进制中的字符a转成2进制来表示
+----------------+
| conv('f',16,2) |
+----------------+
| 1111 |
+----------------+
1 row in set (0.00 sec)
4、bin(n),oct(n),hex(n):将数值n转成对应的二进制,八进制,十六进制
mysql> select bin(13),oct(100),hex(128);--查看十进制数 13的二进制,100的八进制,128的十六进制
+---------+----------+----------+
| bin(13) | oct(100) | hex(128) |
+---------+----------+----------+
| 1101 | 144 | 80 |
+---------+----------+----------+
1 row in set (0.00 sec)
5、char(n...):返回多个ascii码组成的字符串。
mysql> select char(104,101,108,108,111);
+---------------------------+
| char(104,101,108,108,111) |
+---------------------------+
| hello |
+---------------------------+
1 row in set (0.00 sec)
6、concat(str1,str2,...):将多个字符串拼接成一个字符串,如果有一个参数为null,返回的就是null。
7、length(str)/octet_length(str):返回的是默认字符集下的字符串的所有字节数。
8、char_length(str)/character_length(str):这两个函数都是返回字符串的字符长度。
mysql> select length('上海自来水来自海上'),octet_length('上海自来水来自海上');
+------------------------------+------------------------------------+
| length('上海自来水来自海上') | octet_length('上海自来水来自海上') |
+------------------------------+------------------------------------+
| 18 | 18 |
+------------------------------+------------------------------------+
1 row in set (0.00 sec)
mysql> select character_length('上海自来水来自海上'),char_length('上海自来水来自海上');
+----------------------------------------+-----------------------------------+
| character_length('上海自来水来自海上') | char_length('上海自来水来自海上') |
+----------------------------------------+-----------------------------------+
| 9 | 9 |
+----------------------------------------+-----------------------------------+
1 row in set (0.04 sec)
9、locate(substr,str)/position(substr in str)/instr(str,substr);返回substr在str第一次出现的位置。如果没有,返回0。
mysql> select locate('zuo','no zuo no die'), position('bar' in 'foobarfoobarfoo'), instr('foobarfoobarfoo','baa');
+-------------------------------+--------------------------------------+--------------------------------+
| locate('zuo','no zuo no die') | position('bar' in 'foobarfoobarfoo') | instr('foobarfoobarfoo','baa') |
+-------------------------------+--------------------------------------+--------------------------------+
| 4 | 4 | 0 |
+-------------------------------+--------------------------------------+--------------------------------+
1 row in set (0.00 sec)
10、lpad(str,len,padstr)/rpad(str,len,padstr);在字符串str的左边/右边添加padstr,添加后的总长度为len。可称之为左补全/右补全函数, 右对齐/左对齐函数。
#查询emp表中的员工姓名,右对齐,总长度为20。
mysql> select lpad(ename,20,' ') from emp;
+----------------------+
| lpad(ename,20,' ') |
+----------------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| IRONMAN |
| SUPERMAN |
+----------------------+
15 rows in set (0.00 sec)
11、left(str,len)/right(str,len);返回字符串左端/右端的len个字符。
#获取'helloworld'的左端和右端的4个字符。
mysql> select left('helloworld',4), right('helloworld',4);
+----------------------+-----------------------+
| left('helloworld',4) | right('helloworld',4) |
+----------------------+-----------------------+
| hell | orld |
+----------------------+-----------------------+
1 row in set (0.00 sec)
12、substring(str,pos)/substring(str from pos);截取str的子串,从pos下标开始截取到最后。
#截取"no zuo no die",从下标4开始截取;截取"no zuo no die"的最后六个字符。
mysql> select substring('no zuo no die',4),substring('no zuo no die',-6);
+------------------------------+-------------------------------+
| substring('no zuo no die',4) | substring('no zuo no die',-6) |
+------------------------------+-------------------------------+
| zuo no die | no die |
+------------------------------+-------------------------------+
1 row in set (0.00 sec)
13、substring_index(str,delim,count);截取str,截取到第count个分隔符delim。
#截取'www.mysql.com',截取到第二个".";截取'www.mysql.com',从倒数第二个'.'开始截取。
mysql> select substring_index('www.mysql.com','.',2),substring_index('www.mysql.com','.',-2);
+----------------------------------------+-----------------------------------------+
| substring_index('www.mysql.com','.',2) | substring_index('www.mysql.com','.',-2) |
+----------------------------------------+-----------------------------------------+
| www.mysql | mysql.com |
+----------------------------------------+-----------------------------------------+
1 row in set (0.00 sec)
14、ltrim(str)/rtrim(str);删除str的左端/右端的空格。
15、trim([[both | leading | trailing] [remstr] from] str);删除str里两端的remstr。左右两端由 both|leading|trailing来控制。
#删除' bar '字符串的左右空格。删除' hello '前后的空格。
mysql> select ltrim(rtrim(' bar ')), length(ltrim(rtrim(' bar '))),trim(' hello '),trim(both ' ' from ' hello ');
+---------------------------+-----------------------------------+-----------------+-------------------------------+
| ltrim(rtrim(' bar ')) | length(ltrim(rtrim(' bar '))) | trim(' hello ') | trim(both ' ' from ' hello ') |
+---------------------------+-----------------------------------+-----------------+-------------------------------+
| bar | 3 | hello | hello |
+---------------------------+-----------------------------------+-----------------+-------------------------------+
1 row in set (0.00 sec)
16、replace(str,from_str,to_str);使用to_str替换掉str中的from_str。
#使用'#' 替换掉'hellohello'里的"ll";
mysql> select replace('hellohello','ll','#');
+--------------------------------+
| replace('hellohello','ll','#') |
+--------------------------------+
| he#ohe#o |
+--------------------------------+
1 row in set (0.00 sec)
17、insert(str,pos,len,newstr);使用newstr替换str中的一部分,这部分从pos下标开始,len个。
# 使用"mysql"替换掉"i like you"中的'you';
mysql> select insert('i like you',8,3,'mysql');
+----------------------------------+
| insert('i like you',8,3,'mysql') |
+----------------------------------+
| i like mysql |
+----------------------------------+
1 row in set (0.00 sec)
18、lcase(str)/lower(str)/ucase(str)/upper(str);将字符串中的英文字母全变成大/小写。
#将‘I like yourName 3’变成全大写 /全小写。
mysql> select lower('I like yourName 3'),upper('I like yourName 3');
+----------------------------+----------------------------+
| lower('I like yourName 3') | upper('I like yourName 3') |
+----------------------------+----------------------------+
| i like yourname 3 | I LIKE YOURNAME 3 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)
19、abs(n);取参数n的绝对值。
20、sign(n);判断参数的符号。
21、mod(m,n);返回m对n的取余的结果。同(m%n),和java中的取余效果一样;
22、floor(n); 返回小于等于n的最大整数,
23、ceiling(n) ;返回大于等于n的最小整数。
24、round(n,d);对n进行四舍五入,保留d位小数。
25、pow(x,y)/power(x,y) ;计算x的y次方
28、sqrt(n);对n进行开平方。
29、rand();返回0~1.0的一个随机小数。
30、truncate(n,d);对数值n进行截断。 d为正数时,保留小数点后d位; d为0时,保留整数位;d为负数时,保留小数点前d位。
#对12345.12进行保留1位小数,保留整数位,保留到千位。
mysql> select truncate(12345.12,1),truncate(12345.12,0),truncate(12345.12,-3);
+----------------------+----------------------+-----------------------+
| truncate(12345.12,1) | truncate(12345.12,0) | truncate(12345.12,-3) |
+----------------------+----------------------+-----------------------+
| 12345.1 | 12345 | 12000 |
+----------------------+----------------------+-----------------------+
1 row in set (0.00 sec)
31、least(n.....)/greatest(n....);统计多个参数中的最小值/最大值。
#注意:参数类型可以不一致,但是必须能互相自动转换
#统计1,2,'10','5'中最小值和最大值。
mysql> select least(1,2,'10','5'),greatest(1,2,'10','5');
+---------------------+------------------------+
| least(1,2,'10','5') | greatest(1,2,'10','5') |
+---------------------+------------------------+
| 1 | 10 |
+---------------------+------------------------+
1 row in set (0.00 sec)
32、dayofweek(date);查看指定日期date是所在周的第几天,返回的是数字,1--周日.......7--周六
33、weekday(date);返回指定date是所在周的周几。返回的也是数字,--0-周1........6--周日
34、dayname(date);返回指定日期是周几,注意:返回的是英文描述。
35、dayofmonth(date)/dayofyear(date);返回指定日期是当月/当年的第几天。
36、monthname(date);返回指定日期所在月的英文描述。
37、week(date,first);返回指定日期是当年的第几周.first可以取值0或1, 0表示周日是一周的第1天,1表示周一是一周的第1天。
39、year(date),month(date),hour(time),minute(time),second(time);返回指定日期或时间的时间分量。
40、extract(part from date);截取指定日期的一部分,part:year,month,day,hour,minute,second;
41、date_format(date,format) ;将指定日期date,按照自定义格式,转成字符串显示。
#格式:年: %y; 月: %M或%m;日: %d;时:H%;分:%i;秒:%s;
#将当前系统时间格式化 yyyy年mm月dd日 mm分ss秒HH时。
mysql> select date_format(now(),'%Y年%m月%d日 %i分%s秒%H');
+----------------------------------------------+
| date_format(now(),'%Y年%m月%d日 %i分%s秒%H') |
+----------------------------------------------+
| 2018年09月05日 14分34秒20 |
+----------------------------------------------+
1 row in set (0.00 sec)
42、curdate()/curtime();获取当前系统的日期/时间
43、now()/sysdate()/current_timestamp();获取当前系统时间:都包含年月日时分秒。
44、sec_to_time(n);将指定数值n转成时间格式
#计算一下36001转成时间格式
mysql> select sec_to_time(36001);
+--------------------+
| sec_to_time(36001) |
+--------------------+
| 10:00:01 |
+--------------------+
1 row in set (0.00 sec)
视图:
视图(View)是从一个或多个表(或视图)导出的表。
概念:视图与表不同,视图是一个虚表,即视图所对应的数据不进行实际存储,数据库中只存储视图的定义,在对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基本表。
create view view_emp_deptno_10 as select * from emp where deptno=10;
#with check option:对视图的一种检查约束选项,如果在创建视图有此选项时,表示,只能对视图DML操作可见数据。对视图不可见的数据,是不可以通过视图进行DML操作的。
视图的分类:
简单视图:subQuery是一个表中的普通查询语句。
复杂视图:subQuery是一个可以带有分组函数的,或者关联查询的语句。
复杂视图不能对视图进行DML操作,只能进行DQL操作。
索引:
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。
#给emp表中的empno创建索引。
create index index_emp_empno on emp(empno);