mysql数据库初步（1）

SQL structed query language

连接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;

表的操作：创建，查看，修改，删除
*创建：
根据实体类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: 时间戳，自动赋值为当前日期时间

    创建一个员工表
        create table employee(id int,name varchar(20),sex bit,birthday date,salary double,entry_date date,resume text);
*查看：
    查看所有的表：
        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
);
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','一个小牛');
        insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'赵六',2,'1986-09-21',6000,'2012-08-14','一个小小牛');
        delete from employee where id=1 

        create table employee(  id int,name varchar(20),sex bit,birthday date,salary double,entry_date date,resume text);

*U(update更新数据) Update语句 
    将所有员工薪水都增加500元。
        update employee set salary=salary+500;
    将王五的员工薪水修改为10000元，resume改为也是一个中牛
        update employee set salary=10000,resume='也是一个中牛' where name='王五';
*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());
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;

表的约束　　
*定义主键约束　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)
);


    +-------+-------+------+------------------------+

create database mydb2 character set utf8;
use mydb2;

        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 别名 排序都能用
select chinese+english+math 总分 from student
order by 总分;

    select (chinese+english+math )  总分,  avg(chinese+english+math ) avgf from student 
    order by 1;


    order by 总分' at line 1
    mysql> select (chinese+english+math )  总分,  avg(chinese+english+math ) avgf from student 
        -> order by 1;
    +--------+----------+
    | 总分   | avgf     |
    +--------+----------+
    |    255 | 259.3000 |
    +--------+----------+
    1 row in set (0.00 sec)

===学生表分组练习
alter table student add column clssid int;
update student set clssid = 1 where id>=1 and id<=5;

update student set clssid = 2 where id between 6 and 10 

===>求各个班级的总分的平均分
====oracle和mysql的分组区别测试
select clssid 班级编号, avg(chinese+english+math ) 班级平均分
from student
group by clssid

    ======>

    select a, b, c, e
    from table1
    group by  a, b ,c ,d 

======>错误案例
select name, clssid 班级编号, avg(chinese+english+math ) 班级平均分
from student
group by clssid

    mysql> select  name, clssid 班级编号, avg(chinese+english+math ) 班级平均分
        -> from student
        -> group by clssid;
    +--------+--------------+-----------------+
    | name   | 班级编号     | 班级平均分      |
    +--------+--------------+-----------------+
    | 何东   |            1 |        265.8000 |
    | 丁香   |            2 |        252.8000 |
    +--------+--------------+-----------------+
    2 rows in set (0.00 sec)

mysql常用命令

mysql安装完之后，登陆后发现只有两个数据库：mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test |
+——————–+
，mysql> use mysql
ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’

访问被拒绝，原因就是在删除数据库时（rpm -e mysql*）没有删除干净，需要把/var/lib/mysql的目录全部删除干净，然后再重新安装即可。

顺便记一下一些常用的命令：

一、连接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;