mysql

mysql

1、Linux下在线安装mysql命令：

[root@localhost ~]#yum install mysql-server

注意一定是mysql-server，如果仅写mysql是不行的，mysql安装其实分server与client端

2、安装完成后，对服务做相关设置：

[root@localhost ~]#chkconfig mysqld on <–设置服务随系统自动启动

chkconfig --list mysqld　 <-- 确认MySQL自启动
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off　 <–如果2–5为on的状态就OK

3、接下来就可以启动mysql服务了

[root@localhost ~]#service mysqld start

4、使用root用户进入mysql

[root@localhost ~]#mysql -u root

5、退出mysql

mysql>exit
mysql设置密码
1.停止mysql服务
#service mysqld stop

2.进入到skip-grant-tables模式：
#mysqld_safe --skip-grant-tables

3.直接不用密码进入mysql
#mysql

4.进入mysql系统数据库

use mysql

5.修改root密码

update user set password=password(“root”) where user=“root”;

6.刷新权限

flush privileges;
7.启动mysql服务
#service mysql start

8.重新登录root
#mysql -uroot -p root

Mysql下赋予最高权限
GRANT ALL PRIVILEGES ON . TO ‘root’@’%'WITH GRANT OPTION;

登录数据库mysql -u root -p 回车输入密码

查看所有数据库show databases；

创建数据库create database hello;

删除数据库 drop database hello;

使用数据库use hello；

创建数据库表 创建一个名为student的表

create table student(
		 id int,
		 name varchar(20),
		 gender varchar(10),
		 age int
	 );

查看表结构desc student；

删除表 drop table student；

–删除列alter table student drop column death;

–添加列alter table student add column death varchar(10);

–修改字段类型alter table student modify death varchar(20);

–修改字段名称alter table student change id death varchar(10);

–修改表名称rename table student instudents;

– 查询全部数据select * from students;

– 插入数据

insert into students values(1,"zhangsan","nan",18);
    insert into students values(2,"lisi","nan",45);

– 删除全部数据delete from students;

– 把id等于2的那条数据删除delete from students where id = 2;

– 把名字等于zhangsan的那条数据删除delete from student where name = "zhangsan";

– 全部修改update students set gender = "male";

– 把名字叫lisi的那个人的年龄改为update students set age = 20 where name = "lisi";

–把书名像book的书从book改为read

update t_book SET book_no = REPLACE( book_no, 'book', 'read' ) 

查询

–查询全部 select*from nihao；

–根据列查询 select id,name from nihao;

–查询别名 select id,name as "名字"，gender as "性别" from nihao；

–查询时添加列 select id,name, "强"as "性别" from nihao；

–查询时并列

select name,(linux+web) from nihao;

select name,(linux+web)"成绩" from nihao；

–查询时去除重复记录

select distinct gender from nihao;

select distinct age from nihao;

–条件查询

select* from nihao where id=2 and name="zhangsan";
select* from nihao where id=2 or name="zhangsan"

–比较查询 = < > <= >= <>

select* from nihao where linux>50 and linux<70;

select* from nihao where linux between 50 and 70;

–判空查询

select* from nihao  where linux is null;
select* from niaho where linux = "";
select* from nihao where liniux is not null;

–模糊查询 %匹配多个字符

select* from nihao where name like "z%";

– 匹配一个字符

select* from nihao where name like "z_";

– 匹配多个字符

select name from xuesheng where name rlike '[张李]'

–聚合查询
总和sum select sum(linux) as "linux总成绩" from nihao;
平均数avg select avg(linux) as "linux平均成绩" from nihao;
最大值max select max(linux) as "linux最大值" from nihao;
最小值min select max(linux) as "linux最小值" from nihao;
计算条数conut select count(1) from nihao;
分页查询 select * from student limit 0,2;
排序 select * from student order by linux;
asc 升序
desc 降序

select * from student order by linux is null,linux;

分组

select gender,count(*) from student group by gender;
select gender,count(*) from student group by gender having count(*)>5

sql语句的分类

ddl database dingyi language 数据定义语言
以 create alter drop 开头
dml 数据操作语言
以 insert delete update 开头