一、Mysql之表操作:
一、数据库 //1.创建数据库 create database [if not exists] db_name [character set xxx]; 二、表 //1.创建表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, name varchar(25), gender boolean, age int(11), depart varchar(20), salary double(7,2) ); //2.查看表信息 desc tab_name 查看表结构 show tables 查看当前数据库中的所有的表 show create table tab_name 查看当前数据库表建表语句 //3.修改表结构 (1)增加一列 alter table tab_name add [column] 列名 类型; (2)修改一列类型 alter table tab_name modify 列名 类型; (3)修改列名 alter table tab_name change [column] 列名 新列名 类型; (4)删除一列 alter table tab_name drop [column] 列名; (5)修改表名 rename table 表名 to 新表名; (6)修该表所用的字符集 alter table student character set utf8; 三、表记录 //1.增加一条记录insert INSERT INTO emp (id,name,gender,age,depart,salary) values (1,"alex",0,18,"技术部",8000); INSERT INTO emp values("ray",0,18,"技术部",8000); INSERT INTO emp set name="张三",salary=3000; //2.修改表记录 UPDATE emp SET salary=salary+1000,depart="经理" where name="ray"; //3.删除表操作 DELETE FROM emp where name="张三"; //删除表中所有记录,清空记录的操作。 delete from emp; //使用truncate删除表,是把整个表摧毁,然后再创建一个新表(这种用法在于数据很多的时候,不需要一条一条删除)。 truncate table emp; //4.查找 CREATE TABLE ExamResult( id INT PRIMARY KEY auto_increment, name VARCHAR(20), JS DOUBLE, Django DOUBLE, Flask DOUBLE ); //查询表中所有学生的姓名和对应的英语成绩。 select name,JS from ExamResult; //过滤表中重复数据。 select distinct name from ExamResult; //在所有学生分数上加10分特长分显示。(数据库不增加) select name,JS+10,Django+10,Flask+10 from ExamResult; //取别名查。 select name as "姓名" from ExamResult; //过滤查询where字句中可以使用: //比较运算符: > < >= <= <> between 10 and 20 值在10到20之间 in(10,20,3)值是10或20或30 like '张pattern' pattern可以是%或者_, 如果是%则表示任意多字符,张* //查找缺考JS的学生的姓名 select name from ExamResult where JS is null; //5.Order by排序,排序的列即可是表中的列名,也可以是select 语句后指定的别名。 // Asc 升序、Desc 降序,其中asc为默认值 select * from ExamResult order by JS asc; //按总成绩排 select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0)) as "总成绩" from ExamResult order by "总成绩"; //6.聚合函数count(列名)sum(列名)AVG(列名)Max(列名)Min(列名) //查找多少学生 select count(*) from ExamResult; //统计数学成绩大于70的学生有多少个? select count(JS) from ExamResult where JS>70; //统计一个班级JS成绩平均分 select sum(JS)/count(*) from ExamResult; //求一个班级JS平均分?先查出所有的JS分,然后用avg包上。 select avg(ifnull(JS,0)) from ExamResult; //求班级最高分和最低分(数值范围在统计中特别有用) select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0))) from ExamResult; select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0))) from ExamResult; null加上什么数都为null,(ifnull(JS,0))的意思是如果是null转化为0 7.group by分组,where语句只能用在分组之前的筛选,having可以用在分组之后的筛选; //按名字分组,查每组JS的和 select name,sum(JS) from examresult group by name; //按名字分组,查每组JS的总和大于100的 select name,sum(JS) from examresult group by name having sum(JS) > 100; 8.限制查询记录 select * from examresult limit 2; select * from examresult limit 1,3; (查询索引1到3) 9.正则 select * from examresult where name regexp '^al'; select * from examresult where name regexp 'lv$';
二、多表查询之外键查询
create table lesson.classcharger( id int primary key auto_increment, name varchar(20), age int, is_marriged tinyint ); create table student1( id int primary key auto_increment, name varchar(20), charger_id int, foreign key (charger_id) references classcharger(id) )ENGINE=INNODB; 外键和主键的类型要一致。 insert into classcharger (name,age,is_marriged) values ('张三',28,0), ('李四',30,0),('小红',32,0),('小明',40,0); insert into student1 (name,charger_id) values ('ray1',2), ('ray2',4),('ray3',1),('ray4',3),('ray5',1),('ray6',2); //(1)增加外键: 可以明确指定外键的名称,如果不指定外键的名称,mysql会自动为你创建一个外键名称。 取别名 alter table student add constraint FK_st foreign key(charger_id) references classcharger(id); (2)删除外键: alter table student drop foreign key FK_st;(别名) //INNODB支持的ON语句,约束删除父表记录所带来子表的影响。 //on delete cascade子表的内容也跟着父表删除 alter table ss add constraint fk_cid_cc foreign key(charger_id) references cc(id) on delete cascade; //on delete set null子表的内容设置为null alter table ss add constraint fk_cid_cc foreign key(charger_id) references cc(id) on delete set null;
三、多表查询之连接查询
create table employee (id int primary key, name varchar(20), tb_id int); create table dpm (id int primary key,department varchar(20) ); insert into employee values (1,'刘备',1); insert into employee values (2,'关羽',2); insert into employee values (3,'张飞',3); insert into dpm values(1,'财务部'); insert into dpm values(2,'人事部'); insert into dpm values(3,'科技部'); 1.笛卡尔积查询:两张表中一条一条对应的记录,m条记录和n条记录查询,最后得到m*n条记录,其中很多错误数据 select * from employee ,dpm; 2.内连接 select employee.name,dpm.department from employee,dpm where employee.tb_id=dpm.id; select * from employee inner join dpm on employee.tb_id = dpm.id; 3.外连接:左外、右外 左外连接:显示左表全部信息 select employee.name,dpm.department from employee LEFT JOIN dpm on employee.tb_id=dpm.id; 右外连接:显示右表全部信息 select employee.name,dpm.department from employee RIGHT JOIN dpm on employee.tb_id=dpm.id;
四、多表查询之子查询
--子查询是将一个查询语句嵌套在另一个查询语句中。 --内层查询语句的查询结果可以为外层查询语句提供查询条件。 --子查询中包含:IN,NOT IN,ANY,ALL,EXISTS,NOT EXISTS --还有比较运算符:=,!=,>,<等 1.查询employee表,当id必须在部门表中出现过。 select * from employee where id IN (select id from department); 2.用EXISTS(返回true或false) select * from employee where exists (select id from department where id=2);
五、索引
--索引提高搜索时间,提高用户体验 --primary key和unique就是索引,唯一性。 --index|key普通索引可重复 1.普通索引index|key create table emp( id int primary key, name varchar(20), index index_name (name) -- key [索引名] (字段名[(长度)]) ) --添加索引 create INDEX 索引名 ON 表名 (字段名[(长度)]); create INDEX index_name on employee (name); alter table 表名 ADD INDEX 索引名 (字段名[(长度)]); alter table employee add INDEX index_name (name) --删除索引 drop index 索引名 on 表名 drop index index_name on employee; --模拟添加数据 create table t1_suoyin(id int,name varchar(20)); delimiter $ create procedure autoinsert() BEGIN declare i int default 1; while(i<8000)do insert into t1_suoyin values (i,'yuan'); set i=i+1; end while; END$ delimite ; --改回来 call autoinsert(); --执行