What is a database: a repository that stores data. What companies are using databases, financial institutions, gaming sites, shopping sites, forum sites… Software that provides database services: 1. Software categories: MySQL, Oracle, SQL_Server, DB2, MongoDB, MariaDB 2. How to choose which database software to use in the production environment: 1. Open source 1. Open source software: MySQL, Mariadb, MongoDB 2. Commercial software: Oracle, DB2, SQL_Server 2. Cross-platform 1. However, the platform is SQL_Server 2. Cross-platform:…… 3. Types of companies: commercial software: government departments, financial institutions open source software: game websites, shopping websites, forum websites
4.MySQL: 1. Relational database 1. Relational database 1. Data is stored in rows and columns (tables). 2. Each row in a table is a record, and no column is called a field 3. A logical association between tables is called a relationship. Relational database: Table 1 Student information Table Name Age Class Zhang SAN 25 Class 3 Li Si 25 Class 6 Table 2 Class Information Table Class Head teacher Three class big air wing six class pine looking for zhang SAN’s teacher in charge is who through the correlation of two tables can query out (query function is powerful) 2. Non-relational database {” name “:” zhang SAN “, “age” : 25, “teacher in charge”} stores key-value pairs in the form of dictionaries. Cross-platform: Can run database services on Windows, Unix, Linux. 3. Support for multiple programming languages Python, Java, PHP….
5. Database software, database, data factory library concept 1. Database software: is a software, see, can operate, is the logical function of real data 2. Database: A logical concept used to store data in a factory, focusing on storage 3. In terms of the amount of data, the data warehouse is much larger than the database, mainly used for data analysis and data mining
The installation of MySQL
1. Install the MySQL service on Ubuntu
1. Install the server sudo apt-get install mysql-server apt-get
Sudo apt-get install mysql-client
1. Sudo apt-get update
Visit each url in the source list, read the software list, save to local /var/lib/apt/lists
2.sudo apt-get upgrade
Compare the local installed software with the list below. If you find that the installed software version is low, update it all
3. Sudo apt-get -f install
2. Start and link the MySQL server. 1. Sudo /etc/init.d/mysql status (query) 2 Start sudo /etc/init.d/mysql start sudo /etc/init.d/mysql stop sudo D /mysql restart sudo /etc/init.d/mysql reload 2. Client link 1. Command format mysql -h host address -u user name -p password mysql -hlocalhost -uroot -p123456 The usage rule of the SQL command is as follows: 1. No command must be filled; End 2.SQL is case insensitive 3. Use \c to terminate execution of the current command 2. Database management: 1. Basic database operations 1. 2. Create a library create database. Create database database name character set utf8; 3. Run the show create database character set command to create a database. Select database () : 5 Use Library name; 6. Check the existing tables in the database show tables. Drop database drop database name; The naming rules for library names are as follows: 1. Digits, letters, and underscores (_). Library names are case sensitive 3. Special characters and MySQL keywords cannot be used 3. Table management 2. Basic table operations 1. View the table show tables 2. Create table create table name (field name data type, field name data type,……. ); 3. Run the show create table character set command to query the character set of the existing table. 4. Check table structure desc table name. 5. Drop the table name. Note: 1. All data are stored in the root directory of the database as files 2. Data directory: /var/lib/mysql
CD 2.vi. Bash_profile Add: PATH= “$PATH” : /usr/local/mysw/bin 3. source. bash_profile 5 Table record management 1. Insert into values (1), (2)….. ; 2. Insert into table name (select * from table 1,…..) Values (1),…. ; Select * from table_name where (select * from table_name where (select * from table_name where (select * from table_name)) Select * from (select * from (select * from (select * from (select * from (select * from (select * from)))) Form table name [where condition];
How to change the default character set 1. Method (By changing the mysql configuration file) 2. Step 1 Obtain the root permission sudo -i. 2. Subl mysqld. CNF [mysqld] character_set_server = utf8 5 /etc/init.d/mysql 7. 1. Connect to the database server mysql-uroot -p123456 2. Select a database use database name 3. Create a table/modify a table update… 4. Disconnect from the database exit; | quit; | \ q 8. The data type 1. Digital type 1. The integer 1. Int big integer (4 bytes) scope: 2 * * 32-1 (4294967295) 2. Small integer tinyint (one byte) 1. Signed (signed default) : -128 to 127 2. Smallint Specifies a small integer (2 bytes). 4. Bigint Specifies a large integer (8 bytes). Float (4 bytes, display up to 7 significant bits) 1. Float (m, n) m: total number of digits n: decimal number float (5,2) value range? -999.99 to 999.99 2. Decimal (displays a maximum of 28 valid bits) Decimal (M, N) Storage space (integers and decimals are stored separately) rule: Wrap multiples of 9 into four bytes remainder bytes 0 0 1-2 1 3-4 2 5-6 3 7-9 4 example decimal (19,9) integer part: 10/9 = 1%1 4 bytes +1 byte =5 bytes
Decimal part: 9/9 = 1%0 4 bytes +0 bytes =4 bytes
Total value: 9 bytes
2. Character type 1.char(fixed length) 1. Value range: 1 to 255 2. Varcahr (variable length) 1. Value range: 1 to 65535 3.text/ longtext (storage range: 4G)/BLOb/Longblob (4G) Char and VARCHAR features: 1. Cahr: wastes storage space and provides high performance. 2. Save storage space and low performance
3, exercise 1, and create the library testdb, specify the character set for the utf8 2, 3 into the library testdb, view the current library 4, create library testdb2, designated as the latin1 character set 5 testdb2 6, check the testdb2, into the library Select * from testdb; select * from testdb2; select * from testdb; select * from testdb2;
create database testdb character set utf8;
use testdb;
select database();
create database testdb2 character set latin1;
use testdb2;
show create database testdb2;
drop database testdb;
drop database testdb2;Copy the code
Mysql > alter table pymysql set character set UTf8; mysql > alter table pymysql set character set UTf8 Id, name, char (15), age 3, check pymysql create table statement 4, check pymysql table structure 5, delete table pymysql 6, create the library python2 7, in python2 create table t1 and specify the character set for the utf8, id field Mysql > alter table t1 alter table t1 alter table t1 alter database python2
The answer:
create database python1; use python1; create table pymysql( id int, name char(15), age int ) character set utf8; show create table pymysql; desc pymysql; drop table pymysql; create database python2; use python2; Create table t1(id int, name char(15), score float(5,2)) character set utf8; desc t1; drop table t1; drop database python2;Copy the code
Select * from studb; select id, name, age from studB; select * from studB; select * from studB; select * from studB; select * from studB Select * from ‘tab1’ where name = ‘age’; select * from ‘tab1’ where age = 20; select * from ‘tab1’ where age = 20;
show databases; create database studb; use studb; create table tab1( id int, name char(10), age int )character set utf8; desc tab1; Insert into values (1, 2, 3); insert into values (1, 2, 3); Insert into tab1(name,age) values (" 大 吉 大 王",88),(" 大 吉 大 王",87); select * from tab1; select name,age from tab1; select * from tab1 where age>20;Copy the code
Stuinfo: id: int name: int width: 15 age: int height: 2 digits (float) money: stuinfo: id: int name: int width: 15 Select decimal from STUinfo; select decimal from STUinfo; select decimal from STUinfo; select decimal from STUinfo; select decimal from STUinfo;
Create table stuinfo(id int, name char(15), age tinyint unsigned, height float(5,2), money decimal(20,2)); desc stuinfo; Insert into stuinfo values (1,"Bob", 60, 180); select * from stuinfo; insert into stuinfo(id,name) values (2,"Jim"),(3,"Tom"); select id,name from stuinfo;Copy the code
MySQL > select * from ‘MySQL’; ____ ____ Enumeration type Date and time type Integer and character type 2. The core content of a relational database is ___, that is, two-dimensional table relationship 3. Describe the process of storing data on the client to the database server Select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ select * from ‘school’ Select name, age, gender, grade from student where id = 1; select * from student where id = 1; select * from student where id = 1; select * from student where id = 1; select * from student where id = 1; select * from student where id = 1; select * from student where id = 1; select * from student where id = 1 8, Check the names and grades of students who passed (>60)
The answer:
create database school; use school; Create table students(id int, name char(15), age tinyint unsigned, gender char(2), score float(5,2)); desc students; Insert into students values (1, "zhang", 20, "male", 95.56), (2, "bill", 21, "male", 98.30), (3, "king of light", 21, "female", 100); Insert into students (name,score) values (" Tom ",60), ("zhao", 56); select name,score from sutdents; select name,score from students where score>=60;Copy the code