mysql数据库学习

最新推荐文章于 2023-06-28 15:36:27 发布

icnvd

最新推荐文章于 2023-06-28 15:36:27 发布

阅读量110

点赞数

分类专栏：数据库文章标签： mysql linux

本文链接：https://blog.csdn.net/qq_44692240/article/details/112847691

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

数据库基础语法

数据库密码修改

#第一种
mysqladmin -uroot -pxxx password 'xxx'

#第二种
mysql -uroot -p
mysql>set passowrd for root@'localhost'=password('xxxxx');

#第三种
mysql -uroot -p
mysql>alter user root@'localhost' identified by 'xxxxxx';

#修改密码策略
mysql -uroot -p
mysql>set global validate_password_policy=0;   #修改密码策略等级  
#0或LOW为只验证密码长度  
#1或MEDIUM验证长度 数字 大小写和特殊字符
#2或STRONG验证长度 数字  大小写和特殊字符 字典文件
mysql>set global validate_password_length=6;   #修改密码最短长度为6


#查看数据库变量
mysql -uroot -p
mysql> show variables like '%password%';    #检索出含有password的变量
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| default_password_lifetime             | 0     |
| disconnect_on_expired_password        | ON    |
| log_builtin_as_identified_by_password | OFF   |
| mysql_native_password_proxy_users     | OFF   |
| old_passwords                         | 0     |
| report_password                       |       |
| sha256_password_proxy_users           | OFF   |
| validate_password_check_user_name     | OFF   |
| validate_password_dictionary_file     |       |
| validate_password_length              | 6     |
| validate_password_mixed_case_count    | 1     |
| validate_password_number_count        | 1     |
| validate_password_policy              | LOW   |
| validate_password_special_char_count  | 1     |
+---------------------------------------+-------+


#mysql密码忘了  修改
vim /etc/my.cnf
=======================
[mysqld]
skip_grant_tables     #登录无需验证密码
#validate_password_policy=0
#validate_password_length=6                密码策略相关的要注释
=====================================
systemctl restart mysqld   #重启服务

mysql #直接进入      直接更新mysql root用户密码
>update mysql.user set authentication_string=password('xxxxx')
->where user='root' and host='localhost';

>flush privileges;   #刷新
>exit  #退出

vim /etc/my.cnf  #修改配置文件回来
#重新登录



#也可以
mysql
flush privileges;  #先刷新权限 否则会告诉在skip_grant_tables中没法修改
alter user root@'localhost' identified by 'xxxxxx';
exit

#修改配置文件回来

三类语句

DDL 创建语句（create、drop、alter）
DML 操作数据语句（insert、delete、update、select、truncate）
DCL 数据控制语句（grant、revoke）
DTL 数据事务语言 (commit、rollback、savepoint)

创建表

create table tablename(
column_name1  column_type1 constraints,
column_name2  column_type2 constraints
)

column_name1 列名
column_type1 列类型(varchar、date、int…)
constraints 约束条件

查询表结构

use test;      #进入test表
create table emp(ename varchar(10), hiredate date, sal decimal(10,2),deptno int(2));
desc emp;        #查看表emp结构
drop table emp; #删除表emp

重命名表名

语法

alter table tablename rename newtablename
举例

alter table emp rename emp2;           #修改表名
alter table emp2 rename emp;            #修改表名

alter table test.emp rename mydb.empcopy;  #复制到另一个数据库中

修改表字段定义

语法

alter table tablename modify[column] column_definition[first|after col_name]

after | first为mysql在基础SQL上的扩展不是每个数据库语法都支持

举例

alter table emp modify ename varchar(20);  #修改表emp的ename列的数据类型为varchar(20)

增加表字段

语法

alter table tablename add[column] column_definition[first|after col_name]
举例

alter table emp add column age int(3);   #添加age列数据类型为int(3)

删除表字段

语法

alter table tablename drop[column] col_name
举例

alter table emp drop column age;     #删除age列

修改字段定义(包括字段名)

语法

alter table tablename change[column] old_col_name column_definition[first | after col_name]
举例

alter table emp change age agel int(4);  #将age列修改为agel列名int(4)数据类型

change 和modify都可以修改表的定义，但是modify修改不了字段名，change可以，不过change需要写两次字段名(~~废话不写两个怎么从旧值改新值~~)

插入记录

语法

insert into tablename(field1,field2,field3…) values(value1,value2,…)
举例

insert into emp(ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);
insert into emp values('lisa','2003-02-01','3000',2,22),('zs','2001-05-06','4000',3,21);

更新记录

语法

update tablename set field1=value1,field2=value2,…[where condition]
举例

update emp set sal = 4000 where ename = 'lisa'; #修改记录ename为lisa的sal为4000

删除记录

语法

delete from tablename[where condition]
举例

delete from emp where ename='dony';  #从emp表中删除ename为dony的记录

查询记录

语法

select * from tablename[where condition] order by fieldn [desc|asc] limit offset_start,row_count
处理方式
1. 排序
  
  select name,uid from user where id >= 10 and id <= 20 order by uid asc
2. 分组
  
  select shell from user group by shell
3. 过滤查询结果
  
  select name,uid from user where id <= 15 having name='zhangsan’
4. 聚集函数使用
5. 限制查询结果显示的行数
举例

select * from emp;
select ename,hiredate,sal,deptno from emp;
select * from emp  where ename='lisa' and sal=4000;              #条件查询
select * from emp order by sal desc;       #根据sal降序排列输出
select * from emp order by sal asc;        #根据sal增序排列输出
select * from emp order by sal desc limmit 3;   #根据sal降序排列输出3行（从0开始）
select * from emp order by sal desc limit 1,3;   #根据sal降序排列输出1~3行

聚合语法

select [field1,field2,…] fun_name from tablename where [where_contition] group by field1,field2… with rollup having [where_contition]

聚合语法举例

select count(1) from emp;            #统计emp表记录数赋予新的字段名
+----------+
| count(1) |
+----------+
|        4 |
+----------+
select deptno,count(1) from emp group by deptno; #根据deptno分类
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |                     #deptno为1的有2个
|      2 |        1 |
|      3 |        1 |
+--------+----------+
select deptno,count(1) from emp group by deptno with rollup;  #在上面的基础上统计总人数
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|      3 |        1 |
|   NULL |        4 |
+--------+----------+
select deptno, count(1) from emp group by deptno having count(1) > 1; #字段count(1)大于1																	#的值

+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
+--------+----------+

表连接

内连接匹配两张表中互相匹配的记录
外连接选出其他不匹配的记录
- 左连接包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
- 右连接包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
举例

select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bjguan | 2004-05-02 | 5000.00 |      1 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
#内连接
select ename,deptname from emp,dept where emp.deptno=dept.deptno;  #内连接查找两张表中
+--------+----------+								#deptno相同的记录的ename deptname
| ename  | deptname |
+--------+----------+
| zzx1   | tech     |
| lisa   | sale     |
| bjguan | tech     |
+--------+----------+

#左连接
select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| bjguan | tech     |
| lisa   | sale     |
| dony   | NULL     |
+--------+----------+
#显示emp中的字段   如果dept中没有  就NULL替代
#右连接显示右边表    左边表NULL替代
select ename,deptname from right join dept on emp.deptno=dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| lisa   | sale     |
| bjguan | tech     |
| NULL   | hr       |
+--------+----------+

索引

创建表时 index（column_name）

create table tea4(
id char(6) not null,
name varchar(6) not null,
age int(3) not null,
gender enum('boy','girl') default 'boy',
index(id),index(name)              #添加普通索引
);

desc tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   | MUL | NULL    |       | #kEY为MUL 索引
| name   | varchar(5)         | YES   | MUL | NULL    |       |
| age    | int(3)             | YES   |     | NULL    |       |
| gender | enum('boy','girl') | YES   |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+

表创建后

create index age on tea4(age);                 #为tea4的age字段创建age索引
Query OK, 0 rows affected (0.01 sec)

desc tea4; #查看表结构
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | YES   | MUL | NULL    |       | 
| name   | varchar(5)         | YES   | MUL | NULL    |       |
| age    | int(3)             | YES   | MUL | NULL    |       | #age字段有索引了
| gender | enum('boy','girl') | YES   |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+


#删除age索引
drop index age on tea4;   #不需要字段名了
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

show index from tea4\G;   #列表形式查看索引
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE               #默认索引为B树    还有B+树和hash可以设置
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

主键

表创建时

create table tea4 (
id int(4) primary key,                   #单个主键   属性方式添加
name varchar(8)
);

create table tea4(
id int(4),
name varchar(8),
primary key (id)                     #单个主键   函数形式
);

create table tea4(
id int(4),
name varchar(8)
primary key(id, name)                    #函数形式 复合主键
);

表创建后

alter table tea4 drop primary key;   #删除tea4主键    alter drop 
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table tea4 modify id int(4) primary key;  #给id添加主键   change 也行
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table tea4 drop primary key;

alter table tea4 add primary key(id, name);
desc tea4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(4)     | NO   | PRI | NULL    |       | #成功设置主键
| name  | varchar(8) | NO   | PRI | NULL    |       | #成功设置主键
+-------+------------+------+-----+---------+-------+

字段的auto_increment属性只能一个字段且是主键才能使用，复合主键也只能一个字段使用

外键

语法

alter table 库名.表名 add constraint 外键名 foreign key(需加外键的字段名) references 关联表名(关联字段)

alter table gz add constraint gz_id_wj foreign key(gz_id) references yg(yg_id) on 操作名 cascade

#创建员工表   主键为员工id 自增 数据库引擎为innodb
create table yg(
yg_id int primary key auto_increment,
name char(16)
)engine=innodb;  #设置数据库引擎为innodb   还有MyISAM

在MySQL 5.1之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB

MyISAM

它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT，MyISAM是更好的选择。

MyISAM存储引擎的特点是：表级锁、不支持事务和全文索引，适合一些CMS内容管理系统作为后台数据库使用，但是使用大并发、重负荷生产系统上，表锁结构的特性就显得力不从心；
InnoDB

这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,

对于支持事物的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。

InnoDB存储引擎的特点是：行级锁、事务安全（ACID兼容）、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
注意

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表,例如
```
update table set num=1 where name like "a%"
```
两种类型最主要的差别就是InnoDB支持事务处理与外键和行级锁。而MyISAM不支持。所以MyISAM往往就容易被人认为只适合在小项目中使用。

#创建工资表
create table gz(
gz_id int,
name char(16),
gz float(7,2),
foreign key(gz_id) references yg(yg_id)    #创建外键
on update cascade on delete cascade        #同步更新update、同步删除delete
)engine=innodb;


#插入员工数据
insert into yg(name) values('Jerry'),('Tom');
insert into gz(gz_id,name,gz) values(1,'Jerry',12000),(2,'Tom',8000);

select * from gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|     1 | Jerry | 12000.00 |
|     2 | Tom   |  8000.00 |
+-------+-------+----------+

#修改员工表中的id  查看同步更新功能
update yg set yg_id=1234 where name=Jerry;
select * from gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|  1234 | Jerry | 12000.00 |  #工资表中的id也被修改了
|     2 | Tom   |  8000.00 |
+-------+-------+----------+

#删除Jerry   查看同步删除功能
delete from yg where name='Jerry';
select * from gz;
+-------+------+---------+
| gz_id | name | gz      |
+-------+------+---------+
|     2 | Tom  | 8000.00 |        #工资表中Jerry行也没了
+-------+------+---------+


#这里因为工资表没有设置主键 所以有几个问题
insert into gz(gz_id,name,gz) values(2,'Jorry',10000.0);
insert into gz(gz_id,name,gz) values(2,'Jorry',10000.0);
insert into gz(gz_id,name,gz) values(null,'Jorry',5000.0);

select * from gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|     2 | Tom   |  8000.00 |
|     2 | Jorry | 10000.00 |
|     2 | Jorry | 10000.00 |
|  NULL | Jorry | 10000.00 |   #多条重复记录    还有空值  不合理
+-------+-------+----------+

#设置主键
delete from gz where name='Jorry'; #先删除不合规的记录
Query OK, 3 rows affected (0.00 sec)

alter table gz modify gz_id int not null primary key;   #非空 主键
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

insert into gz(gz_id,name,gz) values(null,'Jorry',5000);
ERROR 1048 (23000): Column 'gz_id' cannot be null   #空值无法插入
insert into gz(gz_id,name,gz) values(2,'Jorry',1090);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'   #主键不能重复



#删除外键
show create table gz\G;  #查看工资表的外键名字
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `name` char(16) DEFAULT NULL,
  `gz` float(7,2) DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE         #外键名为'gz_ibfk_1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1


alter table gz drop foreign key 'gz_ibfk_1';  #删除外键

文件导入导出

secure_file_priv参数

默认为NULL ，表示mysqld不允许导入导出
secure_file_priv的值为/tmp/时，表示只允许在/tmp/目录下导入导出
secure_file_priv的值没有具体值时，表示不限制mysqld的导入导出

导入导出命令语法

导入

load data infile ‘文件路径’ into table 表名 fields terminated by ‘字段分隔符’ lines terminated by ‘记录分隔符’；
导出

select * from 表名 into outfile ‘导出文件路径’ fields terminated by ‘字段分隔符’ lines terminated by '记录分隔符’

mkdir /myload
chown mysql /myload  #修改文件所有者   防止权限问题
vim /etc/my.cnf
=========================
[mysqld]
secure_file_priv='/myload'
==============================
systemctl restart mysqld

mysql -uroot -p
show variables like 'secure_file_priv';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /myload/ |    #有了
+------------------+----------+

#拿/etc/password做测试
cp /etc/passwd /myload  #将要导入的文件放入允许导入导出的/myload/目录下
mysql -uroot -p
create database db3;
create table db3.user(  #符合/etc/passwd的内容  七列
	name char(50),
	password char(1),
	uid int,
	gid int,
	comment char(150),
	homedir char(50),
	shell char(50)
	);

#导入命令
load data infile '/myload/passwd' into table db3.user fields terminated by ':' lines terminated by '\n';
#导入文件为/myload/passwd 字段分隔为:  记录分隔为换行符



#导出命令
select * from db3.user into outfile '/myload/user1.txt' fields terminated by '||' lines terminated by '\n';

icnvd

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库学习

文章目录数据库基础语法数据库密码修改三类语句创建表查询表结构重命名表名语法修改表字段定义语法增加表字段语法删除表字段语法修改字段定义(包括字段名)语法插入记录语法更新记录语法删除记录语法查询记录语法聚合语法聚合语法举例表连接索引主键外键文件导入导出数据库基础语法数据库密码修改#第一种mysqladmin -uroot -pxxx password 'xxx'#第二种mysql -uroot -pmysql>set passowrd for root@'localhost'=passwo
复制链接

扫一扫