mysql笔记

    1.创建一个名为mydb1的数据库testtest

    create database mydb1;

    2.创建一个使用utf-8字符集的mydb2数据库

    create database mydb2 character set utf8;

    3.创建一个使用utf-8字符集,并带有校对规则的mydb3数据库

    create database mydb3 character set utf8 collate utf8_general_ci;
    ————————————————————-

    显示数据库:

    show databases;

    显示库的创建语句信息:

    show create database db_name;

    数据库删除语句:

    drop database [if exists] db_name;

    如:drop database mydb1;

    修改库(不能改库名,只能改字符集,校验方法等):

    如:查看服务器中的数据库,并把其中某一个库的字符集修改为改
    变312;   alter database mydb2 character set gb2312;

    备份数据库中的数据:

    mysqldump -u用户名 -p数据库名>文件名 sql(window命令、脚本
    文件)

    恢复数据库:

    Source 文件名.sql(sql命令)
    ———————————————————-
    备份库:

    1.准备库的数据

    create database mydb1;

    use mydb1;

    create table test

    (
    id int
    );
    insert into test(id)values(1);

    select *from test;

    2.备份库

    2.1退出mysql客户端:quit

    2.2在window命令窗口中执行:mysqldump -uroot -p mydb1>c:
    \test.sql

    3.删除库:drop database mydb1;

    4.恢复库(1):

    4.1 创建库:create database mydb1;

    4.2 source c:\test.sql(通过执行脚本文件实现)

    5.恢复库(2):mysql -uroot -p mydb1<c:\test.sql(window 命令)

    ————————————————————-
    创建一个员工表:
    use mydb1;(创建表之前要进入库)
    create table employee
    (
    id int,
    name varchar(20),
    gender varchar(4),
    birthday date,
    entry_date date,
    job varchar(40),
    salary double,
    resume text
    )character set utf8 collate utf8_general_ci;

    查看库中所有表:

    show tables;

    查看表的创建细节:

    show create table employee;

    查看表的结构:

    desc employee;

    修改表:

    在上面员工表的基本上增加一个image列。

    alter table employee modify job varchar(60);

    删除sex列。

    alter table employee drop gender;

    表名改为user。

    rename table employee to user;

    修改表的字符集为utf-8.

    alter table user character set gb2312;

    show create table user;

    列名name改为username.

    alter table user change column name username varchar(20);
    ————————————————————-
    Insert语句:Insert into table[(column[,column…])]values (value[,value…])

    –插入的数据应与字段的数据类型相同

    –数据的大小应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中。

    –在values中列出的数据位置必须与被加入的列的排列位置相对应。

    –字符和日期型数据应包含在单引号中。

    –插入空值,不指定或insert into table value(null)

    使用insert语句向表中插入一个员工的信息。

    insert into employee(id,username,birthday,entry_date,job,salary,resume)values(1,’aaa’,’1990-09-09′,’1998-09-09′,’bbb’,1000,’cccccccc’);

    查看插入数据:

    select * from employee;

    使用insert语句向表中插入一个员工的信息。

    insert into employee(id,username,birthday,entry_date,job,salary,resume)values(2,’小李子’,’1990-09-09′,’1998-09-09′,’bbb’,1000,’cccccccc’);
    ** 插入失败,错误在于“小李子”长度过长,因为这个表采用的是utf-8编码,而“小李子”默认为gb-2312,所以出现乱码。

    插入失败后的解决方案:

    show variables like ‘chara%’;
    set character_set_client=gb2312;

    显示失败(乱码)后的解决方案:

    set character_set_results=gb2312;
    ————————————————————-
    Update语句:

    UPDATE tdl_name
    SET col_name1=expr1[,col_name2=expr2…][WHERE where_definition]

    –UPDATE 语法可以用新值更新原有表行中的各列。

    –SET子句指示要修改哪些列和要给予哪些值。

    –WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有行。

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

    update employee set salary=5000;

    将姓名为‘aaa’的员工薪水改为3000.

    update employee set salary=3000 where username=’aaa’;

    将姓名为‘aaa’的员工薪水改为4000元,job改为cccc

    update employee set salary=4000,job=’cccc’where username=’aaa’;

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

    update employee set salary=salary+1000 where username=’aaa’;
    ————————————————————-
    Delete语句:

    使用delete语句删除表中数据。

    delete from tbl_name[WHERE where_definition]

    –如果不使用where子句,将删除表中所有数据。

    –Delete语句不能删除表中某一列的值(可使用update)

    –使用 delete语句仅删除表中记录,不删除表本身。如果要删除表,使用droptable语句。

    –同insert和update一样,从一个表中删除记录将引起其它表的参照完整性问题,在修改数据库数据时,头脑中应该始终不要忘记这个潜在的问题。

    –删除表中数据也可使用TRUNCATE TABLE语句,它和delete有所不同,参看mysql文档。

    删除表中名称为‘小李子’的记录。

    delete from employee where username=’小李子’;

    删除表中所有记录。

    delete from employee;(delete一条一条删)

    使用truncate删除表中记录。

    truncate table employee;(直接将表全部摧毁)
    ————————————————————-
    Select语句(1):******

    SELECT[DISTINCT]*|{column1,column2,column3…}FROM table;(distinct可选,指示结果时,是否剔除重复数据)

    查询表中所有学生的信息。

    select id,name,chinese,english,math from student;(效果等价于select * from student;)

    查询表中所有学生的姓名和英语成绩。

    select name,english from student;

    过滤表中重复数据。

    select distinct english from student;

    Select语句(2):**********

    在select语句中可使用表达式对查询的列进行运算

    SELECT *|{column|expression,column2|expression,…}FROM table;

    –在select中可以使用as语句

    –SELECT column as 别名from表名;?

    在所有学生的英语分数上加10分特长分。

    select name english+10 from student;

    统计每个学生的总分。

    select name ,(english+chinese+math)from student;

    使用别名表示学生的分数。

    select name as 姓名 ,(english+chinese+math) as 总分from student;

    select name  姓名 ,(english+chinese+math) 总分from student;

    Select语句(3)*******

    查询姓名为ww的学生成绩。

    select * from student where name=’王五’;

    查询英语成绩大于90分的同学。

    select * from student where english>90;

    查询总分大于200分的同学。

    select * from student where (english+chinese+math)>200;

    Select语句(4)********

    在where子句中经常使用的运算符:

    –比较运算符:

    > ,<,<=,>=,=,<>    大于,小于,大于(小于)等于,不等于

    BETWEEN…AND…   显示在某一区间的值

    IN(set)          显示在in列表中的值,例:in(100,200)

    LIKE’张pattern’    模糊查询

    IS NULL             判断是否为空

    –逻辑运算符:

    and       多个条件同时成立

    or        多个条件任一成立

    not       不成立,例:where not(salary>100)

    –Like语句中,%代表零个或多个任意字符,_代表一个字符,例first_name like’_a%’;

    查询英语分数在80-90之间的同学。

    select * from student where english>80 and english<90;

    select * from student where english between 80 and 90;

    查询数学分数为89,90,91的同学。

    select * from student where math in(89,90,91);

    查询所有姓李的学生成绩。

    select * from student where name like ‘李%’;

    (‘李’%:模糊查询,查找出姓李的学生

    ‘李_%’:查找出单名姓李的学生

    ‘%李%’:查找出所有带‘李’字的学生。

    Select(5)***********

    使用order by子句排序查询结果。

    SELECT column1,column2,column3… FROM table;
    order by column asc|desc

    –Order by指定排序的列,排序的列即可是表中的列名,也可以是select语句指定的列名。

    –Asc升序,Desc降序

    –ORDER BY 子句应位于SELECT语句的结尾。

    对数学成绩排序后输出。

    select name,math from student order by math;

    对总分排序后输出,然后再按从高到低的顺序输出

    select name from student order by(math+chinese+english)desc;

    对姓李的学生成绩排序输出

    select name 姓名,(math+english+math)总分from student where name like ‘李%’order by(math+english+math);

    Select(6)************

    合计函数-count:Select count(*)|count(列名)from tablename
    [WHERE wher_definition]

    统计一个班总共有多少学生。

    select count(*)from student;
    select count(name)from student;

    统计数学成绩大于90的学生有多少个。

    select count(*) from student where math>90;

    统计总分大于250的人数有多少。

    select count(*)from student where (math+english+chinese)>250;

    合计函数-SUM(Sum函数返回满足where条件的行为的和):

    Select sum(列名){,sum(列名)…}from tablename
    [WHERE where_definition]

    统计一个班级数学总成绩。

    select sum(math)from student;

    统计一个班级语文、英语、数学各科的总成绩。

    select sum(math),sum(english),sum(chinese)from student;

    统计一个班级语文、数学、英语成绩总和。

    select sum(chinese+math+english)from student;

    统计一个班级语文成绩平均分。

    select sum(chinese)/count(chinese)from student;

    合计函数-AVG(AVG函数返回满足where条件的一列平均值):

    Select sum(列名){,sum(列名)…}from tablename
    [WHERE where_definition]

    求一个班级数学平均分。

    select avg(math)fromstudent;

    求一个班级总分平均分。

    select avg(math+chinese+english)from student;

    合计函数-MAX/MIN(Max/min函数返回满足where条件的一列的最大/最小值:)

    Select max(列名)from tablename
    [WHERE where_definition]

    求班级最高分和最低分(数值范围在统计中特别有用)

    select max(math+chinese+english),min(math+chinese+english)from student ;

    Select(7)

    使用group by子句对列进行分组

    SELECT column1,column2,column3…from table;
    group by column

    对订单表中商品归类后,显示每一类商品的总价

    select product from orders group by product;(对商品归类)
    select product,sum(price)from orders group by product ;(显示总价)

    使用having子句过滤(having和where均可实现过滤,但在having可以使用合计函数,having通常跟在group by后面,它作用于组,而where不可以使用合计函数)

    SELECT column1,column2,column3… FROM table;
    group by column hanging…

    查询购买了几类商品,并且每类总价大于100的商品。

    select product from orders group by product having sum(price)>100;
    ————————————————————-
    定义表的约束

    –主键约束:primary key不允许为空,不允许重复

    定义带有主键约束的表:

    create table test1
    (
    id int primary key,
    name varchar(20),
    password vachar(20)
    );

    删除主键:alter table tablename drop primary key;

    –定义主键自动增长:auto_increment

    定义一个主键自动增长的表:

    create table test2
    (
    id int primary key auto_increment,
    name varchar(20),
    password varchar(20)
    );

    –定义唯一约束:unique

    定义一个唯一约束的表:

    create table test3
    (
    id int primary key auto_increment,
    name varchar(20) unique
    );

    –定义非空约束:not null

    定义一个非空约束的表:

    create table test4
    (
    id int primary key auto_increment,
    name varchar(20) unique not null
    );

    –定义外键约束:constraint ordersid_FK foreign key(ordersid)references orders(id);

    create table husband
    (
    id int primary key,
    name varchar(20)
    );

    create table wife
    (
    id int primary key,
    name varchar(20),
    husband_id int,
    constraint husband_id FK foreign key(husband_id) references husband(id)
    );
    外键约束之一对一(身份证管理系统):
    create table person
    (
    id int primary key,
    name varchar(20)
    );
    create table idcard
    (
    id int primary key,
    address varchar(40),
    constraint id_FK foreign key id references person(id)
    );
    外键约束之一对多(部门管理系统):

    外键约束之多对多(师生管理系统)
    create table teacher
    (
    id int primary key,
    name varchar(20),
    salary double
    );
    create table student
    (
    id int primary key,
    name varchar(20)
    );
    create table teacher_student
    (
    teacher_id int,
    student_id int,
    constraint teacher_id_FK foreign key (teacher_id)references teacher(id),
    constraint student_id_FK foreign key (student_id)references student(id)
    );
    ————————————————————-
    JDBC开发

    PrepareStatement和Statement的区别
    1.PrepareStatement是Statement的孩子
    2.PrepareStatement可以防止sql注入的问题
    3.PrepareStatement它可以对它所代表的sql语句进行预编译,以减轻服务器压力。

    下层程序向上层程序抛出来的异常一般是运行时异常(除抛的异常是个返回值希望上层处理,这个时候就抛编译时异常)。

    转载请注明:软件测试 » mysql笔记

    喜欢 0
标签: