常见数据库对象
常用数据类型
DDL语句
一、常用查询
show databases;
show create database t1;
drop database dbName;
create database if not exists dbName;
alter database 库名 选项信息;
use dbName;
show tables；
desc tableName;
show engines;
二、表操作：
create table user (
uId int not null,
uPrice decimal,
uName varchar(255) default ‘zhangsan’,
uRemark text,
uPhoto blob,
uBirthday datetime default current_datetime,
constraint pk_temp_id primary key(name, pwd)
)
CHARSET = charset_name,ENGINE = engine_name;
create table userInfo (
name varchar(20),
sex char
)
as
select name, sex from user;
create table userInfo
as
select * from user;
CREATE TABLE 表名 LIKE 要复制的表名;
alter table user rename to users;
rename table contact to t1.contract;
drop table users;
truncate users;
show create table t1 ;
三、字段操作：
alter table user add tel varchar(11) default ‘02012345678’;
alter table user
add (
photo blob,
birthday date
);
alter table user drop photo;
alter table user
modify tel varchar(15) default ‘02087654321’ first,
modify name varchar(20) after tel;
alter table users change name u_name varchar(10);
四、约束：
1.null
create table temp(
id int not null,
name varchar(255) not null default ‘abc’,
sex char null
);
alter table temp modify sex varchar(2) not null;
2.unique
create table temp (
id int not null,
name varchar(25),
password varchar(16),
constraint uk_name_pwd unique(name, password)
);
alter table temp add unique(name, password);
alter table temp modify name varchar(25) unique;
alter table temp drop index name;
3.primary
create table temp(
id int auto_increment primary key,
name varchar(25)
);
create table temp2(
id int not null,
name varchar(25),
pwd varchar(15),
constraint pk_temp_id primary key(name, pwd)
);
alter table temp add primary key(name, pwd);
alter table temp modify id int primary key;
alter table temp drop primary key;
4.foreign key
create table student(
id int auto_increment,
name varchar(22),
constraint pk_id primary key(id),
classes_id int references classes(id)
);
create table student(
id int auto_increment primary key,
name varchar(25),
classes_id int,
foreign key(classes_id) references classes(id)
);
create table student(
id int auto_increment primary key,
name varchar(25),
classes_id int,
constraint fk_classes_id foreign key(classes_id) references classes(id) on delete(update) set null
);
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
);
alter table student add foreign key(classes_name, classes_number) references classes(name, number);
alter table student drop foreign key student_ibfk_1;
5.check无用，mysql不支持
create table temp(
id int auto_increment,
name varchar(20),
age int,
primary key(id),
check(age > 20)
);
6.index
create index idx_temp_name on temp(name);
create index idx_temp_name$pwd on temp(name, pwd);
drop index idx_temp_name on temp;
五、视图：
create or replace view view_temp
as
select name, age from temp
with check option;
alter view view_temp
as
select id, name from temp;
drop view view_temp;
show create view v_temp;
六、DML
insert into temp values(null, ‘jack’, 25);
insert into temp(name, age) values(‘jack’, 22);
insert into temp set id = 7, name = ‘jason’;
insert into temp(name) select name from classes;
insert into temp values(null, ‘jack’, 22), (null, ‘jackson’ 23);
update temp set name = ‘jack’, age = 22 where age > 22;
delete from temp where age > 20;
delete from temp order by age limit 1,25;
delete from temp ,temp1 using temp.id = temp1.id;
select [all|distinct] select_expr from -> where -> group by [合计函数] -> having -> order by -> limit
select * from temp;
select * from tb1, tb2;
select distinct name as ‘名称’, age+2 from temp where age = 22 order by id, age asc; limit 2,10;
select t.name Name from temp as t;
select * from t where a > 2 or a >= 3 or a < 5 or a <= 6 or a = 7 or a <> 0;
select * form temp where age between 20 and 25;
select * from temp where id in (1, 2, 3);
select * from temp where name like ‘j%’;
select * from temp where name like ‘\_%’ escape ‘\’;
select * from temp where name is null;
select * from temp where name is not null;
select * from temp where not (age > 20);
select * from temp where id not in(1, 2);
select * from wp_terms t right join wp_posts p on t.name =p.post_name;
需要与group by 结合的函数：count sum max min avg
having与where的区别：
与 where 功能、用法相同，执行时机不同。
where 在开始时执行检测数据，对原数据进行过滤。
having 对筛选出的结果再次进行过滤。
having 字段必须是查询出来的，where 字段必须是数据表存在的。
where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
where 不可以使用合计函数。一般需用合计函数才会用 having
SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 重置auto_increment的值。而delete不会
3，truncate 不知道删除了几条，而delete知道。
4，当被用于带分区的表时，truncate 会保留分区。
七、管理
net start mysql;
mysql -h 地址 -P 端口 -u 用户名 -p 密码
mysqld –skip-grant-tables
update mysql.user set password=password(‘root’);
show PROCESSLIST;
show VARIABLES;
show CHARACTER SET;
show COLLATION;
SET NAMES GBK;
FLUSH PRIVILEGES;
create user ‘bkd’@’%’ identified by ‘123’;
RENAME user ‘bkd’ TO ‘wei’;
SET PASSWORD FOR ‘bkd’ = PASSWORD(‘123′)’
drop user ‘bkd’;
grant all ON t1.wp_posts to bkd;
show grants FOR ‘bkd’;
REVOKE ALL PRIVILEGES, grant OPTION FROM ‘bkd’;
check table contract;
repair table contract;
analyze table contract;
optimize table contract;
八、备份与还原
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > D:/a.sql;
mysqldump -u用户名 -p密码 库名 -d 表1 表2 表3 > D:/a.sql; //导结构
mysqldump -u用户名 -p密码 -B 库名 > D:/a.sql;
source a.sql;
mysql -u用户名 -p密码 库名 < a.sql;
九、事务
特性：原子性、一致性、隔离性、持久性
start transaction;
set autocommit = 0|1;
数据定义语言（DDL）语句不能被回滚，事务不能被嵌套。
lock table wp_posts;
unlock TABLES;
十、触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt;
create trigger insertPostLog after insert on post for each row
begin
insert into postlog values(post.author,post.time);
end
drop TRIGGER [schema_name.]trigger_name
十一、编程
if 条件 then
执行语句
elseif 条件 then
执行语句
else
执行语句
end if;
begin
语句块
end
case value
when value1
then result1
when value2
then result2
end
declare @var1 int default 23;
select @v1:=id, @v2=name from t1 limit 1;
— 数值函数
abs(x) — 绝对值 abs(-10.9) = 10
format(x, d) — 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) — 向上取整 ceil(10.1) = 11
floor(x) — 向下取整 floor (10.1) = 10
round(x) — 四舍五入去整
mod(m, n) — m%n m mod n 求余 10%3=1
pi() — 获得圆周率
pow(m, n) — m^n
sqrt(x) — 算术平方根
rand() — 随机数
truncate(x, d) — 截取d位小数
— 时间日期函数
now(), current_timestamp(); — 当前日期时间
current_date(); — 当前日期
current_time(); — 当前时间
date(‘yyyy-mm-dd hh:ii:ss’); — 获取日期部分
time(‘yyyy-mm-dd hh:ii:ss’); — 获取时间部分
date_format(‘yyyy-mm-dd hh:ii:ss’, ‘%d %y %a %d %m %b %j’); — 格式化时间
unix_timestamp(); — 获得unix时间戳
from_unixtime(); — 从时间戳获得时间
— 字符串函数
length(string) — string长度，字节
char_length(string) — string的字符个数
substring(str, position [,length]) — 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) — 在str中用replace_str替换search_str
instr(string ,substring) — 返回substring首次在string中出现的位置
concat(string [,…]) — 连接字串
charset(str) — 返回字串字符集
lcase(string) — 转换成小写
left(string, length) — 从string2中的左边起取length个字符
load_file(file_name) — 从文件读取内容
locate(substring, string [,start_position]) — 同instr,但可指定开始位置
lpad(string, length, pad) — 重复用pad加在string开头,直到字串长度为length
ltrim(string) — 去除前端空格
repeat(string, count) — 重复count次
rpad(string, length, pad) –在str后用pad补充,直到长度为length
rtrim(string) — 去除后端空格
strcmp(string1 ,string2) — 逐字符比较两字串大小
— 流程函数
case when [condition] then result [when [condition] then result …] [else result] end 多分支
if(expr1,expr2,expr3) 双分支。
— 聚合函数
count()
sum();
max();
min();
avg();
group_concat()
— 其他常用函数
md5();
default();
单行注释 # 注释内容
多行注释 /* 注释内容 */
单行注释 — 注释内容
_ 任意单个字符
% 任意多个字符，甚至包括零字符
单引号需要进行转义 \’
CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
函数体
drop FUNCTION [IF EXISTS] function_name;
show CREATE FUNCTION function_name;
ALTER FUNCTION function_name 函数选项
CREATE PROCEDURE sp_name (参数列表)
begin
过程体
end
调用：CALL 过程名
drop procedure if exists query;
DELIMITER $$
create procedure query(IN t varchar(255))
begin
set @t = t;
— select * from @t
select * from mysql.user where host = @t;
end
$$
DELIMITER ;
CALL query(‘localhost’);