MySql database Introduction

MySql database Introduction 1. database-related concepts Dbms: database manager system database management system, that is, database service programs. Generally, the installation of a database is the installation of a dbms. Only the database system that installs the database can provide services. Therefore, when installing the database program, a database instance is installed by default when the management service program is installed. For example, SQL Server installs plus and northwind by default. Mysql installs test, mysql, and information by default. Oracle will prompt you to install an orcl database (of course the name can be changed ). When you need to create a database when developing an application in the future, you can use the relevant client software to connect to the service program to create the relevant database instance. Http://service.ap-southeast-1.maxcompute.aliyun-inc.com/api. Iii. SQL Introduction It is strongly recommended that you use commands to learn SQL in the early stage and do not use visual tools (including creating and modifying table databases). Here MySQL is used as the learning database.
Structured Query Language: the abbreviation of SQL. It is a Structured Query Language. 1. enter the mysql [-h host name or ip address]-u [] username-p [Password] In the window command line, enter mysql-h 192.168.1.106-u chen-p and press enter to wrap the line, you will be prompted to enter the password. Note that if-h is the local address, you do not need to enter-h. 2. Check that dbms manages several databases. There are currently several databases show databases. 4. SQL statement Classification 1. DDL: data definition language database definition language applicable objects: databases and table structures. Keyword: CREATE ALTER DROP
2. DML: data manipulate language applicable to: records in tables. Keyword: insert update DELETE3. DQL: data query language: Applicable to data query language: Table records. Keyword: SELECT www.2cto.com DDL example: 0> create database adtest; 1> create Table: (use test first; database first) create table if not exists employee (Id int primary key, Name varchar (100) unique not null, Gender varchar (5), Birthday date, salary float) 2> View table structure: desc employee; 3> View tables: show tables; 4> add an image column alter table employee add image blob; 5> alter table employ modify name varchar (40); 6> Delete alter table e Mployee drop image; 7> Modify table Name: user rename table employee to user; 8> Modify table character encoding: utf8 alter table employee character set utf8; 9> change the column name to username alter table employee change name username varchar (40); 10> Delete the table employee drop table employee; www.2cto.com DML example: 1> Insert an employee information Insert into employ (id, name, gender, birthday, salary) values ('1', 'xiaochen ', '2017-10-10', 2012 ); 2> the client can view the database encoding Show variables like '% char % '; There are the following: character_set_client: the code table used by the Notification Server Client character_set_connection: the code table used to connect to the database character_set_database: e is the character set used by a database in the database server, the character set specified during Server installation will be used. Character_set_results: character set used by the database to return data to the client. If not specified, the default Character Set of the server is used. Character_set_server: the default character set specified during Server installation. Character_set_system: character set used by the database system. Note: If you throw Chinese characters in the console, you must enter set character_set_client = gbk. To display Chinese characters, enter set character_set_results = gbk; (The console uses local gbk encoding by default ). This set method is only valid for the current command console. 3> update tablename set columnname = value where... or Update t1 (alias) set t1.columnname = value from tablename t1 where... 4. delete from tablename where .... DQL example: 1> query different english scores the same english score only displays one line of Select distinct english from student2> query english score greater than 90, student ID is 1, 3, select id, name, english, chinese, math from student where english> 90 and id in (1, 3, 8, 9); 3> sort the score and output it. Asc ascending, Desc descending SELECT name, math FROM student order by math ASC;
4> query the items that have been purchased and whose total price is greater than 100... having... SELECT id, product, SUM (price) FROM orders group by product having sum (price)> 100; note: in SQL Server and Oracle using group, the characters in select must be in group by or use aggregate functions such as sum (ItmNum), Agv, and Min. MySql does not have this requirement, so the preceding select id can also be used. 4. commonly used mysql functions Count (*) Count sum (math) mathematical score summary avg (chinese) Average chinese score Max (chinese) chinese highest score Min (chinese) chinese lowest score 5. DCL: data control language: Applicable to dba operations, such as grant.
V. Database constraints 1. primary and foreign key constraints the primary key constraints the foreign key of the primary key foreign key business primary key are related to the business, and the logical primary key is irrelevant to the business. Logical primary keys are recommended. 2. Automatic Primary Key auto-increment MySql primary key requires int and auto_increment auto-incrementing. 3. define unique constraint unique4. define non-null constraint not null 6. Relationship between tables 1. one-to-multiple create table department (id int primary key AUTO_INCREMENT, name VARCHAR (100) unique not null); create table employee (id int primary key AUTO_INCREMENT, name VARCHAR (100) not null, salary FLOAT (8, 2), dept_id int, CONSTRAINT dept_id_fk foreign key (dept_id) REFERENCES department (id); 2. create table teacher (id int primary key AUTO_INCREMENT, name VARCHAR (100) not null, salary FLOAT (8, 2); create table students (id int primary key AUTO_INCREMENT, name VARCHAR (100) not null, grade VARCHAR (10); create table teacher_student (t_id int, s_id int, primary key (t_id, s_id), CONSTRAINT t_id_forfk foreign key (t_id) REFERENCES teacher (id), CONSTRAINT s_id_fk foreign key (s_id) REFERENCES students (id); www.2cto.com 3. create table book (id int primary key AUTO_INCREMENT, name VARCHAR (100) not null, price FLOAT (8, 2); create table cartitem (id int primary key, num int, price FLOAT (8, 2), CONSTRAINT B _id_fk FOREIGN KEY (id) REFERENCES book (id ));