《mysql必知必会》学习

xiaoxiao2021-03-25 58

本学习使用的数据库版本为mysql5.6

查看mysql版本

mysql> select version(); +------------+ | version() | +------------+ | 5.6.25-log | +------------+ 1 row in set (0.00 sec)

选择数据库 use databse; 或者你可以选择查看可用数据库的列表show databses; 为了获得一个数据库内的表，你可以使用show tables; 你可以查看一个表中的信息show columns from user;你也可以使用describe user; mysql的其他show还有：

show status; 用于显示广泛的服务器状态信息 show create table user; 查看创建特定表和数据库的mysql语句 show create database mybatis; show errors; show grants; show warnings;

还有许多show命令，您可以通过help show;来查看。

使用mysql进行查询：select * from user 检索不同行的数据，使用select distinct sex from user; 使用distinct关键字的时候，其作用于所有的列上而并非作用于它的前置列。

限制结果可以使用select username from user limit 5;表示从返回前5行数据，如果想指定起始的行数，可以使用select username from user limit 5,5;表示从第6行开始（第五行数据不计算在内）的5行数据。还可以使用select username from user limit 3 offset 3;

如果您输入的是select username from user limit -1;则数据库报错，如果您输入的是select username from user limit 0;则返回空集。

如果想对数据库返回的结果排序可以使用select username,id from user order by username desc;默认为升序（asc）。select username,id from user order by username;

一般我们排序都是使用的显示所选择的列，实际上使用非检索的列也是允许的，比如这样的语句也是正确的select id from user order by username desc;

如果您想指定多个排序规则则可以使用select username,id from user order by username asc,id desc;

order by子句的位置，在给出order by子句时，应该保证它位于from字子句之后，如果使用limit，它必须位于order by之后，使用子句的次序不正确将导致错误。

过滤数据（where）select username,id from user where id > 10; where子句的位置应该位于order by之前，否则会产生错误。

between是左右闭区间

mysql> select username,id from user where id between 1 and 1; +----------+----+ | username | id | +----------+----+ | 王五 | 1 | +----------+----+

当between左边的值大于右边的时候不会报错，会显示空集。

空值检查

根据上图我们可以发现，空值的判断需要使用is null而不是= null。

AND和OR操作符同时存在时优先结合AND操作符。

in操作符：select id from user where id in(1,10,16,18,20); not操作符：select id from user where id not in(1,10,16,18,20);

通配符 % 匹配任意个字符，除了NULL之外。 _ 匹配且只匹配一个字符

不要过度使用通配符，这样会降低搜索效率。不要在搜索模式的开始处使用通配符，搜索将会变得非常慢。

使用mysql正则表达式select username,id from user where id regexp '1.';

在《mysql必知必会》这本书上，还写着regexp和like的区别，如果使用like，被匹配的文本在列值中出现，like将不会找到它，相应的行也不会被返回。

但是我在实际的实验中，得到的却不是这个结论：

mysql> select username,id from user where username like '王五'; +----------+----+ | username | id | +----------+----+ | 王五 | 1 | | 王五 | 26 | +----------+----+ 2 rows in set (0.00 sec) mysql> select username,id from user where username regexp '王五'; +----------+----+ | username | id | +----------+----+ | 王五 | 1 | | 王五 | 26 | +----------+----+ 2 rows in set (0.00 sec)

regexp也返回了完整的结果，和书上的结论不一致，不知道是因为版本的原因还是书印刷错误。不过还是以最终的测试结果为准吧。

mysql的正则表达式自mysql 3.23.4之后就不区分大小写了，如果希望区分大小写可以使用binary关键字。

mysql> select username,id from user where username regexp 'XZ'; +----------+----+ | username | id | +----------+----+ | xz | 27 | | xz | 28 | | xz | 30 | | xz | 31 | +----------+----+ 4 rows in set (0.00 sec) mysql> select username,id from user where username regexp binary 'XZ'; Empty set (0.00 sec)

匹配任意一个字符可以使用. 匹配几个字符之一可以使用[123] 除却匹配某些字符可以使用[^123] 匹配一个范围可以使用[1-9] 匹配特殊字符使用\转义 0个或多个匹配* 1个或多个匹配+ 0个或1个匹配？指定数目的匹配 {n} 不少于指定数目的匹配{n,} 匹配数目的范围{n,m} m不能超过255

[:character_class:] 在括号表达式中（使用[和]），[:character_class:]表示与术语类的所有字符匹配的字符类。标准的类名称是： alnum 文字数字字符 alpha 文字字符 blank 空白字符 cntrl 控制字符 digit 数字字符 graph 图形字符 lower 小写文字字符 print 图形或空格字符 punct 标点字符 space 空格、制表符、新行、和回车 upper 大写文字字符 xdigit 十六进制数字字符

mysql> select username,id from user where username regexp 'w[[:alpha:]]'; +----------+----+ | username | id | +----------+----+ | wangwu | 72 | | wangsan | 73 | +----------+----+ 2 rows in set (0.00 sec)

上面的例子实际上都是匹配一个串中任意位置的文本，为了匹配特定位置的文本，可以使用定位符。 ^ 文本的开始 $ 文本的结束 [[:<:]] 词的开始 [[:>:]] 词的结束

正则表达式的测试

mysql> select 'hello' regexp '[0-9]'; +------------------------+ | 'hello' regexp '[0-9]' | +------------------------+ | 0 | +------------------------+ 1 row in set (0.00 sec)

mysql使用Concat拼接串

mysql> select Concat(username,'[',id,']') from user where username regexp '^[^x] +$'; +-----------------------------+ | Concat(username,'[',id,']') | +-----------------------------+ | 王五[1] | | 张小明[16] | | 陈小明[22] | | 张三丰[24] | | 陈小明[25] | | 王五[26] | | zhangsan[70] | | lisi[71] | | wangwu[72] | | wangsan[73] | +-----------------------------+ 10 rows in set (0.00 sec)

mysql可以使用LTrim，Trim，RTrim去掉左边、两边、右边的空格。 mysql可以使用now得到当前的时间 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2017-03-14 19:29:22 | +---------------------+ 1 row in set (0.00 sec)

mysql的数据处理函数如果指定column值，则count函数忽略NULL值，如果使用*则不会忽略NULL值。 AVG函数将会忽略NULL值。 mysql5以后添加了参数，默认是all，也可以是distinct

mysql> select count(distinct username) from user; +--------------------------+ | count(distinct username) | +--------------------------+ | 11 | +--------------------------+ 1 row in set (0.00 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 22 | +----------+ 1 row in set (0.00 sec)

在count中，distinct不能使用于*上

mysql> select count(distinct *) from user; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) fr om user' at line 1

分组使用Group By子句的规则： 1、除了聚集函数之外，select语句中的每个列都必须在group by子句中给出。 2、如果分组中有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。 3、group by子句必须出现在where子句之后，order by子句之前。

如果违反了规则，应该是报错的。但是有的时候mysql并不会报错，如下：

mysql> select username,count(*) from user group by username; +-----------+----------+ | username | count(*) | +-----------+----------+ | lisi | 1 | | wangsan | 1 | | wangwu | 1 | | xiezhao | 5 | | xiezhao12 | 3 | | xz | 4 | | zhangsan | 1 | | 张三丰 | 1 | | 张小明 | 1 | | 王五 | 2 | | 陈小明 | 2 | +-----------+----------+ 11 rows in set (0.00 sec) mysql> select username,max(id) from user; +----------+---------+ | username | max(id) | +----------+---------+ | 王五 | 73 | +----------+---------+ 1 row in set (0.00 sec)

可能也有人碰到过这样的情况，如果碰到了可以读一读mysql的官方资料：https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

To disable the MySQL GROUP BY extension and enable standard SQL behavior, enable the ONLY_FULL_GROUP_BY SQL mode. In this case, columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function.

我们需要设置sql_mode为ONLY_FULL_GROUP_BY模式。

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

此时，如果继续查询，mysql将给出报错

mysql> select username,max(id) from user; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR OUP columns is illegal if there is no GROUP BY clause mysql> select username,id from user group by username; ERROR 1055 (42000): 'mybatis.user.id' isn't in GROUP BY

group by分组的顺序有的时候是以分组顺序输出的，但是这并不是sql要求的规范，当使用group by的时候，应该配套order by以获得正确的排序。

使用with rollup可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

过滤分组使用HAVING select子句及顺序 1、select 返回列或表达式 2、from 检索数据的表 3、where 行集过滤 4、group by 分组 5、having 组集过滤 6、order by 输出排序顺序 7、limit 检索的行数

在select语句中，子查询总是从内向外处理。由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

mysql联结

select o.user_id,od.orders_id from orders o inner join orderdetail od on o.id = od.orders_id;

union

mysql> select id,username,sex from user where id<=10 -> union -> select id,username,sex from user where sex=2; +----+----------+------+ | id | username | sex | +----+----------+------+ | 1 | 王五 | 2 | | 10 | xiezhao | 1 | +----+----------+------+ 2 rows in set (0.00 sec)

其实union和where的作用是类似的，上面的查询等价于

select id,username,sex from user where id<=10 or sex =2 ;

union从查询结果集中默认的自动去除了重复的行。如果希望改变这种行为，可以使用union all。

mysql> select id,username,sex from user where id<=10 -> union all -> select id,username,sex from user where sex=2; +----+----------+------+ | id | username | sex | +----+----------+------+ | 1 | 王五 | 2 | | 10 | xiezhao | 1 | | 1 | 王五 | 2 | +----+----------+------+ 3 rows in set (0.00 sec)

对union的结果集进行排序，只能使用一条order by语句，在最后一条select语句后面，不能对每一部分分别使用order by。

mysql> select id,username,sex from user where id<=10 order by username -> union -> select id,username,sex from user where sex=2 order by username; ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

mysql常用的数据库引擎为InnoDB和MyISAM。其中后者支持全文搜索而前者不支持。 mysql全文索引

mysql数据插入

mysql> insert into user(username,sex) values('hellowrold',2); Query OK, 1 row affected (0.00 sec)

有的时候insert会非常的耗时，比如更新索引，而且会降低等待处理的select语句的性能，如果数据检索是最重要的，那么可以使用low_priority降低insert语句的优先级。 insert low_priority into user(username,sex) values('hellowrold',2);

如果一次性insert多个值的时候，可以使用一次insert多个值来替代insert多次值的操作。

mysql> insert low_priority into user(username,sex) values('hellowrold',2),('myNa me',1); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0

插入检索的数据

mysql> INSERT INTO orders(createtime,user_id,number) -> select birthday,id,username -> from user -> where birthday is not null; Query OK, 1 row affected (0.00 sec)

update子句 update更新表

mysql> update orders -> set number=number+1 -> where user_id=1; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0

如果使用update更新多行语句，那么更新的时候如果出现错误则所有的更新将会被取消，此时如果想忽略错误的更新可以使用ignore。

mysql> update ignore orders -> set number=number+1 -> where user_id=1; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0

删除数据为了删除某一列的值，可以使用update，将某一列的值更新为null。 delete操作删除一行或多行数据。 delete只会删除表的内容而不会删除表。想删除一张表里面所有的内容可以使用truncate table语句，其内部直接删除表然后重新建表。

mysql数据类型 mysql定长串和mysql可变长度串。 mysql处理定长串的性能要优于可变长度串，mysql不允许对变长串进行索引，这也会影响性能。

串数据类型 char 1-255个字符的定长，默认为char(1)。 enum 接收最多64k个串组成的一个预定义集合的某个串。 tinytext 最大长度为255字节的text text 最大长度为64k的变长文本 mediumtext 最大长度为16k的text longtext 最大长度为4g的text set 接受最多64个串组成的一个预定义集合的0个或多个串。 varchar 长度可变，最多不超过255字节

数值不是数值的时候，比如‘01234’存储在数值中，最后会得到1234而丢失了信息。

数值数据类型如果您确定您的数值为正数，您可以在数值类型前面加上unsigned，这样可以让数值范围增大2倍。 bit 位字端 1-64位 bigint 整数值相当于java中的long范围 boolean（bool）布尔标志 decimal（dec）精度可变的浮点数 double 双精度浮点数 float 单精度浮点数 int（integer）相当于java中的int范围 mediumint 支持-8388608-83888607的整数值 real 4字节的浮点数 smallint 整数值，支持-32767–32768 tinyint 支持-128–127的整数值

日期类型 date 表示1000-01-01~9999-12-31的日期，格式为yyyy-mm-dd datetime date和time的组合 time 格式为hh：mm：ss year 用2位数表示范围为70（1970~2069）用4位数表示，范围为1901~2155年。 timestamp 功能和datetime相同，但是范围稍小。

二进制数据 blob 最大长度64k mediumblob 最大长度16mb longblob 最大长度4g tinyblob 最大长度255字节

创建表

auto_increment 当我们使用此关键词的时候，mysql会在在增加一行数据的时候自动增量，当我们使用自定义的id时，后续的增量将会在我们自定义的增量基础之上开始增加。

mysql> select * from student; +----+--------------+ | id | username | +----+--------------+ | 1 | helloworld | | 2 | helloworldMy | | 3 | xz | +----+--------------+ 3 rows in set (0.00 sec) mysql> insert into student values(7,'helloworldMy'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+--------------+ | id | username | +----+--------------+ | 1 | helloworld | | 2 | helloworldMy | | 3 | xz | | 7 | helloworldMy | +----+--------------+ 4 rows in set (0.00 sec) mysql> insert into student() values(); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+--------------+ | id | username | +----+--------------+ | 1 | helloworld | | 2 | helloworldMy | | 3 | xz | | 7 | helloworldMy | | 8 | xz | +----+--------------+ 5 rows in set (0.00 sec)

你可以通过

mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 8 | +------------------+ 1 row in set (0.00 sec)

来得到最新增加的id值。（这是一个函数需要加上括号）

mysql不允许使用函数作为默认值，它只支持常量。

mysql> create table functionDefault( -> id int primary key auto_increment, -> username varchar(50) default now())ingine=InnoDB; ERROR 1067 (42000): Invalid default value for 'username' mysql> create table functionDefault( -> id int primary key auto_increment, -> username varchar(50) default '1994-09-09' -> )engine=InnoDB; Query OK, 0 rows affected (0.02 sec)

mysql引擎 InnoDB是一个可靠的事务处理引擎，它不支持全文搜索 MEMORY在功能上等价于MyISAM，但是数据存储在内存中，速度快，适合做临时表。 MyISAM是一个性能极高的引擎，它支持全文搜索，但是它不支持事务处理。

引擎类型可以混用，但是外键不能跨引擎。

更新表

一般表的结构是不会发生变化的，所以alter table的作用大都是定义外键。

alter table stu1 add constraint fk_username foreign key (username) references user (id);

删除表

mysql> create table helloworld( -> id int primary key -> )engine=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> drop table helloworld; Query OK, 0 rows affected (0.01 sec)

重命名表

mysql> create table helloworld( -> id int primary key -> )engine=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> rename table helloworld to helloMyWorld; Query OK, 0 rows affected (0.01 sec)

视图创建视图并使用

mysql> create or replace view myview as -> select id,username from user where id<=10; Query OK, 0 rows affected (0.01 sec) mysql> select username,id -> from myview -> ; +----------+----+ | username | id | +----------+----+ | 王五 | 1 | | xiezhao | 10 | +----------+----+ 2 rows in set (0.00 sec)

视图的作用： 1、重用sql语句。 2、简化复杂的sql语句。 3、保护数据。

查看创建视图的语句

mysql> show create view myview; +--------+---------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------+----------------------+---------------- ------+ | View | Create View | character_set_client | collation_conne ction | +--------+---------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------+----------------------+---------------- ------+ | myview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DE FINER VIEW `myview` AS select `user`.`id` AS `id`,`user`.`username` AS `username ` from `user` where (`user`.`id` <= 10) | utf8 | utf8_general_ci | +--------+---------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------+----------------------+---------------- ------+ 1 row in set (0.00 sec)

视图的其中一个用途就是重新格式化检索出的数据。

mysql> create or replace view myview as -> select concat(username,'-',id) as uid -> from user -> where id<=10; Query OK, 0 rows affected (0.01 sec) mysql> select uid -> from myview; +------------+ | uid | +------------+ | 王五-1 | | xiezhao-10 | +------------+ 2 rows in set (0.00 sec)

存储过程 1、封装了操作。 2、简化了对变动的管理。 3、提高性能。使用存储过程比使用单独的sql语句要快。

创建和使用存储过程

mysql> delimiter // mysql> create procedure procedure1() -> begin -> select username from user where id<=10; -> end// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call procedure1(); +----------+ | username | +----------+ | 王五 | | xiezhao | +----------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

其中delimiter是改变mysql的分隔符，其默认分隔符为；除了/之外，任何字符都可以做mysql的分隔符。

删除存储过程

mysql> drop procedure MyProcedure; Query OK, 0 rows affected (0.00 sec)

更健壮的删除方式

mysql> drop procedure if exists MyProcedure; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------+ | Note | 1305 | PROCEDURE mybatis.MyProcedure does not exist | +-------+------+----------------------------------------------+ 1 row in set (0.00 sec)

存储过程实例

mysql> delimiter // mysql> mysql> drop procedure if exists calc; -> create procedure calc( -> in usernameLike varchar(20), -> in sexNeed varchar(20), -> in addressLike varchar(20), -> out idSum int, -> out idCount int, -> inout idInAndMax int -> )comment '不是必须的，如果有则会在show procedure status中显示' -> begin -> -> select count(id) into idCount -> from user -> where username like usernameLike -> and sex like sexNeed -> and address like addressLike -> and id>=idInAndMax; -> -> select sum(id) into idSum -> from user -> where username like usernameLike -> and sex like sexNeed -> and address like addressLike -> and id>=idInAndMax; -> -> select max(id) into idInAndMax -> from user -> where username like usernameLike -> and sex like sexNeed -> and address like addressLike -> and id>=idInAndMax; -> -> end// ERROR 1046 (3D000): No database selected mysql> delimiter ; mysql> mysql> set @idInAndMax=33; Query OK, 0 rows affected (0.00 sec) mysql> call calc('%x%','1','

转载请注明原文地址: https://ju.6miu.com/read-50300.html

技术

最新回复(0)