mysql学习笔记 基础命令

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