mysql数据库
关系数据库
SQL结构化查询语言,
socket介绍 root root
DDL:数据定义语言
DML:(data Manioulation)数据操作语言
DCL:(data control language)数据控制语言
DQL:(data Qiery language)数据查询语言 (重点)
docket连接软件。
tcp和udp协议:
tcp安全连接:建立连接,三次握手, (文件传输使用tcp 安全性,传输效率低)
udp不安全连接:直接向目的地址发送信息,可以不建立连接,可能丢失信息。(游戏使用udp)
1.DCL:(不重要)
-
创建用户 ,用户只能在指定ip地址
-
create user 用户名@IP地址 identified by '密码'
-
create user root@'%' identified by 'root' //%表示所有的ip地址都适用
-
给用户授权,所有权限可以用all代替。
grant 权限1,……权限n on 数据库.* to 用户名@IP地址
- 撤销权限
revoke
- 查看权限
show grants for 用户名
- 删除用户
drop user 用户名@ip地址
2.DDL语言,对数据库和表结构进行操作
-
MYSQL数据类型 含义 time data datatime 8字节,日期时间 timestamp 4字节,自动存储修改记录的时间 year 1字节,年份
整型,浮点型,字符串数据类型 (char:固定长度, varchar:可变长度),
操作数据库
查看数据库 show database
使用数据库
创建数据库并指定编码
CREATE DATABASE test DEFAULT CHARACTER SET utf8
创建表 create table 表名{}
create table student(
id int primary key
name varchar(20)
age tinyint unsigned
gander char(1)
);
查看当前数据库中的表
查看表结构 dese 表名
删除表 drop 表名
修改表:
- 添加列
alter table 表名 add(列名 类类型……);
- 修改列类型
alter table student modify hobby int
- 修改表的列名称和类型
alter table 表名 change 原列名 新列名 列名类型
- 删除列
alter table student drop newhobby
- 修改表名
ALTER TABLE STUDENT RENAME TO STU
3.DML 数据操作语言,语法(增删改)
- 插入数据(一次插入就是一行)
insert into 表名(列名1,列名2,列名3) values(列值1,列值2,列值3);
- 修改记录
update stu set age=22; //修改表中的所有age为22
update stu set age=23,name="zhangsan" where id =1; //只修改id为1的数据
- 删除数据
delete from stu where id =2;
关闭防火墙:
systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
表约束
约束名称 | 描述 |
---|---|
NOT NULL | 非空约束 |
UNIQUE | 唯一约束 |
PRIMARY KEY | 主键约束 |
FOREIGN KEY | 外键约束(关键字) |
DEFAULT | 默认值(缺省值) |
mysql -uroot -proot
create database mydatabase charset utf8
create table author(
aut_id int unique, //添加非空约束
aut_name char
)
插入数据
insert into anthor(aut_id) values (2);
查看表
select * from author;
删除表
drop table author;
创建一个新表
create table author(aut_id int,
aut_name char (4) default '小白' //添加默认属性
)
create table author (
aut_id int primary key, //添加主键约束
aut_name char (4) default '小白'
)
//auto_increment 主键自增约束
create table author(
aut_id int primary key auto_increment,
aut_name char(4)
)
查看表
select * from author
FOREIGN KEY 约束
外键可以建立多个,
1.删除表时,如果不删除引用外键的表,被引用的表不能直接删除
2.外键的值必须来源于引用的表的主键字段
create table book(
book_id int primary key,
book_name char (20) not null,
aut_id int ,
foreign key( aut_id) reference author(aut_id) //外键从作者表中传入
);
如果作者表中没有id为2的作者,那么author_id = 2 的书籍信息不能插入book表
可视化操作数据库
//创建数据库以及创建表单
drop table if exists student;
create table student(
id int(10) primary key,
name varchar(10),
age int (10) not null,
gander varchar (2)
);
drop table if exists course;
create table course (
id int (10) primary key,
name varchar(10),
t_id int (10)
);
drop table if exists teacher;
create table teacher(
id int (10) primary key,
name varchar(10)
);
drop table if exists scores;
create table scores(
s_id int ,
score int(10),
c_id int (10),
primary key (s_id,c_id)
);
表单填充数据
insert into student(id,name,age,gender)values (1,'baijie',19,'男');
insert into student(id,name,age,gender)values (1,'鲢鱼',19,'男');
insert into student(id,name,age,gender)values (1,'志伟',19,'男');
insert into student(id,name,age,gender)values (1,'李星',19,'男');
insert into student(id,name,age,gender)values (1,'张琪',19,'女');
insert into student(id,name,age,gender)values (1,'吴三',19,'女');
insert into student(id,name,age,gender)values (1,'张倩',19,'女');
insert into course(id,name,t_id)values(1,'数学',1);
insert into course(id,name,t_id)values(2,'语文',2);
insert into course(id,name,t_id)values(3,'c++',3);
insert into course(id,name,t_id)values(4,'java',4);
insert into teacher (id,name)values (1,'张楠');
insert into teacher (id,name)values (1,'老孙');
insert into teacher (id,name)values (1,'微微');
insert into teacher (id,name)values (1,'磊哥');
insert into teacher (id,name)values (1,'慧姐');
insert into scores (s_id,score,c_id)values(1,80,1);
insert into scores (s_id,score,c_id)values(1,56,2);
insert into scores (s_id,score,c_id)values(1,95,3);
insert into scores (s_id,score,c_id)values(1,49,4);
insert into scores (s_id,score,c_id)values(1,76,5);
insert into scores (s_id,score,c_id)values(2,35,1);
insert into scores (s_id,score,c_id)values(2,86,2);
insert into scores (s_id,score,c_id)values(2,73,3);
insert into scores (s_id,score,c_id)values(2,56,4);
insert into scores (s_id,score,c_id)values(2,78,5);
insert into scores (s_id,score,c_id)values(3,64,2);
insert into scores (s_id,score,c_id)values(3,93,3);
insert into scores (s_id,score,c_id)values(3,90,4);
insert into scores (s_id,score,c_id)values(3,83,5);
insert into scores (s_id,score,c_id)values(4,77,1);
insert into scores (s_id,score,c_id)values(4,99,2);
insert into scores (s_id,score,c_id)values(4,66,3);
insert into scores (s_id,score,c_id)values(5,83,2);
insert into scores (s_id,score,c_id)values(5,92,3);
insert into scores (s_id,score,c_id)values(5,73,4);
insert into scores (s_id,score,c_id)values(6,77,1);
insert into scores (s_id,score,c_id)values(6,38,2);
insert into scores (s_id,score,c_id)values(6,88,3);
insert into scores (s_id,score,c_id)values(6,55,4);
insert into scores (s_id,score,c_id)values(6,75,5);
insert into scores (s_id,score,c_id)values(7,39,1);
insert into scores (s_id,score,c_id)values(7,83,2);
insert into scores (s_id,score,c_id)values(7,75,3);
insert into scores (s_id,score,c_id)values(7,54,4);
insert into scores (s_id,score,c_id)values(7,73,5);
insert into scores (s_id,score,c_id)values(8,83,2);
insert into scores (s_id,score,c_id)values(8,76,3);
insert into scores (s_id,score,c_id)values(8,66,4);
insert into scores (s_id,score,c_id)values(8,74,5);
insert into scores (s_id,score,c_id)values(9,30,1);
insert into scores (s_id,score,c_id)values(9,80,2);
insert into scores (s_id,score,c_id)values(9,71,3);
insert into scores (s_id,score,c_id)values(9,70,5);
insert into scores (s_id,score,c_id)values(10,80,2);
insert into scores (s_id,score,c_id)values(10,70,3);
insert into scores (s_id,score,c_id)values(10,59,4);
insert into scores (s_id,score,c_id)values(10,79,5);
单表查询
查询所有列 select * from 表名;
查询指定列 select id ,name from student;
** 完全重复的记录只显示一次 在查询的列前面+ distinct
列运算:数据类型可以进行加减乘除,字符类型可以进行连接运算
** 起别名:
select s_id as student_id from scores; 别名为:student_id
select s_id as student_id from scores as s; 给表起别名
条件控制:
select * from teacher where id=2; 大于小于等于
select * from teacher where id in (1,2,5);
模糊查询,
select * from teacher where name like '%jie%'
- 模糊查询:https://www.cnblogs.com/muzixiaodan/p/5583473.html
排序:asc正序排列,desc倒序排列
select * from student order by age asc;
聚合函数:
count:select count (列名) from 表名;
max:select max (列名) from 表名;
min:select min (列名) from 表名;
sum:select sum (列名) from 表名;
avg:select avg (列名) from 表名;
分组和limit group by...having
select 分组列名,聚合函数1,聚合函数2 from 表名 group by 该分组名;
group by和 where 的区别
where是先筛选条件,group by是先进行分组,再根据having后的条件进行筛选
limit(mysql中独有的语法)
例子: select * from student limit 1,2; 表示从第一行中查2行(用于分页)
union和union all
将查询后的数据放在一起
select id,name from teacher union
union 和union all的区别:union只筛选一个,union all 可连接重复的数据
连接多个不同表的联合查询
select id name from student
union all
select id name name from teacher