java从入门到弃坑数据库0 0

1.数据库入门：数据库软件可以让数据永久保存在数据库中，查询速度快，并且便于查询和管理数据。

2.Mysql数据库：

              A：查看所有数据库：mysql> show databases;   --分号结束。sql语句就会发送给mysql服务器端执行。

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

                       | Database           |

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

                       | information_schema |      --mysql元数据数据库。

                       | mysql             |    --mysql配置数据库。其中user表用于管理mysql用户和密码、权限信息。

                       | performance_schema |     --mysql性能监控信息数据库。

                       | test               |     --测试数据库。

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

3.创建数据库：mysql> createdatabase day16      创建数据库并指定默认字符集                                                 

                                         -> default character set utf8;                                        

                                Query OK, 1 row affected (0.00 sec)

4.删除数据库：mysql> dropdatabase day15;

5.修改数据库默认字符集：mysql> alter databaseday15 default character set gbk;

6.数据库的查询功能：查询所有表：mysql> useday15;   --选择数据库

                                mysql> show tables;  --查看数据库中的所有表

7.创建表：需求：创建学生表，字段(id,name,age)

USE day16;
CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    age INT
   );

8.删除表：

mysql> drop table student;

9.修改表：A：添加字段：ALTER TABLE student ADD COLUMN gender VARCHAR(2);

                     B：修改字段类型：ALTER TABLE student MODIFY COLUMN NAME VARCHAR(100);
                     C：修改字段名称：ALTER TABLE student CHANGE COLUMN gender remark2 VARCHAR(100);
                     D：删除字段：ALTER TABLE student DROP COLUMN remark2;

                     E：修改表名：ALTER TABLE student RENAME TO student2;

10.管理数据：查看表的数据：SELECT * FROM student;

                             插入所有列数据：INSERT INTO student VALUES(1,'eric',20);

                             插入部分列：列的数据和值的顺序和数量应保持一致：INSERT INTO student(id,NAME) VALUES

                                                                                                                                         (3,'jacky');

                             修改所有数据：UPDATE student SET age=18;

                             修改某个条件数据：UPDATE student SET age=50 WHERE id=1;

                             修改多个列：UPDATE student SET age=28,NAME='jack' WHERE id=1;

                             删除全表数据：DELETE FROM student;

                             按条件删除数据：DELETE FROM student WHERE id=2;
                             删除全表数据：TRUNCATE TABLE student;

         两个全表删除区别：delete from删除全部，也可以按条件删除，但是truncate table只能全表删除，不能按条件 

                      删除

                      delete from删除的数据可以回滚，truncate table删除的数据不能回滚。

                      delete from不可以把自增长约束(auto_increment)重置，truncate table可以把自增长约束

                      (auto_increment)重置

11.查询数据：查询所有列：SELECT * FROM student;

             查询指定列：SELECT NAME,age FROM student;

             查询时制定别名：SELECT NAME AS '姓名',age AS '年龄' FROM student;

             添加两个新列：ALTER TABLE student ADD servlet INT,ADD jsp INT;

             更新数据：UPDATE student SET servlet=86,jsp=75 WHERE id=1;
                       UPDATE student SET servlet=90,jsp=65 WHERE id=2
                       UPDATE student SET servlet=78,jsp=50 WHERE id=3;

             合并列查询：SELECT NAME '姓名',(servlet+jsp) '总成绩'  FROM student;

                             查询时添加常量列：SELECT NAME '姓名',age '年龄','java就业班' AS '班级' FROM student;

                             查询时去除重复数据：SELECT DISTINCT id FROM student;

12.条件查询：逻辑条件： and   or：SELECT * FROM student WHERE id=3 AND name='john';    交集

                                                                      SELECT * FROM student WHERE id=3 OR age=10;并集

                             比较条件：>  <  >=  <=  = BETWEEN AND

                             判空条件： =''  IS NULL  <>''  IS NOT NULL

                             模糊条件：like：SELECT * FROM student WHERE NAME LIKE '张%';

13.聚合查询：查询所有学生servlet总分：SELECT SUM(servlet) FROM student;

                             查询所有学生servlet平均分：SELECT AVG(servlet) FROM student;

                             查询最高分：所用函数MAX

                             查询最低分：所用函数MIN

                             查询一共几个学生：SELECT COUNT(*) FROM student;

                              注：聚合函数会排除null值的数据

14.分组查询：查询男女数量个多少：SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender;

      分组筛选：分组筛选(group by + having(条件))：SELECT gender,COUNT(*) '人数' FROM student GROUPBY gender

                             HAVING COUNT(*)>2;

15.分页查询：limit 起始行数，查询的行数： SELECT * FROM student LIMIT 10,5;       

16.查询后排序：按照id升序排列：SELECT * FROM student ORDER BY id ASC;  

                                 按照servlet降序排列：SELECT * FROM student ORDER BY servlet DESC;    

17.字段类型：char（20）：一定占用20个字符空间，不管实际数据长度。

                            varchar（20）：可变字符串，实际长度为数据长度。

                            int：最多十一位，长度跟据实际长度变化。

                            int（4）：固定的数值长度。