MySQL
不区分大小写,可根据自己的习惯来使用大写还是小写。我个人喜欢小写,感觉在写的过程中,来回切换大小写很是别扭。#MySQL以分号(;)作为结尾
1.数据库操作
创建数据库 Create DataBase
create database db_name; //创建数据库
show databases;
show databases like 'db_name_part';
show create database db_name;
create database db_name
default character set gb2312 //创建默认字符集gb2312
default collate gb2312_chinese_ci; //设定默认校对规则 gb3212_chinese_ci
修改数据库 Alter DataBase
alter database db_name //可用于修改字符集和默认校对规则
default character set gb2312 //创建默认字符集gb2312
default collate gb2312_chinese_ci; //设定默认校对规则 gb3212_chinese_ci
删除数据库 Delete DataBase
drop database db_name; //有待补充
选择数据库 Choose DataBase
use db_name;
2.操作表
修改表的存储引擎
alter table tb_name engine=MyISAM; // or MyISAM 、InnoDB、Merge、Federated ..........
设置默认存储引擎
set default_storage_engine=MyISAM;
创建表 Create Table
create table tb_name( ) ; //创建一个名为tb_name的表;
### for example ###
create table zach
(
id int(11),
name varchar(20),
address carchar(20),
tele int(11)
);
####################
show tables; //查看数据库中的所有表
desc tb_name; //查看表结构
show create table tb_name;
修改表 Alter Table
==添加字段==
alter table tb_name add column new_column int first; //在表的第一列添加一个int类的字段new_column;
alter table tb_name add column new_column int after one_column;
==修改字段数据类型==
alter table tb_name modify one_column varchar(50); //将one_column字段数据类型修改为varchar(50)类型.
==删除字段==
alter table tb_name drop one_column;
==修改字段名称==
alter table tb_name change one_column new_column_name varchar(25); //修改one_column的名字和数据类型为new_column_name varchar(25)
==修改表名==
alter table tb_name rename to new_tb_name;
删除表 Delete Table
drop table tb_name;
3.约束
主键约束 Primary Key
==设置主键==
create table zach
(
id int(11) primary key, //primary key 即为主键标志。
name varchar(20),
address carchar(20),
tele int(11),
salary float,
edu date
------------------------------
primary key(id) /或者
------------------------------
);
==设置复合主键==
primary key(id,name)
==添加主键约束==
alter table tb_name add primary key(id);
外键约束 Foreign Key
外键对应于主键,外键所在表中的列与主键所在表中的列一一对应。
设置table zach的primary key(id)
create table alan
(
id int(11) primary key,
name varchar(20),
tele int(11),
deptId int(11),
salary float,
-----创建与zach中相对于的外键----------
constraint fk_zach_alan
foreign key(deptId) references zach(id)
--------------------------------------
);
==添加外键约束==
alter table alan
add constraint fk_zach_alan
foreign key(deptId)
references zach(id);
==删除外键约束==
alter table alan
drop foreign key fk_zach_alan;
唯一约束 Unique
唯一约束,要求所在列唯一,确保该列不出现重复值
==设置唯一约束==
create table alan
(
id int(11) primary key,
name varchar(20) unique, //unique即为将name列设置为唯一约束
....
);
==添加唯一约束==
alter table alan
add constraint unique_name unique(name);
==删除唯一约束==
alter table alan
drop index unique_name;
检查约束 Check
==设置检查约束==
create table alan
(
id int(11) primary key,
name varchar(20) unique,
deptId int(11),
salary float,
check(salary>100 and salary<200), //检查约束
foreign key(deptId) references zach(id)
);
==添加检查约束==
alter table alan
add constarint check_salary
check(salary>100 and salary<200);
==删除检查约束==
alter table alan
drop constraint check_salary;
默认值约束 Default
当没有为一个字段赋值时,系统自动为这个字段赋值为默认值。
==设置默认值约束==
create table alan
(
id int(11) primary key,
name varchar(20) unique,
deptId int(11),
location varchar(50) default 'CHINA'
);
==添加默认值约束==
alter table alan
change columu location
location varchar(50) default 'CHINA';
==删除默认值约束==
alter table alan
change column location
location varchar(50) default null;
非空约束 Not Null
非空约束可约束该列的取值不能为空。
==设置非空约束==
create table alan
(
id int(11) primary key,
name varchar(20) unique,
deptId int(11) not null, // not null 非空约束
location varchar(50) default 'CHINA'
);
==添加非空约束==
alter table alan
change column deptId
deptId int(11) not null;
==删除非空约束==
alter table alan
change column deptId
deptId int(11) null;
4.函数
select function();
数值型函数
abs(val) // 绝对值
mod(val1,val2) // val1%val2
sort(val) // 二次方跟,负数返回结果为null
sign(val) //返回val的符号; 1 0 -1
ceil(val) ceiling(val) // 返回不小于val的最小整数值 bigint
sin(val) asin(vall) // 正弦函数与反正弦函数
cos(val) acos(vall) // 余弦函数与反余弦函数
tan(val) atan(val) // 正切函数与反正切函数
字符串函数
select function() as column;
length('string') // 一个英文字母占1个字节,一个汉字是3个字节
upper('string') // 将string中的小写字母全部转换为大写字母
lower('string') // 将string中的大写字母全部转换为小写字母
left('string',n) // 返回字符串string中的最左边n个字符
right('string',n) // 返回字符串string中的最右边n个字符
concat(s1,s2,...) // 连接参数中的字符串,但有一个参数为Null时,则返回结果为null
trim('string') // 删除字符串string两侧的空格
insert(s1,x,len,s2) // 返回字符串s1,子字符串起始于x位置,并且用len个字符长的字符串代替s2;任何一个参数为null,则返回null
substring(s,n,len) // 返回字符串s中从起始位置n处开始的len个字符,若n小于0,则从后向前
reverse('string') // 反转字符串
replace(s,s1,s2) // 使用字符串s2替换字符串s中的所有的字符串s1
日期与时间函数
select function();
now() sysdate() // 返回当前日期与时间值
curdate() current_date() // (字符串)返回当前日期 ‘yyyy-mm-dd'
curdate()+0 current_date()+0 // (数值)返回当前日期 ’yyyymmdd'
curtime() current_time() // (字符串) 返回当前时间 ‘HH:MM:SS'
curtime()+0 current_time()+0 // (数值) 返回当前时间’HHMMSS'
dayofweek() // 返回当前日期对应的周索引,dayofweek('2019-11-29') 星期五,返回 6 其中1表示周日,2表示周一...
weekday() // 返回当前日期对应的工作日索引,weekday('2019-11-29') 返回4, 其中0表示周一,1表示周二...
dayofmonth() // 返回日期是一个月中的第几天, dayofmonth('2019-11-29') 返回29
dayofyear() // 返回当前日期是一年中的第几天
month(date) // date : '2019-11-29' 返回当前月份
monthname(date) // 返回当前date对应月份的英文全名
datediff(date1,date2) // 返回起始时间date1到结束时间date2之间的天数(date1-date2)
adddate(date,interval time) // 执行日期的加操作,adddate('2019-11-29 23:59:59',interval 1 second) 2019-11-30 00:00:00 // adddate('2019-11-29 23:59:59',interval '1:1' minute_second) 2019-11-30 00:01:00
聚合函数
mysql> select * from student_score;
±--------------------±--------------------+
| student_name | student_score |
±--------------------±--------------------+
| Dany | 90 |
| Green | 99 |
| Henry | 95 |
| Jane | 93 |
| Jim | 89 |
| John | 60 |
| Lily | 89 |
| Susan | 88 |
| Alan | 100 |
| Zachary | 101 |
max() // select max(student_score) as max_score from student_score;
min() // select min(student_score) as min_score from student_score;
count() // select count(student_name) as students_number from student_score;
sum() // select sum(student_score) as score_sum from student_score;
avg() // select avg(student_score) as score_avg from student_score;
5.基本查询语句
基础查询语句
select * from tb_name;
select one_column,two_column,three_column from tb_name;
select choose_column from tb_name;
distince : select disctinct one_column from tb_name; // 将tb_name表中one_column中的部分消除重复记录
select zach.ont_column from tb_name as zach;
select one_column as one, two_column as two from tb_name;
select * from tb_name limit n; // 只显示前n行的查询结果
select * from tb_name limit n1,n2; // 从第n1条记录开始,显示n2条查询结果
select * from tb_name order by one_column (default: asc ; desc:逆序); // 根据one_column中的数据排序显示所有的查询结果
select * from tb_name order by one_column , two_column;
条件查询 Where
select one_column,two_column from tb_name where choose_column<conditon>;
select * from tb_name where choose_column1<conditon> and choose_column2<condition>;
-----------------------------------------------------------
select one_column from tb_name where name like 'T%'; ’_z%' 表示第二个字符为z的任意字符串.
// like:模糊查询 %:匹配任何字符串任意次数; _ : 匹配任何单个字符一次
-----------------------------------------------------------
select * from tb_name where login_time<'date_time'>;
select * from tb_name where login_time between 'date_time1' and 'date_time2';
6.高级查询
多表查询
select * from tb_name;
==内连接查询==
select id , name from tb_zach , tb_alan where tb_zach.deptId=tb_alan.deptId;
select id,name from tb_zach inner join tb_alan where tb_zach.deptId=tb_alan.deptId;
==外连接查询==
select id,name from tb_zach z left outer join tb_alan a on z.deptId=a.deptId; //左外连接,查询结果中除去匹配的之外,还包括左表中有但右表中不匹配的行,则此时设定为null
select id,name from tb_zach z right outer join tb_alan a on z.deptId=a.deptId; //右外连接
子查询
select name from tb_zach where deptId {not} in (select deptId from tb_alan where dept_name='zach'); //在tb_alan表中查询dept_name为zach的deptId,并在表tb_zach中查询出该deptId对应的名字.
select name from tb_zach where deptId=(select deptId from tb_alan where dept_name= / {<>} 'zach');
select * from tb_zach where exists (select dept_name from tb_alan where deptId=521); //在表tb_alan中查询deptId=521的dept_name,如果存在,则查询该dept_name在表tb_zach所匹配的记录.
分组查询
select deptId ,count(*) as total from tb_zach group by deptId; // 根据deptId对表tb_zach中的数据进行分组统计.
select deptId,group_concat(name) as total from tb_zach group by deptId; //在表tb_zach,统计每一个deptId所对应的所有name.
select deptId,group_concat(name) as total from tb_zach group by deptId having count(name)>1; //在表tb_zach,统计每一个deptId所对应的所有name,并只筛选出名字数量为2或者2个以上的结果,
正则表达式查询
==以特定字符(串)开头的记录==
select * from tb_zach where dept_name regexp '^z'; // 查询dept_name以z开头的结果
select * from tb_zach where dept_name regexp '^zach';
==以特定字符(串)结尾的记录==
select * from tb_zach where dept_name regexp 'z$'; // 查询dept_name以z结尾的结果
select * from tb_zach where dept_name regexp 'zach$';
=="."代替字符串中的任意一个字符==
select * from tb_zach where dept_name regexp 'z..h'; //查询以z开头,h结尾的四个字符的字符串
==“*"代替字符串中前面的字符多次==
select * from tb_zach where dept_name regexp '^za*'; // zoo 符合条件 ,,,zach 同样符合
=="+"代替字符串中前面的字符至少一次==
select * from tb_zach where dept_name regexp '^za+'; //zoo 不符合条件 ,,,zach符合条件
==匹配指定字符串==
select * from tb_zach where dept_name regexp 'string'; //查询dept_name字段值包含字符串‘string’的记录
select * from tb_zach where dept_name regexp 'zach | alan'; //查询dept_name字段值包含字符串‘zach'或者'alan’的记录
==匹配指定字符串中的任意一个==
select * from tb_zach where dept_name regexp '[abcd]'; //查询dept_name字段值包含字母‘a','b','c'或者'd'的记录
select * from tb_zach where dept_name regexp '[1234]' / '[1-4]' ;
==匹配指定字符以外的字符==
select * from tb_zach where dept_name regexp '[^a-t]'; //查询dept_name字段值包含a~t以外的字符 zach 符合,z不属于a~t
操作表中的数据
create table tb_zach
(
id int not null auto_increment,
name varchar(40) not null,
age float not null,
info char(100) null,
primary key(id)
);
±---------------------------------+
create table tb_alan
(
id int not null auto_increment,
name varchar(40) not null,
age float not null,
info char(100) null,
primary key(id)
);
添加数据 insert
// 可同时添加多条记录
insert into tb_zach(id,name,age,info) values(1,'zach',18,'computer') , (...) . ... ;
insert into tb_zach(name,info,id,age) values('zach','math',2,20);
insert into tb_zach values(3,'zach',22,'system');
--------------------------------------------------------------------
insert into tb_zach(name,age,info) values('zach',23,'chinese');
--------------------------------------------------------------------
insert into tb_alan(id,name,age,info) select (id,name,age,info) from tb_zach; //将表tb_zach中的数据全部复制到表tb_alan
修改数据 update
update tb_zach set age=10; //将表tb_zach中的age全部改为10
update tb_zach set name='alan',age=11 where id=4;
删除数据 delete
delete from tb_zach; // 删除表中的全部数据
delete from tb_zach where id=5; //根据条件删除数据
#### 还可用 truncate table tb_zach; ####
视图
创建视图
==创建基于单表的视图==
create view view_tb_zach as select * from tb_zach;
create view v_tb_zach(v_column,b_column,a_column) as select v_column_zach,b_column_zach,a_column_zach from tb_zach;
create view v_tb_zach as select * from tb_zach where v_column > 50; //条件创建
==创建基于多表的视图==
create view v_tb_zach(id,age,add) as select z.id,z.age,a.add from tb_zach z,tb_alan a where z.id=a.id;
==查询视图==
desc view_name;
修改视图
修改视图内容,同时会改变基本表中相对应的内容。
update v_tb_zach set age=18,name='zach' where id=100;
删除视图
drop view if exists v_tb_zach;
自定义函数和存储过程
自定义函数
==创建自定义函数==
create function getname() //创建一个从表tb_zach中获取id号码为5的名字
returns varchar(20) // varchar(20) 为name的数据类型,应与表中name_column类型相同,可通过show create table tb_zach; 查看
return
(select name from tb_zach where id=5);
select getname() ; // 获取name
==删除自定义函数==
drop function getname; //注意这里只是函数名,不需要()
结构控制语句
存储过程
==创建不带参数的存储过程==
delimiter // //将语句结束符更改为//
create procedure getscore()
begin
select * from tb_zach_score;
end //
------运行------
delimiter ;
call getscore();
==创建代参数的存储过程==
delimiter //
create procedure getscorebyname
(in name varchar(20))
begin
select score from tb_zach_score
where student_name = name;
end //
-------运行-------
dilimiter ;
call getscorebyname('zach'); //获取zach的分数
==删除存储过程==
drop procedure getscorebyname;
触发器
创建触发器
==创建before类触发器==
create trigger tri_name
before insert|update|delete on tb_zach
for each row
set @sum = @sum+new.salary; //在表tb_zach中,当insert插入数据时,对新插入的salary_column 求和
------运行--------
@sum=0
insert into tb_zach values(..............);
select @sum; //显示求和后的salary sum
==创建after类触发器==
create trigger tri_name
after insert on tb_zach
for each row
insert into tb_alan
values (......new.column,.........3*new.column_one); //当给表tb_zach插入数据时,同时也会向tb_alan中插入数据,且column_one插入的值是tb_zach中相对于的3倍
删除触发器
drop trigger tri_name;
drop trigger if exists tri_name;
索引
B-树索引
哈希索引
创建索引
==创建一般索引==
create table tb_zach
(
id int not null,
name char(45) default null,
dept_id int default null,
age int default null,
height int default null,
index(height) //在创建表tb_zach时创建为height创建索引
);
==创建唯一索引==
create table tb_alan
(
id int not null,
name char(45) default null,
dept_id int default null,
age int default null,
height int default null,
unique index(height) //创建唯一索引,避免数据出现重复
);
==添加索引==
alter table tb_zach add unique index i_zach(height,desc); //以降序排列方式增加唯一索引i_zach
create index zuhe_zach on tb_zach(id,height); //创建组合索引
==查看索引==
show index from tb_zach from database_zach;
删除索引
alter table tb_zach drop index i_zach; //删除表tb_zach中的索引i_zach
drop index height on tb_zach;
alter table tb_zach drop index height;