4.30

1.连接服务器
2.查看所有库
3.创建库
4.删除库
5.选择数据库
6.查看表
7.创建表
8.删除表
9.改表名
10.清空表
11.创建简单表
入门基本语句
MySql -username -password来连接
当连上服务器后，我们首先面对的是？
--是库，库有一个或多个，因此我们要想对表/行做操作的话，得先选库。
选库语句
user 库名
如果不知道有那名些库，想查看。
show databases
当选了库之后，我们面对的是表
查看库下面的所有表 show tables
创建一个数据库
create database 数据库名[charset 字符集]
删除一个数据库
drop database 数据库名
数据库改名
MySQL中表/列可以改名，database 不能改名。
phpMyAdmin 建新库，把所有表复制到新库，再删除旧库完成。
当选了库之后，我们面对的是表
查看库下面的所有表 show tables;
简单的建表语句
create table stu(
Snum int,
Sname varchar(10)
)engine myisam charset utf8;
//engine是指表引擎，和性能特点相关。
删除表
drop table stu;
修改表名
rename table 旧表名 to 新表名
清空表数据
truncate 表名
truncate 和delete区别
在于truncate相当于删表再重建一张同样结构的表
操作后得到一张全新的表而delete是从删除数据行的层面来做的。
truncate相当于把旧的学籍表扔了重画一张。
delete相当于用橡皮把学籍表上的数据给擦掉。
如果决定全部清空的话，truncate的速度更快一些。

create table class(
id int primary key auto_increment,
sname varchar(10) not null default '',
gender char(1) not null default '',
company varchar(20) not null default '',
salary decimal(6,2) not null default 0.00,
fanbu smallint not null default 0
)engine myisam charset utf8;
添那几列(id,sname,gender,company,salary,fanbu)
分别添加什么值(1,'张三','男','百度',8888.44,321)
insert into class
(id,sname,gender,company,salary,fanbu)
values
(1,'张三','男','百度',8888.44,321);
insert into class
(sname,gender,salary)
values
('刀锋','男','8458.79');
insert into class
values
(3,'李四','女','sina',6654.13,250);
id是自增型的，插入时不必为其赋值时一种常见的错误。
列于值必须按顺序一一对应。

5.3

insert into class
(sname,company,salary)
values
('刘备','皇室','14.55'),
('孙策','江东','17.85'),
('曹操','后裔','62.45'),
====update ======
改那张表：update
改那几列： gender company
改成什么值 ：‘女’，‘千度’
update class
set
gender = '女';
company = '千度';
update class
set fanbu = 123
where id = 6;
update class
set gender = '男',fanbu = '212'
where sname = '孙策';
update class set fanbu = 159
where gender = '男' and salary >8000;
=====删除======
删那张表的数据：class
删那几行：where expression
delete from class where salary>8800
=====查询=====
查哪张表的数据：class
查那些列 sname company salary
select sname,company,salary
from class where id = 6;
select * from class
最暴力的查找
*代表所有列，表名后不加where条件
则选所有行
因此取出所有行，所有列。
部分列，所有行，取所有人的姓名和工资
select sname,salary from class ;
查id>3的人的所有列
select * from class where id>3;
#取部分行，部分列
#取id<5的人的姓名和饭补
select sname,fanbu from class where id<5;

5.4

mysql> create table salary(
-> sname varchar(20) not null default '',
-> gongzi float(6,2)
-> )engine myisam charset utf8;
mysql> insert into salary values ('张三',-9999.99);
mysql> insert into salary values ('李四',9999.99);
mysql> alter table salary add bonus float(5,2) unsigned not null default 0.00;
mysql> insert into salary (sname,bonus) values ('王五',888.88);
mysql> insert into salary (sname,bonus) values ('王五',-888.88);
ERROR 1264 (22003): Out of range value for column 'bonus' at row 1
mysql> #bonus是unsigned，不能为负。
mysql> create table account(
-> id int not null default 0,
-> acc1 float(9,2) not null default 0.00,
-> acc2 decimal(9,2) not null default 0.00
-> )engine myisam charset utf8;
mysql> insert into account
-> values
-> (1,1234567.23,1234567.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from account;
+----+------------+------------+
| id | acc1 | acc2 |
+----+------------+------------+
| 1 | 1234567.25 | 1234567.23 |
+----+------------+------------+
1 row in set (0.00 sec)
mysql> #float的精度，有时会损失。
mysql> #如果像账号这样的敏感字段，建议用decimal。
mysql>
mysql> #char varchar text blob
mysql> #char和varchar分别称为定长和变长类型
mysql> #对于char(N)不够N个长度，用空格在尾部补够N个长度，浪费了尾部。
mysql> #对于varchar(N)不用空格补齐，但列内容前，有1-2个字节来标志列的长度
mysql> create table test(
-> ca char(6) not null default '',
-> vca varchar(6) not null default ''
-> )engine myisam charset utf8;
mysql> select * from test;
+-------+-------+
| ca | vca |
+-------+-------+
| hello | hello |
+-------+-------+
1 row in set (0.00 sec)
mysql> insert into test values
-> ('aa ','aa ');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+-------+-------+
| ca | vca |
+-------+-------+
| hello | hello |
| aa | aa |
+-------+-------+
2 rows in set (0.00 sec)
mysql> #concat 连接字符串用的。
mysql> select concat(ca,'!'),concat(vca,'!') from test;
+----------------+-----------------+
| concat(ca,'!') | concat(vca,'!') |
+----------------+-----------------+
| hello! | hello! |
| aa! | aa ! |
+----------------+-----------------+
2 rows in set (0.01 sec)
mysql> #char(M),varchar(M)中的M都是限定的字符，不是字节数。
mysql> insert into test values ('中国','华夏民族源头');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+-------+--------------+
| ca | vca |
+-------+--------------+
| hello | hello |
| aa | aa |
| 中国 | 华夏民族源头 |
+-------+--------------+
3 rows in set (0.01 sec)
mysql> #text文本类型，一般用来存储文章内容，新闻内容等。
mysql> #声明text列是，。不必给默认值
mysql> create table test2(
-> article text
-> );
mysql> #blob是二进制类型，用来存储图像，音频等二进制信息。
mysql> #意义，二进制，0-255都可能出现。
mysql> #blob在于为了防止字符集的问题。导致信息丢失。

mysql> #学习日期/时间类型
mysql> #date型。存储 年-月-日
mysql> create table test3 (
-> star varchar(20) not null default '',
-> birth date not null default '0000-00-00'
-> )engine myisam charset utf8;
mysql> desc test3;
+-------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+------------+-------+
| star | varchar(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+-------+-------------+------+-----+------------+-------+
mysql> insert into test3
-> values
-> ('张国荣','1961-03-12');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xB9\xFA\xC8\xD9' for column 'star' at row 1
mysql> #这个错误，是因为客户端没有声明字符集。
mysql> set names gbk;
mysql> #date 存储那年到那年？
mysql> #date 能存1000-01-01 -- 9999-12-31
mysql> #时间类型 20:20:20
mysql> #论坛签到，记录签到时间
mysql> alter table test3 add sign time not null default '00:00:00';
mysql> desc test3;
+-------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+------------+-------+
| star | varchar(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
| sign | time | NO | | 00:00:00 | |
+-------+-------------+------+-----+------------+-------+
mysql> insert into test3
-> (star,sign)
-> values
-> ('水王','19:10:45');
mysql> select * from test3;
+--------+------------+----------+
| star | birth | sign |
+--------+------------+----------+
| 张国荣 | 1961-03-12 | 00:00:00 |
| 水王 | 0000-00-00 | 19:10:45 |
+--------+------------+----------+
mysql> #datetime类型，日期时间类型
mysql> #日期时间类型输入格式YYYY-nn-dd HH：ii：ss
mysql> create table test4 (
-> sname varchar(20) not null default '',
-> logintime datetime not null default '0000-00-00 00:00:00'
-> )engine myisam charset utf8;
mysql> insert into test4
-> values
-> ('张三','2009-10-13 15:34:48');
mysql> select * from test4;
+-------+---------------------+
| sname | logintime |
+-------+---------------------+
| 张三 | 2009-10-13 15:34:48 |
+-------+---------------------+
mysql> #一个比较有意思的列 timestamp
mysql> create table test5 (
-> ts timestamp default CURRENT_TIMESTAMP,id int
-> )engine myisam charset utf8;
mysql> insert into test5
-> (id)
-> values
-> (1),(2),(3);
mysql> select * from test5;
+---------------------+------+
| ts | id |
+---------------------+------+
| 2014-05-04 16:22:03 | 1 |
| 2014-05-04 16:22:03 | 2 |
| 2014-05-04 16:22:03 | 3 |
+---------------------+------+
mysql> insert into test5
-> (id)
-> values
-> (5);
mysql> select * from test5;
+---------------------+------+
| ts | id |
+---------------------+------+
| 2014-05-04 16:22:03 | 1 |
| 2014-05-04 16:22:03 | 2 |
| 2014-05-04 16:22:03 | 3 |
| 2014-05-04 16:23:07 | 5 |
+---------------------+------+

mysql> #year类型
mysql> create table test6 (
-> thing varchar(20) not null default '',
-> ya year not null default '0000'
-> )engine myisam charset utf8;
mysql> desc test6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| thing | varchar(20) | NO | | | |
| ya | year(4) | NO | | 0000 | |
+-------+-------------+------+-----+---------+-------+
mysql> insert into test6
-> values
-> ('淝水之战','383');
ERROR 1264 (22003): Out of range value for column 'ya' at row 1
mysql> #超出范围了。year类型只占1个字节，最多能存256种变化。
mysql> insert into test6
-> values
-> ('辛亥革命','1911');
mysql> select * from test6;
+----------+------+
| thing | ya |
+----------+------+
| 辛亥革命 | 1911 |
+----------+------+
mysql> insert into test6
-> values
-> ('殖民火星','2156');
ERROR 1264 (22003): Out of range value for column 'ya' at row 1
mysql> insert into test6
-> values
-> ('清帝殉位','0000');
#注意！！！！
mysql> create table test7 (
-> ya year(2)
-> )engine myisam charset utf8;
mysql> insert into test7 values ('95'),('12');
mysql> select * from test7;
+------+
| ya |
+------+
| 95 |
| 12 |
+------+

5.5

列名称 列类型 默认值 是否主键
id用户名 int unsigned pri
username char(20) ''
gender char(1)/tinyint
weight tinyint unsigned
birth date
salary decimal(8,2)
lastlogin int unsigned
intro varchar
在开发中，会员的信息优化往往是把频繁用到的信息优先考虑效率，存储到一张表中，不常用的信息和比较占用空闲的信息，优先考虑空间占用，存储到辅表。
create table member (
id int unsigned auto_increment primary key,
usename char(20) not null default '',
gender char(1) not null default '',
weight tinyint unsigned not null default 0,
birth date not null default '0000-00-00',
salary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0
)engine myisam charset utf8;
修改表语法
alter table 表名 add 列名称 列类型 列参数
加的列在表的最后。
alter table 表名 add 列名称 列类型 列参数 after 某列 [把新列指定加在某列后面]
如果想新建一个列，且在表的最前面出现，用first
alter table m1 add pid int not null default 0 first;
删除列
alter table 表名 drop 列名
修改列类型
alter table 表名 modify 列名 新类型 和新参数
alter table m1 modify gender char(4) not null default '';
修改列名及列类型
alter table 表名 change 旧列名 新列名 新类型 新参数
alter table m1 change id uid int unsigned;

5.6

分析 商城的表并建立类似的小型表
商品表
goods_id
cat_id
goods_sn
goods_name
click_count
goods_number
market_price
shop_price
add_time
is_best
is_new
is_hot
CREATE TABLE `goods` (
`goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`click_count` int(10) unsigned NOT NULL DEFAULT '0',
`goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
`market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into test.goods
select goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price,shop_price,add_time,is_best,is_new,is_hot from gyshop.goods;

mysql> insert into test.goods
-> select goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price,shop_price,add_time,is_best,is_new,is_hot from gyshop.goods;
mysql> #chaxun shangpin zhujian wei 32d shangp .
mysql> select goods_id,goods_name,shop_price from goods where goods_id=32;
mysql> select goods_id,cat_id,goods_name from goods where cat_id !=3;
mysql> select goods_id,cat_id,goods_name from goods where cat_id <> 3;
mysql> select goods_id,cat_id,goods_name,shop_price from goods where shop_price>3000;
mysql> select goods_id,cat_id,goods_name,shop_price from goods where shop_price <=100;
mysql> select goods_id,cat_id,goods_name from goods where cat_id in (4,11);
mysql> select goods_id,goods_name,shop_price from goods where shop_price between 100 and 500;
mysql> select goods_id,cat_id,goods_name from goods where cat_id not in (3,11);
mysql> select goods_id,cat_id,goods_name from goods where cat_id !=3 and cat_id !=11;
mysql> select goods_id,goods_name,shop_price from goods where shop_price>=100 and shop_price<=300
-> or shop_price>=400 and shop_price<=5000;
mysql> select goods_id,goods_name,shop_price from goods where shop_price>=100 and shop_price<=300
-> or shop_price>=4000 and shop_price<=5000;
mysql> select goods_id,goods_name,cat_id,shop_price,click_count
-> from goods
-> where cat_id = 3 and (shop_price<1000 or shop_price>3000) and click_count>=5;