mysql insert 字符集_mysql字符集，insert,update,delete,select

发现有错误：数据太长了。

//查看数据库的所有编码：

show variables like 'character%';

-----+

| character_set_client     | utf8    设置客户端的字符集

|

| character_set_connection | utf8    设置连接的字符集

|

| character_set_database   | utf8 设置数据库的字符集

|

| character_set_filesystem | binary    设置文件系统的字符集

|

| character_set_results    | utf8 设置显示结果是使用的编码

|

| character_set_server     | utf8 设置配置MySQL时设置的字符集

|

| character_set_system     | utf8

|

| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\chars

ets\ |

+--------------------------+----------------------------------------------------

解决乱码的问题：

1、设置客户端的结果集

2、设置显示结果的字符集

1,set character_set_client=gbk;   使用的客户端编码

2,set character_set_results=gbk; 结果集的编码

创建一张a表

create table a

(

id int,

name varchar(20)

);

insert into a values(1,'aaa');

insert into a values('bbbb');//只想添加name时，要把a(name)写上。

insert into a(name) values('bbbb');

+------+------+

| id   | name |

+------+------+

|    1 | aaa  |

| NULL | bbbb |

+------+------+

实际上into也可以不写。

insert a values(2,'cccc');

insert a values(3,'dddd');

上面的等价下面的：

insert a value(4,'cccc'),(5,'dddd');

select * from employee;

将所有的员工的薪水修改为5000元

update employee set salary=5000;

将姓名为  zhangsan 的员工的薪水修改为3000元

update employee set salary=3000 where name='zhangsan';

将姓名为 lisi 的员工薪水修改为4000元，sex修改为female

update employee set salary=4000,gender='female' where name='lisi';

将xiaohong的薪水在原有的基础上增加1000元。

update employee set salary=salary+1000 where name='xiaohong';

set character_set_results=gbk;

3delete语句 删除数据

删除表中name为zhangsan 的记录

delete from employee where name='zhangsan';

删除表中的所有数据

delete from employee;

insert into employee

(id,name,gender,birthday,salary,entry_date,resume)

values(1,'zhangsan','male','1980-1-1',1000,'2000-3-16','good boy');

insert into employee

(id,name,gender,birthday,salary,entry_date,resume)

values(2,'lisi','male','1934-4-1',1000,'2010-3-16','good boy');

insert into employee

(id,name,gender,birthday,salary,entry_date,resume)

values(3,'xiaohong','female','1984-1-1',1000,'2008-3-16','good girl');

使用truncate删除表中的记录

truncate employee;  删除表中的记录。删除表在创建表

select * from employee;

insert into employee

(id,name,gender,birthday,salary,entry_date,resume)

values(1,'zhangsan','male','1980-1-1',1000,'2000-3-16','good boy');

insert into employee

(id,name,gender,birthday,salary,entry_date,resume)

values(2,'lisi','male','1934-4-1',1000,'2010-3-16','good boy');

insert into employee

(id,name,gender,birthday,salary,entry_date,resume)

values(3,'xiaohong','female','1984-1-1',1000,'2008-3-16','good girl');

select * from employee;

mysql> select * from employee;

+------+----------+--------+------------+--------+------------+-----------+

| id   | name     | gender | birthday   | salary | entry_date | resume    |

+------+----------+--------+------------+--------+------------+-----------+

|    1 | zhangsan | male   | 1980-01-01 |   1000 | 2000-03-16 | good boy  |

|    2 | lisi     | male   | 1934-04-01 |   1000 | 2010-03-16 | good boy  |

|    3 | xiaohong | female | 1984-01-01 |   1000 | 2008-03-16 | good girl |

+------+----------+--------+------------+--------+------------+-----------+

4、select 语句

column 指定列名

* 号代表查询所有列

From 指定查询哪张表

DISTINCT可选，值显示结果时，是否剔除重复数据。

student.sql

create table student

(

id int,

name varchar(20),

chinese float,

english float,

math float

);

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);

insert into student(id,name,chinese,english,math) values(2,'李菁',67,53,95);

insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);

insert into student(id,name,chinese,english,math) values(4,'李一',82,98,92);

insert into student(id,name,chinese,english,math) values(5,'李来才',82,84,67);

insert into student(id,name,chinese,english,math) values(6,'张警报',55,85,45);

insert into student(id,name,chinese,english,math) values(1,'黄蓉',75,65,30);

//查询所有的学生的信息select * from student;