MySql

net start mysql  启动mysql

net stop mysql   停止

mysql -V

mysql -uroot -p（密码）  -P(port 3306)  -h127.0.0.1(服务器名称)

exit  quit \q    退出操作

 select version（）  显示版本号

select now（） 显示当前日期时间

select user（） 显示当前用户

 

创建数据库: create database  if not exists【数据库名称】

show warnings查看错误信息

show create database dbnam 查看数据库创建时的信息

crate database if no exists dbnamo character set gbk;  设置数据库的编码gbk

数据库的修改 ： 修改为utf8编码格式

alter database dbname character set = utf8;

drop database dbname；

记录插入

insert into tb_name [col_name] values (val,…);

insert  into tb1 (name , age) values(‘tom’, 25);

空置与非空

creaete tabel tb2(

username varchar(20) not null,

age tinyint unsigned null  

);

自动编号： 必须定义为主键

id samllint unsigned auto_increment   primary key

 auto_increment  要与  primary key 一起使用

primary key  与 auto_increment  不一定一起使用主键不允许重复

 

每张表可以存在多个唯一约束

id smallest assigned auto_incerment primary key,

username varchar(20) not null  unique key,

age tinyint unsigned

默认值 ：没有明确为字段赋值 自动默认值

sex enum(‘1’，‘2’) default ‘3’

约束：保证数据的一致性和完整性 ：not null 、primary key、unique key 、default 、foreign key

Mysqyl配置文件 default-storage-engine = INNODB(默认存储引擎)

外键参见父表

pid  BIGINT,  (要与外键的数据类型相同)

FOREIGN   KEY (pid) REFERENCES provices(id)

外键约束的参照操作：

cascade:从父表删除或更新自动啥拿出或更新字表中匹配的行

set null：从父表删除或更新行并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

restrict：拒绝对父表的删除或是更新操作

no action：表总SQL的关键字，在MySQL中与RESTRICT相同

修改数据表

新增

alter table tab_name add age tidying unsigned not null default 10; (位于所有列的下面)

alter table tab_name add password varchar(20) not null  after username;  （位于username之后）

删除

alter table user drop truename; （删除一列）

alter table user drop password, drop age (删除多列)

添加主键约束：

alter table tb_name add id smallint unsigned ;

alter table tb_name add constraint PK_tb_name_id primary key(id);

添加唯一约束：

alter table user2 add unique(username);

添加外键约束：

alter user2 add foreign key(pid) reference proviences(id);

添加、删除默认约束

alter table user add age tinyint unsigned not null;

alter table user alter age set default 15;

alter table user alter age drop default;

删除主键约束

alter table user drop primary key;

删除唯一约束 show indexex from user查看信息

alter table user drop  index username;

删除外键约束 show create table user

alter table user drop foreign key user2_idfk_i(外键);

alter table user drop index pid;

修改数据表

修改列定义

alter table tb_name modify id smallint unsigned not null first; (移动到首行)

alter table tb_name modify id tinyint unsigned not null;(修改数据类型)

修改列名称：

alter table tb_name change pid p_id tinyint unsigned not null;

alter table tb_name rename user; (修改表名称)

rename table user3 to user2; (可以为多张数据表更名)

插入记录

insert into tb_name  values(null, ‘tom’, 12 );

insert user set usrname=“name”, password=“123”;

更新记录

update user set age = age +5; （省略条件更新所有表中age记录+5）

update user set age = age +10 where id%2=0;

删除记录： 省略条件删除全部记录

delete from user where id = 6;

查找记录：select

select user.id, user.username from user;

select id as userId(字段取的别名) , username as name from user;

where语句条件查询

查询结果分组 group by

having语句设置分组条件  出现在select条件中或为聚合函数

select sex , age form user group by sex having age>35;

order by语句对查询结果排序

ASC升 DESC降

select * from user order by id desc;

limit语句限制查询数量

select *from user limit 2; （返回两条记录）

select *from user limit 3, 2; (索引3 开始返回2条)

select *from user order by id desc limit 2, 2;

子查询与连接：

any some all

in (not in)

exists (not exists)

create table if not exists tb_name(

cate_id smallest unsigned primary key auto_increment,

cate_name varchar(20) 

);

insert … select

insert tb_name(username) select  goods_name from  tb_name group by goods_name

多表更新：

update tb_name inner join tb_name2  on  tab_name1 = tab_name2 set tab_name2 = cid;

create … select

create table tb_name (

id smallest unsigned primary key auto_increment,

name varchar(20) not null

);

 

update tb_name as  1 inner join tb_name2 as 2 on 1.t_name = 2.t_name2 set t_name2= t_id;

连接：

内连接：仅显示符合条件的记录

join 、inner join 、 cross join

使用on关键字设定连接条件

select tb_id, tb_name from tb1 inner join tb2 on tb2.id = tb1.tb_id, tb2.name = tb1.tb_name;

外链接：

左外.显示左表的全部记录及右表符合连接条件的记录  

右外.显示右表的全部记录以及左表符合连接条件的记录

select tb_id, tb_name from tb1 LEFT join tb2 on tb2.id = tb1.tb_id, tb2.name = tb1.tb_name;

select tb_id, tb_name from tb1 RIGHT join tb2 on tb2.id = tb1.tb_id, tb2.name = tb1.tb_name;

多表连接：

无限级分类表设计：

create table tdb_goods_types(

type_id smallest unsigned primary key auto_increament,

type_name varchar(20) not null,

parent_id smallest unsigned not null default 0  （若同一张表中对应 type_id）

);

自身连接：

select s.type_id, s.type_name, p.type_name from tdb_goods_types AS s left join tdb_goods_types AS p on s.parent_id = p.type_id;

select p.type, p.type_name , s .type_name from  tdb_goods_types as p left join  tdb_goods_types as s on  s.parent_id = p.type_id;

多表删除：

delete t1 from tdb_goods_types as t1 left join  (select goods_id, goods_name from tdb_goods GROUP BY goods_name HAVING  count(goods_name)>= 2 )

 AS t2  ON t1.goods_name = t2.goods_name WHERE t1.goods_id . t2.goods_id;

字符函数：

concat() 字符连接

select CONCAT(‘I’,’-’,’he’ );

select concat(first_name, last_naem ) as full name from test;

concat_ws() 使用指定的分隔符进行字符连接

select concat_ws(‘I’, ’ A’,’B’, ’C’);

select format(1233.12, 2);  数字格式化函数 FORMAT（）；

LOWER（） 转换成小写字母 UPPER（）转换成大写字母

LEFT（） 获取左侧字符

select LEFT（‘asdf’，2）；   as

RIGHT（）获取右侧字符

select RIGHT(‘asdf’, 3); sdf

LENGTH()  获取字符串长度

select length(’sql’);    3

LTRIM（） 删除前导空格

RTRIM（） 删除后续空格

TRIM（）   删除前导和后续空格

SUBSTRING（） 字符串截取

[NOT]LIKE 模式匹配

REPLACE 字符串替换

select (leading’?’ from ‘??mysql??’);  mysql??

select (trailing’?’ from ‘??mysql??’);   ??mysql

select (both’?’ from ‘??mysql??’);      mysql

select replace(‘??my??sql??’, ‘?’, ‘’);   mysql

select ‘musql’ like ‘m%’;      1  true   

select * from test  where first_name like ‘%o%’;  包含o的

select * from test  where first_name like ‘%1%%’ escape ‘1’;  1后的%不起通配符的作用（%代表任意字符 -代表任意已给字符） 看做%

数值运算符和函数

select 3+4;  各数学运算符

CEIL() 进一取整   CEIL(3.01);    4

FLOOR()  舍一取整  FLOOR(3.99);    3

DIV  整数除法  select 3 DIV 4 ;     0

MOD 取余数     select 5 MOD3;     2

POWER（）幂运算 select POWER(3,2);   9

ROUND（） 四舍五入 select ROUND(4.1234,2);     4.12

TRUNCATE（） 数字截取 TRUNCATE(122.123,2);    122.12

TRUNCATE(122.123,0);    120

比较运算符和函数：

NOT BETWEEN…ADN …     (不)在范围

NOT IN() （不）在列出值范围内

IS NOT  NULL 是否为空

日期时间函数：

NOW()  当前日期和时间

CURDATE（）  当前日期

CURTIME（）  当前时间

DATE_ADD()     日期变化 select date_add(“2014-3-13”, interval 365 day);      2015-3-13

select date_add(“2014-3-13”, interval 1 year(weak day等));

DATEDIFF（）  日期差值

DATE_FORMAT（） 日期格式化

date_format(“2016-4-6”, ‘%m/%d/%Y’);   6/4/2016

信息函数

CONNECTION_ID()   连接ID

DATEBASE() 当前数据库

LAST_INSERT_ID()  最后插入记录

USER（）      当前用户

VERSION（）  版本信息

聚合函数：

AVG() 平均值

COUNT（） 计数

MAX（） 最大值

MIN（） 最小值

SUM（） 求和

加密函数：

MD5（） 信息摘要算法（优先使用）

select md5(‘admin’)   32位

PASSWORD（） 密码算法  （修改客户端密码）

password（‘admin’）

set password = password（‘newpw’）；

自定义函数：

create function f1() returns varchar(30)  return date_format(now(), ‘%Y年%m月%d日 %H点：%i分：%s秒’)；

select f1();

create function f2(num1 smallint unsigned, num2 smallint unsigned) returns float(10,2) unsigned return (num1+num2)/2  

select f2(10, 20);

drop function f2;

创建具有复合结构函数体的自定义函数：

delimiter//  自定义mysql结束符号

create function adduser(username varchar(20)) returns int unsigned  begin insert  test(username) values(username);

return last_insert_id();

end  //

select adduser(‘rose’); //

delimiter; 

MySql存储过程：sql语句和控制语句的预编译集合

create procedure sp1（） selection version();

call sp1;

create procedure removeUserById(int id int unsigned)

begin delete from users where id（字段） = id（参数）;

end //

dlimiter;

call removeUserById(3);

drop procedure removeUserById;

create procedure removeUserAndReturnUserNums(IN p_id int unsigned, OUT userNums int unsigned)  begin

delete from users where id = p_id;

select count(id) from user INTO userNums;

end//

call removeUserAndReturnUserNums(27, @nums（变量）);

select @nums(剩余的总数);

存储引擎：MyISAM  InnoDB Memory CSV  Archive

锁：共享锁、 排他锁

锁颗粒： 表锁、行锁

事物：用于保证数据库的完整性 （a原子c一致i隔离d永久）

外键：保证数据一致性的策略

索引：数据表中一列或多列的值进行排序的一种结构

设置搜索引擎：

通过修改mysql配置文件实现

-default-storage-engine = engine

通过创建数据表实现

create table table_name(

)engine= MyISAM;

通过修改数据表命令实现

alter table table_name ENGINE = engine_name;

MySql图形化管理工具：PHPMyAdmin、Navicar、MySQL Workebnch