mysql杂项记录

1.数据库的基本操作

显示数据库：show databases;

使用某个数据库：use databasename;

显示数据库表：show tables;

创建表：create table tablename(名字 类型, 名字 类型, 名字 类型,…….);//可选属性有unsigned,NULL,not NULL,primary key,auto_increment(自动赋值，从1开始，每增加一个记录编+1),unique key,default(默认值)

引用外键：foreign key 列名 references (被参照表) id

父表中删除或更新子表也跟着：cascade

子表操作不影响父表：no action

修改（增加 减少）列：alter table 表名 add 列名 类型,drop列名 类型;

删除表：drop table tablename;

更新行：update 表名 set 列名（修改项）where…;

删除行：delete from tablename where…;

显示表的格式：show columns from tablename;

向表插入数据：insert tb1 values(“”, 数据值, 数据值, 数据值,….);双引号

查询表的数据：select from tablename where …;   limt 0 4 表示从第1个数据后取4条记录

删除表某行：delete from tablename where ….;

把文件数据插入到mysql中: LOAD DATA INFILE 'D:/devices.csv'  INTO TABLE devices;(效率高)

创建索引CREATE INDEX  xxidx  ON  table_name(列名);

 

插入查询的数据 INSERT INTO TABLE_NAME(a,b,c)  SELECT a,b,c  FROM TABLE_B

 

导入sql到mysql:source name.sql

 

数据库备份：mysqldump  -uroot  -ppassword  databasename  >  1.sql（数据库文件）

 

连接数据库: mysql -uroot -p --port=端口

 

修改表结构 ALTER TABLE td_name CHANGE colum1 new_colum1 int [primary] ,

 

colum2 new_colum2 int [primary] ,

 

 

 

mysql远程链接 : use mysql

 

update user set host=’%’ where user = ‘root’ and host =’localhost’;

 

flush privileges;

 

 

 

指定编码和引擎

CREATE TABLE `country` (

  `code` CHAR(2) NOT NULL PRIMARY KEY,

  `name` CHAR(52) NOT NULL,

  `population` INT(11) NOT NULL DEFAULT '0'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

2.查询字段内容的小写

对字段内容进行小写处理，lower();

例子:SELECT lower(sn) FROM ds_equipment WHERE sn like '%00%'

 

3.插入数据避免重复插入

插入数据的时候还要加入一个where+exists条件去查询这条对应的记录是否存在，如果存在则不插入。

其中DUAL表是mysql自动创建的，并不需要特意去理会

例子:

 INSERT INTO `ds_bind` 
(uid, mac_id, status,admin) 
SELECT 763, '442C050D4106',1,1 FROM DUAL 
WHERE not exists (select * from ds_bind 
where uid=763 && mac_id='442C050D4106' && STATUS=1 && admin=1 ); 

 

 

 

4.常用函数

对用户进行小写比对，lower();

比较函数：any(), some()（any和some是一样的）, all()（等价于in）

计算函数：min() ,max(), sum(), count(), avg()          

插入查询的数据: INSERT table_name1(key) SELECT key from table_name2;

 

5.参照其他表来更新本表数据

 

参照其他表来更新本表，多表更新（新表已存在）:

UPDATE tb1 INNER JOIN tb2 on tb1.id=tb2.id SET tb1.key1=tb2.key2;  

 

 

创建表格的同时插入数据（注意红字对应字段）（新表不存在）

CREATE TABLE IF NOT EXISTS tb3(

id int primary key auto_increment,

new_name char(30)

)default charset = utf8 select key as new_name from old table;

 

 

 6.无限级分类

表结构（分类id，分类名字，父类的id）

查询类和该类的父类

SELECT c.type_id,c.type_name,p.type_name FROM table AS c
LEFT JOIN table AS p ON c.parent_id=c.type_id;

 查询类和该类的子类

SELECT p.type_id,p.type_name,c.type_name FROM table AS p
LEFT JOIN table AS c ON p.type_id=c.parent_id;

 

7.删除重复记录

　　如有以下场景删除重复姓名的记录，只保留id较小的那个

　   删除重复记录可以拆分为以下步骤

　　1.查出重复姓名的记录

SELECT id,name FROM table GROUP BY name HAVING count(*) >2;

 　　2.查询重复记录中id较大的

SELECT * FROM table AS a 
INNER JOIN 
(SELECT id,name FROM table GROUP BY name HAVING count(*)>2)
AS b ON a.name=b.name
WHERE a.id>b.id;

 　　3.删除这些记录

DELETE a FROM table AS a
INNER JOIN
(SELECT id,name FROM table GROUP BY name HAVING count(*)>2)
AS b ON a.id=b.id
WHERE a.id>b.id;

 

 8.MySQL常用函数

　　concat() : 连接字段或者字符串函数

SELECT concat(first_name,last_name) AS fullname FROM table;

　　concat_ws() : 连接字符以特定的符号去连接：

--第一个参数是分隔符
SELECT concat_ws('-',first_name,last_name) AS fullname FROM table;

 　　format() : 输出数字格式的字符(三位一个逗号隔开，可以保留多少位小数点)

--保留1位小数点
SELECT format(1234.43,1);
--输出  1,234.4

　　lower(),upper()分别是小写和大写字符串输出。

　　left() 输出字符串的左边开始数起的字符

--输出左边三个字符
SELECT left(3,'MySQL');
--输出  MyS

　　right()同上，只是换了方向

　　length() ： 输出字符串的长度

　　ltrim() ： 去除左边空格

　　rtrim() : 去除右边空格

 　   trim() : 左右空格被去掉

--删除  字符串的前面?符号
SELECT TRIM(LEADING '?' FROM '??MySQL??');
--输出  MySQL??

--删除  字符串的后面?符号
SELECT TRIM(TRAINING '?' FROM '??MySQL??');
--输出  ??MySQL

--删除  字符串的两边?符号
SELECT TRIM(BOTH '?' FROM '??MySQL??');
--输出 MySQL

　　replace() : 把特定字符换成某些字符

--把? 替换成 -
SELECT replace('My?SQL','?','-');
--输出 My-SQL

　　substring() : 截取字符

--截取字符第一位 到 第三位，注意MySQL的首字符不是0开始
SELECT substring('MYSQL',1,3);
--输出 MYS

　　ceil() : 向上取整

　　floor():向下取整

　　round() : 四舍五入几位

　　MOD : 取余数

　　div : 整数相除

　　power() : 幂运算

　　between and ： 区间判断

　　last_insert_id() ：上一次插入的记录id

　　from_unixtime(time,'%Y-%m-%d %H:%i:%s') : 转换unix时间戳为格林时间

　　unix_timestamp(date); 　　转换格林时间为时间戳

　　

 9.MySQL事务

　　事务是用于处理一系列原子性的操作，确保一系列操作都是正确的，如果其中一个出现了错误可以回滚到原来的状态。

　　事务开始：btgin 或START TRANSACTION　　

　　事务结束：end

　　自动提交：autocommit ，默认设置为autocommit=1，即每条SQL语句都是一个单独的事务，如果为0的话，即自动开启事务，在执行完SQL语句之后不commit的话事务都会被抛弃

　　存档：savepoint point_name ;

　　回滚到存档点：rollback to point_name;（存档点后的事务都被忽略，只有存档前的都保留下来）

--只执行前两条插入语句，如果不提交comit都不会生效
BEGIN;
INSERT ds_activity_sevenstar VALUES(1,1,2);
INSERT ds_activity_sevenstar VALUES(1,1,2);
SAVEPOINT point1;
INSERT ds_activity_sevenstar VALUES(2,2,3);
ROLLBACK to point1;
COMMIT;

 10.MySQL的索引

　　主键索引：通过给列添加主键来创建索引，多见在创建表格的时候添加

ALTER TABLE ds_activity_sevenstar ADD PRIMARY KEY(uid);

　　唯一索引：通过给列添加唯一约束创建索引

ALTER TABLE ds_activity_sevenstar ADD UNIQUE(step);

　　普通索引：

ALTER TABLE ds_activity_sevenstar ADD index(step);

　　复合索引：

ALTER TABLE ds_activity_sevenstar ADD index(step,prize);

　　全文索引：适用于char，varchar，

ALTER TABLE ds_activity_sevenstar ADD FULLTEXT(test2);

　　外键索引：只能