关于mysql的杂记
- 认识和安装 mysql
- 库表行列的概念
database
table
row
column - 入门语句
- 列类型简单讲解
- 创建简单表
- 增删改查 基本语句
安装
- Mysql Server
- Mysql Client
基本命令
连接msyql server
mysql -h localhost -u Usr -p Passwd
命令行结束标志
分号 ; 或者 \g
库相关命令
说明 | 命令 | 备注 |
---|---|---|
查看 | show databases; | |
创建 | create database dbname [charset ] | |
用库 | use dbname | |
删库 | drop database dbname |
mysql安装完后,默认有以下几个数据库:
information_schema 数据库服务器的基本信息
mysql mysql配置及用户的基本信息
performance_schema 性能优化的基本信息
test test
不能rename database
- 表相关命令
说明 | 命令 | 备注 |
---|---|---|
show tables tbname; | ||
drop table tbname; | ||
rename table oldName to newName; | ||
create table worker( //名称 属性 初始值 id int primary key auto_increment, name char(3) not null default ”, age tinyint unsigned default 0, address varchar(120) not null default ”, salary decimal(6,2) not null default ‘1234.56’ )engine=innodb default charset utf8; |
- 表字段NULL
- 不是真 不是假, 而是 空
- 任何运算符遇到null 都是NULL
- NULL的判断只能是 is null, is not null
- NULL影响查询速度,一般避免值用NULL
mysql> select null = 0;
+----------+
| null = 0 |
+----------+
| NULL |
+----------+
1 row in set (0.01 sec)
mysql> select null <> 0;
+-----------+
| null <> 0 |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> select null <> null;
+--------------+
| null <> null |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
- 无密码登录
- close process mysql
- mysqld –skip-grant-tables
- mysql is reopen, and not check authority
- mysql -uroot ; //not pwd login;
表增删改查
- 插入
insert
建表使用utf8,而cmd-windows用了gbk,
因此,需要声明字符集,set names gbk;
- 查询
select * from test;
显示表格详细信息
- 删除
delete
- 更新
update tbl set tbl.a=val where tbl conditions
- where
select id, name from test where id > 3;
- alter 修改表格列项
alter table test add age tinyint unsigned;
创建库===库里包含各种表;
建表
- 建表就是声明列的过程;
- 列的创建:就是声明类型 大小 初始值;
不同的列占的空间不一样,
选列的原则:够用又不浪费;
列的类型
数值型
整形
格式 大小 有符号范围 无符号范围 tinyint 1byte -128~~127 0~~255 smallint 2bytes -32768~~32767 mediumint 3bytes -2^23~~2^23-1 0~~2^24-1 int 4bytes -2^31~~2^31-1 0~~2^32-1 bigint 8bytes -2^63~~2^63-1 0~~2^64-1
说明:
默认都是,有符号的 -2^...2^-1
tinyint(M) unsigned zerofill;
unsigned 无符号;
M 代表宽带,在zerofill时才有意义; 00001 00003;...当M小于真实数字大小时,不影响显示!
zerofill: 零填充(用了该熟悉,默认该列是无符号的);
浮点型
float(M,D); m总个数 a小数点个数;
float(3,1); -999.9~~999.9 小数点后面超1位的四舍五入;
最末是5的话银行根据前面是1379入,2468舍; 如下testmd 表格;
该规则对decimal不生效; 它依据四舍五入;float(4,2) unsigned 0~~99.99;
定点型
decimal(M,D);
//允许小数点右侧超a个,不允许小数点左侧超 更精确
浮点型 定点型根据系统 要么4个字节要么8个字节
- 举例
mysql> create table testMD(
-> id int primary key auto_increment,
-> fsa float(4,2) zerofill not null default '0.0',
-> dbl decimal(4,2) zerofill not null default '0.0',
-> msg char(12) not null default '')charset utf8;
mysql> desc testmd;
+-------+--------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| fsa | float(4,2) unsigned zerofill | NO | | 0.00 | |
| dbl | decimal(4,2) unsigned zerofill | NO | | 00.00 | |
| msg | char(12) | NO | | | |
+-------+--------------------------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
mysql> insert into testmd
-> (fsa, dbl, msg)
-> values
-> (10.111, 10.111, '10.111 ~'),
-> (10.115, 10.115, '10.115 ~'),
-> (10.116, 10.116, '10.116 ~'),
-> (10.125, 10.125, '10.125 ~');
Query OK, 4 rows affected, 4 warnings (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 4
mysql> select * from testmd
-> ;
+----+-------+-------+----------+
| id | fsa | dbl | msg |
+----+-------+-------+----------+
| 1 | 10.11 | 10.11 | 10.111 ~ |
| 2 | 10.12 | 10.12 | 10.115 ~ |
| 3 | 10.12 | 10.12 | 10.116 ~ |
| 4 | 10.12 | 10.13 | 10.125 ~ |
+----+-------+-------+----------+
4 rows in set (0.00 sec)
字符型
- char(M)
定长 M,0<=M<=255; 可容纳的字符数;
利用率:实占M个字符 i/M <=100%;
不够M个字符用空格补齐! 存入的数据尾部有空格会被丢失
速度比varchar快 - varchar(M)
变长M,0<=M<=65535; 可容纳的字符数;
利用率:实占N个字符(N<=M) i/i+(1~2) < 100%;
以ascii 为例最多2200个utf8字符;跟具体字符有关;
不会丢失空格
select concat(name, ‘!’), concat(waihao, ‘!’) from msg where ..; - text
不能全文索引, 较大的文本,搜索速度慢,没default value;
时期/时间型
year
1bytes 1901-2155,0000
输入2位: 00-69 表示2000-2069;
‘00’ 才表示2000 , 0->0000;
70-99 表示1970-1999;date
0000-00-00 ‘1000-01-01’~~> ‘9999-12-31’
mysql会自动检测错误的时间;time
hh:mm:ss
‘-838:59:59’~~’838:59:59’
不止表示时间还包括时间差
会识别错误时间 eg:13:60:00datetime
‘yyyy-MM-dd hh:mm:ss’ ‘1000-01-01 00:00:00’~~> ‘9999-12-31 23:59:59’时间戳
枚举
不符合关系型数据库的理念,而且空间比tinyint大;
male female 应该新建表,外建链接关系;
表里面不能表示一种关系结构,最好为原子数据;
create table t2(gender enum('male', 'female'))charset utf8;
√> insert into t2 values('female');
√> insert into t2 values('male');
X> insert into t2 values('others');
增删改 详解
insert
用户注册…
语法:给哪张表 的哪几列 添加新数据
insert into [table] (name, age, date) values (...),(...);
主键不能重复的!
delete
删除资料…
语法:从哪张表 删除 哪几行数据;
delete from [table]
where expr
update
更新用户信息等;
语法:给 哪张表 的哪几列改新值 【在哪些行生效】
update [table] set column1=newVal, column2 = newVal where expr ;
查询
查询的五种字句
where
- <, <=, =,
- != <>, >=,
- in, between, # in (var1, var2, var3…varN); # between var1 and varN;
- || or , && and, ! not, # not in()
模糊查询:
like
% 通配任意字符
_ 通配单个字符note:
!! where 后面的语句当成 表达式 来看;
!! select 列名 当成 变量来看
!! 才能写出更好的sql语句
group by
统计
与以下聚合函数一起使用- max
- min
- avg
- sum
- count :求总行数
as 将列取别名; 表也可以
select goods_id,goods_name,max(shop_price) from goods; (XXXXXXXXXX)结果肯定错误 select cat_id,sum(goods_number*shop_price) as sload from goods group by cat_id;
having
select goods_id,goods_name,market_price-shop_price as sheng having sheng >= 200;
having对sheng 这个新列起作用;
即:where对表(旧)起作用, having对列(新) 起作用;
先筛选生成,再进行二次选择;select cat_id,sum(goods_number*shop_price) as sload from goods group by cat_id having sload>800000;
sql对某个表达式的表示为0 1; 此表达式也可以生成表,再进行sum count …
mysql> select * from stu;
+----+------+------+-------+
| id | name | subj | scor |
+----+------+------+-------+
| 1 | 张三 | 数学 | 90.0 |
| 2 | 张三 | 语文 | 50.0 |
| 3 | 张三 | 地理 | 40.0 |
| 4 | 李四 | 语文 | 55.0 |
| 5 | 李四 | 政治 | 45.0 |
| 6 | 王五 | 政治 | 30.0 |
| 7 | 赵六 | A | 100.0 |
| 8 | 赵六 | B | 99.0 |
| 9 | 赵六 | C | 98.0 |
+----+------+------+-------+
求出挂科2门及以上的学生的平均成绩;
select name,avg(scor) as pj,sum(scor<60) as cs from stu group by name having cs>=2;
+------+----------+------+
| name | pj | cs |
+------+----------+------+
| 张三 | 60.00000 | 2 |
| 李四 | 50.00000 | 2 |
+------+----------+------+
2 rows in set (0.00 sec)
- order by
按一个或 多个字段 对查询结果进行排序; 即再已生成的中间表中进行操作;
可以根据字段来排序:升序(默认的, 也可以加 字段名 asc) 、 降序(字段名 desc);
mysql> select goods_id,cat_id,goods_name,shop_price from goods where cat_id=15 order by shop_price desc;
+----------+--------+------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+------------+------------+
| 15 | 15 | NME0000015 | 11123.15 |
| 5 | 15 | NME0000005 | 11110.15 |
+----------+--------+------------+------------+
limit
在语句的最后,起到限制条目的作用;
limit[offset , n]; offset offset跳过的条目,从0 开始; n表示取出的条目truncate
truncate g2;清空一张表;
子查询
where 型子查询:
查询最大商品 最贵商品 把内层查询的结果作为外层查询的比较条件:
查询每个栏目下的最新商品:select goods_id,cat_id,goods_name from goods where goods_id in (select max(goods_id) group by goods_id);
from 型子查询:
查询每个栏目下的最贵/最新商品select * from(select goods_id,cat_id,goods_name from goods group by cat_id order by cat_id asc,goods_id desc) group by cat_id;
把内层的查询结果当成临时表,供外层sql再次查询;
select * from (select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc) as tmp group by cat_id;
exits 型子查询:
查询具有商品的栏目,也就是跨表格查询
把外层的查询结果,拿到内层,看内层的查询是否成立;
数据库备份
普通库备份
mysqldump ko_sns_mission -u root -pCQMYG14dss --default-character-set=utf8 > ko_sns_mission.sql
只备份表结构,不备份数据:
-d, --no-data No row information.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
mysqldump --opt -d ko_sns_basic -u root -pCQMYG14dss --default-character-set=utf8 > ko_sns_basic.sql
mysqldump --opt -d ko_sns_biz -u root -pCQMYG14dss --default-character-set=utf8 > ko_sns_biz.sql
mysqldump --opt -d ko_sns_msg -u root -pCQMYG14dss --default-character-set=utf8 > ko_sns_msg.sql
mysqldump --opt -d ko_sns_task -u root -pCQMYG14dss --default-character-set=utf8 > ko_sns_task.sql