mysql03

最新推荐文章于 2025-04-14 14:33:22 发布

原创最新推荐文章于 2025-04-14 14:33:22 发布 · 175 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

7 篇文章

订阅专栏

day03
1.SQL查询
1.distinct: 不显示字段的重复值
语法：select distinct 字段1，字段2 from 表名;
示例：
1.表中都有哪些国家
select distinct country from sanguo;
select distinct country from MOSHOU.sanguo;
select distinct id,name from MOSHOU.sanguo;
2.表中一共有几个国家
select count(distinct country) as number from sanguo;
3.注意
1. distinct和from之间的所有字段都相同才会去重
2. 查询表记录时可以做数学运算
1. 运算符： + - * / %
2.示例：
1.查询显示所有的攻击力翻倍
select id,name,gongji*2 as new from sanguo;

##苹果安装pip install pymysql
安装软件： sudo apt-get install...
安装模块、库： sudo pip3 install pymysql
安装pip： pip-0.9.tar.gz
文件夹：文件-> setup.py
python3 setup.py install
vi /etc/passwd

2.约束
1. 作用：保证数据的一致性、有效性
2. 约束分类
1. 默认约束（default）
插入记录时不给该字段赋值，则使用默认值
   sex enum("M","F","S") default "S";
2. 非空约束（not null）
不允许该字段的值为null
   id int not null,
   id int not null default 0;

表名.frm 表结构
表名.ibd 表数据

3.索引
1.定义：
对数据库中表的一列或多列字段的值进行排序的一种结构（BTree）
2.优点
加快数据的查询速度
3.缺点
1.当你对表中数据更新时，索引也许要动态维护，降低数据的维护速度
2.索引需要占用物理存储空间
4.索引示例
1.开启运行时间检测：musql> set profiling=1
2.执行查询语句：select name from t1 where name="Lucy00";
3.查看执行时间 show profiles;
4.在name字段创建索引
create index name on t1(name);
5.再次执行查询语句
select name from t1 where name="Lucy1000";
6.查看执行时间
show profiles

5.索引（重点）
1. 普通索引（index）
1. 使用规则
1.可设置多个字段（字段值无约束）
2.把经常用来查询的字段设置为索引字段
3.KEY标志： MUL
2. 创建
1. 创建表时
create table t1(
   ...,
   ...,
   index(字段名1)，
   index(字段名2)
   );
2.已有表中
create index 索引名 on 表名（字段名）;
3. 查看索引
1. desc 表名; ---> key标志为MUL
2. show index from 表名\G;
4. 删除普通索引
drop index 索引名 on 表名;
2. 唯一索引（unique）
1. 使用规则
1.可设置多个字段
2.约束：字段值不允许重复，但可以为NULL
3.key标志：UNI
2. 创建
1. 创建表时
unique(pnumuber),
   unique(cardnumber)
2. 已有表
create unique index 索引名 on 表名;
3. 查看、删除同普通索引
删除： drop index 索引名 on 表名;
3. 主键索引（primary key）&& 自增长属性（auto_increment）
1.使用规则
1.只能有一个字段为主键字段
2.约束：字段值不允许重复，也不能为NULL
3.KEY标识：PRI
4.通常设置记录编号字段 id,能够唯一锁定一条记录
2. 创建
1.创建表时
   自增长属于数据类型可以用modify修改 auto_increment

   1. create table t4(
   id int primary key auto_increment,
   name varchar(20) not null,
   )auto_increment = 10000,
   character set utf8;
注：括号后面可以任意加参数值
若表已经存在
   alter table t4 auto_increment = 10000;

2. id int auto_increment,
name varchar(20),
primary key(id,name) #复合主键

   2.已有表
   alter table 表名 add primary key(id);
   alter table 表名 modify id int auto_increment;
   3.删除主键
   1. 先删除自增长属性（modify）
   alter table t4 modify id int;
   2. 删除主键
   alter table t4 drop primary key;
4. 外键（foreign key）

6.数据导入
1.作用：把文件系统中内容导入到数据库里
2.语法格式：
load data infile "文件名"
into table 表名
fields terminated by "分隔符" ----->字段
lines terminated by "\n"; --->记录
3.将socretable.csv 导入到数据库中
1. 在数据库中创建对应的表
create table score(
   id int,
   name varchar(15),
   score float(5,2),
   phnumber char(11),
   class char(7)
   )character set utf8;

2. 执行数据导入
1. 查看搜索路径
   show variables like "secure_file_priv";
   ## /var/lib/mysql-files/
   2. 拷贝文件
   sudo cp ~/scoreTable.csv /var/lib/mysql-files/

   sudo -i
   cd /var/lib/mysql-files/
   ls -l scoreTable.csv
   chmod 744 scoreTable.csv ---->修改权限
   3.执行数据导入
   load data infile "/var/lib/mysql-files/scoreTable.csv"
   into table score
   fields terminated by ","
   lines terminated by "\n";
7.数据导出
1.把数据库表的记录导出到系统文件里
2.语法格式
select ... from 表名
into outfile "文件名"
fields terminated by "分隔符"
lines terminated by "\n";
3.练习
1.把MOSHOU库下的sanguo表中，英雄的姓名，攻击值和国家给导出来，sanguo.csv
1. 查看搜索路径
   show variables like "%secure%";
   2. 执行数据导出语句
   select name,gongji,country from MOSHOU.sanguo
   into outfile "/var/lib/mysql-files/sanguo.csv"
fields terminated by ","
   lines terminated by "\n";
   3.把mysql库下的user表中user、host的值导出到系统文件 user.txt
   select user,host from mysql.user
   into outfile "/var/lib/mysql-files/user.txt"
   fields terminated by " "
   lines terminated by "\n";
4.查看、更改文件权限
1. ls -l score.txt
- rw- rw- r-- tarena tarena
   r（4）: 读所有者所属组
   w（2）：写
   x（1）: 可执行
   rw- ：所有者权限
   rw- ：同组其他用户文件
   r-- ：其他组的用户权限
2. chmod 777 score.txt
chmod 740 score.txt

8.表的复制
1. 语法
create table 表名 select ... from 表名 where 条件;
2. 示例
1. 复制MOSHOU.sanguo表，sanguo2
create table MOSHOU.sanguo2 select * from MOSHOU.sanguo;
2. 复制MOSHOU.sanguo中的id,name,country的记录，sanguo3
create table MOSHOU.sanguo3 select id,name,country from MOSHOU.sanguo;
3. 复制MOSHOU.sanguo中的name，country，每页显示2条记录，复制第3页的内容
create table MOSHOU.sanguo4 select name,country from MOSHOU.sanguo
limit (3-1)*2,2
4. 复制表结构
create table 表名 select ...from 表名 where false;

select user-id,count(user-id) from commment
group by user-id
order by count(user-id) desc
limit 10;

作业：
1、把 /etc/passwd 文件导入到数据库 userinfo
tarena : x : 1000 : 1000 : tarena,,,:
用户名密码 UID GID 描述
/home/tarena : /bin/bash
主目录登录权限
create database userinfo;
create table userinfo1(
yonghu char(20),
mima char(20),
UID int,
GID int,
miaoshu char(50),
zhumulu char(30),
quanxian char(30)
);

sudo -i
cp /etc/passwd /var/lib/mysql-files/
ls -l passwd

load data infile "/var/lib/mysql-files/passwd"
into table userinfo1
fields terminated by ":"
lines terminated by "\n";

2、在userinfo表中的第1列添加 id 字段,主键、自增长、显示宽度为3,位数不够用0填充

001
002
003

alter table userinfo1 add id int(3) zerofill primary key auto_increment first

3、Mac本配置搜索路径：
sudo -i
vi my.cnf
[mysqld]
secure_file_priv="/usr/local/mysql/data/"
系统偏好设置 - 小海豚 - stop - start
mysql>show variables like "secure_file_priv";

1.创建对应的表
create table userinfo（
username varchar(20),
password char(1),
uid int,
gid int,
comment varchar(50),
homedir varchar(50),
shell varchar(50)
);

2.复制文件到/var/lib/mysql-files/
sudo cp /etc/password /var/lib/mysql-files/

3.输入导入语句
load data infile"/var/lib/mysql-files/passwd"
into table db4.userinfo
fields terminated by ":"
lines terminated by "\n"