MySql基础_Linux

RDB对象
库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器
DCL：数据控制语言（访问权限的控制）
grant
revoke
Mysql用户密码修改三种方法
1、#mysqladmin -u USERNAME -h HOSTNAME password 'NEW_PASS' -p
2、mysql> set PASSWORD for 'USERNMAE'@'HOST'=PASSWORD('new_pass');
3、mysql>update mysql.user set PASSWORD=PASSWORD('new_pass') where condition; flush privileges才生效
在本机上免去输入密码的操作，在vim /root/.my.cnf
[client]
user=root
password=redhat
host=localhost

mysql
--user -u
--host -h
--password -p
--port
--portocol
--database DATABASE -D 如 mysql -D mydata 默认操作的库
mysql>
批处理模式（脚本模式）
mysql < init.sql 输入重定向 （mysql>\. init.sql）
Mysql基本操作
mysql>show databases;
mysql>use mysql; 切换定位到mysql库
mysql>select User,Host,Passwrod from user; 查询表中3列
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | centos-5 | |
| root | 127.0.0.1 | |
| | localhost | |
| | centos-5 | |
+------+-----------+----------+
mysql>drop user ''@localhost; 删除user表中localhost的行
mysql>drop user 'root'@'127.0.0.1'; 删除user表中127.0.0.1的行
mysql>insert into user (User,Host,Password) values ('root','127.0.0.1',''); 插入127.0.0.1的行
mysql>update user set Password=PASSWORD('redhat') where User='root' 更改user表中root用户的密码为redhat
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | centos-5 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
mysql>flush privileges;­ 让mysql重读授权表
修改mysql 存储引擎
mysql>show engines; 查看当前存储引擎类型
innodb引擎默认所有表共用一个表空间无法针对一些高级特性的使用,一般更改一张表一个独立空间
mysql>show variables like '%innodb%' 查看innodb的会话变量 %:任意一个或多个字符
innodb_locks_unsafe_for_binlog | OFF
vim /etc/my.conf
[,mysqld]
innodb_file_per_table = 1
service mysqld restart
mysql>show variables like '%innodb%'
| innodb_file_per_table | ON 修改成功
show table status like '%user%' \G 查看表的属性
客户端命令：
\c：提前终止语句执行，但是在结束符后加上一样会执行
\d：可以更改语句结束符
\!：不用退出mysql执行linux的shell命令
\g：当忘记之前更改的结束符，通过\g都可以直接将此语句送至服务器端执行
\G：无论语句结束符是什么，通过\G可以直接将此语句送至服务器端执行，而且结果以竖排方式显示
\W：语句执行结束后显示警告信息
\#：对新建的对象，支持补全功能 默认是关闭补全功能，通过rehash来开启补全功能
mysql输出格式
mysql -uroot -p --html 查询结果以html的格式显示结果
mysql -uroot -p --xml
查看表的属性信息
select table status like 'user'\G 竖排方式显示

mysqladmin用法，不通在mysql的交互模式下执行
mysqladmin -uroot -p create DATABASE 创建库
mysqladmin -uroot -p drop database 删除库
mysqladmin -uroot -p ping 测试mysql服务器是否在线
mysqladmin -uroot -p processlist 查看mysql服务器进程线程数
mysqladmin -uroot -p status --sleep 2 --count 2 用于监控当前mysql服务器的运行状态每两秒显示一次，2次后退出
mysqladmin -uroot -p extended-status：显示状态变量 监控服务器的一个接口或手段
mysqladmin -uroot -p variables：显示服务器变量
mysqladmin -uroot -p flush-privileges：让mysql重读授权表 等同于（reload）
mysqladmin -uroot -p flush-status：重置大多数的服务器状态变量
mysqladmin -uroot -p flush-hosts：清除主机错误登陆导致禁止访问产生的记录，让其重新计数
mysqladmin -uroot -p refresh：等同于flush-hosts flush-logs
mysqladmin -uroot -p shutdown：关闭服务器mysql进程
mysqladmin -uroot -p version：服务器版本及当前状态信息
mysqladmin -uroot -p start-slave：启动复制，启动从服务器复制线程
mysqladmin -uroot -p stop-slave：关闭复制

Mysql服务器变量：@@变量名
作用域分类
全局变量：show global variables;
会话变量：show [session] variables;
显示：select
设定：set global | session 变量名='value'

SQL语句
1.数据库的操作
DDL：数据定义语言（创建、删除、修改） 操作RDB对象
create
drop
alter
mysql> show character set; 查看当前服务器支持的所有字符集
mysql> show collation; 查看各个字符集的排序规则
数据库创建删除：(mysql>help create database;)
create database [if not exists] students [character set gbk] [collate gbk_chinese_ci];
drop database [if exists] students
数据库的属性修改：alter database db_name [character set utf8];
select databse(); 查看目前在哪个库中

2.表的操作
DML：数据操作语言 （曾删查改）
insert
delete
select
update
创建表的方式三种：
创建表（show table status like 'courses';查询表的属性包含引擎创建时间等）（show create table sutdent; 查看创建某个特定对象的时候用了什么语句）
1.create table courses(CID tinyint unsigned not null auto_increment primary key,Couse varchar(50) not null) engine myisam;
从其他表中查询出数据，并以之创建新表
2.create table testcourses select * from courses where CID <=2;
以其他表为模块创建一个空表
3.create table test like courses;
更改表名
raname table courses to kecheng;
修改表引擎
alter table student engine=InnoDB;
查询表索引
show indexes from courses;
创建索引
create index student_name on student (name(5) desc) using btree; 创建索引在name字段上，同时遵循btree索引规则，索引的长度为5，降序排序
删除索引
drop index index_name on student;
外键约束及删除外键约束（InnoDB才支持）
alter table student add foreign key foreign_cid (CID) references course (CID); 起个名字，在student表上增加一个外键为CID，参考course表的CID
show create table student; (CONSTRAINT `student_ibfk_1`)
alter table student drop foreign key student_ibfk_1;

表记录的增删查改
插入一行表记录的三种方式：
1.insert into courses (Couse) values ('Hamogong'),('pixiejianfa'),('Kuihuabaodian');
2.insert emp set name="wupeiqi",gender=1,birthday="1990-12-11",salary=900,dep="教学部";
3.insert into student (Name,Gender,Age) select Name,Gender,Age from tutors where Age > 20;

删除一行表记录
delete from tab_name where name="egon"; 不会重置计数器
truncate tab_name 适用于表的数据量大的情况，同时重置auto_increment计数器

表的查询
select last_insert_id(); 查看表中的计数器值
select distinct * from tb_name where Gender; distinct是对重复的只显示一次
select Name,Age,Gender from student where not Age>20 and not Gender='M'; 查询年龄不大于20同时不是男性的
select Name,Age,Gender from student where Age between 20 and 25; 查询年龄在20到25之间的包含等于
like '通配符'
%：任意长度任意字符 select Name from student where Name like '%ing%';
_：任意单个字符 select Name from student where Name like 'Y_ng%';
rlike '正则表达式'
select Name from student where Name rlike '^[xy].*$'; 以x或y开头任意字符结尾的
in
select Name from student where Age in (18,20,25); 替换复杂写法 select Name from student where Age=18 or Age=20 or Age=25;
比较为空要用 is null / is not null
select Name from student where CID is null;
order by
select Name from student where CID is not null order by Name DESC； 针对某个字段做降序排列，默认是升序
as：给字段去别名,同时能算数运算
select Name as student_name from student;
select 2+1 as sum
limit [offset],count偏移几个取几个
select Name from student limit 2,3; 针对查询的结果忽略前面两行从第三行往后取3行
聚合函数：sum() min() max() avg() count()
select min(Age) from student;
group by：根据什么分组目的是来做聚合计算
select avg(Age) from student group by Gender；
having：针对group by分组后的结果过滤,不能使用where
select count(CID1) as persons,CID1 from student group by CID1 having persons>=2; 可以使用别名来过滤

多表查询
内连接查询
select * from emp,dep where emp.dep_id=dep.id
tab_name inner join tab_name on
select * from emp inner join dep on emp.dep_id=dep.id
左外连接查询（以左边的表为准）
select * from emp left join dep on emp.dep_id=dep.id
右外连接查询（以右边的表为准）
select * from emp right join dep on emp.dep_id=dep.id
子查询（嵌套查询）只能返回单个值才能比较
select Name from student where Age > (select avg(Age) from student); 查询大于平均年龄的同学

表记录的修改
update emp set salary="1000" where name="egon";

3.视图操作
视图就是一条select语句当做一张表来用。即为虚表,一般用处在于安全，让查询者只看到相应的字段。
create view stu as select Name,Age,Gender from student where not Age>20 and not Gender='M';
select * from sct;

4.表的加解锁
读锁：read （共享锁）
写锁：write（独占锁）
例：lock table student read;
解锁：unlock table;

脚本实现mysql命令
mysql -e 'select * from student.student;'