MySQL基础

MySQL　　关系型关联数据库管理系统

库级操作语句

进入数据库：

 mysql -uusername -ppassword 

退出数据库：

exit 

q 

显示所有库：

 show databases; 

创建库：

 create database [if not exists] db_name; 

显示库创建信息：

 show create database db_name; 

删除库：

 drop database [if exists] db_name;  

切换当前库：

 use db_name; 

---

 表级操作语句

显示所有表：

 show tables; 

创建表：

 create table [if not exists] tb_name(create_definition,...); 

 create table [if not exists] tb_name(id int, name varchar(20));

显示表创建信息：

show create table tb_name;

desc[ribe] tb_name;  

删除表：

 drop table tb_name; 

---

表增删改查

指定插入：

 INSERT INTO tb_name(col_names) VALUES (col_values); 

全列插入：

 INSERT INTO tb_name VALUES (all_values); 

多行插入：

 INSERT INTO tb_name(col_names) VALUES (value_1), (value_2), …; 

指定列查询：

 SELECT col_names FROM tb_name; 

全列查询：

 SELECT * FROM tb_name; 

带条件查询：

 SELECT col_names FROM tb_name WHERE conditions;  

 SELECT col_names FROM tb_name WHERE id > 2;  

改：

 UPDATE tb_name SET field_1=value_1, field_2=value_2,… WHERE a_field = a_value; 

删：

  DELETE FROM tb_name WHERE a_field = a_value; 

注意： 改和删一定要写where条件，否则会删除或改变全部数据

 

---

数据类型：

create table tb2(
     id INT,                                 #默认长度11
     name VARCHAR(20),             #指定长度，最多65535个字符。   ***变长
     sex  CHAR(4),                 #指定长度，最多255个字符。     ***定长
     price DOUBLE(4,2),        #双精度浮点型，m总个数，d小数位
     detail text,                    #可变长度，最多65535个字符
     dates DATETIME,                #日期时间类型 YYYY-MM-DD HH:MM:SS
     ping  ENUM('好评','差评’)      #枚举， 在给出的value中选择
);

insert into tb2 value( 1,'裤子','男',20.0,'这条裤子超级好！！！',now(),'好评');    

---

约束

约束是一种限制， 通过退表的数据做出限制，来确保表中数据的完整性，唯一性。

约束类型	默认	非空	唯一	自增长	主键	外键
关键字	default	not null	unique key	auto_increment	primary key	foreign key

默认约束 default：

# 初始值设置，插入记录时，如果没有明确为字段赋值，则自动赋予默认值
1 create table tb(
    id int,
    name varchar(20),
    age int default 18
);

# 创建表后添加默认约束

 alter table tb modify age int default 20;

 # 取消默认约束

 alter table tb modify age int; 

非空约束 not null：

CREATE TABLE tb1(
     id int,
     name varchar(20) NOT NOLL
);

# 创建表后添加非空约束

 alter table tb1 modify id int not null; 

# 取消非空约束

 alter table tb1 modify id int ; 

唯一约束 unique key：

CREATE TABLE tb2(
     id int UNIQUE KEY,
     name varchar(20)
);

# 创建表后添加唯一约束

 alter table tb2 modify name varchar(20) unique key; 

# 取消唯一约束

 alter table tb2 modify name varchar(20); 

自增长 auto_increment：

# 自动编号，一般与主键组合使用，一个表里面只有一个自增长默认情况下，起始值为1，每次的增量为1。
1 create table tb3(
    id int primary key auto_increment,
    name varchar(20)
)auto_increment =100;

# 创建表后添加自增长

 alter table tb3 modify id int auto_increment; 

# 取消自增长

 alter table tb3 modify id int; 

主键约束 primary key：

# 作用 可以唯一标识一条数据，每张表里只能有一个主键
# 特性 非空唯一。当表里没有主键时，第一个出现非空且唯一的列，被当作主键。
1 create table tb4( 
    id int primary key,
    name varchar(20) not null
); 

# 创建表后添加主键

 alter table tb3 add primary key(id); 

# 删除主键

 alter table tb4 drop primary key; 

外键约束 foreign key：

create table a(
a_id int primary key auto_increment,
a_name varchar(20) not null
);

CREATE TABLE b (
  b_id int auto_increment,
  b_name varchar(20),
  constraint 外键名字 FOREIGN KEY (b_id) REFERENCES a(a_id)
);

# b是a的子表
# b表中的b_id 字段，只能添加 a_id中 已有的数据。
# a表中a_id  被参照的数据,不能被修改和删除

# 创建表后添加外键

 alter table b add constraint AB_foreign foreign key(b_id) references a(a_id); 

# 删除外键

 alter table b drop foreign key AB_foregin key; 

---

表结构修改

表描述	desc tb_name;
添加列	alter table tb_name add [column] name varchar(10);
删除列	alter table tb_name drop [column] name;
修改字段类型	alter table tb_name modify gender char(10);
修改字段名称(change比modify多了修改名称功能)	alter table tb_name change name new_name char(10);
修改表名	alter table tb_name rename to new_students;

---

子查询

SELECT grade FROM grades　　## 在查询张三成绩前，需要获得他的学号
    WHERE student_number = (SELECT number FROM students WHERE name='张三');　　## 嵌套查询张三学号的语句

交叉连接（笛卡尔连接）

内连接

左表 join 右表 on 左表.字段 = 右表.字段;

select name, subject_number as number,grade FROM students JOIN grades ON students.number = grades.student_number;

稍作修改  

select name, title, grade from students join grades on students.number = grades.student_number join subjects on grades.subject_number=subjects.number;

---

编码/用户/权限

编辑配置文件

 vim /etc/mysql/mysql.conf.d/mysqld.cnf 

重启数据库

 sudo service mysql restart 

远程连接

配置文件

 vim /etc/mysql/mysql.conf.d/mysqld.cnf 

第一步 修改监听IP

bind 0.0.0.0

第二步 给用户添加远程访问权限

update user set host = '%' where user = 'root';

第三步 重启数据库

sudo service mysql restart

用户权限

修改密码（记得密码）

 set password=password('new_password') 

修改密码（忘记密码）

1、进入mysqld.cnf文件添加skip-grant-tables（跳过登录）

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 　　在[mysqld]范围内添加 skip-grant-tables

2、登录mysql修改密码

use mysql

update mysql.user set authentication_string=password('qwe123') where user='root';

update mysql.user set plugin = 'mysql_native_password';

flush privileges;

exit

3、注释 skip-grant-tables （在43行）

创建用户

create user 'username'@'localhost' identified by 'password';

用户授权

grant 权限 on 数据库.* to ‘用户名'@'登录主机' identified by “密码";

取消用户授权

revoke privilege on databasename.tablename from 'username'@'%‘;

alter	修改已经存在的表
create	建立新的数据库或表
select	查找记录
update	修改记录
insert	增加表的记录
delete	删除表的记录
usage	只允许登录
all	允许做任何操作
drop	删除数据库或表

创建数据库自定义编码

 create database tb_name character set gbk; 

创建数据表自定义编码

 create table tb_name(id int ) charset utf8;