mysql学习笔记

Mysql命令
Show tables;
Desc pet;

Create table pet (name varchar(20), birthday date);
导入数据
Load data local infile "pet.txt" Into table pet

Select * from pet where  name regexp "^...$"  #查找名字是3个字符的

2）Select * from pet where name regexp ".{n}$"  #{n}表示repeat-n-time'

Select  * from pet where name regexp "fy$"  #查找以fy结尾的名字列表
 

Mysql中常用的函数
1 与日期相关
  To_days()   year()  month()

2 与字符串相关
  Substring()  left()  right()

3 聚集函数
 Max()  min()  avg()  bit_count()

》The following shows an idea of how you can use the bit group functions to calculate the number of days per month a user has visited a web page.
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;
 
Which returns:
 
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

》批量模式查询mysql
Shell > mysql -u root -p  <batchfile | more
或者输出到outfile中
Shell> mysql -u root -p  < batchfile > outfile

Mysql用户管理