3. 运行 C:\Program Files\MySQL\MySQL Server 5.1
\bin\mysqld-nt --skip-grant-tables 启动mysql,关闭权限的
检查
4. 运行 C:\Program Files\MySQL\MySQL Server 5.1
\bin\mysqladmin -u root flush-privileges password
"newpassword" 重设root密码
C:/Documents and Settings/All Users/Application
Data/MySQL/MySQL Server 5.1/Data
-- 连接服务器
mysql -hlocalhost -p3306 -uroot -p
-- 设置字符集
set names GBK;
use mydatabase;
alter table student charset=utf8;
-- 创建表
create table if not exists my_student(
name varchar(10),
gender varchar(10),
number int
)charset utf8;
create table my_class(
name varchar(10),
room varchar(10)
)charset utf8;
create table if not exists student(
name varchar(10),
gender varchar(10),
number int
)charset utf8;
create table class(
name varchar(10),
room varchar(10)
)charset utf8
修改字段
新增
alter table student
add column age int
first;
alter table student
add ages int
after name;
-- 修改
alter table student
modify number char(10) after id;
alter table student
modify id int
first;
-- 重命名
alter table student
change ages sex char(10);
-- 新增数据
insert into student values
(2,28,54,'xiaowang',1,'male'),
(3,22,86,'xiaohua',2,'female'),
(4,24,45,'xiaowang',2,'female')
;
-- 新增部分字段数据
insert into student(number,gender,name,id,age) values
('wanyi001','male','tom',5,32),
('wanyi002','male','lili',6,33)
;
-- 查看数据
select * from student;
-- 查看指定字段
select id,number,sex,name,gender from student where
id=5;
-- 更新数据
update student set gender='female' where
name='xiaoming';
-- 查看所有字符集
show character set;
-- 查看服务器默认对外处理的字符集
show variables like 'character_set%';
-- 修改服务器认为客户端数据的字符集为GBK
show variables like 'character_set%';
-- 修改服务器给定的数据为GBK
set character_set_results=gbk;
-- 定点型数据
create table my_decimal(f1 float(10,2),
d1 decimal(10,2))charset utf8;
-- 添加数据
insert into my_decimal values
(1234.123456,1234.123456789),
(99999999.99,99999999.99);
-- 查看字段
Desc my_decimal;
-- 查看所有字段数据
select * from my_decimal;
select * from my_decimal where f1;
-- 2-6时间
create table my_date(d1 datetime,d2
date,d3 time,
d4 timestamp,d5 year)charset utf8;
-- 添加数据
insert into my_date values('2016-5-13 13:42:44',
'2016-5-13','13:44:38','2016-5-13 13:45:00','69');
select * from my_date;
-- 2-9枚举字符串
create table my_enum(gender enum('男','女'))
charset utf8;
-- 插入数据
insert into my_enum values(1),(2),(2);
select * from my_enum;
-- 将字段结果取出来进行0运算，0为照妖镜
select gender+0,gender from my_enum;
-- 2-10集合字符集，和枚举差不多，但是多选
create table my_set(hobby set('篮球','足球','排球','网
球','台球','棒球','羽毛球','乒乓球','跑步','体操'))
charset utf8;
insert into my_set values('台球,羽毛球');
insert into my_set values(8888);
insert into my_set values(1266);
insert into my_set values(1265);
insert into my_set values(1264);
insert into my_set values(1023);
insert into my_set values(1111);
insert into my_set values(999);
insert into my_set values(255);
insert into my_set values(177);
insert into my_set values(128);
insert into my_set values(124);
insert into my_set values(88);
insert into my_set values(64);
insert into my_set values(32);
insert into my_set values(24);
insert into my_set values(16);
insert into my_set values(8);
insert into my_set values(6);
insert into my_set values(4);
insert into my_set values(2);
select * from my_set;
-- 将字段结果取出来进行0运算，0为照妖镜
select hobby+0,hobby from my_set;
-- 求varchar在utf8和GBK下的最大值
-- 21844*3+2=65534
create table myutf8(name varchar(21844))charset utf8;
-- 32766*2+2=65534
create table mygbk(name varchar(32766))charset gbk;
-- mysql的记录长度对大为65535个字节
-- 释放null，把65535字节用完,不写null就是默认为null--
就是可以为空
-- 1+ 21844*3+2=65535
create table myutf81(age tinyint not null,name varchar
(21844) not null)charset utf8;
-- 1+ 32766*2+2=65535
create table mygbk1(age tinyint not null,name varchar
(32766) not null)charset gbk;
-- 证明text字段占用10个字节长度
-- 10+ 21841*3 +2=65535
create table myutf8text1(textf text not null,name
varchar(21841) not null default 0)charset utf8;
-- 10+ 32761*2 +1 +2=65535
create table mygbktext1(textf text not null comment '文
本字段',
age tinyint not null comment '迷你整型',name varchar
(32761) not null comment '变长字符串')charset gbk;
select * from myutf8text1;
-- 查看表创建语句
show create table mygbktext1\G;
-- 3-02增加主键
create table my_primaly1(id int primary key
auto_increment comment '逻辑主见：自增长',
name varchar(20)not null comment '姓名',
number char(10) not null comment '学号')charset utf8;
-- 修改number
alter table my_primaly1 modify number varchar(20);
insert into my_primaly1 values(1,'圣涛',200908020013);
-- 3-08修改表选项的值，向下无效，向上有效
alter table my_primaly1 auto_increment =10;
-- 查看数据
select * from my_primaly1;
-- 查看表创建语句
show create table my_primaly1\G;
-- 查看数据库
show databases;
-- 查看表
show tables;
-- 查看表字段
Desc my_primaly1;
-- 删除主键
alter table my_primaly modify1 id int;
-- 下面为错误
alter table my_primaly modify1 id int primaly key;
-- 3-10增加唯一主键
-- 增加唯一键
create table my_unique1(
number varchar(20) unique comment'学号：唯一，允许为
空',
name varchar(20)not null)
charset utf8;
-- 增加唯一主键,因为它是唯一的，刚好系统没主键，就把他
当作主键
create table my_unique2(
number varchar(20) comment'学号：唯一，允许为空',
name varchar(20)not null,
unique key(number))
charset utf8;
-- 创建表之后增加唯一键
create table my_unique3(id int primary key
auto_increment,number varchar(20) not null,
name varchar(20)not null)charset utf8;
-- 增加唯一键,若已经存在主键，发现没有唯一键，加一个唯
一键
alter table my_unique3 add unique key(number); |
|
收藏
淘帖
踩
