mysql（五），

1 Navicat无法连接数据库的解决办法

打开【win+r】输入CMD进入命令行界面，然后输入

alter user 'root'@'localhost' identified by '密码' password expire never;

alter user 'root'@'localhost' identified with mysql_native_password by '密码';

flush privileges;

记得将密码替换为mysql 的密码。

2.DDL数据库定义语言

2-1数据库

代码块

查看有哪些数据库:show databases;

创建数据库ec14:   create database ec14;

使用数据库ec14:    use ec14;

修改数据库ec14:   alter database ec14;

删除数据库ec14:    drop database ec14;

2-2表(使用了一个数据库，use ec14;)

代码块
查看哪些表：show tables;

创建表： create database student(
id int primary key auto_increment,
name varchar(20) not null
)

更新表： alter table student;

查看表结构： desc student;

删除表： drop table student;

查询表：select * from student;

修改表名：rename  table student to new_student;

2-3 列（字段）

代码块

添加一列(年龄)    ：alter table student add age  int not null;

修改字段类型：     alter table student modify age bigint;

给字段重命名：   alter table student change age new_age int;

删除一个字段:    alter table studnet drop age;

3 DML数据库操作语言

代码块

向表中插入数据： insert into student values('wangsiyu',18),('alex',27)

插入指定列   insert into student(id,stu_name) value(1,'wangsiyu');

更新列 UPDATE student set stu_email='736055802@qq.com' where stu_name ='wangsiyu';

删除列 truncate table student;

删除这列数据 delete from student where id =1;

4 DQL数据查询语言

代码块
1. 查询指定列：select name,age from student

2. is null/ is not null

3. 去重   select distinct name from student

4. 查询年龄和成绩的和，如果为空则为0：

select ifnull(age,0)+ifnull(score,0) as total from student

5. 重命名 as

6. 模糊查询  like_     like%

7. 排序   order by age asc/desc

8. 聚合函数  count()/max()/min()/sum(0/avg()

9. 分组查询 group by

按照性别分组：

select gender,group_concat(name) from employee group by gender;

注意：select 后面跟的组名一般都出现在group by后出现

where和having区别：

where不用聚合函数，针对分组前的结果，处理后的结果才参与下一步分组。

having只针对group by 分组后的结果来进行筛选和处理，后面跟聚合函数。

10：查询工资大于2000，工资总和大于60000部门以及工资和，按照部门分组并按照总工资和降序排列

select  salary ,dept ,sum(dept) from employee where salary>2000 group by dept having sum(dept)>60000 order by sum(dept) desc;

SQL语句的执行顺序：

from-where-group by-having-select-order by-limit

11.分页的操作

select * from employee limit (curpage-1)*pagesize,pagesize;

5 MYSQL数据库完整性

完整性主要包括实体完整性，参数完整性，域完整性。

5-1主键约束（唯一，不能为空）

代码块：创建主键的三种方式：

主键创建的第一种方式：

CREATE TABLE class (

class_id int PRIMARY KEY,

class_name varchar(10)

);



主键创建第二种方式：

CREATE TABLE class (

class_id int ,

class_name varchar(10),

primary key(class_id)

);



主键添加第三种方式：

ALTER TABLE class ADD CONSTRAINT PRIMARY key (class_id);

5-2唯一约束（不能重复，但是可以为空）

唯一约束创建：与主键的三种创建方式一致。

5-3自动增长列(auto_increment)

代码块
CREATE TABLE class (

class_id int PRIMARY KEY auto_increment ,

class_name varchar(10)

);

向自动增长插入一条数据（需要指定列明）：insert into class（name） VALUE ('asdlfjl');

5-4 域完整性：针对单元格。有3种：数据类型约束，默认值约束，非空约束

代码块
CREATE TABLE class (

class_id int PRIMARY KEY auto_increment ,

class_name varchar(10) not null,

class_gender varchar(1) DEFAULT '男');

5-5参照完整性：表与表之间的一种关系

主键和外键字段类型必须一致。

数据库类型必须是innoDB

外键中的值必须先存在于主键中的值

代码块
创建外键第一种：班级表和学院表

CREATE TABLE class (

id int PRIMARY key ,

class_name varchar(20),

class_college int,

CONSTRAINT class_college_fk FOREIGN key class(class_college) REFERENCES college(id));

6.关于mysql数据库视图

视图：select查询结果的一个虚拟表。

6-1 创建视图

代码块
EATE VIEW stu_salary_view

as

SELECT * from student WHERE salary>5000 with CHECK option;

with CHECK option 表示修改视图时候，salary必须大于5000，否则无法保存。

6-2使用视图

代码块
SELECT * from  stu_salary_view  WHERE stu_age=24;

6-3替换原来的视图

覆盖原来的视图

代码块
CREATE OR REPLACE VIEW stu_salary_view

as

(SELECT * from student )

6-4删除视图

DROP VIEW stu_salary_view;

7创建一个学生的数据库

代码块
-- 创建学院表

CREATE TABLE  college(

  id  int(11)  NOT NULL  AUTO_INCREMENT,

  name    varchar(30) NOT NULL,

  PRIMARY KEY (id)

) ;

-- 创建表班级

CREATE TABLE class (

  id int(11) NOT NULL AUTO_INCREMENT,

  name  varchar(30) NOT NULL,

  belong_college  int(11) DEFAULT NULL,

  PRIMARY KEY (id),

  CONSTRAINT class_college_fk  FOREIGN KEY (belong_college) REFERENCES  college(id)

) ;

-- 创建表班级

CREATE TABLE student(

id int PRIMARY KEY ,

stu_name varchar(20) not null,

age int not null,

gender varchar(6) not null,

number int not null,

birth datetime check(birth > '1990-1-1')

stu_class int,

CONSTRAINT student_class_fk FOREIGN KEY (stu_class) REFERENCES class(id)

);

-- 创建老师表

create table teacher

(

id int  auto_increment primary key,

tea_name varchar(20) not null,

tea_class int,

  CONSTRAINT teacher_class_fk FOREIGN KEY (tea_class) REFERENCES class(id)

);

-- 创建课程表

create table course

(

id int auto_increment primary key,

cou_name varchar(50) not null,

cou_time tinyint check(cou_time>0 and cou_time<100),

cou_teacher int not null,

  CONSTRAINT course_teacher_fk FOREIGN KEY (cou_teacher) REFERENCES teacher(id)

);

-- 创建成绩表

create table score

(

score_course int,

  score_number int,

score int,

  CONSTRAINT score_course_fk FOREIGN KEY(score_course) REFERENCES course(id),

  CONSTRAINT score_student_fk FOREIGN KEY(score_number) REFERENCES student(id)

);