【mysql数据库】操作命令及简单的增删改查

SQL是一种结构化查询语言。

注：在mysql>命令行中输入命令时结尾都需要加分号“；”

一. 启动并打开mysql的基本命令

1. service mysqld start    //打开数据库服务器
2. mysql -u root –p    //以管理员方式运行
3. system clear   //清屏
4. show databases;   //列出已存在的数据库
5. use mysql;   //选择数据库
6. show tables;   //列出已存在的表
7. show warnings;   //打印出警告
8. use basename;    //选择要进行操作的库

SQL的分类

DDL——数据定义语言，对结构的增删改查

        create  drop  alter  show

DML——数据操作语言  ※

        insert  delete  update  select

DCL——数据控制语言，针对权限

        自主存取控制：由root用户给普通用户分配权限

强制存取控制：将权限都设置成等级式

grant赋予权限  revoke回收权限

DDL：定义结构的，库、表

1. create database if not exists CY1706;  //创建库
2. drop database if exists CY1706;  //删除库
3. show create database CY1706;  //查看库
4. 创建表（学生表——学号，姓名，年龄，性别）
   create table Student(
   id varchar(10) primary key COMMENT “学生学号”,
   name varchar(10) not null COMMENT “学生姓名”,
   age int COMMENT “学生年龄”,
   sex enum(“man”, “woman”) default “woman” COMMENT “学生性别”
   );

约束：主键、外键、唯一、非空、默认

字段=名称+类型+约束+注释

“lisi”  char(10)   //10   固长类型，不用计算空间，效率高

varchar(10) //5   变长类型，效率低

1. 删除表  drop table stu;
2. 修改表

1. 修改字段类型  modify

alter table stu modify name varchar(20);

1. 修改字段名称  change

alter table stu change id sid varchar(10);  //id->sid

1. 在表中添加新的字段  add  first  after

alter table stu add score float;   //在所有字段最底下

                 alter table stu add score1 float after sid;

                 alter table stu add score2 float first;

                 (4)在表中删除一个字段  drop

                 alter table stu drop score2;

(5)修改表名  rename

alter table stu rename stu2;

1. 查看表
   show create table tbname;  //查看表的创建信息

                 desc tbname;  //查看表的字段信息

 

DML——数据操作语言

1. 插入数据 insert

“001”,”zhansan”,17,”man”

“002”,”lisi”,18,”woman”

“003”,”wangwu”,19,”man”

“004”,”zhaoliu”,20,”man”

“005”,”wuqi”,21,”woman”

(1)insert into stu values(“001”,”zhansan”,17,”man”);  //向表中插入数据

        insert into stu (sid,name,sex,age) values(“001”,”zhansan”,17,”man”);  //同上

(2)insert into stu(sid,name,sex) values(“002”,”lisi”,”woman”);

(3)小批量插入 

insert into stu values(“003”,”wangwu”,19,”man”),

(“004”,”zhaoliu”,20,”man”),

(“005”,”wuqi”,21,”woman”);

                 当插入发生主键冲突时使用replace，replace=delete+insert

                 (4)大批量插入  load source

2.删除数据 “zhangsan”;

        delete from stu;  //删除stu表中所有数据

delete from stu where name = “zhangsan”;  //只删除匹配到一条数据

SQL频繁的写入删除，多用户系统，不可恢复，要求实时性高

用log进行恢复，把修改前后的数据进行记录 

命令log truncate删除不会做记录

redo.log 重做日志

undo.log 撤销日志

        3.修改数据

                 update stu set age=19;

update stu set age=19 where id=”003”;

        4.查看数据

        (1)普通查询

select * from stu;  //*通配符，不建议用，会把一个人的信息都获取到

                 select sid,name,age,sex from stu;  //建议用，保护数据安全

                 select * from stu where id = “001”;

        (2)去重查询

                 select distinct age from stu;

        (3)排序查询

                 select distinct age from stu order by age;               //默认以升序排序

                 select distinct age from stu order by age asc;    //同上

                 select distinct age from stu order by age desc;   //降序方式排序

 

• 创建一个成绩表

create table result(

                                          sid varchar(10) not null COMMENT "学生学号",

                                          pid varchar(10) not null COMMENT "课程编号",

                                          score float default 0 COMMENT "成绩"

                                  );

• 给成绩表插入数据

                                  insert into result values("001","p01",57),

                                                                                     ("001","p02",89),

                                                                                     ("002","p01",86),

                                                                                     ("002","p02",67),

                                                                                     ("003","p01",56),

                                                                                     ("003","p02",78),

                                                                                     ("004","p01",55),

                                                                                     ("004","p02",78);

(4)分组查询 group by

select sid, SUM(score) all_score

from result

group by sid;

 

多表查询

(5)等值查询，效率低，笛卡尔乘积O(m*n) 穷举匹配

查询年龄未满18岁的学生的不及格成绩

select name,score

from stu,result

where stu.sid=result.sid and age<18 and score<60;

(6)连接查询

• 外连接查询

   左外连接查询(左表缩小后的范围必须全部存在)  常用，效率高

                  select name,score

                  (select sid,name from stu where age<18)  a

        left join

(select sid,score from result where score<60)  b

on a.sid=b.sid

where score is not null;

  右外连接查询

                           select name,score

                  (select sid,name from stu where age<18)  a

        right join

(select sid,score from result where score<60)  b

on a.sid=b.sid

where name is not null;

   全连接查询(保证表中左右两边数据都存在)  本机版本不支持

                           select name,score

                  (select sid,name from stu where age<18)  a

        full join

(select sid,score from result where score<60)  b

on a.sid=b.sid;

• 内连接查询

   内连接查询(只匹配满足的条件)

                  select name,score

                  (select sid,name from stu where age<18)  a

        inner join

(select sid,score from result where score<60)  b

on a.sid=b.sid;

• 创建一个教师表
  create table teacher(
  tid varchar(10) primary key COMMENT “教师工号”,
  tname varchar(20) default NULL,
  tage int(11) default NULL COMMENT “教师年龄”,
  tsex enum(“man”, “woman”) default “woman” COMMENT “教师性别”
  );

        (7)联合查询   union(自带去重);加all不去重

                         //查询全体的师生信息

                         select sid,name,age,sex from stu

                         union all

                         select tid,tname,tage,tsex from teach;

聚集函数SUM、COUNT

时间类型 date、time、datetime、时间戳

 

3.DCL 数据控制语言

权限管理

create user “cy1706”@localhost identified by “123”;

quit;或者exit;   //退出用户

(1)授权 grant

grant select on CY1706.* to cy1706;  //需切换到root用户进行授权

flush privileges;  //刷新权限

select user,host,password from user;

%远程用户

localhost 本地用户

(2)回收权限  revoke

revoke select on CY1706.* from cy1706;

root->u1->u2->u3

谁赋予的权限谁才能回收，防止反祖现象发生