The first chapter
This is the 16th day of my participation in Gwen Challenge
Data, DataBase (DataBase,DB), DataBase management system (DBMS) and DataBase system are four basic concepts closely related to DataBase technology. The characteristics of database system: 1. Data structure 2. High data sharing, low redundancy easy to expand 3. Data management technology has experienced manual management, file system, database system three stages. CLS (clear above) one. Open MsSql login and exit -d in CMD, --database=name Open the specified database --delimiter =name Specify the delimiter -h, --host =name Server name -p, --password[=name] password -p, --port=# port number --prompt =name --user=name user name -v, Mysql -uroot -p h127.0.0.1 -p3306 mysql exit mysql -uroot -p h127.0.0.1 -p3306 quit; \q; Modify the mysql prompt 1. Connect the client to the mysql-uroot-proot-prompt 2. Connect to the client and use prompt to modify prompt promp /u mysql prompt \D: full date \D: current database \h: server name \u: Select version(); 1.1 Data model Model: simulation and abstraction of the real world Database models are divided into four types: hierarchical model, gateway model, relational model, and object-oriented model.Copy the codeChapter 2 Mysql (Relational Database)
1. Query all databases on the database server. Select a database. Use database name. 3. Create database create database name; 5. View all tables in a database and check whether the tables are created successfully. show tables; Create table CCTV (name VARCHAR(20),OWNER VARCHAR(20),species VARCHAR(20),sex CHAR(1),birth The DATE of death DATE); Describe data table name; Select * from (select * from (select * from (select * from)); Insert into values; insert into values; Delete from table_name where table_name = 1; Update table name set set where condition; update a set age=18 where name="b"; Drop table [is exists] drop table 1, table 2, table 3... ; Drop table drop table name; SQL > select distinct from SQL; 14 Query interval between... and... Select * from scort where degree between 60 and 80; select * from scort where degree between 60 and 80; Select * from score where degree>60 and degree<80; select * from score where degree>60 and degree<80 Select * from score where degree ="c" or cb="cb"; select * from score where degree ="c" or cb="cb"; Select * from student order by class desc; Select * from student order by class ASC; Count select count(*) from student where class=9511; Max select Max () 20. Limit 0,2; Select * from new limit 0,2; Select * from new group by name; Having select * from new group by id having ID =3; Select s_name,c_no,sc_degree from student,score where student. S_no =score. S_no; Select sname,student.sno as stu_sno where sname,student.sno as stu_sno where sname,student.sno as stu_sno where sname,student.sno as stu_sno where sname,student.sno as stu_sno where sname,student.sno as stu_sno where sname,student.sno as stu_sno Select year(s_birthday) from student; Select c_name from course union select c_no from course; Select t_no from course where t_no >any(select t_no from course); 28. All () select all(t_no) from course; 29. Not, like,%, _ not: not equal to like: compare character king %: as long as the first character is king king _:_ take a character 30. New select year(now()); Alter table table_name alter table table_name alter table table_name alter table book add abc int; Alter table table name add new field name datatype constraint; alter table book add abc int default '0'; Alter table table_name add new table_name data type first; alter table book add def int first; Alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table book add ggg int after def; Alter table table_name drop table_name; alter table MyClass drop name; Alter table alter table alter table alter table alter table alter table bumen modify name int; Alter table bumen modify name varchar(5) after dianhua; Alter table rename to alter table rename; alter table bumen rename to bumen01; 35. Alter table table name modify field name data type; alter table book modify name varchar(10); Alter table alter table name alter table name alter table name alter table name alter table name alter table name alter table book change jli_name j_name varchar(6); 37. Modify the fields are positioned: alter table table name modify field 1 data type first | after field 2; alter table book modify abc int first; Alter table engine= new engine; Select * from primary table where foreign key constraint is deleted; Common data types a). Data type type size range (signed) range (unsigned) usage TINYINT 1 byte (-128, 127) (0, 255) SMALLINT 2 byte (-32, 768,32, 767) (0, MEDIUMINT 3 bytes (-8 388 608,8 388 607) (0,16 777 215) 4 bytes (-2 147 483 648, 2, 147, 483, 647) (0, 4, 294, 967, 295) big integer BIGINT 8 bytes (9223372036854775808, 9, 223, 372, 036, 854, 775, 807) (0, 18 446 744 073 709 551 615) Maximum integer value FLOAT 4 bytes (-3.402 823 466 E+38, -1.175 494 351 e-38), 0, (1.175 494 351 e-38, 3.402 823 466 351 E+38) 0, (1.175 494 351 E-38, 3.402 823 466 466 E+38) DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+38) -2.225 073 058 058 058 058 058 050 0 E+308), 0, (2.225 073 058 058 050 0 E+308) 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) DECIMAL vs. DECIMAL(M,D), if M>D, Is M+2 otherwise is D+2 depending on the values of M and D depending on the values of M and D small number b). DATE and TIME Type Size (bytes) Range Format Function DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD DATE value TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS TIME value or duration YEAR 1 1901/2155 YYYY YEAR DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS TIMESTAMP 4 1970-01-01 00:00:00/2038 c). String Type Type Size Function CHAR 0-255 bytes fixed length string VARCHAR 0-65535 bytes variable length string TINYBLOB 0-255 bytes Binary string with a maximum of 255 characters TINYTEXT 0-255 bytes short text string BLOB 0-65 535 bytes long TEXT data in binary format TEXT 0-65 535 bytes long TEXT data MEDIUMBLOB 0-16 777 215 bytes MEDIUMTEXT data in binary format MEDIUMTEXT 0-16 777 215 bytes LONGBLOB 0-4 294 967 295 bytes long 0-4 294 967 295 bytes LONGBLOB 0-4 294 967 295 bytes LONGBLOB 0-4 294 967 295 bytes LONGBLOB 0-4 294 967 295 bytes LONGBLOB 0-4 294 967 295 bytes net stop mysqlCopy the codeThe third chapter
Mysql create table constraint Primary key constraint, which uniquely identifies a single entry in a table. create table user( id int primary key , name varchar(20)); Union primary key: Create table user(id int, name varchar(20), Create table user3(id int primary key auto_increment,name vARCHar (20)); insert into user3(name) values("cc"); Add primary key auto_increment increment from 1 to!!!!! If we create a table without adding a primary key. create table user(id int,name,varchar(2)); alter table user add primary key(id); !!!!!!!!!!!!!!!!!! Alter table user drop primary key; !!!!!!!!!!!!!!!!!! Alter table user modify ID int primary key; Unique constraint The value of the modified field cannot be repeated create table user(id int,name varchar(20); Alter table user add unique(name); Constraint on creating a table Create table user(id int,name varchar(20),unique(name); uniques(id,name); Alter table user modify name varchar(20) unique; Alter table user drop index name; Create table user(ID int,name vARCHar (20) not NULL); Default constraint When inserting a field value, if no value is passed, the default value is used. create table user(id int,name int default 10)); Insert into uservalues (1, 2); If a value is passed in, the default value is not used. The foreign key constraint involves two tables: parent table, child table primary table, and secondary table. create table classes(id int primary key,name varchar(20)); create table students(id int primary key,name varchar(20),class_id int,foreign key(class_id) references classes(id)); create table stude1(name1 int primary key,scort int,ccc_name varchar(20),foreign key(ccc_name) references ccc(name)); Associate the primary table name with foreign key References (associated key)Copy the codeconclusion
The article is long, give a big thumbs up to those who see it! Due to the author’s limited level, the article will inevitably have mistakes, welcome friends feedback correction.
If you find this article helpful, please like, comment, and bookmark it
Your support is my biggest motivation!!