连接MYSQL服务器:mysql -uroot -proot
mysql -uroot -p --default_character_set=gbk; (影响数据的输入和输出)
show variables like 'character%';
数据库的操作:创建,查看,修改,删除
*创建:
创建一个名称为mydb1的数据库。
create database mydb1;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
创建一个使用utf-8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;
*查看:
显示所有数据库
show databases;
显示创建数据库的语句信息
show create database mydb2;
*修改:
修改mydb1的字符集为gbk(不能修改数据库名)
alter database mydb1 character set utf8;
*删除:
删除数据库mydb2
drop database mydb1;
msql服务启动不起来问题排查
用root用户操作(启动和关闭mysql服务)
表的操作:创建,查看,修改,删除
create table t1 (id int, name varchar(20)) --用例子引创建表
*创建:
根据实体类Person创建表person
Person {
int id;
String name;
}
create table person(
id int,
name varchar(20)
);
mysql中的数据类型:
bit 1位 但可以指定位数,如:bit(3)
int 2字节 可以指定最大位数,如:int<4>
最大为4位的整数
float 2个字节 可以指定最大的位数和最大的小数位数,如:float<5,2>
最大为一个5位的数,小数位最多2位
double 4个字节 可以指定最大的位数和最大的小数位数,如:float<6,4>
最大为一个6位的数,小数位最多4位
char 必须指定字符数,如char(5)
为不可变字符 即使存储的内容为’ab’,也是用5个字符的空间存储这个数据
varchar 必须指定字符数,如varchar(5)
为可变字符 如果存储的内容为’ab’,占用2个字符的空间;如果为’abc’,则占用3个字符的空间
text: 大文本(大字符串)
blob:二进制大数据 如图片,音频文件,视频文件
date: 日期 如:'1921-01-02'
datetime: 日期时间 如:'1921-01-02 12:23:43'
timeStamp: 时间戳,自动赋值为当前日期时间
创建一个员工表
*查看:
查看所有的表:
show tables;
查看指定表的创建语句
show create table employee;
mysql表 名称区分大小写
显示指定表的结构:
desc employee;
*删除:
删除employee表
drop table employee;
修改表:
增加一个字段:alter table worker add column height double;
修改一个字段:alter table worker modify column height float;
删除一个字段:alter table worker drop column height;
更改表名:rename table employee to worker;
修改表的字符集:alter table worker character set gbk;
表数据的CRUD
*C(create增加数据) Insert语句
创建一个员工表,新建Employee表并表中添加一些记录
create table employee(
id int,
name varchar(20),
sex bit,
birthday date,
salary double,
entry_date date,
resume text
);
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,'张三',1,'1983-09-21',15000,'2012-06-24','一个大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-09-21',10000,'2012-07-24','一个中牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-09-21',7000,'2012-08-24','一个小牛');
*U(update更新数据) Update语句
将所有员工薪水都增加500元。
update employee set salary=salary+500;
将王五的员工薪水修改为10000元,resume改为也是一个中牛
*D(drop删除数据) Delete语句
删除表中姓名为王五的记录。
delete from employee where name='王五';
删除表中所有记录。
delete from employee; --可以有条件,但删除所有记录差了一点
使用truncate删除表中记录。
truncate employee;--无条件 效率高
*R(Retrieve查找数据) Select语句
准备环境:
综合案例:创建一个学生表,插入数据
create table student(
id int,
name varchar(20),
chinese int,
english int,
math int
);
insert into student(id,name,chinese,english,math) values(1,'何东',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'权筝',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'何南',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'叶坦',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'何西',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'丁香',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'何北',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'唐娇',77,80,79);
insert into student(id,name,chinese,english,math) values(9,'任知了',95,85,85);
insert into student(id,name,chinese,english,math) values(10,'王越',94,85,84);
查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select english from student;
select DISTINCT english from student;
select DISTINCT english,name from student;
select english+chinese+math from student;
select english+chinese+math as 总分 from student;
select name,english+chinese+math as 总分 from student;
在所有学生英语分数上加10分特长分。
select name,english+10 from student;
统计每个学生的总分。
select english+chinese+math from student;
使用别名表示学生分数
select name,english+chinese+math as 总分 from student;
select name,english+chinese+math 总分 from student;
查询姓名为何东的学生成绩
select * from student where name='何东';
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于250分的所有同学
select * from student where english+chinese+math>250;
查询英语分数在 85-95之间的同学。
select * from student where english>=85 and english<=95;
select * from student where english between 85 and 95;
查询数学分数为84,90,91的同学。
select * from student where math=84 or math=90 or math=91;
select * from student where math in(84,90,91);
查询所有姓何的学生成绩。
select * from student where name like '何%';
查询数学分>85,语文分>90的同学。
select * from student where math>85 and chinese>90;
对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出,然后再按从高到低的顺序输出
select * from student order by math+chinese+english desc;
对姓何的学生成绩排序输出
select * from student where name like '何%' order by math+chinese+english desc;
select name, math+chinese+english from student where name like '何%' order by math+chinese+english desc;
统计一个班级共有多少学生?
select count(*) from student;
统计数学成绩大于90的学生有多少个?
select count(*) from student where math>90;
统计总分大于250的人数有多少?
select count(*) from student where math+chinese+english>250;
统计一个班级数学总成绩?
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math), sum(chinese), sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;
求一个班级数学平均分?
select avg(math) from student;
求一个班级总分平均分
select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;
求班级最高分和最低分
select max(math+chinese+english),min(math+chinese+english) from student;
综合性练习:为学生表,增加一个班级列,然后训练分组查询
查出各个班的总分,最高分
准备环境
给表添加一个字段:alter table student add column class_id int;
更新表:
update student set class_id=1 where id<=5;
update student set class_id=2 where id>5;
select sum(math+chinese+english),max(math+chinese+english)
from student group by class_id;
查询出班级总分大于1300分的班级ID
select class_id from student group by class_id having sum(math+chinese+english)>1300;
select class_id from student where sum(math+chinese+english)>1300 group by class_id ;
note:where和group区别: 在wehre子句中不能使用分组函数
时间和日期
mysql> select year (now()), month(now()), day(now()) , date(now());
+————–+————–+————+————-+
| year (now()) | month(now()) | day(now()) | date(now()) |
+————–+————–+————+————-+
| 2014 | 9 | 7 | 2014-09-07 |
+————–+————–+————+————-+
select date_add(now(), INTERVAL 2 year) from dual;//增加两年
select charset('name') employee;
select date_add(now(), INTERVAL -1 day) 昨天, now() 今天, date_add(now(), INTERVAL +1 day) 明天;
字符串相关函数
select concat( charset('name'), 'aaaa') 自定义 from dual;
--日期转字符串
select date_format(now(), '%Y-%m-%d') from dual;
select date_format(now(), '%Y-%c-%d %h:%i:%s') from dual;
--日期 转 成字符串
select str_to_date('2015-2-04 05:14:15' , '%Y-%c-%d %h:%i:%s') from dual;
表的约束
*定义主键约束 primary key
:不允许为空,不允许重复
*定义主键自动增长 auto_increment
*定义唯一约束 unique
*定义非空约束 not null
*定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id)
*删除主键:alter table tablename drop primary key ;
create table myclass
(
id INT(11) primary key auto_increment,
name varchar(20) unique
);
create table student(
id INT(11) primary key auto_increment,
name varchar(20) unique,
passwd varchar(15) not null,
classid INT(11), #注意这个地方不要少逗号
constraint stu_classid_FK foreign key(classid) references myclass(id)
);
多表查询
1 创建多表查询案例
创建数据库脚本如下
-- 用root用户登录系统,执行脚本
-- 创建数据库
create database mydb61 character set utf8 ;
-- 选择数据库
use mydb61;
-- 增加 dbuser1 用户
-- 创建用户‘dbuser61’密码为 ‘dbuser61’拥有操作数据库mydb61的所有权限
GRANT ALL ON mydb61.* TO dbuser61 IDENTIFIED BY "dbuser61";
flush privileges;
-- grant select,insert,update,delete on mydb61.* to dbuser61@localhost identified by "dbuser61";
-- grant select,insert,update,delete on mydb61.* to dbuser61@'%' identified by "dbuser61";
-- 创建表
-- 创建部门表 并赋值
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`(
`deptno` int(2) PRIMARY KEY,
`dname` varchar(14) NOT NULL,
`loc` varchar(13)
)DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');
commit;
-- 注意mysql的sql语言 约束如果起名字,需要单独写在表后面
-- 创建员工表 并赋值
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`(
-- `empno` int(4) constraint emp_empno_pk PRIMARY KEY,
`empno` int(4) PRIMARY KEY,
`ename` varchar(10) NOT NULL,
`job` varchar(9),
`mgr` int(4),
`hiredate` DATE,
`sal` int ,
`comm` int,
`deptno` int(2) ,
constraint emp_deptno_fk foreign key(deptno) references dept(deptno)
)DEFAULT CHARSET=utf8;
--创建索引
-- CREATE INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott2;
create index emp_ename_index on emp(ename);
--注意 日期格式不一样
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
-- INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, deptno) VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 30);
INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
commit;
-- 创建工资级别表 并赋值
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`(
`grade` int,
`losal` int,
`hisal` int
)DEFAULT CHARSET=utf8;
INSERT INTO salgrade VALUES(1, 700, 1200);
INSERT INTO salgrade VALUES(2, 1201, 1400);
INSERT INTO salgrade VALUES(3, 1401, 2000);
INSERT INTO salgrade VALUES(4, 2001, 3000);
INSERT INTO salgrade VALUES(5, 3001, 9999);
commit;
--创建奖金表
DROP TABLE IF EXISTS `bonus`;
CREATE TABLE bonus(
`ename` VARCHAR(10),
`job` VARCHAR(9),
`sal` int,
`comm` int
)DEFAULT CHARSET=utf8;
commit;
2 多表操作
等值连接
不等值连接
外连接
自连接
使用SQL99标准的连接查询(JOIN..ON..)
内连接
只返回满足连接条件的数据(两边都有的才显示)。
select e.*, d.*
from emp e
inner join dept d
on e.deptno=d.deptno
-- 也可以省略inner关键字。
select e.*, d.*
from emp e , dept d
where e.deptno=d.deptno
左外连接 左边有值才显示。
select e.*, d.*
from emp e
left outer join dept d
on e.deptno=d.deptno
-- 也可以省略outer关键字
右外连接 右边边有值才显示。
select e.*, d.*
from emp e
right outer join dept d
on e.deptno=d.deptno
-- 也可以省略outer关键字
满外联接 任一边有值就会显示。
select e.*, d.*
from emp e
full outer join dept d
on e.deptno=d.deptno
-- 也可以省略outer关键字
交叉连接: 叉集,就是笛卡尔积
select e.*, d.*
from emp e
cross join dept d
-- 没有连接条件
eg:查询员工信息,员工号,姓名,月薪,部门名称
分析1
oracle的
select e.deptno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
sql99
select e.deptno, e.ename, e.sal, d.dname
from emp e inner join dept d
on e.deptno = d.deptno
====>结论: mysql能识别=
//显示所有部门信息
//显示各个部门的部门人数
–分析1 分组
–分析2 多表
===oracle的
select d.deptno, d.dname , count(e.deptno)
from dept d, emp e
where d.deptno = e.deptno (+)
group by d.deptno, d.dname
===>oracle的语法 mysql不支持
===sql99
select d.deptno, d.dname , count(e.deptno)
from dept d left outer join emp e
on d.deptno = e.deptno
group by d.deptno, d.dname
===>mysql支持
自连接: – 查询员工信息 ,老板信息
显示: *的老板是*
1 先按照oracle语法写
from emp e, emp b
where e.mgr = b.empno
2 改造
select concat( e.ename, '的老板是', b.ename )
from emp e, emp b
where e.mgr = b.empno
3 强化 显示king的老板
select concat( e.ename, '的老板是', b.ename )
from emp e, emp b
where e.mgr = b.empno (+)
4 用sql99的语法
select concat( e.ename, '的老板是', b.ename )
from emp e left outer join emp b
on e.mgr = b.empno ;
5 滤空 修正 nvl在mysql下不能用
select concat( e.ename, '的老板是', nvl(b.ename, '他自己' ) )
from emp e left outer join emp b
on e.mgr = b.empno ;
==>结论 ERROR 1305 (42000): FUNCTION mydb61.nvl does not exist
6 滤空 修正
select concat( e.ename, '的老板是', ifnull(b.ename, '他自己' ) )
from emp e left outer join emp b
on e.mgr = b.empno ;
| concat( e.ename, '的老板是', ifnull(b.ename, '他自己' ) ) |
+-------------------------------------------------------------------+
| SMITH的老板是FORD |
| ALLEN的老板是BLAKE |
| WARD的老板是BLAKE |
| JONES的老板是KING |
| MARTIN的老板是BLAKE |
| BLAKE的老板是KING |
| CLARK的老板是KING |
| SCOTT的老板是JONES |
| KING的老板是他自己 |
| TURNER的老板是BLAKE |
| ADAMS的老板是SCOTT |
| JAMES的老板是BLAKE |
| FORD的老板是JONES |
| MILLER的老板是CLARK |
+-------------------------------------------------------------------+
一些常用的命令:
一、连接MYSQL。
格式: mysql -h主机地址 -u用户名 -p用户密码
1、连接到本机上的MYSQL。
mysql -u root -p
回车后提示你输密码,注意用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密码。
如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是: mysql>
2、连接到远程主机上的MYSQL。假设远程主机的IP为:192.168.2.2,用户名为root,密码为123456。则键入以下命令:
mysql -h192.168.2.2 -uroot -p123456
3、退出MYSQL命令:
exit (回车)
二、修改密码。
格式:mysqladmin -u用户名 -p旧密码 password 新密码
1、给root加个密码123456。键入以下命令:
mysqladmin -u root -password 123456
2、再将root的密码改为56789。
mysqladmin -u root -p123456 password 56789
三、增加新用户。
格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”
1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL,然后键入以下命令:
mysql>grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;
mysql>flush privileges; 使之生效
2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了。
mysql>grant select,insert,update,delete on mydb.* to test2@localhost identified by “abc”;
mysql>flush privileges; 使之生效
如果你不想test2有密码,可以再打一个命令将密码消掉。
mysql>grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;
mysql>flush privileges; 使之生效
操作技巧
1、如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK。
2、你可以使用光标上下键调出以前的命令。
查询、创建、删除、更新命令
1、显示当前数据库服务器中的数据库列表:
mysql>show databases;
注意:mysql库里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。
2、显示数据库中的数据表:
mysql>use 库名;
mysql>show tables;
3、显示数据表的结构:
mysql>describe 表名;
4、建立数据库:
mysql>create database 库名;
5、建立数据表:
mysql>use 库名;
mysql>create table 表名 (字段名 varchar(20), 字段名 char(1));
6、删除数据库:
mysql>drop database 库名;
7、删除数据表:
mysql>drop table 表名;
8、将表中记录清空:
mysql>delete from 表名;
9、显示表中的记录:
mysql>select * from 表名;
10、往表中插入记录:
mysql>insert into 表名 values (”123”,”b”);
11、更新表中数据:
mysql>update 表名 set 字段名1='a',字段名2='b' where 字段名3='c';
12、用文本方式将数据装入数据表中:
mysql>load data local infile “/root/mysql.txt” into table 表名;
13、导入.sql文件命令:
mysql>use 数据库名;
mysql>source /root/mysql.sql;
14、命令行修改root密码:
mysql>update mysql.user set password=PASSWORD('新密码') where user='root';
mysql>flush privileges;
15、显示use的数据库名:
mysql>select database();
16、显示当前的user:
mysql>select user();
备份数据库
1.导出整个数据库,导出文件默认是存在当前操作目录下
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql
3.导出一个数据库结构
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
4.带语言参数导出
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql
查看不到mysql数据库的解决办法:
1.去/var/lib/mysql目录下去查看,发现mysql以及mysql.sock目录都在,还好还好,应该还有希望恢复;
2.停掉mysqld服务——“service mysqld stop”
3.查看mysqld进程是否还在跑——“ps -aux | grep mysqld”,如果还有在跑得进程,可以直接杀之“killall -TERM mysqld”
4.安全模式启动mysql;“mysqld_safe –user=mysql –skip-grant-tables –skip-networking &”
5.另起终端,输入“mysql -u root -p”,进入mysql数据库
6.show databases;看到mysql库又出现了
7.更改root用户密码:use mysql;update user set password=PASSWORD(“new-passwd”)where user=’root’;
8.FLUSH PRIVILEGES;