Maria-DB

mysql客户端可用选项：
　　-A, --no-auto-rehash 禁止补全
　　-u, --user= 用户名,默认为root
　　-h, --host= 服务器主机,默认为localhost
　　-p, --passowrd= 用户密码,建议使用-p,默认为空密码
　　-P, --port= 服务器端口
　　-S, --socket= 指定连接socket文件路径
　　-D, --database= 指定默认数据库
　　-C, --compress 启用压缩
　　-e “SQL“ 执行SQL命令
　　-V, --version 显示版本
　　-v --verbose 显示详细信息
　　--print-defaults 获取程序默认使用的配置

 

安全模式：

　　mysql  -uroot -pcentos -U  安全模式进入，避免批量操作，没有where条件会报错

　　可以将-U选项写入alisa 或者脚本里或者写入/etc/my.cnf.d/client.cnf

　　vim /etc/my.cnf.d/client.cnf　

　    [client]

　　safe_updates

 

创建远程用户

create user wang@'192.168.1.%'  identified by 'centos';   创建用户wang只能从192.168.1网段登陆，密码为centos，也可以指定只能某IP地址登陆

用户远程连接

mysql -uwnag -h'192.168.1.100' -pcentos -P3306；    连接IP地址为192.168.1.100端口为3306的数据库

 

 删除用户：

　　delete from mysql.user where user='wang';

 

在/etc/my.cnf.d/client.cnf  里添加账户和密码，将这组账号密码设置为默认登陆设置，登陆时可省去密码和账户，默认以这组账号密码登陆

mysql --print-defaults； 可以查看默认账号密码

 　　update user set password=password('centos') where user='root';修改密码，password('centos')加密centos，在数据库中不明文显示

在每次修改过数据库权限以后，都要刷新以启用新权限

 

授权：

　　grant  select,delete on 　　

　　grant all on *.* to wang@'192.168.1.%' identitied by 'centos';  授权wang可以在这个网段登陆，拥有所有数据库和表的所有权限

　　show grants for wang@'192.168.1.%';  查看wanng的授权

　　revoke delete on *.* from 'wang'@'192.168.1.%;   删除wang在所有库和表的delete权限

在每次修改过数据库权限以后，刷新以确认新权限启用

 

 

 

 

 

支持通配符：

　　% 匹配任意长度的任意字符

　　_ 匹配任意单个字符

 

 

 

SQL语言规范

　　在数据库系统中，SQL语句不区分大小写(建议用大写)                      库名表名对大小写敏感

　　但字符串常量区分大小写

　　SQL语句可单行或多行书写，以“;”结尾

　　关键词不能跨多行或简写

　　用空格和缩进来提高语句的可读性

　　子句通常位于独立行，便于编辑，提高可读性

　　注释：

　　　　SQL标准：

　　　　　　/*注释内容*/ 多行注释

　　　　　　-- 注释内容 单行注释，注意有空格

　　　　MySQL注释：

　　　　　　#

 

 　  select * mysql.user\G ;     竖列显示

　　create database  [if not exists] 'db_name';  　　创建数据库[如果不存在就创建]

　　drop database  [if exists]  'db_name'; 　　　　 删除数据库[如果存在就删除]

　　CHARACTER SET 'character set name';   　  创建数据库时设定字符集

　　show variables like '%character_set%'; 　　   查看默认字符集

　　SHOW COLLATION　　　　　　　　　　　  查看排序规则

　　create table [if not exists] 'table_name' (col1 type1 修饰符，col2 type2 修饰符，...)

　　show table status from mysql\G; 　　显示mysql数据库里面所有的表结构，竖列显示

　　show table status like 'table'\G;       查看表信息

　　show engines;　　　　　　　　查看数据库支持的引擎

　　mysql -uroot -pcentos -S /mysqldb/3306/socket/mysql.sock  -e 'show databases;'  不进入数据库执行命令

　　desc tlb_name;查看表格式

　　select name as 姓名 from statudent; 　　使用as取别名，支持中文

 

　　将数据库里的表文件独立放置

　　vim /mysqldb/3306/etc/my.cnf 

　　在[mysqld]下添加  innodb_file_per_table   

　　导入数据库文件，每个表将独立存放

 

 

数据类型：

1、整型

　　tinyint(m) 1个字节 范围(-128~127)

　　smallint(m) 2个字节 范围(-32768~32767)

　　mediumint(m) 3个字节 范围(-8388608~8388607)

　　int(m) 4个字节 范围(-2147483648~2147483647)

　　bigint(m) 8个字节 范围(+-9.22*10的18次方)

　　取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)

　　int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

　　BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假。非zero值视为真

 

2、浮点型(float和double)，近似值　　

　　float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位

　　double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位

　　设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

 

3、定点数

　　在数据库中存放的是精确值,存为十进制

　　decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位

　　MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节小数点后的数字用4个字节，小数点本身占1个字节，

　　浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节　

　　因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

 

4、字符串(char,varchar,_text)

　　char(n) 固定长度，最多255个字符

　　varchar(n)可变长度，最多65535个字符

　　tinytext可变长度，最多255个字符

　　text 可变长度，最多65535个字符

　　mediumtext 可变长度，最多2的24次方-1个字符

　　longtext可变长度，最多2的32次方-1个字符

　　BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

　　VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

　　内建类型：ENUM枚举, SET集合

　　　　　ENUM枚举：单选其中一项

　　　　　SET集合：多选，可以随意选几项　　 

修饰符：

　　所有类型：

　　　　NULL 数据列可包含NULL值

　　　　NOT NULL 数据列不允许包含NULL值

　　　　DEFAULT 默认值

　　　　PRIMARY KEY 主键

　　　　UNIQUE KEY 唯一键　　

　　　　CHARACTER SET name 指定一个字符集　　

　　数值型：

　　　　AUTO_INCREMENT 自动递增，适用于整数类型

　　　　UNSIGNED 无符号

示例：

　　CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);

　　DESC students;

　　CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));　　

 

 

 

表操作：

　　desc 'tbl_name'; 　　　　　　　　　　查看表格式

　　DROP TABLE [IF EXISTS] 'tbl_name';   删除表

　　ALTER TABLE 'tbl_name' 　　　　　　修改表

　　　　字段：

　　　　　　添加字段：add

　　　　　　ADD col1 data_type [FIRST|AFTER col_name]

　　　　　　删除字段：drop

　　　　　　修改字段：

　　　　　　alter（默认值）, change（字段名）, modify（字段属性）

　　　　索引:

　　　　　　添加索引：add index

　　　　　　删除索引: drop index

　　create index index_name on students(name);   在students表的name字段上添加索引，名为index_name 　　

　　drop index index_name on students;   在students表上删除索引名为index_name

　　truncate table students;　　　　删表，速度最快的方式

 

　　create table newstudents select * from students; 根据旧表创建新表，但是会丢失主键和一些信息，原理是根据查询结果创建新表

　　

　　create database students;  　　创建数据库，

　　use students;　　　　切换数据库

　　create table class1 (id int primary key,name char(20) not null , sex enum('m','f') default 'm'); 创建表

　　desc class1;　　查看表信息

　　insert class1 (id,name) values (1,'mage');     添加表记录

　　insert class1(id,name) values (2,'zhaohuan') ,(3,'liuhuan');

　　insert class1(id,name,sex) values (4,'lili','m') ,(5,'zhanghuan','m');

　　update class1 set sex='f' where id=4;   修改id=4的sex记录

　　delete from class1 where id=1; 删除id=1的记录

　　select * from students order by age; 默认asc 正序，desc倒序

　　select * from students order by age asc;

　　排序中NULL值默认优先最高，会排在最前面，在查询值前加-即可处理为最低优先级，

　　如：select * from students order by -age asc;

　　select * from class1 order by age limit 5;   最多显示5行

　　select * from class1 order by age limit 3,5;   跳过前三个，再显示4个

　　select id,name 姓名 from class1 where id between 10 and 20；显示表里id在10和20之间的id,name两项，name使用了别名，省略了as

　　select id,name 姓名 from class1 where id >=10 and id <=20; 意义同上

　　select id,name 姓名 from class1 where name like 'x%'; 模糊匹配需要通配符_%配合

 

WHERE子句：指明过滤条件以实现“选择”的功能：

　　过滤条件：布尔型表达式

　　算术操作符：+, -, *, /, %

　　比较操作符：=, !=, <>, <=>, >, >=, <, <=　　

　　BETWEEN min_num AND max_num

　　IN (element1, element2, ...)

　　IS NULL

　　　　select * from students where classid is null

　　IS NOT NULL

　　　　select * from students where classid is not null

 LIKE: 

　　%: 任意长度的任意字符

　　 _：任意单个字符

　　RLIKE：正则表达式，索引失效，不建议使用

　　REGEXP：匹配字符串可用正则表达式书写模式，同上

　　逻辑操作符： NOT,AND,OR,XOR

 

函数计算：

　　select sum(score) from scores where stuid=1;　计算学号为1的同学的总分数

　　select courseid,avg(score) from scores group by courseid;计算每个科目的平均成绩

　　select courseid,abg(score) from scores group by courseid having crouseid=1;查看科目1的平均成绩

　　　　group by    ....    having .... 语法固定

　　select courseid,abg(score) from scores group by courseid having crouseid in （1，3）查看科目1和3的平均成绩

　　

内连接：

　　select st.name,sc.score from students as st,scores as sc where st.stuid=sc.stuid;　　

　　select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid;   其他写法用了inner不能用where

　　select st.name,sc.score from students as st join scores as sc on st.stuid=sc.stuid;  inner可以省略

 

　　select st.name,sc.score from students as st,scores as sc where st.stuid=sc.stuid and sc.score> 90;大于90分以上的学生

 

外连接：

　　select st.name,sc.score from students as st right outer join scores as sc on st.stuid=sc.stuid;　右外连接，显示右边表所有内容

　　select st.name,sc.score from students as st left outer join scores as sc on st.stuid=sc.stuid;   左外连接，显示左边表所有内容

 

自连接：

　　select e2.name,e1.name as 上司 from emp as e1 inner join emp as e2 on e1.id=e2.leaderid; 

　　

union：将两个不同结构的表合并输出显示，搜索的项必须是相同的

　　select t.tid,t.name from teachers as t union select s.stuid,s.name from students as s;

 cross join:  由两个表生成第三个表，并添加了空白的评分列

　　select c.course,t.name," " as 评分 from teachers as t cross join courses as c;

子查询：查询显示比id=9的同学年纪更大的同学

　　select * from students where age > (select age from students where stuid=9);