<span style="font-size:14px;">额外记录:
打开服务:services.msc
打开注册表:regedit
<strong>安装解压版mysql:</strong>
1,解压mysql-noinstall-5.1.73-win32.zip
2,配置环境变量:path:D:\java\mysql-noinstall-5.1.73-win32\mysql-5.1.73-win32\bin;
3,修改配置文件my.ini
3.1,在mysqld下配置:
basedir=D:\java\mysql-noinstall-5.1.73-win32\mysql-5.1.73-win32;#(mysql所在目录)
datadir=D:\java\mysql-noinstall-5.1.73-win32\mysql-5.1.73-win32\data;
3.2,配置编码格式:
[mysqld] [client]
default-character-set=utf8
*my.ini文件的编码必须是英文编码(如windows中的ANSI),不能是UTF-8或GBK等。
4,启动mysql:
4.1 以管理员身份:
mysqld -install #安装
net start mysql #启动mysql服务
mysqld -remove #删除mysql
net stop mysql #停止mysql服务
4.2 首次登入没有密码:
mysql -uroot -p (回车后直接按回车,登录成功)
5,修改root密码:
5.1,用户mysqladmin
未设置密码:mysqladmin -uroot password "newpass"
设置过密码:mysqladmin -uroot password oldpass "newpass" --未成功
5.2,set password命令
set password for 'root'@'localhost'=password('newpass');
5.3,用update直接编辑user表
use mysql
update user set password =password('newpass') where user ='root';
flush privileges; #(强制让MySQL重新加载权限)
5.4,在丢失root密码的时候,可以:
http://www.cnkuai.cn/news/20122/778.htm --未实验
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/MySQL
6,添加用户及权限:
6.1 localhost管理员:
grant all privileges on *.* to admin@localhost identified by 'admin' with grant option;
6.2 远程管理员
grant all privileges on *.* to admin@"%" identified by 'admin' with grant option;
6.3 普通用户
grant 权限(select | update | delete | create | drop| ) on 数据库.* to 用户名@登录主机 identified by '密码';
grant all privileges on *.* to test@localhost identified by "test";
6.4 删除用户
6.4.1 删除账户及权限:
drop user 用户名@'%'|localhost
6.4.2 编辑user表
delete from mysql.user where user='test' and host='localhost';
flush privileges;
6.5 修改指定用户密码
update mysql.user set password=password('newpass') where user='test' and host='localhost';
7.数据库操作
7.1 列出所有数据库
show databases;
7.2 切换数据库
use databaseName;
7.3 显示数据库表结构
describe tableName;
7.4 创建数据库
create database `databaseName`;
create database `test2` default character set utf8 collate utf8_general_ci;
7.5 删除数据库
drop database `databaseName`;
数据准备:
1.数据表只是数据的容器,没有任何数据的表示没有任何意义的。
mysql:建表
create table t_preson (
fname varchar(20),
fage int ,
fremark varchar(20),
primary key (fname)
);
create table t_debt (
fnumber varchar(20),
famount decimal(10,2) not null,
fpreson var char(20),
primary key (fnumber) ,
foreign key (fpreson) references t_person(fname)
);
插入数据:
insert into t_person(fname,fage,fremark ) values('Tom',18,'USA');
更新数据:
update t_person set fremark="SuperMan" where fname='Tom' or fname='jim';
数据删除:
delete from t_debt; # 先删除关联外键的表数据
delete from t_preson;
delete from t_person where fage >20 or fremark ='Mars';
排序:# order by asc/desc
select * from t_Employee order by fage asc ;
通配符:# '_' ,'%',
select * from t_Employee where fname like '%n%';
多值检测:# or , in ,and ,
分组:# group by # 必须放在select语句之后,需要分组的所有列必须位于group编译字句的列名表中,
#也就是没有出现在group by 字句中的列(聚合函数除外),是不能放到色了传统语句后的列名列表中的。
select fage from t_fmployee where fsubcompany ='beijing' group by fage ;
select fage ,count(*) as countOfThisAge from t_Employee group by fage ;
having 语句
select fage,count(*) as countOfThisAge from t_Employee group by fage having count(*)>1 or count(*)<3;
select fage,count(*) as countOfThisAge from t_Employee group by fage having count(*) in(1,3);
限制结果集行数:
select * from t_Employee order by fsalary desc limit 2,5;#降序,第二行开始最多五条记录
抑制数据重复:# distinct
select distinct fdepartment from t_Employee;
数据处理函数:# length('字符长度') substring('截取字段',0,3) abs('绝对值') sin('正弦值')
select fname,length(fname) as namelength from t_Employee where fname is not null;#计算每一个名称不为空的员工名长度。
字符串拼接:# concat() concat_ws('分隔符','参数',... )
select concat("工号为:",fname," 的员工的幸福指数:",fsalary/(fage-21) ) from t_Employee
计算字段的其他用途:# between ... and ... #在...范围内
数据表的创建和管理:
定义默认值:# default "默认值"
定义主键:# primary key("主键、联合主键")
定义外键:# foreign key("外键名称") references '目标表名'('被关联的字段名称')
删除表:# drop table '表名';
索引:# index
约束:# constraint '非空约束' '唯一约束'unique '检索约束'check '主键'primary key '外键'foreign key + references
表连接:
交叉连接: cross join 、内连接: inner join 、外连接: outer join 、联合连接: union join 、
内连接:# inner join ... on ...
select fnumber ,fprice from t_order inner join t_customer on fcustomerid = t_customer.fid where t_customer.fname = 'tom';
不等值连接:会产生笛卡尔集 可以在后边加上等值连接来优化
交叉连接:隐士、显示
自连接:自身相连接
外连接:左外连接 left 、右外连接 right
mysql 不支持全外部连接
</span>
安装解压版mysql杂记
最新推荐文章于 2024-08-06 16:09:13 发布