26.mysql basics

@一贤Don't wear shoes

1. The way to store data now

  • Store data in java (variables, arrays, collections, etc.), and these data are temporarily stored in memory.
  • If you use a stream to store data in a file, the data can be stored on the hard disk for a long time. The data type in the file is single and inconvenient to handle. A database is needed to store the data regularly.

2. The basic concept of the database

2.1: Database (DB)

  • A warehouse that organizes, stores, and manages data according to a data structure (a warehouse that stores data).

2.2. The role of the database

  • Store data, fetch data, and manage data.

2.3: Classification of the database:

Insert picture description here

2.4: Database Management System (DataBase Management System, DBMS)

  • Large-scale software for operating and managing databases, this software is used to establish, use and maintain databases.

2.5. Administrator (DBA): A person who specializes in managing the database.

2.6. Database system (DBS): database, database management system, administrators.

2.7. The composition of relational databases: tables, relationships between tables, and objects.

2.8. A relational database is composed of tables, which in turn are composed of rows and columns.

  • Row=entity=record
  • Column=field=domain

2.9. MySQL

  • It is an open source, free, and small relational database management system.
  • It was developed by the Swedish company MySql AB and now belongs to the Oracle company.
  • MySql5.5 is free before 5.5, service charges after 5.5.
  • The mysql database uses the Innodb engine.

3. MySql uninstallation and installation

3.1. MySql uninstall

3.1.1. Stop MySql service

  • Taskbar-"Right mouse button -" select Task Manager-"Details-"Service -" MySql right click-"Stop.

3.1.2. Uninstall MySql

  • Control Panel-"Programs and Features-"MySql-"Right-click to uninstall.

3.1.3. Clear the registration form

  • System Disk (C Disk)-"ProgramData-"MySql right click to delete.
  • Note: After uninstalling, if there are undeleted MySQL services, you can manually delete them.
    Open the command line as an administrator, enter sc delete MySQL57 and press Enter.

3.2. MySql installation

3.2.1: Install MySql5.7 software: pay attention to the following three places

Insert picture description here


Insert picture description here


Insert picture description here

3.2.2. Set MySql encoding:

		3.2.2.1:找到my.ini:C:\ProgramData\MySQL\MySQL Server 5.7
		3.2.2.2:修改my.ini中编码:
Insert picture description here


Insert picture description here

3.3. Install third-party software: Navicat

4. Stop and start and login of MySql service

4.1. Start and stop of MySql service

4.1.1: start/stop service

  • Right-click on the taskbar-"Select Task Manager-"Details-"Service -" MySql right-click to start/stop.

4.1.2: start/stop service

  • My computer right mouse button-"Management-"Service -" MySql right click to start/stop.

4.1.3: start/stop service

  • Run cmd as an administrator -> enter the command net stop/start mysql;

4.2.MySql login method:

4.2.1: The first login

  • Open the MySql client-"Enter the password corresponding to the root account.

4.2.2: The second login

  • Open cmd-"cd MySql installation file bin directory -" enter the login command: mysql -u account -p ->> password.

4.2.3: The third type of login

  • Use Navicat for MySQL to create a connection and enter the password, test the connection is successful, and make sure you have logged in.

5.Sql: structured query language

Insert picture description here

5.1: Definition language of DDL database: create, delete and modify databases and tables.

5.2: DML database operating language: add, modify, and delete data in the database table.

5.3: DQL database query language: query data in database tables.

5.4: DCL database control language: create, delete and modify users and authority management.

5.5: TCL transaction control language: operating database transactions.

6.DDL: Database definition language, which operates on databases and tables.

6.1. View all databases: show databases

Insert picture description here

6.2. Create a database: create database [if not exists] database name

						[defalut charset utf-8]

6.3. View the creation of the database: show create database database name

6.4. Modify the encoding of the database: alter database database name character set encoding;

6.5.Delete database: drop database database name.

6.6. View the current database: select database();

6.7. Enter the database (use database): use database name;

6.8. View all tables in the current database: show tables;

6.9. Create a table:

		create table 表名(
			列名1 数据库类型1 列的特征,
			列名2 数据库类型2 列的特征,
			...
			列名n 数据库类型n 列的特征
		);
  • Note: Commonly used column characteristics are: primary key, [not] null, AUTO_INCREMENT, DEFAULT'default value';
  • Primary key column VS unique key
  • Similarities: Make sure that the column values ​​are not repeated.
  • Differences: the
    primary key column cannot be empty; the unique key can be empty The
    primary key column can be composed of one or more columns, but the unique key is generally used to limit one column. The
    primary key composed of multiple columns is called the composite primary key, and the composite primary key column requires If the composed column has a different column value, it is a different primary key.The composite primary key requires that all the column values ​​of the composed column be the same, which is a duplicate primary key.
Insert picture description here

6.10. View table creation: show create table table name;

6.11. View the fields of the table: desc table name;

6.12. Add fields to the table: alter table table name add column column name column data type;

6.13. Modify the data type of the field in the table: alter table table name modify the data type of the column name;

6.14. Delete fields in the table: alter table table name drop column column name;

6.15. Modify the table name: rename table original table name to new table name;

6.16. Modify column name: alter table table name change column original column name new column name data type;

6.17. Drop table: drop table table name;

  • Note: The DDL key will be operated with third-party software.

7.Sql's DML language: database operation language, add, modify, and delete data in the database table.

7.1: Add data to the table

7.1.1: The syntax for adding a piece of data to the table:

  • insert into table name (column name 1, column name 2, column name 3...) values ​​(value 1, value 2, value 3...); (recommended)
    or insert into table name values ​​(value 1, value 2, value 3... );//All columns in the table must all add data.
    Note: The column name list and the value list must be one-to-one correspondence (the order is the same, the type corresponds, and the number matches);
eg:#向表所有列添加一条数据
insert into t_student(sid,sname,sage,ssex,saddress)
 values(1,'伟伟',99,'女','深圳');
insert into t_student values(2,'永治',101,'男','千锋');
#向表中部分列添加一条数据
insert into t_student(sid,sname) values(3,'费费');
#向表中添加一条数据,值用默认值
insert into t_student(sid,sname,saddress) values(4,'桦桦',default);

7.1.2: MySql supports the syntax of adding multiple pieces of data to the table:

  • insert into table name (column name 1, column name 2...) values ​​(value 1, value 2...), (value 1, value 2...)...
eg:#向表中添加多条数据
insert into t_student(sid,sname,sage,ssex,saddress)
values(5,'棋棋',3,'男',default),(6,'航航',3,'男',default),
(7,'康康',1,'男',default),(8,'洋洋',1,'男',default),
(9,'贤贤',1,'男',default);	

7.2: Syntax to modify the data in the table: update table name set column name 1=value 1, column name 2=value 2… [where condition];

  • Note: In the modified statement, the where condition must be set, otherwise the data in the entire table will be changed.
eg:#修改表中数据,关系运行符=,>,<,>=,<=,<>,!=
			update t_student set sname='羊羊' where sid=8;

7.3: The syntax for deleting data in a table: delete from table name [where condition];

  • Note: In the delete statement, the where condition is not written, and the data in the entire table is deleted.
eg:#删除表中数据
	delete from t_student where sid=4;

7.4: Delete the data in the entire table:

  • delete from table name; delete the data in the entire table line by line, the structure of the table and the identification seed of the auto-increment column are still there. The deletion efficiency is the lowest, and only the data is deleted. The table structure and identification seed are both present.
  • truncate table table name; delete the data and table structure in the entire table one page by one, and then create a new (empty) table with the same structure as the original table. Delete the most efficient, delete the data and table structure and the original table identification seed, and re Generate a new logo seed.
  • drop table table name; delete all the table and table related, delete the most thorough.
eg:#删除表中所有数据
delete from t_class;

TRUNCATE table t_class;

drop table t_class;

8. Single table query

8.1: The general syntax of a single table query:

  • select column name 1, column name 2...
    from table name
    [where condition // filter while querying]
    [group by column name 1, column name 2]
    [having condition // further filter the grouped results]
    [order by column Name 1, column name 2(asc/desc)]
    [limit parameter 1 [,parameter 2]];

8.2: Columns in the query table:

  • Syntax 1: select column name 1, column name 2…from table name; you can query all or part of the columns in the table (recommended)
  • Syntax 2: select * from table name; query all data in the table
eg:#查询表中所有列
select * from t_student;
select sid,sname,sage,ssex,saddress from t_student;
#查询表中部分列
select sname,ssex from t_student;

8.3: Query the data in the table according to the conditions: select column name 1, column name 2...from table name where conditions;

  • eg:#Query the data in the table according to the conditions
  • #In the database, table and use and, table or or
select sid,sname,sage,ssex,saddress
from t_student
where ssex='男' and sage>1;

8.4: Aggregate functions: sum (column name) for the sum, count (column name) for the total number of records, max (column name) for the maximum value, min (column name) for the minimum value, and avg (column name) for the average value.

  • Note: In general, ordinary columns and aggregate function columns cannot be used together. (The query results of ordinary columns have multiple results, and the aggregate function column has only one record)
  • If you want to use ordinary columns with aggregate function columns, unless you use ordinary columns as grouping conditions.
  • In general, aggregate function columns can be followed by select and having.
eg:#聚合函数的使用
	select sum(sid),count(sid),max(sid),min(sid),avg(sid) from t_student;

8.5: Group query: select column name 1, column name 2...from table name [where condition//select while querying]

  • group by column name 1, column name 2;
eg:select ssex,count(sid) from t_student group by ssex;

8.6: Filter after grouping: select column name 1, column name 2...from table name [where condition // filter while querying]

  • group by column name 1, column name 2 having conditions // further filter the grouped results]
  • Note: SQL statements with having must have group by; SQL statements with group by may not have having.
eg:#分组后再筛选:根据性别查询总人数大于1所有组的总数
select ssex,count(sid)
from t_student
group by ssex
having count(sid)>1;

8.7: Sort the query results:

eg:#查询所有学生信息,根据年龄由大到小排序显示,年龄相同根据学号由大到小排序
select sid,sname,sage,ssex,saddress
from t_student
order by sage desc,sid desc; 

8.8: (mysql and oracle support) deduplication distinct

eg:#查询所有学生的性别并去重
select DISTINCT ssex from t_student;

8.9: (mysql support) limit: limit the number of output records

  • Syntax 1: select column name 1, column name 2...from table name limit number; limit the output of the first few records;
  • Syntax 2: select column name 1, column name 2...from table name limit the starting number of records on the current page, the number of records per page;
  • Pagination formula: select column name 1, column name 2...from table name limit (current page number -1) * the number of records per page, the number of records per page;
eg:#查询所有学生信息,输出年龄最大的三位学生信息
select sid,sname,sage,ssex,saddress
from t_student
order by sage desc
limit 3;
#每页显示三条记录,查询第3页数据
select sid,sname,sage,ssex,saddress
from t_student
limit 6,3;

8.10: Fuzzy query: In general, the efficiency of fuzzy query is lower than that of precise query.

8.10.1: Wildcard:

  • _: Represents any character
  • %: Represents any character of any length

8.10.2: like: column name like'...'; generally used for character columns as conditions for fuzzy queries.

eg:#查询名字中包含"费"字的所有学生信息
select sid,sname,sage,ssex,saddress
from t_student
where sname like '%费%';

8.10.3: between min and max; Query the value of the specified column in the range of min to max.

eg:#查询年龄在1-3岁间的所有学生信息
select sid,sname,sage,ssex,saddress
from t_student
where sage>=1 and sage<=3;
select sid,sname,sage,ssex,saddress
from t_student
where sage BETWEEN 1 and 3;

8.10.4: in/not in: column name [not] in (value 1, value 2...) means that the column name is equal to the value 1 or the column name is equal to the value 2 are eligible.

eg:#查询地址在深圳或北京或上海的所有学生信息
select sid,sname,sage,ssex,saddress
from t_student
where saddress='深圳' or saddress='北京' or saddress='上海';
select sid,sname,sage,ssex,saddress
from t_student
where saddress in('深圳','北京','上海');

8.10.5: Query column name is empty: column name is [not] null;

eg:#查询性别不为空的所有学生信息
select sid,sname,sage,ssex,saddress
from t_student
where ssex is not null;

9.Data integrity: refers to the accuracy and reliability of data.

9.1: The integrity of the most important data in the database. In order to maintain the integrity of the data in the data, provide entity integrity, domain integrity,

  • Referential integrity, custom integrity to share and maintain the integrity of the data in the database.

9.2: Entity integrity: refers to the accuracy and reliability of each record in the table.

  • Pass: primary key constraint, unique constraint, auto-increment column.

9.3: Domain Integrity: Refers to the accuracy and reliability of each column record in the table.

  • Pass: data type (length), default value, non-empty constraint, check constraint.
  • Note: Check constraints are not supported after mysql5.5.

9.4: Referential integrity: The data listed in one table should be accurate and reliable in the introduction of another table.

  • Pass: Foreign key constraints.
  • Advantages of foreign key constraints: Ensure data security.
  • Disadvantages of foreign key constraints: low efficiency.
  • Note: For overall efficiency and safety, foreign key constraints are generally not established in the database now.

9.5: Custom integrity: guaranteed by stored procedures, triggers and rules.

  • Note: In general, each table must have a primary key.

Summary:
1. Enumeration
2. Annotation
3. Common concepts of
database 4. Uninstallation and installation of
database 5. Database login, service start and stop. (Key)
6. SQL composition (Key)
7. DDL
8 in SQL DML in .sql (emphasis)