mysql基础

最新推荐文章于 2024-09-20 21:26:51 发布

码眼

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量298

点赞数

分类专栏： mysql 文章标签： mysql 基础

本文链接：https://blog.csdn.net/zhanglei082319/article/details/103832248

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、创建数据库

create database 数据库名;

二、删除数据库

drop database 数据库名;

三、数据类型

1、数值类型
1) tinyint 大小:1个字节范围:-128~127 用途: 小整数值
2) smallint 大小:2个字节范围: -32768~32767
3) mediumint 大小:3个字节范围: -8388608~8388607
4) int 大小:4个字节范围: -2147483648~2147483647
5) bigint 大小:8个字节范围: -9223372036854775~99223372036854775807
6) flat 大小:4个字节
7) double 大小:8个字节
8) decimal 大小: decimal(m,d) 整数位数=m-d 小数位数=d

注意: 数值类型的长度限制并不是针对数字的字符长度

2、日期时间类型
1) date 大小: 3个字节范围: 0000-01-01~9999-12-31 格式: yyyy-mm-dd
2) time 大小: 3个字节范围: -838-59-59~838-59-59 格式: HH:mm:ss
3) year 大小: 1个字节范围: 1901-2155 格式: yyyy
4) datetime 大小: 8个字节范围: 0000-01-01 00:00:00 ~ 9999-12-31 23:59:59 格式: yyyy-mm-dd HH:mm:ss
5) timestamp 大小: 4个字节范围: 1970-01-01 00:00:00 ~ 2038-01-19 11:14:07 格式: yyyy-mm-dd HH:mm:ss

3、字符串类型
1) char 大小:0~255个字节定长字符串
2) varchar 大小:0~65535个字节变长字符串
3) tinyblob 大小:0~255个字节不超过255个字符的二进制字符串不可定义范围
4) tinytext 大小:0~255个字节短文本字符串不可定义范围
5) blob 大小:0~65535个字节二进制形式的长文本数据不可定义范围
6) text 大小:0~65535个字节长文本数据不可定义范围
7) mediumblob 大小:0~16777215个字节二进制形式的中等长度数据
8) mediumtext 大小:0~16777215个字节中等长度文本数据
9) longblob 大小:0~4294967295 二级制的极大文本数据
10) longtext 大小:0~4294967295 极大文本数据

varchar 和 text的最大的不同是 text不可以设置默认值，不可以指定长度，不是变长字符串

总结：
经常变化的字段用 varchar
知道固定长度的用 char
尽量用 varchar
超过 255 字符的只能用 varchar 或者 text
能用 varchar 的地方不用 text


四、数据库表操作

1、创建表: create table 表名 (字段名字段类型);

   如下：
   CREATE TABLE `invite_exchange_return_record` (
   `id` bigint(18) NOT NULL AUTO_INCREMENT,
   `order_id` varchar(64) NOT NULL COMMENT '币币订单号',
   `base_id` bigint(18) NOT NULL COMMENT '结算用户',
   `tran_id` bigint(18) NOT NULL COMMENT '交易用户',
   `return_level` tinyint(4) NOT NULL COMMENT '返佣等级 1-9',
   `return_rate` decimal(12,5) NOT NULL COMMENT '返佣系数返佣比例',
   `return_symbol` varchar(32) NOT NULL COMMENT '返佣币种',
   `return_amount` decimal(18,8) NOT NULL COMMENT '返佣数量',
   `origin_amount` decimal(18,8) NOT NULL,
   `return_time` date DEFAULT NULL COMMENT '返佣时间',
   PRIMARY KEY (`id`),
   KEY `order_id` (`order_id`) USING BTREE,
   KEY `base_id` (`base_id`) USING BTREE,
   KEY `tran_id` (`tran_id`) USING BTREE,
   KEY `return_symbol` (`return_symbol`) USING BTREE,
   KEY `return_level` (`return_level`) USING BTREE
   ) ENGINE=InnoDB AUTO_INCREMENT=504 DEFAULT CHARSET=utf8 COMMENT='邀请返佣记录';

2、删除表: drop table 表名;
3、插入数据: insert into 表名(字段名1,字段名2,字段名3)values(值1,值2,值3);
insert into country(id,country_name,country_en_name,address)values(4,'俄罗斯','els','地球');
4、更新数据: update 表名 set 字段名=值 where 字段名=''
update country set address='亚洲' where id = 4;
5、查询数据: select * from country where id = 3;
6、删除数据: delete from 表名 where 字段名='';
delete from country where id=2;

五、查询语句

1) like 模糊查询关键字
'%key': 模糊查询以key结尾的值、
'key%': 模糊查询以key开头的值、
'%key%': 模糊查询包含key的值
例如：select * from country where address like '%亚%';

2) union 连接两个以上的查询结果到一个结果集合中
select u.id,u.user_name,u.email from `user` u union all select c.id,c.country_name,c.address from country c;
注意：两条sql语句返回的列数必须相同，类型可以不同，
公式： SELECT expression1, expression2, ... expression_n
           FROM tables
           [WHERE conditions]
           UNION [ALL | DISTINCT]
           SELECT expression1, expression2, ... expression_n
           FROM tables
           [WHERE conditions];
   注意 union后的 all/distinct all表示展示全部，distinct 表示去除重复的记录，此处重复的记录是所有字段都重复。

3) order by 排序格式： order by 字段名 asc/desc;
例： select * from user order by id asc ;
asc：表示正序排序为缺省值
desc: 表示倒序排序

4) group by 分组格式： select 字段1, function(字段2) from 表名 group by 字段名;
例：select name,count(*) from user group by country; 查询相同国家的用户数量列表

5) inner join ; left join ; right join 连接表
inner join：获取两个表中字段匹配关系的记录，没有匹配的忽略掉。
left join：获取左边所有记录，右边记录没有匹配则空值代替
right join：获取右边所有记录，左边记录没有匹配则空值代替

例：select * from user u left join country c on u.country_id = c.id;

6) null处理
is null: 如果值为null,则返回true
is not null: 如果值不为null,则返回true
<=>: 如果两个值相等，或者两个值都为null,则返回true
例：select * from user where name is not null;
注意： mysql中'=null' 或者 '!=null' 都不可作为查询条件，不起作用。

ifnull(字段名,0): 标识如果字段为null则用0代替，否则取字段值

7) regexp 正则表达式
例： select * from user where user_name regexp '^st' 查询所有用户名以st开头的数据

六、事务处理

事务的四大特性： ACID 原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持久性(Durability)
   原子性：一个事务中所有的操作，要么全部执行，要么全部不执行性，不会停留在中间的某个环节，事务执行中产生的错误会自动回滚到事务开始前的状态。
   一致性：在事务开始前和事务结束后，数据的完整性没有被破坏
   隔离性：数据库允许多个事务同时对数据进行读写操作，隔离性保证不同的事务之间不会相互干扰。数据库的隔离级别有四种：读未提交，读已提交，可重复读，可串行化
   一般数据库隔离级别缺省值为：读已提交， mysql的默认隔离级别比较特殊是：可重复读
   持久性：事务处理结束后，对数据库的更新是永久的。

   begin或start transaction：表示开启一个事务
   commit或commit work: 表示提交事务
   rollback或 rollback work：表示回滚一个事务
   savepoint 保存点名称: 允许在事务中创建一个保存点，嵌套事务，一个事务中可以有多个savepoint
   release savepoint identifier：删除一个事务的保存点。
   rollback to identifier: 把事务回滚到标记点。
   set transaction：用来设置事务的隔离级别 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
   set autocommit=0 禁止自动提交 =1表示开启自动提交，缺省值0

七、alter命令
更新表名，或者更新字段信息是可使用alter命令
   alter table user drop user_name; 删除user表中user_name字段。注意如果表中只有一个字段则不可删除
   alter table user add type varchar(40) comment '类型'; 在user表中添加字段type 类型为varchar
   alter table user add type varchar(40) null comment '类型' after id; 在user表中添加字段，指定字段位置。
   alter table user add type varchar(40) null comment '类型' first; 在user表中添加字段，字段放置在首位
   alter table user modify type varchar(64); 将user表中的type类型改成varchar(64)
   alter table user modify type varchar(64) first; | after id; 更改字段的相对位置
   alter table user change type user_type varchar(64); 将user表中的字段type名称改成user_type。
   alter table user alter type set default '测试';将user表中的type字段的默认值该成'测试'
   alter table user alter type drop default ; 删除user表中type字段的默认值，没有默认值则缺省值为null

   alter table user rename to member; 修改表名user为member

   alter table user engine=myisam; 修改表user的存储引擎为myisam;
   alter table user drop foreign key country_id; 删除user表的外键country_id约束

八、索引
四种索引类型
alter table user add primary key(id);添加主键，意味着索引必须唯一不可为空
alter table user add unique user_name(user_name); 添加user_name唯一索引，标识值必须唯一，但可以为空，null可以存在多个
alter table user add index type(type); 添加普通索引
alter table user add fulltext text_field(user_name,email,type,local); 添加全文索引，用于全文检索

注意：索引策略有两种 btree和hash 缺省值为btree

alter table user add primary key(id) 为user表添加主键
alter table user drop primary key; 删除主键

九、临时表操作

创建临时表
create temporary table test(
id bigint(18) primary key,
user_name varchar(64) null,
email varchar(64) null
);
注意： mysql的临时表只在连接中起效，连接关闭后临时表会被自动删除。

十、函数相关
### 字符串相关函数
select CHAR_LENGTH('hello world') as len; ##返回hello world的长度

select CHARACTER_LENGTH('背景') as len; ##返回背景的长度

select CONCAT('hello','world','china') as str; ## 拼接hello world china三个字符串

select CONCAT_WS(',','张三','李四','王五'); ##拼接张三，李四，王五用','分隔

select GROUP_CONCAT(user_name) u from user; ## 查询所有用户名用逗号分隔

select FIELD('1','3','c','d','1') as e; ## 查询第一个'1'在后面的第几为出现。从1开始数

select FIND_IN_SET('a','d,c,d,e,a') as c; ## 查询'a'在第二个参数中第几为出现，从1开始数

select FORMAT(100.3146,3) as f; ## 保留100.3146后面3为小数，最后一位四舍五入

select INSERT('hello world',1,5,'goe') as i; ## 将hello world从第一个字符开始到第五个用goe替换

select LOCATE('w','hello world') as l; ## 查询w在hello world字符串中出现的位置

select LEFT('hello world',4) as l; ## 截取从最左边开始四个字符

select LOWER('HELLO WORLD') as l; ## 将字符串小写

select LTRIM(' hello world') as l; ## 去掉字符串左边的空格

select MID('hello world',7,5) as m; ## 截取字符串hello world，从第7为开始截取，截取5位

select repeat('hello',4) as r; ## 将hello重复四次连接输出

select replace('hello world','hello','go') as r; ## 将hello world中的hello用go替换

select REVERSE('987654321') as r; ## 将数字字符串颠倒

select RTRIM('hello world ') as r; ## 去除字符串右边的空格

select SPACE(10) as s; ## 返回10个空格字符串

select strcmp('hello','hello') as s; ## 如果两个参数相同，返回0。第一个大于第二个返回1 ，小于第二个返回-1

select SUBSTR('hello world',7,5) as s; ## 截取字符串从第七位开始截取，截取5位

select TRIM(' hello world ') as t; ## 去除字符串左边和右边的空格

select UCASE('hello world') as u; ## 字符串大写

select UPPER('hello world') as u; ## 字符串大写

### 数字函数

select abs(-231) as a; ## 获取绝对值

select avg(id) from user; ## 获取平均值

select ceiling(3.4) as c; ## 返回大于3.4或者等于3.4的最小整数

select count(0) from user; ## 获取总数

select 10 div 2 as d; ## 10/2 取整

select exp(2) as e; ## 返回 e 的 x 次方

select floor(3.6) as f; ## 返回小于或等于3.6的最大整数

select GREATEST(32,35,646,2432,7,34) as g; ## 获取参数中的最大值

select LEAST(24,523,53,42,3) as l; ## 获取参数中的最小值

select max(id) from user; ## 获取表中字段的最大值

select min(id) from user; ## 获取表中字段的最小值

select mod(7,3) as m; ## 获取7/3的余数等价于7%3

select pi() as p; ## 圆周率

select pow(3,4) as p; ##获取3的4次方

select rand() as r; ## 获取0-1的随机小数

select truncate(rand()*100,0) as r; ##获取0-100的随机整数

select round(4.5) as r; ## 获取4.5的四舍五入整数

select sign(23) as s; ## 返回参数的符号参数大于0返回1，小于0返回-1，等于0返回0

select sqrt(4) as s; ## 返回4的平方根

select sum(id) from user; ## 获取表中id字段的总和

select truncate(324.4223,1) as t; ## 返回324.4223，保留1为小数

### 日期函数

select adddate('2019-12-31 12:23:42',10) as a; ## 返回10天后的时间

select addtime('2019-12-31 12:23:42',40) as a; ## 返回40秒后的时间

select curdate() as c; ## 返回当前日期

select curtime() as c; ## 返回当前时间

select date('2019-12-31') as d; ## 自定义日期

select abs(datediff('2019-12-31','2020-01-12')) as d; ## 返回两个时间之间的间隔天数

select subdate('2020-01-12',2) as s; ## 返回2天前的日期

select day('2020-05-22') as d; ## 返回指定日期为当月的第几天

select dayname('2020-01-13') as d; ## 返回指定日期为星期几

select dayofmonth('2020-04-14') as d; ## 返回指定日期是当月的第几天

select dayofweek('2020-01-04') as d; ## 返回指定日期是周几周日为1

select dayofyear('2020-06-03') as d; ## 返回指定日期是当年的第几天

select hour('21:32:24') as h; ## 获取小时的时间

select last_day('2020-02-01') as l; ## 获取当月的最后一天

select now() as n; ## 获取当前日期时间

select weekday('2020-01-06') as w; ## 获取指定日期是周几周一为0

### 高级函数

select bin(1023) as b; ## 返回10进制1023的二进制数据

select binary('abc') as b; ## 返回指定字符串的二进制数据

   select case
   when 1>1 then 1
   when 2>0 then 2
   when 3>0 then 3
   end as c;

select cast('232.24' as signed) as c; ### 可转换的类型有： binary char date datetime time decimal signed UNSIGNED 类型转换

select connection_id() as c; ## 当前连接数量

select conv(255,10,2) as c; ## 将255的10进制数据，转换成二进制返回

select charset(convert('abc' using gbk)) as c; ## utf8格式的abc字符串转成gbk

select current_user() as c; ## 返回当前用户

select database() as d; ## 返回当前正在使用的数据库

select if(1>0,'true','false') as i; ## 如果1>0 返回true,否则返回false

select ifnull(null,'hello world') as i; ## 如果第一个参数为null,则返回默认值hello world

select isnull(null) as i; ## 判断参数是否为null。是返回1 否则返回0

select last_insert_id() as l; ## 返回最近一次自增的主键id

select nullif(1,1) as n; ## 如果两个参数相同，返回空，否则返回第一个参数

select session_user() as s; ## 获取当前session用户

select system_user() as s; ## 获取系统用户

select user() as u; ## 获取当前用户

   select version() as v; ## 获取mysql数据库版本

码眼

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录