mysql--笔记

cmd:

登陆：mysql -uroot -p -P3306 -h127.0.0.1

查看版本：mysql -V

 

mysql：（记得写分号）

关键字、函数名大写；其他小写

修改提示符：prompt ...(\d-->当前数据库，\D-->完整日期，\h-->服务器名，\u-->当前用户）

查看版本：SELECT VERSION();

查看当前日期：SELECT NOW();

查看当前用户：SELECT USER();

设置字符集：set names gbk;

\G：       左:右    （默认是上下）

 

数据库

创建数据库：create database 库名 charset utf8;

显示所有数据库：show databases;

匹配某些数据库：show databases like "..."：

                              _  -->  以当前位置一个字符;    % --> 指定位置多个字符

                              （例如现在有mytest和mytest2两个数据库，show databases like "m_test"，show databases like "my%";）

查看数据库创建语句：show create database 库名;

选择数据库：use 库名;

修改数据库字符集：alter database 库名 charater set gbk;

删除数据库：drop database 库名;

 

表

创建表：craete table 表名(字段名 字段类型 [字段属性],...)[表选项];

复制表结构：create table 表名 like 库名.表名;（不会复制数据）

显示所有表：show tables;

匹配表：show tables like "匹配模式";

显示表结构：desc 表名;  或者  show columns from 表名;

查看创建表语句：show create table 表名;

修改字符集：alter table 表名 charset gbk;

修改表名：rename table 表名 to 新表名;

字段

修改表名：rename table 表名 to 新表名;

新增字段：alter table 表名 add 名称 类型 [first/after 字段名];

修改字段名：alter table 表名 change 字段名 新字段名 新字段类型;

修改字段类型：alter table 表名 modify 字段名 新字段类型;

 

删除字段：alter table 表名 drop 字段名,drop 字段名;

删除表结构：drop table 表名[,表名];

 

表数据的增删改查

插入数据：insert into 表名 [字段列表] values (对应字段列表),(对应字段列表);

                 （insert into mytest(name, age) values ("ok", 12);）（如果没有字段列表，就是所有字段）

        可以使用子查询：insert into 表名 set 字段名 = ...;

        将查询结果插入指定数据表：insert into 表名 select ...;

如果插入的数据中是已经存在的主键字段，而你又想更新该数据，例如name字段时主键，而现在存在了name = ok，

①insert into mytest(name, age) values ("ok", 10) on duplicate key update age = 10;

②replace into mytest(name, age) values ("ok", 10);

 

简单条件删除：delete from 表名 [where 字段名 = 值];（如果没写where，默认删除整个表所有数据）

                         但是如果有自增长的话会有些问题，这时应该truncate 表名; （实际上就是drop之后再重新insert）

 

更新（修改）：update 表名 set 字段名 = 值 [where 字段名 = 值] limit 数量;（如果不写where则修改表中全部那个字段的值）

                        ①修改name为ok的年龄为13：update mytest set age = 13 where name = 'ok';

                         ②将前4个name为小名的修改成小明：update mytest set name = '小明' where name = '小名' limit 4; 

查询：

查询所有数据：select * from 表名;  （*表示所有字段）

查询部分字段：select 字段 [as 别名],字段 [as 别名]... from 表名;

简单条件查询：select 字段名  from 表名 where 字段名 = 值;（例如获取13岁的人的名字：select name from mytest age = 13;）

查询结果分组：select 字段名 from 表名 group by 组名 [having 条件];

查询结果排序：select 字段名 from 表名 order by 字段名 [asc升序/desc降序],字段名 [asc/desc]...;

查询结构限制数量：select 字段名 from 表名 limit 数量;（如果是limit 2,2：从第二条数据开始往后2个，注意数据是从0开始的）

         去除重复值：select distinct 字段名 from 表;

列类型

整型：tinyint(1字节：-128~127）、samllint、mediumint、int、bigint

浮点型：float、double、decimal（精确）

日期（不常用）：year、date（YYYY-mm-dd）、time（HH-ii-ss）、datetime（YYYY-mm-dd HH-ii-ss）、timestamp时间戳

字符型：char（M个字节）、varchar(M)、tinytext、mediumtext、longtext、

              enum（v1,v2...）（相当于单选）、set（v1，v2...）（相当于多选）

无符号：unsigned

不能为空：not null

zerofill：从左填充0，只能是无符号的，不到3位填充到3位

列属性（字段属性）

null：是否为空

default：默认值

comment：描述（维护用的，通过show create table 表名才能看到）

主键（对应的数据不能为空，不能重复）

设置主键：①添加字段时加上：id int primary key     ②所有字段之后添加：primary key(字段名，字段名...)   

                  ③表后添加：alter table 表名 add primary key(字段名);

查看主键：①查看表结构    ②查看表的创建语句

删除主键：alter table 表名 drop primary key;

自动增长（依赖于主键）

id int primary key auto_increment

修改值： alter table 表名 auto_increment = 数值;

删除自动增长：alter table 表名 modify id int;（其实就是重新设置）

查看自动增长初始变量：show variables like ‘auto_increment%';

唯一约束（表中可以有多个，不允许重复，但允许多个为null）

设置唯一约束：①添加字段时加上：id int nuique     ②所有字段之后添加：unique key(字段名，字段名...)   

                  ③表后添加：alter table 表名 add nuique key(字段名);

查看唯一约束：查看表结构-->key列

删除唯一约束：alter table 表名 drop index 想要删除的字段名;

修改：先删除后增加

表关系

一对一：一般用来保证两张表中使用同样的主键

一对多（多对一）：在多的这个表中维护一个字段，就例如现在有两张表，一张是人的信息表，另一张是房子信息表，房子表中有一个字段是人物名字（id），对应着人表，而有人有好几个房子，这样就是“多对一”

多对多：添加中间表

外键约束：

设置my.ini文件：default-storage-engine=INNODB

foreign key (字段) references 表名 (字段) [on delete cascade];   （这两个字段的属性要相同）

cascade：从父表删除或更新时，自动删除子表所对应的行

alter table 子表 add foreign key（字段名） references 父表（字段名）;

如果要删除父表中的某行数据，得先将子表中对应的数据删除

删除外键

语法： ALTER TABLE table-name DROP FOREIGN KEY key-id;

子查询（出现在其他sql语句中的select子句）

必须出现在圆括号内

①比较运算符的子查询：

            any/some：只要比其中一个大/小，就可以了   

                               例如现在子查询返回的结果有三个数据，100、20、50，要求找出>=any，也就是说只要大于20 就成

            all：要比最大的大/最小的小

                               例如现在子查询返回的结果有三个数据，100、20、50，要求找出>=all，也就是说要大于100才行

            [not] in：相当于非的意思

            例子：查询价格大于或等于“笔”价格的商品

                       select name,price from mytest where price >= all (select price from mytest where name = '笔');

 

蠕虫复制（成倍复制，将查询结果写入其他表中）：

inset into 表名 [(字段列表)] select 字段列表 from 表名;    （insert into mytest (name) select name from mytest）

多表更新：update 表1 inner/cross join 表2 on 表1字段名 = 表2字段名 set 表2字段名 = 值...;

连接

内连接：inner join  

外连接：outer join    

字符函数

concat()：字符连接

concat_wa(分隔符，字符，字符...)：使用指定的分割符进行字符连接

format(数字，保留小数)：数字格式化（以千分位显示例如1,000.00）

lower()：转成小写

upper()：转成大写

left(字符串，位数)：获取左侧多少位字符

right(字符串，位数)：获取右侧多少位字符

lenght()：获取字符串长度，空格也算

LTRIM()：删除最左侧空格

RTRIM()：删除最右侧空格

TRIM()：删除空格     TRIM(LEADING 字符 字符串)：删除字符串中最左侧字符（TRAILING右侧、BOTH左右）

replace(字符串, 字符, 想要换成的字符)

substring(字符串，开始位置，数量)  注意是从1开始

[not] like：最上面数据库那有例子

数字运算

ceil()：向上取整

floor()：向上取整

数 div 数：整数除法

数 mod 数：取模

power()：幂运算

round()：四舍五入

truncate()：数字截取

比较运算符

数 [not] between ... and ...：[不]在范围里面

数 [not] in(数,数...)：这些数[不]包含这数

is [not] null：[不]为空

日期时间（用得少）

now()：当前日期和时间

curdate()：当前日期

curtime()：当前时间

date_add(日期字符串, interval -100 year/day/week...)：前100天/年/星期

datediff(日期字符串,日期字符串)：日期差值

date_format(日期字符串, 格式)：日期格式化

信息函数

connection_id()：连接id

database()：当前数据库

last_insert_id()：最后插入记录的字段id

user()：当前用户

version()：当前版本

聚合函数

avg()

count()

max()

min()

sum()

加密函数

md5()：加密算法

password()：修改密码

自定义函数

①create function 函数名() returns varchar(50)

->return ...

②create function 函数名(num1 smallint unsigned, num2 smallint unsigned)

->returns float(10,2) unsigned

-<return (num1 + num2)/2;