MySQL语法使用查询

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;

用户及权限

事务及数据库备份恢复