MySQL入门篇 -- 对数据库增删改查的基本操作

attachment/2018/01/12/85301515756105.jpg

一 对数据库与表的操作

数据库的查看创建使用与删除

-- cmd连接MySQL 
mysql -uroot -proot 
-- 创建库并设置字符集为UTF-8 
create database blog charset utf8; 
-- 查看所有数据库 
show databases; 
-- 使用库（必须使用才能够操作数据库中的表） 
use blog; 
-- 删除数据库 
drop database blog; 

创建数据表设计表结构修改表结构等相关操作

-- 创建一个名为student的表(必须先使用数据库 use database） 
create table student( 
  id tinyint unsigned primary key auto_increment, 
  name char(20) not null default '', 
  sex enum('boy','girl') not null default 'boy', 
  birthday date not null default '2018/1/1' 
); 
 
-- 创建一张stuBak表 克隆student表的表结构 
create table stuBak like student; 
 
-- 克隆student表中的数据至stuBack表 
insert into stuBak select * from student; 
 
-- 创建一张stuBackups表 并克隆student表的表结构与表中的数据 
create table stuBackups select * from student; 
 
-- 查看student表 
desc student; 
 
-- 将student表名更改为 newStudent 
alter table student rename newStudent; 
 
-- 给student表追加一个hobby字段 
alter table student add hobby set('basketball','football','volleyball') not null default 'volleyball'; 
 
-- 将student表的hobby字段内删除 
alter table student drop hobby; 
 
-- 修改student表的name字段的长度为32 
alter table student modify name char(32); 
 
-- 将student表id字段的主键属性删除（必须删除自增属性,也就是先修改属性,才能删除主键属性） 
alter table student modify id tinynt unsigned; 
alter table student drop primary key(id); 
 
-- 将student表的name字段名字更换为username,并且修改长度为99 
alter table student change name username char(99) not null default ''; 

二 查询操作

select_from_ 查询

-- 通过 * 可以查得student表中所有的数据，但你不能指定字段顺序，同时查询效率降会低。 
-- 所以建议写具体字段，PHP结果集可以返回关联数组，可以解决顺序问题，但从效率考虑也不建议用＊ 
select * from student; 

as 起别名

-- 查询出student表name字段的数据，并用username显示 
-- 给name字段起一个别名为username , 需要注意的是:不能在where语句中使用as 
select name as 'username' from student; 

concat 连接字段

-- 查询出id与name字段数据，将2个字段的数据合并为一列数据（即直接拼接2段字符串） 
select concat(id,name) from student; 
 
-- 查询出id与name字段数据，将2个字段的数据合并为一列数据 并且为这列字段起一个别名为merge 
select concat(id,name) as 'merge' from student; 

distinct 去重，对指定的字段有重复值的行合并为一行

1 2	`-- 将student表id与name字段里重复的行合并为一个行数据即去重` `select` `distinct` `name,id` `from` `student;`

< > = != >= <= and or 比较符号

-- 查询student表 年龄大于20 的学生姓名  age(年龄字段） name(姓名字段） 
select name from student where age > 20; 
 
-- 查询student表 年龄小于20 的学生姓名 
select name from student where age < 20; 
 
-- 查询student表 年龄为20 的学生姓名 
select name from student where age = 20; 
 
-- 查询student表 年龄不等于20 的学生姓名 
select name from student where age != 20; 
 
-- 查询student表 年龄大于等于20 的学生姓名 
select name from student where age >= 20; 
 
-- 查询student表 年龄小于等于20 的学生姓名 
select name from student where age <= 20; 
 
-- 查询student表 年龄大于等于20 并且 小于30岁 的学生姓名 
select name from student where age >= 20 and age < 30; 
 
-- 查询student表 年龄小于等于20 或者 大于30岁 的学生姓名 
select name from student where age <= 20 or age > 30; 
 
-- 查询student表age字段每行的值与20比较 大于20返回1 小于20则返回0 
select age>20 as bigage from stu; 

is null 与 is not null 判断是否为null

-- 查询student表age字段为null的数据 
select * from student where age is null; 
 
-- 查询student表age字段为null的数据 
select * from student where age is not null; 

if 与 ifnull 判断表达式是否成立

-- if(表达式，值1，值2）    当表达式成立时返回值1，否则返回值2 
-- 查询出每个学生的年龄是大于41还是小于41 
select name,if(age > 41,'大于41','小于41') from student; 
 
-- ifnull(字段，值）    如果表达式成立则返回值 
-- 查询出age字段为空的值，并将其显示为空 
select name,ifnull(age,'空') from student; 

orderby , limit 与 rand()函数排序、截取与随机数

-- orderby    排序 desc(降序) asc(升序) 
-- 对每个学生按照年龄进行排序，按照降序进行排列 
select name from student order by age desc; 
 
-- 对每个学生按照年龄进行排序，按照升序进行排列 不指定asc默认就是升序 
select name from student order by age asc; 
 
-- limit    limit n(n截取行数) limit m,n(从第m行开始截取，截取n行) 
-- 查询出年龄最小的同学的名字(升序排列截取第一行) 
select name from student order by age limit 1; 
 
-- 查询出年龄第二大的同学的名字(降序排列截取第二行） 
select name from student order by age desc limit 1,1 
 
-- rand 产生0-1之间的随机数    利用rand()随机选中6位同学 
select name from student order by rand(); 

between , not between 与 in , not in 查询符合条件的数据

-- between m and n    查询在m与n之间的值，包含m,n 
-- 查询出年龄在20 与 22 岁之间的学生 
select name from student where age between 20 and 22; 
 
-- 查询出年龄不在20 与 22 岁之间的学生 
select name from student where age not between 20 and 22; 
 
-- 查询出年龄不在20 与 22 岁之间的学生 
select name from student where age not between 20 and 22; 
 
-- in(a,b,c,d)    查询数据是否在in表达式里指定的几个值中 
-- 查询出年龄为 20 或 21 或 25 或 29 岁的学生 
select name from student where age in(20,21,25,29); 
 
-- 查询出年龄不为 20 , 21 , 25 , 29 岁的学生 
select name from student where age not in(20,21,25,29); 

like , not like like匹配字符串 not like反之 %为任意数量字符 _表示单个字符

-- '%name'匹配以name结尾的  'name%'匹配以name开头的  '%name%'匹配包含name的 
-- 查询出姓李的学生 
select name from student where name like '李%'; 
 
-- 查询出以聪结尾的学生 
select name from student where name like '%聪'; 
 
-- 查询出姓李，并且名只有一个字的同学 
select name from student where name like '%李_'; 
 
-- 查询出名字中含有聪字的学生 
select name from student where name like '%聪%'; 
 
-- 查询出名字中没有聪字的学生 
select name from student where name not like '%聪%'; 

mid , left , right 截取字符串

-- 将name字段的数据从左边第1位字符开始截取，截取2位 
select left(name,2) from student; 
-- 将name字段的数据从左边第3位字符开始截取，截取2位 
select mid(name,3,2) from student; 
-- 将name字段的数据从右边第1位字符开始截取，截取2位 
select right(name,2) from student; 

三 操作记录

插入记录

/** 
    insert into tableName set name = 'value'; 
    [insert,replace] into tableName (name,name2,name3) values (value1,value2,value3)),(),()... 
    字符串需要使用引号包裹起来，字段与值需要一一对应，可以使用default关键词表示默认值 
 */ 
-- 插入单条数据 
insert into student set name = 'Leo',sex = 'boy',age = 20; 
 
-- 插入多条数据 
insert into student (name,sex,age) values ('Leo','boy',20),('Alice','girl',18),('Bob','boy',17); 
 
-- replace根据主键判断，如果设置的主键已存在，则修改该条数据，若不存在，则为添加数据 
replace into student (id,name,sex) values (2,'Alice','girl'); 

更新记录

/** 
    update tableName set name = value where ... 
    where条件不是必须的，若不设置则直接更新表中所有的数据 
 */ 
--修改表中 id为 1 的记录 
update student set name = 'Leo', sex = 'boy', age = 20 where id = 1 
 
--修改表中全部的数据 
update student set name = 'Leo', sex = 'boy', age = 20; 

删除记录集

/** 
    delete from tableName where ... 
    where条件不是必须的，若不设置则删除表中所有的数据 
 */ 
--修改表中 id为 1 的记录 
delete from student where id = 1; 
 
--删除表中全部的数据 
delete from student; 