signed

QiShunwang

“诚信为本、客户至上”

Mysql的基本操作

2020/12/27 9:56:34   来源:

启动服务器:
net start mysql
停止服务器:
net stop mysql
连接服务器
mysql -uroot -p123
断开服务器:
exit quit \q
数据库:database
创建:create
创建 展示,表演:show
使用:use
删除:drop
建立数据库:
格式: create database 数据库名;
查看是否有这个数据库没有再建立

可选项,表示在创建数据库前进行判断,只有该数据库目前尚未存在时才执行创建语句
If not exits
Create database if not exits 数据库名;

数据库的编码(字符集)设置
格式:create database 数据库名 character set =utf-8;

查看所有的数据库:
格式:show databases;
使用数据库(选择该数据库为当前数据库):
格式:use 数据库名;
删除数据库:
格式:drop database 数据库名;
创建表
Create table 表名(字段1 字段类型, 字段2 字段类型…)
查看表结构
Desc 表名;
或者:show columns from 数据表名 [FROM 数据库名];

数据插入(
(所有字段数据输入)
格式:
insert into 表名(字段1,字段2,字段3)
values(值1,值2,值3);
增加字段(一个或多个)
(增加一个字段)格式:alter table 表名 add 新字段名 新字段类型;

(增加多个字段)格式:alter table 表名 add 新字段名1 新字段类型,add 新字段名2 新字 段类型;

删除字段(一个或多个)
(删除一个字段)格式:alter table 表名 drop 字段名;

(删除多个字段)格式:alter table 表名 drop 字段名1,drop 字段名2,…,drop 字段名n ;
修改字段类型
(修改一个字段)格式:alter table 表名 modify 字段名 字段类型;
(修改多个字段)格式:alter table 表名 modify 字段名1 字段类型,modify 字

修改字段名
(修改一个字段)格式:alter table 表名 change 原字段名 新字段名 字段类型;
(修改多个字段)格式:alter table 表名 change 原字段名1 新字段名1 字段类
查询所有字段
1、查询所有字段是指查询表中所有字段的数据。
2、在Mysql中使用"*"代表所有的字段。
3、语法格式:select * from 表名;

修改表记录
格式:
update 表名 set 字段=值 where 条件; ------修改一个字段值
update 表名 set 字段1=值1,字段2=值2… where 条件; ------修改多个字段值

删除记录(2种方法)

方法一:     格式:delete from 表名 where 条件;
方法二:     格式:truncate  表名where  条件;

SELECT * FROM用来查看表格的具体内容

修改表名(rename–重命名):
有2种方法:
方法一:
格式:alter table 原表名 rename to 新表名;
或者:alter table 原表名 rename as 新表名;
方法二:
格式:rename table 原表名 to 新表名;
查询指定字段
1、查询指定字段是指查询指定的部分字段。
2、语法格式:
select 字段名 from 表名;
3、如果查询多个字段,使用“,”对字段进行分隔
语法格式:
select 字段名1,字段名2,字段名3 from 表名;
去重查询(重复记录只查询一次)
1、使用关键字distinct可以去除查询结果中的重复记录
2、语法格式:
select distinct 字段名 from 表名;
数据表的删除命令(drop):
格式:drop table 表名;
列运算(掌握运算符)
1、列运算:列和列之间进行运算。
2、了解运算符:
1)算术运算符:+ - * /(div) %(mod)
2)比较运算符:> >= < <= = != <>
3)逻辑运算符:and(&&) or(||) not(!)
3、列运算实践:
1)数值类型的列可以做算术运算(+ - * / %)
例如:select 列1.5 from 表名;
select 列1+列2 from 表名;
select 列1
列2 from 表名;
2)字符串类型的列可以做连接运算 concat(列1,列2)或者concat(‘字符常量’,列))
例如:
select concat(列1,列2) from 表名;
select concat(’$’,列) from 表名;
3)数值型
转换NULL值 (把值是NULL的列转换成数值0计算)
ifnull(age,0)-----把age列中存在的NULL值当成0来计算。
字符型
ifnull(sname,‘无姓名’)-----把sname列中为NULL的转换为无姓名
4)给列起别名
例如:select 列1+列2 as 名字 from 表名;
注意:as 可以省略
算数运算符 (c语言会用到的)

  • 加号求和的
    —减号求差
    *乘号求积
    /除号求差
    %模求余数
    列如10%3=1
    10除3的差是3 余数为1所以答案唯1,
    奇数%2=1
    偶数%2=0
    例如查询id能被2整除的是?
    Select * from 表名 where id%2=0;
    查询id不能被二整除的是?
    Select * from 表名 where id%!=0;
    查询id能被2整除也能被3整除的信息?
    Select * from 表名 where id%2=0 and id%3=0;
    掌握集合查询(in)
    1、关键字in可以判断某个字段的值是否在指定的集合中。
    1)如果字段的值在集合中,则满足查询条件,该记录可以被查询出来。
    2)如果字段的值不在集合中,则不满足条件,该记录不能被查询出来。
    2、案例实践
    1)查询学号是1001,1002,1003的记录
    select * from 表名 where id in(1001,1002,1003);
    2)查询学号不是1001,1002,1003的记录
    select * from 表名 where id not in(1001,1002,1003);

掌握范围查询(between…and)
1、关键字between…and可以判断某个字段的值是否在指定的范围中。
1)如果字段的值在指定的范围中,则满足查询条件,该记录可以被查询出来。
2)如果字段的值不在指定的范围中,则不满足条件,该记录不能被查询出来。
2、案例实践
查询年龄在20到40之间的学生记录
select * from 表名 where age between 20 and 40;
或者:select * from 表名 where age>=20 and age<=40;

掌握空值及多重条件查询(is null,and,or)
1、空值查询(is null)
查询年龄为null的记录
select * from 表名 where age is null;
2、查询姓名不为null的学生记录
select * from 表名 where name is not null;
或者:select * from 表名 where not name is null;
3、带关键字and的多条件查询
1、查询年龄大于25岁的男讲师
select * from 表名 where age>25 and sex=‘男’;
2、查询出性别为男、年龄在30岁以下的精英学院的教师
select * from 表名 where sex='男’and age<30 and xueyuan=‘精英学院’;
4、带关键字or的多条件查询
查询出部门为大数据或者收入在5千以上教师姓名、性别、年龄
select name,sex,age from 表名 where bumen=‘大数据’ or money>5000

限制查询结果的数量----limit关键字
1、查询数据时,可能会查询出很多的记录,而用户需要的记录可能只是很少的一部分,这样就需要来限制查询结果的数量。
关键字limit可以对查询结果的记录条数进行限定,控制它输出的行数。
2、显示前3条记录 select * from 表名 limit 3;
3、使用关键字limit还可以查询结果的中间部分取值。
两个参数,参数1是开始读取的第一条记录的编号(在查询结果中,第一个结果的记录编号是0,而不是1);
参数2是要查询记录的个数。
例如:查询出第2条到第4条记录信息 select * from 表名 limit 1,3;

约束的作用
作用:完整性约束是为了表的数据的正确性,如果数据不正确,则数据不能添加到表中。
约束的分类
1、主键约束-----唯一标识
a:当某一列添加了主键约束后,那么这一列的数据就不能重复出现,也不能为空。
b:每一个表中只能定义一个主键。
c:指定主键约束的关键字primary key(普ruai mo kei)
d:主键约束的方式有3种:
1)创建表的列的同时指定主键 格式: 列名 列类型 primary key;
2)创建表的列之后独立指定主键 格式: primary key(列名);
3)修改表指定主键 格式:alter table 表名 add primary key(列名);
e:删除主键约束(注意:只是删除主键约束,不会删除主键列)
格式:alter table 表名 drop primary key;
2、主键自增长约束
a:主键必须是整型才可以自增长。
b:当主键设置为自动增长后,在没有给出主键值时,主键从1开始,每次自增1。
当主键设置为自动增长后,在给定主键值后,会在给定值的基础上每次自增1。
c:指定主键自增长约束的关键字是:auto_increment (ao tu in ke rui men te)
d:主键自增长约束的方式有2种:
1)创建表的列的同时指定主键自增长 格式:列名 类型 primary key auto_increment;
2)修改表时设置主键自增长 格式:alter table 表名 change 旧列名 新列名 类型 primary key auto_increment;
3)删除主键自增长约束:格式:alter table 表名 change 旧列名 新列名 类型;(只是加自增长约束,新列名和旧列名一样就行)
e:主键自增长设置初始值
alter table 表名 auto_increment=初始值;

3、非空约束
   a:指定非空约束的列不能没有值,否则报错。
   b:非空约束的关键字 not null
   c:非空约束的格式: 列名 类型  not null;    
   d:字段默认是可以为空   例如: age int ;  等价于 age int null;

4、唯一约束
   a:字段指定唯一约束后,那么字段的值必须是唯一的。类似于主键约束。
   b:唯一约束的关键字 unique
   c:唯一约束多的格式:列名 类型 unique;(you ni Q)
   d:取消唯一约束: alter table 表名 drop index 列名;  
     alter table student drop index sname;

5、外键约束	                         
   a:主外键是构成表与表关联的唯一途径。
   b:外键是另一张表的主键。
   c:外键约束的格式:
     cons traint 外键名 fore ign key(外键列) re fe re nces 主键表(主键列);
   肯斯追她         fao rui kei        
   d:删除外键约束的格式:
     alter table 外键表 drop foreing key  外键名;
   e:查看外键:show create table 表名;
6、默认值约束(补充) 
   a、使用关键字default(di fao te)设置列的默认值 
   b、语法格式: 
      列名 类型 default 默认值;
      例如:性别列默认值为男   sex varchar(50) default '男';
            年龄列默认值为20   age int default 20;

掌握order by子句对查询结果排序ASC升序,DESC降序
1、使用关键字order by 对查询的结果进行排序。
2、在默认情况下,order by 按升序输出结果。升序(ASC) 降序(DESC)
3、排序的语法格式:
select * from 表名 order by 字段名 asc/desc;
4、当排序时字段值相同,可以按照另一个字段排序
语法格式:
select * from 表名 order by 字段1 asc/desc,字段2 asc/desc;

掌握模糊查询 (like) like----喜欢,像
1、使用关键字like实现模糊查询,有两种通配符:% 和 下划线(
2、“%”----可以匹配一个或多个字符,可以代表任意长度的字符串。
1)查询姓王的学生信息 :select * from 表名 where name like ‘王%’;
2)查询名字中包含“明”的学生信息: select * from 表名 where name like ‘%明%’;
3、“
”----可以匹配一个字符。
1)查询以m开头,以n结尾的3个字符姓名的学生信息 select * from 表名 where name like ‘m_n’;
2)查询由5个字母构成的姓名的学生信息 select * from 表名 where name like ‘_____’;
这是一个纵向运算的函数
聚合函数是用来做纵向运算的函数。

count()函数
1、功能:统计指定列不为null的记录行数
2、查询student表中记录数 select count() from student;
查询student表中sex列不为空的记录行数 select count(sex) from student;
查询student表中工资大于10000的人数 select count(
) from student where money>10000;
3、多列字段同时统计就行数
格式:select count(字段1),count(字段2) from 表名;
sum()函数
1、功能:计算某列的和
2、计算score列之和 select sum(score) from 表名;
3、计算多列的和格式: select sum(列1),sum(列2) from 表名;

avg()函数
1、功能:计算某列的平均值
2、计算score列的平均值 select avg(score) from 表名;
3、平均值保留两位小数
round(平均值,2) -----会进行四舍五入计算
select round(avg(score),保留的小数)from 表名
max()函数
1、功能:找出某列中的最大值
2、语法格式: select max(列名)from 表名;
3、找出多列中的最大值: select max(列名1),max(列名2) from 表名;
min()函数
1、功能:找出某列中的最小值
2、语法格式: select min(列名)from 表名;
3、找出多列中的最小值: select min(列名1),min(列名2) from 表名;

灵活运用分组查询group by子句
例如:每个公司都有多个部门,如果统计每个部门的在职员工人数,那么就需要分组统计人数…
分组查询需要使用关键字group by
语法格式:
select … from 表名 group by 列名;
例如:
查询每个部门的部门编号和每个部门的人数:
select id,count(*) from 表名 group by id;

having用法(补充)

having 用法与WHERE用法类似,但有三点不同
1、HAVING只用于GROUP BY(分组统计语句),
2、WHERE 是用于在初始表中筛选查询,HAVING用于在WHERE和GROUP BY 结果中查询。
3、HAVING可以使用聚合函数,面WHERE 不能。

这个是用在聚合函数的用法。当我们在用聚合函数的时候,一般都要用到GROUP BY 先进行分组,然后再进行聚合函数的运算。
HAVING子句----练习
查询工资总和大于9000的部门编号以及工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

掌握limit子句
限制查询结果的数量----limit关键字
1、查询数据时,可能会查询出很多的记录,而用户需要的记录可能只是很少的一部分,这样就需要来限制查询结果的数量。
关键字limit可以对查询结果的记录条数进行限定,控制它输出的行数。
2、显示前3条记录 select * from 表名 limit 3;
3、使用关键字limit还可以查询结果的中间部分取值。
两个参数,参数1是开始读取的第一条记录的编号(在查询结果中,第一个结果的记录编号是0,而不是1);
参数2是要查询记录的个数。
例如:查询出第2条到第4条记录信息 select * from 表名 limit 1,3;
合并结果集
1、合并结果集就是把不同表中的记录连到一起,这样查询结果会产生笛卡尔积。
笛卡尔积:多表连接时,表中记录个数的乘积。
笛卡尔积中会存在我们不想要的结果,那么怎么去除这些记录呢?用条件过滤不想要的记录。

2、合并结果集的方法:内连接查询和外连接查询。

内连接查询 inner join内连接只显示符合条件的记录
1、内连接是最普通的连接类型,它要求构成连接的表中有等同的字段。
2、内连接要求构成连接的每一部分的每个表的匹配,不匹配的行将被排1除。
3、语法格式:
假设两个表中都有等同的字段user
select 字段名 from 表1,表2 where 表1.user=表2.user; ------隐式内连接
或者:select 字段名 from 表1 inner join 表2 on 表1.user=表2.user; ------显式内连接(效率高)

外连接查询 outer join所有记录都显示没有的记录为null
1、与内连接不同,外连接是指使用outer join关键字将两个表连接起来。
外连接生成的结果集不仅包含符合连接条件的行数据,而且还包括左表(左外连接时的表)、右表(右外连接时的表)或两边连接表(全外连接时的表)中所有的数据行。
2、语法格式:
select 字段名 from 表名1 left|right|outer join 表名2 on 表名1.字段名1=表名2.字段名2;
3、外连接分为左外连接(left join)、右外连接(right join)和全外连接3种类型。
4、左外连接:
左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL。
列如:select * from 表名1 left outer join 表名2 on 是谁和谁连接的;
5、右外连接:
右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示NULL。
列如:select * from 表名2 right outer join 表名1 on 是谁和谁连接的;
在前面显示的表为主表在后面显示的为附表
个人补充:一般来讲那个表的数据多一般是父表,视情况而定
如果父表是ts 子表为tt 那么用左外链接ts父表在左边,用右外链接ts父表在连接的右边
合并查询结果 (补充)
1、合并查询结果是将多个select语句的查询结果合并到一起。

   合并查询结果使用关键字union和union all。
   1)关键字union是将所有的查询结果合并到一起,然后去除重复记录;
   2)关键字union all则只是简单地将结果合并到一起,不会去除重复记录。

2、语法格式:
   select 字段 from 表1    union/union all    select 字段 from 表2;

1、子查询就是嵌套查询,即select中包含select,如果一条语句中存在两个,或两个以上select,那么就是子查询语句了。
2、子查询出现的位置:
1)where后作为条件存在
2)from后作为表存在(多行多列)

带关键字in的子查询 查询多列
1、只有子查询返回的结果列包含一个值时,比较运算符才适用。
2、假如一个子查询返回的结果集是值的列表,这时比较运算符就必须用关键字in代替。
3、in 运算符可以检测结果集中是否存在某个特定的值,如果检测成功就执行外部的查询。
4、练习:查询性别和工资与李鹏飞完全相同的员工信息
select * from 表名 where (sex,salary)in (select sex,salary fron 表名 where sname=‘李鹏飞’);
带比较运算符的子查询 > >= < <= = != <> 查询一列
练习1:查询工资高于李鹏飞的员工信息
select * from 表名 where salary>(select salary from 表名 where sname=‘李鹏飞’);
练习2:查询分数大于班级平均分的学生的信息
select * from student where score>(select avg(score) from student);

带exists的子查询
1、含有exists的子查询特点:
含有exists的子查询实际上不产生任何数据
在有返回行的情况下,子查询将返回true,反之则返回false
由于仅仅判断是否存在返回行,所以子查询的选择列表通常指定为(*)
2、练习:
1)如果student中存在name为张三,则查询表中的所有学生的姓名、性别和年龄
select sname,sex,age from student where exists (select * from student where sname=‘张三’);
2)如果student中不存在name为张三,则查询表中的所有学生的姓名、性别和年龄
select sname,sex,age from student where not exists (select * from student where sname=‘张三’);

带any的子查询-----等价于找最小值
1、关键字any表示满足其中任意一个条件。
2、使用关键字any时,只要满足内层查询语句返回的结果中的任意一个,就可以通过该条件来执行外层查询语句。
3、练习:查询工资大于大数据任意一人薪资的员工信息
select * from 表名 where salary > any(select salary from 表名 where sname=‘大数据’);
等价于:select * from 表名 where salary >(select min(salary) from 表名 where sname=‘大数据’);

带all的子查询------等价于找最大值
1、关键字all表示满足所有条件。
2、使用关键字all时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。
3、练习:查询工资高于大数据所有人的员工信息
select * from 表名 where salary > all(select salary from 表名 where sname=‘大数据’);
等价于:select * from 表名 where salary >(select max(salary) from 表名 where sname=‘大数据’);

合并查询结果
1、合并查询结果是将多个select语句的查询结果合并到一起。

   合并查询结果使用关键字union和union all。
   1)关键字union是将所有的查询结果合并到一起,然后去除重复记录;(比较常用)
   2)关键字union all则只是简单地将结果合并到一起,不会去除重复记录。

2、语法格式:
       select * from 表1    union/union all    select * from 表2;
    或 select 字段 from 表1    union/union all    select 字段 from 表2;