MYSQL基本知识

MYSQL基本知识

查看表结构

describe(desc)表名;

show create table 表名;

修改表

alter table 旧表名 rename 新表名;  //修改表名

alert table 表名 change 旧属性 新属性名 行数据类型;  //修改字段

alert table 表名 add 属性名 数据类型 [完整性约束] [first | after 属性2];  //增加字段

alert table 表名 drop 属性名字; //删除字段

删除表

DROP table 表名;

查询

select * from t_student where age>22;

select * from t_student where age (not)in (21,30);

select * from t_student where age (not)between 20 and 24;

select * from t_student where studName like '张三';

select * from t_student where studName like '张三%';

select * from t_student where studName like '张三_';

%代表任意字符,_代表单个字符

select * from t_student where studName IS(NOT) NULL;

select * from t_student where age>22 and name='张三';

select * from t_student where age>22 or name='张三';
去除重复项
select DISTINCT gardeName from t_student;
排序
select * from t_student ORDER BY age ASC; //升序

select * from t_student ORDER BY age DESC; //降序
分组查询

和GROUP_CONCAT()函数使用

select gradeName,GROUP_CONCAT(stuName) from t_student GROUP BY gradeName; 

与聚合函数一起使用

select gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

与HAVING语句一起使用

select gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

WITH ROLLUP(总计)

select gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3 WITH ROLLUP
分页查询
select * from t_student LIMIT 0,5; //页只显示前5条

select * from t_student LIMIT 5,5; //页显示后5条

select * from t_student LIMIT 10,5;

使用聚合函数查询

统计函数
select COUNT(*) as total from t_grade;
select stuName,COUNT(*) from t_grade GROUP BY stuName;
求和函数
select stuName,SUM(score) from t_grade where stuName="张三";

select stuName,SUM(score) from t_grade GROUP BY stuName;
求平均值
select stuName,AVG(score) FROM t_grade GROUP BY stuName;
求最大值
select stuName,couse,MAX(score) FROM t_grade where stuName='张三';

select stuName,MAX(score) FROM t_grade GROUP BY  stuName;
求最小值
select stuName,couse,MIN(score) FROM t_grade where stuName='张三';

select stuName,MIN(score) FROM t_grade GROUP BY  stuName;

连接查询

内连接查询
select * from t_book,t_bookType where t_book.bookTypeId=t_bookType.id ;
使用别名
select tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby where tb.bookTypeId=tby.id;
left join(左联接):

返回包括左表中的所有记录和右表中联结字段相等的记录

select * from t_book LEFT JOIN  t_bookType ON t_book. bookTypeId=t_bookType.id;

right join(右联接): 返回包括右表中的所有记录和左表中联结字段相等的记录。

select * from t_book RIGHT JOIN t_bookType ON t_book. bookTypeId=t_bookType.id;

子查询

in 语句
select * from t_book where booktypeId in (select id from t_booktype);

select * from t_book where booktypeId in (select id from t_booktype);
>= (后面子查询是一个数据)
select * from t_book where price >=(select pricelevel where priceLevel=1)
exists语句
select * from t_book where EXISTS (select * from t_booktype);

select * from t_book where NOT EXISTS (select * from t_booktype);
ANY ALL(后面的子查询是一个集合)
select * FROM t_book where price >=ANY (select price from t_pricelevel) ;//符合一个就刷选

select * FROM t_book where price >=ANY (select price from t_pricelevel) ;//符合全部才刷选

合并查询

select id from t_book UNION select id from t_booktype;
select id from t_book ALL UNION select id from t_booktype; //不去掉重复的

插入,修改,删除数据

插入所有字段数据
insert into t_book values(NULL,"sadas",20,1);
插入指定的数据
insert into t_book(bookname,author) VALUES('我爱我家','张三')
插入多条数据
insert into t_book values(NULL,"sadas",20,1),(NULL,"sadas",20,1);
更新语句
UPDATE t_book set bookName='java编程',price=120 where id=1;
更新多条
update t_book set bookname='我' where bookName LIKE '%我是%';
删除数据
delete from t_book where id=5;

索引

创建表时创建索引

普通的单列索引
create table t_user1(id int,userName varchar(20),
password varchar(20),
index (userName)
);
唯一的单列索引
create table t_user1(id int,userName varchar(20),
password varchar(20),
unique index index_userName(userName)
);
普通的多列索引
create table t_user1(id int,userName varchar(20),
password varchar(20),
index index_userName_password(userName,password)
);

创建好表后创建索引

create INDEX index_userName ON t_user4(userName);
create UNIQUE INDEX index_userName ON t_user4(userName);
create INDEX index_userName_password ON t_user(username,password);

ALTER table t_user5 ADD INDEX index_userName(userName);
ALTER table t_user5 ADD UNQIE INDEX index_userName(userName);   
ALTER table t_user5 ADD INDEX index_userName(userName,password);    

删除索引

DROP INDEX 索引名 on 表名;

视图

创建单表视图
create VIEW v1 AS select * from t_book

create VIEW v3(b,p) as select bookname,price from t_book
创建多表视图
create VIEW v5 as select tb.bookName,tby.booktypeName from t_book tb,t_booktype tby where tb.bookTypeId=tby.id
查看视图
desc V5

show table status like 'v5';

show table status like 't_book';

show create view v5 
修改视图
select * from v1;
CREATE OR REPLACE VIEW v1(bookName,price)AS select bookName,price from t_book;
alter view v1 as select * from t_book;
视图的更新
INSERT INTO v1 values(null,'java good',120,'feng',1);

UPDATE v1 set bookName='java very good',price=200 where id=5;

delete from v1 where id=5;
删除视图
drop view if extsts v4;

触发器

创建单语句触发器
create trigger trig_book after insert
    on t_book for each row
        update t_bookType set bookName=bookNum+1 where new.bookTypeId=t_booktype.id;

insert into t_book values(NULL,'java好',100,'ka',1);
创建多语句触发器
DELIMITER |
create trigger trig_book2 after delete
    on t_book for each row
    begin 
        update t_bookType set bookNum=bookNum=1 where old.bookTypeId=t_booktype.id;
        insert into t_log values(Null,Now(),'在book表删除一个数据');
        delete from t_test where old.bookTypeId=t_test.id;
    end
|
DELIMITER;

delete from t_book where id=5;
查看触发器
show triggers;
删除触发器
drop trigger trig_book;

加密函数

不可解密(就是获取数据库也不能知道密码)
INSERT INTO test VALUES(NULL,'xiaozhu',PASSWORD('1314520'))

INSERT INTO test VALUES(NULL,'xiaozhu',MD5('1314520'))
可解密(可以恢复成原来用户设置的密码)
INSERT INTO test VALUES(NULL,'xiaozhu2',ENCODE('1314520','key'))

SELECT DECODE(PASSWORD,'key') FROM test WHERE id=9;

存储过程

变量

declare a,b varchar(20);//声明
set a='aavvva';//赋值
select userName,password into a,b from t_user where id=1; //赋值,值在表中拿出来

例子

DELTMITER &&
create procedure pro_book (IN bt int,OUT count_num INT)
READS SQL DATA 
BEGIN
    SELECT conut(*) from t_book where bookTypeId=bt;
END
&&
DELTMITER;

CALL pro_book(1,@tatal);//调用

if语句

DELTMITER &&
create procedure pro_user5 (IN bookId INT)
    begin
        select count(*) into @num from t_user2 where id2=bookId;
        IF @num>0 then update set userName='java12345'
            where id=bookId;
        else
            insert into t_user values(null,'231222','122132');
        end if;
    end
&&
DELTMITER;

call pro_user5 (5);//调用

case语句

DELTMITER &&
create procedure pro_user6 (IN bookId INT)
begin
    select count(*) into @num from t_user2 where id2=bookId;    
    case @num   
    WHEN 1 THEN update t_user set userName='java12345' where id=bookId;
    WHEN 2 THEN insert into t_user values(null,'2222','2222');
    ELSE insert into t_user values(null,'222','1111');
    END CASE;
end
&&
DELTMITER;

call pro_user6 (2);//调用

loop语句

DELTMITER &&
create procedure pro_user7 (IN totalNum INT)
begin
    aaa:LOOP
        set totalNum=totalNum-1;
        if totalNum=0 THEN LEAVE aaa;
        else insert into t_user values (totalNum,'12354','255654');
        end if;
    END 
&&
DELTMITER;

call pro_user6 (5);//调用

ITERATE

跳出循环,继续下一个循环,相当于continue
ITERATE aaa

refeat

DELTMITER &&    
create procedure pro_user8 (IN totalNum INT)
    begin
        refeat
            set totalNum=total-1;
            insert into t_user values(totalNum,'1323','22555');
        end refeat
    end
&&
DELTMITER;

call pro_user8 (5); //调用 

while语句

DELTMITER &&    
create procedure pro_user10(IN totalNum INT)
    begin
        WHILE totalNum>0 DO
            insert into t_user values(totalNum,'223565','225465');
            set totalNum=totalNum-1;
        end while;
    end
&&
DELTMITER;

call pro_user10(11) //调用

存储函数

DELTMITER &&
create function func_book(bookId int)
reture varchar(20)
BEGIN
    RETURE(SELECT bookName from t_book where id=bookId);
END
DELTMITER;

select func_book(1);//函数调用

查看存储过程和存储函数

查看状态

show procedure status like 'pro_book';

查看定义

show create procedure pro_book;

修改存储过程

alter procedure pro_book comment '我来测试一个comment';

删除存储过程

drop procedure pro_user10;

数据得备份和还原

数据的备份

bin目录下用cmd打开mysqldump
mysqldump -u root -p db-book > d:\db_book.sql

数据还原

发表评论

电子邮件地址不会被公开。 必填项已用*标注