mysql 基本语法

创建好表后设置字段不重复

ALTER TABLE `t_user` ADD UNIQUE KEY(`name`);  -- 唯一约束
ALTER TABLE `t_user` ADD UNIQUE KEY(`phone`,`mail`); -- 联合唯一约束

id为2的password字段的值改为md5

update users set password=md5(password) where id=2;

查询语句

mysql中查询字段为null或者不为null

在mysql中，查询某字段为空时，切记不可用 = null，
而是 is null，不为空则是 is not null ; column字段;

select * from table where column is null; 
select * from table where column is not null;

多表查询：

嵌套查询（一个查询的结果是另外查询的条件）
where关联查询
join连接查询：内联inner join，左联 left join，右联right join
嵌套查询：

数据的DQL操作：数据查询

    格式：
        select [字段列表]|* from 表名
        [where 搜索条件]
        [group by 分组字段 [having 子条件]]
        [order by 排序 asc|desc]
        [limit 分页参数]

- 统计函数（聚合函数）max() min() sum() avg() count()
- 获取最大年龄，最小年龄，年龄总和，平均年龄，总计条数

    select max(age),min(age),sum(age),avg(age),count(id) from stu;


concat 函数用于将两个字符串连接起来，形成一个单一的字符串
>    select concat(classid,' : ',name) from stu;
在表后面加一列city
>    select *,'shanghai' city from stu;
正则查询 4位小写字母


select * from stu where name regexp '^[a-z]{4}$';

查10条数据 table1 表名

select * from table1 limit 10

like模糊查询

like模糊查询 ‘%’多个字符 ‘_’单个字符

下面介绍mysql中模糊查询的四种用法： 

1，%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。 
比如 
    SELECT * FROM [user] WHERE u_name LIKE '%三%' ;
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。 

另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件 
    SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE ‘%猫%’;
 
若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%' 
虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。 

2，_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句： 
比如 
    SELECT * FROM [user] WHERE u_name LIKE '_三_' ;
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的； 

再比如 
    SELECT * FROM [user] WHERE u_name LIKE '三__'; 
    只找出“三脚猫”这样name为三个字且第一个字是“三”的； 

3，[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。 
比如 
    SELECT * FROM [user] WHERE u_name LIKE '[张李王]三' ;
    将找出“张三”、“李三”、“王三”（而不是“张李王三”）； 
如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e” 
    SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’;
    将找出“老1”、“老2”、……、“老9”； 

4，[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。 
比如 
    SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三' ;
    将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
 
    SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; 
    将排除“老1”到“老4”，寻找“老5”、“老6”、…… 

5，查询内容包含通配符时 
由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数： 
function sqlencode(str) str=replace(str,"';","';';") 
str=replace(str,"[","[[]") ';此句一定要在最先 str=replace(str,"_","[_]") str=replace(str,"%","[%]") sqlencode=str end function

一、概念：

数据： data
数据库： DB
数据库管理系统：DBMS
数据库系统：DBS
MySQL：数据库
mysql：客户端命令（用来连接服务或发送sql指令）
SQL：结构化查询语言，其中MySQL支持这个。
SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、
DCL（控制）

数据库基本操作

SQL语句中的快捷键

\G 格式化输出（文本式，竖立显示）
\s 查看服务器端信息
\c 结束命令输入操作
\q 退出当前sql命令行模式
\h 查看帮助

二. 连接数据库：

mysql -h 主机名 -u 用户名 -p密码库名

   C:\>mysql  --采用匿名账号和密码登陆本机服务
   C:\>mysql -h localhost -u root -proot   --采用root账号和root密码登陆本机服务
   C:\>mysql -u root -p   --推荐方式默认登陆本机
     Enter password: ****

   C:\>mysql -u root -p lamp61  --直接进入lamp61数据库的方式登陆

三. 授权：

格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';

实例：创建zhangsan账号，密码123，授权lamp61库下所有表的增/删/改/查数据,来源地不限

mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified by '123';
mysql> flush privileges;  # 生效

创建admin账号，密码123, 所有ip都可以访问('%'), 所有库和表( all on .)

mysql> grant all on *.* to admin@'%' identified by '123';
mysql> flush privileges;  # 生效
Query OK, 0 rows affected (0.00 sec)

四. SQL基本操作

查看数据库

show databases;

查看建库语句

show create database mydb;
创建数据库mydb

create database mydb;

解决重复创建数据库报错,尝试创建

mysql> create database if not exists mydb;
Query OK, 1 row affected, 1 warning (0.00 sec)
create database if not exists mydb;
查看当前所有数据库位置：NULL表示没有在任何数据库中

select database();
mydb数据库的表的基本操作

切换到mydb数据库

use mydb;

查看数据表

show tables;

创建数据表

create table user(id int,name varchar(16),age int);

查看数据表

desc users;

查看user创建语句

 show create table user\G

删除user数据表

drop table user;

查看数据表结构

desc user;

给所有字段添加值

1. insert into user(id,name,age) values(1,'zhou',22);
2. insert into 数据库名 values(2,'chen',22);

3. mysql> insert into user values
     -> (4,'wang',25),
    -> (6,'ji',26),
    -> (7,'ru',25);

查看user表的所有数据

select * from user;

查看user和age字段的所有数据

select name,age from user;

修改表user,将id值为4的信息name改为bie（修改表）

update user set name=bie where id=4;

Mysql数据库修改&添加&删除

Mysql数据库添加:

1.1 把boss_job表的数据导入到job_51j里最后一个字段没有逗号

insert into `job_51`(`url`, `pname`, `smoney`, `emoney`, `location`, `syear`, `eyear`, `degree`, `ptype`, `tags`, `date_pub`, `advantage`, `jobdesc`, `jobaddr`, `company`, `crawl_time`, `num`)
select `url`,
       `pname`,
       `smoney`,
       `emoney`,
       `location`,
       `syear`,
       `eyear`,
       `degree`,
       `ptype`,
       `tags`,
       `date_pub`,
       `advantage`,
       `jobdesc`,
       `jobaddr`,
       `company`,
       `crawl_time`,
       `num`      
  from `boss_job`

Mysql数据库删除:

2.1删除数据库

drop database mydb;

2.2删除qiji_job_t表里,pname字段为空的

    delete from `qiji_job_t`  where `pname`  is null;

2.3删除qiji_job_t表里,pname字段为不为空的
- PS:谨慎使用!!!

    delete from `qiji_job_t`  where `pname`  is not null;

Mysql数据库修改:

待续...

MySQL数据库的数据类型

MySQL的数据类型分为四大类：数值类型、字串类型、日期类型、NULL。

5.1 数值类型：

    *tinyint(1字节)0~255  -128~127
    smallint(2字节)
    mediumint(3字节)
    *int(4字节)
    bigint(8字节)
    *float(4字节)   float(6,2)
    *double(8字节)  
    decimal(自定义)字串形数值

5.2 字串类型

    普通字串
    *char  定长字串      char(8)  
    *varchar 可变字串 varchar(8)
    
    二进制类型
    tinyblob
    blob
    mediumblob
    longblob
    
    文本类型
    tinytext
    *text      常用于<textarea></textarea>
    mediumtext
    longtext
    
    *enum枚举
    set集合

5.3 时间和日期类型：

    date  年月日
    time  时分秒
    datetime 年月日时分秒
    timestamp 时间戳
    year 年

5.4 NULL值

    NULL意味着“没有值”或“未知值”
    可以测试某个值是否为NULL
    不能对NULL值进行算术计算
    对NULL值进行算术运算，其结果还是NULL
    0或NULL都意味着假，其余值都意味着真

MySQL的运算符：

    算术运算符：+ - * / % 
    比较运算符：= > < >= <= <> != 
    数据库特有的比较：in，not in, is null,is not null,like, between and 
    逻辑运算符：and or not

表的字段约束：

    unsigned 无符号(正数)
    zerofill 前导零填充
    auto_increment  自增
    default 默认值
    not null  非空
    PRIMARY KEY 主键 （非null并不重复）一个表只能有一个
    unique 唯一性   （可以为null但不重复）
    index 常规索引

建表语句格式：

 create table 表名(
   字段名 类型 [字段约束],
   字段名 类型 [字段约束],
   字段名 类型 [字段约束],
   ...
  );

mysql> create table stu(
    -> id int unsigned not null auto_increment primary key,
    -> name varchar(8) not null unique,
    -> age tinyint unsigned,
    -> sex enum('m','w') not null default 'm',
    -> classid char(8)
    -> );
Query OK, 0 rows affected (0.05 sec)

修改表结构

格式： alter table 表名 action（更改选项）;
1. 添加字段：alter table 表名 add 字段名信息
    --1. 在tt表末尾添加一个phone字段，类型varchar(11)，无其他约束
    mysql> alter table tt add phone varchar(11);
    --2. 在tt表中age字段后添加一个address字段，类型varchar(100) ，无其他约束
    mysql> alter table tt add address varchar(100) after age;
    --3. 在tt表首位插入一个mm字段，类型int
    mysql> alter table tt add mm int first;

2. 删除字段：alter table 表名 drop 被删除的字段名
    --修改name字段名为username
    mysql> alter table tt change name username varchar(16);
3. 修改字段：alter table 表名 change[modify] 被修改后的字段信息
6. 更改AUTO_INCREMENT初始值:
        ALTER TABLE 表名称 AUTO_INCREMENT=1
7. 更改表类型：
        ALTER TABLE 表名称 ENGINE="InnoDB"

备份&日志&恢复&

基本备份恢复

备份mydb数据库,

# mysqldump -u root -p mydb > mydb.sql

备份mydb下的stu0表

# mysqldump -u root -p mydb stu0 > mydb_stu0.sql

恢复mydb数据库

mysql -u root -p mydb < mydb.sql  数据库中必须先有mydb数据库

mysql日志

开启日志： 在mysql配置文件中开启：log-bin=mysql-bin

查看bin-log日志:
mysql>show binary logs;

查看最后一个bin-log日志:
mysql>show master status;

此时就会多一个最新的bin-log日志
mysql>flush logs;

查看最后一个bin日志.
mysql>show master status;

mysql>reset master;
清空所有的bin-log日志
执行查看bin-log日志

备份数据:
mysqldump -uroot -pwei test -l -F >/tmp/test.sql
其中：-F即flush logs，可以重新生成新的日志文件，当然包括log-bin日志 

// Linux关闭MySQL的命令
$mysql_dir/bin/mysqladmin -uroot -p shutdown
// linux启动MySQL的命令
$mysql_dir/bin/mysqld_safe &

有关慢查询操作：

开户和设置慢查询时间:
vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
查看设置后是否生效
mysql> show variables like "%quer%"; 
慢查询次数:
mysql> show global status like "%quer%";

数据库的恢复

1. 首先恢复最后一次的备份完整数据
[root@localhost mnt]# mysql -u root -p mydemo<mydemo_2017-7-26.sql 
Enter password: 

2. 查看bin-log日志
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000009;
  查找到恢复的节点
  
3. 执行bin-log日志文件，恢复最后一块的增量数据。 
[root@localhost data]# mysqlbinlog --no-defaults --stop-position="802" mysql-bin.000009|mysql -u root -p123456 mydemo;

MySQL的其他操作

1.MySQL的表复制

复制表结构
mysql> create table 目标表名 like 原表名;
复制表数据
mysql> insert into 目标表名 select * from 原表名;

2.数据表的索引

创建索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引
DROP INDEX index_name ON talbe_name

3.mysql视图

创建视图:
mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK, 0 rows affected (0.00 sec)

view视图的帮助信息:
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

查看视图:
mysql> show tables;

删除视图v_t1:
mysql> drop view v_t1;

4.MySQL的内置函数

字符串处理函数

*concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串
insert(str,x,y,instr)将字符串str从第xx位置开始，y字符串的子字符串替换为字符串str
lower(str)将所有的字符串变为小写
upper(str)将所有的字符串变为大写
left(str,x)返回字符串中最左边的x个字符
rigth(str,y)返回字符串中最右边的x个字符
lpad(str,n,pad)用字符串pad对str最左边进行填充，直到长度为n个字符串长度
rpad(str,n,pad)用字符串pad对str最右边进行填充，直到长度为n个字符串长度
trim(str)  去掉左右两边的空格
ltrim(str) 去掉字符串str左侧的空格
rtrim(str) 去掉字符串str右侧的空格
repeat(str,x)   返回字符串str重复x次
replace(str,a,b)将字符串的的a替换成b
strcmp(s1,s2)   比较字符串s1和s2
substring(s,x,y)返回字符串指定的长度
*length(str)  返回值为字符串str 的长度

数值函数

*abs(x)    返回x的绝对值
ceil(x)   返回大于x的最小整数值
floor(x)  返回小于x的最大整数值
mod(x,y)  返回x/y的取余结果
*rand()    返回0~1之间的随机数
*round(x,y)返回参数x的四舍五入的有y位小数的值
truncate(x,y) 返回x截断为y位小数的结果

日期和时间函数

curdate()  返回当前日期,按照’YYYY-MM-DD’格式
curtime()  返回当前时间,当前时间以'HH:MM:SS' 
*now()      返回当前日期和时间,
*unix_timestamp(date) 返回date时间的unix时间戳
from_unixtime(unix_timestamp[,format])  返回unix时间的时间
week(date)      返回日期是一年中的第几周
year(date)      返回日期的年份
hour(time)      返回time的小时值
minute(time)    返回日time的分钟值
monthname(date) 返回date的月份
*date_fomat(date,fmt) 返回按字符串fmt格式化日期date值
date_add(date,INTERVAL,expr type) 返回一个日期或者时间值加上一个时间间隔的时间值
*datediff(expr,expr2)   返回起始时间和结束时间的间隔天数

//统计时间戳647583423距离当前时间相差天数（生日天数（不考虑年份））

mysql> select datediff(date_format(from_unixtime(647583423),"2017-%m-%d %h:%i:%s"),now());

其他常用函数

*database() 返回当前数据库名
version()   返回当前服务器版本
user()      返回当前登陆用户名
inet_aton   返回当前IP地址的数字表示 inet_aton("192.168.80.250");
inet_ntoa(num) 返回当前数字表示的ip   inet_ntoa(3232256250);
*password(str)  返回当前str的加密版本
*md5(str)      返回字符串str的md5值

5.MySQL的事务处理

    关闭自动提交功能（开启手动事务）
    mysql> set autocommit=0;
    从表t1中删除了一条记录
    mysql> delete from t1 where id=11;
    此时做一个p1还原点:
    mysql> savepoint p1;
    再次从表t1中删除一条记录:
    mysql> delete from t1 where id=10;
    再次做一个p2还原点:
    mysql> savepoint p2;
    此时恢复到p1还原点，当然后面的p2这些还原点自动会失效: 
    mysql> rollback to p1;
    退回到最原始的还原点:
    mysql> rollback;
    回滚
    
    mysql> commit;
    事务提交
    
    开启自动事务提交（关闭手动事务）
    mysql> set autocommit=1;

6.MySQL的触发器

格式：1、触发器的定义：

  CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

说明：

# trigger_name：触发器名称
# trigger_time:触发时间，可取值：BEFORE或AFTER
# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
# tb1_name：指定在哪个表上
# trigger_stmt：触发处理SQL语句。

示例：

mysql> delimiter $$

mysql> create trigger del_stu before delete on stu for each row
    -> begin
    ->  insert into stu_bak values(old.id,old.name,old.sex,old.age,old.addtime);
    -> end;
    -> $$
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;

密码重置

如果忘记了 MySQL 的 root 密码，可以用以下方法重新设置：

Linux：

1. KILL掉系统里的MySQL进程；
killall -TERM mysqld
2. 用以下命令启动MySQL，以不检查权限的方式启动；
    mysqld_safe --skip-grant-tables &

3. 然后用空密码方式使用root用户登录 MySQL；
 mysql -u root

设置数据库密码
方法一：
在mysql系统外，使用mysqladmin

# mysqladmin -u root -p password "test123"
Enter password: 【输入原来的密码】

方法二：
通过登录mysql系统，

# mysql -uroot -p

Enter password: 【输入原来的密码】
mysql>use mysql;
mysql> update user set password=passworD("test") where user='root';
mysql> flush privileges;
mysql> exit;
## 查看mysql 的用户
mysql> select Host,User,Password from user;

mac:

mac系统中搭建apache+mysql+php的开发环境，安装mysql后，登录报错：mac ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

解决方法：
第一步：如果mysql服务正在进行，将之停止。
第二步：在终端中以管理员权限启动mysqld_safe，命令如下：

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

第三步：不要关闭当前的终端窗口，新建一个终端窗口，输入如下命令，回车登录mysql
/usr/local/mysql/bin/mysql

下一步：
设置数据库密码

    /usr/local/mysql/bin/mysqladmin -uroot password 123456

然后就可以用123456登陆了

设置mysql 在终端下给mysql 和 mysqladmin 起别名

    alias mysql=/usr/local/mysql/bin/mysql

    alias mysqladmin=/usr/local/mysql/bin/mysqladmin

最后编辑于：2018.05.10 11:05:39

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,547评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,399评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,428评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,599评论 1赞 274
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,612评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,577评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,941评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,603评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,852评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,605评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,693评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,375评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,955评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,936评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,172评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,970评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,414评论 2赞 342