数据库常见操作

黑马训练营

---

一. 对数据库的操作

创建数据库用户 在实际开发中 绝对不能使用root数据库    create user '用户名' identified by '密码';为用户授权    grant all on 数据库名1.* to '用户';   //将数据库1灵力的所有权限赋给用户登录：    mysql -uroot -p密码    mysql -uroot -p 回车输入密码    mysql -uroot -p密码 -hIP地址修改密码：    第一步 use mysql;    第二步 update user set password=password('1234') where user='root';服务启动：    net start mysql；服务关闭：    net stop mysql；显示当前数据库服务器中的数据列表：    show databases;显示数据库中的数据表：    show tables;显示当前数据库的状态    status;显示当前数据库中某表的表结构：    desc 表名称;显示当前使用的数据库名称：    select database();显示所支持的字符集：    show character set;查看创建表，sql语句：    show create table 表名;

二、DDL数据库定义语言

创建数据库：    create database [if not exists] 数据库名称 [character set 字符集][collate 比较依据]    eg: create database day13 charcater set utf8 collate utf8_general_ci;查询数据库创建语句：    show create database 数据库名称;删除数据库：    drop database[if exists] 数据库名称;修改数据：    alter database 数据名 character set 字符集 collate 比较方式;表定义语句：    创建表：        create table 表名(字段名称 字段类型[字段约束],字段名称 字段类型[字段约束]，...);       删除表：        drop table 表名;对表结构进行修改：    修改字段类型：        alter table 表名 modify 字段名称 新类型;        eg：alter table user modify username varchar(100);    修改字段名称：alter table 表名 change 老字段 新字段名称 新字段类型;        eg：alter table user change username age int;    添加字段：        alter table 表名 add column 字段名称 字段类型;        eg: alter table user add column class varchar(10);    删除字段:        alter table 表名 drop[column] 字段名称;    重命名：        alter table 表名 rename [to] 新表名;

三、 DML数据操作语言(对数据库中表进行增、删、改操作)

插入数据：    insert into 表名(字段列表) values(字段对应值);更新数据：    update 表名 set 字段名=字段值,字段名=字段值,....;    update 表名 set 字段名=字段值，字段名=字段值，.... where 条件;删除数据：    delete from 表名[where 条件];        **不写条件删除所有

四、 约束

约束：给字段添加规则，约定内容编写。最终保证数据的完整性、一致性等。

• 主键约束：

  关键字 primary key   一个表中唯一表示，可以是一个字段，也可以是多个字段1. 定义表，声明字段，定义主键eg；create table pk01(id int primary key,name varchar(32));    特点：primary key 只能修饰一个字段2. 定义表，声明字段后在约束区域定义主键。eg: cterte table pk02(id int,name varchar(32),constraint primary key(id));    特点： constraint primary key (字段1,字段2,....) 可以设置多个字段3. 定义表，声明字段，表创建之后。修改表结构添加约束。eg:create table pk03(id int,name varchar(32));alter table pk03 add constraint primary key(id);

  特点：也可以设置多个字段更加灵活。
  **推荐方式3

• 唯一约束：

  关键字 unique          被修饰的字段不能重复1. 定义表，声明字段，声明唯一约束。  eg:create table un01(id int unique,name varchar(32));    特点：unique只能修饰一个字段2. 定义表，声明字段之后，在约束区域定义唯一约束。eg:create table un02(id int,name varchar(32),constraint unique(id));    特点：unique可以修饰多个字段3. 定义表，声明字段，表创建之后，修改表结构添加唯一约束。eg：create table un03(id int,name varchar(32));alter table un03 add constraint unique(id);    ########注意 唯一不对null起作用    即：id 可以多次为null。

• 非空约束

  关键字 not null        被修饰字段不能为null定义：在定义表，字段声明时，添加约束    create table nn01(id varchar(32),content varchar(50) not null);     insert into nn01(id) values('u001'); ## 出错    不想出错可以定义默认值：    create table nn02( id varchar(32),content varchar(50) not null default 'dzd');    insert into nn01(id) values('u001'); ## 不出错主键 =唯一 +非空

• 自动增长列

  关键字 auto_increment  mysql特有    被修饰后将自动累加   ps:oracle 没有自动增长列,但提供了序列sequence create table ai03(id int primary key auto_increment,content varchar(50),);*******注意：1. 字段必须是整形，一般使用int           2. 必须是key(主键、唯一)，一般使用主键primary key         3. 被auto_increment修饰的字段，不需要手动维护数据，mysql将自动维护

• 删除约束

  删除主键：    alert table 表名 drop primary key;删除唯一：    可以通过修改列实现删除外键：    alert table 表名 drop foreign key名称;

  cmd命令中文数据处理
  set names gbk;

五、 DQL数据库查询语言

• 无条件查询

  查询所有    select * from 表名;查询部分    select 字段名、字段名、... from 表名;  ##查询所有也可以使用这种方式  效率比*高     别名：    字段[as] 别名    eg：mysql> select id,concat(firstname,secondname) as 姓名 ,count -60 及格 from users;    ``重音符可以解决特殊符号，关键字等    eg: mysql> select id,concat(firstname,secondname) as `姓  名` ,count -60 及格 from users;     *姓名如果没用``修饰 中间有空格 报错 

• 带条件查询

  格式：    select 字段名称，字段名称，... from 表名 where 条件1 [or 条件2 and 条件3 .......];    运算符 优先级  and 优先 or

• 模糊查询：

   不完全匹配、like语句格式：select 字段名称，字段名称，... from 表名 where 字段 like 模糊查询表达式;模糊查询表达式；    % 匹配多个字符        '云'   只能匹配一个云        '%云'  以云结尾        '云%'  以云开头        '%云%' 包含云   ****重要    _ 匹配一个数据        '_云'    匹配  赵云   马云  这些        '__长'   匹配  关云长 

• 查询某一个条件为空的数据

  select 字段名称，字段名称，... from 表名 where 字段名 is null;

• 查询某一个条件不为空的数据

  select 字段名称，字段名称，... from 表名 where 字段名 is not null;

• 聚合函数：

    对表中数据进行统计，显示一个数据(一行一列的数据)## 注意 聚合函数不统计 null值统计有所少记录     关键字  count    select count(*|字段);    eg:select count(id) from users; #7    eg:select count(count) from users;#6    有null值平均值         关键字   avg    select avg(age) from user;   ##不精准  有可能null值    select sun(age)/count(id) from user;      id不为空，且唯一最大值         关键字   max    select max(age) from user;最小值         关键字   min    select min(age) from user;计算和         关键字    sum    select sum(age) from user;去除重复        关键字   distinct排序  select ... order by 字段1 关键字,字段2 关键字，...;    关键字  asc 升序，desc降序    默认 asc    eg:select distinct age from users order by age desc;   # age asc 等效 age [asc] 

• 分组

  关键字 group by 格式:select ... group by 分组字段;eg：select classes,sum(count)/count(id) from users group by classes;

• 多表操作：

  select ... from 表A,表B where  条件1 [or 条件2 and 条件3 .......];表的别名：    select ... from 表名 [as] 别名子查询      一条select语句，作为另一个select一部分。    eg:select * from users,(        select classes,sum(count)/count(id) as cavg from users group by classes) as B         where users.classes = B.classes and cavg < 60;    子一列，查询特点：        查询结果一行，可以使用 select id,(xxx) from        查询结果一行多列(查询多个值)，可以使用关键字 in ,all等        eg: xxx in (...)  xxx > all(xxx);        查询结果多行多列，可以当做另一个表使用。

六、 多表操作 ———–表与表之间的关系

• 一对一 ：

  方法1： ***推荐方式1. 创建主表，确定主表的主键2. 穿件从表，给从表添加外键字段3. 使用主外键关系描述 一对多create table user(id int primary key,#必须要有主键           username varchar(50),password varchar(32));create table order(id varchar(32),price double(5,2),user_id int #必须与主表主键类型一致          );使用主外键关系，从表外键与主表主键形成 主外键关系格式： alter table 从表表名 add [constraint] foreign key (从表外键) references 主表表名(主表主键);alter table order add constraint foreign key (user_id) references user (id);方法：1. 创建主键，添加主键2. 创建从表，创建时添加外键约束。create table t_user2(  id varchar(32) primary key,  #必须要有主键  username varchar(50),  password varchar(32));create table t_order2(  id varchar(32),  price double(5,2),  user_id varchar(32),   # 必须与主表主键类型一致  constraint foreign key (user_id) references t_user2 (id));注意：    外键可以为null    从表外键不能引用主表主键不存在的内容    外键如果引用了，主表数据内容不能删除

• 2 多对多

  # 创建第一个主表，确定主键# 创建第二个主表，确定主键# 创建中间表，添加两个外键，# 使用主外键关系进行描述（需要描述两次）create table t_student(  id varchar(32) primary key,  name varchar(50));create table t_course(  id varchar(32) primary key,  content varchar(50),  teacher varchar(32));create table t_student_course(  student_id varchar(32),   #t_student 对应外键，注意类型  course_id varchar(32)     #t_course 对应外键);#### 主外键关系####中间表 与  学生表alter table t_student_course add constraint student_fk foreign key(student_id ) references t_student(id);####中间表 与 课程表alter table t_student_course add constraint course_fk foreign key(course_id) references t_course(id);### 联合主键alter table t_student_course add constraint primary key(student_id,course_id);###删除外键#alter table `day14`.`t_student_course` drop foreign key `student_fk`#alter table `day14`.`t_student_course` drop foreign key `course_fk`

• 连接查询(多表操作)

  笛卡尔积：两个表的成绩隐式内连接   多表操作时，通过where确定连接   92规范eg: select * from t_user ,t_order where t_user.id = t_order.user_id;内连接  99规范格式：    select * from A inner join B on 连接条件 外链接    左外连接 查询A表所有内容，B表内容是否显示，取决条件是否成立。         如果成立将显示，如果不成立显示null    格式：select ... from A left outer join B on 条件    右外连接，查询B表所有内容，A表中内容是否显示，取决条件是否成立。如果成立将显示，如果不成立显示null    格式：select ... from A right outer join B on 条件

七、 补充

把一个数据库中的表复制到另一个表    create table 表名1 select * from 数据库名.表名2 (where  1=2 );                     ** 注意： 如果不写 where 1=2 会将原来表2的所有内容都复制到表1 如果写上 只复制表结构 不复制表里的数据分页查找    select * from 表名 limit 开始索引，分页个数读锁：共享锁select .... from ... lock in share model;写锁：排它锁，只能一个进行写，不能有其他锁(写锁、读锁) 所有的更新操作都将自动获得写锁select ... from for update;执行.sql文件：source 文件位置; //有图形化界面可以直接拖入